Java, prepareStatement, SQL_CALC_FOUND_ROWS, FoundRows() hows it work

Question

I'm trying to do pagination from the db and so far I have found that if you want to use limit you should use SQL_CALC_FOUND_ROWS.

However, in Java I'm having some issues understanding how to call this.

Can I do it in one prepareStatement or will I need to make multiple calls? If im making multiple calls, how does MySQL know I'm referring to the last call for the count?

Thanks.

Please tell us what you want to achieve. Do you want to use the limit clause in a SQL query? — JB Nizet, Jan 19 '12 at 15:57

score 0 · Accepted Answer · answered Jan 19 '12 at 16:00

0

You'll have to make two calls (unless you implement a some stored function in the db which returns both the limited resultset and the count, which I assume is possible in mysql but never tried that).

MySQL will know what query counts to return because both the query with the limit and the SELECT FOUND_ROWS(); will be in the same session.

answered Jan 19 '12 at 16:00

bpgergo

15,669
5
44
68

Is there any risk of `found_rows` being associated with a different query (eg: if two queries are called one right after the other)? I call `ps = connection.prepareStatement(originalQuery)` then process the result set, then call `ps = connection.prepareStatement(totalRowsQuery)` then process its result set. Any risk of query being called in the same session at the time I am processing the original query? – theyuv Mar 14 '16 at 12:45

Java, prepareStatement, SQL_CALC_FOUND_ROWS, FoundRows() hows it work

1 Answers1