domain
User.java
package com.jsly.drp.sysmgr.domain;
import java.util.Date;
/**
* 用户实体类
* @author Administrator
*
*/
public class User {
//用户代码
private String userId;
//用户姓名
private String userName;
//密码
private String password;
//联系电话
private String contactTel;
//电子邮件
private String email;
//创建日期
private Date createDate;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getContactTel() {
return contactTel == null?"":contactTel;
}
public void setContactTel(String contactTel) {
this.contactTel = contactTel;
}
public String getEmail() {
return email == null?"":email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
}
PageModel.java
package com.jsly.drp.util;
import java.util.List;
/**
* 封装分页信息
* @author Administrator
*
*/
public class PageModel<E> {
//结果集
private List<E> list;
//查询记录数
private int totalRecord;
//每页多少条数据
private int pageSize;
//当前第几页,页码
private int pageNo;
/**
* 总页数totalPage
* @return
*/
public int getTotalPage(){
if(totalRecord % pageSize == 0){
return totalRecord / pageSize;
}else{
return totalRecord / pageSize + 1;
}
//或者
//return (totalRecord + pageSize - 1) / pageSize;
}
/**
* 得到首页
* @return
*/
public int getTopPage(){
return 1;
}
/**
* 上一页
* @return
*/
public int getPreviousPage(){
if (pageNo <= 1){
return 1;
}
return pageNo - 1;
}
/**
* 下一页
* @return
*/
public int getNextPage(){
if (pageNo >= getTotalPage()){
return getTotalPage();
}
return pageNo + 1;
}
/**
* 得到尾页
* @return
*/
public int getBottomPage(){
return getTotalPage();
}
public List<E> getList() {
return list;
}
public void setList(List<E> list) {
this.list = list;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
}
manager
UserManager.java
package com.jsly.drp.sysmgr.manager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import com.jsly.drp.sysmgr.domain.User;
import com.jsly.drp.util.DbUtil;
import com.jsly.drp.util.PageModel;
/**
* 采用单例管理用户
* @author Administrator
*
*/
public class UserManager {
private static UserManager instance = new UserManager();//私有静态成员变量
private UserManager(){ //私有构造方法
}
public static UserManager getInstance(){ //公共静态入口点方法
return instance;
}
/**
* 添加用户
* @param user
*/
public void addUser(User user){
String sql = "insert into t_user (user_id, user_name, password, contact_tel, email, create_date) "+
" values (?, ?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUserId());
pstmt.setString(2, user.getUserName());
pstmt.setString(3, user.getPassword());
pstmt.setString(4, user.getContactTel());
pstmt.setString(5, user.getEmail());
//pstmt.setTimestamp(6, new Timestamp(new java.util.Date().getTime()));
//setDate保存年月日,setTime保存时分秒,setTimestamp保存年月日时分秒
pstmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
/**
* 根据用户代码进行查询
* @param user
* @return
* 如果存在则返回user对象 不存在则返回null
*/
public User findUserById(String userId){
String sql = "select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id=?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
User user = null;
try{
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userId);
rs = pstmt.executeQuery();
if(rs.next()){
user =new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
}
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtil.close(rs);
DbUtil.close(pstmt);
DbUtil.close(conn);
}
return user;
}
/**
* 分页查询
* @param pageNo 第几页
* @param pageSize 每页多少条记录数
* @return pageModel
*/
public PageModel<User> fideUserList(int pageNo,int pageSize){
StringBuffer sbsql = new StringBuffer();
sbsql.append("select user_id, user_name, password, contact_tel, email, create_date from ")
.append("( ")
.append("select rownum rn,user_id, user_name, password, contact_tel, email, create_date from ")
.append("( ")
.append("select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order by user_id ")
.append(")where rownum <= ? ")
.append(")where rn>= ? ");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
PageModel<User> pageModel = null;
int end = pageNo * pageSize;
int begin = end - pageSize + 1;
try{
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sbsql.toString());
pstmt.setInt(1, end);
pstmt.setInt(2, begin);
rs = pstmt.executeQuery();
List<User> userList = new ArrayList<User>();
while(rs.next()){
User user =new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
userList.add(user);
}
pageModel = new PageModel<User>();
pageModel.setList(userList);
pageModel.setTotalRecord(getTotalRecords(conn));
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtil.close(rs);
DbUtil.close(pstmt);
DbUtil.close(conn);
}
return pageModel;
}
/**
*
* @param conn
* @return 总记录条数TotalRecords
* @throws SQLException
*/
private int getTotalRecords(Connection conn) throws SQLException{
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select count(*) from t_user where user_id<>'root'";
int count = 0;
try{
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt(1);
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtil.close(rs);
DbUtil.close(pstmt);
}
return count;
}
/**
* 修改用户
* @param user
*/
public void modifyUser(User user){
StringBuffer sbSql = new StringBuffer();
sbSql.append("update t_user ")
.append("set user_name = ?, ")
.append("password = ?, ")
.append("contact_tel = ?, ")
.append("email = ? ")
.append("where user_id = ? ");
Connection conn = null;
PreparedStatement pstmt = null;
try{
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sbSql.toString());
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getContactTel());
pstmt.setString(4, user.getEmail());
pstmt.setString(5, user.getUserId());
pstmt.executeUpdate();
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
/**
* 删除用户
* @param userId
*/
public void delUser(String userId){
String sql = "delete from t_user where user_id = ?";
Connection conn = null;
PreparedStatement pstmt = null;
try{
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userId);
pstmt.executeUpdate();
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
// /**
// * 批量删除用户
// * 用一条语句完成,只提交一次
// * 使用Statemen拼串
// * 语句delete from t_user where user_id in ('a','b','c')
// * @param userId
// */
// public void delUser(String[] userIds){
//
// StringBuffer sbSql = new StringBuffer();
// for(int i=0;i<userIds.length;i++){
// sbSql.append("'")
// .append(userIds[i])
// .append("'")
// .append(",");
// }
// String sql = "delete from t_user where user_id in ("+sbSql.substring(0, sbSql.length()-1)+")";
// System.out.println("拼串批量删除用户Sql语句:"+sql);
// Connection conn = null;
// Statement stmt = null;
// try{
// conn = DbUtil.getConnection();
// stmt = conn.createStatement();
// stmt.executeUpdate(sql);
// }catch(SQLException e){
// e.printStackTrace();
// }finally{
// DbUtil.close(stmt);
// DbUtil.close(conn);
// }
// }
/**
* 批量删除用户
* 用一条语句完成,只提交一次
* 使用PreparedStatement占位符
* 语句delete from t_user where user_id in (?,?,?)
* @param userId
*/
public void delUser(String[] userIds){
StringBuffer sbSql = new StringBuffer();
for(int i=0;i<userIds.length;i++){
sbSql.append("?,");
}
String sql = "delete from t_user where user_id in ("+sbSql.substring(0, sbSql.length()-1)+")";
System.out.println("占位符批量删除用户Sql语句:"+sql);
Connection conn = null;
PreparedStatement pstmt = null;
try{
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
for(int i=0;i<userIds.length;i++){
pstmt.setString(i+1, userIds[i]);
}
pstmt.executeUpdate();
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
/**
* 用户登录
* @param userId
* @param password
* @return
*/
public User login(String userId,String password){
User user = this.findUserById(userId);
if(user == null){
throw new UserNotFoundException("用户代码不正确!");
}
if(!password.equals(user.getPassword())){
throw new PasswordNotCorrenException("密码不正确!");
}
return user;
}
}