如何在 SQL 连接池中重置会话?

huangapple go评论81阅读模式
英文:

How to reset a session in a sql connection pool?

问题

我使用 javax.sql.DataSource 和 PostgreSQL JDBC 创建了一个连接池。整个过程很简单,我从连接池获取虚拟连接,对其进行处理,然后关闭虚拟连接(释放连接回连接池)。一切都运行正常。

但是,当我第二次获取相同的虚拟连接时,会话中仍然保留着上一次执行的相同数据。主要是在上一次执行中创建的临时表。我如何在每次执行后重置会话?或者您能否提供任何解决此问题的变通方法,而不使用类似于“如果不存在则创建临时表”的查询语法。

示例代码:

import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Base {

  public Connection getConnection() {
    Context ctx = (Context) new InitialContext().lookup("java:/comp/env");
    DataSource ds = (DataSource) ctx.lookup("database");
    Connection con = ds.getConnection();
    return con;
  }

  public Connection closeConnection(Connection con) {
    if (con != null || !con.isClosed()) {
      con.close();
    }
  }
}

context.xml:

<Resource 
  accessToUnderlyingConnectionAllowed="true" 
  auth="Container" 
  closeMethod="close" 
  driverClassName="org.postgresql.Driver" 
  maxTotal="50"
  maxWaitMillis="60000" 
  removeAbandonedOnBorrow="true"
  maxIdle="20" 
  name="Database" 
  password="p*******" 
  type="javax.sql.DataSource" 
  url="jdbc:postgresql://dbUrl/dbName" 
  username="dbUser" 
  validationQuery="select version();"
  validationQueryTimeout="60"
/>

使用示例:

con = getConnection();
CallableStatement st = con.prepareCall("{ doSomething()}");
st.execute();
// 进行处理
st.close();
con.close();

doSomething() 中,我有一个 plpgsql 函数,其中包括临时表的创建(名称为 t_table)、数据处理以及数组输出。当我从 Java 重复调用此函数时,第一次执行成功。但从第二次执行开始,会收到关于 t_table 已存在的错误消息。我认为使用连接池时,连接池中的连接是会话独立的,即从连接池中获取的每个新连接都会有一个新会话。PostgreSQL 的解决方法是删除特定的临时表。但从 Tomcat 方面是否有其他解决方案?

英文:

i have a connection pool using javax.sql.DataSource and PostgreSQL JDBC. The process is simple, I'm taking a virtual connection from connection pool, process it, then close the virtual connection (release the connection to connection pool). Everything works fine.

But when I get the same virtual connection second time the session have the same data from the previous execution. Mainly temp tables that created in the previous execution. How can I reset the session after each execution? Or can you suggest any work around to solve the issue without using creating temp table if not exists query syntax.

sample code

   import java.sql.Connection;
   import javax.naming.Context;
   import javax.naming.InitialContext;
   import javax.naming.NamingException;
   import javax.sql.DataSource;
    
    public class Base {
    
 
      public  Connection getConnection() {
        Context ctx = (Context) new InitialContext().lookup(&quot;java:/comp/env&quot;);
         DataSource ds = (DataSource) ctx.lookup(&quot;database&quot;);
        Connection con = ds.getConnection();
        return con;
      }

       public Connection closeConnection( Connection con){
         if (con != null || !con.isClosed()) {
                con.close();
            }
   

    }

context.xml =>

   &lt;Resource 
          accessToUnderlyingConnectionAllowed=&quot;true&quot; 
        auth=&quot;Container&quot; 
        closeMethod=&quot;close&quot; 
        driverClassName=&quot;org.postgresql.Driver&quot; 
        maxTotal=&quot;50&quot;
        maxWaitMillis=&quot;60000&quot; 
        removeAbandonedOnBorrow=&quot;true&quot;
        maxIdle=&quot;20&quot; 
        name=&quot;Database&quot; 
        password=&quot;p*******&quot; 
        type=&quot;javax.sql.DataSource&quot; 
        url=&quot;jdbc:postgresql://dbUrl/dbName&quot; 
        username=&quot;dbUser&quot; 
        validationQuery=&quot;select version();&quot;
       validationQueryTimeout=&quot;60&quot;
    /&gt;

// use case

con = getconnection()
CallableStatement st = con.prepareCall(&quot;{ doSomething()}&quot;);
st.execute()
//processing
st.close()
con.close()

in doSomething() plpgsql function i have a temp table creation (with name t_table), data processing and an array output. when i repeatedly call this from java first execution is success. But from the second execution message saying that t_table already exist is thrown. I thought that when using connection pool, the connections in the pool are session independent IE. every new connections from pool will have a new session. PostgreSQL solution is to drop that particular temp table . But there is no other solution for this from the tomcat side ?

答案1

得分: 1

The discard temporary 命令可用于清理会话。您可以将其配置为在连接返回到池时运行的验证查询。

对于 Tomcat JDBC 池,配置类似于:

validationQuery=&quot;discard temporary&quot;
testWhileIdle=&quot;false&quot;
testOnBorrow=&quot;false&quot;
testOnReturn=&quot;true&quot;

如果您想要更彻底地清理会话,可以考虑改用 discard all,但这需要启用自动提交,不确定是否适用于您。

如果不允许执行此类语句,您可以将其封装到一个函数中:

create function session_cleanup()
  returns boolean
as
$$
  discard temporary;
  select true;
$$
language sql;
validationQuery=&quot;select session_cleanup()&quot;
testWhileIdle=&quot;false&quot;
testOnBorrow=&quot;false&quot;
testOnReturn=&quot;true&quot;
英文:

The discard temporary command can be used to clean up a session.
You can configure that as a validation query to be run when the connection is returned to the pool.

With the Tomcat JDBC pool, this would be something like this:

validationQuery=&quot;discard temporary&quot;
testWhileIdle=&quot;false&quot;
testOnBorrow=&quot;false&quot;
testOnReturn=&quot;true&quot;

If you want to clean up the session even more, you can consider using discard all instead, but that requires autocommit to be enabled, not sure if that would work for you.

If such a statement isn't allowed, you can wrap it into a function:

create function session_cleanup()
  returns boolean
as
$$
  discard temporary;
  select true;
$$
language sql;
validationQuery=&quot;select session_cleanup()&quot;
testWhileIdle=&quot;false&quot;
testOnBorrow=&quot;false&quot;
testOnReturn=&quot;true&quot;

huangapple
  • 本文由 发表于 2020年10月7日 18:30:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/64242202.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定