【需求】
将Neo4j的查询结果导入Oracle数据库,查询是cypher语句,里面用到with [集合] as,集合的数据来自Oracle数据库表。
【Java代码】
package neo4j;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.neo4j.driver.v1.AuthTokens;
import org.neo4j.driver.v1.Driver;
import org.neo4j.driver.v1.GraphDatabase;
import org.neo4j.driver.v1.Record;
import org.neo4j.driver.v1.Session;
import org.neo4j.driver.v1.StatementResult;
import org.neo4j.driver.v1.Transaction;
public class kg_n2o
{
public static void main(String[] args) throws Exception
{
//记录程序开始时间
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(df.format(new Date())+" kg_n2o.java开始");
//neo4j驱动
Driver driver = GraphDatabase.driver("bolt://neo4j服务器IP:7687",AuthTokens.basic("neo4j登录用户名","neo4j密码"));
Connection con = null;
try (Session session = driver.session())
{
try (Transaction tx = session.beginTransaction())
{
String url = "jdbc:oracle:thin:@Oracle数据库IP:1521:SID";
String user = "数据库用户";
String password = "数据库密码";
Class.forName("oracle.jdbc.driver.OracleDriver");
con = (Connection)DriverManager.getConnection(url,user,password);
con.setAutoCommit(false);
//Cypher语句用到with集合,集合里面是从数据库表查出的id字段值
String sql_id = "select id from 表名";
PreparedStatement pst = (PreparedStatement)con.prepareStatement(sql_id);
ResultSet result_id = pst.executeQuery(sql_id);
//拼接Cypher语句用到的with集合
StringBuffer set_id = new StringBuffer();
if (result_id.next())
{
set_id.append("with ['"+result_id.getString(1)+"'");
}
while (result_id.next())
{
set_id.append(",'"+result_id.getString(1)+"'");
}
set_id.append("] as DETECTLIST\n"); //DETECTLIST是给with语句定义集合取的名称
System.out.println(df.format(new Date())+" 已准备好id");
String sql = set_id +
" match ......\n"+
" where ......\n"+
" and id in DETECTLIST\n"+
" return 字段1,字段2,字段3";
StatementResult result = tx.run(sql);
String sql_insert = "insert into 结果表 (字段1,字段2,字段3) values (?,?,?)";
int i = 0;
pst = (PreparedStatement)con.prepareStatement(sql_insert);
while (result.hasNext())
{
if (i == 0)
{
System.out.println(df.format(new Date())+" 开始提交数据");
}
Record record = result.next();
pst.setString(1, record.get("字段1").toString().replace("\"", "")); //去掉返回结果里面的双引号
pst.setString(2, record.get("字段2").toString().replace("\"", ""));
pst.setString(3, record.get("字段3").toString().replace("\"", ""));
pst.addBatch();
i = i+1;
//每5000条批量提交一次
if (i%5000==0)
{
pst.executeBatch();
con.commit();
pst.clearBatch();
System.out.println(df.format(new Date())+" 提交记录数:"+i);
}
}
pst.executeBatch();
con.commit();
System.out.println(df.format(new Date())+" 提交记录数:"+i);
pst.close();
con.close();
}catch (Exception e) {
e.printStackTrace();
}
finally {
if (!con.isClosed())
{
con.close();
}
System.out.println(df.format(new Date())+" kg_n2o.java结束");
}
}
System.out.println("ok");
}
}
完毕。