diff --git a/src/osm/jp/api/HttpPOST.java b/src/osm/jp/api/HttpPOST.java index 47e8cf0..e5cc453 100644 --- a/src/osm/jp/api/HttpPOST.java +++ b/src/osm/jp/api/HttpPOST.java @@ -10,6 +10,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLIntegrityConstraintViolationException; +import java.sql.SQLSyntaxErrorException; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; @@ -18,7 +19,6 @@ import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; -import osm.jp.coverage.busstop.DbBusstop; /** * Java HTTP クライアントサンプル - HttpURLConnection 版 - @@ -60,8 +60,9 @@ String createSt; if (withdrop) { - DbBusstop.drop(con, HttpPOST.TABLE_NAME); - DbBusstop.drop(con, "AREA_NODE"); + sql(con, "DROP TABLE "+ HttpPOST.TABLE_NAME +";"); + sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;"); + sql(con, "DROP TABLE AREA_NODE"); } // 'table.FUEL_EXIST'を新規に作る @@ -74,6 +75,20 @@ createSt = "CREATE TABLE AREA_NODE (idref VARCHAR(12) NOT NULL, pid VARCHAR(12), lat DOUBLE, lon DOUBLE);"; Db.updateSQL(con, createSt); } + + public static void sql(Connection con, String sql) throws SQLException { + System.out.println(sql); + try (PreparedStatement ps = con.prepareStatement(sql)) { + ps.executeUpdate(); + } + catch (SQLSyntaxErrorException e) { + System.out.println("107:"+ e.toString()); + if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { + throw e; + } + } + } + public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException { getCapabilities(key, value, minLat, maxLat, minLon, maxLon, "node"); @@ -457,12 +472,13 @@ } // idref と brandStr をデータベースに格納する - System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon, score) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +")N"); - try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon, score) VALUES (?,?,?,?)")) { + System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N"); + try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) { ps5.setString(1, idrefStr); ps5.setDouble(2, Double.parseDouble(latStr)); ps5.setDouble(3, Double.parseDouble(lonStr)); ps5.setInt(4, score); + ps5.setString(5, nameStr); ps5.executeUpdate(); } catch (HsqlException | SQLIntegrityConstraintViolationException e) { diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java index 9dbae80..4cc5947 100644 --- a/src/osm/jp/coverage/busstop/DbBusstop.java +++ b/src/osm/jp/coverage/busstop/DbBusstop.java @@ -5,7 +5,6 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.SQLSyntaxErrorException; import java.util.ArrayList; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; @@ -16,6 +15,7 @@ import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; +import osm.jp.api.HttpPOST; public class DbBusstop { public static final String TABLE_NAME = "bus_stop"; @@ -57,7 +57,8 @@ try { conHsql = DatabaseTool.openDb("database"); if (DROP) { - dropTable(conHsql); + HttpPOST.sql(conHsql, "DROP TABLE "+ TABLE_NAME); + HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;"); } create(conHsql); try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) { @@ -347,49 +348,12 @@ // 'table.BUS_STOP'を新規に作る createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));"; - create(conHsql, createSt); + HttpPOST.sql(conHsql, createSt); + + createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);"; + HttpPOST.sql(conHsql, createSt); } - public static void create(Connection con, String createsql) throws SQLException { - System.out.println(createsql); - try (PreparedStatement ps = con.prepareStatement(createsql)) { - ps.executeUpdate(); - } - catch (SQLSyntaxErrorException e) { - System.out.println("83:"+ e.toString()); - if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: object name already exists:"))) { - throw e; - } - } - } - - /** - * 'table.BUS_STOP'を削除する - * @param conHsql - * @throws java.io.FileNotFoundException - * @throws java.lang.ClassNotFoundException - * @throws SQLException - * @throws javax.xml.parsers.ParserConfigurationException - * @throws org.xml.sax.SAXException - */ - public static void dropTable (Connection conHsql) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException { - drop(conHsql, TABLE_NAME); - } - - public static void drop(Connection con, String tableName) throws SQLException { - String createSt = "DROP TABLE "+ tableName +";"; - System.out.println(createSt); - try (PreparedStatement ps = con.prepareStatement(createSt)) { - ps.executeUpdate(); - } - catch (SQLSyntaxErrorException e) { - System.out.println("107:"+ e.toString()); - if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) { - throw e; - } - } - } - /** * 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する * @param con