Mybatis快速入门

依赖

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

配置

spring.datasource.username=root
spring.datasource.password=ccc+022599
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/baoshihua?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis.type-aliases-package=cn.baoshihua               
mybatis.mapper-locations = classpath*:mapper/**.xml

dao

public interface StudentMapper extends BaseMapper<Student> {
    // 查找学生成绩
    public List<ScoreDto> findScore();
}

mapper

<?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="cn.ccb.mapper.StudentMapper">
    
    <select id="findScore" resultType="ScoreDto">   // 别名为类名,首字母大写小写都行
        select student.student_id     as studentId,
               student.student_name   as studentName,
               class.class_name       as className,
               result.score
        from tb_score result
               left join tb_student student on result.student_id = student.student_id
               left join tb_class class on student.class_id = class.class_id
    </select>
</mapper>

命名问题

对于驼峰式命名的实体类和字段，mybatis默认是通过下划线分隔；

• 比如 public interface SysUserDao extends BaseMapper<SysUser> 实体名默认映射 sys_user表；

• 比如 userName字段名默认映射是user_name列；

配置如下：

// 表前缀
mybatis.global-config.db-config.table-prefix=t_             

// 将sysUser类映射成sys_user表
mybatis.global-config.db-config.table-underline=true       

// 将类中的属性userName 映射成 user_name
mybatis.configuration.map-underscore-to-camel-case=true  

深入参数

• 将一些特殊符号表示为普通字符时，可以将符号放入<![CDATA[ ]]>，如：<![CDATA[ < ]]>

• 参数只是一个简单类型（java 基本类型和 String），可以使用#{任意字符}

• 多个参数使用@Param，在方法形参前面加入@Param(“自定义参数名”)，mapper 文件中使用#{自定义参数名}

• 多个参数使用对象

  List<Student> selectMultiObject(QueryParam queryParam);
  
  <select id="selectMultiObject" resultType="com.domain.Student">
  	select id,name from student where name= #{queryName} or age = #{queryAge}
  </select>
  

• 多个参数使用map，map集合中key为String，value为Object，mapper文件中使用#{key} 引用参数值

  Map<String,Object> data = new HashMap<String,Object>();
  data.put(“myname”,”李力”);
  data.put(“myage”,20);
  
  List<Student> selectMultiMap(Map<String,Object> map);
  
  <select id="selectMultiMap" resultType="com.domain.Studentt">
  	select * from student where name=#{myname} or age=#{myage}
  </select>
  

分页查询

• 引入依赖

  <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper-spring-boot-starter</artifactId>
      <version>1.4.1</version>
  </dependency>
  

• 编写代码

  @GetMapping("/first")
  public List<StudentDto> first() {
      PageHelper.startPage(2, 3);
      List<StudentDto> list = studentMapper.first();
      
      PageInfo<StudentDto> page = new PageInfo<>(list);
      return list;
  }
  

动态sql

• <if>：第一个<if>标签可以包含and也可以不包含and，但其他<if/>中的and必须要有
• <where>：有查询条件时会自动添加where语句，没有查询条件时不会添加
• <trim>：添加或移除 前缀/后缀

<select id="selectStudentForList"  resultType="com.domain.Student">
     select id,name,email from student
         <if test="list !=null and list.size > 0 ">
            where id in
             <foreach collection="list" open="(" close=")" item="stuobject" separator=",">
                #{stuobject.id}
             </foreach>
         </if>
</select>

<insert id="insertSelective" parameterType="com.api.entity.crm.ApplyEntity">
    insert into order_apply
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="applyId != null">
            apply_id,
        </if>
        <if test="businessCode != null">
            business_code,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="applyId != null">
            #{applyId,jdbcType=INTEGER},
        </if>
        <if test="businessCode != null">
            #{businessCode,jdbcType=VARCHAR},
        </if>
    </trim>
</insert>