现在需要将内网数据库的数据及表同步到外网中，但是连接内网需要jar包才能穿透连接到，经测试，jar包对获取建表语句进行了限制（注释的代码），所以需要内外网两边把表建好，直接同步数据。
逻辑：
获取内网中的数据，查询出来insert到外网数据库中。代码如下：
package com.merit.common.controller;
import com.merit.common.service.BakDatebaseService;
import com.merit.common.util.JdbcUtil;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.scheduling.config.ScheduledTaskRegistrar;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Properties;
@Configuration
@EnableScheduling //开启定时任务
public class SaticScheduleTask {
@Scheduled(cron = "${auto.cron}")
private void configureAutoTasks() {
System.err.println("定时查询是否有需要更新的表，当前执行动态定时任务时间: " + LocalDateTime.now());
//1.新建属性集对象
Properties properties = new Properties();
//2通过反射，新建字符输入流，读取db.properties文件
InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
//3.将输入流中读取到的属性，加载到properties属性集对象中
try {
properties.load(input);
} catch (IOException e) {
e.printStackTrace();
}
//4.根据键，获取properties中对应的值
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("username");
String password = properties.getProperty("password");
int prosCons = Integer.parseInt(properties.getProperty("pros_cons"));
new BakDatebaseService(driver,url, "", user, password, "",prosCons).startBakUpdate();
}
}
package com.merit.common.service;
import com.merit.common.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class BakDatebaseService {
private String DRIVER = "sgcc.nds.jdbc.driver.NdsDriver";
//private String DRIVER = "com.mysql.jdbc.Driver";
private String URL = null;
private String USERNAME = null;
private String PASSWORD = null;
//外网中：为2，反向，从rds到外网
private Integer prosCons = 0;
private Connection conn = null;
private String SQL = "SELECT * FROM ";// 数据库操作
private final static Logger log = LoggerFactory.getLogger(BakDatebaseService.class);
public BakDatebaseService(String driver,String ip, String database, String userName, String password, String bakFilePath,int prosCons) {
try {
//this.DRIVER = driver;
Class.forName(DRIVER);
//this.URL = String.format("jdbc:mysql://%s:63336/%s?useUnicode=true&characterEncoding=utf8", ip, database);
this.URL = ip;
this.USERNAME = userName;
this.PASSWORD = password;
this.prosCons = prosCons;
SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-ddHH时mm分ss秒");
String datetime = tempDate.format(new java.util.Date());
//自动加上时间戳
datetime = datetime + "_数据库名称：" + database ;
} catch (ClassNotFoundException e) {
log.error("can not load jdbc driver:"+e.getMessage());
}
}
private Connection getConnection() {
try {
if (null == conn) {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
} catch (SQLException e) {
log.error("get connection failure:"+e.getMessage());
}
return conn;
}
private void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
log.error("close connection failure:"+e.getMessage());
}
}
}
private List getTableNames() {
List tableNames = new ArrayList();
Connection conn = getConnection();
String sql = "select table_name as tableName from table_bak where flag = 2 and is_run = 0 and pros_cons ="+ prosCons;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
tableNames.add(rs.getString(1));
}
} catch (Exception e) {
log.error("getTableNames failure:"+e.getMessage());
} finally {
try {
if (null != rs) {
rs.close();
}
closeConnection(conn);
} catch (SQLException e) {
log.error("close ResultSet failure:"+e.getMessage());
}
}
return tableNames;
}
private List getUpdateTableNames() {
List tableNames = new ArrayList();
Connection conn = getConnection();
String sql = "select table_name as tableName from table_bak where is_run = 1 and flag = 2 and pros_cons = " + prosCons;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
tableNames.add(rs.getString(1));
}
} catch (Exception e) {
log.error("getTableNames failure:"+e.getMessage());
} finally {
try {
if (null != rs) {
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
closeConnection(conn);
} catch (SQLException e) {
log.error("close ResultSet failure:"+e.getMessage());
}
}
return tableNames;
}
private List getColumnNames(String tableName) {
List columnNames = new ArrayList();
// 与数据库的连接
Connection conn = getConnection();
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
try {
pStemt = conn.prepareStatement(tableSql);
// 结果集元数据
ResultSetmetaData rsmd = pStemt.getmetaData();
// 表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnNames.add(rsmd.getColumnName(i + 1));
}
} catch (SQLException e) {
log.error("getColumnNames failure:"+e.getMessage());
} finally {
if (pStemt != null) {
try {
pStemt.close();
} catch (SQLException e) {
log.error("getColumnNames close pstem and connection failure:"+e.getMessage());
}
}
}
return columnNames;
}
private List getColumnTypes(String tableName) {
List columnTypes = new ArrayList();
// 与数据库的连接
Connection conn = getConnection();
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
try {
pStemt = conn.prepareStatement(tableSql);
// 结果集元数据
ResultSetmetaData rsmd = pStemt.getmetaData();
// 表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnTypes.add(rsmd.getColumnTypeName(i + 1));
}
} catch (SQLException e) {
log.error("getColumnTypes failure:"+e.getMessage());
} finally {
if (pStemt != null) {
try {
pStemt.close();
} catch (SQLException e) {
log.error("getColumnTypes close pstem and connection failure:"+e.getMessage());
}
}
}
return columnTypes;
}
private String generateCreateTableSql(String tableName) {
//String sql = String.format("SHOW CREATE TABLE `%s`", tableName);
String sql = "show create table "+ "`" + tableName + "`";
System.out.println("查询创建表sql语句：" + sql);
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
//conn = getConnection();
System.out.println("conn-----"+conn);
pstmt = conn.prepareStatement("show create table "+ "`" + tableName + "`");
System.out.println("pstmt-----:"+pstmt);
rs = pstmt.executeQuery();
System.out.println("---rs---"+rs);
while (rs.next()) {
// 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
System.out.println("rs.getString(2)---"+rs.getString(2));
return rs.getString(2);
}
} catch (Exception e) {
log.error("generateCreateTableSql建表语句生成异常:"+e.getMessage(),e);
}finally {
try {
if (null != rs){
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
log.error("generateCreateTableSql方法关闭流异常:"+e.getMessage(),e);
}
}
return null;
}
private List getColumnComments(String tableName) {
// 与数据库的连接
Connection conn = getConnection();
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
List columnComments = new ArrayList();// 列名注释集合
ResultSet rs = null;
try {
pStemt = conn.prepareStatement(tableSql);
rs = pStemt.executeQuery("show full columns from " + tableName);
while (rs.next()) {
columnComments.add(rs.getString("Comment"));
}
} catch (SQLException e) {
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
log.error("getColumnComments close ResultSet and connection failure:"+e.getMessage());
}
}
}
return columnComments;
}
private String bakTableData(String tableName) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet iters = null;
//
Connection mysqlconn = null;
PreparedStatement mysqlpstmt = null;
try {
//List strings = getTableDatas(tableName);
//for (Object[] string : strings) {
// for (int i = 0; i < string.length; i++) {
// System.out.print(string[i]+",");
// }
// System.out.println("----------");
//}
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
pstmt = conn.prepareStatement("select * from " + tableName);
iters = pstmt.executeQuery();
//结果集获取到的长度
int size = iters.getmetaData().getColumnCount();
//拼接insert into 语句
StringBuffer sbf =new StringBuffer();
String isExistsTable = "truncate table "+tableName+ "";
if(0 == deleteTable(isExistsTable)){
System.err.println("******删除表语句:"+isExistsTable);
};
sbf.append("insert into "+tableName+" values (");
String link ="";
for (int i = 0; i getTableDatas(String tableName) {
List list = new ArrayList<>();
String sql = "select * from " + tableName;
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
System.out.println("11111---"+rs);
//结果集获取到的长度
int size = rs.getmetaData().getColumnCount();
while (rs.next()) {
Object[] columnValue = new Object[size];// 列值。
for (int i = 0; i < size; i++) {
columnValue[i] = rs.getObject(i + 1);
//return rs.getObject(i);
}
list.add(columnValue);
}
return list;
} catch (Exception e) {
log.error("getTableDatas异常:"+e.getMessage(),e);
}finally {
try {
if (null != pstmt) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
log.error("getTableDatas关闭流异常:"+e.getMessage(),e);
}
}
return list;
}
private void updateTableStatus(String tableName) {
String sql = "update table_bak set is_run = 0,cron_time = now() where table_name ='"+tableName+"'";
PreparedStatement pstmt = null;
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
//conn=dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
log.info(tableName+"表已更新。");
} catch (ClassNotFoundException | SQLException e) {
log.error("更新数据时出错："+e.getMessage());
}finally {
try {
if (pstmt != null) {
pstmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
log.error("关闭流出错:"+e.getMessage());
}
}
}
private String getRowValues(ResultSet rs, int size, List columnTypeList) {
try {
String rowValues = null;
for (int i = 1; i <= size; i++) {
String columnValue = null;
// 获取字段值
columnValue = getValue(rs, i, columnTypeList.get(i - 1));
// 如果是空值不添加单引号
if (null != columnValue) {
columnValue = "'" + columnValue + "'";
}
// 拼接字段值
if (null == rowValues) {
rowValues = columnValue;
} else {
rowValues = rowValues + "," + columnValue;
}
}
return rowValues;
} catch (Exception e) {
log.error("获取表数据一行的所有值异常:"+e.getMessage());
return null;
}
}
private String getValue(ResultSet resultSet, Integer index, String columnType) {
try {
if ("int".equals(columnType) || "INT".equals(columnType)) {
// 整数
Object intValue = resultSet.getObject(index);
if (null == intValue) {
return null;
}
return intValue + "";
} else if ("bigint".equals(columnType) || "BIGINT".equals(columnType)) {
// 长整形
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("smallint".equals(columnType) || "SMALLINT".equals(columnType)) {
// 整数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("tinyint".equals(columnType) || "TINYINT".equals(columnType)) {
// 整数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("mediumint".equals(columnType) || "MEDIUMINT".equals(columnType)) {
// 长整形
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("integer".equals(columnType) || "INTEGER".equals(columnType)) {
// 整数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("float".equals(columnType) || "FLOAT".equals(columnType)) {
// 浮点数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("double".equals(columnType) || "DOUBLE".equals(columnType)) {
// 浮点数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("decimal".equals(columnType) || "DECIMAL".equals(columnType)) {
// 浮点数-金额类型
BigDecimal value = resultSet.getBigDecimal(index);
if (null == value) {
return null;
}
return value.toString();
} else if ("char".equals(columnType) || "CHAR".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("varchar".equals(columnType) || "VARCHAR".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("tinytext".equals(columnType) || "TINYTEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("text".equals(columnType) || "TEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("mediumtext".equals(columnType) || "MEDIUMTEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("longtext".equals(columnType) || "LONGTEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("year".equals(columnType) || "YEAR".equals(columnType)) {
// 时间类型：范围 1901/2155 格式 YYYY
String year = resultSet.getString(index);
if (null == year) {
return null;
}
// 只需要年的字符即可，
return year.substring(0, 4);
} else if ("date".equals(columnType) || "DATE".equals(columnType)) {
// 时间类型：范围 '1000-01-01'--'9999-12-31' 格式 YYYY-MM-DD
return resultSet.getString(index);
} else if ("time".equals(columnType) || "TIME".equals(columnType)) {
// 时间类型：范围 '-838:59:59'到'838:59:59' 格式 HH:MM:SS
return resultSet.getString(index);
} else if ("datetime".equals(columnType) || "DATETIME".equals(columnType)) {
// 时间类型：范围 '1000-01-01 00:00:00'--'9999-12-31 23:59:59' 格式 YYYY-MM-DD HH:MM:SS
return resultSet.getString(index);
} else if ("timestamp".equals(columnType) || "TIMESTAMP".equals(columnType)) {
// 时间类型：范围 1970-01-01 00:00:00/2037 年某时 格式 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
return resultSet.getString(index);
} else {
return null;
}
} catch (Exception e) {
log.error("获取数据库类型值异常:"+e.getMessage());
return null;
}
}
public void startBak() {
try {
List tableNames = getTableNames();
System.out.println("tableNames:" + tableNames);
for (String tableName : tableNames) {
bakTableData(tableName);
// System.out.println(generateCreateTableSql(tableName));
// System.out.println("ColumnNames:" + getColumnNames(tableName));
// System.out.println("ColumnTypes:" + getColumnTypes(tableName));
// System.out.println("ColumnComments:" + getColumnComments(tableName));
}
// 统一关闭连接
closeConnection(conn);
} catch (Exception e) {
log.error(e.getMessage());
}
}
public void startBakUpdate() {
try {
List tableNames = getUpdateTableNames();
System.out.println("tableNames:" + tableNames);
for (String tableName : tableNames) {
bakTableData(tableName);
// System.out.println(generateCreateTableSql(tableName));
// System.out.println("ColumnNames:" + getColumnNames(tableName));
// System.out.println("ColumnTypes:" + getColumnTypes(tableName));
// System.out.println("ColumnComments:" + getColumnComments(tableName));
}
// 统一关闭连接
closeConnection(conn);
} catch (Exception e) {
log.error(e.getMessage());
}
}
private int deleteTable(String isExistsTable){
Statement stmt;
int i = 0;
try {
stmt = getConnIvr().createStatement();
//stmt = JdbcUtil.getConnection().createStatement();
i = stmt.executeUpdate(isExistsTable);
} catch (SQLException e) {
log.error("删除表失败！"+e.getMessage());
}
return i;
}
private boolean copyTable(String createTableSql) {
Statement stmt ;
try {
stmt = getConnIvr().createStatement();
//stmt = JdbcUtil.getConnection().createStatement();
if(0 == stmt.executeLargeUpdate(createTableSql)) {
System.out.println("成功创建表！");
}else{
System.out.println("创建表失败！");
}
} catch (SQLException e) {
log.error("创建表出错！"+e.getMessage(),e);
return false;
}
return true;
}
private void copyTableData(String insertSql) {
//DruidDataSource dataSource= new DruidDataSource();
PreparedStatement pstmt = null;
Connection conn = null;
//String DRIVER="com.mysql.jdbc.Driver";
//String DB_URL="jdbc:mysql://localhost:3306/ozone?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
//String DB_USERNAME="root";
//String DB_PASSWORD="123456";
//dataSource.setUrl(DB_URL);
//dataSource.setUsername(DB_USERNAME);
//dataSource.setPassword(DB_PASSWORD);
//dataSource.setDriverClassName(DRIVER);
//dataSource.setInitialSize(5);
//dataSource.setMaxActive(10);
try {
//Class.forName(DRIVER);
//conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
//conn=dataSource.getConnection();
conn = JdbcUtil.getConnection();
pstmt = conn.prepareStatement(insertSql);
pstmt.executeUpdate();
} catch (SQLException e) {
log.error("复制数据时出错："+e.getMessage());
}finally {
try {
if (pstmt != null) {
pstmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
log.error("关闭流出错:"+e.getMessage());
}
}
}
private static synchronized Connection getConnIvr(){
Connection CONN = null;
//String DRIVER="com.mysql.jdbc.Driver";
//String DB_URL="jdbc:mysql://localhost:3306/ozone?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
//String DB_USERNAME="root";
//String DB_PASSWORD="123456";
//1.新建属性集对象
Properties properties = new Properties();
//2通过反射，新建字符输入流，读取db.properties文件
InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
//3.将输入流中读取到的属性，加载到properties属性集对象中
try {
properties.load(input);
} catch (IOException e) {
e.printStackTrace();
}
//4.根据键，获取properties中对应的值
String driver = properties.getProperty("out.driver");
String url = properties.getProperty("out.url");
String user = properties.getProperty("out.username");
String password = properties.getProperty("out.password");
try {
Class.forName(driver);
CONN = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException | SQLException e) {
log.error("getConnIvr连接数据源出错"+e.getMessage());
}
return CONN;
}
public static void main(String[] args) {
//new BakDatebaseService("123.139.156.125", "ozone", "root", "merit1q98.c0m", "f:\bak.sql").startBak();
//1.新建属性集对象
Properties properties = new Properties();
//2通过反射，新建字符输入流，读取db.properties文件
InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
//3.将输入流中读取到的属性，加载到properties属性集对象中
try {
properties.load(input);
} catch (IOException e) {
e.printStackTrace();
}
//4.根据键，获取properties中对应的值
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("username");
String password = properties.getProperty("password");
int prosCons = Integer.parseInt(properties.getProperty("pros_cons"));
new BakDatebaseService(driver,url, null, user, password, null,prosCons).startBak();
}
}
package com.merit.common.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
//获取到配置文件中的数据库信息
public class JdbcUtil {
//私有变量
private static String driver;
private static String url;
private static String user;
private static String password;
//静态块
static{
try{
//1.新建属性集对象
Properties properties = new Properties();
//2通过反射，新建字符输入流，读取db.properties文件
InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("application.properties");
//3.将输入流中读取到的属性，加载到properties属性集对象中
properties.load(input);
//4.根据键，获取properties中对应的值
driver = properties.getProperty("out.driver");
url = properties.getProperty("out.url");
user = properties.getProperty("out.username");
password = properties.getProperty("out.password");
}catch(Exception e){
System.err.println("读取配置文件错误："+e.getMessage());
}
}
//返回数据库连接
public static Connection getConnection(){
try{
//注册数据库的驱动
Class.forName(driver);
//获取数据库连接（里面内容依次是：主机名和端口、用户名、密码）
Connection connection = DriverManager.getConnection(url,user,password);
//返回数据库连接
return connection;
}catch (Exception e){
System.err.println("获取内网数据库连接出错："+e.getMessage());
}
return null;
}
}
参考：https://www.cnblogs.com/oukele/p/9626006.html
package com.merit.common.controller;
import java.sql.*;
public class CopyMysql {
private Connection getIteconn() {
try {
Class.forName("org.sqlite.JDBC");
return DriverManager.getConnection("jdbc:sqlite:E:\MyDB\lagou.db");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
private Connection getMysqlconn() {
try {
Class.forName("org.mariadb.jdbc.Driver");
return DriverManager.getConnection("jdbc:mariadb://localhost:3306/test", "oukele", "oukele");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
public void deal() throws SQLException {
//SQLite数据库
Connection iteconn = getIteconn();
Statement itestmt = iteconn.createStatement();
ResultSet iters = itestmt.executeQuery("select * from lagou_position");
//结果集获取到的长度
int size = iters.getmetaData().getColumnCount();
//比较懒，拼接insert into 语句
StringBuffer sbf = new StringBuffer();
sbf.append("insert into lagou values (");
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();
} catch (SQLException e) {
e.printStackTrace();
}
}
参考连接：https://blog.csdn.net/qq_27184497/article/details/82454997
package com.mysql.bak;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabasemetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetmetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import com.utils.FileUtils;
public class BakDatebase {
private String DRIVER = "com.mysql.jdbc.Driver";
private String URL = null; // "jdbc:mysql://182.xxx.xxx.xxx:3306/xd_love_dev?useUnicode=true&characterEncoding=utf8";
private String USERNAME = null;// "root";
private String PASSWORD = null;//"woaini";
// 备份的文件地址
private String filePath;
private Connection conn = null;
private String SQL = "SELECt * FROM ";// 数据库操作
public BakDatebase(String ip, String database, String userName, String password, String bakFilePath) {
try {
Class.forName(this.DRIVER);
this.URL = String.format("jdbc:mysql://%s:3306/%s?useUnicode=true&characterEncoding=utf8", ip, database);
this.USERNAME = userName;
this.PASSWORD = password;
SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-ddHH时mm分ss秒");
String datetime = tempDate.format(new java.util.Date());
//自动加上时间戳
datetime = datetime + "_数据库名称：" + database ;
if(bakFilePath.indexOf(".") != -1) {
bakFilePath = bakFilePath.replace(".", datetime+".");
} else {
bakFilePath = datetime + ".sql";
}
this.filePath = bakFilePath;
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.err.println("can not load jdbc driver");
}
}
private Connection getConnection() {
try {
if (null == conn) {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
} catch (SQLException e) {
e.printStackTrace();
System.err.println("get connection failure");
}
return conn;
}
private void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
System.err.println("close connection failure");
}
}
}
private List getTableNames() {
List tableNames = new ArrayList();
Connection conn = getConnection();
ResultSet rs = null;
try {
// 获取数据库的元数据
DatabasemetaData db = conn.getmetaData();
// 从元数据中获取到所有的表名
rs = db.getTables(null, null, null, new String[] { "TABLE" });
while (rs.next()) {
tableNames.add(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
System.err.println("getTableNames failure");
} finally {
try {
if (null != rs) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
System.err.println("close ResultSet failure");
}
}
return tableNames;
}
private List getColumnNames(String tableName) {
List columnNames = new ArrayList();
// 与数据库的连接
Connection conn = getConnection();
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
try {
pStemt = conn.prepareStatement(tableSql);
// 结果集元数据
ResultSetmetaData rsmd = pStemt.getmetaData();
// 表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnNames.add(rsmd.getColumnName(i + 1));
}
} catch (SQLException e) {
System.err.println("getColumnNames failure");
e.printStackTrace();
} finally {
if (pStemt != null) {
try {
pStemt.close();
} catch (SQLException e) {
e.printStackTrace();
System.err.println("getColumnNames close pstem and connection failure");
}
}
}
return columnNames;
}
private List getColumnTypes(String tableName) {
List columnTypes = new ArrayList();
// 与数据库的连接
Connection conn = getConnection();
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
try {
pStemt = conn.prepareStatement(tableSql);
// 结果集元数据
ResultSetmetaData rsmd = pStemt.getmetaData();
// 表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnTypes.add(rsmd.getColumnTypeName(i + 1));
}
} catch (SQLException e) {
e.printStackTrace();
System.err.println("getColumnTypes failure");
} finally {
if (pStemt != null) {
try {
pStemt.close();
} catch (SQLException e) {
e.printStackTrace();
System.err.println("getColumnTypes close pstem and connection failure");
}
}
}
return columnTypes;
}
private String generateCreateTableSql(String tableName) {
String sql = String.format("SHOW CREATE TABLE %s", tableName);
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = (PreparedStatement) conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
// 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
return rs.getString(2);
}
} catch (Exception e) {
e.printStackTrace();
try {
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e2) {
e.printStackTrace();
System.err.println("关闭流异常");
}
}
return null;
}
private List getColumnComments(String tableName) {
// 与数据库的连接
Connection conn = getConnection();
PreparedStatement pStemt = null;
String tableSql = SQL + tableName;
List columnComments = new ArrayList();// 列名注释集合
ResultSet rs = null;
try {
pStemt = conn.prepareStatement(tableSql);
rs = pStemt.executeQuery("show full columns from " + tableName);
while (rs.next()) {
columnComments.add(rs.getString("Comment"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
System.err.println("getColumnComments close ResultSet and connection failure");
}
}
}
return columnComments;
}
private String bakTableData(String tableName) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 备份建表语句
String createTableSql = generateCreateTableSql(tableName);
createTableSql = String.format(
"nnnn%sn",
tableName, createTableSql);
FileUtils.writeFileContent(filePath, createTableSql);
// 获取字段类型
List columnTypes = getColumnTypes(tableName);
// 获取所有 字段
List columnNames = getColumnNames(tableName);
String columnArrayStr = null;
for (String column : columnNames) {
if (null == columnArrayStr) {
columnArrayStr = "`" + column + "`";
} else {
columnArrayStr = columnArrayStr + "," + "`" + column + "`";
}
}
String sql = String.format("select %s from %s", columnArrayStr, tableName);
conn = getConnection();
pstmt = (PreparedStatement) conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String rowValues = getRowValues(rs, columnNames.size(), columnTypes);
// 返回建表语句语句，查询结果的第二列是建表语句，第一列是表名
String insertSql = String.format("insert into %s (%s) values(%s);", tableName, columnArrayStr,
rowValues);
System.out.println(insertSql);
insertSql = insertSql.replaceAll("n", "
");
insertSql = insertSql + "n";
FileUtils.writeFileContent(filePath, insertSql);
}
} catch (Exception e) {
e.printStackTrace();
try {
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e2) {
e.printStackTrace();
System.err.println("关闭流异常");
}
}
return null;
}
private String getRowValues(ResultSet rs, int size, List columnTypeList) {
try {
String rowValues = null;
for (int i = 1; i <= size; i++) {
String columnValue = null;
// 获取字段值
columnValue = getValue(rs, i, columnTypeList.get(i - 1));
// 如果是空值不添加单引号
if (null != columnValue) {
columnValue = "'" + columnValue + "'";
}
// 拼接字段值
if (null == rowValues) {
rowValues = columnValue;
} else {
rowValues = rowValues + "," + columnValue;
}
}
return rowValues;
} catch (Exception e) {
e.printStackTrace();
System.out.println("获取表数据一行的所有值异常");
return null;
}
}
private String getValue(ResultSet resultSet, Integer index, String columnType) {
try {
if ("int".equals(columnType) || "INT".equals(columnType)) {
// 整数
Object intValue = resultSet.getObject(index);
if (null == intValue) {
return null;
}
return intValue + "";
} else if ("bigint".equals(columnType) || "BIGINT".equals(columnType)) {
// 长整形
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("smallint".equals(columnType) || "SMALLINT".equals(columnType)) {
// 整数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("tinyint".equals(columnType) || "TINYINT".equals(columnType)) {
// 整数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("mediumint".equals(columnType) || "MEDIUMINT".equals(columnType)) {
// 长整形
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("integer".equals(columnType) || "INTEGER".equals(columnType)) {
// 整数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("float".equals(columnType) || "FLOAT".equals(columnType)) {
// 浮点数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("double".equals(columnType) || "DOUBLE".equals(columnType)) {
// 浮点数
Object value = resultSet.getObject(index);
if (null == value) {
return null;
}
return value + "";
} else if ("decimal".equals(columnType) || "DECIMAL".equals(columnType)) {
// 浮点数-金额类型
BigDecimal value = resultSet.getBigDecimal(index);
if (null == value) {
return null;
}
return value.toString();
} else if ("char".equals(columnType) || "CHAR".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("varchar".equals(columnType) || "VARCHAR".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("tinytext".equals(columnType) || "TINYTEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("text".equals(columnType) || "TEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("mediumtext".equals(columnType) || "MEDIUMTEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("longtext".equals(columnType) || "LONGTEXT".equals(columnType)) {
// 字符串类型
String value = resultSet.getString(index);
return value;
} else if ("year".equals(columnType) || "YEAR".equals(columnType)) {
// 时间类型：范围 1901/2155 格式 YYYY
String year = resultSet.getString(index);
if (null == year) {
return null;
}
// 只需要年的字符即可，
return year.substring(0, 4);
} else if ("date".equals(columnType) || "DATE".equals(columnType)) {
// 时间类型：范围 '1000-01-01'--'9999-12-31' 格式 YYYY-MM-DD
return resultSet.getString(index);
} else if ("time".equals(columnType) || "TIME".equals(columnType)) {
// 时间类型：范围 '-838:59:59'到'838:59:59' 格式 HH:MM:SS
return resultSet.getString(index);
} else if ("datetime".equals(columnType) || "DATETIME".equals(columnType)) {
// 时间类型：范围 '1000-01-01 00:00:00'--'9999-12-31 23:59:59' 格式 YYYY-MM-DD HH:MM:SS
return resultSet.getString(index);
} else if ("timestamp".equals(columnType) || "TIMESTAMP".equals(columnType)) {
// 时间类型：范围 1970-01-01 00:00:00/2037 年某时 格式 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
return resultSet.getString(index);
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
System.err.println("获取数据库类型值异常");
return null;
}
}
public void startBak() {
try {
List tableNames = getTableNames();
System.out.println("tableNames:" + tableNames);
for (String tableName : tableNames) {
bakTableData(tableName);
// System.out.println(generateCreateTableSql(tableName));
// System.out.println("ColumnNames:" + getColumnNames(tableName));
// System.out.println("ColumnTypes:" + getColumnTypes(tableName));
// System.out.println("ColumnComments:" + getColumnComments(tableName));
}
// 统一关闭连接
closeConnection(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new BakDatebase("182.xxx.xxx.xxx", "xd_love_dev", "root", "woaini", "f:\bak.sql").startBak();
}
}
参考：https://blog.csdn.net/z1589714/article/details/104937335
（未验证）
package com.test.web.service;
public class TestMysqlToOracle {
public static final String dirver = "com.mysql.jdbc.Driver";
public static final String useranem="root";
public static final String password="root";
public static final String type = "mysql";
public static final String url="jdbc:mysql://XXXXXX/test";
public static final String dirverR = "oracle.jdbc.OracleDriver";
public static final String useranemR="root";
public static final String passwordR="root";
public static final String urlR = "jdbc:oracle:thin:@localhost:1522:orcl";
public static final String typeR = "oracle";
public static void main(String[] args) throws Exception{
Connection conn = null;
List> list = new ArrayList>();
StringBuffer sql = new StringBuffer();
Class.forName(dirver);
conn = DriverManager.getConnection(url, useranem, password);
DatabasemetaData databasemetaData = conn.getmetaData();
ResultSet tables = databasemetaData.getTables(null, null, "%", null);
while (tables.next()) {
new TestG().sync(tables.getString("TABLE_NAME"));
}
}
@Transactional(rollbackFor = Exception.class)
public Map sync(String tableName) throws Exception {
Map map = new HashMap<>();
long start = System.currentTimeMillis();
// 生成建表sql
Map sql = sql(tableName);
Map columnMap = (Map) sql.get("map");
Map sqlMap = (Map) sql.get("sql");
String createTableSQL = (String) sqlMap.get("sql");
System.out.println("-------获取表结构成功，建表sql生成成功-----");
Boolean tableExit = false;
createTable(dirverR, urlR, useranemR, passwordR , createTableSQL, tableName);
return map;
}
// 获取建表sql
public Map sql(String tableName) throws Exception {
Map map = new HashMap<>();
Map sqlMap = new HashMap<>();
Connection conn = null;
DatabasemetaData metaData = null;
ResultSet rs = null;
ResultSet pt = null;
String sql = null;
boolean isExit = false;
List primaryKeyList = new ArrayList<>();
List> list = new ArrayList>();
try {
Class.forName(dirver);
conn = DriverManager.getConnection(url, useranem, password);
String catalog = conn.getCatalog(); // catalog 是数据库名
System.out.println("---------连接成功，数据库：" + catalog);
metaData = conn.getmetaData();
// 获取表
rs = metaData.getColumns(null, null, tableName, null);
Map dataMap = new HashMap<>();
List> rows = new ArrayList>();
// 获取信息
while (rs.next()) {
HashMap row = new HashMap();
dataMap.put("TABLE_NAME", tableName);
row.put("COLUMN_NAME", rs.getString("COLUMN_NAME")); //字段名
row.put("TYPE_NAME", rs.getString("TYPE_NAME")); //字段类型
if ("DATETIME".equals(rs.getString("TYPE_NAME"))) {
row.put("COLUMN_SIZE", Integer.valueOf(0)); //如果事dataTime类型修改为0，调试返回时19，创表失败
} else {
row.put("COLUMN_SIZE", rs.getInt("COLUMN_SIZE"));
}
map.put(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));
row.put("NULLABLE", rs.getInt("NULLABLE") == 0 ? "NOT NULL" : " "); //可否为null
rows.add(row);
}
//主键
pt = metaData.getPrimaryKeys(conn.getCatalog(), null, tableName);
while (pt.next()) {
primaryKeyList.add( pt.getString("COLUMN_NAME"));
}
dataMap.put("PRIMARYS",primaryKeyList); //获取主键
dataMap.put("rows", rows);
sql = getSql(dataMap, tableName, typeR);
sqlMap.put("sql", sql);
Map all = new HashMap<>();
all.put("map", map);
all.put("sql", sqlMap);
return all;
} finally {
if (null != pt) {
pt.close();
}
if (null != rs) {
rs.close();
}
if (null != conn) {
conn.close();
}
}
}
//拼接建表sql
public String getSql(Map map, String tableName, String typeR) {
StringBuffer sb = new StringBuffer();
List PRIMARYS =(List) map.get("PRIMARYS");
sb.append("CREATE TABLE ").append(map.get("TABLE_NAME")).append(" (").append("n");
List> rows = (List>) map.get("rows");
for (Map rowMap : rows) {
if ("mysql".equals(typeR)){
sb.append("`").append(rowMap.get("COLUMN_NAME") + "` ");
}
else {
sb.append(" ").append(rowMap.get("COLUMN_NAME") + " ");
}
sb.append(casevale(rowMap.get("TYPE_NAME"), typeR));
// sql server 除了varchar类型，其他都加大小
if ("sql_server".equals(typeR)) {
if ((casevale(rowMap.get("TYPE_NAME"), typeR)).equals("VARCHAR")) {
sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
}
// sql server 不存在float和double,转换成decimal
if ((casevale(rowMap.get("TYPE_NAME"), typeR)).equals("DECIMAL")) {
sb.append("(12,4)");
}
}
// mysql , dateTime和时间戳类型不需要大小
else if ("mysql".equals(typeR)) {
if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("DATETIME") || casevale(rowMap.get("TYPE_NAME"), typeR).equals("TIMESTAMP(6)")
|| casevale(rowMap.get("TYPE_NAME"), typeR).equals("LONGTEXT") || casevale(rowMap.get("TYPE_NAME"), typeR).equals("TEXT")) {
// sb.append("(0)");
} else if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("CHAr(1)")) {
} else if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE") || casevale(rowMap.get("TYPE_NAME"), typeR).equals("FLOAT")) {
sb.append("(12,4)");
} else if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("BIT")) {
sb.append("(1)");
}
else {
sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
}
} else {
// 如果是oracle的date类型，不需要加大小
if (!casevale(rowMap.get("TYPE_NAME"), typeR).equals("DATE") && !casevale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE")
&& !casevale(rowMap.get("TYPE_NAME"), typeR).equals("FLOAT")) {
if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE")) {
sb.append("(12,4)");
} else if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("CHAR")) {
sb.append("(1)");
} else if (casevale(rowMap.get("TYPE_NAME"), typeR).equals("VARCHAR2")) {
sb.append("(255)");
}
else {
sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
}
}
}
sb.append((rowMap.get("NULLABLE") == "" ? "" : " " + rowMap.get("NULLABLE")) + ",");
sb.append("n");
}
if (PRIMARYS.size() > 0) {
sb.append(" PRIMARY KEY (");
}
for (int i = 0; i < PRIMARYS.size(); i++) {
sb.append(PRIMARYS.get(i) + ",");
}
sb.deleteCharAt(sb.lastIndexOf(","));
if (PRIMARYS.size() > 0) {
sb.append(")n");
}
sb.append(")");
System.out.println("-----------建表语句n" + sb.toString());
return sb.toString();
}
//两方不同数据库，需要对类型进行转换
public String casevale(Object typeName, String type) {
String typeClound = null;
if ("mysql".equals(type)) {
switch (((String) typeName).toUpperCase()) {
case "NUMBER":
typeClound = "BIGINT";
break;
case "DATE":
typeClound = "DATETIME";
break;
case "VARCHAR2":
typeClound = "VARCHAR";
break;
case "TIMESTAMP(6)":
typeClound = "DATETIME";
break;
case "TIMESTAMP":
typeClound = "DATETIME";
break;
default:
typeClound = (String) ((String) typeName).toUpperCase();
break;
}
} else if ("oracle".equals(type)) {
switch (((String) typeName).toUpperCase()) {
case "BIGINT":
typeClound = "NUMBER";
break;
case "DATETIME":
typeClound = "DATE";
break;
case "BIT":
typeClound = "CHAR";
break;
case "TIMESTAMP(6)":
typeClound = "DATE";
break;
case "TIMESTAMP":
typeClound = "DATE";
break;
case "INT":
typeClound = "NUMBER";
break;
case "FLOAT":
typeClound = "NUMBER";
break;
case "DOUBLE":
typeClound = "NUMBER";
break;
case "TEXT":
typeClound = "VARCHAR2";
break;
case "SMALLINT":
typeClound = "NUMBER";
break;
default:
typeClound = ((String) typeName).toUpperCase();
break;
}
} else {
switch (((String) typeName).toUpperCase()) {
case "NUMBER":
typeClound = "BIGINT";
break;
case "DATE":
typeClound = "DATETIME";
break;
case "CHAR":
typeClound = "BIT";
break;
case "VARCHAR2":
typeClound = "VARCHAR";
break;
case "TIMESTAMP(6)":
typeClound = "DATETIME";
break;
case "FLOAT":
typeClound = "DECIMAL";
break;
case "DOUBLE":
typeClound = "DECIMAL";
break;
case "LONGTEXT":
typeClound = "TEXT";
break;
case "BLOB":
typeClound = "IMAGE";
break;
default:
typeClound = ((String) typeName).toUpperCase();
break;
}
}
return typeClound;
}
//连接对方系统进行建表
public boolean createTable(String dirverR, String urlR, String useranemR, String passwordR, String sql, String tableName) throws Exception {
Connection remoteConn = null;
PreparedStatement preparedStatement = null;
Boolean bl = false;
try {
Class.forName(dirverR);
remoteConn = DriverManager.getConnection(urlR, useranemR, passwordR);
System.out.println("-------连接对方系统成功-------");
if (!typeR.equals("oracle")) {
if (exitsTable(dirverR, urlR, useranemR, passwordR, tableName)) {
return true;
} else {
preparedStatement = remoteConn.prepareStatement(sql);
preparedStatement.execute();
return exitsTable(dirverR, urlR, useranemR, passwordR, tableName);
}
} else {
if (exitsTable(dirverR, urlR, useranemR, passwordR, tableName)) {
return true;
} else {
preparedStatement = remoteConn.prepareStatement(sql);
preparedStatement.execute();
return exitsTable(dirverR, urlR, useranemR, passwordR, tableName);
}
}
} finally {
if (preparedStatement != null) preparedStatement.close();
if (remoteConn != null) remoteConn.close();
}
}
//判断对方表是否存在
public boolean exitsTable(String dirverR, String urlR, String useranemR, String passwordR, String tableName) throws Exception {
Connection remoteConn = null;
ResultSet rs = null;
try {
Class.forName(dirverR);
remoteConn = DriverManager.getConnection(urlR, useranemR, passwordR);
rs = remoteConn.getmetaData().getTables(null, null, tableName.toUpperCase(), null);
if (rs.next()) {
return true;
} else {
return false;
}
} finally {
if (null != rs) rs.close();
if (null != remoteConn) remoteConn.close();
}
}
//获取insert模板
public Map sqlTemplet(List> list, String tableName) {
List fieldList = new ArrayList<>();
StringBuffer sb = new StringBuffer();
Map map = new HashMap<>();
sb.append("insert into ").append(tableName).append(" (");
for (Map.Entry entry : list.get(0).entrySet()) {
sb.append(entry.getKey()).append(",");
fieldList.add(entry.getKey());
}
sb.deleteCharAt(sb.lastIndexOf(",")).append(") values (");
for (Map.Entry entry : list.get(0).entrySet()) {
sb.append("?").append(",");
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append(")n");
System.out.println(sb.toString());
map.put("fieldList", fieldList);
map.put("sqlTemplet", sb.toString());
return map;
}
}
原文链接：https://blog.csdn.net/z1589714/article/details/104937335
