package com.sp.model;
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 update(String s1,String s2,String s3,String s4,String s5,String s6,String s7,String s8,String s9){
boolean b=false;
// private longuserId ;
// private String username ;
// private String truename ;
// private String passwd ;
// private String email ;
// private String phone ;
// private String address ;
// private String postcode ;
// private int grade ;
//
try{
ConnDB cd=new ConnDB();
ct=cd.getCon();
ps=ct.prepareStatement("update users set username='"+s2+"' , passwd='"+s3+"', email='"+s4+"', grade="+s5+"', truename="+s6+"', phone="+s7+"', address="+s8+"', postcode="+s9+" where userId='"+s1+"'");
int num= ps.executeUpdate();
if(num==1){
b=true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
this.close();
}
return b;
}
//增加用户信息,
//增加用户放在注册用户上
// public boolean addUser(String s2,String s3,String s4,String s5){
// boolean b=false;
// try{
// ConnDB cd=new ConnDB();
// ct=cd.getCon();
// ps=ct.prepareStatement("insert into users values(?,?,?,?)");
//
// ps.setString(1, s2);
// ps.setString(2, s3);
// ps.setString(3, s4);
// ps.setString(4, s5);
// // ps.setString(5, s5);
// int num= ps.executeUpdate();
// if(num==1){
// b=true;
//
// }
//
// }catch(Exception e){
// e.printStackTrace();
// }finally{
//
// this.close();
// }
//
// return b;
//
// }
//
//查询用户
public ArrayList getResultUser(String userId,String username){
ArrayList al=new ArrayList();
try {
ConnDB cd=new ConnDB();
ct=cd.getCon();
if(!userId.equals("")&&username.equals("")){
ps=ct.prepareStatement("select * from users where userId='"+userId+"'");
System.out.println("ID号查询");
}
if(userId.equals("")&&!username.equals("")){
ps=ct.prepareStatement("select * from users where username='"+username+"'");
System.out.println("通过用户名查询");
}
if(!userId.equals("")&&!username.equals("")){
System.out.println("userId="+userId);
ps=ct.prepareStatement("select * from users where userId='"+userId+"' and username='"+username+"'");
System.out.println("通过用户名和ID号查询");
}
rs=ps.executeQuery();
while(rs.next()){
//将rs中的 每条记录封装到UserBean ub
UserBean ub=new UserBean();
ub.setUserId(rs.getInt(1));
ub.setUsername(rs.getString(2));
ub.setPasswd(rs.getString(3));
ub.setEmail(rs.getString(4));
ub.setGrade(rs.getInt(5));
//将ub ,放入到ArrayList 中
al.add(ub);
}
}
catch (Exception ex) {
ex.printStackTrace();
}finally{
this.close();
}
return al;
}
//删除用户
public boolean deluser(String userid){
boolean b=false;
try{
ConnDB cd=new ConnDB();
ct=cd.getCon();
ps=ct.prepareStatement("delete users where userId="+userid+"");
int num= ps.executeUpdate();
if(num==1){
b=true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
this.close();
}
return b;
}
public ArrayList getResultByPage(int pageNow,int pageSize){
ArrayList al=new ArrayList();
try {
int rowCount=0;//共有几条记录(查表)
//得到rowCount
ConnDB cd=new ConnDB();
ct=cd.getCon();
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.setPasswd(rs.getString(3));
ub.setEmail(rs.getString(4));
ub.setGrade(rs.getInt(5));
//将ub ,放入到ArrayList 中
al.add(ub);
System.out.println("ub="+ub);
}
}
catch (Exception ex) {
ex.printStackTrace();
}finally{
this.close();
}
return al;
}
public int getPageCount(){
return this.pageCount;
}
//验证用户
public boolean checkUser(String u,String p){
boolean b=false;
try{
ConnDB cd=new ConnDB();
ct=cd.getCon();
ps=ct.prepareStatement("select top 1 passwd from users where username=?");
ps.setString(1,u);
rs=ps.executeQuery();
if(rs.next()){
String dbpass=rs.getString(1);
if(p.equals(dbpass)){
b=true;
}
}
}catch(Exception e){
e.printStackTrace();
}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 ex) {
ex.printStackTrace();
}
}
}