关于jdbc数据库连接池的问题?急!

[IwantFlay]

环境 tomcat5.0 win2000server jdk1.4.0
代码如下:
public boolean executeUserSql(Vector sqls)
{
if (sqls.isEmpty()) return(false);

boolean blnRet=false;

Connection myConn = getConn("wab"); //从数据库连接池中取得一个可用的连接
try
{
myConn.setAutoCommit(false); //这里出错,出错信息如最后所示:
Statement stmt = myConn.createStatement();

for (int i=0;i<sqls.capacity();i++)
{
sql = (String)sqls.elementAt(i);
System.out.println(sql+" ("+i+")");
stmt.executeUpdate(sql);
}

myConn.commit();
blnRet = true;
if (stmt != null) {stmt.close(); stmt = null;}
}
catch(Exception e)
{
blnRet = false;
try
{
myConn.rollback();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
System.out.println("ClsUseDBPool's executeUserSql(事务) err=="+e+"\nsql="+sql);
// e.printStackTrace();
}

freeConn("wab",myConn);

return(blnRet);
}
错误信息:
[Microsoft][SQLServer JDBC Driver] Can't start manual transaction mode because there are cloned connections.

请问有解决的方法吗?

[Tasia]

写出this只是为了更好的可读性。

[SnailLi]

你用的是不是sqlserver,如果是的话,sqlserver的驱动有一个特点,他的一个连接上同时只能有一个活动Statement,你的问题可能是你的那个连接之前使用的时候,有一个statement并没有手动释放,而是等着java的垃圾回收来做,而垃圾回收不是实时的,所以,你再次得到这个连接的时候就会说什么这是一个clone的连接.所以每次使用连接完后,尽量手工释放所有这个连接的资源.!!!

[SnailLi]

给你几个链接
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B313181
http://support.microsoft.com/default.aspx?scid=kb;EN-US;313220

[zhbw]

将
myConn.setAutoCommit(false);
修改为
myConn.setAutoCommit(true);
试试呢

[IwantFlay]

我要的效果就是能够实现事务功能,如果变成自动提交事务,那就无法人工控制事务了

[Tasia]

你在getConn()方法中获得一个Connection，再把它返回来。在Connection myConn = getConn("wab"); 这一句里myConn其实是得到一个Connection的拷贝引用，它是不能应用手动事务处理的。所以你手动设置提交方式时它出错。
解决方法是你把Connection对象作为这个类的一个类变量，在getConn方法中把设置这个变量的值，那么在其它方法中也可以直接用它，就不会有问题了。

[IwantFlay]

Tasia兄,你有简单的例子吗?如有,不甚感激

[j2nix]

Direct—The direct method sends the complete result set in one
request to the driver. It is useful for queries that only produce a
small amount of data that you fetch completely. You should avoid
using direct when executing queries that produce a large amount
of data, as the result set is cached completely on the client and
constrains memory. In this mode, each statement requires its own
connection to the database. This is accomplished by "cloning"
connections. Cloned connections use the same connection
properties as the original connection; however, because
transactions must occur on a single connection, auto commit
mode is required. Due to this, JTA is not supported in direct mode.
In addition, some operations, such as updating an insensitive
result set, are not supported in direct mode because the driver
must create a second statement internally. Exceptions generated
due to the creation of cloned statements usually return an error
message similar to “Cannot start a cloned connection while in
manual transaction mode.”

上面这一段是JDBC带的说明文档，也正说明了此问题。

[Tasia]

例子没有，不过我可以在这里简单地写一个给你。

public class DB{
Connection conn = null;
Statement stmt = null;

public void DBconn(){
... //这里连接数据库

conn = DBManager.getConnection();
stmt = conn.createStatement();
}

public ResultSet executeQuery(String sql){
try{
this.conn.setAutoCommit(false);
ResultSet rs = stmt.executeQuery();
this.conn.setAutoCommit(true);
return rs;
}catch(SQLException e){
//handle the exception
}
}
}

[IwantFlay]

I try it, thanks

[j2nix]

this.conn.setAutoCommit(false);
this.conn.setAutoCommit(true);

这里的this不用显式写出来的。