数据库同步

上次发布订阅实现数据库同步，这次用触发器+代码实现了按表的同步。

思路是：1，需同步的A。B两个表都建立触发器，和同步表A-Trigger,B_Trigger。在对表增删改的时候，用触发器记录存放在同步表中。

2，定时读取两个同步表，然后将更改记录更新到对方的表中。

实现：

1 触发器：建立一个和A结构一样的同步表，并新加一个字段，用来存放操作的类型，如：update，insert，delete。然后在 A表上建立触发器，将对A的增删改记录存放到这个同步表中。

use DB 
---创建同步表
select top 1 * into unit_Trigger from unit;
----删除临时表中的数据
delete from unit_Trigger;
select * from unit_Trigger;
---在临时表中增加记录 操作类型的字段 TYPE_TRIGGER
alter table unit_Trigger add TYPE_TRIGGER varchar(20);
go

----创建插入触发器
if exists(select name from sysobjects where name='unit_insert_trigger' and type='tr')
drop trigger unit_insert_trigger;
go

create trigger unit_insert_trigger 
on unit
after insert
as 
begin
insert into ProbeDb.dbo.unit_Trigger(C1,C2,C3,TYPE_TRIGGER)
				(select C1,C2,C3,'insert' from inserted);
end

go

--创建更改触发器
if exists(select name from sysobjects where name='unit_update_trigger' and type='tr')
drop trigger unit_update_trigger;

go

create trigger unit_update_trigger 
on unit
after update
as 
begin
 insert into ProbeDb.dbo.unit_Trigger (C1,C2,C3,TYPE_TRIGGER)
 				(select C1,C2,C3,'update' from inserted);
end

go



--创建删除触发器
if exists(select name from sysobjects where name='unit_delete_trigger' and type='tr')
drop trigger unit_delete_trigger;
go

create trigger unit_delete_trigger 
on unit
after delete
as 
begin
insert into ProbeDb.dbo.unit_Trigger(C1,C2,C3,TYPE_TRIGGER)

 				(select C1,C2,C3,'delete' from deleted);
end

2，提取数据并插入

1）编写实体类，bean

2）编写DAO

public class UnitDao {

	/**
	 * 读取触发器记录表
	 * @param conn
	 * @return
	 */
	public List readTrigger(Connection conn,String tableName){
		List<Unit> unitList = new ArrayList<Unit>();
		String sqlSelect = "select * from unit_Trigger";
		
		Statement statem = null;
		ResultSet rs = null;
		try {
			statem = conn.createStatement();
			rs = statem.executeQuery(sqlSelect);
			while(rs.next()){
				Unit unit = new Unit();
				
				unit.setC1(rs.getString("C1"));
				unit.setC2(rs.getString("C2"));
				unit.setC3(rs.getString("C3"));
				unit.setTYPE_TRIGGER(rs.getString("TYPE_TRIGGER"));
				
				unitList.add(unit);
			}
			
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!= null){
					rs.close();
				}
				if(statem!=null){
					statem.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return unitList;
	}
	
	/**
	 * 清空触发器记录表中的数据
	 * @param conn
	 */
	public void clearTrigger(Connection conn,String tbTriggerName){
		String sqlDelete = "delete from "+tbTriggerName;
		Statement statem = null;
		try {
			statem = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			statem.execute(sqlDelete);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 向触发器表中插入数据
	 */
	public void insertTrigger(Connection conn,String tbTriggerName,List<Unit> unitTriggerList){
		String sqlInsert = "insert into  "+tbTriggerName
				+ "(C1,C2,C3,TYPE_TRIGGER) "
				+ "values (?,?,?,?)";
		for(Unit unitTigger : unitTriggerList){
			PreparedStatement psInsert;
			try {
				psInsert = conn.prepareStatement(sqlInsert);
				
				psInsert.setString(1, unitTigger.getC1());
				psInsert.setString(2, unitTigger.getC2());
				psInsert.setString(3,unitTigger.getC3());
				psInsert.setString(4, unitTigger.getTYPE_TRIGGER());
				
				
				psInsert.execute();
				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}

	/**
	 * 按主键查询数据
	 * @param conn
	 * @return
	 */
	public Unit selectByKey(Connection conn,String tableName, String C1){
		String sql = "select * from "+tableName+" where C1 = '"+C1+"'";
		Unit unit = null;
		Statement statem = null;
		try {
			statem = conn.createStatement();
			ResultSet rs = statem.executeQuery(sql);
			while(rs.next()){
				unit = new Unit();
			
				unit.setC1(rs.getString("C1"));
				unit.setC2(rs.getString("C2"));
				unit.setC3(rs.getString("C3"));

				
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return unit;
	}
	
	/**
	 * 插入一条记录
	 * @param conn
	 * @return
	 */
	public boolean insert(Connection conn,String tableName ,Unit unit){
		boolean flag = false;
		int insNum = 0;
		String sqlInsertunit = "insert into "+tableName+" (C1,C2,C3) "
				+ "values (?,?,?)";
		PreparedStatement psInsert;
		try {
			psInsert = conn.prepareStatement(sqlInsertunit);
			psInsert.setString(1,unit.getC1());
			psInsert.setString(2, unit.getC2());
			psInsert.setString(3, unit.getC3());

			insNum = psInsert.executeUpdate();
			conn.commit();
			if (insNum == 1){
				flag = true;
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 更新数据
	 * @param conn
	 * @return
	 */
	public boolean update(Connection conn,String tableName,Unit unit){
		boolean flag = false;
		
	
		String sqlUpdateunit = "update "+tableName+" set C1=?,C2=?,C3=?) "
				+ "where C1=?";
		int udNum = 0;
		PreparedStatement psUpdate;
		try {
			psUpdate = conn.prepareStatement(sqlUpdateunit);

			psUpdate.setString(1, unit.getC1());
			psUpdate.setString(2, unit.getC2());
			psUpdate.setString(3, unit.getC3());
			
			udNum = psUpdate.executeUpdate();
			conn.commit();
			if(udNum == 1){
				flag = true;
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			e.printStackTrace();
		}
		
		return flag;
	}
	
	/**
	 * 删除一条数据
	 * @param conn
	 * @return
	 */
	public boolean deleteByKey(Connection conn,String tableName,Unit unit){
		int delNum = 0;
		boolean flag = false;
		String sqlDeleteunit = "delete from "+tableName+" where C1=? ";
		PreparedStatement psDelete;
		try {
			psDelete = conn.prepareStatement(sqlDeleteunit);
			psDelete.setString(1, unit.getC1());
			delNum = psDelete.executeUpdate();
			conn.commit();
			if(delNum == 1){
				flag = true;
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return flag;
	}
	
	public List readUnit(Connection conn,String tableName){
		List unitList = new ArrayList();
		String sqlSelect = "select * from "+tableName;
		
		Statement statem = null;
		ResultSet rs = null;
		try {
			statem = conn.createStatement();
			rs = statem.executeQuery(sqlSelect);
			while(rs.next()){
				Unit unit = new Unit();
				unit.setC1(rs.getString("C1"));
				unit.setC2(rs.getString("C2"));
				unit.setC3(rs.getString("C3"));

				unitList.add(unit);
			}
			
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!= null){
					rs.close();
				}
				if(statem!=null){
					statem.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return unitList;
	}
	
}

3） 提取数据并更新

public class UnitAccess  implements Runnable{

	private final String tableNameA = "Unit";
	private final String tableNameB = "TB";
	private final String tbTriggerNameA = "unit_Trigger";
	private final String tbTriggerNameB = "TB_Trigger";
    @Override  
    public void run() {  
    	boolean keyA = false;
    	boolean keyB = false;
    	Connection connA = null;
    	Connection connB = null;
    	connA = DBCPConn.getConnectionA();
    	connB = DBCPConn.getConnectionB();
    	UnitDao unitDao = new UnitDao();
    	
    	List unitListA = unitDao.readTrigger(connA,tbTriggerNameA);
    	unitDao.clearTrigger(connA,tbTriggerNameA);
    	keyA = true;
    	List unitListB = unitDao.readTrigger(connB,tbTriggerNameB);
    	unitDao.clearTrigger(connB,tbTriggerNameB);
    	keyB = true;
    	while(!keyA || !keyB){
    		System.out.println("等待全部取数完成");
    	}
    	writeUnit(connB,tableNameB, tbTriggerNameB, unitListA);
    	writeUnit(connA,tableNameA, tbTriggerNameA, unitListB);
    }	
    
     public void writeUnit(Connection conn,String tableName,String tbTriggerName,List<Unit> unitList){
		/**
		 * 1,将Trigger表中的数据保存起来
		 */
		List<Unit> unitTriggerList = new ArrayList<Unit>();
		UnitDao unitDao = new UnitDao();
		unitTriggerList = unitDao.readTrigger(conn,tbTriggerName);
		unitDao.clearTrigger(conn,tbTriggerName);
		/**
		 *2. 将传过来的数据更新到数据库
		 */
		for(Unit unit: unitList){
			switch(unit.getTYPE_TRIGGER()){
			case "insert":
				Unit unitinsert = unitDao.selectByKey(conn, tableName,unit.getF_DWBH());
				if( unitinsert == null){
					unitDao.insert(conn,tableName, unit);
				}
				else{
					unitDao.update(conn,tableName, unit);
				}
				break;
			case "update":
				Unit unitUpdate = unitDao.selectByKey(conn ,tableName,unit.getF_DWBH());
				if( unitUpdate == null){
					unitDao.insert(conn,tableName, unit);
				}
				else{
					unitDao.update(conn,tableName, unit);
				}
				break;
			case "delete":
				unitDao.deleteByKey(conn,tableName, unit);
				break;
			}
		}
    			
		/**
		 *3 删除更新产生的Trigger数据
		 */
		unitDao.clearTrigger(conn,tbTriggerName);
		
		/**
		 * 4.恢复Trigger 原先的数据
		 */
		if(!unitTriggerList.isEmpty()){
			unitDao.insertTrigger(conn, tbTriggerName,unitTriggerList);
		}
    }

}