Newbie : Unable to Fetch Data From MySQL using Hibernate

[Russell Ray]

I have been working with Hibernate only a short period of time. I have gotten my database model up and running and I have generated all the classes using the tools from MyEclipse. I have been successful in storing and retrieving data to the main object/table. I have written a Junit test to accomplish this action. Now I have moved on to the second table. In this table I have one attribute as a LONGTEXT datatype (XML Doucment). I have mapped this attribute as described in the Hibernate documentation:

    <class name="my.metadata.orm.MetadataDocument" table="metadata_document" catalog="my_metadata_db">
        <id name="metadataDocId" type="java.lang.Integer">
            <column name="metadata_doc_id" />
            <generator class="native" />
        </id>
        <many-to-one name="myDocument" class="my.metadata.orm.MyDocument" fetch="select">
            <column name="fk_my_document_id" not-null="true" />
        </many-to-one>
        <property name="xmlDocument" update ="true" insert="true" type="java.lang.String" length="2147483647">
            <column name="xml_document" />
        </property>
        <property name="xmFileSize" type="java.lang.Long">
            <column name="xm_file_size" />
        </property>
        <property name="xmlFileName" type="java.lang.String">
            <column name="xml_file_name" length="50" />
        </property>
        <property name="xmlFileCreateDate" type="java.util.Date">
            <column name="xml_file_create_date" length="0" />
        </property>
        <property name="xmlFileUpdateDate" type="java.util.Date">
            <column name="xml_file_update_date" length="0" />
        </property>
        <property name="xmlFileDescription" type="java.lang.String">
            <column name="xml_file_description" length="256" />
        </property>
        <property name="xmlFileOsDate" type="java.util.Date">
            <column name="xml_file_os_date" length="0" />
        </property>
    </class>

What I see in the log files is the data is being pumped into the database as it should. Next, Hibernate fetches the data to test the SELECT statement works. However, something is going wrong because I see:

- Began transaction (1): transaction manager [org.springframework.orm.hibernate3.HibernateTransactionManager@aea5cd]; default rollback = true
Hibernate: 
    /* 
from
    MetadataDocument */ select
        metadatado0_.metadata_doc_id as metadata1_154_,
        metadatado0_.fk_my_document_id as fk2_154_,
        metadatado0_.xml_document as xml3_154_,
        metadatado0_.xm_file_size as xm4_154_,
        metadatado0_.xml_file_name as xml5_154_,
        metadatado0_.xml_file_create_date as xml6_154_,
        metadatado0_.xml_file_update_date as xml7_154_,
        metadatado0_.xml_file_description as xml8_154_,
        metadatado0_.xml_file_os_date as xml9_154_ 
    from
        jtdi_metadata_db.metadata_document metadatado0_
- Could not toggle autocommit
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Connection.getMutex(Connection.java:3728)
at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5365)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
at org.hibernate.transaction.JDBCTransaction.toggleAutoCommit(JDBCTransaction.java:194)
at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:186)
at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:162)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:597)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:753)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:730)
at org.springframework.test.AbstractTransactionalSpringContextTests.endTransaction(AbstractTransactionalSpringContextTests.java:289)
at org.springframework.test.AbstractTransactionalSpringContextTests.onTearDown(AbstractTransactionalSpringContextTests.java:233)
at org.springframework.test.AbstractSingleSpringContextTests.tearDown(AbstractSingleSpringContextTests.java:125)
at junit.framework.TestCase.runBare(TestCase.java:140)
at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.access$001(AbstractAnnotationAwareTransactionalTests.java:47)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests$1.run(AbstractAnnotationAwareTransactionalTests.java:113)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTest(AbstractAnnotationAwareTransactionalTests.java:176)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTestTimed(AbstractAnnotationAwareTransactionalTests.java:150)
at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runBare(AbstractAnnotationAwareTransactionalTests.java:109)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:76)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
- SQL Error: 0, SQLState: null
- Already closed.

From what I can tell something is happening in the database Connection pool.


My setting are as follows for the database Connection pool:

<!-- Connection Pool -->
<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.c3p0.idle_test_period">3000</property>

Thank you for taking the time to read my post and for any suggestions you can offer.

Russ

[Mark Spritzler]

So the connection has closed. Did you close the Session? is there a timeout in MySQL or the Connection pool that closes it quickly?

Can you post your Session code that does the query?

Thanks

Mark

[Russell Ray]

Mark,

I took you question as a suggestion and I tried something. The only thing different is a I placed the find operation within a transaction per your question.

@SuppressWarnings("unchecked")
public List<MetadataDocument> findAll() {
log.debug("finding all MetadataDocument instances");

Session session = null;
Transaction tx = null;

try {
session = getSessionFactory().openSession();
tx = session.beginTransaction();
String queryString = "from MetadataDocument";
List<MetadataDocument> metadataList =  getHibernateTemplate().find(queryString);
tx.commit();
log.debug("Find All successful");
return metadataList;
 
} catch (RuntimeException re) {
log.error("Find All failed", re);
try{
    tx.rollback();
    }catch(RuntimeException rbEx){
    log.error("Couldn't roll back transaction", rbEx);
    }
    throw re;
    }finally{
      session.close();
}
}
 

I am still getting the same error. My method for adding metadata documents is identifical with regards to wrapping the actions within a transaction.

Russ

[Russell Ray]

I fixed my problem by increating the heal size from the standard to

-Xms32m -Xmx128m 

Thanks Mark!

[Mark Spritzler]

Originally posted by Russell Ray:
I fixed my problem by increating the heal size from the standard to

-Xms32m -Xmx128m 

Thanks Mark!

Cool.

Mark