diff --git a/src/osm/jp/coverage/postoffice/DbPostoffice.java b/src/osm/jp/coverage/postoffice/DbPostoffice.java index 17ac222..80c0e6e 100644 --- a/src/osm/jp/coverage/postoffice/DbPostoffice.java +++ b/src/osm/jp/coverage/postoffice/DbPostoffice.java @@ -52,6 +52,7 @@ } } + DbPostoffice.margeDb(con); DbPostoffice.export(con); } finally { @@ -285,15 +286,40 @@ } /** + * UPDATE TABLE_NAME SET column = Expression [, ...] [WHERE Expression]; + * + * @param con + * @throws java.sql.SQLException + */ + public static void margeDb(Connection con) throws SQLException { + PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon FROM "+ TABLE_NAME2); + try (ResultSet rset8 = ps8.executeQuery()) { + while (rset8.next()) { + String idref = rset8.getString(1); + Double lat = rset8.getDouble(2); + Double lon = rset8.getDouble(3); + + String updateSt = "UPDATE "+ TABLE_NAME +" SET lat=?, lon=? WHERE idref=?"; + try (PreparedStatement ps = con.prepareStatement(updateSt)) { + ps.setDouble(1, lat); + ps.setDouble(2, lon); + ps.setString(3, idref); + ps.executeUpdate(); + } + } + } + } + + /** * 'table.POSTOFFICE'の内容をCSV形式にして標準出力に出力する * @param con * @throws java.sql.SQLException */ public static void export(Connection con) throws SQLException { - String header = "idref,lat,lon,fixed"; + String header = "idref,lat,lon,fixed,area"; System.out.println("TABLE: "+ TABLE_NAME); System.out.println(header); - PreparedStatement ps8 = con.prepareStatement("SELECT "+ TABLE_NAME +".idref,"+ TABLE_NAME2 +".lat,"+ TABLE_NAME2 +".lon,fixed,area FROM "+ TABLE_NAME +","+ TABLE_NAME2 +" where "+ TABLE_NAME +".idref="+ TABLE_NAME2 +".idref"); + PreparedStatement ps8 = con.prepareStatement("SELECT idref,lat,lon,fixed,area FROM "+ TABLE_NAME); try (ResultSet rset8 = ps8.executeQuery()) { while (rset8.next()) { String name = rset8.getString(1);