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.

Answer 1

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.