Mybatis學習筆記
來源：程序員人生 發布時間：2015-04-25 09:47:22 閱讀次數：4012次
1、數據庫字段名與實體類屬性名不相同問題
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 查詢數據實現
方式1: 通過在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>
方式2: 通過<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對1關聯表查詢
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
<!--
方式1：嵌套結果：使用嵌套結果映照來處理重復的聯合結果的子集
封裝聯表查詢的數據(去除重復的數據)
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>
<!--
方式2：嵌套查詢：通過履行另外1個SQL映照語句來返回預期的復雜類型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //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對多的關聯查詢
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是1對多的關系。
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 定義映照文件
<!--
方式1: 嵌套結果: 使用嵌套結果映照來處理重復的聯合結果的子集
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>
<!--
方式2：嵌套查詢：通過履行另外1個SQL映照語句來返回預期的復雜類型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上1個查詢得到的teacher_id的值
SELECT * FROM student WHERE class_id=1 //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>
4、動態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⑻" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis⑶-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標簽
5、Mybatis緩存
正如大多數持久層框架1樣，MyBatis 一樣提供了1級緩存和2級緩存的支持
1. 1級緩存: 基于PerpetualCache 的 HashMap本地緩存，其存儲作用域為 Session，當 Session flush 或 close 以后，該Session中的所有 Cache 就將清空。
2. 2級緩存與1級緩存其機制相同，默許也是采取 PerpetualCache，HashMap存儲，不同在于其存儲作用域為 Mapper(Namespace)，并且可自定義存儲源，如 Ehcache。
3. 對緩存數據更新機制，當某1個作用域(1級緩存Session/2級緩存Namespaces)的進行了 C/U/D 操作后，默許該作用域下所有 select 中的緩存將被clear。
5.1 1級緩存
1級緩存: 也就Session級的緩存(默許開啟)
a. 1級緩存: 也就Session級的緩存(默許開啟)
b. 查詢條件是1樣的
c. 沒有履行過session.clearCache()清算緩存或session.close()
d. 沒有履行過增刪改的操作(這些操作都會清算緩存)
5.2 2級緩存
只需要在userMapper.xml文件中添加1個標簽，即啟用2級緩存：
<cache/> <!―映照文件級別的緩存 -->
5.3 補充說明
a. 映照語句文件中的所有select語句將會被緩存。
b. 映照語句文件中的所有insert，update和delete語句會刷新緩存。
c. 緩存會使用Least Recently Used（LRU，最近最少使用的）算法來收回。
d. 緩存會根據指定的時間間隔來刷新。
e. 緩存會存儲1024個對象
<cache
eviction="FIFO" //回收策略為先進先出
flushInterval="60000" //自動刷新時間60s
size="512" //最多緩存512個援用對象
readOnly="true"/> //只讀
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
