SpringBoot整合MyBatis(实现前端)
使用MyBatis Generatot插件
系统要求
Java 8+
springBoot2.5 +
创建springBoot项目工程
导入依赖
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.4.0</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<overwrite>true</overwrite>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
查看是否有MyBatis Generator插件
编写sql
-- 创建数据库
create
database webapp1 charset utf8mb4;
-- 创建用户名、密码
create
user'webapp1'@'localhost'identified by'webapp1';
-- 授权
grant all
on webapp1.*to'webapp1'@'localhost';
-- 用用户名、密码登录
mysql -uwebapp1 -pwebapp1
-- 创建表
SET NAMES utf8mb4;
SET
FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`
(
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`sex` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL,
`birthday` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 554 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
配置application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3307/webapp1
# ?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
username: webapp1
password: webapp1
#Spring Boot 默认是不注入这些属性值的，需要自己绑定
#druid 数据源专有配置
# 初始化大小，最小，最大
initialSize: 5
minIdle: 5
maxActive: 200
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis: 300000
# 用来检测连接是否有效的sql，要求是一个查询语句
validationQuery: SELECT 1 FROM DUAL
# 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
testWhileIdle: true
# 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testOnBorrow: false
# 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn: false
# 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
poolPreparedStatements: true
# 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
max-pool-prepared-statement-per-connection-size: 50
#配置监控统计拦截的filters，stat:监控统计、log4j：日志记录、wall：防御sql注入
#如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority
#则导入 log4j 依赖即可，Maven 地址：https://mvnrepository.com/artifact/log4j/log4j
filters: stat,wall,log4j
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
mvc:
hiddenmethod:
filter:
#手动开启Restful风格
enabled: true
mybatis:
#sql映射文件的位置
# mapper-locations: classpath:com/xiang/mapper/*.xml
mapper-locations: classpath:mapper/*.xml
#开启驼峰命名转化
configuration:
map-underscore-to-camel-case: true
#开启别名
# type-aliases-package: com.xiang
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params:
count: countSql
配置generatorConfig.xml
<!DOCTYPE generatorConfiguration PUBLIC
"-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="simple" targetRuntime="MyBatis3Simple">
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3307/webapp1"
userId="webapp1" password="webapp1"/>
<!-- useSSL=true&useUnicode=true&characterEncoding=UTF-8"-->
<!-- <commentGenerator>-->
<!-- <!– 是否去除自动生成的注释 true：是 ： false:否 –>-->
<!-- <property name="suppressAllComments" value="false" />-->
<!-- </commentGenerator>-->
<javaModelGenerator targetPackage="com.xiang.model" targetProject="src/main/java"/>
<sqlMapGenerator targetPackage="com.xiang.mapper" targetProject="src/main/resources"/>
<javaClientGenerator targetPackage="com.xiang.mapper" targetProject="src/main/java" type="XMLMAPPER"/>
<table tableName="user"/>
</context>
</generatorConfiguration>
编写mapper、model 这两个包。
（只写包名就好）包下边的实体类、与接口、xml文件使用插件生成
接下来创建service、controller层
编写UserService
package com.xiang.service;
import com.xiang.model.User;
import java.util.List;
/**
* Created by IntelliJ IDEA.
* User: xiang
* Date: 2021/10/19 11:24
*/
public interface UserService {
int deleteByPrimaryKey(Integer id);
int insert(User record);
User selectByPrimaryKey(Integer id);
List<User> selectAll();
int updateByPrimaryKey(User record);
// 登录
User login(User user);
}
编写UserServiceImpl
package com.xiang.service.impl;
import com.xiang.mapper.UserMapper;
import com.xiang.model.User;
import com.xiang.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* Created by IntelliJ IDEA.
* User: xiang
* Date: 2021/10/19 11:27
*/
@Service
public class UserServiceImpl implements UserService{
@Autowired
UserMapper userMapper;
@Override
public int deleteByPrimaryKey(Integer id) {
return userMapper.deleteByPrimaryKey(id);
}
@Override
public int insert(User record) {
return userMapper.insert(record);
}
@Override
public User selectByPrimaryKey(Integer id) {
return userMapper.selectByPrimaryKey(id);
}
@Override
public List<User> selectAll() {
return userMapper.selectAll();
}
@Override
public int updateByPrimaryKey(User record) {
return userMapper.updateByPrimaryKey(record);
}
@Override
public User login(User user) {
return userMapper.login(user);
}
}
编写LoginController
package com.xiang.controller;
import com.xiang.model.User;
import com.xiang.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
/**
* Created by IntelliJ IDEA.
* User: xiang
* Date: 2021/10/19 21:31
*/
@Controller
public class LoginController {
@Autowired
UserService userService;
@GetMapping("/")
public String index() {
return "index2";
}
@GetMapping("/login")
public String login(User user, Model model, HttpSession session) {
User login = userService.login(user);
if (login == null) {
//登录失败，id或用户名错误
model.addAttribute("msg", "登录失败，id或用户名错误");
return "index2";
} else {
session.setAttribute("login", login);
return "forward:/findAll";
}
}
//注销
@RequestMapping(value = "/logout")
public String getLogout(HttpServletRequest request) {
HttpSession session = request.getSession();
if (session != null) {
session.removeAttribute("login");
}
return "index2";
}
}
编写UserController
package com.xiang.controller;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.xiang.model.User;
import com.xiang.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import java.util.List;
/**
* Created by IntelliJ IDEA.
* User: xiang
* Date: 2021/10/19 21:45
*/
@Controller
public class UserController {
@Autowired
UserService userService;
// @GetMapping("/findAll")
// public String findAll(Model model) {
// List<User> list = userService.selectAll();
// model.addAttribute("list", list);
// return "list";
// }
@GetMapping("/findAll")
public Object list(@RequestParam(value = "page", defaultValue = "1") Integer page, Model model) {
//获取指定页数据，每页显示10条数据
PageHelper.startPage(page, 6);
//紧跟的第一个select方法被分页
List<User> list = userService.selectAll();
model.addAttribute("list", list);
//使用PageInfo包装数据 navigatePages表示导航标签的数量
PageInfo pageInfo = new PageInfo(list, 3);
model.addAttribute("pageInfo", pageInfo);
return "list";
}
@GetMapping("/addByUser")
public String addByUser() {
return "addByUser";
}
@GetMapping("/addUser")
public String addUser(User user, Model model) {
userService.insert(user);
List<User> list = userService.selectAll();
model.addAttribute("list", list);
return "forward:/findAll";
// modelAndView.addObject("list", list);
// modelAndView.setViewName("list");
// return modelAndView;
}
@GetMapping("/updateById/{id}")
public String findById(@PathVariable("id") int id, Model model) {
User user = userService.selectByPrimaryKey(id);
model.addAttribute("user", user);
return "updateUser";
}
@GetMapping("/updateUser")
public Object updateUser(User user, Model model) {
userService.updateByPrimaryKey(user);
List<User> list = userService.selectAll();
model.addAttribute("list", list);
return "forward:/findAll";
// modelAndView.addObject("list", list);
// modelAndView.setViewName("list");
// return modelAndView;
// return "forward:/list";
}
@GetMapping("/deleteById/{id}")
public Object deleteById(@PathVariable("id") int id, Model model) {
userService.deleteByPrimaryKey(id);
List<User> list = userService.selectAll();
model.addAttribute("list", list);
// modelAndView.addObject("list", list);
// modelAndView.setViewName("list");
// return modelAndView;
return "redirect:/findAll";
}
// @GetMapping("/list")
// @ResponseBody
// public String deleteById2() {
// return "帅哥";
// }
}
接下来是前端页面
编写index2.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="/resource/style.css" media="all"/>
</head>
<body>
<div class="loginBox">
<img src="/resource/user.png" class="user">
<h2>登陆</h2>
<form th:action="@{/login}" method="get">
<div>
<p>ID</p>
<input type="text" id="id" name="id" placeholder="id">
</div>
<div>
<p>userName</p>
<input type="text" id="username" name="username" placeholder="username">
</div>
<h4 style="color: red" th:text="${msg}"></h4>
<input type="submit"></input>
</form>
</div>
</body>
</html>
编写list.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org" xmlns:fmt="">
<head>
<meta charset="UTF-8">
<title>list</title>
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/popper.js/1.15.0/umd/popper.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
<!-- Image and text -->
<div class="container">
<nav class="navbar navbar-light "style="background-color:hotpink;">
<a class="h2 ">用户管理页面</a>
<form class="form-inline">
<input class="form-control mr-sm-2" type="search" placeholder="Search" aria-label="Search">
<button class="btn btn-outline-success my-2 my-sm-0" type="submit">Search</button>
</form>
</nav>
<h4>当前用户：
[[${session.login == null ? "" : session.login.username+"欢迎您"}]]
</h4>
<div style="margin: 5px; float: right;">
<a type="button" class="btn btn-primary" role="button" th:href="@{/addByUser}">添加用户</a>
<a type="button" class="btn btn-info" role="button" th:href="@{/logout}">注销用户</a>
</div>
<table class="table table-striped">
<tr>
<th>序号</th>
<!-- <th >编号</th>-->
<th>姓名</th>
<th>性别</th>
<th>年龄</th>
<th>生日</th>
<th>操作</th>
</tr>
<!--
private Integer id;
private String username;
private String sex;
private Integer age;
private Date birthday;
-->
<tr th:each="lists,stats:${list}">
<td th:text="${stats.count}"></td>
<!-- <td th:text="${lists.getId()}"></td>-->
<td th:text="${lists.getUsername()}"></td>
<td th:text="${lists.getSex()}"></td>
<td th:text="${lists.getAge()}"></td>
<!-- <td th:text="${user.getBirthday()}"></td>-->
<!-- <td>-->
<!-- <fmt:formatDate value="${user.getBirthday()}" type="date"/>-->
<!-- </td>-->
<td th:text="${#dates.format(lists.getBirthday(),'yyyy-MM-dd')}"></td>
<td>
<!--@{/queryUserById/{id}(id=${user.getId()})}:路径后可以跟参数，之后需要用括号说明参数的值是什么-->
<a role="button" class="btn btn-warning" th:href="@{/updateById/{id}(id=${lists.getId()})}">修改</a>
<a role="button" class="btn btn-danger" th:href="@{/deleteById/{id}(id=${lists.getId()})}">删除</a>
</td>
</tr>
</table>
</div>
<!--分页-->
<div align="center">
<span>
<a th:href="@{|findAll?page=1|}">首页</a>
</span>
<span th:if="${pageInfo.hasPreviousPage==true}">
<a th:href="@{|findAll?page=${pageInfo.pageNum-1}|}">上一页</a>
</span>
<span th:each="page:${pageInfo.navigatepageNums}">
<a th:href="@{|findAll?page=${page}|}" th:text="${page}"></a>
</span>
<span th:if="${pageInfo.hasNextPage==true}">
<a th:href="@{|findAll?page=${pageInfo.pageNum+1}|}">下一页</a>
</span>
<span>
<a th:href="@{|findAll?page=${pageInfo.pages}|}">末页</a>
</span>
当前页:第<span th:text="${pageInfo.pageNum}"></span>/<span th:text="${pageInfo.pages}"></span>页
</div>
</body>
</html>
编写addByUser.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/popper.js/1.15.0/umd/popper.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
<!--
private Integer id;
private String username;
private String sex;
private Integer age;
private Date birthday;
-->
<div style=" 500px;height: 1200px;margin:100px auto">
<form action="/addUser" class="form-horizontal" >
<div class="form-group">
<label for="username">姓名</label>
<input type="text" class="form-control" id="username" name="username" placeholder="username">
</div>
<div class="form-group">
<label for="sex">性别</label>
<input type="text" class="form-control" id="sex" name="sex" placeholder="sex">
</div>
<div class="form-group">
<label for="age">年龄</label>
<input type="text" class="form-control" id="age" name="age" placeholder="age">
</div>
<div class="form-group">
<label for="birthday">生日</label>
<input type="date" class="form-control" id="birthday" name="birthday" placeholder="birthday">
</div>
<button type="submit" class="btn btn-success">确认添加</button>
<input type="button" onclick="history.go(-1)" class="btn btn-warning" value="取消添加"/>
</form>
</div>
</body>
</html>
编写updateUser.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>update</title>
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/popper.js/1.15.0/umd/popper.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
<div style=" 500px;height: 1200px;margin:100px auto">
<form th:action="@{/updateUser}" class="form-horizontal">
<input type="hidden" name="id" th:value="${user.getId()}">
<div class="form-group">
<label for="username">姓名</label>
<input type="text" class="form-control" id="username" name="username"
th:value="${user.getUsername()}">
</div>
<div class="form-group">
<label for="sex">性别</label>
<input type="text" class="form-control" id="sex" name="sex" th:value="${user.getSex()}">
</div>
<div class="form-group">
<label for="age">年龄</label>
<input type="text" class="form-control" id="age" name="age" th:value="${user.getAge()}">
</div>
<div class="form-group">
<label for="birthday">生日</label>
<input type="date" class="form-control" id="birthday" name="birthday"
th:value="${#dates.format(user.birthday, 'yyyy-MM-dd')}">
</div>
<!-- th:value="${user.getBirthday()}">-->
<button type="submit" class="btn btn-success">确认修改</button>
<input type="button" onclick="history.go(-1)" class="btn btn-warning" value="取消修改"/>
</form>
</div>
</body>
</html>
前端运行
登录页style.css
body{
margin: 0;
padding: 0;
background: url(photo.jpg);
background-size: cover;
font-family: sans-serif;
}
.loginBox{
position: absolute;
top: 50%;
left: 50%;
transform: translate(-50%,-50%);
350px;
height: 420px;
padding: 80px 40px;
box-sizing: border-box;
background: rgba(0, 0, 0, 0.5);
}
.user{
100px;
height: 100px;
border-radius: 50%;
overflow: hidden;
position: absolute;
top: calc(-100px/2);
/*left: calc(50% -50px);*/
}
h2{
margin: 0;
padding: 0 0 20px;
color: #32cd32;
text-align: center;
}
.loginBox p{
margin: 0;
padding: 0;
font-weight: bold;
color: #fff;
}
.loginBox input{
100%;
margin-bottom: 20px;
}
.loginBox input[type="text"],
.loginBox input[type="password"]{
border: none;
border-bottom: 1px solid #fff;
background: transparent;
outline: none;
height: 40px;
color: #fff;
font-size: 16px;
}
::placeholder{
color: rgba(255, 255, 255, 0.5);
}
.loginBox input[type="submit"]{
border: none;
outline: none;
height: 40px;
color: #ffffff;
font-size: 16px;
background: #ff6347;
cursor: pointer;
border-radius: 20px;
}
.loginBox input[type="submit"]:hover{
background: #32cd32;
color: #ffffff;
}
.loginBox a{
color: #fff;
font-size: 14px;
font-weight: bold;
text-decoration: none;
}
登录页
list页面
add页面
update页面
