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.
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,
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?
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...
Quote tag doesnt show the tag... anyway:
"code" tag should show everything for you. :)
What version of MySQL are you using? There's also:
...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: