业务场景：
从15.5数据库把需要同步的数据表同步到174数据表中，其中174中有些字段比较短，有些字段不存在，有些表不存在，重复主键等问题。
字段大小不一致：执行insert操作时捕获异常，忽略错误。
174表中字段不一致:判断表中是否有相应的字段。
表是否存在：判断表是否存在。
重复主键：原本通过delete，删除了以前的数据。后来使用了try catch捕获异常，忽略错误。这里有些可以改进的地方，有更新的情况没有考虑。
System.out.println()严重影响了效率，尽量避免使用。
其实以上的错误都可以通过在insert时捕获异常来处理，代码的处理逻辑与上面的描述一致。
主函数
package dataChange;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ChangeMain {
public static void main(String[] args)
{
GetMessage gm = new GetMessage();
try {
//从源数据库中获取数据
Map<String, List<HashMap<String, Object>>> map = gm.selectData();
//插入到目标数据库中
gm.insertNewDataBase(map);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}获取数据和插入数据：
package dataChange;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
public class GetMessage {
//从15.5上获取数据
public Map<String, List<HashMap<String, Object>>> selectData() throws SQLException {
Connection con = getConnection("","","","");
Statement stmt = con.createStatement();
//dx_dbdata_bxxpz表中存放着需要同步的数据表
ResultSet rs =stmt.executeQuery("select * from dx_dbdata_bxxpz");
List<String> li = new ArrayList<>();
while (rs.next())
{
//第二列数据为数据表名称
li.add(rs.getString(2));
}
rs.close();
stmt.close();
/* while (rs.next()) {
Hashtable<String, Object> row = new Hashtable<String, Object>();
for (int i = 1; i <= rsm.getColumnCount(); i++) {
// 打印列名
System.out.println(rsm.getColumnName(i));
row.put(rsm.getColumnName(i), rs.getObject(i));
}
rows.add(row);
}*/
//这个map 键为表的名称，值为表中所有数据的list集合，list集合包含map，其中键为列名称，值为列对应的值
Map<String,List<HashMap<String, Object>>> totalMap = new HashMap<>();
for (int i = 0;i < li.size();i++)
{
Statement stmt1 = con.createStatement();
String tableName = li.get(i);
String sql = "select * from "+tableName;
ResultSet rs1 =stmt1.executeQuery(sql);
ResultSetMetaData rsm = rs1.getMetaData();
//这里List，便于保存
List<HashMap<String, Object>> rows = new ArrayList<HashMap<String, Object>>();
while (rs1.next())
{
HashMap<String, Object> row = new HashMap<String, Object>();
for (int j = 1; j <= rsm.getColumnCount(); j++) {
row.put(rsm.getColumnName(j), rs1.getObject(j));
}
rows.add(row);
}
totalMap.put(tableName, rows);
// System.out.println(totalMap);
rs1.close();
stmt1.close();
}
con.close();
return totalMap;
}
//实现数据库的连接
private static Connection getConnection(String url,String user,String password,String shema) {
String driver = "com.mysql.jdbc.Driver";
String url1 = "jdbc:mysql://"+url+":3306/"+shema;
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url1, user, password);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
//插入到174的环境中
public void insertNewDataBase(Map<String, List<HashMap<String, Object>>> map) {
Connection con =getConnection("","","","");
for (Entry<String,List<HashMap<String, Object>>> elh:map.entrySet())
{
String key = elh.getKey();
List<HashMap<String, Object>> lhso = elh.getValue();
//先执行删除操作
try {
if (exitsTable(key,con)){
//拼接insert into的列明1，列明2.......和value1....。这里使用StringBuilder，StringBuilder貌似是线程不安全的。方便不容易出错
StringBuilder sb = new StringBuilder();
StringBuilder sb1 = new StringBuilder();
for (int i = 0;i < lhso.size();i++)
{
HashMap<String, Object> mapColumn = lhso.get(i);
for (Entry<String, Object> eso:mapColumn.entrySet()){
try {
if (columAndJudge(key,eso.getKey()))
{
sb.append(eso.getKey());
sb.append(",");
if (eso.getValue() != null && (eso.getValue().getClass().getName().equals("java.lang.String")))
{
String result = "'"+eso.getValue()+"'";
sb1.append(result);
}else
{
sb1.append(eso.getValue());
}
sb1.append(",");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
sb.deleteCharAt(sb.length()-1);
sb1.deleteCharAt(sb1.length()-1);
String column = sb.toString();
String value = sb1.toString();
Statement stmtInsert = null;
try {
stmtInsert = con.createStatement();
String sql = "insert into "+key+"("+column+")"+"values ("+value+")";
stmtInsert.executeUpdate(sql);
} catch (SQLException e) {
//这里一定要删除，否组会出错
sb = sb.delete( 0, sb.length() );
sb1 = sb1.delete( 0, sb1.length() );
System.out.println("忽略错误"+key);
continue;
} finally{
try {
stmtInsert.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
//这里使用delete清空StringBuilder，网上搜了一下，貌似是清空中效率最高的
sb = sb.delete( 0, sb.length() );
sb1 = sb1.delete( 0, sb1.length() );
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("同步完成");
}
//判断是否存在特定的数据表
private boolean exitsTable(String key, Connection con) throws SQLException {
ResultSet rs = con.getMetaData().getTables(null, null, key, null );
if (rs.next())
{
return true;
}else
{
return false;
}
}
/**
* 判断执行的表格是否特定的列名
* @param key 表名称
* @param key2 列名称
* @return
* @throws SQLException
*/
private boolean columAndJudge(String key, String key2) throws SQLException {
Connection con =getConnection("","","","");
Statement stmt = con.createStatement();
String sql = "select * from "+key;
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rmd = rs.getMetaData();
for (int i = 1; i <= rmd.getColumnCount(); i++) {
// 打印列名
if (key2.equals(rmd.getColumnName(i)))
{
stmt.close();
con.close();
return true;
}
}
stmt.close();
con.close();
return false;
}
}以后可以研究下线程池和数据库连接池，这样性能不太好。有很大改进空间，希望不吝指教。
考虑 过使用批量插入的方法，但实际上相应的列对应不上。
批量插入代码实例：
import com.mysql.jdbc.Connection;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class MysqlBatchUtil {
private String sql = "INSERT INTO intest (username, password, create_time) VALUES (?, ?, now())";
//MySql 的批量操作，要加rewriteBatchedStatements参数
private String connectStr = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useServerPrepStmts=false&rewriteBatchedStatements=true";
private String username = "root";
private String password = "123";
private void doStore() throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = (Connection) DriverManager.getConnection(connectStr, username, password);
conn.setAutoCommit(false);
int count = 0;
PreparedStatement psts = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 1; i <= 5000000; i++) {
psts.setString(1, "user" + i);
psts.setString(2, "pass" + i);
psts.addBatch(); // 加入批量处理
count++;
}
psts.executeBatch(); // 执行批量处理
conn.commit(); // 提交
long end = System.currentTimeMillis();
System.out.println("数量=" + count);
System.out.println("运行时间=" + (end - start));
conn.close();
}
public static void main(String[] args) {
try {
new MysqlBatchUtil().doStore();
} catch (ClassNotFoundException | SQLException | IOException e) {
e.printStackTrace();
}
}
}
