Jdbc realizes the management of things and moves tables

In this paper, the data from the old database is transferred to the new database.

1. Obtain jdbc connection

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, load the corresponding driver 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, load the corresponding driver conn = (Connection) DriverManager.getConnection (url, username, password);} catch (ClassNotFoundException e) {e.printStackTrace ();} catch (SQLException e) {e.printStackTrace ();} return conn } public void closeConnection (Connection conn) {/ / determine whether conn is empty if (conn! = null) {try {conn.close (); / / close database connection} catch (SQLException e) {e.printStackTrace () }}

2. Get the old data

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. Entity class

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. Insert a new library

Use throws Exception instead of try/catch to throw all exceptions to the outer layer

If you use try/catch, the outer layer will not be able to catch exceptions and will not perform a rollback operation.

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 } / * * use throws Exception instead of try/catch The purpose is to throw all exceptions to the outer layer * / 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. Test

Perform a rollback operation after the outer layer catches an exception

Package com.zkbc.transferdata;import java.sql.SQLException;import java.util.List;import com.mysql.jdbc.Connection;import com..entity.Customer;public class Testtest {/ * * perform rollback operation after catching exception * / 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 table and credit table 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) }}}