JDBC连接多个库进行数据操作（常用于数据迁移）

package turnOverClass;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtils {
    private static String url = "jdbc:mysql://数据库?characterEncoding=UTF-8";
    private static String user = "用户名";
    private static String password = "密码";

    private static String localUrl = "jdbc:mysql://数据库?useOldAliasMetadataBehavior=true";
    private static String localUser = "用户名";
    private static String localPwd = "密码";
    

    public static Connection conn;
    public static Statement stat;
    public static ResultSet rs;

    // 连接
    public static Connection getConnection(){

        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 连接数据库
            return DriverManager.getConnection(url,user,password);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 连接
    public static Connection getLocalConn(){

        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 连接数据库
            return DriverManager.getConnection(localUrl,localUser,localPwd);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static ResultSet query(String sql, Connection con){
        Statement st ;
        ResultSet rs  = null;
        try {
            st = con.createStatement();
            // 运行SQL查询语句
            rs =  st.executeQuery(sql);
        } catch(Exception e) {
            System.err.println("Exception: " + e.getMessage());
        }
        return rs;
    }

    public static List<Map<String, Object>> read(String sql, Connection conn) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = ps.getMetaData();

            // 取得结果集列数
            int columnCount = rsmd.getColumnCount();

            // 构造泛型结果集
            List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
            Map<String, Object> data = null;

            // 循环结果集
            while (rs.next()) {
                data = new HashMap<String, Object>();
                // 每循环一条将列名和列值存入Map
                for (int i = 1; i < columnCount; i++) {
                    data.put(rsmd.getColumnLabel(i), rs.getObject(rsmd
                            .getColumnLabel(i)));
                }
                // 将整条数据的Map存入到List中
                datas.add(data);
            }
            return datas;
        } catch (Exception e) {
            throw new RuntimeException();
        } finally {
            DBUtils.close();
        }
    }

    public static Integer readInt(String sql, Connection conn) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = ps.getMetaData();

            Integer i = null;
            // 循环结果集
            while (rs.next()) {
                // 每循环一条将列名和列值存入Map
                   i = (Integer) rs.getObject(rsmd.getColumnLabel(1));
                }
          
            return i;
        } catch (Exception e) {
            throw new RuntimeException();
        } finally {
            DBUtils.close();
        }
    }
    
    public static void close(){
        try {
            if (rs != null){
                rs.close();
                rs = null;
            }
            if (stat != null){
                stat.close();
                stat=null;
            }
            if (conn != null){
                conn.close();
                conn=null;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 把ResultSet的结果放到java对象中
     *
     * @param <T>
     * @param rs
     *            ResultSet
     * @param obj
     *            java类的class
     * @return
     */
    public static <T> ArrayList<T> putResult(ResultSet rs, Class<T> obj) {
        try {
            ArrayList<T> arrayList = new ArrayList<T>();
            ResultSetMetaData metaData = rs.getMetaData();
            /**
             * 获取总列数
             */
            int count = metaData.getColumnCount();
            while (rs.next()) {
                /**
                 * 创建对象实例
                 */
                T newInstance = obj.newInstance();
                for (int i = 1; i <= count; i++) {
                    /**
                     * 给对象的某个属性赋值
                     */
                    String name = metaData.getColumnName(i).toLowerCase();
                    name = toJavaField(name);// 改变列名格式成java命名格式
                    String substring = name.substring(0, 1);// 首字母大写
                    String replace = name.replaceFirst(substring, substring.toUpperCase());
                    Class<?> type = obj.getDeclaredField(name).getType();// 获取字段类型
                    Method method = obj.getMethod("set" + replace, type);
                    /**
                     * 判断读取数据的类型
                     */
                    if(type.isAssignableFrom(String.class)){
                        method.invoke(newInstance, rs.getString(i));
                    }else if(type.isAssignableFrom(int.class) || type.isAssignableFrom(Integer.class)){
                        method.invoke(newInstance, rs.getInt(i));
                    }else if(type.isAssignableFrom(Boolean.class) || type.isAssignableFrom(boolean.class)){
                        method.invoke(newInstance, rs.getBoolean(i));
                    }else if(type.isAssignableFrom(Date.class)){
                        method.invoke(newInstance, rs.getDate(i));
                    }
                }
                arrayList.add(newInstance);
            }
            return arrayList;

        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }
    /**
     * 数据库命名格式转java命名格式
     *
     * @param str
     *            数据库字段名
     * @return java字段名
     */
    public static String toJavaField(String str) {

        String[] split = str.split("_");
        StringBuilder builder = new StringBuilder();
        builder.append(split[0]);// 拼接第一个字符

        // 如果数组不止一个单词
        if (split.length > 1) {
            for (int i = 1; i < split.length; i++) {
                // 去掉下划线，首字母变为大写
                String string = split[i];
                String substring = string.substring(0, 1);
                split[i] = string.replaceFirst(substring, substring.toUpperCase());
                builder.append(split[i]);
            }
        }
        return builder.toString();
    }

}

package turnOverClass;




import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;
/**
 * 先建表
 * @author ywh
 *
 */


public class DataMigration {


	/**
	 * 获取当前表数据
	 */
    private static void getSeriesClassVersion() {
        String sql = "select sclass.series_class_id ,"
        		+ "m.version as version_code,sclass.create_id,"
        		+ "sclass.create_time,"
        		+ "sclass.update_id,"
        		+ "sclass.update_time "
        		+ "from series_class sclass "
        		+ "left join menu m on m.id = sclass.menu_id where sclass.exist = '0'  ";
        Connection localCon = DBUtils.getLocalConn();
        List<TtsSeriesClassVersion> ttsSeriesClassVersions = DBUtils.putResult(DBUtils.query(sql, localCon), TtsSeriesClassVersion.class);
        DBUtils.close();
        Connection conn = DBUtils.getConnection();
        
        for (TtsSeriesClassVersion ttsSeriesClassVersion : ttsSeriesClassVersions) {
            sql = "select version_id from tts_version where version_code='"+ttsSeriesClassVersion.getVersionCode()+"'";
            //version查出来有些为null,为null的显示为-1,导完数据统一将-1改为null
            ttsSeriesClassVersion.setVersionId(DBUtils.readInt(sql,conn)==null?-1:DBUtils.readInt(sql,conn));
            insertTtsSeriesClassVersion(ttsSeriesClassVersion, localCon);
        }
        DBUtils.close();
        System.out.println("数据插入完成");
    }


    
    /**
     * 插入到tts_series_class_version
     * @param tcv
     * @param conn
     * @return
     */
    private static int insertTtsSeriesClassVersion(TtsSeriesClassVersion tcv, Connection conn) {
    	System.out.println(tcv.getSeriesClassId());
        int i = 0;
        //表中create_id,update_id均为空，插入数据后，均为零，因此不予插入
        String sql = "INSERT INTO tts_series_class_version ( series_class_id, version_id, version_code, create_time, update_time) VALUES ( ?, ?, ?, ?, ?)";
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        PreparedStatement pstmt;
        try {
        	
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setInt(1, tcv.getSeriesClassId());
            pstmt.setInt(2, tcv.getVersionId());
            pstmt.setString(3, tcv.getVersionCode());
            pstmt.setString(4, df.format(tcv.getCreateTime()));
            pstmt.setString(5, df.format(tcv.getUpdateTime()));
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    


   /**
    *获取已删除数据 
    */
    private static void getSeriesClassVersionHis() {
        String sql = "select sclass.series_class_id ,"
        		+ "m.version as version_code,sclass.create_id,"
        		+ "sclass.create_time,"
        		+ "sclass.update_id,"
        		+ "sclass.update_time "
        		+ "from series_class sclass "
        		+ "left join menu m on m.id = sclass.menu_id where sclass.exist = '1'  ";
        Connection localCon = DBUtils.getLocalConn();
        List<TtsSeriesClassVersion> ttsSeriesClassVersions = DBUtils.putResult(DBUtils.query(sql, localCon), TtsSeriesClassVersion.class);
        DBUtils.close();
        Connection conn = DBUtils.getConnection();
        
        for (TtsSeriesClassVersion ttsSeriesClassVersion : ttsSeriesClassVersions) {
            sql = "select version_id from tts_version where version_code='"+ttsSeriesClassVersion.getVersionCode()+"'";
            //version查出来有些为null,为null的显示为-1,导完数据统一将-1改为null
            ttsSeriesClassVersion.setVersionId(DBUtils.readInt(sql,conn)==null?-1:DBUtils.readInt(sql,conn));
            insertTtsSeriesClassVersionHis(ttsSeriesClassVersion, localCon);
        }
        DBUtils.close();
        System.out.println("数据插入完成");
    }


    
    
    private static int insertTtsSeriesClassVersionHis(TtsSeriesClassVersion tcv, Connection conn) {
    	System.out.println(tcv.getSeriesClassId());
        int i = 0;
        //表中create_id,update_id均为空，插入数据后，均为零，因此不予插入
        String sql = "INSERT INTO tts_series_class_version_his ( series_class_id, version_id, version_code, create_time, update_time) VALUES ( ?, ?, ?, ?, ?)";
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        PreparedStatement pstmt;
        try {
        	
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setInt(1, tcv.getSeriesClassId());
            pstmt.setInt(2, tcv.getVersionId());
            pstmt.setString(3, tcv.getVersionCode());
            pstmt.setString(4, df.format(tcv.getCreateTime()));
            pstmt.setString(5, df.format(tcv.getUpdateTime()));
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    
    
    public static void main(String[] args) {
    	//迁移到tts_series_class_version
    	//getSeriesClassVersion();
    	//迁移到tts_series_class_version_his
    	getSeriesClassVersionHis();


    }


}