JDBC操作数据库（同步：备份，修改，删除）笔记

本文链接：https://blog.csdn.net/fei2656/article/details/38865353

个人笔记！

实现两个不同数据库的表的同步，建立java project，使用JDBC。

一、步骤：

1、导入驱动包：把mysql-connetor-java.jar 包放置到lib下；然后设置读取加载在project ->proprities ->java buit path ->libraries 下设置。

2、加载驱动：

static{
		 try{
	    	 Class.forName("com.mysql.jdbc.Driver");
	    	 System.out.println("驱动加载成功!");
	    
	     }catch (ClassNotFoundException  e){
	    	 System.out.println("加载驱动问题："+e.getMessage());
	    	 e.printStackTrace();	    	 
	     }

3、连接数据库：

	private final static String url_promanage ="jdbc:mysql://...?" +"useUnicode=true&characterEncoding=utf-8";
	private final static String url_backup ="jdbc:mysql://...?" +"useUnicode=true&characterEncoding=utf-8";
	private final static String username = "root";
	private final static String password = "123456";

/**
	 * 被同步数据库连接（promanage），另外同样方式加载备份数据库
	 */
	public static Connection getProManage() throws SQLException{		
		Connection conProManage =  DriverManager.getConnection(url_promanage, username, password); 
		return conProManage;		
	}

4、实现同步方法：参考http://blog.csdn.net/thinker28754/article/details/6919126

5、插入、更新，操作时不能用

stmProMagBackup.executeUpdate("UPDATE promanage_backup.b_bug SET b_bug SELECT * FROM  promanage.b_bug WHERE id="+rsUpdate.getString(1)+"");
     	    	 stmProMagBackup.executeUpdate("UPDATE b_bug SET founder='"+rsUpdate.getString(2)+"',projectName='"+rsUpdate.getString(3)+"',functionMk='"+rsUpdate.getString(4)+"'," +
     	    	 		"developer='"+rsUpdate.getString(5)+"',receiver='"+rsUpdate.getString(6)+"',title='"+rsUpdate.getString(7)+"',describle='"+rsUpdate.getString(8)+"',state='"+rsUpdate.getString(9)+"',userId='"+rsUpdate.getString(10)+"'," +
"compareDate='"+ctime+"'  WHERE id="+rsUpdate.getString(1));

   		 stmProMagBackup.executeUpdate("INSERT INTO promanage_backup.b_bug  SELECT * FROM  promanage.b_bug WHERE id="+rsUpdate.getString(1)+"");
    		 stmProMagBackup.executeUpdate("INSERT INTO b_bug (id,founder,projectName,functionMk,developer,receiver,title,describle,state,userId,compareDate) VALUES " +
 					"('"+rsUpdate.getString(1)+"','"+rsUpdate.getString(2)+"','"+rsUpdate.getString(3)+"','"+rsUpdate.getString(4)+"','"+rsUpdate.getString(5)+"','"+rsUpdate.getString(6)+"','"
 					+rsUpdate.getString(7)+"','"+rsUpdate.getString(8)+"','"+rsUpdate.getString(9)+"','"+rsUpdate.getString(10)+"','"+rsUpdate.getString(11)+"')");

以上操作在保存rs.setDstring(*)为空时会保存入一个空字符窜“null”。

应该使用批处理：

 	 
      	    	PreparedStatement pstmt = conProMagBackup.prepareStatement("UPDATE b_bug SET id=?, founder=?, projectName=?, functionMk=?, developer=?, receiver=?, title=?, describle=?, state=?, userId=?, compareDate=?" +
      	    			"  WHERE id = '"+rsUpdate.getString(1)+"'");
      	    	pstmt.setString(1, rsUpdate.getString(1));
      	    	pstmt.setString(2, rsUpdate.getString(2));
      	    	pstmt.setString(3, rsUpdate.getString(3));
      	    	pstmt.setString(4, rsUpdate.getString(4));
      	    	pstmt.setString(5, rsUpdate.getString(5));
      	    	pstmt.setString(6, rsUpdate.getString(6));
      	    	pstmt.setString(7, rsUpdate.getString(7));
      	    	pstmt.setString(8, rsUpdate.getString(8));
      	    	pstmt.setString(9, rsUpdate.getString(9));
      	    	pstmt.setString(10, rsUpdate.getString(10));
      	    	pstmt.setString(11, rsUpdate.getString(11));
      	    	pstmt.addBatch();
                //提交
      	    	pstmt.executeBatch();
      	    	pstmt.clearBatch();
      	    	conProMagBackup.commit();

执行插入时也可以：

stmProMagBackup.executeUpdate("INSERT INTO promanage_backup.b_bug  SELECT * FROM  promanage.b_bug WHERE id="+rsUpdate.getString(1)+"");