Mybatis

2022-03-03 本文已影响0人请叫我平爷

Mybatis

常见

#{} 和 ${}

{}:sql占位符
${}:字符替换，properties中的变量占位符

Mybatis使用方式

方式一：

Mapper类

public interface StudentMapper {
    List<Student> selectAll();
}

StudentMapper.xml

<select id="selectAll" resultMap="BaseResultMap">
    select `name`, code
    from student
</select>

方式二：

Mapper类：

public interface StudentMapper{
    @Select("select * from student")
    List<Student> selectAll();
}

方式三

Mapper类：

public interface StudentMapper{
    @SelectProvider(type= StudentProvider.class,method="selectStudentById")
    Student selectStudentById(@Param(value="code") String code);
}

StudentProvider

public class StudentProvider {
    public String selectStudentById(Map<String, Object> para){
        return new SQL(){{
            SELECT("*");
            FROM("student");
            WHERE("code="+para.get("code"));
        }}.toString();
    }
}

获取自增长主键id

Mysql：

方式一：

<insert id="insert" parameterType="com.mi.learn.mybatis.domin.Afinfo">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into afinfo (`name`, age, birth, 
      sex, memo)
    values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{birth,jdbcType=DATE}, 
      #{sex,jdbcType=CHAR}, #{memo,jdbcType=VARCHAR})
</insert>

方式二

<insert id="insertAfinfo" parameterType="com.mi.learn.mybatis.domin.Afinfo" useGeneratedKeys="true" keyProperty="id">
    insert into afinfo (`name`, age, birth,
      sex, memo)
    values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{birth,jdbcType=DATE},
      #{sex,jdbcType=CHAR}, #{memo,jdbcType=VARCHAR})
</insert>

Oracle：

// 这个是创建表的自增序列
CREATE SEQUENCE student_sequence
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 10;

// 方式一，使用 `<selectKey />` 标签 + BEFORE
<insert id="add" parameterType="Student">
　　<selectKey keyProperty="student_id" resultType="int" order="BEFORE">
      select student_sequence.nextval FROM dual
    </selectKey>
    
     INSERT INTO student(student_id, student_name, student_age)
     VALUES (#{student_id},#{student_name},#{student_age})
</insert>

// 方式二，使用 `<selectKey />` 标签 + AFTER
<insert id="save" parameterType="com.threeti.to.ZoneTO" >
    <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER" >
      SELECT SEQ_ZONE.CURRVAL AS id FROM dual
    </selectKey>
    
    INSERT INTO TBL_ZONE (ID, NAME ) 
    VALUES (SEQ_ZONE.NEXTVAL, #{name,jdbcType=VARCHAR})
</insert>

Mapper传递多个参数

使用Map传递

Afinfo selectAfinfoWithMap(Map<String,Object> map);

<select id="selectAfinfoWithMap" parameterType="Map" resultType="com.mi.learn.mybatis.domin.Afinfo">
    select id, `name`, age, birth, sex, memo
    from afinfo
    where `name` = #{name} and age=#{age}
  </select>

使用@Param传递

Afinfo selectAfinfoWithParam(@Param("name") String name,
                              @Param("age") Integer age
                              );
<select id="selectAfinfoWithParam" resultType="com.mi.learn.mybatis.domin.Afinfo">
    select id, `name`, age, birth, sex, memo
    from afinfo
    where `name` = #{name} and age=#{age}
  </select>

不使用@Param

Afinfo selectAfinfoWithObject(String name,
                               Integer age);

<select id="selectAfinfoWithObject" resultType="com.mi.learn.mybatis.domin.Afinfo">
    select id, `name`, age, birth, sex, memo
      from afinfo
      where `name` = #{param1} and age=#{param2}
  </select>

批量插入

for循环调用单条插入

for (int i=0;i<5; i++){
    mapper.insertAfinfo(afinfo);   
}

<insert id="insertAfinfo" parameterType="com.mi.learn.mybatis.domin.Afinfo" useGeneratedKeys="true" keyProperty="id">
    insert into afinfo (`name`, age, birth,
      sex, memo)
    values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{birth,jdbcType=DATE},
      #{sex,jdbcType=CHAR}, #{memo,jdbcType=VARCHAR})
</insert>

批量插入

mapper.insertBatch(list);

<insert id="insertBatch" parameterType="list" useGeneratedKeys="true" keyProperty="id">
    insert into afinfo (`name`, age, birth,
    sex, memo) values
    <foreach collection="list" item="item" index="index" separator=",">
    (#{item.name,jdbcType=VARCHAR}, #{item.age,jdbcType=INTEGER}, #{item.birth,jdbcType=DATE},
      #{item.sex,jdbcType=CHAR}, #{item.memo,jdbcType=VARCHAR})
    </foreach>
</insert>

控制台打印sql日志

logging.level.com.mi.learn.mybatis.mapper=debug

Mybatis缓存

# 开启缓存
mybatis.configuration.cache-enabled=true

Mybatis和Hibernate

Hibernate 全自动ORM映射工具，可以查询关联对象或者关联集合对象，可以根据对象关系模型直接获取
Mybatis属于半自动ORM映射工具，在查询关联对象或关联集合对象时，需要手动编写SQL

转义字符

转义字符	字符	含义
<	<	小于
>	>	大于
&	&	与
'	'	单引号
"	"	双引号

或者

<![CDATA[    ]]>

XML标签

<select></select>
<insert></insert>
<update></update>
<delete></delete>
<resultMap></resultMap>
<parameterMap></parameterMap>
<sql></sql>
<include></include>
<selectKey></selectKey>

sql、include标签

List<Afinfo> selectAfinfoWithObject(String name,
                               Integer age);

<sql id="student_element">
    id,`name`,age,birth,sex,memo
</sql>

<select id="selectAfinfoWithObject" resultType="com.mi.learn.mybatis.domin.Afinfo">
    select <include refid="student_element"></include>
      from afinfo
      where `name` = #{param1} and age=#{param2}
  </select>

xml和Mapper接口的工作原理，Mapper接口参数不通，能否重载

xml文件与Mapper类通过namespace来一一对应
Mapper接口是没有实现类的，通过调用方法，mapper类的包名+接口名为key
- 如：com.mi.learn.mybatis.mapper.AfinfoMapper.insert
  - 可以找到namespace为com.mi.learn.mybatis.mapper.AfinfoMapper下
  - id为insert的MappedStatement(映射语句)
  - 每个 select、update、delete、insert都会被解析成一个MapperStatement(映射语句)
Mapper接口是不能重载的，因为是通过包名+方法名去保存和寻找
Mapper接口的工作原理是JDK动态代理
- Mybatis运行时会通过JDK动态代理把Mapper接口生成代理proxy对象
- 代理对象proxy会拦截接口方法
- 改成执行MapperStatement所对应的sql
- 将sql执行的结果返回

Mybits分页

RowBounds对象进行分页，对ResultSet结果集执行内存分页
分页插件原理是使用Mybatis提供的插件接口，实现自定义插件，在插件的拦截方法中拦截待执行的sql，重写sql，添加对应的物理分页和物理分页参数

Mybatis的插件运行原理，怎么编写一个插件

Mybatis只能编写针对ParameterHandler、ResultSetHandler、StatementHandler、Executor这四种接口插件

package com.mi.learn.mybatis.handler;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.springframework.cglib.proxy.InvocationHandler;
import org.springframework.cglib.proxy.Proxy;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Slf4j
@Component
@Intercepts({@Signature(type= Executor.class, method = "select", args = {MappedStatement.class,Object.class})})
public class MybatisInterceptor implements Interceptor {
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget(); //被代理对象
        Method method = invocation.getMethod(); //代理方法
        Object[] args = invocation.getArgs(); //方法参数
        // do something ...... 方法拦截前执行代码块
        Object result = invocation.proceed();
        // do something .......方法拦截后执行代码块
        return result;
    }
    public Object plugin(Object target) {
        return Proxy.newProxyInstance(Interceptor.class.getClassLoader(), target.getClass().getInterfaces(), new InvocationHandler() {
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                return intercept(new Invocation(target, method, args));
            }
        });
    }
}

Mybatis一对多、多对一

一对多

List<StudentExam> selectStudentExam();

//StudentExam
@Data
public class StudentExam {

    private String name;

    private String code;

    private List<Exam> examList;

}

// Exam
@Data
public class Exam {

    private String code;

    private String subject;

    private String score;

}

```xml
<resultMap id="StudentExamResultMap" type="com.mi.learn.mybatis.bean.StudentExam">
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="code" jdbcType="VARCHAR" property="code" />
    <collection property="examList" ofType="com.mi.learn.mybatis.domin.Exam" column="code">
    <result column="code" jdbcType="VARCHAR" property="code"/>
    <result column="subject" jdbcType="VARCHAR" property="subject"/>
    <result column="score" jdbcType="VARCHAR" property="score"/>
    </collection>
</resultMap>


<select id="selectStudentExam" resultMap="StudentExamResultMap">
    select s.* , e.*
    from student s , exam e
    where s.code = e.code
    order by s.code
</select>

```

多对一

List<ExamStudent> selectExamStudent();

//StudentExam
@Data
public class StudentExam {

    private String name;

    private String code;

    private List<Exam> examList;

}

//Exam
@Data
public class Exam {

    private String code;

    private String subject;

    private String score;

}

```xml
<resultMap id="ExamStudentResultMap" type="com.mi.learn.mybatis.bean.ExamStudent">
    <result column="code" jdbcType="VARCHAR" property="code"/>
    <result column="subject" jdbcType="VARCHAR" property="subject"/>
    <result column="score" jdbcType="VARCHAR" property="score"/>
    <association property="student" javaType="com.mi.learn.mybatis.domin.Student">
    <result column="code" jdbcType="VARCHAR" property="code"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    </association>
</resultMap>

<select id="selectExamStudent" resultMap="ExamStudentResultMap">
    select s.* , e.*
    from student s , exam e
    where s.code = e.code
    order by s.code
</select>
```

Mybatis延迟加载

# 开启缓存
mybatis.configuration.cache-enabled=true
# 延迟加载
mybatis.configuration.lazy-loading-enabled=true
# 关闭积极加载
mybatis.configuration.aggressive-lazy-loading=false

    List<StudentExam> selectLazyStudent();

    List<Exam> selectExamByCode(@Param("code") String code);


    @Test
    @Transactional
    public void selectLazyStudentTest(){
        List<StudentExam> res = mapper.selectLazyStudent();
        log.info(JSON.toJSONString(res));
        log.info("==========================================");
        List<Exam> examList1 = mapper.selectExamByCode("001");
        List<Exam> examList2 = mapper.selectExamByCode("002");
        List<Exam> examList3 = mapper.selectExamByCode("003");
        log.info("==========================================");
        log.info("examList1:{}",JSON.toJSONString(examList1));
        log.info("examList1:{}",JSON.toJSONString(examList2));
        log.info("examList1:{}",JSON.toJSONString(examList3));
    }

<resultMap id="StudentExamLazyResultMap" type="com.mi.learn.mybatis.bean.StudentExam">
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="code" jdbcType="VARCHAR" property="code" />
    <association property="examList" javaType="List" select="com.mi.learn.mybatis.mapper.StudentMapper.selectExamByCode" column="code"/>
</resultMap>

<select id="selectLazyStudent" resultMap="StudentExamLazyResultMap">
    select s.*
    from student s
    order by s.code
</select>

<select id="selectExamByCode" resultType="com.mi.learn.mybatis.domin.Exam" parameterType="string">
    select * from exam where exam.code = #{code}
</select>

日志：

com.mi.learn.mybatis.StudentMapperTest   : ==========================================
com.mi.learn.mybatis.StudentMapperTest   : ==========================================
com.mi.learn.mybatis.StudentMapperTest   : examList1:[{"code":"001","score":"80","subject":"数学"},{"code":"001","score":"90","subject":"语文"},{"code":"001","score":"90","subject":"英语"}]
com.mi.learn.mybatis.StudentMapperTest   : examList2:[{"code":"002","score":"75","subject":"数学"},{"code":"002","score":"85","subject":"语文"},{"code":"002","score":"85","subject":"英语"}]
com.mi.learn.mybatis.StudentMapperTest   : examList3:[{"code":"003","score":"80","subject":"英语"}]

并没有打印查询Exam表的sql语句

Mybatis中xml的id是否能一样

查找是按namespace+id来作为Map<key,MappedStatement>的key
设置了namespace，namespace不一样，id一样是可以的
没设置namespace，id不能一样。

Executor

SimpleExecutor
- 每执行一次update或select，就开启一个Statement对象，用完立刻关闭Statement对象
ReuseExecutor
- 执行update或select，以sql作为key查找Statement对象，存在就使用，不存在就创建，用完后，不关闭Statement对象，而是放置于Map<String, Statement>内，供下一次使用。简言之，就是重复使用Statement对象。
BatchExecutor
- 执行update（没有select，JDBC批处理不支持select），将所有sql都添加到批处理中（addBatch()），等待统一执行（executeBatch()），它缓存了多个Statement对象，每个Statement对象都是addBatch()完毕后，等待逐一执行executeBatch()批处理。与JDBC批处理相同。

Mybatis映射Enum枚举

SexEnum

public enum SexEnum {

    SEX_NO_KNOW(0,"未知"),

    SEX_ENUM_MAN(1,"男"),

    SEX_ENUM_WOMAN(2,"女");
    
    private Integer sex;
    private String context;

    SexEnum(int sex, String context) {
        this.sex = sex;
        this.context = context;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Integer getSex() {
        return sex;
    }

    public void setContext(String context) {
        this.context = context;
    }

    public String getContext() {
        return context;
    }

}

Afinfo

@Data
@ToString
public class Afinfo implements Serializable {
    private Integer id;

    private String name;

    private Integer age;

    private Date birth;

    private SexEnum sex;

    private String sexContext;


    private String memo;

    private static final long serialVersionUID = 1L;
}

AfinfoMapper.xml

<resultMap id="BaseResultMap" type="com.mi.learn.mybatis.domin.Afinfo">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="birth" jdbcType="DATE" property="birth" />
    <result column="sex" property="sex" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.mi.learn.mybatis.myenum.SexEnum" />
    <result column="memo" jdbcType="VARCHAR" property="memo" />
  </resultMap>

Test

List<Afinfo> afinfoList11 = mapper.selectAfinfoWithMap(map);
afinfoList11.forEach(item->{
    SexEnum sexEnum = item.getSex();
    log.info(sexEnum.getContext());
});

log日志

com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 女
com.mi.learn.mybatis.AfinfoMapperTest    : 未知
com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 男
com.mi.learn.mybatis.AfinfoMapperTest    : 男

数据库中的数据

在ResultMap中加入固定的typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"
加入javaType="com.mi.learn.mybatis.myenum.SexEnum"，SexEnum为Enum包
Afinfo的sex属性改成SexEnum引用对象

使用

pom.xml

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.4.4</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

配置自动生成代码

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.7</version>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.12</version>
                    </dependency>
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>1.3.7</version>
                    </dependency>
                </dependencies>
                <executions>
                    <execution>
                        <id>MyBatis Artifacts</id>
                        <phase>package</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <!--允许移动生成的文件 -->
                    <verbose>true</verbose>
                    <!-- 是否覆盖 -->
                    <overwrite>true</overwrite>
                    <!-- 自动生成的配置 -->
                    <configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
                </configuration>
            </plugin>
        </plugins>

    </build>

generatorConfig.xml

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!--加载配置文件，为下面读取数据库信息准备-->
    <properties resource="application.properties"/>

    <!--defaultModelType="flat" 大数据字段，不分表 -->
    <context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="autoDelimitKeywords" value="true" />
        <property name="beginningDelimiter" value="`" />
        <property name="endingDelimiter" value="`" />
        <property name="javaFileEncoding" value="utf-8" />
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin" />

        <plugin type="org.mybatis.generator.plugins.ToStringPlugin" />

        <!-- 注释 -->
        <commentGenerator >
            <property name="suppressAllComments" value="true"/><!-- 是否取消注释 -->
            <property name="suppressDate" value="false" /> <!-- 是否生成注释代时间戳-->
        </commentGenerator>

        <!--数据库链接地址账号密码-->
        <jdbcConnection driverClass="${spring.datasource.driver-class-name}"
                        connectionURL="${spring.datasource.url}"
                        userId="${spring.datasource.username}"
                        password="${spring.datasource.password}">
        </jdbcConnection>

        <!-- 类型转换 -->
        <javaTypeResolver>
            <!-- 是否使用bigDecimal， false可自动转化以下类型（Long, Integer, Short, etc.） -->
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>

        <!--生成Model类存放位置-->
        <javaModelGenerator targetPackage="com.mi.learn.mybatis.domin" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!-- 生成mapxml文件 -->
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources/" >
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>

        <!-- 生成mapxml对应client，也就是接口dao -->
        <javaClientGenerator targetPackage="com.mi.learn.mybatis.mapper" targetProject="src/main/java" type="XMLMAPPER" >
            <property name="enableSubPackages" value="false" />
        </javaClientGenerator>

        <table tableName="afinfo" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true">
            <generatedKey column="id" sqlStatement="Mysql" identity="true" />
        </table>
    </context>
</generatorConfiguration>

application.properties

# 端口
server.port=8001

# xxxMapper.xml文件的路径
mybatis.mapper-locations=classpath:mapping/*.xml
# 日志显示sql
logging.level.com.mi.learn.mybatis.mapper=debug

# mysql数据库用户名
spring.datasource.username=root
# mysql数据库用户密码
spring.datasource.password=123456
# mysql连接url
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false
# mysql连接驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

文档

Mybatis官网

Mybatis面试题

Mybatis

Mybatis

常见

#{} 和 ${}

{}:sql占位符

Mybatis使用方式

获取自增长主键id

Mapper传递多个参数

批量插入

控制台打印sql日志

Mybatis缓存

Mybatis和Hibernate

转义字符

XML标签

sql、include标签

xml和Mapper接口的工作原理，Mapper接口参数不通，能否重载

Mybits分页

Mybatis的插件运行原理，怎么编写一个插件

Mybatis一对多、多对一

Mybatis延迟加载

Mybatis中xml的id是否能一样

Executor

Mybatis映射Enum枚举

使用

pom.xml

generatorConfig.xml

application.properties

文档

猜你喜欢

热点阅读