--- a/admin/neo4jimporter/src/main/java/Importer.java +++ b/admin/neo4jimporter/src/main/java/Importer.java @@ -1,157 +1,165 @@ + +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.io.File; +import java.io.IOException; +import java.io.InputStream; +import java.io.Writer; +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.GraphDatabaseService; +import org.neo4j.graphdb.Label; +import org.neo4j.graphdb.Node; +import org.neo4j.graphdb.RelationshipType; +import org.neo4j.helpers.collection.MapUtil; +import org.neo4j.unsafe.batchinsert.BatchInserter; +import org.neo4j.unsafe.batchinsert.BatchInserters; + +public class Importer { + + public static void main(String[] argv) { +Map config = new HashMap(); +config.put( "neostore.nodestore.db.mapped_memory", "90M" ); +BatchInserter inserter = BatchInserters.inserter("target/batchinserter-example-config", config ); + //BatchInserterIndexProvider indexProvider = new LuceneBatchInserterIndexProvider(inserter); + //BatchInserterIndex names = indexProvider.nodeIndex("names", MapUtil.stringMap("type", "exact")); + //names.setCacheCapacity("name", 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\", " + + " (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\", " + + " (case when \"supplierABN\" != 0 THEN \"supplierABN\"::text ELSE \"supplierName\" END)"); + HashMap supplierIDs = new HashMap(); + HashMap agencyIDs = new HashMap(); + +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 + while (rs.next()) { + long supplierID, agencyID; + String supplierKey; + if (agencyIDs.get(rs.getString("agencyName")) == null) { + Map properties = new HashMap(); + properties.put("name", rs.getString("agencyName")); + properties.put("type", rs.getString("agency")); + agencyID = inserter.createNode(properties, agencyLabel); + agencyIDs.put(rs.getString("agencyName"), agencyID); + if (agencyID % 10 == 0) { + System.out.println("Agency " + agencyID); + } + } + agencyID = agencyIDs.get(rs.getString("agencyName")); + + + // inject some data + if (supplierIDs.get(rs.getString("supplierID")) == null) { + Map properties = new HashMap(); + properties.put("name", rs.getString("supplierName")); + properties.put("type", rs.getString("supplier")); + supplierID = inserter.createNode(properties, supplierLabel); + supplierIDs.put(rs.getString("supplierID"), supplierID); + if (supplierID % 1000 == 0) { + System.out.println("Supplier " + supplierID); + } + } + supplierID = supplierIDs.get(rs.getString("supplierID")); + + +// To set properties on the relationship, use a properties map +// instead of null as the last parameter. +Map properties = new HashMap(); +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 + 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! +// names.flush(); + +// Make sure to shut down the index provider +// indexProvider.shutdown(); + inserter.shutdown(); + } +} -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 supplierIDs = new HashMap(); - HashMap agencyIDs = new HashMap(); - - // 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(); - } -}