继上篇博客写的拷贝数据库的表里面的数据,这次又来了新要求,在库里没有这个表的情况下,要拷贝这个表以及这个表里面的数据。
步骤大概是,先去库1里面查数据,然后把库1里面的建表sql 拿到,然后去库2里面取这个表名,有就直接insert 数据,没有这个表名,就先把刚才库1的建表语句在库2执行一遍,然后再insert 到库2的表里数据。
这里有个缺点就是,每次操作数据库都要重新连接一次,我对jdbc的原生操作不多,不太熟悉,后面再优化这里。
代码如下:
package com.sitech.module.utils;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
public class SQLite_To_MySQL {
private Connection getIteconn(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://ip:端口/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false","test1","test1");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
private Connection getMysqlconn(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://ip:端口/test2?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false","test1","test1");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
public void deal(String tableName) throws SQLException {
Connection iteconn = getIteconn();
Statement itestmt =iteconn.createStatement();
String c = "select * from " + tableName ;
// ResultSet iters = itestmt.executeQuery("select * from tableName");
ResultSet iters = itestmt.executeQuery(c);
Connection iteconn2 = getIteconn();
Statement itestmt2 =iteconn2.createStatement();
String d = "SHOW CREATE TABLE " + tableName;
// String d = "select * from sdn_system_user ";
ResultSet iter2 = itestmt2.executeQuery(d);
String createSql = null;
while (iter2.next()){
String table = iter2.getString("Table");
createSql = iter2.getString("Create Table");
}
//结果集获取到的长度
int size = iters.getMetaData().getColumnCount();
//先去检查库里有没有这个表
Connection mysqlconn2 = getMysqlconn();
DatabaseMetaData dbmd = mysqlconn2.getMetaData();
ResultSet rs1 = dbmd.getTables(null, null, tableName, new String[]{"TABLE"});
if(rs1.next()){
//有这个表
String tname=rs1.getString(3);
}else{
//没有这个表,先建表
Connection mysqlconn3 = getMysqlconn();
Statement statement3 =mysqlconn3.createStatement();
System.out.println("createSql" + createSql);
// statement3.execute(createSql);
if(statement3.execute(createSql)){
System.out.println("1" + "true");
}else {
System.out.println("2" + "false");
}
}
//比较懒,拼接insert into 语句
StringBuffer sbf =new StringBuffer();
// sbf.append("insert ignore into sdn_system_menu values ("); replace into
String b = "insert ignore into " + tableName + " 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) {
Map<String,String> map = new HashMap<String,String>();
map.put("tableName","user");
SQLite_To_MySQL test = new SQLite_To_MySQL();
try {
test.deal(map.get("tableName"));
} catch (SQLException e) {
e.printStackTrace();
}
}
}
这里学到的点:1,操作数据库拿到test1库的建表语句,通过SHOW CREATE TABLE tableName
2,DatabaseMetaData类的学习,里面可以获取到很多表的信息,表结构等等
参考连接:https://www.cnblogs.com/dnn179/p/DatabaseMetaData.html
DatabaseMetaData dbmd = mysqlconn2.getMetaData();
ResultSet rs1 = dbmd.getTables(null, null, tableName, new String[]{"TABLE"});