在oarcle的sql脚本中字段长度超过4000执行会有异常,而在postgresql中超过4000仍可以正常执行,产品同时支持多个数据库,如oracle和postgresql,在基础数据较多时,只能通过导出基础数据相关表的dmp或backup文件进行升级部署。开发的时候以oracle作为开发库,需要将基础数据弄到postgresql制作backup文件,通过程序直接读oracle表写到postgresql。
步骤:
1、修改oracle和postgresql库的ip、用户名和密码;
2、修改库表列表;
3、运行;
public static void main(String[] args) throws Exception
{
long t0 = System.currentTimeMillis();
Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName("org.postgresql.Driver");
Connection srcCon = null, dstCon = null;
Statement srcStmt = null, dstStmt= null;
PreparedStatement ps = null;
try{
/*创建连接*/
srcCon = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.12:1521:orcl",
"h2do", "h2do");
dstCon = DriverManager.getConnection("jdbc:postgresql://192.168.1.23:5432/h2do",
"postgres", "postgres");
srcStmt = srcCon.createStatement();
dstStmt = dstCon.createStatement();
/*库表列表*/
String[] tables = new String[]{
"h2do",
"e2say"
};
/*逐表处理*/
for(String table : tables)
{
/*1、清理目标表*/
dstStmt.execute("truncate table " + table);
/*2、查询源表字段拼接预处理SQL语句*/
ResultSet rs = srcStmt.executeQuery("select * from " + table);
StringBuilder sql1 = new StringBuilder("insert into " + table + "(");
StringBuilder sql2 = new StringBuilder(") values (");
ResultSetMetaData rsmd = rs.getMetaData();
for(int col = 1; col <= rsmd.getColumnCount(); col++)
{
if(col > 1){
sql1.append(",");
sql2.append(",");
}
sql1.append(rsmd.getColumnName(col).toLowerCase());
sql2.append("?");
}
String sql = sql1.toString() + sql2.toString() + ")";
System.out.println(sql);
/*3、读取源表数据插入目标表,每千条提交一次*/
int rows = 0;
ps = dstCon.prepareStatement(sql);
while(rs.next())
{
for(int col = 1; col <= rsmd.getColumnCount(); col++)
{
if(rsmd.getColumnType(col) == Types.CLOB){
ps.setString(col, rs.getString(col));
}else{
ps.setObject(col, rs.getObject(col));
}
}
ps.addBatch();
rows++;
if(rows%1000 == 0)
{
ps.executeBatch();
dstCon.commit();
ps.clearBatch();
rows = 0;
}
}
if(rows > 0){
ps.executeBatch();
dstCon.commit();
}
ps.close();
System.out.println("耗时:" + (System.currentTimeMillis() - t0) + "毫秒(" + table + ")。");
}
}finally{
try{if(null != srcStmt)srcStmt.close();}catch(Exception e){}
try{if(null != srcCon )srcCon.close(); }catch(Exception e){}
try{if(null != dstStmt)dstStmt.close();}catch(Exception e){}
try{if(null != dstCon )dstCon.close(); }catch(Exception e){}
}
System.out.println("总耗时:" + (System.currentTimeMillis() - t0) + "毫秒。");
}