公告发布系统

package com.lx.data; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.lx.entity.User; import com.lx.data.DBHelper; import com.mysql.jdbc.Statement; import com.lx.entity.User; public class UserDao { /* * 测试用户名是否存在 */ public int isUser(User user){ String sql = "select count(*) from User where username=?"; Connection con= DBHelper.getConnection(); //连接数据库 PreparedStatement pstmt=null; //PreparedStatement表示预编译的SQL语句的对象，SQL语句被预编译并存储在PreparedStatement对象中 ResultSet rs=null; //存储数据库查询结果 int result = 0; try{ pstmt = con.prepareStatement(sql); pstmt.setString(1, user.getUsername()); rs = pstmt.executeQuery(); //executeQuery（）在该PreparedStatement对象中执行SQL查询，并返回该查询生成的ResultSet对象 if(rs.next()) result =rs.getInt(1); }catch(SQLException e){ e.printStackTrace(); }finally{ DBHelper.close(rs, pstmt, con); } System.out.println(user.getUsername()); System.out.println("根据返回的用户名是否为空判定查询的用户是否存在"+result); return result; } /* * 添加用户 */ public boolean add(User user){ if(isUser(user)==0){ String sql="insert into user values(?,?,?,?,?,?)"; String[] params = {user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress(),user.getGarde()}; int count = DBHelper.excuteUpdate(sql, params); System.out.println(user.getUsername()+user.getPassword()); System.out.println("add"); return count>0; } System.out.println("***********"); return false; } /* * 管理员登录 */ public boolean login(User user){ Connection con=DBHelper.getConnection(); PreparedStatement pstmt = null; ResultSet rs=null; int result = 0; String sql = "select count(*) from user where username=? and password=? and garde='admin'"; try{ pstmt = con.prepareStatement(sql); pstmt.setString(1, user.getUsername()); pstmt.setString(2, user.getPassword()); rs=pstmt.executeQuery(); rs.next(); result = rs.getInt(1); }catch(SQLException e){ e.printStackTrace(); }finally{ DBHelper.close(rs, pstmt, con); } System.out.println(result); System.out.println("sql"); System.out.println(user.getUsername()); System.out.println(user.getPassword()); return result>0; } /* * 用户登录 */ public boolean userlogin(User user){ Connection con=DBHelper.getConnection(); PreparedStatement pstmt = null; ResultSet rs=null; int result = 0; String sql = "select count(*) from user where username=? and password=? and garde='user'"; try{ pstmt = con.prepareStatement(sql); pstmt.setString(1, user.getUsername()); pstmt.setString(2, user.getPassword()); rs=pstmt.executeQuery(); rs.next(); result = rs.getInt(1); }catch(SQLException e){ e.printStackTrace(); }finally{ DBHelper.close(rs, pstmt, con); } return result>0; } /* * 此方法返回2个值，一个是用户名，一个是用户密码；主要用于从数据库中获取要显示在userlist.jsp页面用户的信息 * uList 用户集合 * uPwd 用户密码集合 */ public List<User> getUser(String username,String location){ List<User> userList=new ArrayList<User>(); Connection con= DBHelper.getConnection(); PreparedStatement pstmt=null; ResultSet rs=null; String sql="select username,garde from user"; try{ pstmt=con.prepareStatement(sql); pstmt.setString(1, username); rs=pstmt.executeQuery(); while(rs.next()){ User u=new User(); u.setUsername(rs.getString("username")); u.setPassword(rs.getString("garde")); userList.add(u); } }catch(SQLException e){ e.printStackTrace(); }finally{ DBHelper.close(rs, pstmt, con); } return userList; } /* * 删除用户 */ public boolean deleteUser(User user) { PreparedStatement pstmt = null; Connection con= DBHelper.getConnection(); if(isUser(user)!=0){ try { pstmt = con.prepareStatement( "delete user from user where username=?"); pstmt.setString(1,user.getUsername()); System.out.println(user.getUsername()); int flag = pstmt.executeUpdate(); if (flag == 0) { return false; } } catch (SQLException ex) { ex.printStackTrace(); return false; } System.out.println("deleteuser"); return true; } return false; } /** * 查看用户列表 */ public List<User> UserList(){ List<User> list = new ArrayList<User>(); Connection con = DBHelper.getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; String sql="select username,garde from user"; try{ pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ User u = new User(); u.setUsername(rs.getString("username")); u.setGarde(rs.getString("garde")); list.add(u);} } catch(SQLException e){ e.printStackTrace(); }finally{ DBHelper.close(rs, pstmt, con); } return list; } /** * 编辑更新用户信息 */ public boolean editUser(User user){ Connection con = DBHelper.getConnection(); PreparedStatement pstmt = null; String sql = "update user set password=?,sex=?,email=?,address=?,garde=? where username=?"; try{ pstmt = con.prepareStatement(sql); pstmt.setString(1, user.getPassword()); pstmt.setString(2, user.getSex()); pstmt.setString(3, user.getEmail()); pstmt.setString(4, user.getAddress()); pstmt.setString(5, user.getGarde()); pstmt.setString(6, user.getUsername()); String[] params = {user.getPassword(),user.getSex(),user.getEmail(),user.getAddress(),user.getGarde(),user.getUsername()}; int count = DBHelper.excuteUpdate(sql, params); //pstmt.executeUpdate(); return true; } catch(SQLException e){ e.printStackTrace(); } return false; } }