can Use Hibernate and Tomcat Connection pool at same time?

Question

I am developing a java web Application and I use Tomcat connection pooling, here is my setting:

<?xml version="1.0" encoding="UTF-8"?>
<Context path="" docBase="" debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/jdbcPool"
            auth="Container"
            type="javax.sql.DataSource"
            maxActive="100"
            maxIdle="30"
            maxWait="10000"
            username="root"
            password="*******"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>            
</Context>

and my DAO:

 public static Connection dbConnection() throws NamingException {
        Context initContext;
        DataSource ds = null;
        Connection conn = null;
        try {
            initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            ds = (DataSource) envContext.lookup("jdbc/jdbcPool");
            conn = ds.getConnection();        
        }catch (SQLException ex){
            logger.error("SQLException Occurred in DAO.dbConnection() Method, Exception Message is: " + ex.getMessage(), ex);
        }
        catch (RuntimeException er){
            logger.fatal("SQLException Occurred in DAO.dbConnection() Method, Exception Message is: " + er.getMessage(), er);
        }catch(Exception rt){
           logger.fatal("Exception Occurred in DAO.dbConnection() Method, Exception Message is: " + er.getMessage(), er);
        }
        return conn;
    }

I want to use hibernate so I refactor some part of my code, now I want to know is it possible for me us use both of them in my application (I mean some part of my code use hibernate and some part use my DAO connection?) If yes, what's gonna happen to those tables that not mapped with hibernate but some mapped tables have relation with them?

Answer 1

My personal preference with hibernate is is not to configure it with a connection pool at all. This can be done by simply omitting the connection pool settings in our hibernate configuration and using the openSession(Connection) method:

Connection conn = ... // get from jndi
Session session = sessionFactory.openSession(connection);
try{
   //do some work with either hte connection or the session or both
}finally{
   session.close();
   conn.close();
}

This has the advantage that you are in control of which connection is being used and where it is allocated, and most importantly where it is closed, this may be important if you are performing a transaction using hibernate and jdbc code.

EDIT: on @ChssPly76 point about excluding hibernates inbuilt transaction management, and he is quite right, hibernate provides reasonable transaction support and if a given a JTA will synchronise with any on going transaction. In a none JTA app where you require both hibernate and jdbc code to operate in the same jdbc transaction it is important to make sure that the hibernate Session is using the same Connection as the jdbc code, the best way to do this is to give the Connection to the session factory. Note this doesn't exclude using a Hibernate transaction object:

Connection conn = ... // get from jndi
Session session = sessionFactory.openSession(connection);
try{
   Transaction tx = new Transaction(); // 
   //do some work with either hte connection or the session or both
   tx.commit();
}finally{
   session.close();
   conn.close();
}

it'll work just fine.