C3P0连接池内连接有效性配置

作者: Jorvi | 来源:发表于2020-03-19 18:29 被阅读0次

C3P0连接池内连接有效性配置
JDBC C3P0数据库连接池
不同的数据库连接池(DBCP,C3P0,Druid,Hikari
c3p0连接池与dbcp连接池的区别：
Spring配置c3p0连接池和dao使用jdbcTemplat
C3p0连接mysql，超时问题
Spring-005-jdbcTemplete配置连接池和dao
（四）配置池大小和连接的年龄
配置_连接池的通用配置
JDBC常见开源的连接池

如果MySQL连接池内连接失效，继续读写会出现异常：

20/03/18 09:16:30,063 com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
20/03/18 09:16:30,063   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
20/03/18 09:16:30,063   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
20/03/18 09:16:30,063   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
20/03/18 09:16:30,063   at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
20/03/18 09:16:30,063   at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
20/03/18 09:16:30,063   at com.mysql.jdbc.Util.getInstance(Util.java:386)
20/03/18 09:16:30,063   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
20/03/18 09:16:30,063   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
20/03/18 09:16:30,063   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
20/03/18 09:16:30,063   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
20/03/18 09:16:30,063   at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1311)
20/03/18 09:16:30,063   at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1303)
20/03/18 09:16:30,063   at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4521)
20/03/18 09:16:30,063   at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4486)
20/03/18 09:16:30,063   at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:567)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.DStream$$anonfun$foreachRDD$1$$anonfun$apply$mcV$sp$3.apply(DStream.scala:630)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.DStream$$anonfun$foreachRDD$1$$anonfun$apply$mcV$sp$3.apply(DStream.scala:630)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.ForEachDStream$$anonfun$1$$anonfun$apply$mcV$sp$1.apply$mcV$sp(ForEachDStream.scala:51)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.ForEachDStream$$anonfun$1$$anonfun$apply$mcV$sp$1.apply(ForEachDStream.scala:51)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.ForEachDStream$$anonfun$1$$anonfun$apply$mcV$sp$1.apply(ForEachDStream.scala:51)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.DStream.createRDDWithLocalProperties(DStream.scala:415)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.ForEachDStream$$anonfun$1.apply$mcV$sp(ForEachDStream.scala:50)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.ForEachDStream$$anonfun$1.apply(ForEachDStream.scala:50)
20/03/18 09:16:30,063   at org.apache.spark.streaming.dstream.ForEachDStream$$anonfun$1.apply(ForEachDStream.scala:50)
20/03/18 09:16:30,063   at scala.util.Try$.apply(Try.scala:192)
20/03/18 09:16:30,063   at org.apache.spark.streaming.scheduler.Job.run(Job.scala:39)
20/03/18 09:16:30,063   at org.apache.spark.streaming.scheduler.JobScheduler$JobHandler$$anonfun$run$1.apply$mcV$sp(JobScheduler.scala:256)
20/03/18 09:16:30,063   at org.apache.spark.streaming.scheduler.JobScheduler$JobHandler$$anonfun$run$1.apply(JobScheduler.scala:256)
20/03/18 09:16:30,063   at org.apache.spark.streaming.scheduler.JobScheduler$JobHandler$$anonfun$run$1.apply(JobScheduler.scala:256)
20/03/18 09:16:30,064   at scala.util.DynamicVariable.withValue(DynamicVariable.scala:58)
20/03/18 09:16:30,064   at org.apache.spark.streaming.scheduler.JobScheduler$JobHandler.run(JobScheduler.scala:255)
20/03/18 09:16:30,064   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
20/03/18 09:16:30,064   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
20/03/18 09:16:30,064   at java.lang.Thread.run(Thread.java:748)
20/03/18 09:16:30,064 Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

解决方案：
增加配置：

    // 异步检测连接有效性
    dataSourceRead.setTestConnectionOnCheckin(true)
    // 检测连接有效性的间隔(秒)
    dataSourceRead.setIdleConnectionTestPeriod(3600)

官网配置：

testConnectionOnCheckin

Default: false

If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid. Use in combination with <tt>idleConnectionTestPeriod</tt> for quite reliable, always asynchronous Connection testing. Also, setting an <tt>automaticTestTable</tt> or <tt>preferredTestQuery</tt> will usually speed up all connection tests. [See "Configuring Connection Testing"]

idleConnectionTestPeriod

Default: 0

If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds. [See "Configuring Connection Testing"]

automaticTestTable

Default: null

If provided, c3p0 will create an empty table of the specified name, and use queries against that table to test the Connection. If <tt>automaticTestTable</tt> is provided, c3p0 will generate its own test query, therefore any <tt>preferredTestQuery</tt> set will be ignored. You should not work with the named table after c3p0 creates it; it should be strictly for c3p0's use in testing your Connection. (If you define your own ConnectionTester, it must implement the QueryConnectionTester interface for this parameter to be useful.) [See "Configuring Connection Testing"]