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();    }}