1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | import java.io.ObjectInputStream.GetField; import java.math.BigInteger; import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; import java.util.HashMap; import java.util.Map; import org.neo4j.graphdb.DynamicRelationshipType; import org.neo4j.graphdb.GraphDatabaseService; import org.neo4j.graphdb.Node; import org.neo4j.graphdb.index.BatchInserterIndex; import org.neo4j.graphdb.index.BatchInserterIndexProvider; import org.neo4j.helpers.collection.MapUtil; import org.neo4j.kernel.impl.batchinsert.BatchInserter; import org.neo4j.kernel.impl.batchinsert.BatchInserterImpl; import org.neo4j.index.impl.lucene.*; public class Importer { public static void main(String[] argv) { BatchInserter inserter = new BatchInserterImpl("target/neo4jdb-batchinsert"); BatchInserterIndexProvider indexProvider = new LuceneBatchInserterIndexProvider(inserter); BatchInserterIndex labels = indexProvider.nodeIndex("labels", MapUtil.stringMap("type", "exact")); labels.setCacheCapacity("Label", 100000); System.out.println("-------- PostgreSQL " + "JDBC Connection Testing ------------"); try { Class.forName("org.postgresql.Driver"); } catch (ClassNotFoundException e) { System.out.println("Where is your PostgreSQL JDBC Driver? " + "Include in your library path!"); e.printStackTrace(); } System.out.println("PostgreSQL JDBC Driver Registered!"); Connection conn = null; try { conn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/contractDashboard", "postgres", "snmc"); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); e.printStackTrace(); } if (conn != null) { System.out.println("You made it, take control your database now!"); } else { System.out.println("Failed to make connection!"); } try { // Print all warnings for (SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning()) { System.out.println("SQL Warning:"); System.out.println("State : " + warn.getSQLState()); System.out.println("Message: " + warn.getMessage()); System.out.println("Error : " + warn.getErrorCode()); } // Get a statement from the connection Statement stmt = conn.createStatement(); // Execute the query ResultSet rs = stmt.executeQuery("SELECT contractnotice.\"agencyName\", " + " contractnotice.\"supplierABN\",contractnotice.\"supplierName\",sum(value) as sum " + "FROM public.contractnotice where contractnotice.\"agencyName\" != 'Department of Defence'" + " AND contractnotice.\"agencyName\" != 'Defence Materiel Organisation' GROUP BY contractnotice.\"agencyName\", " + " contractnotice.\"supplierABN\",contractnotice.\"supplierName\""); String previousAgency = ""; GraphDatabaseService gds = inserter.getGraphDbService(); HashMap<String, Long> supplierIDs = new HashMap<String, Long>(); HashMap<String, Long> agencyIDs = new HashMap<String, Long>(); // Loop through the result set while (rs.next()) { long supplierID, agencyID; String supplierKey; if (agencyIDs.get(rs.getString("agencyName")) == null) { Node myNode = gds.createNode(); myNode.setProperty("Label", rs.getString("agencyName")); myNode.setProperty("type", "agency"); agencyIDs.put(rs.getString("agencyName"), myNode.getId()); if (myNode.getId() % 100 == 0) { System.out.println("Agency " + myNode.getId()); } } agencyID = agencyIDs.get(rs.getString("agencyName")); if (rs.getString("supplierABN") != "0" && rs.getString("supplierABN") != "") { supplierKey = rs.getString("supplierABN"); } else { supplierKey = rs.getString("supplierName"); } // inject some data if (supplierIDs.get(supplierKey) == null) { Node myNode = gds.createNode(); myNode.setProperty("Label", rs.getString("supplierName")); myNode.setProperty("type", "supplier"); supplierIDs.put(supplierKey, myNode.getId()); if (myNode.getId() % 1000 == 0) { System.out.println("Supplier " + myNode.getId()); } } supplierID = supplierIDs.get(supplierKey); long rel = inserter.createRelationship(agencyID, supplierID, DynamicRelationshipType.withName("KNOWS"), null); inserter.setRelationshipProperty(rel, "Weight", rs.getDouble("sum")); } // Close the result set, statement and the connection rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { System.out.println("SQL Exception:"); // Loop through the SQL Exceptions while (se != null) { System.out.println("State : " + se.getSQLState()); System.out.println("Message: " + se.getMessage()); System.out.println("Error : " + se.getErrorCode()); se = se.getNextException(); } } //make the changes visible for reading, use this sparsely, requires IO! labels.flush(); // Make sure to shut down the index provider indexProvider.shutdown(); inserter.shutdown(); } } |