摘要:
用spring task+java jdbc 完成
问题背景
从sqlserver -》mysql
(上一篇)[https://blog.csdn.net/qq_26769591/article/details/90313081]中kettle无法实现视图的copy,不得已转用java手动实现
思路:
1.jdbc从数据库中读取服务器A的表a到文件
2.jdbc从文化读取数据插入到服务器B的表a_copy
3.最后在同一个库内进行库内存储过程同步
驱动版本
sqljdbc41.jar + mysql5.7+mysql-connector-java-5.1.15-bin.jar
核心代码
SynchronizeTask.java
package org.hbnky.task;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* import data from one database to another
*
* @author
*/
@Component
public class SynchronizeTask {
// @Scheduled(cron = "0/10 * * * * ? ") //每10秒执行一次
public void testPing() {
System.out.println("SynchronizeTask 每10秒执行一次");
}
// @Scheduled(cron = "0/10 * * * * ? ") //每10秒执行一次
public void testPing2() {
System.out.println("SynchronizeTask 每10秒执行一次");
}
// 从sqlserver数据库中获取数据
@Scheduled(cron = "0/60 * * * * ? ") //每60秒执行一次
public void datafromSqlserver() {
Connection con = null;// 创建一个数据库连接
PreparedStatement pre = null;// 创建预编译语句对象,一般都是用这个而不用Statement
ResultSet result = null;// 创建一个结果集对象
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载sql server驱动程序
System.out.println("开始尝试连接sqlserver数据库!");
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=ykchr";
String user = "whnkyview";// 用户名,系统默认的账户名
String password = "189cloud.SQL";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("sql servere连接成功!");
String sql = "select login_id,birthdate from vw_user_IO";// 预编译语句,“?”代表参数
pre = con.prepareStatement(sql);// 实例化预编译语句
result = pre.executeQuery();// 执行查询,注意括号中不需要再加参数
/* while (result.next()) {
System.out.println(result.getString("birthdate"));
}*/
//将数据写入到文件中
writecsv(result);
//将数据写入到本地数据库中
try {
if(pre!=null)pre.close();
if(con!=null)con.close();
if (result!=null) {
result.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
datatomysql();
} catch (Exception e) {
System.out.println("sqlserver 连接失败");
e.printStackTrace();
}finally {
}
}
//插入到本地mysql数据库中
public void datatomysql() {
Connection con = null;// 创建一个数据库连接
PreparedStatement pre = null;// 创建预编译语句对象,一般都是用这个而不用Statement
ResultSet result2 = null;// 创建一个结果集对象
Timestamp currentTime = new Timestamp(System.currentTimeMillis());
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(currentTime);
try {
Class.forName("com.mysql.jdbc.Driver");// 加载Oracle驱动程序
System.out.println("开始尝试连接mysql数据库!");
String url = "jdbc:mysql://localhost/rpms";//为连接本地的emp数据库
String user = "root";//mysql数据库的登录名
String password = "root";//数据库密码
con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("mysql连接成功!");
List<String> alllist=readcsv();
int num = 0;
for(String s:alllist)
{
if(s.equals(" ")||s==null||s.equals("")){continue;}
String line[]=s.split(",");
String sql = "insert into user_account_ly(name,create_time) values(?,?)";
pre = con.prepareStatement(sql);// 实例化预编译语句
pre.setString(1,line[0]);
pre.setString(2,line[1]);
pre.execute();
pre.close();
num = num + 1;
}
System.out.println("插入成功第" + (num) + "条数据");
try {
if(pre!=null)pre.close();
if(con!=null)con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}finally {
}
}
public void writecsv(ResultSet result) {
try {
File csv = new File("remote.csv");//CSV文件
BufferedWriter bw = new BufferedWriter(new FileWriter(csv, false));
//新增一行数据
result.next();
while (result.next()) {
bw.newLine();
bw.write(result.getString("login_id"));
bw.write(",");
bw.write(result.getString("birthdate"));
}
bw.close();
}catch (Exception e)
{
e.printStackTrace();
}
}
public List<String> readcsv() {
File csv = new File("remote.csv"); // CSV文件路径
BufferedReader br = null;
try {
br = new BufferedReader(new FileReader(csv));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
String line = "";
String everyLine = "";
List<String> allString = new ArrayList<String>();
try {
while ((line = br.readLine()) != null) //读取到的内容给line变量
{
everyLine = line;
System.out.println(everyLine);
allString.add(everyLine);
}
System.out.println("csv表格中所有行数:" + allString.size());
} catch (IOException e) {
e.printStackTrace();
}
return allString;
}
}
spring.xml
<!--定时任务相关配置-->
<task:annotation-driven scheduler="myScheduler"/> <!-- 定时器开关-->
<task:scheduler id="myScheduler" pool-size="5"/>
<!-- 自动扫描的包名 -->
<context:component-scan base-package="org.hbnky.task" />
bug日志
- @Scheduled Spring定时任务每次执行两次解决方案
https://blog.csdn.net/pearyangyang/article/details/77248020
web.xml
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value></param-value>
<!--注釋掉以防止調用兩次-->
<!--<param-value>classpath*:/spring.xml</param-value>-->
</context-param>
项目源码:
https://github.com/sunny73/WEB_Projects/commit/93fa4cb0fb8bb8dc583a02e6e3982f84943ae555