projects / contractdashboard.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
unified | plain
Descriptive title tags
[contractdashboard.git] / admin / neo4jimporter / src / main / java / Importer.java
blob:a/admin/neo4jimporter/src/main/java/Importer.java -> blob:b/admin/neo4jimporter/src/main/java/Importer.java
   
  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<String, String> config = new HashMap<String, String>();
  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<String, Long> supplierIDs = 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
  while (rs.next()) {
  long supplierID, agencyID;
  String supplierKey;
  if (agencyIDs.get(rs.getString("agencyName")) == null) {
  Map<String, Object> properties = new HashMap<String, Object>();
  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<String, Object> properties = new HashMap<String, Object>();
  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<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
  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.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 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.index.impl.lucene.LuceneBatchInserterIndexProvider;  
import org.neo4j.kernel.impl.batchinsert.BatchInserter;  
import org.neo4j.kernel.impl.batchinsert.BatchInserterImpl;  
   
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();  
}  
}  
Unnamed repository; edit this file to name it for gitweb.
GitPHP by Chris Han