SMBMS
项目如何搭建?
考虑使用不使用Maven?依赖、Jar
项目搭建准备工作
1、搭建maven web项目
2、配置Tomcat
3、测试项目是否能够跑起来
4、导入项目用到的jar包
jsp,Servlet,mysql
5、创建项目包结构
dao:操作数据库层
filter:过滤器层
pojo:普通实体类层
serive:服务层,写业务的
servlet:
util:工具类
6、编写实体类
ORM映射:表-类映射
7、编写基础公共类
-
数据库配置文件。Resources目录下建db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://10.1.136.28:3306?useUnicode=true&characterEncoding=utf-8 username=root password=t7780243
-
编写数据库的公共类
package cn.zjnu.edu.dao; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.net.URL; import java.sql.*; import java.util.Properties; //操作数据库的公共类 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() throws ClassNotFoundException, SQLException { Class.forName(driver); Connection connection = DriverManager.getConnection(url, username, password); 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(); //预编译,直接执行就好了。不用传sql 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,PreparedStatement preparedStatement,ResultSet resultSet){ 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(); //GC回收 preparedStatement=null; } catch (SQLException e) { e.printStackTrace(); flag=false; } } if (connection != null) { try { connection.close(); //GC回收 connection=null; } catch (SQLException e) { e.printStackTrace(); flag=false; } } return flag; } }
-
编写字符编码过滤器
8、 导入静态资源
登录功能实现
-
编写前段页面
-
设置首页
<!-- 设置首页--> <welcome-file-list> <welcome-file>login.jsp</welcome-file> </welcome-file-list>
-
编写dao层用户登录
//得到要登录的用户 public User getLoginuser(Connection connection, String userCode) throws SQLException;
-
编写dao接口的实现类
public class UserDaoImpl implements UserDao { //得到要登录的用户 @Override 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 User login(String userCode, String password) throws SQLException, ClassNotFoundException;
-
业务层实现类
public class UserServiceImpl implements UserService { //业务层都会调用dao层,所以我们要引入Dao层 private UserDao userDao; public UserServiceImpl() { userDao = new UserDaoImpl(); } @Override public User login(String userCode, String password) { Connection connection = null; User user = null; try { connection = BaseDao.getconnection(); //通过业务层调用对应的具体的数据库操作 user = userDao.getLoginuser(connection, userCode); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection, null, null); } return user; } @Test public void test(){ UserServiceImpl userService = new UserServiceImpl(); User admin = userService.login("admin", "12223"); System.out.println(admin.toString()); } }
-
编写Servlet
package cn.zjnu.edu.servlet.LoginServlet; import cn.zjnu.edu.pojo.User; import cn.zjnu.edu.serive.user.UserServiceImpl; import cn.zjnu.edu.util.Constants; import javax.servlet.*; import javax.servlet.http.*; import java.io.IOException; public class Servlet extends HttpServlet { //servlet:控制层,调用业务层代码 @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("LoginServlet--start"); //获取用户命和密码 String userCode = request.getParameter("userCode"); String userPassword = request.getParameter("userPassword"); //和数据库中的密码进行对比,调用业务层 UserServiceImpl userService = new UserServiceImpl(); User user = userService.login(userCode, userPassword);//这里已经把登录的人查出来了 if (user != null) {//查有此人,可以登录 //将用户的信息放到session中 request.getSession().setAttribute(Constants.USER_SESS, user); response.sendRedirect("jsp/frame.jsp"); } else {//查无此人,无法登录 //转发回登录页面,顺带提示它,用户名或者密码错误 request.setAttribute("error","用户名或者密码不正确"); request.getRequestDispatcher("login.jsp").forward(request,response); } } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
8、 注册Servlet
<servlet> <servlet-name>LoginServlet</servlet-name> <servlet-class>cn.zjnu.edu.servlet.user.Servlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LoginServlet</servlet-name> <url-pattern>/login.do</url-pattern> </servlet-mapping>
9、测试访问,确保以上成功!
登录功能优化
注销功能:
思路:移除session,返回
package cn.zjnu.edu.servlet.user;
import cn.zjnu.edu.util.Constants;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class LogoutServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//移除用户的session
req.getSession().removeAttribute(Constants.USER_SESS);
resp.sendRedirect(req.getContextPath()+"/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>cn.zjnu.edu.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 config) throws ServletException {
}
public void destroy() {
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws ServletException, IOException {
HttpServletRequest req = (HttpServletRequest) request;
HttpServletResponse resp = (HttpServletResponse) response;
//过滤器,从Session中获取用户
User user = (User) req.getSession().getAttribute(Constants.USER_SESS);
if (user == null) { //已经被移除获取注销了,或者未登录
resp.sendRedirect(req.getContextPath() + "/error.jsp");
} else {
chain.doFilter(request, response);
}
}
}
注册一下
<filter>
<filter-name>SysFilter</filter-name>
<filter-class>cn.zjnu.edu.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接口实现类
//修改当前用户密码 @Override public int updatePwd(Connection connection, int id, int password) throws SQLException { PreparedStatement pstm = null; int execute = 0; if (connection != null) { String sql = "update smbms.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层
public boolean updatePwd(int id, int password) throws SQLException, ClassNotFoundException;
-
UserService实现类
@Override public boolean updatePwd(int id, int password) throws SQLException, ClassNotFoundException { Connection connection = null; boolean flag=false; //修改密码 try { connection = BaseDao.getconnection(); if (userDao.updatePwd(connection, id, password)>0) { flag = true; } } catch (SQLException e) { e.printStackTrace(); }finally { BaseDao.closeResource(connection, null, null); } return flag; }
-
记得实现复用,需要提取出方法!
public void updatepwd(HttpServletRequest request, HttpServletResponse response) { //从session里面拿ID; User o = (User) request.getSession().getAttribute(Constants.USER_SESS); String newpassword = request.getParameter("newpassword"); System.out.println(o==null); System.out.println(StringUtils.isNullOrEmpty(newpassword)); if (o != null && !StringUtils.isNullOrEmpty(newpassword)) { UserServiceImpl userService = new UserServiceImpl(); boolean flag = false; flag = userService.updatePwd(o.getId(), newpassword); System.out.println(flag); if (flag) { request.setAttribute("message", "修改密码成功,请退出,使用新密码退出"); //密码修改成功,移除当前Session request.getSession().removeAttribute(Constants.USER_SESS); } else { //密码修改失败, request.setAttribute("message", "密码修改失败"); } } else { request.setAttribute("message", "新密码有问题"); } try { request.getRequestDispatcher("pwdmodify.jsp").forward(request, response); } catch (ServletException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
-
测试
-
优化密码修改使用Ajax
//验证旧密码Servlet
public void pwdModify(HttpServletRequest request, HttpServletResponse response) {
User user = (User) request.getSession().getAttribute(Constants.USER_SESS);
boolean flag=false;
String oldpassword = request.getParameter("oldpassword");
//万能的Map:结果集
HashMap<String, String> resultMap = new HashMap<String, String>();
if (user == null) {//Session失效了,
resultMap.put("result", "sessionerror");
} else if (StringUtils.isNullOrEmpty(oldpassword)) {//输入密码为空
resultMap.put("result", "error");
}else {
String userPassword = user.getUserPassword();
if (oldpassword.equals(userPassword)) {
resultMap.put("result", "true");
} else {
resultMap.put("result", "false");
}
}
try {
response.setContentType("application/json");
PrintWriter writer = response.getWriter();
// JSONArray 阿里巴巴的json工具类,转换格式
/*
resultMap= {result=sessionerror, result3=true, result2=false, result1=true}
*/
writer.write(JSONArray.toJSONString(resultMap));
writer.flush();
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// Serive端
public boolean updatePwd(int id, String password) {
Connection connection = null;
boolean flag=false;
//修改密码
try {
connection = BaseDao.getconnection();
if (userDao.updatePwd(connection, id, password)>0) {
System.out.println("修改成功");
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection, null, null);
}
return flag;
}
用户管理实现
思路:
1、导入分页的工具类
2、用户列表页面导入
userlist.jsp
1、获取用户数量
-
userDao
//查询用户总数 public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
-
UserDaoImpl
//根据用户名或者角色查询用户总数 @Override public int getUserCount(Connection connection, String username, int userRole) throws SQLException { PreparedStatement pstm = null; ResultSet rs = null; int count = 0; if (connection != null) { StringBuffer sql = new StringBuffer(); sql.append("select count(1) count from smbms.smbms_user t1 ,smbms.smbms_role t2 where t1.userRole=t2.id"); ArrayList<Object> list = new ArrayList<Object>(); if (username!=null) { sql.append(" and t1.userName like ?"); list.add("%" + username + "%");//index:0 } if (userRole > 0) { sql.append(" and t1.userRole=?"); list.add(userRole);//index:1 } //怎么把list转换为数组? Object[] params = list.toArray(); System.out.println(sql.toString()); //输出最后完整的sql语句 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) throws SQLException, ClassNotFoundException;
-
UserServiceImpl
@Override public int getUserCount(String username, int userRole) throws SQLException, ClassNotFoundException { Connection getconnection = null; int userCount = 0; try { getconnection = BaseDao.getconnection(); userCount = userDao.getUserCount(getconnection, username, userRole); return userCount; } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { BaseDao.closeResource(getconnection,null,null); } return userCount; }
2、获取用户列表
1、userDao
//通过条件查询-userList public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
2、userDaoImp
@Override public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception { // TODO Auto-generated method stub 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); } 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")); userList.add(_user); } BaseDao.closeResource(null, pstm, rs); } return userList; }
3、usersevice
//根据条件查询用户列表 public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
4、userseviceImp
@Override public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) { // TODO Auto-generated method stub 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) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ BaseDao.closeResource(connection, null, null); } return userList; }
2、获取用户列表
3、获取角色操作
为了我们职权同一,可以把角色的操作单独放在一个包中,和pojo类对应
RoleDao
//获取角色列表 public List<Role> getRoleList(Connection connection) throws SQLException;
RoleDaoImpl
@Override //获取角色列表 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.smbms_role"; Object[] params = {}; BaseDao.execute(connection, pstm, sql, params); while (resultSet.next()) { Role role = new Role(); int id = resultSet.getInt("id"); String roleName = resultSet.getString("roleName"); String roleCode = resultSet.getString("roleCode"); role.setId(id); role.setRoleName(roleName); role.setRoleCode(roleCode); roleList.add(role); } } BaseDao.closeResource(null, pstm, resultSet); return roleList; }
RoleService
public interface RoleService { public List<Role> getRoleList() throws SQLException, ClassNotFoundException; }
RoleServiceImpl
private RoleDao roleDao; public RoleServiceImpl() { roleDao = new RoleDaoImpl(); } @Override public List<Role> getRoleList() throws SQLException, ClassNotFoundException { Connection connection = null; List<Role> roleList = null; try { connection = BaseDao.getconnection(); roleList = roleDao.getRoleList(connection); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { BaseDao.closeResource(connection, null, null); } return roleList; }
4、用户显示的Servlet
- 获取用户前段的数据(查询)
- 判断请求是否需要执行,看参数的
- 为了实现分页,需要计算出当前页面和总页码,以及页面大小