hibernate - uniqueResult silently fails

Question

I have a login controller that use the hibernate uniqueResult method. Everything works fine when i test it in eclipse's tomcat server. But when i deploy my webapps to tomcat server (on the same machine) it fails: it always returns null even i use the correct credential.

Here is my hibernate code:

session.createCriteria(User.class)
            .add(Restrictions.eq(User.USERNAME_FIELD, userName))
            .add(Restrictions.eq(User.PASSWORD_FIELD, password)).uniqueResult();

Thank you!

Answer 1

There is not enough information, try to trace the SQL that is being generated:

Set in log4j.properties

log4j.logger.org.hibernate.SQL=TRACE
log4j.logger.org.hibernate.type=TRACE

Or set these Hibernate properties

hibernate.show_sql=true
hibernate.format_sql=true

Then try to run these queries, with the passed parameters that return null directly in the database.

Answer 2

Maybe you should try to see the actual hibernate query and parameters using the logger. The two loggers you should make to "debug" are:

• org.hibernate.SQL
• org.hibernate.type

Put both on TRACE or ALL and check the result on logging. For more information about the logger see the hibernate documentation.

The most common case would be log4j. AFAIK, hibernate.show_sql is deprecated.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration>
<appender name="Stdout" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
    </layout>
</appender>

<logger name="org.hibernate.SQL">
    <level value="TRACE"/>
</logger>

<logger name="org.hibernate.type">
    <level value="TRACE"/>
</logger>

<root>
    <level value="INFO"/>
    <appender-ref ref="Stdout"/>
</root>

</log4j:configuration>

Answer 3

I've solve this problem by adding session.flush()

But i'm definitely not happy with this :-(. Please tell me is there any better solution. Thank you.

Answer 4

I have a similar question. I am doing an INSERT and SELECT in a named SQL query and trying to execute the same with Query.uniqueResult(). The following is my named query definition:

<sql-query name="QRY_ADD_FOO_ITEM" >
<return-scalar column="id" type="integer"/>
<![CDATA[
    INSERT INTO foo_items (col1, col2, col3, col4) 
    VALUES (:col1, :col2, :col3, :col4);
    SELECT @@IDENTITY AS id;
]]>
</sql-query>

Now in my DAO, I am trying to insert and retrieve this using:

public int saveFooItem(final Foo fooToSave) throws DataAccessException {

        return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

        /* (non-Javadoc)
         * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
         */
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query qry = session.getNamedQuery("QRY_ADD_FOO_ITEM");
            qry.setString("col1", prefSection.getCol1());
            qry.setInteger("col2", prefSection.getCol2());
            qry.setInteger("col3", prefSection.getCol3());
            qry.setString("col4", prefSection.getCol4());
            int retVal = ((Integer)qry.uniqueResult()).intValue();
            session.flush();
            return retVal;
        }

    });
}

I wanted save the Foo item into the table and retrieve its auto-generated ID. That is the reason I combined both these INSERT and SELECT operations into one query and used uniqueResult() to retrieve the ID value. I agree this may not be the best way to do it.

Now the problem is, I am finding that in most cases the Foo item gets inserted into the table but sometimes fails to insert the data item and does not even report an error.

Please let me know what I can do.

P.S. I am using Hibernate 3.0.5 with Spring 2.0 with SQL Server 2005