|
|
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();
|
}
|
}
|
} |
} |
|
|