sqlserver的jdbc驱动的SelectMethod=Cursor打开了服务器游标

连接数据库成功之后，想在一个事务中初始化多个预处理句柄时报错 dbConn.setAutoCommit(false) for (int i = 0; i < 5; i++) { pstmt[i] = dbConn.prepareStatement(strPreSQL[i]); 错误提示： java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Can't start manual transaction mode because there are cloned connections 怀疑MS SQL不能在一个事务中建多个预处理句柄 Resolution: You have to add a property to the pool definition, something to do with selectMode=cursor or selectMethod=cursor. Check the driver documentation. Otherwise the driver will not allow more than one statement per connection at any given time 微软的专家告诉的 This error occurs when you try to execute multiple statements against a SQL Server database with the JDBC driver while in manual transaction mode (AutoCommit=false) and while using the direct (SelectMethod=direct) mode. Direct mode is the default mode for the driver." import java.sql.*; import java.io.*; public class Repro{ public static void main(String args[]) {     try {       Connection con;       Statement s1 = null;       ResultSet r1 = null;       Statement s2 = null;       ResultSet r2 = null;       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");       con = DriverManager.getConnection(         "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs;SelectMethod=Direct;User=User;Password=Password");       //fix 1         //"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs;SelectMethod=Cursor;User=User;Password=Password");       con.setAutoCommit(false);             try {         s1 = con.createStatement();         r1 = s1.executeQuery("SELECT * FROM authors");                 //fix 2         //r1.close();         //s1.close();         s2 = con.createStatement();         r2 = s2.executeQuery("SELECT * FROM publishers");       }       catch (SQLException ex)       {         System.out.println(ex);              }         }     catch (Exception e)     {       e.printStackTrace();     } } } 用SQL Server驱动一次select很多数据最好在connection string中加上SelectMethod=Cursor,以利用服务器端游标加快速度,其实不只sqlserver,oracle的jdbc,只要使用PreparedStatement,驱动默认就使用游标,sqlserver则不然，必须使用SelectMethod=Cursor才打开游标。 这点在使用jotm时,并且使用Xapool时,必须修改DataSourceFactory，把PreparedStatementPool禁掉,否则记录插的太快了,很可能是游标没来得及关闭 即使不使用jotm,大量向oracle插入数据,例如每毫秒1条,也会引发游标用完,所以大量插入数据时,应该使用oracle的批处理batchupdate. 可惜的是,微软的sqlserver的jdbc驱动不支持这个属性

转载于:https://www.cnblogs.com/qg1019/archive/2010/10/15/1852021.html