当JPA获取数据库数据连接时，如果连接数超过最大连接数的配置，系统就会报错：
Unable to acquire JDBC Connection和：
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30002ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676) ~[HikariCP-3.2.0.jar:?]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190) ~[HikariCP-3.2.0.jar:?]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155) ~[HikariCP-3.2.0.jar:?]
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.2.0.jar:?]
at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke(<generated>) ~[HikariCP-3.2.0.jar:?]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]
at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$5e528ef9.getConnection(<generated>) ~[HikariCP-3.2.0.jar:?]
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1984) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1914) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2689) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2672) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.list(Loader.java:2501) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
... 64 more连接数的配置：
spring:
datasource:
hikari:
minimum-idle: 1
maximum-pool-size: 5如果不配置的话，默认都是10.
我们使用entitymanager进行查询和其他操作时，调用这个方法org.springframework.orm.jpa.SharedEntityManagerCreator.DeferredQueryInvocationHandler#invoke，红色代码是会数据库连接池进行进行释放。SharedEntityManagerCreator这个类是这个关键，有兴趣可以重点看一下。
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
// Invocation on Query interface coming in...
if (method.getName().equals("equals")) {
// Only consider equal when proxies are identical.
return (proxy == args[0]);
}
else if (method.getName().equals("hashCode")) {
// Use hashCode of EntityManager proxy.
return hashCode();
}
else if (method.getName().equals("unwrap")) {
// Handle JPA 2.0 unwrap method - could be a proxy match.
Class<?> targetClass = (Class<?>) args[0];
if (targetClass == null) {
return this.target;
}
else if (targetClass.isInstance(proxy)) {
return proxy;
}
}
else if (method.getName().equals("getOutputParameterValue")) {
if (this.entityManager == null) {
Object key = args[0];
if (this.outputParameters == null || !this.outputParameters.containsKey(key)) {
throw new IllegalArgumentException("OUT/INOUT parameter not available: " + key);
}
Object value = this.outputParameters.get(key);
if (value instanceof IllegalArgumentException) {
throw (IllegalArgumentException) value;
}
return value;
}
}
// Invoke method on actual Query object.
try {
Object retVal = method.invoke(this.target, args);
if (method.getName().equals("registerStoredProcedureParameter") && args.length == 3 &&
(args[2] == ParameterMode.OUT || args[2] == ParameterMode.INOUT)) {
if (this.outputParameters == null) {
this.outputParameters = new LinkedHashMap<>();
}
this.outputParameters.put(args[0], null);
}
return (retVal == this.target ? proxy : retVal);
}
catch (InvocationTargetException ex) {
throw ex.getTargetException();
}
finally {
if (queryTerminatingMethods.contains(method.getName())) {
// Actual execution of the query: close the EntityManager right
// afterwards, since that was the only reason we kept it open.
if (this.outputParameters != null && this.target instanceof StoredProcedureQuery) {
StoredProcedureQuery storedProc = (StoredProcedureQuery) this.target;
for (Map.Entry<Object, Object> entry : this.outputParameters.entrySet()) {
try {
Object key = entry.getKey();
if (key instanceof Integer) {
entry.setValue(storedProc.getOutputParameterValue((Integer) key));
}
else {
entry.setValue(storedProc.getOutputParameterValue(key.toString()));
}
}
catch (IllegalArgumentException ex) {
entry.setValue(ex);
}
}
}
EntityManagerFactoryUtils.closeEntityManager(this.entityManager);
this.entityManager = null;
}
}
}
如果我们使用@Transactional注解时，就不是上会的代码来释放连接池的，是下面的代码：
org.springframework.orm.jpa.JpaTransactionManager#doCleanupAfterCompletion
@Override
protected void doCleanupAfterCompletion(Object transaction) {
JpaTransactionObject txObject = (JpaTransactionObject) transaction;
// Remove the entity manager holder from the thread, if still there.
// (Could have been removed by EntityManagerFactoryUtils in order
// to replace it with an unsynchronized EntityManager).
if (txObject.isNewEntityManagerHolder()) {
TransactionSynchronizationManager.unbindResourceIfPossible(obtainEntityManagerFactory());
}
txObject.getEntityManagerHolder().clear();
// Remove the JDBC connection holder from the thread, if exposed.
if (getDataSource() != null && txObject.hasConnectionHolder()) {
TransactionSynchronizationManager.unbindResource(getDataSource());
ConnectionHandle conHandle = txObject.getConnectionHolder().getConnectionHandle();
if (conHandle != null) {
try {
getJpaDialect().releaseJdbcConnection(conHandle,
txObject.getEntityManagerHolder().getEntityManager());
}
catch (Exception ex) {
// Just log it, to keep a transaction-related exception.
logger.error("Could not close JDBC connection after transaction", ex);
}
}
}
getJpaDialect().cleanupTransaction(txObject.getTransactionData());
// Remove the entity manager holder from the thread.
if (txObject.isNewEntityManagerHolder()) {
EntityManager em = txObject.getEntityManagerHolder().getEntityManager();
if (logger.isDebugEnabled()) {
logger.debug("Closing JPA EntityManager [" + em + "] after transaction");
}
EntityManagerFactoryUtils.closeEntityManager(em);
}
else {
logger.debug("Not closing pre-bound JPA EntityManager after transaction");
}
}
这是两种逻辑，如果我们在@Transactional注解的方法会使用em的方法进行数据库的操作的话，会采用第二种方式来释放，
如果不加@Transactional注解的话，采用第一种方法来释放。
所以为了避免发现特殊情况，出现不释放连接池的情况，可以采用加@Transactional注解的方式 处理，如下面的代码：
@Transactional
@RequestMapping("/search")
public List search() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<BillDO> root = query.from(BillDO.class);
List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(root.get(BillDO.Fields.hisId),"3035-M6748091-1695675075-1"));
query.where(Iterables.toArray(predicates, Predicate.class));
query.multiselect(Arrays.asList(
root.get(BillDO.Fields.hisId),
root.get(BillDO.Fields.hospitalName),
root.get(BillDO.Fields.hospitalId),
root.get(BillDO.Fields.id),
root.get(BillDO.Fields.diseaseName)
));
TypedQuery<Tuple> typedQuery = entityManager.createQuery(query);
// Query unwrap = typedQuery.unwrap(Query.class);
QueryImpl unwrap = typedQuery.unwrap(QueryImpl.class);
Query query1 = unwrap.setResultTransformer(
new CisAliasToBeanResultTransformer(BillDO.class, query.getSelection()));
List resultList = query1.list();
return resultList;
}
如果不加@Transactional注解的话，那么连接池将不会释放，连接数会一直增加，直到超过最大连接数的配置。
可以通过下面的配置，来观察hikari数据库连接连接池的变化：
logging:
level:
root: warn
com:
zaxxer:
hikari: trace
这个代码是Hikari回收连接的：com.zaxxer.hikari.pool.HikariPool#recycle
@Override
void recycle(final PoolEntry poolEntry)
{
metricsTracker.recordConnectionUsage(poolEntry);
connectionBag.requite(poolEntry);
}
