如何在Apache Tomcat连接池的JDBC连接中使用SQL Server临时表？

问题背景 #

SQL Server本地临时表（#前缀）的生命周期绑定到创建它的连接：仅当连接保持打开且未被sp_reset_connection重置时，临时表才会存在。但执行以下代码时，第一个查询能确认临时表已创建，第二个查询却抛出#SomeTable不存在的SQLException：

sql = "create table #SomeTable (\n" +
      "    SomeID   int,\n" +
      "    SomeTime datetime,\n" +
      "    primary key (SomeID,SomeTime)\n" +
      ");\n" +
      "select * from tempdb.sys.tables order by name desc";

PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
// 处理结果集....

sql = "select * from #SomeTable";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
// 处理结果集....

排查已排除连接弃用超时（设置为10分钟），怀疑Tomcat连接池在PreparedStatement之间执行了sp_reset_connection，但未找到对应配置项控制此行为，需要实现复用连接执行多个依赖临时表的语句。

解决方案 #

1. 用事务包裹所有临时表操作 #

将连接的autoCommit设为false，在同一个事务内完成临时表的创建和查询操作，确保连接在整个过程中不会触发重置或清理：

con.setAutoCommit(false);
try {
    // 创建临时表并执行初始查询
    String createSql = "create table #SomeTable (\n" +
          "    SomeID   int,\n" +
          "    SomeTime datetime,\n" +
          "    primary key (SomeID,SomeTime)\n" +
          ");\n" +
          "select * from tempdb.sys.tables order by name desc";
    try (PreparedStatement createStmt = con.prepareStatement(createSql);
         ResultSet createRs = createStmt.executeQuery()) {
        // 处理结果集逻辑
    }

    // 查询临时表
    String querySql = "select * from #SomeTable";
    try (PreparedStatement queryStmt = con.prepareStatement(querySql);
         ResultSet queryRs = queryStmt.executeQuery()) {
        // 处理结果集逻辑
    }

    con.commit();
} catch (SQLException e) {
    con.rollback();
    throw e;
} finally {
    con.setAutoCommit(true);
}

2. 配置Tomcat连接池禁用连接重置 #

在Tomcat数据源配置中（如context.xml或server.xml），通过connectionProperties添加SQL Server驱动的resetConnection=false属性，阻止驱动执行sp_reset_connection；同时建议设置defaultAutoCommit=false避免自动提交导致的隐式重置：

<Resource name="jdbc/YourDatabase"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://your-db-server:1433;databaseName=your-db"
          username="db-user"
          password="db-pass"
          maxTotal="100"
          maxIdle="20"
          minIdle="5"
          connectionProperties="resetConnection=false"
          defaultAutoCommit="false"/>

3. 改用全局临时表（备选） #

如果不需要临时表仅对当前连接可见，可以改用全局临时表（##前缀），它会持续存在直到创建它的连接关闭且无其他连接在使用它。但需注意并发场景下的命名冲突问题，建议使用唯一命名规则。

内容的提问来源于stack exchange，提问作者joshp