How to use a variable as a value for a @Query in Spring

Admin 1
Viewed 253

I know the title is not really explaining the problem so I will try to give more details.

I have a String in the Controller, something like this (keep in mind that this String is not manually written since it's dynamic, there are 7 filters and I want to return the information based on the filters selected, so I can't just put the String in the @Query value, since some filters will remain untouched and you cannot SELECT * WHERE value = *)

String query = "SELECT * FROM table WHERE column1=value1 AND column2=value2"

Now, the Queries are made in the Repository, but I really have no idea how to make the value of that query to be this String, something like this :

@Query(value = query, nativeQuery = true)
public List<ExampleEntity> testFunction(String query);

My first idea was to do this :

@Query(value = "?", nativeQuery = true)
public List<ExampleEntity> testFunction(String query);

But it inputs the whole String inside ' '

'SELECT * FROM table WHERE column1=value1 AND column2=value2'

(now that I think about this, it does that because the compiler sees this as like I'm trying to set the value of something to be the String, makes sense but I wish there could be a way to simply set the input of the function to be the value of the Query :/ )

Sooo ... is there any other way to use that string as the query value?

spring-data-jpa spring mysql
2 Answers

Annotations are on classes, they are set at compile time. You can’t change them at runtime. Spring can’t change them either.

What you could do is check out how to use Specifications to construct queries, or use your existing query constructing code within a custom JPA repository. Defining repositories with interfaces is a good option for simple cases, but this is not a simple case.

It would be better to move your query building code out of the controller layer and into the repository, btw. Even assuming everything in the current workflow is secure, having a repository take a native query as a parameter just isn’t a good look, it’s wide open to abuse.

user_2173240

I took a different approach to solve this problem that is much simpler than using Specifications.

After searching how can you send a Query in the Controller I found jdbcTemplate that is Standard in Spring (no need to add a dependency or anything else)

These are the steps that I took and in the end, everything works at it should.

1 Autowire the JdbcTemplate in the Controller

@Autowired
private JdbcTemplate jdbcTemplate;

2 Set the return to be the Query

return jdbcTemplate.queryForList(query);

Keep in mind that this is for multiple Columns, if you have to return only one column I think you have to use something like this :

return jdbcTemplate.query(query, new SingleColumnMapper(Example.class)

It might not be the safest or the most recommended way of solving this, but it worked for me.

Admin1
Related