今天写了个工具类,要求是将一个数据库的表中数据,拷贝到另一个数据库的表中
参考文章如下:https://blog.csdn.net/dei5960/article/details/101647453
mysql如何复制表数据如何避免主键重复:https://blog.csdn.net/mouday/article/details/81281946
下面是我的代码:
package com.sitech.module.utils;
import java.sql.*;
public class SQLite_To_MySQL {
private Connection getIteconn(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://数据库连接ip:端口号/要拷贝数据的数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false","用户名","密码");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
private Connection getMysqlconn(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://数据库连接ip:端口号/要写入数据的数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false","用户名","密码");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
public void deal(String userName) throws SQLException {
// String userName = "sdn_system_menu";
//SQLite数据库
Connection iteconn = getIteconn();
Statement itestmt =iteconn.createStatement();
String c = "select * from " + userName ;
// ResultSet iters = itestmt.executeQuery("select * from sdn_system_menu");
ResultSet iters = itestmt.executeQuery(c);
//结果集获取到的长度
int size = iters.getMetaData().getColumnCount();
//比较懒,拼接insert into 语句
StringBuffer sbf =new StringBuffer();
// sbf.append("insert ignore into sdn_system_menu values (");
// replace into
String b = "insert ignore into " + userName + " values( ";
sbf.append(b);
String link ="";
for (int i = 0; i <size ; i++) {
sbf.append(link).append("?");
link=",";
}
sbf.append(")");
//MySQL数据库
Connection mysqlconn = getMysqlconn();
PreparedStatement mysqlpstmt = mysqlconn.prepareStatement(sbf.toString());
//取出结果集并向MySQL数据库插入数据 ( 使用批处理 )
//完成条数
int count =0;
int num=0;
//取消事务(不写入日志)
mysqlconn.setAutoCommit(false);
long start = System.currentTimeMillis();
while (iters.next()) {
++count;
for (int i=1;i<= size;i++) {
mysqlpstmt.setObject(i, iters.getObject(i));
}
//将预先语句存储起来,这里还没有向数据库插入
mysqlpstmt.addBatch();
//当count 到达 20000条时 向数据库提交
if (count % 20000 ==0 ){
++num;
mysqlpstmt.executeBatch();
System.out.println("第"+num+"次提交,耗时:"+(System.currentTimeMillis()-start)/1000.0+"s");
}
}
//防止有数据未提交
mysqlpstmt.executeBatch();
//提交
mysqlconn.commit();
System.out.println("完成 "+count+" 条数据,耗时:"+(System.currentTimeMillis()-start)/1000.0+"s");
//恢复事务
// mysqlconn.setAutoCommit(true);
//关闭资源
close(mysqlconn,mysqlpstmt,null);
close(iteconn,itestmt,iters);
}
public void close(Connection conn, Statement stmt, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
SQLite_To_MySQL test = new SQLite_To_MySQL();
try {
test.deal("sdn_system_menu");
} catch (SQLException e) {
e.printStackTrace();
}
}
}