文章目录
- 一、数据库准备
- 二、导入依赖 与 JDBC工具类
- 三、pojo包处理
- 四、dao
- dao包工具类
- 五、service
- 六、controller
- servlet 基类 反射
- 七、加密工具类 MD5
- 八、页面文件
- 九、业务代码
- 9.1 注册业务处理
- 9.2 登录业务处理
- 总结
一、数据库准备
创建数据库:
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);
}