How to get the size of a Lazy load relation

[icordoba]

Hi there,
I need to pre-load the "size" of a Lazy load relation. I need to do it because I need to access the Entity in a JSP and I get "No session or session closed" if getting the size directly from the relation (I don't want to put a Transaciton using a scriptlet in the JSP page)
I can neither do it using PostLoad annotation. It doesn't work at least with JBoss and if I use Eager relations the system loads all data just when checking the size, so becomes very slow.
thanks for any help/ideas,
Ignacio

[alrubinger]

Within the context of your UserTransaction (if your persistence unit has Tx type JTA) or Hibernate Session (if you're managing this manually), I'd execute an EJBQL "COUNT" Function to get a slim-and-trim resultant SQL Query sent to your DB.

Integer objId = [whatever your PK is];
EntityManager em = [wherever you get your EM from];
Integer count = (Integer)(em.createQuery("SELECT COUNT(obj.association) FROM " + YourEntityBean.class.getName()
 + " obj WHERE obj.id = :objId").setParameter("objId",objId).getSingleResult());

You don't really want to be "loading" anything if you don't need these objects....just the count.

S,
ALR

[icordoba]

Thanks a lot for your reply.
I am trying this both as a NamedQuery or as a direct query and I am getting the following exception:

2007-09-17 17:12:05,036 DEBUG [org.hibernate.SQL] select count(.) as col_0_0_ from Role role0_, User_Role users1_, User user2_ where role0_.pkey=users1_.roles_pkey and users1_.users_pkey=user2_.pkey and role0_.pkey=?
2007-09-17 17:12:05,038 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2007-09-17 17:12:05,038 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
2007-09-17 17:12:05,038 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2007-09-17 17:12:05,039 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select count(.) as col_0_0_ from Role role0_, User_Role users1_, User user2_ where role0_.pkey=users1_.roles_pkey and users1_.users_pkey=user2_.pkey and role0_.pkey=?]
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as col_0_0_ from Role role0_, User_Role users1_, User user2_ where role0_.pkey' at line 1

The compiled HQL is the folling (and when using a named query it is compiled without errors):

select count(r.users) from com.servengine.user.Role r WHERE r.pkey = :pkey

Thanks for any ideas,
Ignacio

[alrubinger]

"icordoba" wrote:
select count(.) as col_0_0_ from Role role0_, User_Role users1_, User user2_ where role0_.pkey=users1_.roles_pkey and users1_.users_pkey=user2_.pkey and role0_.pkey=?

...not sure about that generated "count(.)". Just ran a test for that syntax on MySQL 5.0.27 and came up with the same error you're getting.

Sure you've configured the correct dialect for Hibernate in persistence.xml?

S,
ALR

[icordoba]

I am using

<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>

I am using caches in persistence.xml, I'll deactivate them just to test...

:-(

Thanks,
Ignacio

[icordoba]

Quote tag doesnt show the tag... anyway:

[icordoba]

Uffff...

org.hibernate.dialect.MySQLDialect

[alrubinger]

"code" tag should show everything for you. :)

What version of MySQL are you using? There's also:

MySQL5Dialect
MySQL5InnoDBDialect

...available to you, though I'm not confident that's the source of your problem. I'd play around with the EJBQL to get resultant SQL that'll execute in the MySQL Command-Line or GUI Tools on its own first. That "count(.)" should either be "count(fieldName)" or "count(*)". Maybe try EJBQL:

SELECT COUNT(obj.association.pkey)...

?

S,
ALR