I have servlet that return data in json. I try to execute few querys in loop. If array of querys contain less then 5 querys it work well. But if i try to execute 10 querys in loop it stacked and return NullPointerExceprion. here my code
private JSONArray getQueryResultJSONById(String queryId, String formName, JSONObject paramsJSON) {
String sql = getQuery(queryId, formName);
if (sql != null) {
try {
Connector connector = new Connector(Defaults.DS_DEFAULT);
connector.prepareStatement(sql);
connector.setPreparedJSONParams(paramsJSON);
connector.executePrepared();
JSONArray dbrs = ResultSetConverter.convertToJSON(connector.getPreparedSelect());
connector.close();
return dbrs;
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
Connector class:
public void prepareStatement(String query) {
try {
this.sql = query;
if (connection.isClosed()) {
connection = dataSource.getConnection();
}
preparedStatement = connection.prepareStatement(query);
} catch (Exception e) {
logException(e);
}
}
public void setPreparedJSONParams(JSONObject paramsJSON) {
if (paramsJSON != null) {
JSONArray array = paramsJSON.getJSONArray("params");
for (int i = 0; i < array.length(); i++) {
String paramValue = array.getString(i);
if (NULL.equals(paramValue)) {
setPreparedParam(i + 1, (String) null);
} else {
setPreparedParam(i + 1, array.getString(i));
}
}
}
}
public boolean executePrepared() {
try {
preparedStatement.executeUpdate();
return true;
} catch (Exception e) {
logException(e);
return false;
}
}
public ResultSet getPreparedSelect() {
ResultSet resultSet = null;
try {
resultSet = preparedStatement.executeQuery();
} catch (Exception e) {
logException(e);
}
return resultSet;
}
public void close() {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (Exception e) {
logException(e);
}
}
in loop i try do like this to execute querys:
queryRs = new JSONArray(uiQueryService.getQueryResultById(querys.get(i), "REPORT", params.toString()));
I tried to debug this place to see what request produce the problem, but in debugger each of request work well and result successfully returns. I have no any idea how to resolve the problem
exception :
J2CA0045E: Во время вызова метода createOrWaitForConnection для ресурса jdbc/Oep/NonXaDataSourceWeb соединение недоступно.
[18.03.19 13:08:33:025 MSK] 0000f40e TaskUtils$Log E org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler handleError Unexpected error occurred in scheduled task.
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:243)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:420)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:257)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy158.pollDiagnosisLock(Unknown Source)
at sun.reflect.GeneratedMethodAccessor209.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
at java.lang.reflect.Method.invoke(Method.java:620)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:483)
at java.util.concurrent.FutureTask.run(FutureTask.java:274)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:190)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1157)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:627)
at java.lang.Thread.run(Thread.java:809)
Caused by: com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
at com.ibm.ws.rsadapter.AdapterUtil.toSQLException(AdapterUtil.java:1680)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:661)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:611)
at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
at java.lang.reflect.Method.invoke(Method.java:620)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at com.sun.proxy.$Proxy123.getConnection(Unknown Source)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:205)
... 20 more
Caused by: com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1783)
at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3874)
at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3094)
at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1548)
at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:1031)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:644)
... 28 more
Beside the answer by Aaron Digulla there is one more thing to configure if you want to connect more JDBC connections concurrently. You can set Maximum connections property for your datasource on Websphere application server:
Resources > JDBC > Data Sources > data_source > [Additional Properties] Connection pool properties
This set maximum number of JDBC connections in pool. Please see more here: https://www.ibm.com/support/knowledgecenter/en/SSAW57_8.5.5/com.ibm.websphere.nd.multiplatform.doc/ae/udat_conpoolset.html
But still you have to have good management of connection releasing to avoid usage of all connections (so avoid blocking resources by not used connections).