SMBMS
数据库
项目如何搭建？
考虑是否使用Maven？依赖，Jar
项目搭建准备工作
-
搭建一个maven web项目
-
配置Tomcat
-
测试项目是否能够跑起来
-
导入项目中会遇到的jar包
jsp，Servlet，mysql驱动，jstl，standard……
-
创建项目包结构
-
编写实体类
ORM映射：表-类映射
-
编写基础公共类
-
数据库配置文件:db.properties
driver=com.mysql.jdbc.Driver #在和mysql传递数据的过程中，使用unicode编码格式，并且字符集设置为utf-8 url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8 username=root password=123456 -
编写数据库的公共类
// 操作数据库的公共类 public class BaseDao { private static String driver; private static String url; private static String username; private static String password; // 静态代码块，类加载的时候就初始化了 static { Properties properties = new Properties(); // 通过类加载器读取对应的资源 InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties"); try { properties.load(is); } catch (IOException e) { e.printStackTrace(); } driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); } // 获取数据库的连接 public static Connection getConnection() { Connection connection = null; try { Class.forName(driver); connection = DriverManager.getConnection(url,username,password); } catch (Exception e) { e.printStackTrace(); } return connection; } // 编写查询公共类 public static ResultSet execute(Connection connection, String sql, Object[] params, ResultSet resultSet, PreparedStatement preparedStatement) throws SQLException { // 预编译的sql，在后面直接执行即可 preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { // setObject，占位符从1开始，但是我们数组是从0开始！ preparedStatement.setObject(i+1,params[i]); } resultSet = preparedStatement.executeQuery(); return resultSet; } // 编写增删改公共方法 public static int execute(Connection connection, String sql, Object[] params, PreparedStatement preparedStatement) throws SQLException { preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { // setObject，占位符从1开始，但是我们数组是从0开始！ preparedStatement.setObject(i+1,params[i]); } int updateRows = preparedStatement.executeUpdate(); return updateRows; } // 释放资源 public static boolean closeResource(Connection connection, ResultSet resultSet, PreparedStatement preparedStatement) { boolean flag = true; if (resultSet != null) { try { resultSet.close(); // GC回收 resultSet = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (preparedStatement != null) { try { preparedStatement.close(); preparedStatement = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (connection != null) { try { connection.close(); connection = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } return flag; } } -
编写字符编码过滤器
public class CharacterEncodingFilter implements Filter { public void init(FilterConfig filterConfig) throws ServletException {} public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); chain.doFilter(request,response); } public void destroy() {} }<!--字符编码过滤器--> <filter> <filter-name>CharacterEncodingFilter</filter-name> <filter-class>com.won.filter.CharacterEncodingFilter</filter-class> </filter> <filter-mapping> <filter-name>CharacterEncodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping>
-
-
导入静态资源
登录功能实现
-
编写前端页面
-
设置首页
<!--设置欢迎页面--> <welcome-file-list> <welcome-file>login.jsp</welcome-file> </welcome-file-list> -
编写dao层登录用户的接口
public interface UserDao { // 得到要登陆的用户 public User getLoginUser(Connection connection,String userCode) throws SQLException; } -
编写dao接口的实现类
public class UserDaoImpl implements UserDao { public User getLoginUser(Connection connection, String userCode) throws SQLException { PreparedStatement pstm = null; ResultSet rs = null; User user = null; if (connection != null) { String sql = "select * from smbms_user where userCode=?"; Object[] params = {userCode}; rs = BaseDao.execute(connection, pstm, rs, sql, params); if (rs.next()) { user = new User(); user.setId(rs.getInt("id")); user.setUserCode(rs.getString("userCode")); user.setUserName(rs.getString("userName")); user.setUserPassword(rs.getString("userPassword")); user.setGender(rs.getInt("gender")); user.setBirthday(rs.getDate("birthday")); user.setPhone(rs.getString("phone")); user.setAddress(rs.getString("address")); user.setUserRole(rs.getInt("userRole")); user.setCreatedBy(rs.getInt("createdBy")); user.setCreationDate(rs.getTimestamp("creationDate")); user.setModifyBy(rs.getInt("modifyBy")); user.setModifyDate(rs.getTimestamp("modifyDate")); } BaseDao.closeResource(null,pstm,rs); } return user; } } -
业务层接口
public interface UserService { // 用户登录 public User login(String userCode, String password); } -
业务层实现类
public class UserServiceImpl implements UserService { // 业务层都会调用dao层，所以要引入Dao层 private UserDao userDao; public UserServiceImpl() { userDao = new UserDaoImpl(); } public User login(String userCode, String password) { Connection connection = null; User user = null; try { connection = BaseDao.getConnection(); // 通过业务层调用对应的具体的数据库操作 user = userDao.getLoginUser(connection,userCode); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return user; } @Test public void test() { UserServiceImpl userService = new UserServiceImpl(); User admin = userService.login("admin", "1234567"); System.out.println(admin.getUserPassword()); } } -
编写Servlet
public class LoginServlet extends HttpServlet { // Servlet：控制层，调用业务层代码 @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { System.out.println("LoginServlet--start......"); // 获取用户名和密码 String userCode = req.getParameter("userCode"); String userPassword = req.getParameter("userPassword"); // 和数据库中的密码进行对比，调用业务层 UserServiceImpl userService = new UserServiceImpl(); User user = userService.login(userCode, userPassword); // 这里已经把登录的人查出来了 if (user != null) { // 查有此人，可以登录 // 将用户的信息放到Session中 req.getSession().setAttribute(Constants.USER_SESSION,user); // 跳转到主页 resp.sendRedirect("jsp/frame.jsp"); } else { // 查无此人，无法登录 // 转发回登录页面，顺带提示它，用户名或密码错误 req.setAttribute("error","用户名或密码不正确"); req.getRequestDispatcher("login.jsp").forward(req,resp); } } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } } -
注册Servlet
<servlet> <servlet-name>LoginServlet</servlet-name> <servlet-class>com.won.servlet.user.LoginServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LoginServlet</servlet-name> <url-pattern>/login.do</url-pattern> </servlet-mapping> -
测试访问，确保以上功能成功！
登录功能优化
注销功能：
思路：移除Session，返回登录页面
public class LogoutServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 清除Session
req.getSession().removeAttribute(Constants.USER_SESSION);
resp.sendRedirect("/login.jsp"); // 返回登录页面
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
注册xml
<servlet>
<servlet-name>LogoutServlet</servlet-name>
<servlet-class>com.won.servlet.user.LogoutServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LogoutServlet</servlet-name>
<url-pattern>/jsp/logout.do</url-pattern>
</servlet-mapping>
登录拦截优化
编写过滤器并注册
public class SysFilter implements Filter {
public void init(FilterConfig filterConfig) throws ServletException {}
public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest) req;
HttpServletResponse response = (HttpServletResponse) resp;
// 过滤器，从Session中获取用户
User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);
if (user == null) { // 已经移除或者注销了，或者未登录
response.sendRedirect("/smbms/error.jsp");
} else {
chain.doFilter(req,resp);
}
}
public void destroy() {}
}
<!--用户登录过滤器-->
<filter>
<filter-name>SysFilter</filter-name>
<filter-class>com.won.filter.SysFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>SysFilter</filter-name>
<url-pattern>/jsp/*</url-pattern>
</filter-mapping>
测试，登录，注销，权限，都要保证完善！
密码修改
-
导入前端素材
<li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">密码修改</a></li> -
写项目，建议从底层向上写
-
UserDao接口
// 修改当前用户密码 public int updatePwd(Connection connection, int id, int password) throws SQLException; -
UserDao接口实现类
// 修改当前用户密码 public int updatePwd(Connection connection, int id, int password) throws SQLException { PreparedStatement pstm = null; int execute = 0; if (connection != null) { String sql = "update smbms_user set userPassword = ? where id = ?"; Object[] params = {password, id}; execute = BaseDao.execute(connection, pstm, sql, params); BaseDao.closeResource(null, pstm, null); } return execute; } -
UserService层
// 根据用户id修改密码 public boolean updatePwd(int id, int pwd); -
UserService实现类
public boolean updatePwd(int id, int pwd) { Connection connection = null; boolean flag = false; try { connection = BaseDao.getConnection(); if (userDao.updatePwd(connection, id, pwd) > 0) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null, null); } return flag; } -
记得实现复用，需要提取方法！
// 实现Servlet复用 public class UserServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String method = req.getParameter("method"); if (method.equals("savepwd") && method != null) { this.updatePwd(req,resp); } } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } public void updatePwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // 从Session里面拿id Object o = req.getSession().getAttribute(Constants.USER_SESSION); String newpassword = req.getParameter("newpassword"); System.out.println("UserServlet:" + newpassword); boolean flag = false; //System.out.println((User)o); //System.out.println(StringUtils.isNullOrEmpty(newpassword)); if (o != null && newpassword != null) { UserServiceImpl userService = new UserServiceImpl(); flag = userService.updatePwd(((User) o).getId(), newpassword); if (flag) { req.setAttribute("message","修改密码成功，请退出，使用新密码登录！"); // 密码修改成功，移除当前Session req.getSession().removeAttribute(Constants.USER_SESSION); } else { req.setAttribute("message","修改密码失败！"); } } else { req.setAttribute("message","新密码有问题！"); } req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp); } } -
测试
优化密码修改使用Ajax -
阿里巴巴的fastjson
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.61</version> </dependency> -
后台代码修改
// 修改密码 public void updatePwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // 从Session里面拿id Object o = req.getSession().getAttribute(Constants.USER_SESSION); String newpassword = req.getParameter("newpassword"); System.out.println("UserServlet:" + newpassword); boolean flag = false; //System.out.println((User)o); //System.out.println(StringUtils.isNullOrEmpty(newpassword)); if (o != null && newpassword != null) { UserServiceImpl userService = new UserServiceImpl(); flag = userService.updatePwd(((User) o).getId(), newpassword); if (flag) { req.setAttribute("message","修改密码成功，请退出，使用新密码登录！"); // 密码修改成功，移除当前Session req.getSession().removeAttribute(Constants.USER_SESSION); } else { req.setAttribute("message","修改密码失败！"); } } else { req.setAttribute("message","新密码有问题！"); } req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp); } // 验证旧密码，session中有用户的密码 public void pwdModify(HttpServletRequest req, HttpServletResponse resp) { Object o = req.getSession().getAttribute(Constants.USER_SESSION); String oldpassword = req.getParameter("oldpassword"); // 万能的Map：结果集 Map<String,String> resultMap = new HashMap<String, String>(); if (o == null) { // session失效或者session过期 resultMap.put("result","sessionerror"); } else if(StringUtils.isNullOrEmpty(oldpassword)) { // 输入的密码为空 resultMap.put("result","error"); } else { String userPassword = ((User) o).getUserPassword(); // Session中用户的密码 if (oldpassword.equals(userPassword)) { resultMap.put("result","true"); } else { resultMap.put("result","false"); } } try { resp.setContentType("application/json"); PrintWriter writer = resp.getWriter(); // JSONArray阿里巴巴的JSON工具类，转换格式 writer.write(JSONArray.toJSONString(resultMap)); writer.flush(); writer.close(); } catch (IOException e) { e.printStackTrace(); } } -
测试
用户管理实现
思路：
- 导入分页的工具类
- 用户列表页面导入
userlist.jsp
1、获取用户数量
- UserDao
// 查询用户总数 public int getUserCount(Connection connection,String username,int userRole) throws SQLException; - UserDaoImpl
// 根据用户名或者用户角色查询用户总数【最难理解的SQL】 public int getUserCount(Connection connection, String username, int userRole) throws SQLException { PreparedStatement pstm = null; ResultSet rs = null; int count = 0; ArrayList<Object> list = new ArrayList<Object>(); // 存放我们的参数 if (connection != null) { StringBuffer sql = new StringBuffer(); sql.append("select count(1) as count from smbms_user u,smbms_role r where r.id = u.userRole"); if (!StringUtils.isNullOrEmpty(username)) { sql.append(" and u.userName like ?"); list.add("%" + username + "%"); // index:0 } if (userRole > 0) { sql.append(" and u.userRole = ?"); list.add(userRole); // index:1 } // 将ArrayList转换为数组 Object[] params = list.toArray(); System.out.println("UserDaoImpl --> getUserCount:" + sql.toString()); rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params); if (rs.next()) { count = rs.getInt("count"); // 从结果集中获取最终的数量 } BaseDao.closeResource(null,pstm,rs); } return count; } - UserService
// 查询记录数 public int getUserCount(String username, int userRole); - UserServiceImpl
// 查询记录数 public int getUserCount(String username, int userRole) { Connection connection = null; int count = 0; try { connection = BaseDao.getConnection(); count = userDao.getUserCount(connection, username, userRole); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return count; }
2、获取用户列表
- UserDao
// 获取条件查询-userList public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws Exception; - UserDaoImpl
public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws Exception { PreparedStatement pstm = null; ResultSet rs = null; List<User> userList = new ArrayList<User>(); if (connection != null) { StringBuffer sql = new StringBuffer(); sql.append("select u.*,r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id"); List<Object> list = new ArrayList<Object>(); if (!StringUtils.isNullOrEmpty(username)) { sql.append(" and u.userName like ?"); list.add("%"+username+"%"); } if (userRole > 0) { sql.append(" and u.userRole = ?"); list.add(userRole); // index:1 } sql.append(" order by creationDate DESC limit ?,?"); currentPageNo = (currentPageNo-1)*pageSize; list.add(currentPageNo); list.add(pageSize); Object[] params = list.toArray(); System.out.println("sql ----> "+sql.toString()); rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params); while (rs.next()) { User _user = new User(); _user.setId(rs.getInt("id")); _user.setUserCode(rs.getString("userCode")); _user.setUserName(rs.getString("userName")); _user.setGender(rs.getInt("gender")); _user.setBirthday(rs.getDate("birthday")); _user.setPhone(rs.getString("phone")); _user.setUserRole(rs.getInt("userRole")); _user.setUserRoleName(rs.getString("userRoleName")); } BaseDao.closeResource(null, pstm, rs); } return userList; } - UserService
// 根据条件查询用户列表 public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize); - UserServiceImpl
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) { Connection connection = null; List<User> userList=null; System.out.println("queryUserName ---> " + queryUserName); System.out.println("queryUserRole ---> " + queryUserRole); System.out.println("currentPageNo ---> " + currentPageNo); System.out.println("pageSize ---> " + pageSize); try { connection = BaseDao.getConnection(); userList = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return userList; }
3、获取角色操作
为了职责统一，可以把角色的操作单独放在一个包中，和POJO类对应。
RoleDao
public interface RoleDao {
// 获取角色列表
public List<Role> getRoleList(Connection connection) throws SQLException;
}
RoleDaoImpl
public class RoleDaoImpl implements RoleDao {
public List<Role> getRoleList(Connection connection) throws SQLException {
// 获取角色列表
PreparedStatement pstm = null;
ResultSet resultSet = null;
ArrayList<Role> roleList = new ArrayList<Role>();
if (connection != null) {
String sql = "select * from smbms_role";
Object[] params = {};
resultSet = BaseDao.execute(connection, pstm, resultSet, sql, params);
while (resultSet.next()) {
Role _role = new Role();
_role.setRoleName(resultSet.getString("roleName"));
_role.setId(resultSet.getInt("id"));
_role.setRoleCode(resultSet.getString("roleCode"));
roleList.add(_role);
}
BaseDao.closeResource(null,pstm,resultSet);
}
return roleList;
}
}
RoleService
public interface RoleService {
// 获取角色列表
public List<Role> getRoleList();
}
RoleServiceImpl
public class RoleServiceImpl implements RoleService {
// 引入Dao
private RoleDao roleDao;
public RoleServiceImpl(RoleDao roleDao) {
roleDao = new RoleDaoImpl();
}
public List<Role> getRoleList(){
Connection connection = null;
List<Role> roleList = null;
try {
connection = BaseDao.getConnection();
roleList = roleDao.getRoleList(connection);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection,null,null);
}
return roleList;
}
}
4、用户显示的Servlet
- 获取用户前端的数据（查询）
- 判断请求是否需要执行，判断参数的值
- 为了实现分页，需要计算出当前页面和总页面，页面大小……
- 用户列表展示
- 返回前端
public class PageSupport {
//当前页码-来自于用户输入
private int currentPageNo = 1;
//总数量（表）
private int totalCount = 0;
//页面容量
private int pageSize = 0;
//总页数-totalCount/pageSize（+1）
private int totalPageCount = 1;
public int getCurrentPageNo() {
return currentPageNo;
}
public void setCurrentPageNo(int currentPageNo) {
if(currentPageNo > 0){
this.currentPageNo = currentPageNo;
}
}
public int getTotalCount() {
return totalCount;
}
// OOP三大特性：封装（属性私有，get/set，在set中限定一些不安全的情况）、继承、多态
public void setTotalCount(int totalCount) {
if(totalCount > 0){
this.totalCount = totalCount;
//设置总页数
this.setTotalPageCountByRs();
}
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
if(pageSize > 0){
this.pageSize = pageSize;
}
}
public int getTotalPageCount() {
return totalPageCount;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount = totalPageCount;
}
public void setTotalPageCountByRs(){
if(this.totalCount % this.pageSize == 0){
this.totalPageCount = this.totalCount / this.pageSize;
}else if(this.totalCount % this.pageSize > 0){
this.totalPageCount = this.totalCount / this.pageSize + 1;
}else{
this.totalPageCount = 0;
}
}
}
// 重点，难点
public void query(HttpServletRequest req, HttpServletResponse resp) throws IOException {
// 查询用户列表
// 从前端获取数据
String queryUserName = req.getParameter("queryName");
String temp = req.getParameter("queryUserRole");
String pageIndex = req.getParameter("pageIndex");
int queryUserRole = 0;
// 获取用户列表
UserServiceImpl userService = new UserServiceImpl();
List<User> userList = null;
// 第一次走这个请求，一定是第一页，页面大小固定
int pageSize = 5; // 可以把这个写到配置文件中，方便后期修改
int currentPageNo = 1;
if (queryUserName == null) {
queryUserName = "";
}
if (temp != null && !temp.equals("")) {
queryUserRole = Integer.parseInt(temp); // 给查询赋值！0,1,2,3
}
if (pageIndex != null) {
currentPageNo = Integer.parseInt(pageIndex);
}
// 获取用户的总数（分页：上一页，下一页的情况）
int totalCount = userService.getUserCount(queryUserName,queryUserRole);
// 总页数支持
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = ((int)(totalCount/pageSize))+1;
// 控制首页和尾页
// 如果页面小于1了，就显示第一页的东西
if (currentPageNo < 1 ) {
currentPageNo = 1;
} else if (currentPageNo > totalPageCount) { // 当前页面大于了最后一页
currentPageNo = totalPageCount;
}
// 获取用户列表展示
userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
req.setAttribute("userList",userList);
RoleServiceImpl roleService = new RoleServiceImpl();
List<Role> roleList = roleService.getRoleList();
req.setAttribute("roleList",roleList);
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
req.setAttribute("queryUserName",queryUserName);
req.setAttribute("queryUserRole",queryUserRole);
// 返回前端
try {
req.getRequestDispatcher("userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
}
}
扫一扫
261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
