CODEWITHSUNDEEP
javamysqljpa
radio_head
radio_head

Reputation: 1375

How to write a Sql query in Jpa repository?

I am trying to display a set of records from database based on user's search request.I dont think there is a predefined method in JPA repository for this.Hence i would like to write a query in repository for this.

But during build, there is an exception stating as "IllegalArgumentException".

Can someone help me to write a proper query? If there is a better way of doing it, let me know.

The loanReport in the argument is an object which has the user's search request

    public interface LoanReportRepository extends JpaRepository<LoanReport, Long> , JpaSpecificationExecutor<LoanReport> {
    public final static String GET_LOAN_REPORTS = "SELECT * FROM loan_report  WHERE product=loanReport.product";

       @Query(GET_LOAN_REPORTS)
    List<LoanReport> findByPreference(final LoanReport loanReport);

}

Upvotes: 1

Views: 14238

Answers (2)

Merwais Muafaq
Merwais Muafaq

Reputation: 111

You can also try the following code if you use sql

public interface LoanReportRepository extends JpaRepository<LoanReport, Long> , JpaSpecificationExecutor<LoanReport> {
    public final static String GET_LOAN_REPORTS = "SELECT * FROM loan_report  WHERE product = :product";

       @Query(GET_LOAN_REPORTS,nativeQuery=true)
    List<LoanReport> findByPreference(@Param("product")final String prouduct);    
}

Upvotes: 2

Bunti
Bunti

Reputation: 1760

You can invoke JPQL query like this,

public interface LoanReportRepository extends JpaRepository<LoanReport, Long> , JpaSpecificationExecutor<LoanReport> {

public final static String GET_LOAN_REPORTS = "SELECT lr FROM LoanReport lr WHERE product = :product";

@Query(GET_LOAN_REPORTS)
List<LoanReport> findByPreference(@Param("product") product);

Here product can be a value that directly stored in a DB column or another JPA Entity. In latter case entity's identifier will be mapped with the LoanReport's foreign key constraints.

And you can invoke findByPreference by passing the product property directly like this.

 loanReportRepository.findByPreference(loanReport.product);

See here for more information. Documentation is pretty good.

P.S.

As far as I know there is no built-in way to pass just an object and expect all/some of its property values to be mapped to entity fields and then to DB level columns.

Another option is to use Spring Data JPA provided dynamic queries that with all the fields you want to search with. See here for information

If you want to use multiple fields in search query, best option is to pass all those possible parameters as method parameters and map them to query's parameters. This approach would additionally allow you to sanitise or convert user parameter value into a different format that may be stored on the DB level.

Upvotes: 3

Related Questions