MyBatis进阶

多对一(association关联)查询

mybatis会自动推断完全限定名,多数情况下都可以推断成功

按照结果嵌套处理

查询学生信息,同时返回对应的老师名字

Teacher

@Data
@AllArgsConstructor
@NoArgsConstructor
@Alias("teacher")
public class Teacher {

  private int id;
  private String name;

}

Student里包含老师对象

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
  private int id;
  private String name;

  private Teacher teacher;

}

StudentMapper

List<Student> getStudent();

StudentMapper.xml

<select id="getStudent" resultMap="studentAndTeacherName">
  <!--多表联动查询-->
  select s.name sname, t.name tname
  from student s inner join teacher t on s.tid = t.id
</select>

<!--最终返回一个Student类型的对象-->
<resultMap id="studentAndTeacherName" type="student">
  <!--返回的表中字段sname赋值,给student对象的name属性-->
  <result property="name" column="sname"/>

  <!--复杂的属性,需要单独处理
association对象:返回对象中包含一个teacher类型的对象,类型(javaType)是teacher-->
  <association property="teacher" javaType="teacher">
    <!--把字段tname赋值给teacher对象的name属性-->
    <result property="name" column="tname"/>
  </association>
</resultMap>

List<Student> student = mapper.getStudent();

for (Student student1 : student) {
  System.out.println(student1.toString());
}

---

按照查询嵌套处理

<!--查出所有的学生信息:返回的字段有id,name,tid-->
  <select id="getStudent" resultMap="studentAndTeacherName">
  select * from student
  </select>

  <!--结果集映射,需要返回一个teacher类型-->
  <resultMap id="studentAndTeacherName" type="student">
  <result column="id" property="id"/>
  <!--这个teacher对象,由tid字段来赋值,这个tid从select中查询拿到,会自动和teacher的id进行匹配的-->
  <association property="teacher" javaType="teacher" column="tid" select="getTeacherById"/>
  </resultMap>

  <!--查询teacher对象,#{id}是从上面传过来的tid-->
  <select id="getTeacherById" resultType="teacher">
  select * from teacher where id = #{id};
</select>

---

一对多(collection集合)

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
  private int id;
  private String name;

  private int tid;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
@Alias("teacher")
public class Teacher {

  private int id;
  private String name;

  private List<Student> students;

}

Teacher getTeacher(@Param("tid") int id);

按结果嵌套查询

<select id="getTeacher" resultMap="teacherAndStudent">
  select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid
  from teacher t inner join student s on t.id = s.tid
  where t.id = 1;
</select>

<resultMap id="teacherAndStudent" type="teacher">
  <id property="id" column="tid"/>
  <result property="name" column="tname"/>

  <!--teacher对象中需要一个student对象的集合,类型就是student对象(别名)-->
  <collection property="students" ofType="student">

    <!--把从数据库中查到的字段值,一一对应的赋值给对象-->
    <id property="id" column="sid"/>
    <result property="name" column="sname"/>
    <result property="tid" column="stid"/>

  </collection>

</resultMap>

Teacher teacher = mapper.getTeacher(2);

System.out.println(teacher.toString());

---

按查询嵌套

<select id="getTeacher" resultMap="teacherAndStudent">
  select *
  from teacher
  where id = #{tid};
</select>

<resultMap id="teacherAndStudent" type="teacher">
  <!--teacher对象中需要一个student对象的集合,类型就是student对象(别名)
        每个对象的值来自于select查询的结果,把id传过去作为参数(它可以不用)-->
  <collection property="students" ofType="student" javaType="ArrayList"
              column="id" select="getStudentList"/>
</resultMap>

<select id="getStudentList" resultType="student">
  select * from student where tid = #{tid};
</select>

---

log4j可以完整打印出sql执行流程

Preparing: select * from teacher where id = ?;
Parameters: 2(Integer)
  
Preparing: select * from student where tid = ?;
Parameters: 2(Integer)
  
Total: 2
Total: 1

面试高频问题:

innodb底层原理,索引和索引优化

---

动态Sql

不要用java.sql.Date,直接用java.util.Date

真实开发中id是用UUID来生成的,保证唯一

String replace = UUID.randomUUID().toString().replace("-", "");

Student student = new Student(replace,"长歌",2);

mapper1.insert(student);

开始自动驼峰命名:允许字段名与实体类属性不同

<settings>
  <setting name="mapUnderscoreToCamelCase" value="true"></setting>
</settings>

//数据库中对应的字段名是student_name
private String studentName;//这是Student类中的属性

//但是最后插入成功了,就是因为我们设置开始了自动驼峰映射
mapper1.insert(new Student("13","12",3));

where在 最少有一个子元素成立 情况下,才会插入.会自动根据情况去掉and或or

<select id="getStudent" resultType="student">
  select * from student
  <where>
    <if test="id != null">
      id = #{id}
    </if>
    <if test="name != null">
      and name = #{name};
    </if>
  </where>

</select>

ohterwise相当于default

<select id="getStudent" resultType="student">
  select * from student
  <where>
    <!--有一个成立就直接break了-->
    <choose>
      <when test="id != null">
        id = #{id}
      </when>

      <when test="name != null">
        and name = #{name}
      </when>

      <otherwise>
        tid = #{tid};
      </otherwise>
    </choose>
  </where>

</select>

set在最少有一个子元素成立的情况下成立,会自动去除 , 号

<update id="updateStudent">
  update student
  <set>
    name = #{name},
  </set>
  <where>
    id = #{id};
  </where>

</update>

日志查看最终的sql语句

Preparing: update student SET name = ? WHERE id = ?;

int updateStudent(@Param("id") int id,@Param("name")String name,@Param("tid")int tid);

trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

https://blog.csdn.net/wt_better/article/details/80992014#:~:text=mybatis

<!--update student SET name = ? where id = ? and tid = ?-->
<update id="updateStudent">
  update student
  <set>
    name = #{name},
  </set>

  <trim prefix="where" prefixOverrides="and">
    <if test="id != null">
      and id = #{id}
    </if>
    <if test="tid != null">
      and tid = #{tid}
    </if>
  </trim>

</update>

int updateStudent(@Param("id")int id, @Param("name")String name,@Param("tid")int tid);

mapper1.updateStudent(1,"天才第二步",1);

prefix	给SQL语句拼接的前缀，为 trim 包含的内容加上前缀
suffix	给SQL语句拼接的后缀，为 trim 包含的内容加上后缀
prefixOverrides	去除 SQL 语句前面的关键字或字符，该关键字或者字符由 prefixOverrides 属性指定。
suffixOverrides	去除 SQL 语句后面的关键字或者字符，该关键字或者字符由 suffixOverrides 属性指定。

---

sql片段:导入其他sql语句

<update id="updateStudent">
  update student
  <set>
    name = #{name},
  </set>

  <!--导入sql语句-->
  <include refid="sqlTest"/>

</update>

<sql id="sqlTest">
  <if test="id != null">
    <where>
      id = #{id};
    </where>
  </if>
</sql>

---

Foreach

<!--Preparing: update student SET name = ? WHERE id in ( ? , ? , ? )-->
<update id="updateStudent">
  update student
  <set>
    name = #{name},
  </set>

  <where>
    id in
    <!--where id in(id,id,id)
                open开始,separator分隔,close结束
            -->
    <foreach collection="ids" item="id"
             open="(" separator="," close=")">
      #{id}
    </foreach>
  </where>
</update>

int updateStudent(@Param("ids")ArrayList ids, @Param("name")String name);


ArrayList<Object> arrayList = new ArrayList();
arrayList.add(1);
arrayList.add(2);
arrayList.add(3);

mapper1.updateStudent(arrayList,"天才第一步");

不要写慢sql

---

缓存:减少与数据库交互次数,减少系统开销

早些年会加一台memcached(缓存)服务器专门用来读取数据,做到读写分离.后来数据库方面又加入了**主从复制(依靠哨兵实现)**概念

小白都能懂的Mysql主从复制原理（原理+实操） - 知乎 (zhihu.com)

缓存机制包括:LRU(最近最少引用,默认60秒)和先进先出

---

一级缓存(本地缓存,SqlSession)

1. 在一次会话中(从SqlSession创建到close),

2. 会把查询到的数据放在本地缓存中,

   下次再查询时就不走数据库,直接从本地缓存中拿

   Student studentById = mapper1.getStudentById(1);
   System.out.println(studentById);
   
   
   Student studentById1 = mapper1.getStudentById(1);
   System.out.println(studentById1);
   

   打开日志查看,就可以看到查询语句只输出了一遍

   Created connection 1955920234.
     
   Preparing: select * from student where id = ?
   Parameters: 1(Integer)
   Total: 1
     
   Student(id=1, name=天才第二步, tid=1)
   Student(id=1, name=天才第二步, tid=1)
   

   sqlSession.clearCache();//清理缓存

   Student studentById = mapper1.getStudentById(1);
   System.out.println(studentById);
   
   sqlSession.clearCache();//清理缓存
   
   Student studentById1 = mapper1.getStudentById(1);
   System.out.println(studentById1);
   

   Created connection 1955920234.
     
   Preparing: select * from student where id = ?
   Parameters: 1(Integer)
   Total: 1
   Student(id=1, name=天才第二步, tid=1)
   
   //查询了两遍  
   Preparing: select * from student where id = ?
   Parameters: 1(Integer)
   Total: 1
   Student(id=1, name=天才第二步, tid=1)
   

   ---

一级缓存相当于一个Map,

用debug时,可以看到mapper中有个methodCache对象,这就是一级缓存

---

二级缓存(全局缓存:namespace级别缓存)

工作机制:会话关闭后,一级缓存中的数据,会被保存到二级缓存中

<!--当前mapper开启二级缓存,只写一个cache会报错-->

<!--这个更高级的配置,创建了一个 FIFO 缓存，每隔 60 秒刷新，最多可以存储结果对象或列表的 512 个引用，而且返回的对象被认为是只读的.
因此对它们进行修改可能会在不同线程中的调用者产生冲突。-->
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>


<!-- cache只作用在当前这个mapper文件中的才有效,
     注解无用
 -->
<select id="getStudentById" resultType="student">
select * from student where id = #{id}
</select>

会话关闭后,再查询同一个语句,

调用二级缓存,

最后只有一次查询语句

SqlSession sqlSession = MyBatisUtil.getSqlSession();
SqlSession sqlSession1 = MyBatisUtil.getSqlSession();

StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
StudentMapper mapper1 = sqlSession1.getMapper(StudentMapper.class);


mapper.getStudentById(1);
sqlSession.close();

mapper1.getStudentById(1);
sqlSession1.close();

Created connection 1332210474.
  
Preparing: select * from student where id = ?
Parameters: 1(Integer)
  
Total: 1

---

缓存原理

二级缓存没有找一级缓存,还没有再查询数据库

缓存就是为了提高查询的效率

<!-- useCache=false不使用缓存,最后就是查询两次 -->
<select id="getStudentById" resultType="student" useCache="false">

提示:二级缓存是事务性的。这意味着，当 SqlSession 完成并提交时，或是完成并回滚，但没有执行 flushCache=true 的 insert/delete/update 语句时，缓存会获得更新。

---

自定义缓存

导入依赖

<!--这个依赖加上后,log4j就不用能了-->
<dependency>
  <groupId>org.mybatis.caches</groupId>
  <artifactId>mybatis-ehcache</artifactId>
  <version>1.2.2</version>
</dependency>

实现Cache类后,就是一个自定义缓存策略类,知道就行了

import org.apache.ibatis.cache.Cache;

public class MyCache implements Cache {

  public String getId() {
    return null;
  }

  public void putObject(Object key, Object value) {

  }

  public Object getObject(Object key) {
    return null;
  }

  public Object removeObject(Object key) {
    return null;
  }

  public void clear() {

  }

  public int getSize() {
    return 0;
  }
}

使用自定义缓存

<cache type="com.changGe.li.mapper.MyCache"/>