Mybatis學習筆記，mybatis

Mybatis學習筆記，mybatis

一、資料庫欄位名與實體類屬性名稱不相同問題

1.1 準備資料表和資料

CREATE TABLE orders(order_id INT PRIMARY KEY AUTO_INCREMENT,order_no VARCHAR(20), order_price FLOAT);INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);

1.2 定義實體類

public class Order {private int id;private String orderNo;private float price;}

1.3 查詢資料實現

方式一: 通過在sql語句中定義別名<select id="selectOrder" parameterType="int" resultType="_Order">select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}</select>方式二: 通過<resultMap><select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap">select * from orders where order_id=#{id}</select><resultMap type="_Order" id="orderResultMap"><id property="id" column="order_id"/><result property="orderNo" column="order_no"/><result property="price" column="order_price"/></resultMap>

二、一對一關聯表查詢

2.1 建立表和資料

CREATE TABLE teacher(t_id INT PRIMARY KEY AUTO_INCREMENT, t_name VARCHAR(20));CREATE TABLE class(c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(20), teacher_id INT);ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);INSERT INTO teacher(t_name) VALUES('LS1');INSERT INTO teacher(t_name) VALUES('LS2');INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

提出需求：根據班級id查詢班級資訊（帶老師的資訊）

2.2 定義實體類

public class Teacher {private int id;private String name;}

public class Classes {private int id;private String name;private Teacher teacher;}

2.3 定義SQL對應檔ClassMapper.xml

<!-- 方式一：嵌套結果：使用嵌套結果映射來處理重複的聯合結果的子集         封裝聯表查詢的資料(去除重複的資料)select * from class c, teacher t where c.teacher_id=t.t_id and  c.c_id=1--><select id="getClass" parameterType="int" resultMap="ClassResultMap">select * from class c, teacher t where c.teacher_id=t.t_id and  c.c_id=#{id}</select><resultMap type="_Classes" id="ClassResultMap"><id property="id" column="c_id"/><result property="name" column="c_name"/><association property="teacher" javaType="_Teacher"><id property="id" column="t_id"/><result property="name" column="t_name"/></association></resultMap><!-- 方式二：巢狀查詢：通過執行另外一個SQL映射語句來返回預期的複雜類型SELECT * FROM class WHERE c_id=1;SELECT * FROM teacher WHERE t_id=1   //1 是上一個查詢得到的teacher_id的值--> <select id="getClass2" parameterType="int" resultMap="ClassResultMap2">select * from class where c_id=#{id} </select> <resultMap type="_Classes" id="ClassResultMap2"><id property="id" column="c_id"/><result property="name" column="c_name"/><association property="teacher" column="teacher_id" select="getTeacher"></association> </resultMap>  <select id="getTeacher" parameterType="int" resultType="_Teacher">SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id} </select>

三、一對多的關聯查詢

3.1 增加學生表

CREATE TABLE student(s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT);INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

由於學生表的加入，建立Student實體類，並修改Classes類，class與student是一對多的關係。

public class Student {private int id;private String name;}public class Classes {private int id;private String name;private Teacher teacher;private List<Student> students;}

3.2 定義對應檔

<!-- 方式一: 嵌套結果: 使用嵌套結果映射來處理重複的聯合結果的子集SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND  c.c_id=1 --><select id="getClass3" parameterType="int" resultMap="ClassResultMap3">select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and  c.c_id=#{id}</select><resultMap type="_Classes" id="ClassResultMap3"><id property="id" column="c_id"/><result property="name" column="c_name"/><association property="teacher" column="teacher_id" javaType="_Teacher"><id property="id" column="t_id"/><result property="name" column="t_name"/></association><!-- ofType指定students集合中的物件類型 --><collection property="students" ofType="_Student"><id property="id" column="s_id"/><result property="name" column="s_name"/></collection></resultMap><!-- 方式二：巢狀查詢：通過執行另外一個SQL映射語句來返回預期的複雜類型SELECT * FROM class WHERE c_id=1;SELECT * FROM teacher WHERE t_id=1   //1 是上一個查詢得到的teacher_id的值SELECT * FROM student WHERE class_id=1  //1是第一個查詢得到的c_id欄位的值 --> <select id="getClass4" parameterType="int" resultMap="ClassResultMap4">select * from class where c_id=#{id} </select> <resultMap type="_Classes" id="ClassResultMap4"><id property="id" column="c_id"/><result property="name" column="c_name"/><association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher2"></association><collection property="students" ofType="_Student" column="c_id" select="getStudent"></collection> </resultMap>  <select id="getTeacher2" parameterType="int" resultType="_Teacher">SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id} </select>  <select id="getStudent" parameterType="int" resultType="_Student">SELECT s_id id, s_name name FROM student WHERE class_id=#{id} </select>

四、動態SQL與模糊查詢

4.1 準備資料表

create table d_user(  id int primary key auto_increment,  name varchar(10),age int(3)); insert into d_user(name,age) values('Tom',12);  insert into d_user(name,age) values('Bob',13);  insert into d_user(name,age) values('Jack',18);

需求：實現多條件查詢使用者(姓名模糊比對, 年齡在指定的最小值到最大值之間)

4.2 查詢條件實體類ConditionUser

private String name;private int minAge;private int maxAge;

4.3 表實體類User

private int id;private String name;private int age;

4.4 SQL對應檔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="com.atguigu.day03_mybatis.test6.userMapper"><select id="getUser" parameterType="com.atguigu.day03_mybatis.test6.ConditionUser" resultType="com.atguigu.day03_mybatis.test6.User">select * from d_user where age>=#{minAge} and age<=#{maxAge}<if test='name!="%null%"'>and name like #{name}</if></select></mapper>

4.5 測試

String statement = "com.atguigu.day03_mybatis.test6.userMapper.getUser";List<User> list = sqlSession.selectList(statement, new ConditionUser("%a%", 1, 12));System.out.println(list);

Mybatis中可用的動態SQL標籤

五、Mybatis緩衝

正如大多數持久層架構一樣，MyBatis 同樣提供了一級緩衝和二級緩衝的支援
1. 一級緩衝: 基於PerpetualCache 的 HashMap本機快取，其儲存範圍為 Session，當 Session flush 或 close 之後，該Session中的所有 Cache 就將清空。
2. 二級緩衝與一級緩衝其機制相同，預設也是採用 PerpetualCache，HashMap儲存，不同在於其儲存範圍為 Mapper(Namespace)，並且可自訂儲存源，如 Ehcache。
3. 對於快取資料更新機制，當某一個範圍(一級緩衝Session/二級緩衝Namespaces)的進行了 C/U/D 操作後，預設該範圍下所有 select 中的緩衝將被clear。

5.1 一級緩衝

一級緩衝: 也就Session級的緩衝(預設開啟)

a. 一級緩衝: 也就Session級的緩衝(預設開啟)

b. 查詢條件是一樣的

c. 沒有執行過session.clearCache()清理緩衝或者session.close()

d. 沒有執行過增刪改的操作(這些操作都會清理緩衝)

5.2 二級緩衝

只需要在userMapper.xml檔案中添加一個標籤，即啟用二級緩衝：

<cache/>  <!—對應檔層級的緩衝 -->

5.3 補充說明

a. 映射語句檔案中的所有select語句將會被緩衝。 
b. 映射語句檔案中的所有insert，update和delete語句會重新整理緩衝。 
c. 緩衝會使用Least Recently Used（LRU，最近最少使用的）演算法來收回。 
d. 緩衝會根據指定的時間間隔來重新整理。 
e. 緩衝會儲存1024個對象

<cache eviction="FIFO"  //回收策略為先進先出flushInterval="60000" //自動重新整理時間60ssize="512" //最多緩衝512個引用對象readOnly="true"/> //唯讀