/** * Creates a new MapDbConnector and connects to the database * * @param db path to the db which to connect * @throws FileNotFoundException if the passed DB path does not exist */ private MapDbConnector(File db) throws FileNotFoundException { if (!db.exists()) throw new FileNotFoundException("The passed DB does not exist"); try { Class.forName(driver); // Load driver connection = DriverManager.getConnection("jdbc:derby:" + db.getAbsolutePath()); // Establish connection } catch (ClassNotFoundException e) { throw new RuntimeException("The JDBC driver " + driver + " was not found.", e); } catch (SQLException e) { throw new RuntimeException("Failed to connect to the database!", e); } }
/** * Creates the database from the source file, if the database does not exist. * * <p>This implementation requires the org.apache.derby library. */ public static void install() { if (db.exists()) return; // No need to create the DB, if it already exists boolean success = false; try { Statement statement; Connection connection; // CREATE DB FILE try { Class.forName(driver); // Load driver connection = DriverManager.getConnection("jdbc:derby:" + db.getAbsolutePath() + ";create=true"); statement = connection.createStatement(); } catch (Exception e) { throw new RuntimeException( "Failed to create database - is org.apache.derby lib availible?", e); } // READ IN SOURCE SQL INSTRUCTIONS Scanner scanner; try { scanner = new Scanner(source, "UTF-8"); } catch (FileNotFoundException e) { throw new RuntimeException("Failed to create database. Source is missing.", e); } StringBuilder sql = new StringBuilder((int) source.length()); try { if (scanner.hasNextLine()) sql.append(scanner.useDelimiter("\\A").next()); } finally { scanner.close(); } String[] sqlStatements = sql.toString().split(";"); // BUILD DB TABLES AND DEFAULT CONTENT try { connection.setAutoCommit(false); for (String sqlStatement : sqlStatements) { System.out.println(sqlStatement); statement.execute(sqlStatement); } connection.commit(); // Release locks } catch (SQLException e) { throw new RuntimeException( "Failed to create database. Could not execute source instructions.", e); } // INSERT NODES DATA System.out.println("Inserting nodes..."); try { final PreparedStatement nodeStatement = connection.prepareStatement( "INSERT INTO \"nodes\" (\"id\", \"x\", \"y\") VALUES (?, ?, ?)"); KrakParser.getInstance() .parseNodes( new ParserCallback<String>() { public void callback(String line) { try { String[] data = line.split(","); nodeStatement.setInt(1, Integer.parseInt(data[2])); nodeStatement.setDouble(2, Double.parseDouble(data[3])); nodeStatement.setDouble(3, Double.parseDouble(data[4])); nodeStatement.execute(); } catch (SQLException e) { throw new RuntimeException( "Failed to create database. Could not insert nodes data.", e); } } }); connection.commit(); } catch (SQLException e) { throw new RuntimeException( "Failed to create database. Could not prepare to insert nodes data.", e); } // INSERT CONNECTIONS DATA System.out.println("Inserting connections..."); try { final PreparedStatement cS = connection.prepareStatement( "INSERT INTO \"connections\" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); final PreparedStatement rS = connection.prepareStatement( "INSERT INTO \"roads\" (\"name\", \"krak_road_no\") VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS); KrakParser.getInstance() .parseConnections( new ParserCallback<String>() { private Map<Integer, Map<String, Integer>> roadsKeyLookup = new HashMap<Integer, Map<String, Integer>>(); public void callback(String line) { try { String[] data = KrakParser.splitCsv(line); int roadKey; int krakRoadNo; try { krakRoadNo = Integer.parseInt(data[30]); } catch (NumberFormatException e) { krakRoadNo = 0; // In stupid case of ********** that should not be possible <_< } String roadName = data[6].substring(1, data[6].length() - 1); if (!roadsKeyLookup.containsKey(krakRoadNo) || !roadsKeyLookup.get(krakRoadNo).containsKey(roadName)) { rS.setString(1, roadName); rS.setInt(2, krakRoadNo); rS.execute(); ResultSet keys = rS.getGeneratedKeys(); keys.next(); roadKey = keys.getInt(1); if (!roadsKeyLookup.containsKey(krakRoadNo)) roadsKeyLookup.put(krakRoadNo, new HashMap<String, Integer>()); roadsKeyLookup.get(krakRoadNo).put(roadName, roadKey); } else roadKey = roadsKeyLookup.get(krakRoadNo).get(roadName); Integer fLNumber = "0".equals(data[7]) ? null : Integer.parseInt(data[7]); Integer tLNumber = "0".equals(data[8]) ? null : Integer.parseInt(data[8]); Integer fRNumber = "0".equals(data[9]) ? null : Integer.parseInt(data[9]); Integer tRNumber = "0".equals(data[10]) ? null : Integer.parseInt(data[10]); String fLLetter = data[11].substring(1, data[11].length() - 1).trim(); String tLLetter = data[12].substring(1, data[12].length() - 1).trim(); String fRLetter = data[13].substring(1, data[13].length() - 1).trim(); String tRLetter = data[14].substring(1, data[14].length() - 1).trim(); String ruteNr = data[22].substring(1, data[22].length() - 1).trim(); String oneway = data[27].substring(1, data[27].length() - 1).trim(); // SET TO NULL IF NO DATA IS PRESENT OR PARSE AND SET VALUES IF PRESENT // ORIGINAL KRAK FIELDS cS.setInt(1, Integer.parseInt(data[4])); // DAV_DK-ID cS.setInt(2, Integer.parseInt(data[0])); // FNODE# cS.setInt(3, Integer.parseInt(data[1])); // TNODE# cS.setDouble(4, Double.parseDouble(data[2])); // LENGTH cS.setShort(5, Short.parseShort(data[5])); // TYP cS.setInt(6, roadKey); // Road id if (fLNumber == null) cS.setNull(7, INTEGER); else cS.setInt(7, fLNumber); // FROMLEFT if (tLNumber == null) cS.setNull(8, INTEGER); else cS.setInt(8, tLNumber); // TOLEFT if (fRNumber == null) cS.setNull(9, INTEGER); else cS.setInt(9, fRNumber); // FROMRIGHT if (tRNumber == null) cS.setNull(10, INTEGER); else cS.setInt(10, tRNumber); // TORIGHT if (fLLetter.isEmpty()) cS.setNull(11, VARCHAR); else cS.setString(11, fLLetter); // FROMLEFT_BOGSTAV if (tLLetter.isEmpty()) cS.setNull(12, VARCHAR); else cS.setString(12, tLLetter); // TOLEFT_BOGSTAV if (fRLetter.isEmpty()) cS.setNull(13, VARCHAR); else cS.setString(13, fRLetter); // FROMRIGHT_BOGSTAV if (tRLetter.isEmpty()) cS.setNull(14, VARCHAR); else cS.setString(14, tRLetter); // TORIGHT_BOGSTAV if ("0".equals(data[15])) cS.setNull(15, INTEGER); else cS.setInt(15, Integer.parseInt(data[15])); // V_SOGNENR if ("0".equals(data[16])) cS.setNull(16, INTEGER); else cS.setInt(16, Integer.parseInt(data[16])); // H_SOGNENR if ("0".equals(data[17])) cS.setNull(17, SMALLINT); else cS.setShort(17, Short.parseShort(data[17])); // V_POSTNR if ("0".equals(data[18])) cS.setNull(18, SMALLINT); else cS.setShort(18, Short.parseShort(data[18])); // H_POSTNR if ("0".equals(data[19])) cS.setNull(19, SMALLINT); else cS.setShort(19, Short.parseShort(data[19])); // KOMMUNENR if ("0".equals(data[20])) cS.setNull(20, INTEGER); else cS.setInt(20, Integer.parseInt(data[20])); // VEJKODE if ("0".equals(data[21])) cS.setNull(21, SMALLINT); else cS.setShort(21, Short.parseShort(data[21])); // SUBNET if ("".equals(ruteNr)) cS.setNull(22, VARCHAR); else cS.setString(22, ruteNr); // RUTENR if ("0".equals(data[23])) cS.setNull(23, SMALLINT); else cS.setShort(23, Short.parseShort(data[23])); // FRAKOERSEL if ("0".equals(data[24])) cS.setNull(24, SMALLINT); else cS.setShort(24, Short.parseShort(data[24])); // ZONE cS.setShort(25, Short.parseShort(data[25])); // SPEED cS.setDouble(26, Double.parseDouble(data[26])); // DRIVETIME cS.setString(27, Direction.fromKrak(oneway).toString()); // ONE_WAY cS.setLong(28, Long.parseLong(data[32])); // TJEK_ID /* Hardkodede fiks til kdv_unload.txt Kommune nr @ Vej => Korrekt Kommune nr @ Vej (connection KDV-ID) 1309 @ Lundevejen => 309 @ Lundevejen (connection 62879) 13 @ => 213 @ (connection 72147) 53 @ => 153 @ (connection 177991) 47 @ => 747 @ (connection 602399) */ cS.execute(); } catch (SQLException e) { throw new RuntimeException( "Failed to create database. Could not insert connection data.", e); } } }); connection.commit(); } catch (SQLException e) { throw new RuntimeException( "Failed to create database. Could not prepare inserts of connections data.", e); } // ATTEMPT TO CLEAN UP BY COMPRESSING THE DB System.out.println("Attempting compression..."); try { PreparedStatement compressor = connection.prepareStatement("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)"); final String userSchema = "APP"; compressor.setString(1, userSchema); // User tables compressor.setShort(3, (short) 1); ResultSet tables = connection.getMetaData().getTables(null, userSchema, null, null); while (tables.next()) { compressor.setString(2, tables.getString("TABLE_NAME")); compressor.execute(); } connection.commit(); } catch (SQLException e) { System.out.println("Failed to compress the DB!"); } // DONE! System.out.println("Done!"); success = true; } finally { // Undo work if unsuccessful if (!success) db.delete(true); // Delete recursively } }