SpringBoot整合MyBatis(适合初学者)
1.创建Maven工程(这里省略)
2.编写pom
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.7.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
<spring.security.version>5.1.6.RELEASE</spring.security.version>
<fastjson.version>1.2.46</fastjson.version>
</properties>
<dependencies>
<!--web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<!--连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.14</version>
</dependency>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
3. yml配置文件编写
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://localhost:3306/mango-platform?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
mybatis:
mapper-locations: classpath:com/security/mapper/*Mapper.xml
type-aliases-package: com.security.domain
- MySql8以上版本
数据源的url写法:
1)useUnicode=true&characterEncoding=utf-8,useUnicode=true(默认为true可以不写直接写字符集配置即可)时数据库采用 &后面的字符集即utf-8,useUnicode=false则使用服务器的字符集
2)useSSL=false(关闭ssl协议)
SSL协议提供服务主要:
1)认证用户服务器,确保数据发送到正确的服务器; .
2)加密数据,防止数据传输途中被窃取使用;
3)维护数据完整性,验证数据在传输过程中是否丢失;
当前支持SSL协议两层:
SSL记录协议(SSL Record Protocol):建立靠传输协议(TCP)高层协议提供数据封装、压缩、加密等基本功能支持
SSL握手协议(SSL Handshake Protocol):建立SSL记录协议用于实际数据传输始前通讯双进行身份认证、协商加密
算法、 交换加密密钥等。
3)serverTimezone=UTC (时区),
这个时区要设置好,不然会出现时差,
如果你设置serverTimezone=UTC,连接不报错,
但是我们在用java代码插入到数据库时间的时候却出现了问题。
比如在java代码里面插入的时间为:2018-06-24 17:29:56
但是在数据库里面显示的时间却为:2018-06-24 09:29:56
有了8个小时的时差
UTC代表的是全球标准时间 ,但是我们使用的时间是北京时区也就是东八区,领先UTC八个小时。
//北京时间东八区
serverTimezone=GMT%2B8
//或者使用上海时间
serverTimezone=Asia/Shanghai
4. Java代码编写
- 对应数据库表的实体类 SysUser
public class SysUser {
private Long id;
private String name;
private String nickName;
private String avatar;
private String password;
private String salt;
private String email;
private String mobile;
private Byte status;
private Long deptId;
private String createBy;
private Date createTime;
private String lastUpdateBy;
private Date lastUpdateTime;
private Byte delFlag;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName == null ? null : nickName.trim();
}
public String getAvatar() {
return avatar;
}
public void setAvatar(String avatar) {
this.avatar = avatar == null ? null : avatar.trim();
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password == null ? null : password.trim();
}
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt == null ? null : salt.trim();
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email == null ? null : email.trim();
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile == null ? null : mobile.trim();
}
public Byte getStatus() {
return status;
}
public void setStatus(Byte status) {
this.status = status;
}
public Long getDeptId() {
return deptId;
}
public void setDeptId(Long deptId) {
this.deptId = deptId;
}
public String getCreateBy() {
return createBy;
}
public void setCreateBy(String createBy) {
this.createBy = createBy == null ? null : createBy.trim();
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getLastUpdateBy() {
return lastUpdateBy;
}
public void setLastUpdateBy(String lastUpdateBy) {
this.lastUpdateBy = lastUpdateBy == null ? null : lastUpdateBy.trim();
}
public Date getLastUpdateTime() {
return lastUpdateTime;
}
public void setLastUpdateTime(Date lastUpdateTime) {
this.lastUpdateTime = lastUpdateTime;
}
public Byte getDelFlag() {
return delFlag;
}
public void setDelFlag(Byte delFlag) {
this.delFlag = delFlag;
}
}
- 数据库访问层 SysUserMapper
public interface SysUserMapper {
public List<SysUser> findAll();
}
- 业务层 SysUserService 和 SysUserServiceImpl
public interface SysUserService {
List<SysUser> findAll();
}
@Service
public class SysUserServiceImpl implements SysUserService {
@Autowired
private SysUserMapper sysUserMapper;
@Override
public List<SysUser> findAll() {
return sysUserMapper.findAll();
}
}
- 控制层 SysUserController
@RestController
@RequestMapping("/SysUser")
public class SysUserController {
@Autowired
private SysUserService sysUserService;
@GetMapping("/findAll")
public List<SysUser> findAll() {
return sysUserService.findAll();
}
}
- Mapper映射文件 SysUserMapper.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="com.security.mapper.SysUserMapper">
<select id="findAll" resultType="com.security.domain.SysUser">
SELECT * FROM sys_user
</select>
</mapper>
**
SysUserMapper.xml 映射文件在类加载路径的目录结构要和工程路径中的目录结构一致,否者调用Controller的接口时会报错。这里SysUserMapper.xml 和SysUserMapper.java 的目录结构都是 com.security.mapper
注意:resource下面的目录编写是 应该是 com/security/mapper,如果使用com.security.mapper的话idea会将com.security.mapper识别为一个目录,
启动后看target下面的目录结构和工程目录结构一致就是对的,否者就不对
**
- SpringBoot 启动类
@SpringBootApplication
@MapperScan(value = "com.security.mapper")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class,args);
}
}
- 工程结构
测试:
检查数据库数据条数:
Sql脚本
/*
Navicat Premium Data Transfer
Source Server : 本地MySql
Source Server Type : MySQL
Source Server Version : 50721
Source Host : localhost:3306
Source Schema : mango-platform
Target Server Type : MySQL
Target Server Version : 50721
File Encoding : 65001
Date: 01/05/2020 20:24:10
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
`nick_name` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
`avatar` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像',
`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
`salt` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '加密盐',
`email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
`mobile` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
`status` tinyint(4) NULL DEFAULT NULL COMMENT '状态 0:禁用 1:正常',
`dept_id` bigint(20) NULL DEFAULT NULL COMMENT '机构ID',
`create_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`last_update_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
`last_update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
`del_flag` tinyint(4) NULL DEFAULT 0 COMMENT '是否删除 -1:已删除 0:正常',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 33 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户管理' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, 'admin', '超管', NULL, 'bd1718f058d8a02468134432b8656a86', 'YzcmCZNvbXocrsz9dm8e', 'admin@qq.com', '13612345678', 1, 4, 'admin', '2018-08-14 11:11:11', 'admin', '2018-08-14 11:11:11', 0);
INSERT INTO `sys_user` VALUES (22, 'liubei', '刘备', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 7, 'admin', '2018-09-23 19:43:00', 'admin', '2019-01-10 11:41:13', 0);
INSERT INTO `sys_user` VALUES (23, 'zhaoyun', '赵云', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 7, 'admin', '2018-09-23 19:43:44', 'admin', '2018-09-23 19:43:52', 0);
INSERT INTO `sys_user` VALUES (24, 'zhugeliang', '诸葛亮', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 7, 11, 'admin', '2018-09-23 19:44:23', 'admin', '2018-09-23 19:44:29', 0);
INSERT INTO `sys_user` VALUES (25, 'caocao', '曹操', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 8, 'admin', '2018-09-23 19:45:32', 'admin', '2019-01-10 17:59:14', 0);
INSERT INTO `sys_user` VALUES (26, 'dianwei', '典韦', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 10, 'admin', '2018-09-23 19:45:48', 'admin', '2018-09-23 19:45:57', 0);
INSERT INTO `sys_user` VALUES (27, 'xiahoudun', '夏侯惇', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 8, 'admin', '2018-09-23 19:46:09', 'admin', '2018-09-23 19:46:17', 0);
INSERT INTO `sys_user` VALUES (28, 'xunyu', '荀彧', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 10, 'admin', '2018-09-23 19:46:38', 'admin', '2018-11-04 15:33:17', 0);
INSERT INTO `sys_user` VALUES (29, 'sunquan', '孙权', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 10, 'admin', '2018-09-23 19:46:54', 'admin', '2018-09-23 19:47:03', 0);
INSERT INTO `sys_user` VALUES (30, 'zhouyu', '周瑜', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 11, 'admin', '2018-09-23 19:47:28', 'admin', '2018-09-23 19:48:04', 0);
INSERT INTO `sys_user` VALUES (31, 'luxun', '陆逊', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 11, 'admin', '2018-09-23 19:47:44', 'admin', '2018-09-23 19:47:58', 0);
INSERT INTO `sys_user` VALUES (32, 'huanggai', '黄盖', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'test@qq.com', '13889700023', 1, 11, 'admin', '2018-09-23 19:48:38', 'admin', '2018-09-23 19:49:02', 0);
SET FOREIGN_KEY_CHECKS = 1;