JavaWeb初级新闻网站项目

package dao; import model.Pager; import model.SystemContext; import model.UserException; import model.User; import util.DButil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * Created by Mr.Sad on 2017/8/27. */ public class UserDao implements IUserDao{ @Override public void add(User user) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; con = DButil.getConnection(); String sql = "select count(*) from news_user where username=?"; try { ps = con.prepareStatement(sql); ps.setString(1,user.getUsername()); rs = ps.executeQuery(); while(rs.next()){ if(rs.getInt(1)>0) throw new UserException("用户以存在"); } sql = "INSERT INTO news_user (username,password,nickname,type,status) VALUES (?,?,?,?,?)"; ps = con.prepareStatement(sql); ps.setString(1, user.getUsername()); ps.setString(2, user.getPassword()); System.out.println("UserDao:"+user.getNickname()); ps.setString(3, user.getNickname()); ps.setInt(4,user.getType()); ps.setInt(5,user.getStatus()); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { DButil.close(rs); DButil.close(ps); DButil.close(con); } } @Override public void delete(int id) { Connection con = null; PreparedStatement ps = null; try { con = DButil.getConnection(); User u = this.load(id); if(u.getId()==1)throw new UserException("I am SuperAdmin"); String sql = "delete from news_user where id=?"; ps = con.prepareStatement(sql); ps.setInt(1,id); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { DButil.close(ps); DButil.close(con); } } @Override public void update(User user) { Connection con = null; PreparedStatement ps = null; try { con = DButil.getConnection(); String sql = "update news_user set password=?,nickname=?,type=?,status=? where id=?"; ps = con.prepareStatement(sql); ps.setString(1,user.getPassword()); ps.setString(2,user.getNickname()); ps.setInt(3,user.getType()); ps.setInt(4,user.getStatus()); ps.setInt(5,user.getId()); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { DButil.close(ps); DButil.close(con); } } @Override public User load(int id) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; User u = null; try { con = DButil.getConnection(); String sql = "select * from news_user where id=?"; ps = con.prepareStatement(sql); ps.setInt(1,id); rs = ps.executeQuery(); while(rs.next()){ u = new User(); u.setId(rs.getInt("id")); u.setUsername(rs.getString("username")); u.setPassword(rs.getString("password")); u.setNickname(rs.getString("nickname")); u.setStatus(rs.getInt("status")); u.setType(rs.getInt("type")); } } catch (SQLException e) { e.printStackTrace(); }finally { DButil.close(rs); DButil.close(ps); DButil.close(con); } return u; } @Override public Pager<User> list(String condition) { int pageOffset = SystemContext.getPageOffset(); int pageSize = SystemContext.getPageSize(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; Pager<User> pages = new Pager<User>(); List<User> users = new ArrayList<User>(); User u = null; try { if (pageOffset <= 0) pageOffset = 1; int start = (pageOffset - 1) * pageSize; con = DButil.getConnection(); String sql = "select * from news_user"; String sqlcount = "select count(*) from news_user"; if (condition != null || "".equals(condition)) { sql += " where username like '%" + condition + "%' or nickname like '%" + condition + "%'"; sqlcount += " where username like '%" + condition + "%' or nickname like '%" + condition + "%'"; } sql += " limit ?,?"; ps = con.prepareStatement(sql); ps.setInt(1, pageOffset); ps.setInt(2, pageSize); // if (condition == null || "".equals(condition)) { // sql+=" limit ?,?"; // ps = con.prepareStatement(sql); // ps.setInt(1,start); // ps.setInt(2,pageSize); // } else { // sql += " where username like ? or nickname like ?"; // ps = con.prepareStatement(sql); // ps.setString(1, "%" + condition + "%"); // ps.setString(2, "%" + condition + "%"); // // } rs = ps.executeQuery(); while (rs.next()) { u = new User(); u.setId(rs.getInt("id")); u.setUsername(rs.getString("username")); u.setPassword(rs.getString("password")); u.setNickname(rs.getString("nickname")); u.setStatus(rs.getInt("status")); u.setType(rs.getInt("type")); users.add(u); } ps = con.prepareStatement(sqlcount); rs = ps.executeQuery(); int totalRecord = 0; while (rs.next()) { totalRecord = rs.getInt(1); } int totalPage = (totalRecord - 1) / pageSize + 1; pages.setPageOffset(pageOffset); pages.setPageSize(pageSize); pages.setTotalPage(totalPage); pages.setTotalRecord(totalRecord); } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(rs); DButil.close(ps); DButil.close(con); } pages.setDatas(users); return pages; } @Override public User login(String username, String password) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; User u = null; try { con = DButil.getConnection(); String sql = "select * from news_user where username=?"; ps = con.prepareStatement(sql); ps.setString(1,username); rs = ps.executeQuery(); while(rs.next()){ u = new User(); u.setId(rs.getInt("id")); u.setNickname(rs.getString("nickname")); u.setUsername(rs.getString("username")); u.setPassword(rs.getString("password")); u.setStatus(rs.getInt("status")); u.setType(rs.getInt("type")); } //显示的信息的方法封装在一个类里 if (u == null) throw new UserException("用户不存在"); if (!u.getPassword().equals(password)) throw new UserException("账号或密码错误"); if (u.getStatus()==1) throw new UserException("该用户已被停用"); } catch (SQLException e) { e.printStackTrace(); }finally { DButil.close(rs); DButil.close(ps); DButil.close(con);