遇到一个关于PreparedStatement的奇怪问题,
我的程序大致是这样的;
String sql1 = "select myid.nextval from dual";
执行sql1,从sequence拿到id。
然后执行update语句。
String sql = "insert into mytable1(id,content) values ("+ id +",empty_clob()");
try{ Connection con = ds.getConnection();
con.setAutoCommit(false);
try
{
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.excuteUpdate();
}
finally
{
log.debug("close begin!");
close(pstmt);
log.debug("close end!");
}
log.debug("begin commit!");
con.commit();}catch (SQLException e) {
try
{
con.rollback();
}
catch (SQLException e1)
{
log.error(e1.getMessage(), e);
} log.error(e.getMessage(), e);
throw new MyDBException(e.getMessage());
}
finally
{
close(con);
}奇怪的是报ORA-00001的错误,说id违反唯一约束,更奇怪的是close begin!和close end!打印出来了,但是begin commit!居然没打印出来!!
换成Statement则不会出现问题了,或者用PreparedStatement使用"insert into mytable1(id,content) values (?,empty_clob()",然后
setString的方式也不会出现问题。虽然问题解决了,但是我一点没想明白!我是在websphere 5.0 + oralce9i下发现这个问题的,奇怪的还有,我把websphere 启动打成可以debug,想调试一下程序,结果这个问题
就不会出现!邪门透了!请大家一定帮我看看,解我心头之惑,非常感谢!
我的程序大致是这样的;
String sql1 = "select myid.nextval from dual";
执行sql1,从sequence拿到id。
然后执行update语句。
String sql = "insert into mytable1(id,content) values ("+ id +",empty_clob()");
try{ Connection con = ds.getConnection();
con.setAutoCommit(false);
try
{
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.excuteUpdate();
}
finally
{
log.debug("close begin!");
close(pstmt);
log.debug("close end!");
}
log.debug("begin commit!");
con.commit();}catch (SQLException e) {
try
{
con.rollback();
}
catch (SQLException e1)
{
log.error(e1.getMessage(), e);
} log.error(e.getMessage(), e);
throw new MyDBException(e.getMessage());
}
finally
{
close(con);
}奇怪的是报ORA-00001的错误,说id违反唯一约束,更奇怪的是close begin!和close end!打印出来了,但是begin commit!居然没打印出来!!
换成Statement则不会出现问题了,或者用PreparedStatement使用"insert into mytable1(id,content) values (?,empty_clob()",然后
setString的方式也不会出现问题。虽然问题解决了,但是我一点没想明白!我是在websphere 5.0 + oralce9i下发现这个问题的,奇怪的还有,我把websphere 启动打成可以debug,想调试一下程序,结果这个问题
就不会出现!邪门透了!请大家一定帮我看看,解我心头之惑,非常感谢!
就好像用了StringBuffer还在append(a+b)每次都让我如此吐血!
public PreparedStatement prepareStatement(String sql)
throws SQLExceptionNote: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.
PrepareStatement在处理异常时会有影响。另外,再看一下参数说明:
Parameters:
sql - an SQL statement that may contain one or more '?' IN parameter placeholders 也许必须包含一个"?"啊!
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.set...(1,id);
connection.prepareStatement(String sql) 对参数sql是有要求的~~~
改成这样最好(基于你的sequence定义是全局的)
"insert into mytable1(id,content) values (myid.nextval ,empty_clob())";
好好弄一下,还有就是用了preparestatement就不要在组织sql语句了
sql - an SQL statement that may contain one or more '?' IN parameter placeholders something that MAY contain one or more '?'does never mean thatit MUST contain AT LEAST one '?'没有答应begin commit是因为里面的try-finally的try段抛出了SQLException,在执行了里面的finally之后,跳到了外面那个catch段。至于SQLException的原因,是主键重复。主键重复的原因是什么,从这段代码上,我没看出来