Mybatis学习笔记
栏目：数据库应用时间：2015-04-25 09:47:22
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"/> //只读
------分隔线----------------------------
------分隔线----------------------------
最新技术推荐
1cassandra 3.x官方文档(5)---探测器
2[置顶] Aerospike数据库实战(四) -- Aerospike 安装部署
3Oracle 12c for GIS解决方案
4[置顶] Aerospike数据库实战(五) -- Aerospike C Client 开发
5MySQL Group Replication [Single-Primary Mode] 详细搭建部署过程
6mysql进阶(三十)MySQL Workbench出错KERNELBASE.dll停止工作 + MySQL服务消失 + 服务启动失败解决方法
7[置顶] Aerospike数据库实战(六) -- 踩坑&经验总结