八、查询用户业务
8.1 创建查询用户持久层
创建查询用户的抽象方法和实现类。
package com.zj.dao; import com.zj.pojo.User; import java.util.List; public interface UserManageDao { //用户添加 void insertUser(User user); //查询用户 List<User> selectUserByProperty(User user); }
package com.zj.dao.impl; import com.zj.commons.jdbcUtils; import com.zj.dao.UserManageDao; import com.zj.pojo.User; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class UserManageDaoImpl implements UserManageDao { @Override public void insertUser(User user) { Connection con = null; try { con = jdbcUtils.getConnection(); //关闭自动提交事务，加深对事务印象 con.setAutoCommit(false); PreparedStatement ps = con.prepareStatement("INSERT INTO users values (default ,?,?,?,?,?)"); ps.setString(1, user.getUsername()); ps.setString(2, user.getUserpwd()); ps.setString(3, user.getUsersex()); ps.setString(4, user.getPhonenumber()); ps.setString(5, user.getQqnumber()); ps.executeUpdate(); con.commit(); }catch (Exception e) { e.printStackTrace(); //出现异常回滚 jdbcUtils.rollbackConnection(con); }finally { jdbcUtils.closeConnection(con); } } @Override public List<User> selectUserByProperty(User user) { Connection con = null; List<User> users = new ArrayList<User>(); try { con = jdbcUtils.getConnection(); String sql = this.createSQL(user); PreparedStatement ps = con.prepareStatement(sql); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()) { User u = new User(); u.setUserid(resultSet.getInt("userid")); u.setUsername(resultSet.getString("username")); u.setPhonenumber(resultSet.getString("phonenumber")); u.setQqnumber(resultSet.getString("qqnumber")); u.setUserpwd(resultSet.getString("userpwd")); u.setUsersex(resultSet.getString("usersex")); users.add(u); } }catch (Exception e) { e.printStackTrace(); } return users; } //sql拼接 private String createSQL(User user){ //没有给定查询参数时，查询全部数据 StringBuffer stringBuffer = new StringBuffer("select * from users where 1=1"); if (user.getUsersex() !=null && user.getUsersex().length() > 0){//从表单获取的数据是空串，也是有长度的。所以还要判断当前的字段的长度。 //拼接到sql stringBuffer.append(" and usersex="+user.getUsersex()); } if (user.getQqnumber() !=null && user.getQqnumber().length()>0){ stringBuffer.append(" and qqnumber="+user.getQqnumber()); } if (user.getUsername() !=null && user.getUsername().length()>0){ stringBuffer.append(" and username="+user.getUsername()); } if (user.getPhonenumber() !=null && user.getPhonenumber().length()>0){ stringBuffer.append(" and phonenumber="+user.getPhonenumber()); } return stringBuffer.toString(); } }
8.2 创建查询用户的业务层
package com.zj.service; import com.zj.pojo.User; import java.util.List; public interface UserManageService { void addUser(User user); List<User> findUsers(User user); }
package com.zj.service.Impl; import com.zj.dao.UserManageDao; import com.zj.dao.impl.UserManageDaoImpl; import com.zj.pojo.User; import com.zj.service.UserManageService; import java.util.List; public class UserManageServiceImpl implements UserManageService { /*添加用户*/ @Override public void addUser(User user) { UserManageDao userManageDao = new UserManageDaoImpl(); userManageDao.insertUser(user); } /*查询用户*/ @Override public List<User> findUsers(User user) { UserManageDao userManageDao = new UserManageDaoImpl(); List<User> users = userManageDao.selectUserByProperty(user); return users; } }
8.3 创建查询用户的servlet
/*处理查询用户请求*/ private void findUsers(HttpServletRequest request,HttpServletResponse response) throws IOException { //获取用户请求的数据 User user = this.createUser(request, response); try { UserManageService userManageService = new UserManageServiceImpl(); List<User> users = userManageService.findUsers(user); request.setAttribute("list",users); request.getRequestDispatcher("usermanage/showUsers.jsp").forward(request, response); }catch (Exception e){ e.printStackTrace(); response.sendRedirect("error.jsp"); } }
8.4 创建查询用户与显示结果的页面
查询用户页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>无标题文档</title> <link href="../css/style.css" rel="stylesheet" type="text/css" /> <link href="../css/select.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="place"> <span>位置：</span> <ul class="placeul"> <li><a href="#">用户管理</a></li> <li><a href="#">查询用户</a></li> </ul> </div> <div class="rightinfo"> <form method="post" action="../userManage.do"> <input type="hidden" name="flag" value="find"/> <ul class="prosearch"> <li> <label>查询：</label> <i>用户名</i> <a> <input name="username" type="text" class="scinput" /> </a> </li> <li> <label>性别：</label> <input name="usersex" type="radio" value="1" checked="checked" /> 男 <input name="usersex" type="radio" value="0" /> 女 </li> <li> <label>手机号：</label> <a> <input name="phonenumber" type="text" class="scinput" /> </a> </li> <li> <label>QQ号：</label> <a> <input name="qqnumber" type="text" class="scinput" /> </a> </li> <a> <input type="submit" class="sure" value="查询"/> </a> </ul> </form> </div> </body> </html>
显示结果页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>显示用户数据</title> <%--当前页面是服务端跳转的页面不需要在样式文件加../--%> <link href="css/style.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="place"> <span>位置：</span> <ul class="placeul"> <li><a href="#">用户管理</a></li> <li><a href="#">查询用户</a></li> <li><a href="#">查询结果</a></li> </ul> </div> <div class="rightinfo"> <div class="formtitle1"><span>用户列表</span></div> <table class="tablelist" > <thead> <tr> <th>序号</th> <th>用户名</th> <th>用户性别</th> <th>手机号</th> <th>QQ号</th> <th>操作</th> </tr> </thead> <tbody> <c:forEach var="user" items="${requestScope.list}" varStatus="status"> <tr> <td>${status.count}</td> <td>${user.username}</td> <td> <c:choose> <c:when test="${user.usersex == 1}">男</c:when> <c:otherwise>女</c:otherwise> </c:choose> </td> <td>${user.phonenumber}</td> <td>${user.qqnumber}</td> <td><a href="userUpdate.html" class="tablelink">修改</a> <a href="#" class="tablelink click"> 删除</a></td> </tr> </c:forEach> </tbody> </table> <div class="tip"> <div class="tiptop"><span>提示信息</span><a></a></div> <div class="tipinfo"> <span><img src="images/ticon.png" /></span> <div class="tipright"> <p>是否确认对信息的修改 ？</p> <cite>如果是请点击确定按钮 ，否则请点取消。</cite> </div> </div> <div class="tipbtn"> <input name="" type="button" class="sure" value="确定" /> <input name="" type="button" class="cancel" value="取消" /> </div> </div> </div> <script type="text/javascript"> $('.tablelist tbody tr:odd').addClass('odd'); </script> </body> </html>
九、更新用户业务
9.1 创建预更新用户查询的持久层接口和实现类
package com.zj.dao; import com.zj.pojo.User; import java.util.List; public interface UserManageDao { //用户添加 void insertUser(User user); //查询用户 List<User> selectUserByProperty(User user); //更新用户 User selectUserById(int userid); }
//根据id查询用户数据 @Override public User selectUserById(int userid) { Connection con = null; User u = null; try { con = jdbcUtils.getConnection(); PreparedStatement ps = con.prepareStatement("select * from users where userid = ?"); ps.setInt(1, userid); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()) { u = new User(); u.setUserid(resultSet.getInt("userid")); u.setUsername(resultSet.getString("username")); u.setPhonenumber(resultSet.getString("phonenumber")); u.setQqnumber(resultSet.getString("qqnumber")); u.setUserpwd(resultSet.getString("userpwd")); u.setUsersex(resultSet.getString("usersex")); } }catch (Exception e){ e.printStackTrace(); }finally { jdbcUtils.closeConnection(con); } return u; }
9.2 创建预更新用户的业务层
package com.zj.service; import com.zj.pojo.User; import java.util.List; public interface UserManageService { void addUser(User user); List<User> findUsers(User user); User findUserById(int id); }
/*根据用户id查询用户，预更新用户*/ @Override public User findUserById(int id) { UserManageDao userManageDao = new UserManageDaoImpl(); User user = userManageDao.selectUserById(id); return user; }
9.3 创建更新预查询的servlet
//用户预更新查询 public void preUpdate(HttpServletRequest request, HttpServletResponse response) throws IOException { String userid = request.getParameter("userid"); try { UserManageService userService = new UserManageServiceImpl(); User user = userService.findUserById(Integer.parseInt(userid)); request.setAttribute("user", user); request.getRequestDispatcher("usermanage/update.jsp").forward(request, response); }catch (Exception e){ e.printStackTrace(); response.sendRedirect("error.jsp"); } }
9.4 创建更新用户页面
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>无标题文档</title> <link href="css/style.css" rel="stylesheet" type="text/css" /> <script type="text/javascript"> function update(){ window.location='ok.html'; } </script> </head> <body> <div class="place"> <span>位置：</span> <ul class="placeul"> <li><a href="#">用户管理</a></li> <li><a href="#">修改用户</a></li> </ul> </div> <div class="formbody"> <div class="formtitle"><span>基本信息</span></div> <form method="post" action="../userManage.do"> <ul class="forminfo"> <li><label>用户名</label><input name="username" type="text" class="dfinput" value="${requestScope.user.username}"/> </li> <li> <label>性别</label> <c:choose> <c:when test="${requestScope.user.usersex == 1}"> <input name="usersex" type="radio" value="1" checked="checked" />男 <input name="usersex" type="radio" value="0" />女 </c:when> <c:otherwise> <input name="usersex" type="radio" value="1" />男 <input name="usersex" type="radio" value="0" checked="checked"/>女 </c:otherwise> </c:choose> </li> <li><label>手机号</label><input name="phonenumber" type="text" class="dfinput" value="${requestScope.user.phonenumber}"/></li> <li><label>QQ号</label><input name="qqnumber" type="text" class="dfinput" value="${requestScope.user.qqnumber}"/></li> <li><label> </label><input type="submit" class="btn" value="确认修改"/></li> </ul> </form> </div> </body> </html>
9.5 创建更新用户持久层
package com.zj.dao; import com.zj.pojo.User; import java.util.List; public interface UserManageDao { //用户添加 void insertUser(User user); //查询用户 List<User> selectUserByProperty(User user); //预更新用户 User selectUserById(int userid); //更新用户 void updateUser(User user); }
@Override public void updateUser(User user) { Connection con = null; try { con = jdbcUtils.getConnection(); con.setAutoCommit(false); PreparedStatement ps = con.prepareStatement("update users set userpwd=?, username=?,usersex=?,phonenumber=?,qqnumber=?"); ps.setString(1, user.getUserpwd()); ps.setString(2,user.getUsername()); ps.setString(3,user.getUsersex()); ps.setString(4, user.getPhonenumber()); ps.setString(5, user.getQqnumber()); int i = ps.executeUpdate(); con.commit(); }catch (Exception e){ e.printStackTrace(); jdbcUtils.rollbackConnection(con); }finally { jdbcUtils.closeConnection(con); } }
