Not able to obtain connection in hibernate 3

ruchiahuja10 · **Joined:** Thu Jun 02, 2005 5:03 am **Posts:** 15

I have recently upgraded to hibernate 3.0.5. I am getting the following exception on running query. It was working in hibernate 2.1.6

Code:

org.hibernate.HibernateException: Not able to obtain connection
   at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:113)
   at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
   at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
   at org.hibernate.loader.Loader.doQuery(Loader.java:390)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   at org.hibernate.loader.Loader.list(Loader.java:1563)
   at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:890)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

My code is

Code:

Query query = (Query) this.getHibernateTemplate().execute(new HibernateCallback()
{
   public Object doInHibernate(Session session)
   throws HibernateException
   {
      return session.createQuery(sql);
   }
});
query.setString(0, "TEST1");
query.setBigDecimal(   1, 1000); 
query.setString(2, "N");
Iterator itor = query.list().iterator();

shl · **Posted:** Thu Jul 21, 2005 7:28 am

Checkout your sessionFactory configuration in the Spring application context.

for example here is my working one:

Code:

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <!--<property name="dataSource">-->
        <!--    <ref local="myDataSource"/>-->
        <!--</property>-->
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</prop>
                <!--<prop key="hibernate.connection.url">jdbc:hsqldb:file:gpcswebdb/gpcswebdb</prop>-->
                <prop key="hibernate.connection.url">jdbc:hsqldb:hsql://localhost/gpcswebdb</prop>
                <prop key="hibernate.connection.username">sa</prop>
                <prop key="hibernate.connection.password"></prop>
                <prop key="hibernate.c3p0.min_size">5</prop>
                <prop key="hibernate.c3p0.max_size">20</prop>
                <prop key="hibernate.c3p0.timeout">1800</prop>
                <prop key="hibernate.c3p0.max_statements">50</prop>

                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>

                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.use_outer_join">true</prop>

                <prop key="hibernate.hbm2ddl.auto">update</prop>

                <prop key="hibernate.jdbc.batch_size">0</prop>
                <prop key="hibernate.jdbc.batch_versioned_data">true</prop>
                <prop key="hibernate.max_fetch_depth">1</prop>
                <prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
            </props>
        </property>
        <property name="mappingResources">
            <list>
                <value>com/citi/rpc/webentity/BusinessDay.hbm.xml</value>
                <value>com/citi/rpc/webentity/Duty.hbm.xml</value>
                <value>com/citi/rpc/webentity/Employee.hbm.xml</value>
                <value>com/citi/rpc/webentity/TeamMember.hbm.xml</value>
                <value>com/citi/rpc/webentity/Project.hbm.xml</value>
            </list>
        </property>
    </bean>

shl · **Posted:** Thu Jul 21, 2005 7:41 am

the main thing Hibernate 3.x: org.springframework.orm.hibernate3.LocalSessionFactoryBean

ruchiahuja10 · **Joined:** Thu Jun 02, 2005 5:03 am **Posts:** 15

I have figured out the problem. Its is because my code wasnt running in a transaction. I made the method transactional and it worked.

But the point to note is that it wasnt running in a transaction in hibernate 2.1.6, but dint gave any error. Why is it giving error in hibernate 3. Does that means that we cant execute code in hibernate 3 without transaction. Do you have any idea?

Here are my logs -

Code:

55:50,707 DEBUG SessionImpl:250 - [main] opened session at timestamp: 11219469500
55:50,707 DEBUG ConnectionManager:302 - [main] opening JDBC connection
55:50,707 DEBUG DriverManagerDataSource:232 - [main] Creating new JDBC connection to [jdbc:oracle:thin:@mypc:1521:mysid]
55:57,138 DEBUG HibernateTemplate:249 - [main] Eagerly flushing Hibernate session
55:57,138 DEBUG SessionFactoryUtils:773 - [main] Closing Hibernate Session
55:57,138 DEBUG SessionImpl:269 - [main] closing session
55:57,138 DEBUG ConnectionManager:324 - [main] closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
55:57,856 DEBUG JDBCContext:283 - [main] after transaction completion
55:57,856 DEBUG SessionImpl:403 - [main] after transaction completion
55:57,856 DEBUG catalog:332 - [main] The method executed successfully
55:57,856 DEBUG SessionImpl:829 - [main] find: SELECT tkfv.fldDescX FROM TTkfv tkfv WHERE tkfv.delI = ?
55:57,856 DEBUG QueryParameters:221 - [main] parameters: [TEST, 1000, N]
55:57,856 DEBUG QueryParameters:224 - [main] named parameters: {}
55:57,856 DEBUG QueryTranslatorImpl:174 - [main] compiling query
55:57,920 DEBUG StandardQueryCache:75 - [main] checking cached query results in region: org.hibernate.cache.StandardQueryCache
55:57,920 DEBUG StandardQueryCache:78 - [main] query results were not found in cache
55:57,920 DEBUG QueryTranslatorImpl:239 - [main] HQL: SELECT tkfv.fldDescX FROM TTkfv tkfv WHERE tkfv.delI = ?
55:57,920 DEBUG QueryTranslatorImpl:240 - [main] SQL: select ttkfv_.FLD_DESC_X as col_1_0_ from T_TKFV ttkfv_ where ttkfv_.DEL_I=?
55:57,920 DEBUG AbstractBatcher:290 - [main] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
55:57,920 DEBUG JDBCContext:322 - [main] after autocommit
55:57,920 DEBUG SessionImpl:403 - [main] after transaction completion
55:57,950 ERROR catalog:720 - [main] Error occurred in persistence operation: Not able to obtain connection

shl · **Posted:** Fri Jul 22, 2005 3:11 am

ruchiahuja10 wrote:

But the point to note is that it wasnt running in a transaction in hibernate 2.1.6, but dint gave any error. Why is it giving error in hibernate 3.

I saw in your example:

Code:

getHibernateTemplate().execute(new HibernateCallback()

It looks like you are using hibernate with spring framework. Spring framework can manage your transactions if configured properly. I think during porting to Hibernate 3.x you changed some of your spring configuration and now spring declarative transactions do not work.

You also should be aware that with Hibernate 3.x you should use org.springframework.orm.hibernate3.HibernateCallback instead of org.springframework.orm.hibernate.HibernateCallback and etc.

ruchiahuja10 wrote:

Does that means that we cant execute code in hibernate 3 without transaction.

If you want your changes applied in the DB you have to commit transaction manually, using Spring Declarative Transaction Demarcation or CMT.

ruchiahuja10 · **Joined:** Thu Jun 02, 2005 5:03 am **Posts:** 15

I have just done the following steps in my project -
1. Updated hibernate and related jars - hibernate3.jar, cglib-2.1.jar, commons-collections-2.1.1.jar, dom4j-1.6.jar, ehcache-1.1.jar
2. Added new jars - asm.jar, asm-attrs.jar, antlr-2.7.5H3.jar
3. Replace net.sf.hibernate.expression with org.hibernate.criterion
4. Replace net.sf.hibernate with org.hibernate in all the files
5. Replace org.springframework.orm.hibernate with org.springframework.orm.hibernate3
7. Change http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd to http://hibernate.sourceforge.net/hibern ... ng-3.0.dtd
8. Change http://hibernate.sourceforge.net/hibern ... on-2.0.dtd to http://hibernate.sourceforge.net/hibern ... on-3.0.dtd

Solaris · **Joined:** Fri Sep 16, 2005 3:10 am **Posts:** 4

Hi, I have the same problem. plz tell me how to solve it thx

ruchiahuja10 · **Joined:** Thu Jun 02, 2005 5:03 am **Posts:** 15

Wrap the code in a transaction, it works

Solaris · **Joined:** Fri Sep 16, 2005 3:10 am **Posts:** 4

ruchiahuja10 wrote:

Wrap the code in a transaction, it works

Transaction t=xsldao.beginTransaction(xsldao.getSession());
System.out.print(t.isActive());
List xsllist= xsldao.getQuery("FROM XSL").list();

for( ListIterator lt=xsllist.listIterator(); lt.hasNext();){
XSL xsl2=(XSL)lt.next();
System.out.println(xsl2.getXslDesc());
}
xsldao.commitTransaction(t);

you mean like this? it's not work either. and I agree with your prior opinion.
Do we need transaction when we only query something?At least in Hibernate2 I don't have to do this.

So, there is something wrong.
any suggestions?

ruchiahuja10 · **Joined:** Thu Jun 02, 2005 5:03 am **Posts:** 15

Are u using hibernate with spring?

Solaris · **Joined:** Fri Sep 16, 2005 3:10 am **Posts:** 4

ruchiahuja10 wrote:

Are u using hibernate with spring?

No, actually it's a simple test application.

I use hibernate synchronizer and test some DAO functions and find out
DAO.get(ID) or DAO.findAll() just work fine.
Only Query goes wrong.

I totally have no idea about this.

ruchiahuja10 · **Joined:** Thu Jun 02, 2005 5:03 am **Posts:** 15

I think it is a issue in hibernate 3, because i also saw it working with hibernate 2.

I dont know the fix for it because i dint hear anything from the hibernate team in this post. For the time being i have wrapped my code in a transaction to make it work.

Solaris · **Joined:** Fri Sep 16, 2005 3:10 am **Posts:** 4

ruchiahuja10 wrote:

I think it is a issue in hibernate 3, because i also saw it working with hibernate 2.

I dont know the fix for it because i dint hear anything from the hibernate team in this post. For the time being i have wrapped my code in a transaction to make it work.

Thx anyway. I think there should be something wrong in hibernate.cfg.xml or somewhere else.

Here is part of my setting. Hopefully somebody can give some suggestion.
<property name="hibernate.connection.username">RSSFEED</property>
<property name="hibernate.connection.password">1i6fm06</property>
<property name="hibernate.connection.pool_size">3</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>

<property name="hibernate.show_sql">true</property>
<property name="hibernate.use_outer_join">true</property>

<property name="hibernate.jdbc.batch_size">0</property>
<property name="hibernate.jdbc.batch_versioned_data">true</property>
<property name="hibernate.max_fetch_depth">1</property>
<property name="hibernate.jdbc.use_streams_for_binary">
true
</property>



<property name="dialect">
org.hibernate.dialect.Oracle9Dialect
</property>

kbaum · **Joined:** Tue Aug 17, 2004 11:27 pm **Posts:** 24

Quote:

I have figured out the problem. Its is because my code wasnt running in a transaction. I made the method transactional and it worked.

If Spring is running inside of a transaction, it binds one hibernate session to that transaction. That session is used throughout the duration of that transaction.

If, however, you are not operating within a transaction, Spring will look for an open session attached to the current worker thread. If there is no open hibernate session associated with the thread, the HibernateTemplate will do one of two things depending on your configuration. If you have allowCreate set to true, Spring will open a new session for that database operation and close the session afterword. This is called session per operation and is not recommended. If allowCreate is set to false, however, spring will throw an exception complaining it cannot find an open session for this thread. The recommended approach is to leave allowCreate as false and configure the HibernateInterceptor or OpenSessionInViewFilter to proxy the entire request. The example here might help.

In regards to your code example, I would recommend you perform all of your hibernate operations within the execute method if possible. The execute method method allows for executing any hibernate logic within the context of an open session. In your example, your session could have been closed by the time you executed your query. Of course this would only be the case if you were not in a transaction and there was no open session binded to the thread. In either case i would recommend something like:

Code:

getHibernateTemplate().execute(
            new HibernateCallback() {
               public Object doInHibernate(Session session)
                     throws HibernateException {
                  Query query = session.createQuery(sql);
                  query.setString(0, "TEST1");
                  query.setBigDecimal(1, new BigDecimal(1000));
                  query.setString(2, "N");
                  Iterator iter = query.list().iterator();
                  while (iter.hasNext()) {
                     //your logic here.
                  }
               }
            });

Hope that helps.

-karl