MySQL stored procedure page and total number of returned pages

Because MySQL currently does not support dynamic parameter paging, you can either pass the SQL statement in the stored procedure or write a string connection method by yourself.

Method 2

Write one line for each place to be split and clear

1. BEGIN

1. SELECTCEIL (COUNT(*)/PageNum)INTOPageCountFROMMytestdb. mttestWHERETt_id = arg_mt_id;

1. SET@ SqlStr = CONCAT ('Select' mt _ id', 'tt _ id', 'tt _ name' FROM 'mytestdb'. 'mttest' WHERE 'tt _ id' =');

1. SET@ SqlStr = CONCAT (@ sqlStr, arg_mt_id );

1. SET@ SqlStr = CONCAT (@ sqlStr,'Limit');SET@ SqlStr = CONCAT (@ sqlStr, (pageNum-1) * pageSize ));

1. SET@ SqlStr = CONCAT (@ sqlStr,',');

1. SET@ SqlStr = CONCAT (@ sqlStr, pageSize );

1. SET@ SqlStr = CONCAT (@ sqlStr,';');

1. PREPARES1FROM@ SqlStr;

1. EXECUTES1;

1. DEALLOCATE PREPARES1;

1. END

Or write a row, which is efficient but easier to write errors.

1. BEGIN

1. SELECTCEIL (COUNT(*)/PageNum)INTOPageCountFROMMytestdb. mttestWHERETt_id = arg_mt_id;

1. SET@ SqlStr = CONCAT ('Select' mt _ id', 'tt _ id', 'tt _ name' FROM 'mytestdb'. 'mttest' WHERE 'tt _ id' =', Arg_mt_id,'Limit',

1. (PageNum-1) * pageSize ),',', PageSize';');

1. PREPARES1FROM@ SqlStr;EXECUTES1;DEALLOCATE PREPARES1;END

Java end

1. Try{Connection conn = db. openDB (getServletContext (); String SQL ="{Call 'mypagingtest '(?,?,?,?)} "; CallableStatement cs = conn. prepareCall (SQL); cs. setInt (1,1); Cs. setInt (2,1); Cs. setInt (3,5); Cs. registerOutParameter (4, Types. INTEGER); rs = cs.exe cuteQuery (); System. out. println (cs. getInt (4));While(Rs. next () {out. println (rs. getInt (1); Out. println (rs. getInt (2); Out. println (rs. getString (3) +"<Br>");}}Catch(SQLException e ){// TODO Auto-generated catch blocke. printStackTrace ();} finally {try {if (rs! = Null) {rs. close () ;}if (db! = Null) {db. close () ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}