jdbc实现事物管理并搬表

本文实现旧库中的数据搬到新库中

1、获取jdbc连接

package com.transferdata;import java.sql.DriverManager;import java.sql.SQLException;import com.mysql.jdbc.Connection;public class ConnectionUtils {        public Connection getOldConn() {            String driver = "com.mysql.jdbc.Driver";            String url = "jdbc:mysql://localhost:3306/old?useUnicode=true&characterEncoding=utf-8";            String username = "root";            String password = "123456";            Connection conn = null;            try {                Class.forName(driver); //classLoader,加载对应驱动                conn = (Connection) DriverManager.getConnection(url, username, password);            } catch (ClassNotFoundException e) {                e.printStackTrace();            } catch (SQLException e) {                e.printStackTrace();            }            return conn;        }                public Connection getNewConn() {            String driver = "com.mysql.jdbc.Driver";            String url = "jdbc:mysql://localhost:3306/new?useUnicode=true&characterEncoding=utf-8";            String username = "root";            String password = "123456";            Connection conn = null;            try {                Class.forName(driver); //classLoader,加载对应驱动                conn = (Connection) DriverManager.getConnection(url, username, password);            } catch (ClassNotFoundException e) {                e.printStackTrace();            } catch (SQLException e) {                e.printStackTrace();            }            return conn;        }                public void closeConnection(Connection conn){          // 判断conn是否为空          if(conn != null){              try {                  conn.close();   // 关闭数据库连接              } catch (SQLException e) {                  e.printStackTrace();              }          }      }}

2、获取老的数据

package com.transferdata;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;import com.entity.Customer;public class GetOldData {        private Connection oldConn;        public GetOldData(Connection oldConn){                this.oldConn = oldConn;        }                public List getCustomerList() {             List customerList = new ArrayList();            String sql = "select * from customer";            PreparedStatement pstmt;            try {                pstmt = (PreparedStatement)oldConn.prepareStatement(sql);                ResultSet rs = pstmt.executeQuery();                while (rs.next()) {                    Customer customer = new Customer();                    customer.setId(rs.getString("id"));                    customer.setEmail(rs.getString("email"));                    customer.setIdcardNo(rs.getString("idcard_no"));                    customer.setLoginName(rs.getString("login_name"));                    customer.setMobileNo(rs.getString("mobile_no"));                    customer.setPassword(rs.getString("password"));                    customer.setRealName(rs.getString("real_name"));                    customer.setRecomerMoNo(rs.getString("recomer_mo_no"));                                customerList.add(customer);                                        }            } catch (SQLException e) {                e.printStackTrace();            }            return customerList;        }                }

3、实体类

package com.entity;public class Customer {        private String id;        private String loginName;        private String mobileNo;        private String recomerMoNo;        private String realName;        private String email;        private String idcardNo;        private String password;        public String getId() {                return id;        }        public void setId(String id) {                this.id = id;        }        public String getLoginName() {                return loginName;        }        public void setLoginName(String loginName) {                this.loginName = loginName;        }        public String getMobileNo() {                return mobileNo;        }        public void setMobileNo(String mobileNo) {                this.mobileNo = mobileNo;        }        public String getRecomerMoNo() {                return recomerMoNo;        }        public void setRecomerMoNo(String recomerMoNo) {                this.recomerMoNo = recomerMoNo;        }        public String getRealName() {                return realName;        }        public void setRealName(String realName) {                this.realName = realName;        }        public String getEmail() {                return email;        }        public void setEmail(String email) {                this.email = email;        }        public String getIdcardNo() {                return idcardNo;        }        public void setIdcardNo(String idcardNo) {                this.idcardNo = idcardNo;        }        public String getPassword() {                return password;        }        public void setPassword(String password) {                this.password = password;        }        }

4、插入新库

用throws Exception，而不用try/catch，目的是将异常全部抛到外层

如果用try/catch ，外层将不能捕获异常，不会执行回滚操作，这点注意

package com.transferdata;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Date;import java.util.List;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;import com.entity.Customer;public class InsertNewSchame {        private Connection newConn;        public InsertNewSchame(Connection newConn){                this.newConn = newConn;        }                        /*         * 用throws Exception，而不用try/catch，目的是将异常全部抛到外层         */        public boolean insertCustomer(Customer customer, String id) throws Exception{        // account_id\mobile_app_info\zhima_score\device_token            String sqlInsert = "INSERT INTO adm_sys_customer(email,idcard_no,login_name,mobile_no,password,real_name) "                               + "VALUES (?,?,?,?,?,?)";            PreparedStatement pstmt;                           pstmt = (PreparedStatement) newConn.prepareStatement(sqlInsert);                pstmt.setString(1, customer.getEmail());                pstmt.setString(2, customer.getIdcardNo());                pstmt.setString(3, customer.getLoginName());                pstmt.setString(4, customer.getMobileNo());                pstmt.setString(5, customer.getPassword());                pstmt.setString(6, customer.getRealName());                                pstmt.executeUpdate();                      pstmt.close();                       return true;                    }        }

5、测试

外层捕获异常后执行回滚操作

package com.zkbc.transferdata;import java.sql.SQLException;import java.util.List;import com.mysql.jdbc.Connection;import com..entity.Customer;public class Testtest {        /*         * 外层捕获异常后执行回滚操作         */        public static void main(String args[]) {                ConnectionUtils ConnectionUtils = new ConnectionUtils();                Connection oldConn = ConnectionUtils.getOldConn();                Connection newConn = ConnectionUtils.getNewConn();                                GetOldData oldData = new GetOldData(oldConn);                InsertNewSchame newData = new InsertNewSchame(newConn);                try{                        oldConn.setAutoCommit(false);                        newConn.setAutoCommit(false);                        String cuId = "0";                        List customerList = oldData.getCustomerList();                        for(Customer customer:customerList) {                                cuId = (Integer.parseInt(cuId) + 1) + "";                                //customer表和credit表                                newData.insertCustomer(customer, cuId);                        }                                                oldConn.commit();                        newConn.commit();                }catch(Exception e) {                        e.printStackTrace();                        try {                                oldConn.rollback();                                newConn.rollback();                        } catch (SQLException e1) {                                // TODO Auto-generated catch block                                e1.printStackTrace();                        }                }finally {                        try {                                oldConn.setAutoCommit(true);                                newConn.setAutoCommit(true);                        } catch (SQLException e) {                                // TODO Auto-generated catch block                                e.printStackTrace();                        }                        ConnectionUtils.closeConnection(oldConn);                        ConnectionUtils.closeConnection(newConn);                                        }               }}