数据库增删改查接口实现
首先,我们基于mabatis+redis+mysql实现一个user类数据库增删改查的基本功能。
实体类bean.User实现 Serializable 接口,因为 Spring 会将对象先序列化再存入 Redis
package com.example.demospringboot.bean;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@NoArgsConstructor
public class User implements Serializable {
private int id;
private String username;
private String password;
}
dao层,定义UserMapper接口:
package com.example.demospringboot.dao;
import com.example.demospringboot.bean.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.cache.annotation.CachePut;
import org.springframework.stereotype.Repository;
import java.util.List;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;
@Repository
@Cacheable
@CachePut
@CacheConfig(cacheNames = "users")
public interface UserMapper {
User findUserById(@Param("id") int id);
User findUserByName(@Param("username") String username);
String getUserRole(@Param("id") int id);
List<User> findAllUsers();
@CachePut // 更新缓存
void deleteUserById(@Param("id") int id);
@CachePut
void deleteAllUsers();
@CachePut
int insertUser(@Param("user") User user);
@CachePut
void updateUserPassword(@Param("user") User user);
}
对应的demospringboot\src\main\resources\mybatis\UserMapper.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- mapper标签要指定namespace属性,和实际的mapper文件一致-->
<mapper namespace="com.example.demospringboot.dao.UserMapper">
<select id="findUserById" resultType="com.example.demospringboot.bean.User">
select * from user where id = #{id}
</select>
<select id="findUserByName" resultType="com.example.demospringboot.bean.User">
select * from user where username = #{username}
</select>
<select id="getUserRole" parameterType="int" resultType="String">
SELECT
NAME AS role
FROM
USER u
LEFT JOIN user_role ur ON id = ur.user_id
LEFT JOIN role r ON r.id = ur.role_id
WHERE u.id = #{id}
</select>
<select id="findAllUsers" resultType="com.example.demospringboot.bean.User">
select * from user
</select>
<delete id="deleteAllUsers" >
delete from user
</delete>
<delete id="deleteUserById" parameterType="int">
delete from user where id=#{id}
</delete>
<insert id="insertUser" parameterType="com.example.demospringboot.bean.User">
insert into user(id,username,password) values(#{user.id},#{user.username},#{user.password})
</insert>
<update id="updateUserPassword" parameterType="com.example.demospringboot.bean.User">
update user set password=#{user.password} where id=#{user.id}
</update>
</mapper>
同样地,我们为job也分别创建bean、dao、xml:
bean:
package com.example.demospringboot.bean;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
public class Job {
private int id;
private int sendmail;
private int analysisLog;
}
DAO:
package com.example.demospringboot.dao;
import org.apache.ibatis.annotations.Param;
public interface JobMapper {
int getSendmail();
void setSendmail(@Param("cnt") int cnt);
int getAnalysisLog();
void setAnalysisLog(@Param("cnt") int cnt);
}
xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- mapper标签要指定namespace属性,和实际的mapper文件一致-->
<mapper namespace="com.example.demospringboot.dao.JobMapper">
<select id="getSendmail" resultType="Integer">
select sendMail from jobs
</select>
<update id="setSendmail" parameterType="Integer">
update jobs set sendMail=#{cnt}
</update>
<select id="getAnalysisLog" resultType="Integer">
select analysisLog from jobs
</select>
<select id="setAnalysisLog" resultType="Integer">
update jobs set analysisLog=#{cnt}
</select>
</mapper>
主启动类:
package com.example.demospringboot;
import lombok.extern.slf4j.Slf4j;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.cache.annotation.EnableCaching;
import com.example.demospringboot.workmanager.WorkManager;
import com.example.demospringboot.workmanager.AsyncWorkManager;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import com.spring4all.swagger.EnableSwagger2Doc;
@Slf4j
@EnableCaching
@EnableAsync
@SpringBootApplication
@EnableSwagger2Doc
// 需要指定扫描的类,并在配置文件指定mybatis.mapper-locations为对应的xml路径
@MapperScan(value = {"com.example.demospringboot.dao"})
public class DemospringbootApplication implements CommandLineRunner {
@Autowired
private WorkManager workManager;
@Autowired
private AsyncWorkManager asyncWorkManager;
@Autowired
private DataSource dataSource;
public static void main(String[] args) {
SpringApplication.run(DemospringbootApplication.class, args);
}
@Override
public void run(String... strings) throws SQLException {
initDatabase();
}
private void initDatabase() throws SQLException {
log.info("======== 自动初始化数据库开始 ========");
Resource initData = new ClassPathResource("schema.sql");
Connection connection = null;
try {
connection = dataSource.getConnection();
ScriptUtils.executeSqlScript(connection, initData);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (connection != null) {
connection.close();
}
}
log.info("======== 自动初始化数据库结束 ========");
}
}
Web页面接口实现
我们基于springboot内嵌的tomcat,用Thymeleaf 模版实现一个存储用户账号密码的web界面,效果如下:
登录功能:拿到前端传来的帐号和密码后,去数据库验证,验证成功则返回登录成功页面,并将当前登录用户的Session信息保存下来;失败则拒绝登录;
注册功能:返回注册页面,将注册信息存入数据库, 并设置发送邮件标志位为1
在UsreController中设计接口如下:
接口 | 作用 |
---|---|
@GetMapping(value = {“/register”}) public String registerPage() | 返回注册界面html |
@GetMapping(value = {“/login”}) public String loginPage() | 返回登录界面html |
@GetMapping(value = {“/success”}) public String UserPage(HttpSession session, Model model) | 返回登录以后的界面html |
@PostMapping(“register”) public String Register(User user, Model model) | 提交注册信息进行注册 |
@PostMapping(“login”) public String login(User user, HttpSession session, Model model) | 提交登录信息进行登录 |
对应的3个html放置在demospringboot\src\main\resources\templates\目录下:
success.html
登录成功后的页面,如果是管理员,显示所有用户list
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Thymeleaf Spring Boot Example</title>
</head>
<body>
<h1 th:text="'Welcome, '+${user}+'!'"></h1>
<a>You have successfully logged in !</a>
<h1 th:text="用户列表"></h1>
<a th:text="'userList: '+${users}"></a>
</body>
</html>
login.html
访问http://localhost:8080/login时返回login.html页面。
点击登录按钮,通过html的form表单method="post" th:action="@{/login}
跳到Controller的@PostMapping("login")
,查询成功后重定向到@GetMapping("success")
,返回success.html
点击注册按钮,通过html的form表单method="get" th:action="@{/register}
跳到Controller的@GetMapping("register")
,返回registe.html页面。
<!DOCTYPE html >
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>登录注册界面</title>
<link rel="stylesheet" href="../static/style.css">
</head>
<body>
<!-- 整体布局 -->
<div class="container right-panel-active">
<!-- 登录框 -->
<div class="container_from container_signin">
<form class="form" id="form" method="post" th:action="@{/login}">
<h2 class="form_title">欢迎登录</h2>
<div class="row">
<span>用户名:</span>
<input type="text" name="username" placeholder="请输入您的账号" class="input">
</div>
<div class="row">
<span>密 码:</span>
<input type="password" name="password" placeholder="请输入您的密码" class="input">
</div>
<div class="row">
<span th:text="${msg}"></span>
</div>
<input type="submit" class="btn" value="登录"/>
</form>
<form class="form" method="get" id="form1" th:action="@{/register}">
<label id="register" class="form-label" >没有账号?请点击
<input class="btn" type="submit" value="注册"/>
</form>
</div>
</div>
<script src="../static/login.js"></script>
</body>
</html>
register.html
填入信息后点击注册,通过html的form表单method="post" th:action="@{/register}
跳到Controller的@PostMapping("register")
,进行注册
<!DOCTYPE html >
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>登录注册界面</title>
<link rel="stylesheet" href="../static/style.css">
</head>
<body>
<!-- 整体布局 -->
<div class="container right-panel-active">
<!-- 注册框 -->
<div class="container_from container_signup">
<form class="from" method="post" id="from" th:action="@{/register}">
<h2 class="form_title">注册账号</h2>
<div class="row">
<span>用户名:</span>
<input type="text" id="username" name="username" placeholder="请输入账号" class="input">
</div>
<div class="row">
<span>密 码:</span>
<input type="password" name="password" placeholder="请输入密码" class="input">
</div>
<!-- 提示注册信息${tip} -->
<div class="row">
<span th:text="${tip}"></span>
</div>
<input class="btn" type="submit" value="注册"/>
</form>
</div>
</div>
<script src="../static/login.js"></script>
</body>
</html>
实现userController:
package com.example.demospringboot.controller;
import com.example.demospringboot.bean.User;
import com.example.demospringboot.dao.JobMapper;
import com.example.demospringboot.dao.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.ui.Model;
import javax.servlet.http.HttpSession;
import java.util.List;
@Controller
public class UserController {
@Autowired
UserMapper userMapper;
@Autowired
JobMapper jobMapper;
@GetMapping(value = {"/login"})
public String loginPage() {
// 返回login.html
return "login";
}
@GetMapping("register")
public String registerPage() {
// 返回register.html
return "register";
}
@PostMapping("register")
public String Register(User user, Model model) {
try {
User userName = userMapper.findUserByName(user.getUsername());
//没有用户可以进行注册
if (userName == null) {
if (user.getPassword().equals("") || user.getUsername().equals("")) {
model.addAttribute("tip", "请填写信息");
return "register";
} else {
int ret = userMapper.insertUser(user);
if (ret > 0) {
model.addAttribute("tip", "注册成功,请返回登录页面进行登录");
// 设置发送邮件标志位为1
jobMapper.setSendmail(1);
}
return "register";
}
} else {
model.addAttribute("tip", "用户已存在,请返回登录页面进行登录");
return "register";
}
} catch (Exception e) {
e.printStackTrace();
return e.getMessage();
}
}
@PostMapping("login")
public String login(User user, HttpSession session, Model model) {
try {
//先查找一下有没有该账号
User userReturn = userMapper.findUserByName(user.getUsername());
if (userReturn != null) {
//如果有账号则判断账号密码是否正确
if (userReturn.getPassword().equals(user.getPassword())) {
//添加到session保存起来
System.out.println(session);
session.setAttribute("loginUser", user);
//重定向到@GetMapping("success")
return "redirect:/success";
} else {
//如果密码错误,则提示输入有误
model.addAttribute("msg", "账号或者密码有误");
return "login";
}
} else {
model.addAttribute("msg", "账号或者密码有误");
return "login";
}
} catch (Exception e) {
e.printStackTrace();
return e.getMessage();
}
}
@GetMapping("success")
public String UserPage(HttpSession session, Model model) {
User loginUser = (User)session.getAttribute("loginUser");
if (loginUser != null) {
model.addAttribute("user", loginUser.getUsername());
// 如果是管理员,返回用户列表
User user = userMapper.findUserByName(loginUser.getUsername());
if ("admin".equals(userMapper.getUserRole(user.getId()))) {
List<User> users = userMapper.findAllUsers();
model.addAttribute("users", users);
}
// 返回success.html
return "success";
} else {
model.addAttribute("msg", "请登录");
return "login";
}
}
}
访问http://localhost:8080/login
进行测试即可。