MyBatis
一、SpringBoot中集成Mybatis快速入门
以下测试均在SpringBoot环境下进行学习,也可按照官网步骤,但配置略有繁琐:Mybatis官网
第一步:导入Maven依赖
<!--Mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>
<!--Mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
第二步:配置application.yml
-
mybatis.mapper-locations
的作用是:实现mapper接口和mapper接口配置文件的绑定。如果mapper接口和mapper接口对应的配置文件 命名上相同 and 所在路径相同 ,那么mybatis.mapper-locations可以不用配置,配置了也不生效;反之如果任意一个条件没达到,只能通过配置mybatis.mapper-locations才能实现接口的绑定( 参考博客) -
mybatis.type-aliases-package
的作用是:在mapper接口配置文件中,resultMap / resultType / parameterType填入自定义的pojo时,不再需要完全限定名来指定PoJo的引用,例如:
<select id="getAllStudent" resultType="com.pojo.Student">
.......
</select>
<!-- 可以写成 👇👇👇(省略了com.pojo)-->
<select id="getAllStudent" resultType="Student">
.......
</select>
- 其中,mapper.xml相关文件建议放在resources目录下, 编译后也能在target目录中看到mapper.xml文件;
############################################################
#
# web访问端口号 约定:8888
#
############################################################
server:
port: 8888
tomcat:
uri-encoding: UTF-8
max-http-header-size: 80KB
############################################################
#
# 配置数据源信息
#
############################################################
spring:
datasource: # 数据源的相关配置
type: com.zaxxer.hikari.HikariDataSource # 数据源类型:HikariCP
driver-class-name: com.mysql.jdbc.Driver # mysql驱动
url: jdbc:mysql://localhost:3306/carle?useUnicode=true&characterEncoding=UTF-8&autoReconnect
username: root
password: root
hikari:
connection-timeout: 30000 # 等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQ
minimum-idle: 5 # 最小连接数
maximum-pool-size: 20 # 最大连接数
auto-commit: true # 自动提交
idle-timeout: 600000 # 连接超时的最大时长(毫秒),超时则被释放(retired),默认:10分钟
pool-name: DateSourceHikariCP # 连接池名字
max-lifetime: 1800000 # 连接的生命时长(毫秒),超时而且没被使用则被释放(retired),默认:30分钟
connection-test-query: SELECT 1
############################################################
#
# Mybatis 配置
#
############################################################
mybatis:
mapper-locations: mapper/*.xml # mapper映射文件,mapper接口和配置文件绑定
type-aliases-package: com.pojo # 所有PoJo类所在包路径,省略完全限定名
第三步:接口和XML配置文件
// mapper接口层
@Mapper
public interface UserMapper {
public void addUser(@Param("user") User user);
public void deleteUserById(@Param("id") int userId);
public void updateUserById(@Param("user") User user);
public List<User> getAllUsers();
public User getUserById(@Param("id") int userId);
}
// 注意更换命名空间,Mybatis高版本是不允许有中文存在
<?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.mapper.UserMapper">
<insert id="addUser" useGeneratedKeys="true">
INSERT INTO USER (username, password) VALUES (#{user.username},#{user.password});
</insert>
<delete id="deleteUserById" parameterType="int">
DELETE FROM USER WHERE id = #{id};
</delete>
<update id="updateUserById">
UPDATE USER SET username = #{user.username}, password = #{user.password} WHERE id = #{user.id};
</update>
<select id="getAllUsers" resultType="com.pojo.User">
SELECT id, username, password FROM USER WHERE 1;
</select>
<select id="getUserById" parameterType="int" resultType="com.pojo.User">
SELECT id, username, password FROM USER WHERE id = #{id};
</select>
</mapper>
第四步:service和controller层
// service层
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
/**
* 新增用户
* @param user
*/
@Transactional(propagation = Propagation.REQUIRED)
public void addUser(User user) {
if (user != null) {
userMapper.addUser(user);
}
}
/**
* 根据ID删除用户
* @param userId
*/
@Transactional(propagation = Propagation.REQUIRED)
public void deleteUserById(int userId) {
if ( userId > 0) {
userMapper.deleteUserById(userId);
}
}
/**
* 更新用户信息
* @param user
*/
@Transactional(propagation = Propagation.REQUIRED)
public void updateUserById(User user) {
userMapper.updateUserById(user);
}
/**
* 获取所有用户信息
* @return
*/
@Transactional(propagation = Propagation.SUPPORTS)
public List<User> getAllUsers() {
return userMapper.getAllUsers();
}
/**
* 根据ID获取用户信息
* @param userId
* @return
*/
@Transactional(propagation = Propagation.SUPPORTS)
public User getUserById(int userId) {
User user = new User();
user.setId(userId);
if (userId > 0) {
user = userMapper.getUserById(userId);
}
return user;
}
}
// controller层
@RestController("user")
public class UserController {
@Autowired
private UserService userService;
@PostMapping("/addUser")
public HttpStatus addUser(UserBO userBO) {
if (userBO.getUsername() == "" || userBO.getPassword() == "") {
return HttpStatus.INTERNAL_SERVER_ERROR;
}
User user = new User();
user.setUsername(userBO.getUsername());
user.setPassword(userBO.getPassword());
userService.addUser(user);
return HttpStatus.OK;
}
@PostMapping("/deleteUserById")
public HttpStatus deleteUserById(@RequestParam int userId) {
userService.deleteUserById(userId);
return HttpStatus.OK;
}
@PostMapping("/updateUserById")
public HttpStatus updateUserById(@RequestBody User user) {
User isExit = userService.getUserById(user.getId());
if (isExit == null) {
return HttpStatus.INTERNAL_SERVER_ERROR;
}
userService.updateUserById(user);
return HttpStatus.OK;
}
@GetMapping("/getAllUsers")
public List<User> getAllUsers() {
List<User> allUsers = userService.getAllUsers();
System.out.println(allUsers);
return allUsers;
}
@GetMapping("/getUserById")
public User getUserById(@RequestParam int userId) {
User user = userService.getUserById(userId);
return user;
}
}
第五步:SQL结构和数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'carle', 'carle');
INSERT INTO `user` VALUES (2, 'root', 'root');
INSERT INTO `user` VALUES (3, 'admin', 'admin');
SET FOREIGN_KEY_CHECKS = 1;
二、深入了解Mybatis
2.1 多对一(⭐⭐)
输出结果:
Student{id=1, username='张三', teacher=Teacher{id=1, username='Carle'}}
Student{id=2, username='李四', teacher=Teacher{id=1, username='Carle'}}
Student{id=3, username='王五', teacher=Teacher{id=1, username='Carle'}}
// Student类
public class Student {
private Integer id;
private String username;
private Teacher teacher;
}
// Teacher类
public class Teacher {
private Integer id;
private String username;
}
方法一:查询嵌套处理
<!-- StudentMapper.xml文件 -->
<!-- 不推荐使用该方法 -->
<resultMap id="StudentMapper" type="com.pojo.Student">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<association property="teacher" column="tid" javaType="com.pojo.Teacher" select="getTeacherById"></association>
</resultMap>
<select id="getAllStudent" resultMap="StudentMapper">
select * from student
</select>
<select id="getTeacherById" resultType="com.pojo.Teacher">
select * from teacher where id = #{id}
</select>
方法二:结果嵌套处理(推荐)
<!-- StudentMapper.xml文件 -->
<resultMap id="StudentMapper" type="com.pojo.Student">
<id property="id" column="id"></id>
<result property="username" column="sname"></result>
<association property="teacher" javaType="Teacher">
<id property="id" column="tid"></id>
<result property="username" column="tname"></result>
</association>
</resultMap>
<select id="getAllStudent" resultMap="StudentMapper">
SELECT s.id id, s.username sname, t.id tid, t.username tname FROM student AS s, teacher AS t WHERE s.tid = t.id
</select>
2.2 一对多(⭐⭐)
输出结果:
Teacher{
id=1,
username='Carle',
students=[
Student{id=1, username='张三'},
Student{id=2, username='李四'},
Student{id=3, username='王五'}
]
}
// Teacher类
public class Teacher {
private Integer id;
private String username;
private List<Student> students;
}
// Student类
public class Student {
private Integer id;
private String username;
}
方法一:查询嵌套处理
<!-- 不推荐使用该方法 -->
方法二:结果嵌套处理(推荐)
<!-- TeacherMapper.xml文件 -->
<resultMap id="TeacherMapper" type="com.pojo.Teacher">
<id property="id" column="tid"></id>
<result property="username" column="tname"></result>
<collection property="students" ofType="com.pojo.Student">
<id property="id" column="sid"></id>
<result property="username" column="sname"></result>
</collection>
</resultMap>
<select id="getAllTeacher" parameterType="int" resultMap="TeacherMapper">
SELECT t.id tid, t.username tname, s.id sid, s.username sname
FROM teacher AS t, student AS s
WHERE t.id = s.tid and t.id = #{tid}
</select>
2.3 动态SQL(⭐)
详细配置官网查看即可:动态SQL官方文档
-
if
-
choose (when, otherwise)
-
trim (where, set)
-
foreach
-
bind(使用like模糊查询时可以使用)
<select id="getStudentByUsernameAndTid" resultType="com.pojo.Student"> <bind name="usernamePattern" value="'%' + username + '%'"/> select * from student <where> <if test="username != null and username != ''"> username like #{usernamePattern} </if> </where> </select>
2.4 通用Mapper(⭐⭐⭐)
使用通用Mapper可以在操作数据库的时候,简化代码,例如增、删、改不用撰写SQL语句;同时查询的时候提供了一些好用的内置方法,比如通过实体类、条件等方法简化开发。
步骤一:添加Maven依赖坐标
<!--通用Mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.2.4</version>
</dependency>
步骤二:构建myMapper
通用接口
import tk.mybatis.mapper.common.BaseMapper;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
public interface myMapper<T> extends BaseMapper<T>, Mapper<T>, MySqlMapper<T> {
}
步骤三:在PoJo接口上继承myMapper
@Mapper
public interface UserMapper extends myMapper<Student> {
}
步骤四:使用通用mapper内置方法
【注意】通过测试发现,凡是继承通用mapper接口的接口类,则可以不用再绑定新的xml文件,内部会自动构建通用mapper的xml文件。如果还想自定义SQL语句的话,那么在原来已经继承了通用mapper的接口上,再绑定一个xml文件即可,注意namespace要填写正确,这样就可以实现,一个pojo接口类绑定多个mapper的xml文件。
2.5 PageHelper(⭐⭐)
步骤一:导入Maven依赖:官方地址
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
步骤二:调用PageHelper.startPage 静态方法
@Test
public void testPageHelper() {
// 1. 使用PageHelper
PageHelper.startPage(3,2,true);
// 2. 获取数据集合
List<User> users = userMapper.selectAll();
// 3. 使用PageInfo封装数据
PageInfo<User> userPageInfo = new PageInfo<>(users);
// 4. 获取分页后的数据集合
List<User> list = userPageInfo.getList();
// 5. 打印数据项
for (User user : list) {
System.out.println(user);
}
/**
* PageHelper.startPage(pageNum,pageSize,count)
* 参数详解:
* pageNum:表示从第几页开始
* pageSize:表示一页多少条记录
* count:表示是否返回总的真实记录数
* 注意事项:方法要紧跟着查询,否则可能不分页
*
* public class PageInfo<T> extends PageSerializable<T>
* 参数详解:
* private int pageNum;//当前页
* private int pageSize;//每页的数量
* private int size;//当前页的数量
* private int startRow;//当前页面第一个元素在数据库中的行号
* private int endRow;//当前页面最后一个元素在数据库中的行号
* private long total;//总记录数
* private int pages;//总页数
* private List<T> list;//结果集
* private int prePage;//前一页
* private int nextPage;//下一页
* private boolean isFirstPage;//是否为第一页
* private boolean isLastPage;//是否为最后一页
* private boolean hasPreviousPage;//是否有前一页
* private boolean hasNextPage;//是否有下一页
* private int navigatePages;//导航页码数
* private int[] navigatepageNums;//所有导航页号
* private int navigateFirstPage;//导航条上的第一页
* private int navigateLastPage;//导航条上的最后一页
*/
}
三、Mybatis在控制台输出SQL语句【IDEA】
在properties.yml文件中,添加map-underscore-to-camel-case
和 log-impl
相关配置
mybatis:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl