select query inside loop - Database connections in JPA

Question

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 :

1. 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)?

2. 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