savepoint mysql 使用_Java-java 中如何使用mysql的 savepoint 功能

看了诸位的回复,我写了一个貌似能用的类,这样的代码

package com.ninedresses.competitordas.dao;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Savepoint;

import java.util.HashMap;

import org.apache.commons.dbutils.DbUtils;

import org.apache.commons.dbutils.QueryRunner;

/**

* 抽象的 jdbc 操作类,支持事务

*

* @author xuxh

*

*/

public class QueryExecutor {

private QueryRunner queryRunner;

private Connection conn;

private int trans_count = 0;

private boolean has_failed_query=false;

private HashMap savepointList;

public QueryExecutor(QueryRunner queryRunner){

this.queryRunner = queryRunner;

this.trans_count = 0;

}

public void init() throws SQLException {

this.conn = queryRunner.getDataSource().getConnection();

this.savepointList = new HashMap();

}

public void startTrans() throws SQLException{

if (0 == this.trans_count){

conn.setAutoCommit(false);

this.has_failed_query = false;

}

this.trans_count ++;

if (this.trans_count > 1){

String key = QueryExecutor.getSavepointKey(this.trans_count);

Savepoint savepoint = conn.setSavepoint(key);

this.savepointList.put(key, savepoint);

}

}

/**

* 完成事务，根据查询是否出错决定是提交事务还是回滚事务

*

* 如果 commitOnNoErrors 参数为 true，当事务中所有查询都成功完成时，则提交事务，否则回滚事务

* 如果 commitOnNoErrors 参数为 false，则强制回滚事务

*

* @param commitOnNoErrors 指示在没有错误时是否提交事务

* @throws SQLException

*/

public void completeTrans(boolean commitOnNoErrors) throws SQLException {

if (this.trans_count > 0){

this.trans_count --;

if (this.trans_count > 0){

String key = QueryExecutor.getSavepointKey(this.trans_count);

Savepoint savepoint = this.savepointList.get(key);

this.savepointList.remove(key);

if (null != savepoint){

conn.rollback(savepoint);

savepoint = null;

}

}else {

if (this.has_failed_query == false && commitOnNoErrors) {

conn.commit();

} else {

conn.rollback();

}

}

}

}

static String getSavepointKey(int sid){

return String.format("savepoint-%d", sid);

}

/**

* 强制指示在调用 completeTrans() 时回滚事务

*/

public void failTrans()

{

this.has_failed_query = true;

}

/**

* 返回事务是否失败的状态

*/

public boolean hasFailedTrans()

{

return this.has_failed_query;

}

public void destroy(){

DbUtils.closeQuietly(conn);

conn = null;

savepointList = null;

queryRunner = null;

}

public QueryRunner getQueryRunner() {

return queryRunner;

}

}

使用demo:

QueryExecutor qe = new QueryExecutor(Connecter.getQueryRunner());

qe.init();

qe.startTrans();

qe.getQueryRunner().update(SQL_FOR_SET_FINGERPRINT_MATCHING,product.getId());

Date now = new Date();

for(String samelocal : samelocalArray){

if ( !"".equals(samelocal)){

qe.getQueryRunner().update(SQL_FOR_SET_SAMELOCAL, product.getId(),samelocal,SqlHelper.dbTimeStamp(now));

}

}

qe.completeTrans(true);

qe.destroy();

qe = null;

now = null;