java 数据库连接池配置问题

java 数据库连接池配置问题

问题描述：在Java中使用连接池链接mysql数据库， 有时候会遇到 The last packet successfully received from the server was ***  milliseconds ago.

问题分析：

MySQL Server default "wait_timeout" is 28,800 seconds or 8 hours, means that if a connection idle time exceed 8 hours, MySQL will automatically disconnect the connection, and connection pool is that the connection is still valid (because the school did not test the effectiveness of the connection), when the application when applying for the connection, it will lead to the above error.MYSQl 数据库默认等待时间是28800秒， 即8 小时， 如果超过8小时 没有数据库操作， mysql数据库会自动断开与连接池链接， 但是应用与连接池的链接仍然有效， 所以当应用链接数据库时 就会出现以上错误。

解决办法：

1. add ?autoReconnect=true to our jdbc URL, like jdbc:mysql://:3306/?autoReconnect=true

2. add below configuration condition to jndi file (使用JNDI的方式配置 Jboss).

 

<validation>
                        <check-valid-connection-sql>/*ping*/</check-valid-connection-sql>              
                        <background-validation>true</background-validation>
                         <background-validation-millis>5000</background-validation-millis>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>


<datasource jta="true" jndi-name="java:jboss/datasources/OCIRISEmailDB" pool-name="OCIRISEmailDBPool" enabled="true" use-ccm="true">
                    <connection-url>[url]?autoReconnect=true</connection-url>
                    <driver-class>com.mysql.jdbc.Driver</driver-class>
                    <driver>mysql</driver>
   <pool>
                        <min-pool-size>5</min-pool-size>
                        <max-pool-size>25</max-pool-size>
                        <prefill>true</prefill>
                        <use-strict-min>true</use-strict-min>
                        <flush-strategy>IdleConnections</flush-strategy>
                    </pool>
                    <security>
                        <user-name>intake</user-name>
                        <password>oayQUa8FMoFl</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>/*ping*/</check-valid-connection-sql>              
                        <background-validation>true</background-validation>
                         <background-validation-millis>5000</background-validation-millis>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                    <timeout>
                        <set-tx-query-timeout>false</set-tx-query-timeout>
                        <blocking-timeout-millis>0</blocking-timeout-millis>
                        <idle-timeout-minutes>2</idle-timeout-minutes>
                        <query-timeout>0</query-timeout>
                        <use-try-lock>0</use-try-lock>
                        <allocation-retry>0</allocation-retry>
                        <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
                    </timeout>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>