Passing a query as a query parameter in JPQL Native Query

Question

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.

What you are trying is not possible. Parameters will be bound using a prepared statement and the you cannot pass a query. Why don't you write a query with the real statement you want to execute? — Simon Martinelli
– Simon Martinelli, Commented Jul 12, 2021 at 13:56
@SimonMartinelli i know it's a strange implementation but the query I'm passing as a parameter is generated and stored in a table and it represents part of a larger formula that can change so that's why I'm trying to build the query this way so I can create a single query with all the generated subqueries inside. — cheroz
– cheroz, Commented Jul 12, 2021 at 14:02
Hi @cheroz how did you get this to work in the end? I am in a similar position right now. — Diana Eftaiha
– Diana Eftaiha, Commented May 31, 2024 at 6:58

v.ladynev · Accepted Answer · 2021-07-12 14:33:44Z

Create an interface for a custom repository SomeRepositoryCustom

public interface SomeRepositoryCustom { BigDecimal getTotal(String sql); }

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); } }

Extend your JpaRepository with SomeRepositoryCustom

@Repository public interface SomeRepository extends JpaRepository, SomeRepositoryCustom { }

to run a query

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

Collectives™ on Stack Overflow

Passing a query as a query parameter in JPQL Native Query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related