📫 作者简介:「子非我鱼」,专注于研究全栈
🔥 三连支持:欢迎 ❤️关注、👍点赞、👉收藏三连,支持一下博主~
引言
步骤一:导入数据库依赖
<!--MySQL连接包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<!--postgresql-->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.1</version>
</dependency>
我这里一个是把pg库里面的数据迁移到mysql数据库,所以引入的是pg和mysql的依赖,你们根据实际使用情况进行导入
步骤二:编写java代码,DataMigration.java
package com.myqxin.sx.common.utils;
import java.sql.*;
/**
* @author: myqxin
* @Desc:
* @create: 2021-09-18 10:31
**/
public class DataMigration {
/**
* 数据源
*
* @return
*/
private Connection formConn() {
try {
Class.forName("org.postgresql.Driver");
return DriverManager.getConnection("jdbc:postgresql://localhost:5432/dw", "用户名", "密码!");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 目标库
*
* @return
*/
private Connection toDestination() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://localhost:3306/intellect_masterdata?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "用户名", "密码");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
public void transfer() throws SQLException {
//获取源数据库
Connection formConn = formConn();
Statement statement = formConn.createStatement();
ResultSet resultSet = statement.executeQuery("select * from mdata.v_pharmacy_info");
//结果集获取到的长度
int size = resultSet.getMetaData().getColumnCount();
//比较懒,拼接insert into 语句
StringBuffer sbf = new StringBuffer();
sbf.append("inster into sync_master_data_base_info values (");
String link = "";
for (int i = 0; i < size; i++) {
sbf.append(link).append("?");
link = ",";
}
sbf.append(")");
//要存入的数据库
Connection connection = toDestination();
PreparedStatement preparedStatement = connection.prepareStatement(sbf.toString());
//取出结果集并向目标数据库插入数据 ( 使用批处理 )
//完成条数
int count = 0;
int num = 0;
//取消事务(不写入日志)
connection.setAutoCommit(false);
long start = System.currentTimeMillis();
while (resultSet.next()) {
++count;
for (int i = 1; i <= size; i++) {
preparedStatement.setObject(i, resultSet.getObject(i));
}
//将预先语句存储起来,这里还没有向数据库插入
preparedStatement.addBatch();
//当count 到达 1000条时 向数据库提交
if (count % 1000 == 0) {
++num;
preparedStatement.executeBatch();
System.out.println("第" + num + "次提交,耗时:" + (System.currentTimeMillis() - start) / 1000.0 + "s");
}
}
//防止有数据未提交
preparedStatement.executeBatch();
//提交
connection.commit();
System.out.println("完成 " + count + " 条数据,耗时:" + (System.currentTimeMillis() - start) / 1000.0 + "s");
//恢复事务
// mysqlconn.setAutoCommit(true);
//关闭资源
close(connection, preparedStatement, null);
close(formConn, statement, resultSet);
}
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) {
DataMigrationUtil dataMigrationUtil = new DataMigrationUtil();
try {
dataMigrationUtil.transfer();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
步骤三:调用
public static void main(String[] args) {
DataMigration dataMigration = new DataMigration();
try {
dataMigration.transfer();
} catch (SQLException e) {
e.printStackTrace();
}
}
如果是做定时任务的,避免重复的数据插入,可以使用replace into,如果存在primary key 或者 unique相同的记录,则先删除掉,再插入新的记录
sbf.append("replace into test_user values (");
参考来自:https://www.cnblogs.com/oukele/p/9626006.html
注意事项:要保证操作的两张表,字段个数是一致的(类型可以不一致),不然会抛出异常,可以在拼接?的那里做修改,具体看你们实际情况使用