Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1.3

Name and version of the database you are using:
mySQL4.0

The generated SQL (show_sql=true):

SELECT this_.id as y0_ from employee this_ left outer join voucher vouchers2_ on this_.id=vouchers2_.employeeId left outer join voucherStatus log3_ on vouchers2_.id=log3_.voucherId inner join CreditItemCalculation credit1_ on this_.id=credit1_.employeeId left outer join CreditItem credit1_1_ on credit1_.CreditItemCalculation=credit1_1_.id left outer join CreditItemCalculationEZ credit1_2_ on credit1_.CreditItemCalculation=credit1_2_.CreditItemCalculationEZ left outer join CreditItemCalculationFRC credit1_3_ on credit1_.CreditItemCalculation=credit1_3_.CreditItemCalculationFRC left outer join CreditItemCalculationLARZ credit1_4_ on credit1_.CreditItemCalculation=credit1_4_.CreditItemCalculationLARZ left outer join CreditItemCalculationFEZ credit1_5_ on credit1_.CreditItemCalculation=credit1_5_.CreditItemCalculationFEZ where this_.clientId= ? and (log3_.voucherStatusCodeId is null) group by this_.id


When I run the above query (that was generated from a criteria) in the query browser I get results. But the resultsing list is empty. Any help would be greatly apprecaited.

Thanks.

It may have something to do with your chosen result transformer, or some other option you've set in the criteria. Can you post the relevant java code?

Your query seems excessively big and slow: why are all those unused joins in there? There's six unused joins in that query...

they are just outerjoins to get fetch="join" or implicit joins.

...and show the query; it is most likely caused by you doing alot of inner joins and the data then simply does not exists to make it return something.

Here is the criteria that is used:

Criteria filterCriteria = getSession().createCriteria(Employee.class);
filterCriteria.add( Expression.eq("client.id", filter.getClientId()));
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.groupProperty("id"));
Disjunction voucherStatusExpression = Expression.disjunction();
filterCriteria.createAlias("vouchers", "vouchers", JoinFragment.LEFT_OUTER_JOIN);
filterCriteria.createAlias("vouchers.statusLog", "log", JoinFragment.LEFT_OUTER_JOIN); voucherStatusExpression.add(Expression.isNull("log.code.id"));
filterCriteria.add(voucherStatusExpression);
filterCriteria.setProjection(projectionList);
result = filterCriteria.list();

But what I think is wierd is that the generated SQL does return results.

I figured it out thanks for the help. It was just a dumb mistake on my part.

Hibernate 3.1.3
Mysql 4.1.2
Linux 2.6.12

I haven't been so lucky... I have yet to find my dumb mistake...
---
The code:

StringBuffer hql = new StringBuffer( "from mycompany.domain.pojo.Account ");
hql.append( "a where a.username=:username" );
Query query = session.createQuery( hql.toString() );
query.setString( "username", username );
Account acct = (Account) query.uniqueResult();
return ( acct );

(and I've traced the code to see that 'username' is, in fact, set properly)
---
show_sql gives this:

select account0_.id as id54_, account0_.version as version54_, account0_.username as username54_, account0_.password as password54_, account0_.person_id as person5_54_ from account account0_ where account0_.username=?

---
Pasting that into mysql command line with the ? replaced with a valid 'username' works:

mysql> select account0_.id as id54_, account0_.version as version54_, account0_.username as username54_, account0_.password as password54_, account0_.person_id as person5_54_ from account account0_ where account0_.username="admin";
+-------+------------+-------------+------------------+-------------+
| id54_ | version54_ | username54_ | password54_ | person5_54_ |
+-------+------------+-------------+------------------+-------------+
| 1 | 0 | admin | 43e9a4ab75570f5b | 1 |
+-------+------------+-------------+------------------+-------------+
1 row in set (0.00 sec)

---
But I get 0 rows back from hibernate...

Is it possible to do something like 'show_sql' that will show the sql *after* the parameters have been substituted?

Thanks
M.

You can debug your way into the Hibernate stuff and see what it was set to in the prepared statement. Thats what I had to do.

The easiet way to do this is to use p6spy - an open source SQL 'spy'.
Basically it pretends to be a database driver. You tell Hibernate to
use it rather than your own database driver and configure it to use
the actual database driver as follows.

So, the following should be set:
1. hibernate.connection.driver_class=com.p6spy.engine.spy.P6SpyDriver

2. Have the jar in your class path

3. In the directory you're running the app from (you can specify a system property that points to a different folder if you wish), have a spy.properties
file (one is provided as a template with the distribution). You'll need to
enable the property realdriver=com.mysql.jdbc.Driver, and any
logging options you like (Log4J, etc). Then just watch the log - it contains
all the interactions with the database, including the unrolled statements,
with '?' substituted for the actual variable values.


Hope this helps.

No, that's the 2nd easiest. The easiest is to turn on the appropriate logging. Add "log4j.logger.org.hibernate.type=DEBUG" to your log4j.properties file.

Note that the ?s are never replaced by the JDBC driver. That happens on the database side. The whole point of those ?s is so that the database can create a partial execution plan that will work for every combination of variable: if the SQL that was sent to the DB was different every time (because params were substituted before they left the JDBC driver) then it couldn't do it.

Thanks for tips. Very helpful. By turning on hibernate debug logging, I could verify that the parameters were being bound properly. That lead me 'down' to the jdbc level.

A little searching found some web pages that indicated that there may be some compatibility troubles between mysql 4.1.? and the 3.1.12 jdbc connector.

Downgrading to the 3.0.17 jdbc connector solved this particular problem.

Thanks again.
Mike

You're right about the JDBC driver treatment, except that since p6spy
pretends to be a real driver, it gets to see all traffic to the database,
including setting the parameters and so it can collect all the values as
appropriate. At the time of execution, the logged statement has all
the '?' replaced by the real values. The executed statement is still the
same (ie. using the '?' placeholders).

Another advantage of p6spy over the internal debugging is (IMHO) that
the logged statement is a single line whereas the Hibernate debugging
stuff occupies lots of logging real estate and one needs to build the
resulting statement from all these fragments.