分页的业务类（顺平）版

//这是一个处理类(处理users表)<---->操作UserBean

package zxl;

import java.sql.*;

import java.util.*;

public class UserBeanCl {

//业务逻辑

private Connection ct=null;

private PreparedStatement ps=null;

private ResultSet rs=null;

private int pageCount=0;//共有几页(计算)

//修改用户

public boolean updateUser(String id,String passwd,String email,String grade){

boolean b=false;

try {

//得到连接

ConnDB cd=new ConnDB();

ct=cd.getConnection();

String sql="update users set password='"+passwd+"',email='"+email+"',grade='"+grade+"' where userId='"+id+"'";

System.out.println("sql==="+sql);

ps=ct.prepareStatement(sql);

int num=ps.executeUpdate();

if(num==1){

//修改成功

b=true;

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally{

this.close();

}

return b;

}

//删除用户

public boolean DelUser(String id){

boolean b=false;

try {

//得到连接

ConnDB cd=new ConnDB();

ct=cd.getConnection();

String sql="delete from users where userId='"+id+"'";

ps=ct.prepareStatement(sql);

int num=ps.executeUpdate();

if(num==1){

//删除成功

b=true;

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally{

this.close();

}

return b;

}

//返回pageCount

public int getPageCount(){

return this.pageCount;

}

//分页显示

public ArrayList getResultByPage(int pageNow,int pageSize){

ArrayList al=new ArrayList();

try {

int rowCount=0;//一共有几条记录(查表)

//得到rowCount

ConnDB cd=new ConnDB();

ct=cd.getConnection();

ps=ct.prepareStatement("select count(*) from users");

rs=ps.executeQuery();

if(rs.next()){

rowCount=rs.getInt(1);

}

//计算pageCount

if(rowCount%pageSize==0){

pageCount=rowCount/pageSize;

}else{

pageCount=rowCount/pageSize+1;

}

ps=ct.prepareStatement("select top "+pageSize+" * from users where userId not " +

"in(select top "+pageSize*(pageNow-1)+" userId from users)");

rs=ps.executeQuery();

while(rs.next()){

//将rs中的每条记录封装到UserBean ub中

UserBean ub=new UserBean();

ub.setUserId(rs.getInt(1));

ub.setUserName(rs.getString(2));

ub.setPassword(rs.getString(3));

ub.setMail(rs.getString(4));

ub.setGrade(rs.getInt(5));

//将ub放入到集合中

al.add(ub);

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally{

this.close();

}

return al;

}

public boolean checkUser(String u,String p){

boolean b=false;

try {

ConnDB cd=new ConnDB();

ct=cd.getConnection();

ps=ct.prepareStatement("select top 1 password from users where username=?");

ps.setString(1, u);

rs=ps.executeQuery();

if(rs.next()){

String dbPasswd=rs.getString(1);

if(dbPasswd.equals(p)){

b=true;

}

}

} catch (Exception e) {

// TODO: handle exception

}finally{

this.close();

}

return b;

}

//关闭资源

public void close(){

try {

if(rs!=null){

rs.close();

rs=null;

}

if(ps!=null){

ps.close();

ps=null;

}

if(ct!=null){

ct.close();

ct=null;

}

} catch (Exception e) {

// TODO: handle exception

}

}

}