package com.gzsxt.dao.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.jws.soap.SOAPBinding.Use;
import com.gzsxt.dao.SysUserDao;
import com.gzsxt.pojo.SysUser;
import com.gzsxt.util.BaseUtil;
public class SysUserDaoImpl implements SysUserDao{
//实例化工具类
BaseUtil util=new BaseUtil();
@Override
public SysUser userLogin(String username, String password) {
SysUser user=null;
//编写SQL语句
String sql ="select * from s_user where s_userName=? and s_password=?";
PreparedStatement statement=util.getConnection(sql);
try {
//接收参数
if(username!=null) {
statement.setString(1, username);
}
if(password!=null) {
statement.setString(2, password);
}
//执行sql
ResultSet result=statement.executeQuery();
//判断结果集中是否有数据
while(result.next()) {
user=new SysUser();
user.setS_id(result.getInt("s_id"));//主键
user.setS_username(result.getString("s_userName"));
user.setS_uname(result.getString("s_uname"));
user.setS_email(result.getString("s_email"));
user.setS_phone(result.getLong("s_phone"));
user.setS_password(result.getString("s_password"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return user;
}
@Override
public boolean checkUserName(String username) {
boolean flage=false;
//编写SQL语句
String sql ="select s_id from s_user where s_username=? ";
PreparedStatement statement=util.getConnection(sql);
try {
//接收参数
if(username!=null) {
statement.setString(1, username);
}
//执行sql
ResultSet result=statement.executeQuery();
//判断结果集中是否有数据
if(result.next()) {
flage=true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flage;
}
@Override
public int addUser(String username, String uname, String pwd, String email, Long phone) {
//编写sql语句
String sql ="insert into s_user(s_userName,s_uname,s_password,s_email,s_phone) values(?,?,?,?,?)";
PreparedStatement statement=util.getConnection(sql);
int row=0;
//存入参数
try {
if(username!=null) {
statement.setString(1, username);
}
if(uname!=null) {
statement.setString(2, uname);
}
if(pwd!=null) {
statement.setString(3, pwd);
}
if(email!=null) {
statement.setString(4, email);
}
if(phone!=null) {
statement.setLong(5,phone);
}
//执行添加方法
row=statement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return row;
}
//currentPage当前页面
//pagesize每一页存放数据的数量
@Override
public List<SysUser> getUserList(String name,int currentPage,int pageSize) {
int index=0;
List<SysUser> list=new ArrayList<SysUser>();
//编写SQL语句
String sql ="select * from s_user ";
//判断传递的用户名是否为空
if(name!=null&&name.length()!=0) {
//sql语句拼接
sql+=" where s_userName like ?";
}
if(currentPage>0&&pageSize>0) {
sql+=" limit ?,?";
}
PreparedStatement statement=util.getConnection(sql);
try {
//用户名不为空，sql语句入参
if(name!=null&&name.length()!=0) {
statement.setString(++index, "%"+name+"%");
}
if(currentPage>0&&pageSize>0) {
//当前页码与索引的关系
//pageindex:第一页为0，以后每次的索引为当当前页码减一乘以页数？？？
int pageindex=(currentPage-1)*pageSize;
statement.setInt(++index, pageindex);
statement.setInt(++index, pageSize);
}
//执行sql
ResultSet result=statement.executeQuery();
//判断结果集中是否有数据
while(result.next()) {
SysUser user=new SysUser();
user.setS_id(result.getInt("s_id"));//主键
user.setS_username(result.getString("s_userName"));
user.setS_uname(result.getString("s_uname"));
user.setS_email(result.getString("s_email"));
user.setS_phone(result.getLong("s_phone"));
user.setS_password(result.getString("s_password"));
//把对象存入到集合中
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public int deleteUserById(int id) {
String sql ="delete from s_user where s_id=?";
PreparedStatement statement=util.getConnection(sql);
try {
statement.setInt(1, id);
int number=statement.executeUpdate();//增删改都是用此方法
return number;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
@Override
public SysUser getUserById(int id) {
SysUser user=null;
//编写SQL语句
String sql ="select * from s_user ";
try {
if(id>0) {
sql+="where s_id=?";
}
//执行sql
PreparedStatement statement=util.getConnection(sql);
//接收参数
if(id>0) {
statement.setInt(1, id);
}
//执行sql
ResultSet result=statement.executeQuery();
//判断结果集中是否有数据
if(result.next()) {
user=new SysUser();
user.setS_id(result.getInt("s_id"));//主键
user.setS_username(result.getString("s_userName"));
user.setS_uname(result.getString("s_uname"));
user.setS_email(result.getString("s_email"));
user.setS_phone(result.getLong("s_phone"));
user.setS_password(result.getString("s_password"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
@Override
public int updatUser(int id, String username, String uname, String pwd, String email, Long phone) {
//编写sql语句
String sql="update s_user set s_userName=?,s_uname=?,s_password=?,s_email=?,s_phone=? where s_id=?";
PreparedStatement statement=util.getConnection(sql);
int row=0;
//存入参数
try {
if(username!=null) {
statement.setString(1, username);
}
if(uname!=null) {
statement.setString(2, uname);
}
if(pwd!=null) {
statement.setString(3, pwd);
}
if(email!=null) {
statement.setString(4, email);
}
if(phone!=null) {
statement.setLong(5,phone);
}
if(id>0) {
statement.setInt(6, id);
}
row=statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
@Override
public int getTotalCount() {
String sql ="select count(*) from s_user";
PreparedStatement statement=util.getConnection(sql);
try {
ResultSet resultSet=statement.executeQuery();
if(resultSet.next()) {
int totalCount=resultSet.getInt("count(*)");
return totalCount;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
}
