我现在需要对数据库中的两个表进行插入操作,要求插入要么两个表都插入成功,要么都不成功,代码如下,请各位认真看看:其中**VO代表的是javabean,就是属性的set get方法。要求插入第一个表后,如果再插入第二个表的时候出现异常,第一个表原来已经插入的内容也要撤销。
public void saveNewCampaignManagerInfoTest(CmVO _cmVO,String _approve,int[]_selectedId )throws Exception{
Connection con = DataBase.getConnection();
con.setAutoCommit(false);
Savepoint savepoint = con.setSavepoint("keypoint");
try{
int newCmId = CounterUtils.getNextCounter(con,"cm");//取得新的Id
//Insert the CmVO
_cmVO.setCmId(newCmId);
insertCmVOTest(con,_cmVO);//插入第一个表
//Insert the ServiceCmVO
if(_selectedId != null){
for(int i =0;i< _selectedId.length;i++){
Savepoint savePoint1 = savepoint;
int serviceId = _selectedId[i];
ServiceCmVO serviceCmVO = new ServiceCmVO();
serviceCmVO.setServiceId(serviceId);
serviceCmVO.setCmId(newCmId);
if("ApproveAll".equals(_approve)){
serviceCmVO.setApprovalStatus(1);
}else{
serviceCmVO.setApprovalStatus(0);
}//这些都是为插入做准备,没有必要关心
insertServiceCMTest(con,serviceCmVO);//插入第二个表
}
}
}catch(Exception e){
logger.error("Error in the saveNewCampaignManagerInfo method :"
+ e.getStackTrace());
con.rollback(savepoint);
throw e;
}finally{
con.close();
}
}
private void insertCmVOTest(Connection _con ,CmVO _cmVO)throws Exception{
try{
PreparedStatement ps = _con
.prepareStatement("insert into cm values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
ps.setInt(1,_cmVO.getCmId());
ps.setInt(2,_cmVO.getCmLspId());
ps.setString(3,_cmVO.getCmUserId());
ps.setString(4,_cmVO.getCmPasswd());
ps.setString(5,_cmVO.getCmEmail());
ps.setString(6,_cmVO.getCmName());
ps.setInt(7,_cmVO.getCmStatus());
ps.setTimestamp(8,_cmVO.getCmStatusTS());
ps.setBigDecimal(9,_cmVO.getPricePerSMS());
ps.setBigDecimal(10,_cmVO.getPricePerMMS());
ps.setString(11,_cmVO.getCmAddrLine1());
ps.setString(12,_cmVO.getCmAddrLine2());
ps.setString(13,_cmVO.getCmCity());
ps.setString(14,_cmVO.getCmState());
ps.setString(15,_cmVO.getCmZipCode());
ps.setString(16,_cmVO.getCmTelNo());
ps.setString(17,_cmVO.getCmFax());
ps.setString(18,_cmVO.getCmCountryCode());
ps.setString(19,_cmVO.getCmContactFirstName());
ps.setString(20,_cmVO.getCmContactLastName());
ps.execute();
_con.commit();
}catch(Exception e){
logger.error("Error when insert the cmVO :"+e.getStackTrace());
throw e;
}
}
private void insertServiceCMTest(Connection _con ,ServiceCmVO _serviceCmVO)throws Exception{
try{
PreparedStatement ps = _con
.prepareStatement("insert into service_cm values(?,?,?)");
ps.setInt(1,_serviceCmVO.getServiceId());
ps.setInt(2,_serviceCmVO.getCmId());
ps.setInt(3,_serviceCmVO.getApprovalStatus());
ps.execute();
if(true){
throw (new Exception());
}//故意抛出异常
_con.commit();
}catch(Exception e){
throw e;
}
}
这样为什么第一个表的插入不能撤销?
如果将savepoint 作为参数传递给下面的两个方法,而且在两个方法捕捉到异常后各自回滚,就会报错说 have no such savepoint
修改后的两个方法分别为
private void insertCmVOTest(Connection _con ,CmVO _cmVO,Savepoint _savePoint)throws Exception{
try{
......
_con.commit();
}catch(Exception e){
_con.rollback(_savepoint);
}
}private void insertServiceCMTest(Connection _con ,ServiceCmVO _serviceCmVO,Savepoint _savepoint)throws Exception{ try{
...... _con.commit();
}catch(Exception e){
_con.rollback(_savepoint);
}
}请各位执教
public void saveNewCampaignManagerInfoTest(CmVO _cmVO,String _approve,int[]_selectedId )throws Exception{
Connection con = DataBase.getConnection();
con.setAutoCommit(false);
Savepoint savepoint = con.setSavepoint("keypoint");
try{
int newCmId = CounterUtils.getNextCounter(con,"cm");//取得新的Id
//Insert the CmVO
_cmVO.setCmId(newCmId);
insertCmVOTest(con,_cmVO);//插入第一个表
//Insert the ServiceCmVO
if(_selectedId != null){
for(int i =0;i< _selectedId.length;i++){
Savepoint savePoint1 = savepoint;
int serviceId = _selectedId[i];
ServiceCmVO serviceCmVO = new ServiceCmVO();
serviceCmVO.setServiceId(serviceId);
serviceCmVO.setCmId(newCmId);
if("ApproveAll".equals(_approve)){
serviceCmVO.setApprovalStatus(1);
}else{
serviceCmVO.setApprovalStatus(0);
}//这些都是为插入做准备,没有必要关心
insertServiceCMTest(con,serviceCmVO);//插入第二个表
}
}
}catch(Exception e){
logger.error("Error in the saveNewCampaignManagerInfo method :"
+ e.getStackTrace());
con.rollback(savepoint);
throw e;
}finally{
con.close();
}
}
private void insertCmVOTest(Connection _con ,CmVO _cmVO)throws Exception{
try{
PreparedStatement ps = _con
.prepareStatement("insert into cm values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
ps.setInt(1,_cmVO.getCmId());
ps.setInt(2,_cmVO.getCmLspId());
ps.setString(3,_cmVO.getCmUserId());
ps.setString(4,_cmVO.getCmPasswd());
ps.setString(5,_cmVO.getCmEmail());
ps.setString(6,_cmVO.getCmName());
ps.setInt(7,_cmVO.getCmStatus());
ps.setTimestamp(8,_cmVO.getCmStatusTS());
ps.setBigDecimal(9,_cmVO.getPricePerSMS());
ps.setBigDecimal(10,_cmVO.getPricePerMMS());
ps.setString(11,_cmVO.getCmAddrLine1());
ps.setString(12,_cmVO.getCmAddrLine2());
ps.setString(13,_cmVO.getCmCity());
ps.setString(14,_cmVO.getCmState());
ps.setString(15,_cmVO.getCmZipCode());
ps.setString(16,_cmVO.getCmTelNo());
ps.setString(17,_cmVO.getCmFax());
ps.setString(18,_cmVO.getCmCountryCode());
ps.setString(19,_cmVO.getCmContactFirstName());
ps.setString(20,_cmVO.getCmContactLastName());
ps.execute();
_con.commit();
}catch(Exception e){
logger.error("Error when insert the cmVO :"+e.getStackTrace());
throw e;
}
}
private void insertServiceCMTest(Connection _con ,ServiceCmVO _serviceCmVO)throws Exception{
try{
PreparedStatement ps = _con
.prepareStatement("insert into service_cm values(?,?,?)");
ps.setInt(1,_serviceCmVO.getServiceId());
ps.setInt(2,_serviceCmVO.getCmId());
ps.setInt(3,_serviceCmVO.getApprovalStatus());
ps.execute();
if(true){
throw (new Exception());
}//故意抛出异常
_con.commit();
}catch(Exception e){
throw e;
}
}
这样为什么第一个表的插入不能撤销?
如果将savepoint 作为参数传递给下面的两个方法,而且在两个方法捕捉到异常后各自回滚,就会报错说 have no such savepoint
修改后的两个方法分别为
private void insertCmVOTest(Connection _con ,CmVO _cmVO,Savepoint _savePoint)throws Exception{
try{
......
_con.commit();
}catch(Exception e){
_con.rollback(_savepoint);
}
}private void insertServiceCMTest(Connection _con ,ServiceCmVO _serviceCmVO,Savepoint _savepoint)throws Exception{ try{
...... _con.commit();
}catch(Exception e){
_con.rollback(_savepoint);
}
}请各位执教
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货