最近在写jdbc程序,遇到了事务的问题void fun1() throws Exception{
.....
try{
conn.setAutoCommit(false);
......
fun2();
.....
conn.commit();
}catch(Exception e){
conn.rollback();
throw e;
}
}
void fun2() throws Exception{
.....
try{
conn.setAutoCommit(false);
......
conn.commit();
}catch(Exception e){
conn.rollback();
throw e;
}
}
这两个Connection不是同一个
这样做会死锁,因为事务是不能嵌套的
那怎样实现这种效果呢?可以在同一个线程中共享一个Connnection,通过Savepoint实现void fun1() throws Exception{
Savepoint a;
try{
conn=getConnection(); //这个Connection从当前线程取,如果当前线程没有就新创建一个
Savepoint a = conn.setSavepoint();
....
fun2();
...
}catch(Exception e){
conn.rollback(a);
throw e;
}
}
void fun2() throws Exception{
Savepoint a;
try{
conn=getConnection(); //这个Connection从当前线程取,如果当前线程没有就新创建一个
Savepoint a = conn.setSavepoint();
...
}catch(Exception e){
conn.rollback(a);
throw e;
}
}问题是如何将Connection对象放入当前线程中?
在Hibernate中一个SessionFactory对象sessionFactory.getCurrentSession()总获得当前线程的session,如果当前线程中有session的话,每有就创建一个新的,尽管所有线程都调用同一个sessionFactory
context.getConnection获得环境上下文中的Connection,如果没有,就创建一个
把新创建的放入到context中,这样同一个线程中的数据库的访问都在同一个Connection,
然后利用Savepoint来进行代码的嵌套
里面创建一些connection
然后建立一个Filter,用ThreadLocal把定义类里的一个connection取出来
然后在filter里doFilter方法中Chain.doFilter(request, response)后关闭这个
connection(一般不是真正的关闭,而是重新放入池中)事务可以在取出时开启,关闭前提交等等
java.sql.SQLException: No operations allowed after connection closed.ConnectionUtil部分代码public static Connection getConnection() throws SQLException {
Connection conn = (Connection)session.get(); //session是ThreadLocal实例
if(conn==null){
conn = DriverManager.getConnection(info.getProperty("uri"),
info.getProperty("user"), info.getProperty("password"));
conn.setAutoCommit(false);
session.set(conn);
}
return conn;
}
public static void commit(){
Connection conn = (Connection)session.get();
if(conn!=null){
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}过滤器的doFilter方法public void doFilter(ServletRequest req, ServletResponse res,
FilterChain chain) throws IOException, ServletException {
chain.doFilter(req, res);
ConnectionUtil.commit();
}一个业务逻辑代码public void importGoods(String id, int quantity) throws SQLException {
Connection conn = null;
PreparedStatement pstat = null;
Savepoint s = null;
try {
conn = ConnectionUtil.getConnection();
s = conn.setSavepoint();
pstat = conn.prepareStatement("UPDATE GOODS SET QUANTITY=QUANTITY+? WHERE ID=?");
pstat.setInt(1, quantity);
pstat.setString(2, id);
int count = pstat.executeUpdate();
if (count == 0) {
throw new SQLException("进货发生异常");
}
System.out.println("import a goods"); } catch (SQLException e) {
try {
conn.rollback(s);
} catch (SQLException e1) {
e1.printStackTrace();
}
throw e;
} finally {
ConnectionUtil.closePStatement(pstat);
}
}
Connection conn = (Connection)session.get();
if(conn!=null){
session.remove();
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}