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

[jbbarquero]

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:

@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:

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:

    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

[jbbarquero]

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?

[jbbarquero]

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