【JavaWeb】日程管理系统 项目搭建 第二期

---

一、数据库准备

创建数据库：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- 创建日程表
-- ----------------------------
DROP TABLE IF EXISTS `sys_schedule`;
CREATE TABLE `sys_schedule`  (
  `sid` int NOT NULL AUTO_INCREMENT,
  `uid` int NULL DEFAULT NULL,
  `title` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `completed` int(1) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 插入日程数据
-- ----------------------------

-- ----------------------------
-- 创建用户表
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `uid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `user_pwd` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 插入用户数据
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, 'zhangsan', 'e10adc3949ba59abbe56e057f20f883e');
INSERT INTO `sys_user` VALUES (2, 'lisi', 'e10adc3949ba59abbe56e057f20f883e');

SET FOREIGN_KEY_CHECKS = 1;

二、导入依赖 与 JDBC工具类

使用lombok处理 getter setter equals hashcode 构造器

导入JDBCUtil连接池工具类并准备jdbc.properties配置文件

package com.doug.wake.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @Description: JDBCUtil连接池工具类
 */
public class JDBCUtil {
    private static ThreadLocal<Connection> threadLocal =new ThreadLocal<>();

    private static DataSource dataSource;
    // 初始化连接池
    static{
        // 可以帮助我们读取.properties配置文件
        Properties properties =new Properties();
        InputStream resourceAsStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    /*1 向外提供连接池的方法*/
    public static DataSource getDataSource(){
        return dataSource;
    }

    /*2 向外提供连接的方法*/
    public static Connection getConnection(){
        Connection connection = threadLocal.get();
        if (null == connection) {
            try {
                connection = dataSource.getConnection();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            threadLocal.set(connection);
        }

        return connection;
    }

    /*定义一个归还连接的方法 (解除和ThreadLocal之间的关联关系) */
    public static void releaseConnection(){
        Connection connection = threadLocal.get();
        if (null != connection) {
            threadLocal.remove();
            // 把连接设置回自动提交的连接
            try {
                connection.setAutoCommit(true);
                // 自动归还到连接池
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/schedule_system
username=root
password=root
initialSize=5
maxActive=10
maxWait=1000

三、pojo包处理

实体类：

使用lombok处理getter setter equals hashcode 构造器

implements Serializable : 习惯上要实现序列化接口，与缓存、分布式项目数据传递有关 将对象序列化 防止报错

@AllArgsConstructor // 全参构造
@NoArgsConstructor // 添加无参构造
@Data         //getter setter equals hashcode toString
public class SysUser implements Serializable {
    private int uid;
    private String username;
    private String userPwd;
}

@AllArgsConstructor
@NoArgsConstructor
@Data
public class SysSchedule {
    private int sid;
    private int uid;
    private String title;
    private int completed;
}

四、dao

Data access Object 数据访问对象
该类中用于定义针对数据库表格的CRUD的方法
DAO层一般需要定义接口和实现类

dao包工具类

package com.doug.wake.dao;

import com.doug.wake.util.JDBCUtil;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description: Dao工具类
 */
public class BaseDao {
    // 公共的查询方法  返回的是单个对象
    public <T> T baseQueryObject(Class<T> clazz, String sql, Object ... args) {
        T t = null;
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int rows = 0;
        try {
            // 准备语句对象
            preparedStatement = connection.prepareStatement(sql);
            // 设置语句上的参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            // 执行 查询
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                t = (T) resultSet.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            JDBCUtil.releaseConnection();
        }
        return t;
    }
    // 公共的查询方法  返回的是对象的集合

    public <T> List<T> baseQuery(Class clazz, String sql, Object ... args){
        List<T> list =new ArrayList<>();
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet =null;
        int rows = 0;
        try {
            // 准备语句对象
            preparedStatement = connection.prepareStatement(sql);
            // 设置语句上的参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }

            // 执行 查询
            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 将结果集通过反射封装成实体类对象
            while (resultSet.next()) {
                // 使用反射实例化对象
                Object obj =clazz.getDeclaredConstructor().newInstance();

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(columnName);
                    // 处理datetime类型字段和java.util.Data转换问题
                    if(value.getClass().equals(LocalDateTime.class)){
                        value= Timestamp.valueOf((LocalDateTime) value);
                    }
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(obj,value);
                }

                list.add((T)obj);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null !=resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            JDBCUtil.releaseConnection();
        }
        return list;
    }

    // 通用的增删改方法
    public int baseUpdate(String sql,Object ... args) {
        // 获取连接
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        int rows = 0;
        try {
            // 准备语句对象
            preparedStatement = connection.prepareStatement(sql);
            // 设置语句上的参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }

            // 执行 增删改 executeUpdate
            rows = preparedStatement.executeUpdate();
            // 释放资源(可选)


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }
            JDBCUtil.releaseConnection();
        }
        // 返回的是影响数据库记录数
        return rows;
    }
}

基本按照这样写：

• Dao 接口

/**
 * @Description: 日程表CRUD
 */
public interface SysScheduleDao {
    /**
     * 用于向数据中增加一条日程记录
     * @param schedule 日程数据以SysSchedule实体类对象形式入参
     * @return 返回影响数据库记录的行数，行数为0增加失败，行数大于0增加成功
     */
    int addSchedule(SysSchedule schedule);

    /**
     * 查找所有的日程信息
     * @return 返回全部日程数据
     */
    List<SysSchedule> findSchedule();
}

public interface SysUserDao {
    /**
     * 向数据库表sys_user增加一条用户记录的方法
     * @param sysUser 要增加记录的username和usePwd 以SysUser对象的形式接收
     * @return 增加 成功返回 1， 失败0
     */
    int addUser(SysUser sysUser);
}

• Dao实现类：

public class SysScheduleDaoImpl extends BaseDao implements SysScheduleDao {
    @Override
    public int addSchedule(SysSchedule schedule) {
        String sql = "insert into sys_schedule values(DEFAULT,?,?,?)";
        int rows = baseUpdate(sql, schedule.getUid(),schedule.getTitle(), schedule.getCompleted());
        return rows;
    }

    @Override
    public List<SysSchedule> findSchedule() {
        String sql = "select sid,uid,title,completed from sys_schedule";
        List<SysSchedule> scheduleList = baseQuery(SysSchedule.class, sql);
        return scheduleList;
    }
}

public class SysUserDaoImpl extends BaseDao implements SysUserDao {
    @Override
    public int addUser(SysUser sysUser) {
        String sql = "insert into sys_user values(DEFAULT,?,?);";
        int rows = baseUpdate(sql, sysUser.getUsername(), sysUser.getUserPwd());
        return rows;
    }
}

五、service

这样写： 写注释

/**
 * 该接口定义了以 sys_user 表 为核心的业务处理功能
 */
public interface SysUserService {

    /**
     * 用户注册方法
     * @param sysUser 要注册的用户名和明文密码 以SysUser对象的形式接收
     * @return 注册 成功返回1 ， 失败返回0
     */
    int regist(SysUser sysUser);
}

public class SysUserServiceImpl implements SysUserService {
    private SysUserDao userDao = new SysUserDaoImpl();

    @Override
    public int regist(SysUser sysUser) {
        //密码加密
        sysUser.setUserPwd(MD5Util.encrypt(sysUser.getUserPwd()));
        return userDao.addUser(sysUser);
    }
}

六、controller

前端 action 格式： servlet 的名字 + 方法名
user/add
user/remove …

• 接收前端传来的URI【 /user/add 】截取后面的add 再定义同名方法，
• 这样就自动invoke执行相同名字的方法
• 不用自己手动去写ifelse( 加一个方法就得写一个
  

servlet 基类 反射

/**
 * @Description: Controller servlet基类 使用反射调用方法
 */
public class BaseController extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //拿到方法的URI 格式：项目上下文Context 加上 后缀名 
        //举例项目Context设置为空时： /schedule/add
        String requestURI = req.getRequestURI();
        //拿到最后的 add 方法名
        String[] split = requestURI.split("/");
        String methodName = split[split.length - 1];
        //使用反射 获取所有的方法
        Class aClass = this.getClass();
        try {
            Method declaredMethod = aClass.getDeclaredMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
            //取消访问检查 防止方法为私有导致不能执行
            declaredMethod.setAccessible(true);
            //执行方法 传入参数
            declaredMethod.invoke(this, req, resp);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

这样只要 继承基类 定义 方法即可

@WebServlet("/schedule/*")
public class SysScheduleController extends BaseController{
    protected void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("add");
    }
}

@WebServlet("/user/*")
public class SysUserController extends BaseController{
    private SysUserService userService = new SysUserServiceImpl();

    /**
     * 接收用户注册的业务请求 处理方法
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    protected void regist(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String userPwd = req.getParameter("userPwd");
        SysUser sysUser = new SysUser(null, username, userPwd);
        int rows =  userService.regist(sysUser);
        if (rows > 0){
            resp.sendRedirect("/registSuccess.html");
        }else{
            resp.sendRedirect("/registFail.html");
        }
    }
}

七、加密工具类 MD5

public class MD5Util {
    public static String encrypt(String strSrc) {
        try {
            char hexChars[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8',
                    '9', 'a', 'b', 'c', 'd', 'e', 'f' };
            byte[] bytes = strSrc.getBytes();
            MessageDigest md = MessageDigest.getInstance("MD5");
            md.update(bytes);
            bytes = md.digest();
            int j = bytes.length;
            char[] chars = new char[j * 2];
            int k = 0;
            for (int i = 0; i < bytes.length; i++) {
                byte b = bytes[i];
                chars[k++] = hexChars[b >>> 4 & 0xf];
                chars[k++] = hexChars[b & 0xf];
            }
            return new String(chars);
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
            throw new RuntimeException("MD5加密出错!!!");
        }
    }
}

八、页面文件

注册页面：

登录页面

九、业务代码

9.1 注册业务处理

controller （servlet）

@WebServlet("/user/*")
public class SysUserController extends BaseController{
    private SysUserService userService = new SysUserServiceImpl();

    /**
     * 接收用户注册的业务请求 处理方法
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    protected void regist(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String userPwd = req.getParameter("userPwd");
        SysUser sysUser = new SysUser(null, username, userPwd);
        int rows =  userService.regist(sysUser);
        if (rows > 0){
            resp.sendRedirect("/registSuccess.html");
        }else{
            resp.sendRedirect("/registFail.html");
        }
    }
}

service 业务代码

/**
 * 该接口定义了以 sys_user 表 为核心的业务处理功能
 */
public interface SysUserService {

    /**
     * 用户注册方法
     * @param sysUser 要注册的用户名和明文密码 以SysUser对象的形式接收
     * @return 注册 成功返回1 ， 失败返回0
     */
    int regist(SysUser sysUser);
}

public class SysUserServiceImpl implements SysUserService {
    private SysUserDao userDao = new SysUserDaoImpl();

    @Override
    public int regist(SysUser sysUser) {
        //密码加密
        sysUser.setUserPwd(MD5Util.encrypt(sysUser.getUserPwd()));
        return userDao.addUser(sysUser);
    }
}

dao 数据库

/**
 * @Description: 用户CRUD
 */
public interface SysUserDao {
    /**
     * 向数据库表sys_user增加一条用户记录的方法
     * @param sysUser 要增加记录的username和usePwd 以SysUser对象的形式接收
     * @return 增加 成功返回 1， 失败0
     */
    int addUser(SysUser sysUser);
}

public class SysUserDaoImpl extends BaseDao implements SysUserDao {
    @Override
    public int addUser(SysUser sysUser) {
        String sql = "insert into sys_user values(DEFAULT,?,?);";
        int rows = baseUpdate(sql, sysUser.getUsername(), sysUser.getUserPwd());
        return rows;
    }
}

9.2 登录业务处理

controller :

 /**
     * 接收用户登录请求，完成登录业务接口
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    protected void login(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String userPwd = req.getParameter("userPwd");
        //通过用户名查找对象
        SysUser loginUser = userService.findUserByUsername(username);
        //用户为Null不存在
        if(null == loginUser){
            resp.sendRedirect("/loginUsernameError.html");
            //密码不匹配
        } else if (!MD5Util.encrypt(userPwd).equals(loginUser.getUserPwd())) {
            resp.sendRedirect("/loginUserPwdError.html");
        }else{
            resp.sendRedirect("/showSchedule.html");
        }
    }

service

    /**
     * 根据用户名查找完整的user对象
     * @param username 要查找的用户名
     * @return 查到返回SysUser对象，找不到返回Null
     */
    SysUser findUserByUsername(String username);

    @Override
    public SysUser findUserByUsername(String username) {
        return userDao.findUserByUsername(username);
    }

dao

    /**
     * 根据用户名查找完整的user对象
     * @param username 要查找的用户名
     * @return 查到返回SysUser对象，找不到返回Null
     */
    SysUser findUserByUsername(String username);

    @Override
    public SysUser findUserByUsername(String username) {
        String sql = "select uid,username,user_pwd as userPwd from sys_user where username=?;";
        List<SysUser> sysUserList = baseQuery(SysUser.class, sql, username);
        return sysUserList != null && sysUserList.size() > 0 ? sysUserList.get(0) : null;
    }

---

总结

开启注解处理：

测试类：
测试日程表的CRUD 调用DAO层方法

public class TestSysScheduleDao {
    private static SysScheduleDao sysScheduleDao;

    @BeforeClass
    public static void initSysScheduleDaoImpl(){
        sysScheduleDao = new SysScheduleDaoImpl();
    }

    @Test
    public void testAddSchedule(){
        int rows = sysScheduleDao.addSchedule(new SysSchedule(null, 2, "学习后端", 0));
        System.out.println(rows);
    }

    @Test
    public void testFindSchedule(){
        List<SysSchedule> scheduleList = sysScheduleDao.findSchedule();
        scheduleList.forEach(System.out::println);
    }
}

测试BaseDao工具类：

    private static BaseDao baseDao;

    @BeforeClass
    public static void initBaseDao(){
        baseDao = new BaseDao();
    }

    @Test
    public void testBaseUpdate(){
        String sql = "insert into sys_schedule values(DEFAULT,?,?,?)";
        int rows = baseDao.baseUpdate(sql, 1, "学习JS", 1);
        System.out.println(rows);
    }

    @Test
    public void testBaseQuery(){
        String sql = "select sid,uid,title,completed from sys_schedule";
        List<SysSchedule> listSchedules = baseDao.baseQuery(SysSchedule.class, sql);
        listSchedules.forEach(System.out::println);
    }

    @Test
    public void testBaseSingleQuery(){
        String sql = "select count(1) from sys_schedule";
        Long aLong = baseDao.baseQueryObject(Long.class, sql);
        System.out.println(aLong);
    }