Определение кол-ва записей с лимитом, найденных по запросу SQL (JDBC)

175

28 августа 2018, 15:50

Хочу делать запрос в базу данных MySQL, мне нужно получить только 5 записей из неё, но при этом мне необходимо сосчитать общее количество записей, удовлетворяющих моему условию.

То есть мне надо по строке запросе пользователя вывести ему на 1 страницу только 5 записей, но кол-во страниц придётся считать от общего количества найденных записей.

Я понимаю, что можно сделать запрос без лимита, но тогда мне вернется слишком много данных, а если делать 2 запроса: 1 получает кол-во записей, а второй получает 5 записей, то придётся 2 раза присоединяться к бд и тратить дополнительное время на поиск заново.

Вопрос, можно ли как-то это сделать в 1 SQL запросе или минимизировать затраты ресурсов при 2-х запросах. Поправьте, если что-то не так понимаю.

Заранее спасибо за любую помощь.

Answer 1

Оригинал ответа

Это возможно сделать при помощи оконных функций, но в MySql их ввели только с 8-й версии (см. статью или cюда). Вот пример использования:

create table test (
id integer,
field integer  
);
insert into test (id, field) values (1,10);
insert into test (id, field) values (2,10);
insert into test (id, field) values (3,10);
insert into test (id, field) values (5,20);
SELECT id 
, count(*) OVER() AS full_count 
FROM   test
WHERE  field=10
LIMIT  2;

И наглядный результат работы (но для postgress, т.к. указанный ресурс даёт возможность потестировать только запрос для MySql 5.6)

P.S. Единственный момент, что у тебя в каждой записи будет общее количество записей из выборки. Но я не думаю, что это будет какой-то весомой проблемой)

Answer 2

Копайте в сторону SQL_CALC_FOUND_ROWS.

Answer 3

with cte as
(select * from t where ...)
select *, (select count(*) from cte)  qty  from cte limit 5
;

Answer 4

Вам надо создать скроллируемый Statement, по умолчанию ResultSet может двигаться только вперед, то есть если вы попробуете посчитать количество возвращенных строк, то обратно не сможете вернуться. Надо сделать что-то типа:

//скроллируемый указатель только для чтения
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
               ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM " + dbName);
rs.last(); //двигаем указатель на последнюю запись
int recs=rs.getRow(); //получаем количество записей
rs.beforeFirst(); //двигаем курсор перед первой записью
while (rs.next()) {
    //работаем с записями
}

Answer 5

хорошие ответы уже были, но позвольте и мне тоже что то сказать :) и так:

можете смотреть вот ->сюда<- вроде как есть на ваш вопрос ответ
можете просто получить все записи и на уровне языка получить и нужные 5 записей и общее количество в бд. типа так

resultList.size() //дает общее количество for (int i = 0; i < 5; i++) { System.out.println(mc.toString()); }
можете воспользоваться с процедурой, только в этом случае вам придется писать два резалтсета

CREATE DEFINER = 'test'@'%' PROCEDURE test.procedure1 () BEGIN SELECT COUNT(t.id) AS count FROM table m; SELECT t.id, t.state, t.name FROM table t LIMIT 5; END