wwwwwvwwwww 2008-10-31 22:00
浏览 253
已采纳

JDBC事务难题

大牛们 ,,,有一段加了JDBC事务的方法



public boolean updateStuAcc(int stuID, List accL, int schID) {

boolean b = true;

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

String sql1 = "select * from stuAccounts where stuID!=? and Account=?";

String sql2 = "update stuAccounts set Account=? where pactID=?";

String sql3 = "update [" + schID

+ "_StuRefundInfo] set account=? where pactID=? ";

try {

conn = DBCon.getCon();

conn.setAutoCommit(false);

for (int i = 0; i < accL.size(); i++) {

StuUpdateAccDto suaDto = (StuUpdateAccDto) accL.get(i);

ps = conn.prepareStatement(sql1);

ps.setInt(1, stuID);

ps.setString(2, suaDto.getAccount());

rs = ps.executeQuery();

if (rs.next()) {

b = false;

break;

} else {

ps = conn.prepareStatement(sql2);

ps.setString(1, suaDto.getAccount());

ps.setString(2, suaDto.getPactID());

ps.executeUpdate();

ps = conn.prepareStatement(sql3);

ps.setString(1, suaDto.getAccount());

ps.setString(2, suaDto.getPactID());

ps.execute();

}

}

conn.commit();

} catch (SQLException e) {

b = false;

try {

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

} finally {

try {

if (rs != null) {

rs.close();

}

if (ps != null) {

ps.close();

}

if (conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

return b;

}

public boolean updateStuAcc(int stuID, List accL, int schID) {
boolean b = true;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql1 = "select * from stuAccounts where stuID!=? and Account=?";
String sql2 = "update stuAccounts set Account=? where pactID=?";
String sql3 = "update [" + schID
+ "_StuRefundInfo] set account=? where pactID=? ";
try {
conn = DBCon.getCon();
conn.setAutoCommit(false);
for (int i = 0; i < accL.size(); i++) {
StuUpdateAccDto suaDto = (StuUpdateAccDto) accL.get(i);
ps = conn.prepareStatement(sql1);
ps.setInt(1, stuID);
ps.setString(2, suaDto.getAccount());
rs = ps.executeQuery();
if (rs.next()) {
b = false;
break;
} else {
ps = conn.prepareStatement(sql2);
ps.setString(1, suaDto.getAccount());
ps.setString(2, suaDto.getPactID());
ps.executeUpdate();
ps = conn.prepareStatement(sql3);
ps.setString(1, suaDto.getAccount());
ps.setString(2, suaDto.getPactID());
ps.execute();
}
}
conn.commit();
} catch (SQLException e) {
b = false;
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return b;
}





该方法有时候会抛出这样的异常







at java.lang.Thread.run(Thread.java:595)

com.microsoft.sqlserver.jdbc.SQLServerException: 事务(进程 ID 54)与另一个进程被

死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError

(Unknown Source)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown

Source)

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep

aredStatement(Unknown Source)

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecC

md.doExecute(Unknown Source)

        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unkno

wn Source)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknow

n Source)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unkn

own Source)

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(

Unknown Source)

        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(

DelegatingPreparedStatement.java:93)

        at dao.StuAllInfoUDao.updateStuAcc(StuAllInfoUDao.java:33)

        at workerServlet.SchUpdateStuInfoServlet.doPost(SchUpdateStuInfoServlet.

java:59)





  • 写回答

2条回答 默认 最新

  • Chris-Hsu 2008-11-03 11:42
    关注

    你可以尝试着这样做一下:

    ...........
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;
    PreparedStatement ps3 = null;

    ...............

    try {

    conn = DBCon.getCon();

    conn.setAutoCommit(false);

    for (int i = 0; i < accL.size(); i++) {

    StuUpdateAccDto suaDto = (StuUpdateAccDto) accL.get(i);

    ps1 = conn.prepareStatement(sql1);

    ps1.setInt(1, stuID);

    ps1.setString(2, suaDto.getAccount());

    rs = ps1.executeQuery();

    if (rs.next()) {

    b = false;

    break;

    } else {

    ps2 = conn.prepareStatement(sql2);

    ps2.setString(1, suaDto.getAccount());

    ps2.setString(2, suaDto.getPactID());

    ps2.executeUpdate();

    ps3 = conn.prepareStatement(sql3);

    ps3.setString(1, suaDto.getAccount());

    ps3.setString(2, suaDto.getPactID());

    ps3.execute();

    }

    }

    conn.commit();

    } catch (SQLException e) {

    b = false;

    try {

    conn.rollback();

    } catch (SQLException e1) {

    e1.printStackTrace();

    }

    e.printStackTrace();

    } finally {

    try {

    if (rs != null) {

    rs.close();

    }

    if (ps1 != null) {

    ps1.close();

    }
    if (ps2 != null) {

    ps2.close();

    }

                if (conn != null) {      
                    conn.close();      
                }      
            } catch (SQLException e) {      
                e.printStackTrace();      
            }      
        }   
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题