6 Replies Latest reply on Sep 16, 2009 4:35 PM by Rainer Schön

    EntityQuery fails on getResultCount with composite primary keys on Oracle

    Alessandro Torrisi Newbie

      Hi my friends, I'm trying to interact with an Oracle enterprise database through Seam, but I'm encountering lot of problems.
      My development platform is based on Eclipse 3.4.2 / JBoss Tools / Seam 2.1.0-GA / JBoss AS 5.1.0-GA.
      I try to explain the problem in detail, giving you all the components.


      Here it is the table definition:



      create table WV_DWH.WV0514A_FILIALE
      (
        SQ_FILIALE_ID NUMBER not null,
        DT_REFERENCE  DATE not null,
        CD_FILIALE    VARCHAR2(100) not null,
        DS_FILIALE    VARCHAR2(400),
        SQ_RDT_ID     NUMBER not null,
        CD_CAB        VARCHAR2(6),
        CD_ABI        VARCHAR2(5),
        SQ_BANCA_ID   NUMBER
      )
      
      alter table WV_DWH.WV0514A_FILIALE
        add constraint WV0514A_PK primary key (DT_REFERENCE, SQ_FILIALE_ID)
        using index 
        tablespace WVU_STAGE_INDX_S0
        pctfree 10
        initrans 2
        maxtrans 255
        storage
        (
          initial 128K
          next 128K
          minextents 1
          maxextents unlimited
          pctincrease 0
        );




      So we have a table with a composite primary key made up by a REFERENCE DATE and an ID.


      The Seam Generate Entities Eclipse mapping function creates two entity classes made up by an @Embeddable class (the ID) in which we have declared:
      - Date dtReference
      - BigDecimal sqFilialeId
      and the table class in which we have declared:
      - Wv0514aFilialeId id
      - String cdFiliale
      - String dsFiliale
      - BigDecimal sqRdtId
      - String cdCab
      - String cdAbi
      - BigDecimal sqBancaId


      Here the classes definition:


      package it.cogmps.massivesim.entity;
      
      // Generated 6-lug-2009 15.46.51 by Hibernate Tools 3.2.4.GA
      
      import java.math.BigDecimal;
      import java.util.Date;
      import javax.persistence.Column;
      import javax.persistence.Embeddable;
      import javax.persistence.Temporal;
      import javax.persistence.TemporalType;
      
      import org.hibernate.validator.NotNull;
      
      /**
       * Wv0514aFilialeId generated by hbm2java
       */
      @Embeddable
      public class Wv0514aFilialeId implements java.io.Serializable {
      
           private static final long serialVersionUID = -1437555899840893475L;
           private Date dtReference;
           private BigDecimal sqFilialeId;
      
           public Wv0514aFilialeId() {
           }
      
           public Wv0514aFilialeId(Date dtReference, BigDecimal sqFilialeId) {
                this.dtReference = dtReference;
                this.sqFilialeId = sqFilialeId;
           }
      
           @Temporal(TemporalType.DATE)
           @Column(name = "DT_REFERENCE", nullable = false, length = 7)
           @NotNull
           public Date getDtReference() {
                return this.dtReference;
           }
      
           public void setDtReference(Date dtReference) {
                this.dtReference = dtReference;
           }
      
           @Column(name = "SQ_FILIALE_ID", nullable = false, precision = 22, scale = 0)
           @NotNull
           public BigDecimal getSqFilialeId() {
                return this.sqFilialeId;
           }
      
           public void setSqFilialeId(BigDecimal sqFilialeId) {
                this.sqFilialeId = sqFilialeId;
           }
      
           public boolean equals(Object other) {
                if ((this == other))
                     return true;
                if ((other == null))
                     return false;
                if (!(other instanceof Wv0514aFilialeId))
                     return false;
                Wv0514aFilialeId castOther = (Wv0514aFilialeId) other;
      
                return ((this.getDtReference() == castOther.getDtReference()) || (this
                          .getDtReference() != null
                          && castOther.getDtReference() != null && this.getDtReference()
                          .equals(castOther.getDtReference())))
                          && ((this.getSqFilialeId() == castOther.getSqFilialeId()) || (this
                                    .getSqFilialeId() != null
                                    && castOther.getSqFilialeId() != null && this
                                    .getSqFilialeId().equals(castOther.getSqFilialeId())));
           }
      
           public int hashCode() {
                int result = 17;
      
                result = 37
                          * result
                          + (getDtReference() == null ? 0 : this.getDtReference()
                                    .hashCode());
                result = 37
                          * result
                          + (getSqFilialeId() == null ? 0 : this.getSqFilialeId()
                                    .hashCode());
                return result;
           }
      
      }
      



      package it.cogmps.massivesim.entity;
      
      // Generated 6-lug-2009 15.46.51 by Hibernate Tools 3.2.4.GA
      
      import java.math.BigDecimal;
      import javax.persistence.AttributeOverride;
      import javax.persistence.AttributeOverrides;
      import javax.persistence.Column;
      import javax.persistence.EmbeddedId;
      import javax.persistence.Entity;
      import javax.persistence.Table;
      import org.hibernate.validator.Length;
      import org.hibernate.validator.NotNull;
      
      /**
       * Wv0514aFiliale generated by hbm2java
       */
      @Entity
      @Table(name = "WV0514A_FILIALE", schema="WV_DWH")
      public class Wv0514aFiliale implements java.io.Serializable {
      
           private static final long serialVersionUID = 349405820102500746L;
           private Wv0514aFilialeId id;
           private String cdFiliale;
           private String dsFiliale;
           private BigDecimal sqRdtId;
           private String cdCab;
           private String cdAbi;
           private BigDecimal sqBancaId;
      
           public Wv0514aFiliale() {
           }
      
           public Wv0514aFiliale(Wv0514aFilialeId id, String cdFiliale,
                     BigDecimal sqRdtId) {
                this.id = id;
                this.cdFiliale = cdFiliale;
                this.sqRdtId = sqRdtId;
           }
      
           public Wv0514aFiliale(Wv0514aFilialeId id, String cdFiliale,
                     String dsFiliale, BigDecimal sqRdtId, String cdCab, String cdAbi,
                     BigDecimal sqBancaId) {
                this.id = id;
                this.cdFiliale = cdFiliale;
                this.dsFiliale = dsFiliale;
                this.sqRdtId = sqRdtId;
                this.cdCab = cdCab;
                this.cdAbi = cdAbi;
                this.sqBancaId = sqBancaId;
           }
      
           @EmbeddedId
           @AttributeOverrides( {
                     @AttributeOverride(name = "dtReference", column = @Column(name = "DT_REFERENCE", nullable = false, length = 7)),
                     @AttributeOverride(name = "sqFilialeId", column = @Column(name = "SQ_FILIALE_ID", nullable = false, precision = 22, scale = 0)) })
           @NotNull
           public Wv0514aFilialeId getId() {
                return this.id;
           }
      
           public void setId(Wv0514aFilialeId id) {
                this.id = id;
           }
      
           @Column(name = "CD_FILIALE", nullable = false, length = 100)
           @NotNull
           @Length(max = 100)
           public String getCdFiliale() {
                return this.cdFiliale;
           }
      
           public void setCdFiliale(String cdFiliale) {
                this.cdFiliale = cdFiliale;
           }
      
           @Column(name = "DS_FILIALE", length = 400)
           @Length(max = 400)
           public String getDsFiliale() {
                return this.dsFiliale;
           }
      
           public void setDsFiliale(String dsFiliale) {
                this.dsFiliale = dsFiliale;
           }
      
           @Column(name = "SQ_RDT_ID", nullable = false, precision = 22, scale = 0)
           @NotNull
           public BigDecimal getSqRdtId() {
                return this.sqRdtId;
           }
      
           public void setSqRdtId(BigDecimal sqRdtId) {
                this.sqRdtId = sqRdtId;
           }
      
           @Column(name = "CD_CAB", length = 6)
           @Length(max = 6)
           public String getCdCab() {
                return this.cdCab;
           }
      
           public void setCdCab(String cdCab) {
                this.cdCab = cdCab;
           }
      
           @Column(name = "CD_ABI", length = 5)
           @Length(max = 5)
           public String getCdAbi() {
                return this.cdAbi;
           }
      
           public void setCdAbi(String cdAbi) {
                this.cdAbi = cdAbi;
           }
      
           @Column(name = "SQ_BANCA_ID", precision = 22, scale = 0)
           public BigDecimal getSqBancaId() {
                return this.sqBancaId;
           }
      
           public void setSqBancaId(BigDecimal sqBancaId) {
                this.sqBancaId = sqBancaId;
           }
      
      }



      The seam generator creates also a wonderful UI to interact with the database. So now I'm ready to deploy the project to JBoss and browse my data through the UI, but it's here that I encounter the first problems...


      The data browsing is perfect for the tables without a composite primary key, but not for the objects described above...


      I saw the SQL queries in standard output written by

      <property name="hibernate.show_sql" value="true"/>

      setting in the persistence.xml configuration file.
      I noticed that it tryies to make pagination through a couple of queries:


      15:11:01,604 INFO  [STDOUT] Hibernate: 
          select
              * 
          from
              ( select
                  wv0514afil0_.DT_REFERENCE as DT1_50_,
                  wv0514afil0_.SQ_FILIALE_ID as SQ2_50_,
                  wv0514afil0_.CD_ABI as CD3_50_,
                  wv0514afil0_.CD_CAB as CD4_50_,
                  wv0514afil0_.CD_FILIALE as CD5_50_,
                  wv0514afil0_.DS_FILIALE as DS6_50_,
                  wv0514afil0_.SQ_BANCA_ID as SQ7_50_,
                  wv0514afil0_.SQ_RDT_ID as SQ8_50_ 
              from
                  WV_DWH.WV0514A_FILIALE wv0514afil0_ ) 
          where
              rownum <= ?



      15:11:01,792 INFO  [STDOUT] Hibernate: 
          select
              * 
          from
              ( select
                  count((wv0514afil0_.DT_REFERENCE,
                  wv0514afil0_.SQ_FILIALE_ID)) as col_0_0_ 
              from
                  WV_DWH.WV0514A_FILIALE wv0514afil0_ ) 
          where
              rownum <= ?



      I think that in the second query it's trying to count how many distinct primary objects are there, but the query is wrong...
      The

      count((wv0514afil0_.DT_REFERENCE,
                  wv0514afil0_.SQ_FILIALE_ID))

      statement has a bad syntax and so I've got the exception...


      15:11:01,885 WARN  [JDBCExceptionReporter] SQL Error: 907, SQLState: 42000
      15:11:01,885 ERROR [JDBCExceptionReporter] ORA-00907: missing right parenthesis



      Here it is the full stack trace:


      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
           at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
           at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:128)
           at org.jboss.seam.framework.EntityQuery.initResultCount(EntityQuery.java:135)
           at org.jboss.seam.framework.EntityQuery.getResultCount(EntityQuery.java:126)
           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:597)
           at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
           at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
           at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:97)
           at org.jboss.seam.util.Work.workInTransaction(Work.java:47)
           at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:91)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
           at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
           at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
           at it.cogmps.massivesim.session.Wv0514aFilialeList_$$_javassist_seam_3.getResultCount(Wv0514aFilialeList_$$_javassist_seam_3.java)
           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:597)
           at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
           at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:54)
           at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:72)
           at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
           at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
           at org.jboss.el.parser.AstChoice.getValue(AstChoice.java:29)
           at org.jboss.el.parser.AstChoice.getValue(AstChoice.java:29)
           at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
           at com.sun.facelets.el.ELText$ELTextVariable.writeText(ELText.java:184)
           at com.sun.facelets.el.ELText$ELTextComposite.writeText(ELText.java:108)
           at com.sun.facelets.compiler.TextInstruction.write(TextInstruction.java:45)
           ... 58 more
      Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
           at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
           at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
           at org.hibernate.loader.Loader.doList(Loader.java:2231)
           at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
           at org.hibernate.loader.Loader.list(Loader.java:2120)
           at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
           at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
           at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
           at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
           at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
           at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88)
           ... 94 more
      Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis
      
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
           at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:922)
           at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1122)
           at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:973)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1244)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3469)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3514)
           at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
           at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
           at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
           at org.hibernate.loader.Loader.doQuery(Loader.java:697)
           at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
           at org.hibernate.loader.Loader.doList(Loader.java:2228)
           ... 102 more



      Please help me,
      Thanks in advance.


      Alex

        • 1. Re: EntityQuery fails on getResultCount with composite primary keys on Oracle
          thierry accart Newbie

          Hi



          I think that it's not Sean, but hibernate who's guily : see bug HSEARCH-306...



          We're using Microsoft SQLServer and I just discovered the same issue as yours :
          the ejbql


          select count(userRole) from UserRole userRole



          is sent to sql as


          declare @P1 int
          set @P1=NULL
          declare @P2 int
          set @P2=0
          declare @P3 int
          set @P3=0
          exec sp_cursorprepexec @P1 output, @P2 output, NULL, N'select count((userrole0_.roleId, userrole0_.userId)) as col_0_0_ from TP_UM_UserRole userrole0_', 16, 8193, @P3 output
          select @P1, @P2, @P3



          and SQLServer does not understand the part



            count((userrole0_.roleId, userrole0_.userId)).
          




          That's too bad, because a simple



             select count(*) from TP_UM_UserRole 




          would work fine. Only index would be scanned I think.


          • 3. Re: EntityQuery fails on getResultCount with composite primary keys on Oracle
            Alessandro Torrisi Newbie

            Hi my friend, I think the problem is in org.jboss.seam.framework.EntityQuery. I solved the problem subclassing it and overriding the method getCountEjbql in the following way:


            package demo.session;
            
            import java.util.regex.Matcher;
            import java.util.regex.Pattern;
            
            import org.jboss.seam.framework.EntityQuery;
            
            public class OracleCompliantEntityQuery<T> extends EntityQuery<T> {
            
                 private static final long serialVersionUID = -8919405191396687051L;
            
                 private static final Pattern FROM_PATTERN = Pattern.compile(
                           "(^|\\s)(from)\\s", 2);
                 private static final Pattern WHERE_PATTERN = Pattern.compile(
                           "\\s(where)\\s", 2);
                 private static final Pattern ORDER_PATTERN = Pattern.compile(
                           "\\s(order)(\\s)+by\\s", 2);
            
                 @Override
                 protected String getCountEjbql() {
                      String ejbql = getRenderedEjbql();
                      Matcher fromMatcher = FROM_PATTERN.matcher(ejbql);
                      if (!fromMatcher.find()) {
                           throw new IllegalArgumentException("no from clause found in query");
                      } else {
                           int fromLoc = fromMatcher.start(2);
                           Matcher orderMatcher = ORDER_PATTERN.matcher(ejbql);
                           int orderLoc = orderMatcher.find() ? orderMatcher.start(1) : ejbql
                                     .length();
                           Matcher whereMatcher = WHERE_PATTERN.matcher(ejbql);
                           int whereLoc = whereMatcher.find() ? whereMatcher.start(1)
                                     : orderLoc;
                           String subject = "*";
                           return (new StringBuilder(ejbql.length() + 15)).append(
                                     "select count(").append(subject).append(") ").append(
                                     ejbql.substring(fromLoc, whereLoc).replace("join fetch",
                                               "join"))
                                     .append(ejbql.substring(whereLoc, orderLoc)).toString()
                                     .trim();
                      }
                 }
            
            }
            



            package demo.session;
            
            import demo.entity.User;
            
            import java.util.Arrays;
            
            import org.jboss.seam.annotations.Name;
            import org.jboss.seam.framework.EntityQuery;
            
            @Name("userList")
            public class UserList extends EntityQuery<User> {
            
                 private static final String EJBQL = "select user from User user";
            
                 private static final String[] RESTRICTIONS = {
                           "lower(user.txUser) like lower(concat(#{userList.user.txUser},'%'))",
                           "lower(user.dsUser) like lower(concat(#{userList.user.dsUser},'%'))",
                           "lower(user.txPassword) like lower(concat(#{userList.user.txPassword},'%'))", };
            
                 private User user = new User();
            
                 public UserList() {
                      setEjbql(EJBQL);
                      setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                      setMaxResults(25);
                 }
            
                 public User getUser() {
                      return user;
                 }
            }
            



            I don't know if my subclassing is correct and complete, but extending my OracleCompliantEntityQuery instead of EntityQuery it runs correctly now...


            Now I correctly have:


            select count(*) from user



            instead of the count on the composite primary keys fields :-D
            Let me know what you think about this solution.


            Thanks in advance,
            Alex.





            • 4. Re: EntityQuery fails on getResultCount with composite primary keys on Oracle
              thierry accart Newbie

              your workaround seems correct... as a workaround, but any time you (anybody) run a select count (entity) from Entity entity, the problem will be there.



              The fact is that ejbql is not correctly transformed in native query language.
              It's a problem at least for MSSQL, but also for http://opensource.atlassian.com/projects/hibernate/browse/HHH-3096.


              Rgds


              • 5. Re: EntityQuery fails on getResultCount with composite primary keys on Oracle
                Marco De Toni Newbie
                Hello,
                I have a similar issue with firebird 2.1, the syntax    
                select first ? count((column1, column2, column3))
                raises an exception in firebird:
                Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
                SQL error code = -104
                Token unknown - line 1, column 48
                ,

                Regards
                • 6. Re: EntityQuery fails on getResultCount with composite primary keys on Oracle
                  Rainer Schön Newbie

                  I know, the thread is a little bit old, but maybe my contribution is helpful to other persons.


                  Try this override of the getCountEjbql(), which is less verbose:


                  /* 
                   * Set the wild card flag to true (which is obviously set to false when
                   * you use jpa compliant persistence. This flag must be true, if a composite
                   * primary key is used by the entity the list consists of. If the primary
                   * key is a simple key, this override is not necessary.
                   *
                   * The documentation in the seam source says, the use of the wild card (*) in a count
                   * query is not jpa compliant, so I'm not sure, if it works  with a persistence 
                   * provider other than Hibernate.
                   */
                          @Override
                       protected String getCountEjbql() {
                            setUseWildcardAsCountQuerySubject(true);
                            return super.getCountEjbql();                    
                       }
                  


                  Hope, it helps.