Announcement Announcement Module
Collapse
No announcement yet.
How can I create a custom count Query? (Roo and DB2 with composite PK) Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I create a custom count Query? (Roo and DB2 with composite PK)

    Hello,

    I'm trying to access an entity with composite PK in DB2. The code has been generated using Roo (1.2.1), so it comes out something like this:

    Code:
    @RooToString
    @RooEquals
    @RooIdentifier
    public final class MyEntityId {
    
        @NotNull
        @Column(name = "C_CODE", columnDefinition = "char(10)")
        @Size(max = 10)
        private String code;
    
        @NotNull
        @Column(name = "C_KEY", columnDefinition = "char(2)")
        @Size(max = 2)
        private String theKey;
    In the version of DB2 I have, the JPA select is:

    Code:
    select count((myentity0_.c_code, myentity0_.c_key)) as col_0_0_ from t_table myentity0_
    But it throws a SQLGrammarException:

    Caused by: com.ibm.db2.jcc.b.eo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=,;+ -, DRIVER=3.53.95
    at com.ibm.db2.jcc.b.bd.a(bd.java:676)
    at com.ibm.db2.jcc.b.bd.a(bd.java:60)
    at com.ibm.db2.jcc.b.bd.a(bd.java:127)
    at com.ibm.db2.jcc.b.gm.c(gm.java:2484)
    at com.ibm.db2.jcc.b.gm.d(gm.java:2461)
    at com.ibm.db2.jcc.b.gm.a(gm.java:1962)
    at com.ibm.db2.jcc.b.gm.a(gm.java:1940)
    at com.ibm.db2.jcc.t4.db.g(db.java:133)
    at com.ibm.db2.jcc.t4.db.a(db.java:38)
    at com.ibm.db2.jcc.t4.t.a(t.java:32)
    at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
    at com.ibm.db2.jcc.b.gm.bb(gm.java:1933)
    at com.ibm.db2.jcc.b.hm.ec(hm.java:2558)
    at com.ibm.db2.jcc.b.hm.e(hm.java:3259)
    at com.ibm.db2.jcc.b.hm.e(hm.java:3425)
    at com.ibm.db2.jcc.b.hm.Rb(hm.java:583)
    at com.ibm.db2.jcc.b.hm.executeQuery(hm.java:557)
    at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:96 )
    at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:96 )
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:80 2)
    at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:254 2)
    ... 61 more
    The solution with a @RooJpaActiveRecord is to rename the query to:

    Code:
        public static long countMyEntities() {
            return entityManager().createQuery("SELECT COUNT(*) FROM MyEntity o", Long.class).getSingleResult();
        }
    How can I achieve the same with Spring Data JPA (@RooJpaEntity)?

    I've done several attempts without success:

    @RooJpaRepository(domainType = MyEntity.class)
    public interface MyEntityRepository {

    //With DATA_JP 1.1.0.RELEASE
    @Query(value = "SELECT COUNT(*) FROM MyEntity o ", nativeQuery = true)
    public long count();

    //With DATA_JP 1.0.3.RELEASE
    @Query("SELECT COUNT(*) FROM MyEntity o ")
    public long count();
    }
    I can't find information on this regard (queries for counting) not even in the official documentation

    Thank you very much in advance

  • #2
    I've created a similar POST in the Roo forums, just in case it's related with the way Roo creates the code.

    Please, find the new POST: Repository JPA, how can I create a custom COUNT query?

    Comment


    • #3
      Updated. See: Select count(*) as count query for findAll(Pageable) is not being called

      Comment

      Working...
      X