Human0722's blog
Xueliang
2022-11-30
目录

Mybatis

Mybatis 快速上手 (For Spring Boot)

Mybatis 官方文档 (opens new window)

本文环境:

  • Spring Boot 2.7.6
  • Mybatis Starter 2.2.2
  • MySQL 5.7
  • MySQL Connector 8.0.16

# 一、安装配置使用

# 1 引入依赖

<!-- Mybatis Starter-->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>2.2.2</version>
</dependency>
<!-- MySQL Connect Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12

# 2 配置 Mybatis

spring:
  # 数据库连接设置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_plus
    username: root
    password: puhou
mybatis:
  # 包含SQL的mapper文件位置
  mapper-locations: classpath:mapper/**
  # 自动生成别名,可以在mapper.xml中的ResultType中写精简类名
  type-aliases-package: io.github.human0722.springbootmybatisdemo.domain
1
2
3
4
5
6
7
8
9
10
11
12

# 3 MVC 代码

domain.User

public class User {
  private Integer id;
  private String name;
  
  //... setter()、getter()
}
1
2
3
4
5
6

dao.UserDao

import org.apache.ibatis.annotations.Mapper;
@Maper
public interface UserDao {
    User getUserById(int id);
}
1
2
3
4
5

mapper.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="io.github.human0722.springbootmybatisdemo.dao.UserDao">
    <!--可以使用简写resultType="User"的前提是在配置了type-alias-package, 不然要写类全名-->
    <select id="getUserById" resultType="User" parameterType="int">
        select * from t_user where id=#{id}
    </select>
</mapper>
1
2
3
4
5
6
7
8

UserDaoTest

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class UserDaoTest{
    @Autowired
    private UserDao userDao;
    
    @Test
    public void testSelectUserById() {
        User user = userDao.testSelectUserById(2);     
        System.out.println(user);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 二、增删改查

# Template

<?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="User">
  <select></select>
</mapper>
1
2
3
4
5

# 新增

<!-- 新增一条：int insertOne(User user) -->
<!-- 自动生成id,数据库id字段要设置成 primary key + auto increment -->
<insert id="insertOne" useGeneratedKeys="true" keyProperty="id">
  insert into User (name,age,email)
  <!-- 接口参数类型是User, #{name} 会自动解析 User.getName() -->
  values (#{name},#{age},#{email})
</insert>
1
2
3
4
5
6
7
<!-- 批量新增： int insertBatch(List<User> users) -->
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
    insert into User (name,age,email) values
    <foreach item="user" collection="list" separator=",">
      (#{user.name}, #{user.age}, #{user.email})
    </foreach> 
</insert>
1
2
3
4
5
6
7

# 删除

<!-- 删除一条 int deleteOne(String id) -->
<delete>
  delete from t_user where id = #{id}
</delete>
1
2
3
4

# 更新

<update id="updateById" parameterType="User">
  update Author
  <set>
    <if test="name != null">name=#{name},</if>
    <if test="age != null">age=#{age},</if>
    <if test="email != null">email=#{email},</if>
    <if test="is_deleted != null">is_deleted=#{is_deleted}</if>
  </set>
  where id=#{id}
</update>
1
2
3
4
5
6
7
8
9
10

# 查询

# HashMap 接收

<!-- Map<String, Object> findUserById(String id) -->
<select id="findUserById" parameterType="string" resultType="hashmap"> 
  select * from t_user where id = #{id}
</select>
1
2
3
4

# 实体类接收

<!-- User findUserById(String id) -->
<!-- 需要配置 type-alias-package 才可以通过简称使用 User -->
<select id="findUserById" parameterType="string" resultType="User"> select * from t_user where id = #{id}
  select * from t_user where id = #{id}
</select>
1
2
3
4
5

# 实体类集合接收

<!-- List<User> findUserList() -->
<select id="findUserList" resultTYpe="User">
  select * from t_user
</select>
1
2
3
4

# 多对一映射

JavaBean

class Emp {
  private String id;
  private String name;
  private Dept;
}
class Dept {
  private String id;
}
1
2
3
4
5
6
7
8
# 级联方式
<resultMap id="empAndDeptResultMapOne" type="Emp">
	<id property="eid" column="eid"></id>
	<result property="empName" column="emp_name"></result>
	<result property="age" column="age"></result>
	<result property="sex" column="sex"></result>
	<result property="email" column="email"></result>
	<result property="dept.did" column="did"></result>
	<result property="dept.deptName" column="dept_name"></result>
</resultMap>
<!--Emp getEmpAndDept(@Param("eid")Integer eid);-->
<select id="getEmpAndDept" resultMap="empAndDeptResultMapOne">
	select * from t_emp left join t_dept on t_emp.eid = t_dept.did where t_emp.eid = #{eid}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
# association 方式
<resultMap id="empAndDeptResultMapTwo" type="Emp">
	<id property="eid" column="eid"></id>
	<result property="empName" column="emp_name"></result>
	<result property="age" column="age"></result>
	<result property="sex" column="sex"></result>
	<result property="email" column="email"></result>
	<association property="dept" javaType="Dept">
		<id property="did" column="did"></id>
		<result property="deptName" column="dept_name"></result>
	</association>
</resultMap>
<!--Emp getEmpAndDept(@Param("eid")Integer eid);-->
<select id="getEmpAndDept" resultMap="empAndDeptResultMapTwo">
	select * from t_emp left join t_dept on t_emp.eid = t_dept.did where t_emp.eid = #{eid}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 分步查询
<resultMap id="empAndDeptByStepResultMap" type="Emp">
  <id property="eid" column="eid"></id>
  <result property="empName" column="emp_name"></result>
  <result property="age" column="age"></result>
  <result property="sex" column="sex"></result>
  <result property="email" column="email"></result>
  <association property="dept"
               select="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo"
               column="did"></association>
</resultMap>
        <!--Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid);-->
<select id="getEmpAndDeptByStepOne" resultMap="empAndDeptByStepResultMap">
select * from t_emp where eid = #{eid}
</select>

<resultMap id="EmpAndDeptByStepTwoResultMap" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
</resultMap>
        <!--Dept getEmpAndDeptByStepTwo(@Param("did") Integer did);-->
<select id="getEmpAndDeptByStepTwo" resultMap="EmpAndDeptByStepTwoResultMap">
select * from t_dept where did = #{did}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 一对多映射

JavaBean

public class Dept {
    private Integer did;
    private String deptName;
    private List<Emp> emps;
}
1
2
3
4
5
# collection 方式
<resultMap id="DeptAndEmpResultMap" type="Dept">
	<id property="did" column="did"></id>
	<result property="deptName" column="dept_name"></result>
	<collection property="emps" ofType="Emp">
		<id property="eid" column="eid"></id>
		<result property="empName" column="emp_name"></result>
		<result property="age" column="age"></result>
		<result property="sex" column="sex"></result>
		<result property="email" column="email"></result>
	</collection>
</resultMap>
<!--Dept getDeptAndEmp(@Param("did") Integer did);-->
<select id="getDeptAndEmp" resultMap="DeptAndEmpResultMap">
	select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did = #{did}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 分步查询
<resultMap id="DeptAndEmpByStepOneResultMap" type="Dept">
	<id property="did" column="did"></id>
	<result property="deptName" column="dept_name"></result>
	<collection property="emps"
				select="com.atguigu.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
				column="did"></collection>
</resultMap>
<!--Dept getDeptAndEmpByStepOne(@Param("did") Integer did);-->
<select id="getDeptAndEmpByStepOne" resultMap="DeptAndEmpByStepOneResultMap">
	select * from t_dept where did = #{did}
</select>

<!--List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did);-->
<select id="getDeptAndEmpByStepTwo" resultType="Emp">
select * from t_emp where did = #{did}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 自定义映射

<!-- UserResponse findUser(String id) -->
<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="target:domain_property" column="source:database_column"/>
</resultMap>
<select id="selectUsers" resultMap="userResultMap">
select user_id, user_name, hashed_password
from some_table
where id = #{id}
</select>
1
2
3
4
5
6
7
8
9
10
11

# 三、动态 SQL

# if

<!-- 模糊查询 OR NOT List<User> findPage(String name); -->
<select id="selectPage">
  select * from t_user 
  <where>
  <if test="#{name} != null and #{name} != ''">
    and name like "%"#{name}"%"
  </if>
  </where>
  limit 0,5
</select>
1
2
3
4
5
6
7
8
9
10

# choose

<select id="findUserActive"
        resultType="User">
  SELECT * FROM BLOG WHERE is_deleted = ‘0’
  <choose>
    <when test="name != null">
      AND name like #{name}
    </when>
    <when test="author != null and author.name != null">
      AND age like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# trim

trim用于替代类似 <where> <set> 标签的功能。

<trim prefix="WHERE" prefixOverrides="OR|AND">
  1 = 1
</trim>
1
2
3
<trim prefix="SET" suffixOverrides=",">
  1 = 1
</trim>
1
2
3

# foreach

<!-- 批量新增： int insertBatch(List<User> users) -->
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
  insert into User (name,age,email) values
  <foreach item="user" collection="list" separator=",">
    (#{user.name}, #{user.age}, #{user.email})
  </foreach>
</insert>
1
2
3
4
5
6
7
druid starter
Mybatis_Plus

← Mybatis_Plus→

最近更新
01
DefineSprintBootStarter
03-23
02
Spring MVC 启动流程分析
03-23
03
Redis
03-23
更多文章>
Theme by Vdoing | Copyright © 2019-2024 Human0722 | MIT License