7 Replies Latest reply on Sep 17, 2007 3:25 PM by Andrew Rubinger

    How to get the size of a Lazy load relation

    Ignacio de Cordoba Novice

      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

        • 1. Re: How to get the size of a Lazy load relation
          Andrew Rubinger Master

          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

          • 2. Re: How to get the size of a Lazy load relation
            Ignacio de Cordoba Novice

            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

            • 3. Re: How to get the size of a Lazy load relation
              Andrew Rubinger Master

               

              "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

              • 4. Re: How to get the size of a Lazy load relation
                Ignacio de Cordoba Novice

                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

                • 6. Re: How to get the size of a Lazy load relation
                  Ignacio de Cordoba Novice

                  Uffff...

                  org.hibernate.dialect.MySQLDialect

                  • 7. Re: How to get the size of a Lazy load relation
                    Andrew Rubinger Master

                    "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