jdbcTemplate query with multiple queries - spring boot

Question

When I execute the following query, its work fine

jdbcTemplate.query("select * from foo Limit 50");

But when I want to try to execute the following query , it giving me syntax error even successfully running in mysql

jdbcTemplate.query("select SQL_CALC_FOUND_ROWS * from foo Limit 10; SELECT FOUND_ROWS()");

any update ?

So you do a multiple select, what do you expect the jdbc template to return? — NiVeR, Apr 23 '18 at 18:32
@NiVeR https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_database — Shahid Ghafoor, Apr 23 '18 at 18:34
@NiVeR in mysql, this query returning 2 result set, one for foo result and other for count result , I want to achieve the same! — Shahid Ghafoor, Apr 23 '18 at 18:39
It seems yo can execute them. I'm not sure though about accessing the results: https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement — Jens Schauder, May 01 '20 at 06:58

NiVeR · Answer 1 · 2018-04-24T07:33:38.293

3

You can't execute two queries on a single query execution from the template. It expects (correctly) that a single execution of its corresponding method will execute a single query, and hence return a single result. The key point is the ; character. I believe that's the cause of the syntax error, exactly because of what you are trying to do.

There are two ways to go around this:

Perform 2 different queries, one for the limit and one for the total count
Implement a stored procedure that will give you both results, and this time you can get them with just a single query

If you choose the first approach and you want to synchronize you can check this link.

edited Apr 24 '18 at 07:33

answered Apr 23 '18 at 18:43

NiVeR

9,644
4
30
35

I know we can use stored procedure. I am facing issue with query and in my scenario I am unable to use stored procedure due to various reason.. currently I am executing two queries ,but the issue is that before running the second query .. another thread may run the first query .. which will lead to wrong result .. any update ? How we can ensure that no other thread will not destroy the result. – Shahid Ghafoor Apr 24 '18 at 05:20
You can put the two queries in a single sinchronized block. – NiVeR Apr 24 '18 at 05:53
There are multiple methods that can run select query i.e. – Shahid Ghafoor Apr 24 '18 at 07:27
Hmm, and so? I am referring to a synchronized keyword which guarantees that the code inside the block will be accessed in synchronized manner, in other words, by one thread at a time. I updated the answer with a link. – NiVeR Apr 24 '18 at 07:31
I mean there are so many other method that are not depending on this method in which I want to execute these both queries at once, so synchronization will not work here – Shahid Ghafoor Apr 24 '18 at 07:34

jdbcTemplate query with multiple queries - spring boot

1 Answers1