| import java.sql.Connection; | import java.sql.Connection; |
| import java.sql.DriverManager; | import java.sql.DriverManager; |
| import java.sql.ResultSet; | import java.sql.ResultSet; |
| import java.sql.SQLException; | import java.sql.SQLException; |
| import java.sql.SQLWarning; | import java.sql.SQLWarning; |
| import java.sql.Statement; | import java.sql.Statement; |
| import java.io.File; | |
| import java.io.IOException; | |
| import java.io.InputStream; | |
| import java.io.Writer; | |
| import java.util.HashMap; | import java.util.HashMap; |
| import java.util.Map; | |
| import org.neo4j.graphdb.Direction; | |
| import org.neo4j.graphdb.DynamicLabel; | |
| import org.neo4j.graphdb.DynamicRelationshipType; | import org.neo4j.graphdb.DynamicRelationshipType; |
| import org.neo4j.graphdb.GraphDatabaseService; | import org.neo4j.graphdb.GraphDatabaseService; |
| import org.neo4j.graphdb.Label; | |
| import org.neo4j.graphdb.Node; | import org.neo4j.graphdb.Node; |
| import org.neo4j.graphdb.index.BatchInserterIndex; | import org.neo4j.graphdb.RelationshipType; |
| import org.neo4j.graphdb.index.BatchInserterIndexProvider; | |
| import org.neo4j.helpers.collection.MapUtil; | import org.neo4j.helpers.collection.MapUtil; |
| import org.neo4j.index.impl.lucene.LuceneBatchInserterIndexProvider; | import org.neo4j.unsafe.batchinsert.BatchInserter; |
| import org.neo4j.kernel.impl.batchinsert.BatchInserter; | import org.neo4j.unsafe.batchinsert.BatchInserters; |
| import org.neo4j.kernel.impl.batchinsert.BatchInserterImpl; | |
| public class Importer { | public class Importer { |
| public static void main(String[] argv) { | public static void main(String[] argv) { |
| BatchInserter inserter = new BatchInserterImpl("target/neo4jdb-batchinsert"); | Map<String, String> config = new HashMap<String, String>(); |
| BatchInserterIndexProvider indexProvider = new LuceneBatchInserterIndexProvider(inserter); | config.put( "neostore.nodestore.db.mapped_memory", "90M" ); |
| BatchInserterIndex labels = indexProvider.nodeIndex("labels", MapUtil.stringMap("type", "exact")); | BatchInserter inserter = BatchInserters.inserter("target/batchinserter-example-config", config ); |
| labels.setCacheCapacity("Label", 100000); | //BatchInserterIndexProvider indexProvider = new LuceneBatchInserterIndexProvider(inserter); |
| //BatchInserterIndex names = indexProvider.nodeIndex("names", MapUtil.stringMap("type", "exact")); | |
| //names.setCacheCapacity("name", 100000); | |
| System.out.println("-------- PostgreSQL " | System.out.println("-------- PostgreSQL " |
| + "JDBC Connection Testing ------------"); | + "JDBC Connection Testing ------------"); |
| try { | try { |
| Class.forName("org.postgresql.Driver"); | Class.forName("org.postgresql.Driver"); |
| } catch (ClassNotFoundException e) { | } catch (ClassNotFoundException e) { |
| System.out.println("Where is your PostgreSQL JDBC Driver? " | System.out.println("Where is your PostgreSQL JDBC Driver? " |
| + "Include in your library path!"); | + "Include in your library path!"); |
| e.printStackTrace(); | e.printStackTrace(); |
| } | } |
| System.out.println("PostgreSQL JDBC Driver Registered!"); | System.out.println("PostgreSQL JDBC Driver Registered!"); |
| Connection conn = null; | Connection conn = null; |
| try { | try { |
| conn = DriverManager.getConnection( | conn = DriverManager.getConnection( |
| "jdbc:postgresql://127.0.0.1:5432/contractDashboard", | "jdbc:postgresql://127.0.0.1:5432/contractDashboard", |
| "postgres", "snmc"); | "postgres", "snmc"); |
| } catch (SQLException e) { | } catch (SQLException e) { |
| System.out.println("Connection Failed! Check output console"); | System.out.println("Connection Failed! Check output console"); |
| e.printStackTrace(); | e.printStackTrace(); |
| } | } |
| if (conn != null) { | if (conn != null) { |
| System.out.println("You made it, take control your database now!"); | System.out.println("You made it, take control your database now!"); |
| } else { | } else { |
| System.out.println("Failed to make connection!"); | System.out.println("Failed to make connection!"); |
| } | } |
| try { | try { |
| // Print all warnings | // Print all warnings |
| for (SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning()) { | for (SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning()) { |
| System.out.println("SQL Warning:"); | System.out.println("SQL Warning:"); |
| System.out.println("State : " + warn.getSQLState()); | System.out.println("State : " + warn.getSQLState()); |
| System.out.println("Message: " + warn.getMessage()); | System.out.println("Message: " + warn.getMessage()); |
| System.out.println("Error : " + warn.getErrorCode()); | System.out.println("Error : " + warn.getErrorCode()); |
| } | } |
| // Get a statement from the connection | // Get a statement from the connection |
| Statement stmt = conn.createStatement(); | Statement stmt = conn.createStatement(); |
| // Execute the query | // Execute the query |
| ResultSet rs = stmt.executeQuery("SELECT contractnotice.\"agencyName\", " | ResultSet rs = stmt.executeQuery("SELECT contractnotice.\"agencyName\", " |
| + " contractnotice.\"supplierABN\",contractnotice.\"supplierName\",sum(value) as sum " | + " (case when \"supplierABN\" != 0 THEN \"supplierABN\"::text ELSE \"supplierName\" END) as supplierID , max(contractnotice.\"supplierName\") as \"supplierName\",sum(value) as sum " |
| + "FROM public.contractnotice GROUP BY contractnotice.\"agencyName\", " | + "FROM public.contractnotice GROUP BY contractnotice.\"agencyName\", " |
| + " contractnotice.\"supplierABN\",contractnotice.\"supplierName\""); | + " (case when \"supplierABN\" != 0 THEN \"supplierABN\"::text ELSE \"supplierName\" END)"); |
| String previousAgency = ""; | |
| GraphDatabaseService gds = inserter.getGraphDbService(); | |
| HashMap<String, Long> supplierIDs = new HashMap<String, Long>(); | HashMap<String, Long> supplierIDs = new HashMap<String, Long>(); |
| HashMap<String, Long> agencyIDs = new HashMap<String, Long>(); | HashMap<String, Long> agencyIDs = new HashMap<String, Long>(); |
| Label agencyLabel = DynamicLabel.label( "Agency" ); | |
| inserter.createDeferredSchemaIndex( agencyLabel ).on( "name" ); | |
| Label supplierLabel = DynamicLabel.label( "Supplier" ); | |
| inserter.createDeferredSchemaIndex( agencyLabel ).on( "name" ); | |
| // Loop through the result set | // Loop through the result set |
| while (rs.next()) { | while (rs.next()) { |
| long supplierID, agencyID; | long supplierID, agencyID; |
| String supplierKey; | String supplierKey; |
| if (agencyIDs.get(rs.getString("agencyName")) == null) { | if (agencyIDs.get(rs.getString("agencyName")) == null) { |
| Node myNode = gds.createNode(); | Map<String, Object> properties = new HashMap<String, Object>(); |
| myNode.setProperty("Label", rs.getString("agencyName")); | properties.put("name", rs.getString("agencyName")); |
| myNode.setProperty("type", "agency"); | properties.put("type", rs.getString("agency")); |
| agencyIDs.put(rs.getString("agencyName"), myNode.getId()); | agencyID = inserter.createNode(properties, agencyLabel); |
| if (myNode.getId() % 100 == 0) { | agencyIDs.put(rs.getString("agencyName"), agencyID); |
| System.out.println("Agency " + myNode.getId()); | if (agencyID % 10 == 0) { |
| System.out.println("Agency " + agencyID); | |
| } | } |
| } | } |
| agencyID = agencyIDs.get(rs.getString("agencyName")); | 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 | // inject some data |
| if (supplierIDs.get(supplierKey) == null) { | if (supplierIDs.get(rs.getString("supplierID")) == null) { |
| Node myNode = gds.createNode(); | Map<String, Object> properties = new HashMap<String, Object>(); |
| myNode.setProperty("Label", rs.getString("supplierName")); | properties.put("name", rs.getString("supplierName")); |
| myNode.setProperty("type", "supplier"); | properties.put("type", rs.getString("supplier")); |
| supplierIDs.put(supplierKey, myNode.getId()); | supplierID = inserter.createNode(properties, supplierLabel); |
| if (myNode.getId() % 1000 == 0) { | supplierIDs.put(rs.getString("supplierID"), supplierID); |
| System.out.println("Supplier " + myNode.getId()); | if (supplierID % 1000 == 0) { |
| System.out.println("Supplier " + supplierID); | |
| } | } |
| } | } |
| supplierID = supplierIDs.get(supplierKey); | supplierID = supplierIDs.get(rs.getString("supplierID")); |
| long rel = inserter.createRelationship(agencyID, supplierID, | // To set properties on the relationship, use a properties map |
| DynamicRelationshipType.withName("KNOWS"), null); | // instead of null as the last parameter. |
| inserter.setRelationshipProperty(rel, "Weight", rs.getDouble("sum")); | Map<String, Object> properties = new HashMap<String, Object>(); |
| properties.put( "value", rs.getDouble("sum")); | |
| inserter.createRelationship(agencyID, supplierID, | |
| DynamicRelationshipType.withName("PAYS"), properties); | |
| inserter.createRelationship(supplierID, agencyID, | |
| DynamicRelationshipType.withName("PAID_BY"), properties); | |
| } | } |
| // Close the result set, statement and the connection | // Close the result set, statement and the connection |
| rs.close(); | rs.close(); |
| stmt.close(); | stmt.close(); |
| conn.close(); | conn.close(); |
| } catch (SQLException se) { | } catch (SQLException se) { |
| System.out.println("SQL Exception:"); | System.out.println("SQL Exception:"); |
| // Loop through the SQL Exceptions | // Loop through the SQL Exceptions |
| while (se != null) { | while (se != null) { |
| System.out.println("State : " + se.getSQLState()); | System.out.println("State : " + se.getSQLState()); |
| System.out.println("Message: " + se.getMessage()); | System.out.println("Message: " + se.getMessage()); |
| System.out.println("Error : " + se.getErrorCode()); | System.out.println("Error : " + se.getErrorCode()); |
| se = se.getNextException(); | se = se.getNextException(); |
| } | } |
| } | } |
| //make the changes visible for reading, use this sparsely, requires IO! | //make the changes visible for reading, use this sparsely, requires IO! |
| labels.flush(); | // names.flush(); |
| // Make sure to shut down the index provider | // Make sure to shut down the index provider |
| indexProvider.shutdown(); | // indexProvider.shutdown(); |
| inserter.shutdown(); | inserter.shutdown(); |
| } | } |
| } | } |