jboss connection pooling

Question

I have a question related to Prepared Steatement pooling (across all connections). Here's the config file

<datasources>
  <local-tx-datasource>
    <jndi-name>JNDI-NAME</jndi-name>
    <connection-url>jdbc:mysql://<server_name>/<database_name>?useServerPrepStmts=true</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>xxx</user-name>
    <password>xxxxx</password>
    <min-pool-size>10</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>20</idle-timeout-minutes>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
    <background-validation>true</background-validation>
    <background-validation-minutes>5</background-validation-minutes>
    <prepared-statement-cache-size>100</prepared-statement-cache-size>
    <share-prepared-statements>true</share-prepared-statements>
    <!-- sql to call when connection is created
    <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->
    <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers
    <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      -->
    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

It seems that this line:

<background-validation-minutes>5</background-validation-minutes> 

doesn't cause any problems with Prepared Statements, but:

<idle-timeout-minutes>20</idle-timeout-minutes>

causes that all connections are removed and re-created if there was no traffic for the last 20 minutes. Because of that existing Prepared Statements are removed from the pool of cached Prepared Statements. How to overcome this issue? I have to use idle-timeout-minutes because MySQL server closes the connection after 8h

Answer 1

Set idle timeout=480 minutes?

Answer 2

You must be using PreparedStatement for a wrong purpose. PreparedStament is mainly used to execute the same query multiple times with different arguments in a single flow. Once it's need is done, it should be closed shown below.

PreparedStatement pstmt = con.prepareStatement("insert into Emp(name) values(?)");
pstmt.setString(1, "foo");
int i1=stmt.executeUpdate();  // inserted one record
pstmt.setString(1, "bar");
int i2=stmt.executeUpdate(); // inserted 2nd record
pstmt.close(); // close prepared statement 
con.close();

It looks like, a CallableStatement is more relevant than PreparedStatement for your requirements.

Explore CallableStatement in jdbc here