1. 准备jar包
- commons-beanutils.jar
- commons-dbutils-1.4.jar
- commons-logging-1.0.4.jar
- druid-1.1.10.jar
- jstl-1.2.jar
- mysql-connector-java-5.1.7-bin.jar
2.写Druid的数据库连接池工具类
2.1 设计工具类
package cn.javabs.school.util; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; /** * 工具类全都使用static */ public class DruidUtils { private static DataSource dataSource = null; static String myFile ="druid.properties"; static { try { InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream(myFile); Properties p = new Properties(); p.load(resourceAsStream); // DruidDataSourceFactory核心类 德鲁意数据资源工厂 的 创建数据资源 dataSource = DruidDataSourceFactory.createDataSource(p); } catch (Exception e) { throw new RuntimeException(e); } } public static DataSource getDataSource(){ return dataSource; } public static Connection getConnection(){ try { return dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } }
2.2 设计配置文件
新建一个file 、命名为 druid.properties
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/0314 username=root password=sorry
3.写sql代码
CREATE DATABASE `usermanager` ; USE `usermanager` ; DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `sex` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `birthday` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_username_uindex` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8 COMMENT='用户表'; INSERT INTO `user` VALUES ('1001', 'admin', '男', '123', '1999-09-09');
4.写测试类 测试查询是否可以查到。用于测试数据库连接是否有误!
package cn.javabs.school.test; import cn.javabs.school.entity.User; import cn.javabs.school.util.DruidUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.junit.Test; import java.sql.SQLException; import java.util.List; public class TestData { QueryRunner qr = new QueryRunner(DruidUtils.getDataSource()); @Test public void fun() throws SQLException { User user = new User(); List<User> users = qr.query("select * from user", new BeanListHandler<User>(User.class)); System.out.println(users); } }
5.设计[业务逻辑层]代码
5.1 设计UserService接口
package cn.javabs.school.service; import cn.javabs.school.entity.User; import java.util.List; /** * 设计UserService接口 */ public interface UserService { /** * 用户登录 * @param username * @param password * @return */ User UserLogin(String username , String password); /** * 用户添加 * @param user * @return */ int addUser(User user); /** * 删除用户 * @param id * @return */ int delUser(int id); /** * 修改用户 * @param user * @return */ int updateUser(User user); /** * 查询全部用户 * @return */ List<User> findAllUser(); /** * 根据id查询用户 * @param id * @return */ User findUserById(int id); /** * 根据用户名名称查询用户 * @param username * @return */ User findUserByName(String username); }
5.2 设计UserServiceImpl实现类
package cn.javabs.school.service.impl; import cn.javabs.school.dao.UserDao; import cn.javabs.school.dao.impl.UserDaoImpl; import cn.javabs.school.entity.User; import cn.javabs.school.service.UserService; import java.util.List; /** * 设计业务逻辑层接口的实现类 * @author Mryang */ public class UserServiceImpl implements UserService { UserDao userDao = new UserDaoImpl(); @Override public User UserLogin(String username, String password) { return userDao.login(username,password); } @Override public int addUser(User user) { return userDao.addUser(user); } @Override public int delUser(int id) { return userDao.delUser(id); } @Override public int updateUser(User user) { return userDao.updateUser(user); } @Override public List<User> findAllUser() { return userDao.getAllUsers(); } @Override public User findUserById(int id) { return userDao.getUserById(id); } @Override public User findUserByName(String username) { return userDao.getUserByName(username); } }
6.设计[数据访问层]代码
6.1 设计UserDao接口
package cn.javabs.school.dao; import cn.javabs.school.entity.User; import java.util.List; public interface UserDao { int addUser(User user); int delUser(int id); int updateUser(User user); List<User> getAllUsers(); User getUserById(int id); User getUserByName(String username); User login(String username, String password); }
6.2 设计UserDaoImpl接口的实现类
package cn.javabs.school.dao.impl; import cn.javabs.school.dao.UserDao; import cn.javabs.school.entity.User; import cn.javabs.school.util.DruidUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import java.sql.SQLException; import java.util.List; public class UserDaoImpl implements UserDao { QueryRunner qr = new QueryRunner(DruidUtils.getDataSource()); /** * 添加用户 * @param user * @return */ @Override public int addUser(User user) { try { return qr.update("insert into user(id,username,password,sex,birthday) values (?,?,?,?,?)", user.getId(),user.getUsername(),user.getPassword(),user.getSex(),user.getBirthday()); } catch (SQLException e) { throw new RuntimeException(e); } } /** * 删除用户 * @param id * @return */ @Override public int delUser(int id) { try { return qr.update("delete from user where id = ?",id); } catch (SQLException e) { throw new RuntimeException(e); } } /** * 修改用户 * @param user * @return */ @Override public int updateUser(User user) { try { return qr.update("update user set username = ? where id = ?",user.getUsername(),user.getId()); } catch (SQLException e) { throw new RuntimeException(e); } } /** * 查询所有用户 * @return */ @Override public List<User> getAllUsers() { List<User> users = null; try { users = qr.query("select * from user", new BeanListHandler<User>(User.class)); return users; } catch (SQLException e) { throw new RuntimeException(e); } } /** * 根据用户id查询用户 * @param id 参数是 用户的id * @return */ @Override public User getUserById(int id) { try { User user = qr.query("select * from user where id = ?", new BeanHandler<User>(User.class),id); return user; } catch (SQLException e) { throw new RuntimeException(e); } } /** * 根据用户名获取用户 * @param username * @return */ @Override public User getUserByName(String username) { try { User user = qr.query("select * from user where username = ?", new BeanHandler<User>(User.class),username); return user; } catch (SQLException e) { throw new RuntimeException(e); } } @Override public User login(String username, String password) { try { return qr.query("select * from user where username=? and password =?", new BeanHandler<User>(User.class),username,password); } catch (SQLException e) { throw new RuntimeException(e); } } }
7.设计[表现层]代码
7.1 设计 Servlet代码
package cn.javabs.school.servlet; import cn.javabs.school.entity.User; import cn.javabs.school.service.UserService; import cn.javabs.school.service.impl.UserServiceImpl; import org.apache.commons.beanutils.BeanUtils; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; import java.lang.reflect.InvocationTargetException; import java.util.List; @WebServlet("/userServlet") public class UserServlet extends HttpServlet { UserService userService = new UserServiceImpl(); protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet( request, response); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); String op = request.getParameter("op"); if ("addUser".equals(op)){ addUser( request, response); }else if ("updateUser".equals(op)){ updateUser( request, response); }else if("delUser".equals(op)){ delUser( request, response); }else if("findAllUsers".equals(op)){ findAllUsers( request, response); }else if("editUser".equals(op)){ editUser( request, response); }else if("goToAddUserView".equals(op)){ goToAddUserView( request, response); }else if("userLogin".equals(op)){ userLogin( request, response); }else if("userLogout".equals(op)){ userLogout( request, response); } else{ System.out.println("参数传递有误!"); } } /** * 用户退出 * @param request * @param response */ private void userLogout(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { HttpSession session = request.getSession(); session.removeAttribute("USER_SESSION"); request.getRequestDispatcher("/index.jsp").forward(request,response); } /** * 用户登录 * @param request * @param response */ private void userLogin(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { User user = new User(); try { BeanUtils.populate(user,request.getParameterMap()); } catch (Exception e) { throw new RuntimeException(e); } User u = userService.UserLogin(user.getUsername(), user.getPassword()); if(u!= null){ HttpSession session = request.getSession(); session.setAttribute("USER_SESSION",u);// USER_SESSION 是一个记号|标记 request.getRequestDispatcher("/admin/main.jsp").forward(request,response); }else{ request.setAttribute("msg","用户登录失败"); request.getRequestDispatcher("/message.jsp").forward(request,response); } } /** * 去添加用户页面的方法 * @param request * @param response */ private void goToAddUserView(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.getRequestDispatcher("/admin/addUser.jsp").forward(request,response); } /** * 查询全部用户 * @param request * @param response * @throws ServletException * @throws IOException */ private void findAllUsers(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List<User> list = userService.findAllUser(); request.setAttribute("list",list); request.getRequestDispatcher("/admin/UserList.jsp").forward(request,response); } /** * 修改用户之第二个环节,提交数据 * @param request * @param response * @throws ServletException * @throws IOException */ private void updateUser(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String id = request.getParameter("id"); int userId = Integer.parseInt(id); User user = new User(); try { user.setId(userId);// user中只要id 有没有username password ... 都没有 BeanUtils.populate(user,request.getParameterMap());// 有了username password ... | 不会管 id的问题 // user 有 了 id username password ... } catch (Exception e) { throw new RuntimeException(e); } int rows = userService.updateUser(user); if (rows>0){ request.setAttribute("msg","修改用户成功"); request.getRequestDispatcher("/message.jsp").forward(request,response); }else{ request.setAttribute("msg","修改用户失败"); request.getRequestDispatcher("/message.jsp").forward(request,response); } } /** * 根据id删除用户 * @param request * @param response * @throws ServletException * @throws IOException */ private void delUser(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String id = request.getParameter("id"); int userId = Integer.parseInt(id); int rows = userService.delUser(userId); if (rows>0){ request.setAttribute("msg","删除用户成功"); request.getRequestDispatcher("/message.jsp").forward(request,response); }else{ request.setAttribute("msg","删除用户失败"); request.getRequestDispatcher("/message.jsp").forward(request,response); } } /** * 添加用户 * @param request * @param response * @throws ServletException * @throws IOException */ private void addUser(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { User user = new User(); try { BeanUtils.populate(user,request.getParameterMap()); int rows = userService.addUser(user); if (rows>0){ request.setAttribute("msg","添加用户成功"); request.getRequestDispatcher("/message.jsp").forward(request,response); }else{ request.setAttribute("msg","添加用户失败"); request.getRequestDispatcher("/message.jsp").forward(request,response); } } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } } /** * 修改用户之第一个环节,数据回显 * @param request * @param response * @throws ServletException * @throws IOException */ private void editUser(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String id = request.getParameter("id"); System.out.println("id:"+id); int userId = Integer.parseInt(id); System.out.println("userId:"+userId); User user = userService.findUserById(userId); if (user != null){ request.setAttribute("user",user); request.getRequestDispatcher("/admin/updateUser.jsp").forward(request,response); }else{ request.setAttribute("msg","用户数据回显失败"); request.getRequestDispatcher("/message.jsp").forward(request,response); } } }
7.2 设计jsp页面的代码
在web中创建两个文件夹
一个front:用于存放前台页面的文件夹;
另一个为admin:用于存放后台页面的文件夹
目前我们在写的页面是后台的模块,因此在admin里创建页面。
首页index.jsp【位于web下】
<%-- Created by IntelliJ IDEA. User: Mryang Date: 2019/6/11 Time: 13:54 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>用户管理首页</title> </head> <body> <a href="${pageContext.request.contextPath}/userServlet?op=goToAddUserView">添加用户</a> <a href="${pageContext.request.contextPath}/userServlet?op=findAllUsers">所有用户</a> </body> </html>
用户登录页面login.jsp【位于web下】
<%-- Created by IntelliJ IDEA. User: Mryang Date: 2019/6/12 Time: 9:23 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> <form action="${pageContext.request.contextPath}/userServlet?op=userLogin" method="post"> <table border="1"> <tr> <td> 用户名 </td> <td> <input type="text" name="username" /> </td> <td></td> </tr> <tr> <td> 密码 </td> <td> <input type="text" name="password" /> </td> <td></td> </tr> <tr align="center"> <td colspan="2"> <input type="submit" value="用户登录" /> </td> <td></td> </tr> </table> </form> </body> </html>
提示信息页面message.jsp【位于web下】
<%-- Created by IntelliJ IDEA. User: Mryang Date: 2019/6/12 Time: 8:17 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> ${msg} </body> </html>
添加用户addUser.jsp【位于admin下】
<%-- Created by IntelliJ IDEA. User: Mryang Date: 2019/6/12 Time: 8:08 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> <form action="${pageContext.request.contextPath}/userServlet?op=addUser" method="post"> <table border="1"> <tr> <td> 用户名 </td> <td> <input type="text" name="username" /> </td> <td></td> </tr> <tr> <td> 密码 </td> <td> <input type="text" name="password" /> </td> <td></td> </tr> <tr> <td>性别</td> <td> <input type="text" name="sex" /> </td> <td></td> </tr> <tr> <td>出生年月</td> <td> <input type="text" name="birthday" /> </td> <td></td> </tr> <tr align="center"> <td colspan="2"> <input type="submit" value="添加用户" /> </td> <td></td> </tr> </table> </form> </body> </html>
查询用户列表UserList.jsp
<%-- Created by IntelliJ IDEA. User: Mryang Date: 2019/6/11 Time: 16:02 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%--jstl 标准标签库--%> <html> <head> <title>用户列表</title> </head> <body> <table border="1" width="438"> <c:forEach items="${list}" var="l"> <tr> <td>${l.id}</td> <td>${l.username}</td> <td>${l.birthday}</td> <td> <a href="JavaScript:delUser('${l.id}')">删除</a> <a href="${pageContext.request.contextPath}/userServlet?op=editUser&id=${l.id}">修改</a> </td> </tr> </c:forEach> </table> </body> </html> <script> function delUser(id) { var sure = confirm("你确定要删除吗?"); if(sure){ window.location.href="${pageContext.request.contextPath}/userServlet?op=delUser&id="+id; }else { alert("NoOk") } } </script>
修改用户页面updateUser
<%-- Created by IntelliJ IDEA. User: Mryang Date: 2019/6/12 Time: 8:08 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>修改用户页面</title> </head> <body> <form action="${pageContext.request.contextPath}/userServlet?op=updateUser&id=${user.id}" method="post"> <table border="1"> <tr> <td> 用户名 </td> <td> <input type="text" name="username" value="${user.username}" /> </td> <td></td> </tr> <tr> <td> 密码 </td> <td> <input type="text" name="password" value="${user.password}" /> </td> <td></td> </tr> <tr> <td>性别</td> <td> <input type="text" name="sex" value="${user.sex}"/> </td> <td></td> </tr> <tr> <td>出生年月</td> <td> <input type="text" name="birthday" value="${user.birthday}"/> </td> <td></td> </tr> <tr align="center"> <td colspan="2"> <input type="submit" value="修改用户" /> </td> <td></td> </tr> </table> </form> </body> </html>
8. 将代码提交到GitHub
案例代码:https://github.com/yangsir1688/controlManagerPage/tree/master/jlnk-school/demo-case
如图: