Can I create this query in JPA without using native query?

Question

I would like to create this query using JPA Repository. How can I do this? My problem is I cannot get the COUNT(1) in entity management class. I do not want to use native query @Query.

     SELECT distinct COUNT(1) as count, reject_cd FROM TXN_RESP_PRICE_REJECT rej 
     GROUP BY rej.reject_cd FETCH FIRST 10 ROWS ONLY

Here is the top part of entity class without setters and getters.

          @Entity
          @Table(name = "txn_resp_price_reject")
          public class TxnRejectDTO {
              @Id
              private String rejectCd;
              private String count;

              public TxnRejectDTO() {
              }
          }

TXNREJECTD0_"."COUNT": invalid identifier. This is not a native query. @gnanajeyam95 — dropkick, Jan 17 '20 at 18:29
I am using findAll(Specification). My main issue is getting the count in the Entity class. @gnanajeyam95 — dropkick, Jan 17 '20 at 18:44
Then mention your criteria query. What you are trying to pass. — GnanaJeyam, Jan 17 '20 at 18:47
Let me change the code to show you what I am doing. I am calling jpa repository class. List — dropkick, Jan 17 '20 at 18:51
What do you mean by "not using native query `@Query` ? Do you mean not even JPQL query or is this something possible ? — RUARO Thibault, Jan 17 '20 at 19:33
Like in the JPA Repository class I dont want to specify the exact query. @Query("SELECT distinct COUNT(1) as count, reject_cd FROM TXN_RESP_PRICE_REJECT rej GROUP BY rej.reject_cd DESC FETCH FIRST 10 ROWS ONLY") @RUAROThibault — dropkick, Jan 17 '20 at 19:36
Does this answer your question? [JPA and aggregate functions. How do I use the result of the query?](https://stackoverflow.com/questions/2911558/jpa-and-aggregate-functions-how-do-i-use-the-result-of-the-query) — mentallurg, Jan 17 '20 at 19:44
@dropkick: In that example there is NO SQL. This is JPQL. I suppose you don't know the difference. Briefly, JPQL is a cross platform language that doesn't depend on specific database. You asked for *without using native query*. JPQL does exactly that. — mentallurg, Jan 17 '20 at 20:19

score 0 · Answer 1 · answered Jan 17 '20 at 20:43

I think what you are trying to do is not do-able... Even if it were, I wouldn't recommend it...

The purpose of an ORM (Object Relational Mapping) is to map your java code into something that a Relational Database can understand. Basically, you can translate a column of a table into an attribute of a class.

Here, you are trying to do something a bit more complicated : you map the content of a query with an aggregate function (count) into a java object... but there is no column to map.

I think that you are experiencing what I call a "Post traumatic JPA syndrome": Basically, you think JPA will rule the world and you don't see the point of writing SQL query yourself. You are somewhat allergic to SQL :)

In your case, you are not mapping a table, but the result of a query. Which is not what ORM is made for.

Here is 2 solutions.

Query + JPA mapping your table

Your TxnRejectDTO does not map one table, therefor, you should drop the annotations. You can create another object that maps the table "TXN_RESP_PRICE_REJECT" : TxnRespProceReject. Something like this:

@Entity
@Table(name = "txn_resp_price_reject")
public class TxnRespPriceReject {
    @Id
    private String rejectCd;
    private String aColumn;

    public TxnRespPriceReject() {
    }

    // generate setters and getters

}

And create a repository to access your data. Spring will be able to "bind" your Repository to your Entity. And in this, add your query to retrieve your TxnRejectDTO :

public interface TxnRejectPriceRejectRepository extends CrudRepository< TxnRespPriceReject, Long> {
    @Query("SELECT new your.package.TxnRejectDTO(rejectCd, count(t)) FROM TxnRejectPriceReject t GROUP BY t.rejectCd")
    public List<TxnRejectDTO> findTxnReject();
}

Please note the usage of fully qualified name for the constructor (with the package).
In the meantime, make TxnRejectDTO a simple bean by removing the annotation related to Hibernate, with an all args constructor.

public class TxnRejectDTO {
    private String rejectCd;
    private String count;

    public TxnRejectDTO(String rejectCd, Long count) {
        this.rejectCd = rejectCd;
        this.count = count;
    }
}

That should do it... But not sure it's what you want that. Here is a good link: How to return a custom object from a Spring Data JPA GROUP BY query

JPA without QUERY

So, if you became really allergic to SQL in your java code, why don't you use a view ?

Create a view in your database

CREATE VIEW txn_reject AS SELECT reject_id, count(1) as count from TXN_RESP_PRICE_REJECT rej GROUP BY rej.reject_cd FETCH FIRST 10 ROWS ONLY

Map your TxnRejectDTO to your view

@Entity
@Table(name = "txn_reject")
@Immutable // To let Spring know it won't need to update it (lighter, no cache...)
public class TxnRejectDTO {
    @Id
    private String rejectCd;
    private String count;

    public TxnRejectDTO() {
    }
}

And then declare a repository to access your entity:

public interface TxnRejectDTORepository extends CrudRepository<TxnRejectDTO, Long> {
// the method findAll is provided, so you can get everything you need.
}

Here is a link for this solution: https://thoughts-on-java.org/hibernate-tips-map-view-hibernate/

Hope it helps !

Thanks for the tips. The only reason I dont want a view or sql in my java is because I needed a dynamic where clause for some search fields in my UI. — dropkick, Jan 21 '20 at 15:05

Can I create this query in JPA without using native query?

1 Answers1

Query + JPA mapping your table

JPA without QUERY