最近有个项目在本地已经测试成功,准备在服务器上线,要把原数据源中的数据导入到目标数据库,于是乎就学习了数据库的数据转移,原数据库的数据命名不太规范,没有主键,字段还有中文名,数据冗余,新建立的数据库把原表拆成了三个表,并且增加了主键和外键。以下就是我导数据的过程。
1.一开始我是通过新建一个maven项目,通过jdbc来链接数据库,并通过sql语句来操作数据库,首先导入依赖:
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
只需导入mysql依赖即可。
并且新建一个main方法主类和ImportData方法类
ImportData方法类代码如下:
/**
* @author 旺旺米雪饼
*/
public class ImportData {
/**
* 数据源
*/
public void ImportDataByJdbc() {
//获取开始时间
long startTime = System.currentTimeMillis();
// 创建一个数据库连接
Connection con = null;
// 创建预编译语句对象,一般都是用这个而不用Statement
PreparedStatement pre = null;
// 创建一个结果集对象
ResultSet result = null;
try {
// 加载mysql驱动程序
Class.forName("com.mysql.jdbc.Driver");
System.out.println("开始尝试连接cms数据库!");
//这是我测试用的源数据库连接
String url = "jdbc:mysql://localhost:3306/data?characterEncoding=utf-8&useSSL=false";
// 用户名,系统默认的账户名
String user = "root";
// 你安装时选设置的密码
String password = "123456";
// 获取连接
con = DriverManager.getConnection(url, user, password);
System.out.println("cms连接成功!");
//user1,mingcheng,lxsj,xxdz,lxr,sfz_hm,lxdh,dzys,frdm,frdh,frmc,lxcz,zcrq
String sql = "select * from cddw_basic where yhjb="+"'"+"直属单位"+"'";
// 实例化预编译语句
pre = con.prepareStatement(sql);
// 执行查询
result = pre.executeQuery();
//数据存入目标库处理
importData(result);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 逐一将上面的几个对象关闭,因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序,最后使用的最先关闭
if (result != null) {
result.close();
}
if (pre != null) {
pre.close();
}
if (con != null) {
con.close();
}
System.out.println("cms数据库连接已关闭!\n");
//获取结束时间
long endTime = System.currentTimeMillis();
//输出程序运行时间
System.out.println("程序运行时间:" + (endTime - startTime) + "ms \n");
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 目标库
*插入表units
* @param
* @return
*/
public void importData(ResultSet result) {
// 创建一个数据库连接
Connection conn = null;
// 创建预编译语句对象,一般都是用这个而不用Statement
PreparedStatement pre = null;
// 创建一个结果集对象
Timestamp currentTime = new Timestamp(System.currentTimeMillis());
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(currentTime);
try {
// 加载mysql驱动程序
Class.forName("com.mysql.jdbc.Driver");
System.out.println("开始尝试连接omp数据库3!");
String url = "jdbc:mysql://101.42.227.226:3306/data_back?characterEncoding=utf-8&useSSL=false";
// 用户名,系统默认的账户名
String user = "lhj";
// 你安装时选设置的密码
String password = "*******";
// 获取连接
conn = DriverManager.getConnection(url, user, password);
System.out.println("omp3连接成功!");
int num = 0;
while (result.next()) {
// 预编译语句
String sql1 = "insert into units(open_status,supper_unit_id,unit_name,unit_address,unit_number,admin_name,admin_card,admin_phone,unit_email,open_time,jur_code,jur_phone,jur_name,user_id,fax_number,admin_tel)" +
" values(1,62,"+"'"+
result.getString("mingcheng") +"','" +
result.getString("xxdz")+"','" +
result.getString("申报单位代码")+"','" +
result.getString("lxr")+"','" +
result.getString("sfz_hm")+"','" +
result.getString("lxsj")+"','" +
result.getString("dzys")+"','" +
result.getString("zcrq")+"','" +
result.getString("frdm")+"','" +
result.getString("frdh")+"','" +
result.getString("frmc")+"','" +
result.getString("user1")+"','" +
result.getString("lxcz")+"','" +
result.getString("lxdh")
+"')";
// 实例化预编译语句
pre = conn.prepareStatement(sql1);
pre.execute();
pre.close();
num = num + 1;
}
System.out.println("成功插入(" + num + ")条数据");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 逐一将上面的几个对象关闭,因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序,最后使用的最先关闭
if (result != null) {
result.close();
}
if (pre != null) {
pre.close();
}
if (conn != null) {
conn.close();
}
System.out.println("omp数据库3连接已关闭!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
并且在主类中调用方法即可:
/**
* @author 旺旺米雪饼
*/
public class Main {
public static void main(String[] args) {
ImportData importData=new ImportData();
importData.ImportDataByJdbc();
}
}
运行结果如下图所示:
不过这种方法只能实现从一个表到另一个表的单表操作,如何实现多表操作查询另一个表的主键并且当作外键呢,还是用springboot+mybatis配置多数据源比较方便。
2.springboot配置多数据源
首先我们导入依赖:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
<version>2.3.7.RELEASE</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<!--mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
配置application.properties文件
#DB1 Configuration:
spring.datasource.test1.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.test1.jdbc-url=jdbc:mysql://localhost:3306/data?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.test1.username=root
spring.datasource.test1.password=123456
#DB2 Configuration:
spring.datasource.test2.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.test2.jdbc-url=jdbc:mysql://101.42.227.226:3306/data_back?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.test2.username=lhj
spring.datasource.test2.password=******
文件结构:
首先,Config文件:
/**
* @author 旺旺米雪饼
*/
@Configuration
@MapperScan(basePackages = {"com.example.data_change.mapper1"}, sqlSessionFactoryRef = "test1SqlSessionFactory")
public class ConfigDb01 {
@Bean(name = "test1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test1")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test1SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
ConfigDb02.java文件同理:
/**
* @author 旺旺米雪饼
*/
@Configuration
@MapperScan(basePackages = {"com.example.data_change.mapper2"}, sqlSessionFactoryRef = "test2SqlSessionFactory")
public class ConfigDb02 {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
然后就是entity实体类,下面是其中一个实体类:
/**
* @author 旺旺米雪饼
*/
@Data
public class Cddw_Basic {
private String id;
private String 申报单位代码;
private String 主管单位;
private String 主管单位代码;
private String 原主管单位;
private String 原主管单位代码;
private String 原申报单位;
private String 原申报单位代码;
private String 变更时间;
private String 撤回时间;
private String mingcheng;
private String pass;
private String user1;
private String sfkt;
private String yhjb;
private String wenti;
private String daan;
private String wangye;
private String frdm;
private String frmc;
private String frdh;
private String lxr;
private String lxdh;
private String lxsj;
private String lxcz;
private String dzys;
private String zgcs;
private String szzq;
private String yzbm;
private String xxdz;
private String lsgx;
private String sshy;
private String dwfl;
private String jjlx;
private String zgbm;
private String jdms;
private String zcrq;
private String zwzc;
private String sfz_hm;
private String zw;
private String khmc;
private String khyh;
private String khzh;
}
然后就是我自己用注解编写的sql语句了,并没有再配置.xml文件,在mapper文件夹下,主要是完成数据迁移:
/**
* @author 旺旺米雪饼
*/
@Mapper
public interface TestMapper1 {
/**
* 查询数据源所有信息
* @return
*/
@Select("select * from cddw_basic where yhjb='申报单位'")
List<Cddw_Basic> selectAll();
}
/**
* @author 旺旺米雪饼
*/
@Mapper
public interface TestMapper2 {
/**
* 插入units表
* @param units
* @return
*/
@Insert({"insert into units(open_status,supper_unit_id,unit_name,unit_address,unit_number,admin_name,admin_card,admin_phone,unit_email,open_time,jur_code,jur_phone,jur_name,user_id,fax_number,admin_tel) " +
"values (#{open_status},#{supper_unit_id},#{unit_name},#{unit_address},#{unit_number},#{admin_name},#{admin_card},#{admin_phone},#{unit_email},#{open_time,jdbcType=TIMESTAMP},#{jur_code},#{jur_phone},#{jur_name},#{user_id},#{fax_number},#{admin_tel})"})
int insertUnits(Units units);
@Select("select admin_id from admins where admin_name=#{admin_name}")
int findadmin_id(@Param("admin_name") String admin_name);
}
DataChangeApplication文件如下所示:
/**
* @author 旺旺米雪饼
*/
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class DataChangeApplication {
public static void main(String[] args) {
SpringApplication.run(DataChangeApplication.class, args);
}
}
然后编写测试用例:
@Slf4j
@SpringBootTest
class DataChangeApplicationTests {
int j = 0;//状态字段
@Autowired
private TestMapper1 testMapper1;
@Autowired
private TestMapper2 testMapper2;
@Test
void contextLoads() throws ParseException {
for (int i = 0; i < testMapper1.selectAll().size(); i++) {
if (testMapper1.selectAll().get(i).getSfkt().equals("通过")) {
j = 1;
} else if (testMapper1.selectAll().get(i).getSfkt().equals("待审核")) {
j = 0;
} else if (testMapper1.selectAll().get(i).getSfkt().equals("祝彦杰重名停用")) {
j = 3;
} else
j = 2;
int k = testMapper2.findadmin_id(testMapper1.selectAll().get(i).get主管单位());
Units units = new Units();
units.setUnit_name(testMapper1.selectAll().get(i).getMingcheng());
units.setUnit_address(testMapper1.selectAll().get(i).getXxdz());
units.setUnit_number(testMapper1.selectAll().get(i).get申报单位代码());
units.setSupper_unit_id(k);
units.setAdmin_name(testMapper1.selectAll().get(i).getLxr());
units.setAdmin_card(testMapper1.selectAll().get(i).getSfz_hm());
units.setAdmin_phone(testMapper1.selectAll().get(i).getLxsj());
units.setUnit_email(testMapper1.selectAll().get(i).getDzys());
units.setOpen_status(j);
//将String类型转换成Date类型
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(testMapper1.selectAll().get(i).getZcrq());
units.setOpen_time(date);
units.setJur_code(testMapper1.selectAll().get(i).getFrdm());
units.setJur_phone(testMapper1.selectAll().get(i).getFrdh());
units.setJur_name(testMapper1.selectAll().get(i).getFrmc());
units.setUser_id(testMapper1.selectAll().get(i).getUser1());
units.setFax_number(testMapper1.selectAll().get(i).getLxcz());
units.setAdmin_tel(testMapper1.selectAll().get(i).getLxdh());
testMapper2.insertUnits(units);
log.info("插入第"+(i+1)+"条记录");
}
}
}
得到结果如下:
成功插入。