项目流程图：
提示：由于时间关系，系统只写了用户这一条线，其他角色基本类似
项目架构设计
1.DAO包下Basedao，封装了数据库底层连接，查询，修改操作，并向外提供调用接口
package com.xia.dao;
import com.mysql.jdbc.Driver;
import java.io.IOException;
import java.io.InputStream;
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{
InputStream resourceAsStream = Basedao.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
try {
properties.load(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
}
//连接数据库
public static Connection getConnect(){
Connection con=null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return con;
}
//编写查询公共类
public static ResultSet execute(Connection connection,String sql,Object[] param) throws SQLException {
//预编译sql
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
preparedStatement.setObject(i+1,param[i]);
}
ResultSet resultSet = preparedStatement.executeQuery();
return resultSet;
}
//编写增删改
public static int update(Connection connection,String sql,Object[] param) throws SQLException {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
preparedStatement.setObject(i+1,param[i]);
}
int resultSet = preparedStatement.executeUpdate();
return resultSet;
}
//释放资源
public static boolean closeResources(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
boolean flag = true;
try {
if(resultSet!=null){
resultSet.close();
//垃圾回收
}
} catch (SQLException throwables) {
throwables.printStackTrace();
flag=false;
}
try {
if(preparedStatement!=null){
preparedStatement.close();
//垃圾回收
}
} catch (SQLException throwables) {
throwables.printStackTrace();
flag=false;
}
try {
if(connection!=null){
connection.close();
//垃圾回收
}
} catch (SQLException throwables) {
throwables.printStackTrace();
flag=false;
}
return flag;
}
}
2.DAO包下的UserDao接口,UserDaoImpl实现类，用来实现编写具体sql语句操作，向外提供接口
package com.xia.dao.user;
import com.xia.pojo.Role;
import com.xia.pojo.User;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public interface UserDao {
//得到要登录的用户
public User getloginUser(Connection connection,String userCode);
//修改密码
public int updatePwd(Connection connection,int id,String pwd);
//查询用户总数
public int getUserCount(Connection connection,String username,int userRole);
//获取用户列表
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
//添加用户
public int add(Connection connection,User user)throws Exception;
}
package com.xia.dao.user;
import com.mysql.cj.util.StringUtils;
import com.xia.dao.Basedao;
import com.xia.pojo.Role;
import com.xia.pojo.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl implements UserDao{
@Override
public User getloginUser(Connection connection, String userCode) {
PreparedStatement pstm=null;
User user=null;
ResultSet resultSet=null;
if (connection!=null) {
String sql = "select * from smbms_user where userCode=?";
Object[] param = {userCode};
try {
ResultSet rs = Basedao.execute(connection,sql,param);
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.closeResources(connection,null,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return user;
}
@Override
public int updatePwd(Connection connection, int id, String pwd) {
String sql = "update smbms_user set userPassword=? where id=? ";
PreparedStatement pstm = null;
int rs=0;
if (connection != null) {
Object[] param = {pwd,id};
try {
rs = Basedao.update(connection, sql, param);
Basedao.closeResources(connection, null, null);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return rs;
}
@Override
public int getUserCount(Connection connection, String username, int userRole) {
ResultSet resultSet=null;
int count=0;
if (connection != null) {
StringBuffer sql = new StringBuffer();
sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole=r.id");
ArrayList<Object> objects = new ArrayList<>(); //存在参数
if(!StringUtils.isNullOrEmpty(username)){
sql.append(" and u.userName like ?");
objects.add("%"+username+"%"); //index=0
}
if(userRole>0){
sql.append(" and u.userRole = ?");
objects.add(userRole);
}
Object[] objects1=objects.toArray();
System.out.println("UserdaoImpl->getUserCount:"+sql.toString());
try {
resultSet= Basedao.execute(connection, sql.toString(), objects1);
if(resultSet.next()){
count=resultSet.getInt("count"); //获取总数
}
Basedao.closeResources(null,null ,resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return count;
}
@Override
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)
throws Exception {
// TODO Auto-generated method stub
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, 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.closeResources(null, null, rs);
}
return userList;
}
@Override
public int add(Connection connection, User user) {
int updateRows = 0;
if(null != connection){
String sql = "insert into smbms_user (userCode,userName,userPassword," +
"userRole,gender,birthday,phone,address,creationDate,createdBy) " +
"values(?,?,?,?,?,?,?,?,?,?)";
Object[] params = {user.getUserCode(),user.getUserName(),user.getUserPassword(),
user.getUserRole(),user.getGender(),user.getBirthday(),
user.getPhone(),user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
try {
updateRows = Basedao.update(connection, sql, params);
Basedao.closeResources(null, null,null);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return updateRows;
}
}
3,Service包下的UserService，UserServiceImpL属于业务层代码，承上启下，是servlet和数据库调用的桥梁，专注于业务
package com.xia.service.user;
import com.xia.pojo.User;
import java.util.List;
public interface UserService {
//用户登录
public User login(String userCode,String password);
//修改密码
public int modifypwd(int id,String password);
//获取用户总数
public int getCount(String username,int userRole);
//获取用户列表
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
//增加用户
public boolean add(User user)throws Exception;
}
package com.xia.service.user;
import com.xia.dao.Basedao;
import com.xia.dao.user.UserDao;
import com.xia.dao.user.UserDaoImpl;
import com.xia.pojo.User;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
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.getConnect();
user=userDao.getloginUser(connection,userCode);
if (!(user.getUserPassword().equals(password)))
{
user=null;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
Basedao.closeResources(connection,null,null);
}
return user;
}
@Override
public int modifypwd(int id, String password) {
Connection connection=null;
int user=0;
try {
connection= Basedao.getConnect();
user=userDao.updatePwd(connection,id,password);
} catch (Exception e) {
e.printStackTrace();
}finally {
Basedao.closeResources(connection,null,null);
}
return user;
}
@Override
public int getCount(String username, int userRole) {
Connection connection=null;
int user=0;
try {
connection= Basedao.getConnect();
user=userDao.getUserCount(connection,username,userRole);
} catch (Exception e) {
e.printStackTrace();
}finally {
Basedao.closeResources(connection,null,null);
}
return user;
}
@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.getConnect();
userList = userDao.getUserList(connection, queryUserName,queryUserRole,currentPageNo,pageSize);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
Basedao.closeResources(connection, null, null);
}
return userList;
}
//增删改都属于事务，ACID,要考虑失败回滚
@Override
public boolean add(User user) {
boolean flag = false;
Connection connection = null;
try {
connection = Basedao.getConnect();
connection.setAutoCommit(false);//开启JDBC事务管理
int updateRows = userDao.add(connection,user);
connection.commit();
if(updateRows > 0){
flag = true;
System.out.println("add success!");
}else{
System.out.println("add failed!");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
System.out.println("rollback==================");
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
//在service层进行connection连接的关闭
Basedao.closeResources(connection, null, null);
}
return flag;
}
@Test
public void test(){
UserServiceImpL userServiceImpL = new UserServiceImpL();
System.out.println("总数"+userServiceImpL.getCount("", 0));
}
}
4.控制层借助Servlet来实现，对视图层的不同请求做出响应，实现页面的跳转，前端要展示数据的传递等
UserServlet 用户登陆
package com.xia.servlet;
import com.xia.pojo.User;
import com.xia.service.user.UserServiceImpL;
import com.xia.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 UserServlet extends HttpServlet {
//servlet控制层:调用业务层service
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("login");
//获取用户名和密码
String username = req.getParameter("userCode");
String password=req.getParameter("userPassword");
//验证，调用业务层
UserServiceImpL userServiceImpL = new UserServiceImpL();
User login = userServiceImpL.login(username, password);
if(login!=null){ //验证通过，可以登录
req.getSession().setAttribute(Constants.USER_SESSION,login);
resp.sendRedirect(req.getContextPath()+"/jsp/frame.jsp");
}
else{ //查无此人，提示错误
req.setAttribute("error","用户名或密码不正确");
req.getRequestDispatcher("login.jsp").forward(req,resp);
}
}
}
Loginout 用户注销
package com.xia.servlet;
import com.xia.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 Loginout extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//移除用户的session
req.getSession().removeAttribute(Constants.USER_SESSION);
resp.sendRedirect(req.getContextPath()+"/login.jsp");
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
super.doPost(req, resp);
}
}