select query inside loop - Database connections in JPA
1
In my Spring MVC application, I have @Transactional annotated method inside which I make call(select query) to DAO layer inside for loop.
If my input list size as "100" then I need to select 100 records from database. When the method inside the service layer is annotated with @Transactional, I thought selection of 100 records is a Unit of work and
I observe that each statement/call to DAO layer opens Connection.
I think it will impact the performance.
Questions :
Is it possible to open the DB connection once and use the same till the end
of all query(For Ex : 100 queries If the input list size is 100)?If I proceed in the current way of working will it be impacting the performance as it open the DB connection for every statements?
Service Implementation:
@Servive
public class MyServiceImpl implements MyService{
@Autowired
private MyDao myDao;
@Transactional
@Override
public List<Object> getRecords(Request req) {
List<String> inputList = new ArrayList<String>();
for(String input inputList) {
try {
Object recordFromDB = mydao.fetchRecordsFromDB(input);
} catch(NoResultFoundException e) {
LOGGER.error("NoResultException caught while making a call to DB TABLE1");
}
}
}
}
Dao Implementation:
@Repository
public class MyDaoImpl implements MyDao{
@PersistentContext
private EntityManager em;
@Override
public Object fetchRecordsFromDB(input ip) {
Query q = em.createNativeQuery("select col1, col2, col3 from TABLE1 where col = ?1 and col2 = ?2 and col3 = ?3");
Object recordsDB = null;
recordsDB = q.getSingleResult();
return recordsDB;
}
}
Logs :
As shown below DB connection is done for every statement.
09:35:56.815 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@7bd18d
09:35:56.947 TRACE o.h.s.i.AbstractServiceRegistryImpl - Initializing service [role=org.hibernate.stat.spi.StatisticsImplementor]
09:35:56.954 DEBUG o.h.s.internal.StatisticsInitiator - Statistics initialized [enabled=false]
09:35:56.955 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893568
09:35:56.963 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:56.964 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.243 TRACE o.h.engine.query.spi.QueryPlanCache - Unable to locate native-sql query plan in cache; generating (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.251 TRACE o.h.loader.custom.sql.SQLCustomQuery - Starting processing of sql query [SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1]
09:35:57.267 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.285 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.444 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue2 -> 2 [2]
09:35:57.452 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [ABC]
09:35:57.455 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue1 -> 1 [1]
09:35:57.455 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [XYZ]
09:35:57.456 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.521 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.528 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.528 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.530 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.532 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.532 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.532 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@1e6c7f5]
09:35:57.532 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.532 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.535 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.535 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.537 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 javax.persistence.NoResultException: No entity found for query
09:35:57.538 DEBUG o.s.o.j.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
09:35:57.538 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@14d6023
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893575
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.538 TRACE o.h.engine.query.spi.QueryPlanCache - Located native-sql query plan in cache (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.539 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.539 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 2 [2]
09:35:57.539 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [val2]
09:35:57.540 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 1 [1]
09:35:57.542 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [val1]
09:35:57.543 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.549 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.550 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.550 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.550 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.550 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@570f5f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.551 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.551 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.552 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.552 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 furtherjavax.persistence.NoResultException: No entity found for query
spring hibernate jpa jdbc transactions
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
add a comment |
1
In my Spring MVC application, I have @Transactional annotated method inside which I make call(select query) to DAO layer inside for loop.
If my input list size as "100" then I need to select 100 records from database. When the method inside the service layer is annotated with @Transactional, I thought selection of 100 records is a Unit of work and
I observe that each statement/call to DAO layer opens Connection.
I think it will impact the performance.
Questions :
Is it possible to open the DB connection once and use the same till the end
of all query(For Ex : 100 queries If the input list size is 100)?If I proceed in the current way of working will it be impacting the performance as it open the DB connection for every statements?
Service Implementation:
@Servive
public class MyServiceImpl implements MyService{
@Autowired
private MyDao myDao;
@Transactional
@Override
public List<Object> getRecords(Request req) {
List<String> inputList = new ArrayList<String>();
for(String input inputList) {
try {
Object recordFromDB = mydao.fetchRecordsFromDB(input);
} catch(NoResultFoundException e) {
LOGGER.error("NoResultException caught while making a call to DB TABLE1");
}
}
}
}
Dao Implementation:
@Repository
public class MyDaoImpl implements MyDao{
@PersistentContext
private EntityManager em;
@Override
public Object fetchRecordsFromDB(input ip) {
Query q = em.createNativeQuery("select col1, col2, col3 from TABLE1 where col = ?1 and col2 = ?2 and col3 = ?3");
Object recordsDB = null;
recordsDB = q.getSingleResult();
return recordsDB;
}
}
Logs :
As shown below DB connection is done for every statement.
09:35:56.815 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@7bd18d
09:35:56.947 TRACE o.h.s.i.AbstractServiceRegistryImpl - Initializing service [role=org.hibernate.stat.spi.StatisticsImplementor]
09:35:56.954 DEBUG o.h.s.internal.StatisticsInitiator - Statistics initialized [enabled=false]
09:35:56.955 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893568
09:35:56.963 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:56.964 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.243 TRACE o.h.engine.query.spi.QueryPlanCache - Unable to locate native-sql query plan in cache; generating (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.251 TRACE o.h.loader.custom.sql.SQLCustomQuery - Starting processing of sql query [SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1]
09:35:57.267 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.285 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.444 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue2 -> 2 [2]
09:35:57.452 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [ABC]
09:35:57.455 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue1 -> 1 [1]
09:35:57.455 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [XYZ]
09:35:57.456 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.521 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.528 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.528 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.530 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.532 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.532 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.532 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@1e6c7f5]
09:35:57.532 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.532 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.535 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.535 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.537 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 javax.persistence.NoResultException: No entity found for query
09:35:57.538 DEBUG o.s.o.j.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
09:35:57.538 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@14d6023
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893575
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.538 TRACE o.h.engine.query.spi.QueryPlanCache - Located native-sql query plan in cache (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.539 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.539 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 2 [2]
09:35:57.539 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [val2]
09:35:57.540 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 1 [1]
09:35:57.542 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [val1]
09:35:57.543 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.549 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.550 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.550 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.550 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.550 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@570f5f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.551 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.551 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.552 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.552 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 furtherjavax.persistence.NoResultException: No entity found for query
spring hibernate jpa jdbc transactions
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
add a comment |
1
1
1
2
In my Spring MVC application, I have @Transactional annotated method inside which I make call(select query) to DAO layer inside for loop.
If my input list size as "100" then I need to select 100 records from database. When the method inside the service layer is annotated with @Transactional, I thought selection of 100 records is a Unit of work and
I observe that each statement/call to DAO layer opens Connection.
I think it will impact the performance.
Questions :
Is it possible to open the DB connection once and use the same till the end
of all query(For Ex : 100 queries If the input list size is 100)?If I proceed in the current way of working will it be impacting the performance as it open the DB connection for every statements?
Service Implementation:
@Servive
public class MyServiceImpl implements MyService{
@Autowired
private MyDao myDao;
@Transactional
@Override
public List<Object> getRecords(Request req) {
List<String> inputList = new ArrayList<String>();
for(String input inputList) {
try {
Object recordFromDB = mydao.fetchRecordsFromDB(input);
} catch(NoResultFoundException e) {
LOGGER.error("NoResultException caught while making a call to DB TABLE1");
}
}
}
}
Dao Implementation:
@Repository
public class MyDaoImpl implements MyDao{
@PersistentContext
private EntityManager em;
@Override
public Object fetchRecordsFromDB(input ip) {
Query q = em.createNativeQuery("select col1, col2, col3 from TABLE1 where col = ?1 and col2 = ?2 and col3 = ?3");
Object recordsDB = null;
recordsDB = q.getSingleResult();
return recordsDB;
}
}
Logs :
As shown below DB connection is done for every statement.
09:35:56.815 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@7bd18d
09:35:56.947 TRACE o.h.s.i.AbstractServiceRegistryImpl - Initializing service [role=org.hibernate.stat.spi.StatisticsImplementor]
09:35:56.954 DEBUG o.h.s.internal.StatisticsInitiator - Statistics initialized [enabled=false]
09:35:56.955 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893568
09:35:56.963 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:56.964 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.243 TRACE o.h.engine.query.spi.QueryPlanCache - Unable to locate native-sql query plan in cache; generating (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.251 TRACE o.h.loader.custom.sql.SQLCustomQuery - Starting processing of sql query [SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1]
09:35:57.267 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.285 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.444 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue2 -> 2 [2]
09:35:57.452 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [ABC]
09:35:57.455 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue1 -> 1 [1]
09:35:57.455 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [XYZ]
09:35:57.456 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.521 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.528 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.528 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.530 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.532 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.532 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.532 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@1e6c7f5]
09:35:57.532 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.532 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.535 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.535 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.537 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 javax.persistence.NoResultException: No entity found for query
09:35:57.538 DEBUG o.s.o.j.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
09:35:57.538 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@14d6023
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893575
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.538 TRACE o.h.engine.query.spi.QueryPlanCache - Located native-sql query plan in cache (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.539 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.539 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 2 [2]
09:35:57.539 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [val2]
09:35:57.540 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 1 [1]
09:35:57.542 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [val1]
09:35:57.543 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.549 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.550 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.550 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.550 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.550 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@570f5f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.551 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.551 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.552 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.552 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 furtherjavax.persistence.NoResultException: No entity found for query
spring hibernate jpa jdbc transactions
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
In my Spring MVC application, I have @Transactional annotated method inside which I make call(select query) to DAO layer inside for loop.
If my input list size as "100" then I need to select 100 records from database. When the method inside the service layer is annotated with @Transactional, I thought selection of 100 records is a Unit of work and
I observe that each statement/call to DAO layer opens Connection.
I think it will impact the performance.
Questions :
Is it possible to open the DB connection once and use the same till the end
of all query(For Ex : 100 queries If the input list size is 100)?If I proceed in the current way of working will it be impacting the performance as it open the DB connection for every statements?
Service Implementation:
@Servive
public class MyServiceImpl implements MyService{
@Autowired
private MyDao myDao;
@Transactional
@Override
public List<Object> getRecords(Request req) {
List<String> inputList = new ArrayList<String>();
for(String input inputList) {
try {
Object recordFromDB = mydao.fetchRecordsFromDB(input);
} catch(NoResultFoundException e) {
LOGGER.error("NoResultException caught while making a call to DB TABLE1");
}
}
}
}
Dao Implementation:
@Repository
public class MyDaoImpl implements MyDao{
@PersistentContext
private EntityManager em;
@Override
public Object fetchRecordsFromDB(input ip) {
Query q = em.createNativeQuery("select col1, col2, col3 from TABLE1 where col = ?1 and col2 = ?2 and col3 = ?3");
Object recordsDB = null;
recordsDB = q.getSingleResult();
return recordsDB;
}
}
Logs :
As shown below DB connection is done for every statement.
09:35:56.815 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@7bd18d
09:35:56.947 TRACE o.h.s.i.AbstractServiceRegistryImpl - Initializing service [role=org.hibernate.stat.spi.StatisticsImplementor]
09:35:56.954 DEBUG o.h.s.internal.StatisticsInitiator - Statistics initialized [enabled=false]
09:35:56.955 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893568
09:35:56.963 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:56.964 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.243 TRACE o.h.engine.query.spi.QueryPlanCache - Unable to locate native-sql query plan in cache; generating (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.251 TRACE o.h.loader.custom.sql.SQLCustomQuery - Starting processing of sql query [SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1]
09:35:57.267 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.285 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.444 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.445 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue2 -> 2 [2]
09:35:57.452 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [ABC]
09:35:57.455 DEBUG org.hibernate.loader.Loader - bindNamedParameters() myValue1 -> 1 [1]
09:35:57.455 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [XYZ]
09:35:57.456 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.521 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.528 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.528 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@17509f]
09:35:57.530 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@18e6f11]
09:35:57.530 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.530 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.532 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.532 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.532 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@1e6c7f5]
09:35:57.532 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.532 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.535 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.535 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.537 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 javax.persistence.NoResultException: No entity found for query
09:35:57.538 DEBUG o.s.o.j.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
09:35:57.538 TRACE o.h.i.SessionFactoryImpl$SessionBuilderImpl - Opening Hibernate Session. tenant=null, owner=org.hibernate.jpa.internal.EntityManagerImpl@14d6023
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Opened session at timestamp: 15427893575
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting flush mode to: AUTO
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - Setting cache mode to: NORMAL
09:35:57.538 TRACE o.h.engine.query.spi.QueryPlanCache - Located native-sql query plan in cache (SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ?1 AND COL2 = ?2 AND ROWNUM = 1)
09:35:57.538 TRACE org.hibernate.internal.SessionImpl - SQL query: SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL1 = ? AND COL2 = ? AND ROWNUM = 1
09:35:57.539 DEBUG org.hibernate.SQL - _SELECT_COL1,COL2,_COL3 _FROM_TABLE1 _WHERE_COL1 = ? _AND COL2 = ? _AND ROWNUM = 1
09:35:57.539 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering last query statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.539 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 2 [2]
09:35:57.539 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [val2]
09:35:57.540 DEBUG org.hibernate.loader.Loader - bindNamedParameters() val2-> 1 [1]
09:35:57.542 TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [val1]
09:35:57.543 TRACE org.hibernate.loader.Loader - Bound [3] parameters total
09:35:57.549 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE org.hibernate.loader.Loader - Processing result set
09:35:57.550 TRACE org.hibernate.loader.Loader - Done processing result set (0 rows)
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing result set [org.apache.commons.dbcp.DelegatingResultSet@1950c3f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Closing prepared statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@1db1a33]
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
09:35:57.550 TRACE o.h.e.i.StatefulPersistenceContext - Initializing non-lazy collections
09:35:57.550 DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
09:35:57.550 TRACE org.hibernate.internal.SessionImpl - Closing session
09:35:57.550 TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@570f5f]
09:35:57.550 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.551 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Closing logical connection
09:35:57.551 TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Releasing JDBC resources
09:35:57.552 TRACE o.h.r.j.i.LogicalConnectionManagedImpl - Logical connection closed
09:35:57.552 ERROR c.c.a.s.i.MyServiceImpl - ----NoResultException caught while making a call to DB TABLE1 furtherjavax.persistence.NoResultException: No entity found for query
spring hibernate jpa jdbc transactions
spring hibernate jpa jdbc transactions
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
edited Nov 21 '18 at 16:42
Alagammal P
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
asked Nov 21 '18 at 14:51
Alagammal PAlagammal P
102518
102518
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414676%2fselect-query-inside-loop-database-connections-in-jpa%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414676%2fselect-query-inside-loop-database-connections-in-jpa%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
