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