private static final String UPDATE_0="update tb_sys_client_business_ext set m_status=?,m_regdate=?,m_unregdate=null where m_usid=? and m_type=? ";
private static final String UPDATE_1="update tb_sys_client_business_ext set m_status=?,m_unregdate=? where m_usid=? and m_type=? ";
/**
* 修改 将allList里的数据统一更新为0 或者是1
* @param status 状态
* @param list 已存在的商户号
* @param allList 需要设置的商户
* @throws Exception
*/
public void setAllUpdate(String status,List<Business_extForm> allList) throws Exception{
Connection conn=DBManager.getConnection();
PreparedStatement pst=null;
String sql="";
Timestamp now=DateLib.now();
try {
Business_extForm bf = null;int i;
conn.setAutoCommit(false);
System.out.println("start:"+System.currentTimeMillis());
while(allList.size()!=0){
i=0;
sql=sql(status);
pst=conn.prepareStatement(sql);
try {
while(allList.size()!=0)
{
bf=allList.remove(0);
pst.setString(1,status);
pst.setTimestamp(2,now);
pst.setString(3,bf.getM_usid());
pst.setString(4,bf.getM_type());
pst.addBatch();
pst.clearParameters();
i++;
if (i == 100)
break;
}
if (i > 0)
pst.executeBatch();
conn.commit();
} catch (Exception e) {
conn.rollback();
throw e;
}
pst.close();
}
System.out.println(" end:"+System.currentTimeMillis());
} catch (Exception e) {
throw e;
}finally{
conn.close();
}
}
ps: 1、表是这样设计的 一个id对应多个业务类型~~ allList里面相当于(id会有重复) 一个商户号里有多个业务类型 2、sqlserver 优化, java代码 优化, 分割多个try catch 优化。
如题