CODEWITHSUNDEEP
javasqljpajpql
cheroz
cheroz

Reputation: 127

Passing a query as a query parameter in JPQL Native Query

I'm trying to pass a query as a string parameter in another JPQL native query.

@Query(value = "SELECT (:query)", nativeQuery = true)
BigDecimal getTotal(@Param("query") String query);

So the resulting query would be something like the query below that returns me a value

SELECT (50 * (SELECT COUNT(*) FROM SOMETABLE))

but what I'm getting in return is only the string of :query parameter and not the result of the executed complete query.

Upvotes: 0

Views: 974

Answers (1)

v.ladynev
v.ladynev

Reputation: 19956

  1. Create an interface for a custom repository SomeRepositoryCustom
public interface SomeRepositoryCustom {

    BigDecimal getTotal(String sql);

}
  1. Create an implementation of SomeRepositoryCustom
@Repository
class SomesRepositoryCustomImpl implements SomeRepositoryCustom {

    private JdbcTemplate template;

    @Autowired
    public SomesRepositoryCustomImpl(JdbcTemplate template) {
        this.template = template;
    }

    @Override
    public BigDecimal getTotal(String sql) {
        return template.queryForObject(sql, BigDecimal.class);
    }

}
  1. Extend your JpaRepository with SomeRepositoryCustom
@Repository
public interface SomeRepository extends JpaRepository, SomeRepositoryCustom {

}

to run a query

someRepository.getTotal("SELECT (50 * (SELECT COUNT(*) FROM SOMETABLE))");

Upvotes: 1

Related Questions