JAVA调用MYSQL存储过程实现分页

create procedure newpage()

  begin

  select count(*) from admin;

  end;

  

 

 

 create procedure jilu(in news int,in pagesizeint)

  begin

  set @news = (news-1)*pagesize;

  set @pagesize = pagesize;

  set @COUNT_STRING= concat("SELECT * FROM admin LIMIT",@news,",",@pagesize);

  prepare count_string from @COUNT_STRING;

  execute count_string;

  deallocate prepare count_string;

  end

  实现方法

 

 

 

 

 

 

 

 

 

 

 

 

 

package cn.csdn.web.dao;

 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Types;

importjava.util.ArrayList;

importjava.util.List;

 

importcn.csdn.web.domain.Admin;

 

public classAdminDaoImpl implements AdminDao{

       /*封装数据库操作的接口*/

       private static Connection conn;

       privatePreparedStatement pstmt;

       privateResultSet rs;

       privateCallableStatement st = null;

       /*封装每页显示的记录数*/

       privatestatic final Integer PAGESIZE=3;

       //总记录数

       privateInteger countRecord;

       //总页数

       privateInteger countPage;

       /*声明URL地址*/

       privatestatic final String URL="jdbc:mysql://localhost:3306/3g?user=root&password&useUnicod=true&characterEncoding=UTF-8";

       static{

              /*准备驱动程序，加载驱动*/

              try{

                     Class.forName("com.mysql.jdbc.Driver");

                     /*创建连接对象*/

                     try{

                            conn=DriverManager.getConnection(URL);

                     }catch (SQLException e) {

                            e.printStackTrace();

                     }

              }catch (ClassNotFoundException e) {

                     e.printStackTrace();

              }

             

       }

       publicList<Admin> findNowPageInfo(Integer nowpage) {

              //声明返回值变量

              List<Admin>allentities = new ArrayList<Admin>();

           try{

           st= conn.prepareCall("{call jilu(?,?)}"); 

           st.setInt(1, nowpage);

           st.setInt(2, 3); 

           boolean hadOtherRS = st.execute();

                     while(hadOtherRS) {

                            rs= st.getResultSet();

                            while(rs.next()){

                                   Adminentity = new Admin();

                                   entity.setId(rs.getInt("id"));

                                   entity.setName(rs.getString("name"));

                                   entity.setPass(rs.getString("pass"));

                                   entity.setSex(rs.getString("sex"));

                                   entity.setAge(rs.getInt("age"));

                                   allentities.add(entity);

                         }

                            hadOtherRS= st.getMoreResults();

                     }

                     release(rs,pstmt);

              }catch (SQLException e) {

                     //TODO Auto-generated catch block

                     e.printStackTrace();

              }

             

              returnallentities;

       }

      

       privatevoid release(ResultSet rs, PreparedStatement pstmt) {

              if(rs!=null){

                     try{

                            rs.close();

                     }catch (SQLException e) {

                            //TODO Auto-generated catch block

                            e.printStackTrace();

                     }}

                     if(pstmt!=null){

                            try{

                                   pstmt.close();

                            }catch (SQLException e) {

                                   //TODO Auto-generated catch block

                                   e.printStackTrace();

                            }

                     }

             

             

       }

 

       publicInteger findCountRecord() {

             

              try{

                     st=conn.prepareCall("{callnowpage}");

                     booleanhadOtherRS=st.execute();

                     while(hadOtherRS) {

                            rs= st.getResultSet();

                            while(rs.next()){

                            this.countRecord= rs.getInt(1);

                     }

                     hadOtherRS= st.getMoreResults();

              }

                     release(rs,pstmt);

              }catch (SQLException e) {

                     //TODO Auto-generated catch block

                     e.printStackTrace();

              }

              returnthis.countRecord;

       }

 

       publicInteger findCountPage() {

              findCountRecord();

              this.countPage= this.countRecord%this.PAGESIZE==0?this.countRecord/this.PAGESIZE:this.countRecord/this.PAGESIZE+1;

              returnthis.countPage;

       }

 

}

具体代码详见            http://download.csdn.net/detail/pzw0416/3804793