src/osm/jp/DbBusstop.java

   1 package osm.jp;\r
   2 \r
   3 import java.io.*;\r
   4 import java.sql.Connection;\r
   5 import java.sql.PreparedStatement;\r
   6 import java.sql.ResultSet;\r
   7 import java.sql.SQLException;\r
   8 import jp.co.areaweb.tools.database.*;\r
   9 \r
  10 public class DbBusstop {\r
  11         File inputFile;\r
  12         String filter = "";\r
  13         int iCounter = 0;\r
  14         String urlStr = "";\r
  15         Connection con;\r
  16         String timeStampStr = null;\r
  17         File dir = null;\r
  18 \r
  19         /** メイン\r
  20          * @throws IOException\r
  21          * @throws SQLException\r
  22          * @throws ClassNotFoundException\r
  23          * @throws FileNotFoundException */\r
  24         public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException \r
  25         {\r
  26                 Connection con = null;\r
  27                 try {\r
  28                         con = DatabaseTool.openDb("database");\r
  29                         DbBusstop.export(con);\r
  30                 }\r
  31                 finally {\r
  32                         if (con != null) {\r
  33                                 DatabaseTool.closeDb(con);\r
  34                         }\r
  35                 }\r
  36         }\r
  37 \r
  38         /**\r
  39          * 'table.BUS_STOP'を新規に作る\r
  40          * 既にテーブルが存在する時には何もしない\r
  41          * @param con\r
  42          * @throws SQLException\r
  43          */\r
  44         public static void create(Connection con) throws SQLException {\r
  45                 create(con, "CREATE TABLE bus_stop (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, ifile VARCHAR(128), CONSTRAINT bus_stop_pk PRIMARY KEY(idref));");\r
  46         }\r
  47         \r
  48         public static void create(Connection con, String createsql) throws SQLException {\r
  49                 System.out.println(createsql);\r
  50                 PreparedStatement ps = con.prepareStatement(createsql);\r
  51                 try {\r
  52                         ps.executeUpdate();\r
  53                 }\r
  54                 catch (SQLException e) {\r
  55                         if (!(e.toString().startsWith("Table already exists:"))) {\r
  56                                 throw e;\r
  57                         }\r
  58                 }\r
  59                 finally {\r
  60                         ps.close();\r
  61                 }\r
  62         }\r
  63 \r
  64         /**\r
  65          * 'table.BUS_STOP'を削除する\r
  66          * @param con\r
  67          * @throws SQLException\r
  68          */\r
  69         public static void drop(Connection con) throws SQLException {\r
  70                 String createSt = "DROP TABLE bus_stop;";\r
  71                 System.out.println(createSt);\r
  72                 PreparedStatement ps = con.prepareStatement(createSt);\r
  73                 ps.executeUpdate();\r
  74                 ps.close();\r
  75         }\r
  76         \r
  77         /**\r
  78          * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する\r
  79          * @param con\r
  80          */\r
  81         public static void export(Connection con) {\r
  82                 try {\r
  83                         System.out.println("TABLE: BUS_STOP");\r
  84                         System.out.println("\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\"");\r
  85                         PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed,ifile FROM bus_stop");\r
  86                         ResultSet rset8 = ps8.executeQuery();\r
  87                         while (rset8.next()) {\r
  88                                 String name = rset8.getString(1);\r
  89                                 Double lat = rset8.getDouble(2);\r
  90                                 Double lon = rset8.getDouble(3);\r
  91                                 int fixed = rset8.getInt(4);\r
  92                                 String ifile = rset8.getString(5);\r
  93                                 System.out.println("\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\"");\r
  94                         }\r
  95                         rset8.close();\r
  96                 }\r
  97                 catch (SQLException e) {\r
  98                         e.printStackTrace();\r
  99                 }\r
 100         }\r
 101 }