mysql分页查询通用存储过程

分页查询存储过程

最新推荐文章于 2021-08-25 23:00:07 发布

转载最新推荐文章于 2021-08-25 23:00:07 发布 · 1k 阅读

文章标签：

#mysql #存储 #list #session #query #java

java and procedurces 同时被 2 个专栏收录

2 篇文章

订阅专栏

数据库 - mysql

2 篇文章

订阅专栏

本文介绍了一个用于数据库分页查询的存储过程实现方法，并提供了具体的调用示例。该存储过程支持灵活的分页参数设置，包括页码、列名、排序方式等。

DELIMITER $$ DROP PROCEDURE IF EXISTS prc_page_result $$ CREATE PROCEDURE prc_page_result ( IN currpage INT, IN COLUMNS VARCHAR(500), IN tablename VARCHAR(500), IN sCondition VARCHAR(500), IN order_field VARCHAR(100), IN asc_field INT, IN primary_field VARCHAR(100), IN pagesize INT ) BEGIN DECLARE sTemp VARCHAR(1000); DECLARE sSql VARCHAR(4000); DECLARE sOrder VARCHAR(1000); IF asc_field = 1 THEN SET sOrder = CONCAT(' order by ', order_field, ' desc '); SET sTemp = '<(select min'; ELSE SET sOrder = CONCAT(' order by ', order_field, ' asc '); SET sTemp = '>(select max'; END IF; IF currpage = 1 THEN IF sCondition <> '' THEN SET sSql = CONCAT('select ', COLUMNS, ' from ', tablename, ' where '); SET sSql = CONCAT(sSql, sCondition, sOrder, ' limit ?'); ELSE SET sSql = CONCAT('select ', COLUMNS, ' from ', tablename, sOrder, ' limit ?'); END IF; ELSE IF sCondition <> '' THEN SET sSql = CONCAT('select ', COLUMNS, ' from ', tablename); SET sSql = CONCAT(sSql, ' where ', sCondition, ' and ', primary_field, sTemp); SET sSql = CONCAT(sSql, '(', primary_field, ')', ' from (select '); SET sSql = CONCAT(sSql, ' ', primary_field, ' from ', tablename, sOrder); SET sSql = CONCAT(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder); SET sSql = CONCAT(sSql, ' limit ?'); ELSE SET sSql = CONCAT('select ', COLUMNS, ' from ', tablename); SET sSql = CONCAT(sSql, ' where ', primary_field, sTemp); SET sSql = CONCAT(sSql, '(', primary_field, ')', ' from (select '); SET sSql = CONCAT(sSql, ' ', primary_field, ' from ', tablename, sOrder); SET sSql = CONCAT(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder); SET sSql = CONCAT(sSql, ' limit ?'); END IF; END IF; SET @iPageSize = pagesize; SET @sQuery = sSql; PREPARE stmt FROM @sQuery; EXECUTE stmt USING @iPageSize; END; $$

调用：

1。当前页2.查询的列,3.表名,4.列名,排序方式，1为倒序,排序的列名,页大小

CALL prc_page_result(1, "*", "solexa_annotation", "", "queryName", 1, "id", 25);

原文：

java调用：

   1. CallableStatement call = conn.prepareCall("{Call proc(?)}");
   2. call.setString(1, 参数);
   3. rs = call.executeQuery();

Session session =HibernateSessionFactory.getSession();
SQLQuery query = session.createSQLQuery("{Call proc()}");
List list =query.list();

   1. SQLQuery query = session.createSQLQuery("{Call proc(?)}");
   2. query.setString(0, 参数);
   3. List list =query.list();

create   PROCEDURE proc()
begin
     select   *   from proctab;
end ;