Mybatis

1. Mybatis介绍

MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录.
JDBC-àdbutils(自动封装)-àMyBatis-àHibernate

2. mybatis快速入门
Jar包+数据库表+mybatis-config.Xml配置文件+实体类+mapper.xml+Dao接口+测试Session层
编写第一个基于mybaits的测试例子:
2.1. 添加jar包
【mybatis】mybatis-3.1.1.jar 【MYSQL驱动包】mysql-connector-java-5.1.7-bin.jar

2.2. 建库+表
create database mybatis;use mybatis;CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);INSERT INTO users(NAME, age) VALUES(‘Tom’, 12);INSERT INTO users(NAME, age) VALUES(‘Jack’, 11);

2.3. 添加Mybatis的配置文件conf.xml（第一层）

<?xml version="1.0" encoding="UTF-8"?>

2.4. 定义表所对应的实体类
public class User {private int id;private String name;private int age; //get,set方法等}

2.5. 定义操作users表的sql映射文件userMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

2.6. 在conf.xml文件中注册userMapper.xml文件

2.7. 编写测试代码：执行定义的select语句
public class Test {public static void main(String[] args) throws IOException {String resource = “conf.xml”;//加载mybatis的配置文件（它也加载关联的映射文件）Reader reader = Resources.getResourceAsReader(resource);//构建sqlSession的工厂SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);//创建能执行映射文件中sql的sqlSessionSqlSession session = sessionFactory.openSession();//映射sql的标识字符串String statement = “com.hqyj.mybatis.bean.userMapper”+".selectUser";//执行查询返回一个唯一user对象的sqlUser user = session.selectOne(statement, 1);System.out.println(user);}}

3. 操作users表的CRUD
3.1. XML的实现
1). 定义sql映射xml文件：
insert into users(name, age) values(#{name}, #{age}); delete from users where id=#{id}update users set name=#{name},age=#{age} where id=#{id}select * from users where id=#{id}select * from users

2). 在config.xml中注册这个映射文件

3). 在dao中调用：
public User getUserById(int id) {SqlSession session = sessionFactory.openSession();User user = session.selectOne(URI+".selectUser", id);return user;}

3.2. 注解的实现
1). 定义sql映射的接口
public interface UserMapper {@Insert(“insert into users(name, age) values(#{name}, #{age})”)public int insertUser(User user); @Delete(“delete from users where id=#{id}”)public int deleteUserById(int id);@Update(“update users set name=#{name},age=#{age} where id=#{id}”)public int updateUser(User user); @Select(“select * from users where id=#{id}”)public User getUserById(int id); @Select(“select * from users”)public List getAllUser();}

2). 在config中注册这个映射接口（跳过mapper.xml）

3). 在dao类中调用
public User getUserById(int id) {SqlSession session = sessionFactory.openSession();UserMapper mapper = session.getMapper(UserMapper.class);User user = mapper.getUserById(id);return user;}

4. 几个可以优化的地方
4.1. 连接数据库的配置单独放在一个properties文件中
mybatis.Xml
//mybatis连接properties 语句：
db.properties文件（file类型）
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/xcwsystemdbusername=rootpassword=820938
注：db.properties无双引号和逗号
4.2. 为实体类定义别名,简化sql映射xml文件中的引用

4.3. 可以在src下加入log4j的配置文件,打印日志信息

1. 添加jar包:log4j-1.2.16.jar2.log4j配置2.1. log4j.properties(方式一) log4j.properties，log4j.rootLogger=DEBUG, Console#Consolelog4j.appender.Console=org.apache.log4j.ConsoleAppenderlog4j.appender.Console.layout=org.apache.log4j.PatternLayoutlog4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%nlog4j.logger.java.sql.ResultSet=INFOlog4j.logger.org.apache=INFOlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG 2.2. log4j.xml(方式二) <?xml version="1.0" encoding="UTF-8" ?><log4j:configuration xmlns:log4j=“http://jakarta.apache.org/log4j/”><param name=“ConversionPattern"value=”%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" /></log4j:configuration>

5. 解决字段名与实体类属性名不相同的冲突
5.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);
5.2. 定义实体类：
public class Order {private int id;private String orderNo;private float price;}
5.3. 实现getOrderById(id)的查询：
方式一: 通过在sql语句中定义别名select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}方式二: 通过select * from orders where order_id=#{id}

6.实现关联表查询
6.1. 一对一关联
1). 提出需求
根据班级id查询班级信息(带老师的信息)
2). 创建表和数据
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);
3). 定义实体类：
public class Teacher {private int id;private String name;}public class Classes {private int id;private String name;private Teacher teacher;}

4). 定义sql映射文件ClassMapper.xml

select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}// property为成员变量javaType为实体类 select * from class where c_id=#{id} SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}

5). 测试
@Testpublic void testOO() {SqlSession sqlSession = factory.openSession();Classes c = sqlSession.selectOne(“com.hqyj.day03_mybatis.test5.OOMapper.getClass”, 1);System.out.println©;} @Testpublic void testOO2() {SqlSession sqlSession = factory.openSession();Classes c = sqlSession.selectOne(“com.hqyj.day03_mybatis.test5.OOMapper.getClass2”, 1);System.out.println©;}

6.2. 一对多关联
1). 提出需求
根据classId查询对应的班级信息,包括学生,老师
2). 创建表和数据：
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);

3). 定义实体类：
public class Student {private int id;private String name;} public class Classes {private int id;private String name;private Teacher teacher;private List students;}
4). 定义sql映射文件ClassMapper.xml

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 * from class where c_id=#{id} SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id} SELECT s_id id, s_name name FROM student WHERE class_id=#{id}

5). 测试：
@Testpublic void testOM() {SqlSession sqlSession = factory.openSession();Classes c = sqlSession.selectOne(“com.hqyj.day03_mybatis.test5.OOMapper.getClass3”, 1);System.out.println©;} @Testpublic void testOM2() {SqlSession sqlSession = factory.openSession();Classes c = sqlSession.selectOne(“com.hqyj.day03_mybatis.test5.OOMapper.getClass4”, 1);System.out.println©;}

7. 动态SQL与模糊查询
7.1. 提出需求:
实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)
7.2. 准备数据表和数据:
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);
7.3. ConditionUser(查询条件实体类)
private String name;private int minAge;private int maxAge;

7.4. User(表实体类)
private int id;private String name;private int age;

7.5. userMapper.xml(映射文件)

<?xml version="1.0" encoding="UTF-8" ?>select * from d_user where age>=#{minAge} and age<=#{maxAge}and name like #{name}

7.6. UserTest(测试)
public class UserTest { public static void main(String[] args) throws IOException {Reader reader = Resources.getResourceAsReader(“conf.xml”);SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);SqlSession sqlSession = sessionFactory.openSession();String statement = “com.hqyj.day03_mybatis.test6.userMapper.getUser”;List list = sqlSession.selectList(statement, new ConditionUser("%a%", 1, 12));System.out.println(list);}}

MyBatis中可用的动态SQL标签

8.调用存储过程
8.1. 提出需求:
查询得到男性或女性的数量, 如果传入的是0就女性否则是男性
8.2. 准备数据库表和存储过程:
create table p_user( id int primary key auto_increment, name varchar(10),sex char(2)); insert into p_user(name,sex) values(‘A’,“男”); insert into p_user(name,sex) values(‘B’,“女”); insert into p_user(name,sex) values(‘C’,“男”); #创建存储过程(查询得到男性或女性的数量, 如果传入的是0就女性否则是男性)DELIMITER $CREATEPROCEDUREmybatis.ge{s}_{u}se{r}_{c}ount(INse{x}_{i}dINT,OUTuse{r}_{c}ountINT)BEGINIFse{x}_{i}d=0THENSELECTCOUNT(\ast )FROMmybatis.p_{u}serWHERE{p}_{u}ser.sex{=}^{\prime }{女}^{\prime }INTOuse{r}_{c}ount;ELSESELECTCOUNT(\ast )FROMmybatis.p_{u}serWHERE{p}_{u}ser.sex{=}^{\prime }{男}^{\prime }INTOuse{r}_{c}ount;ENDIF;END$ #调用存储过程DELIMITER ;SET @user_count = 0;CALL mybatis.ges_user_count(1, @user_count);SELECT @user_count;
8.3. 创建表的实体类
public class User {private String id;private String name;private String sex;}

8.4. userMapper.xml
call mybatis.get_user_count(?,?)

8.5. 测试调用:
Map<String, Integer> paramMap = new HashMap<>();paramMap.put(“sex_id”, 0); session.selectOne(statement, paramMap);Integer userCount = paramMap.get(“user_count”);System.out.println(userCount);

9. Mybatis缓存
9.1. 理解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。

9.2. Mybatis一级缓存

1. 提出需求:
   根据id查询对应的用户记录对象
   2). 准备数据库表和数据
   CREATE TABLE c_user(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT);INSERT INTO c_user(NAME, age) VALUES(‘Tom’, 12);INSERT INTO c_user(NAME, age) VALUES(‘Jack’, 11);
   3). 创建表的实体类
   public class User implements Serializable{ private int id;private String name;private int age;}
   4). userMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> select * from c_user where id=#{id} update c_user setname=#{name}, age=#{age} where id=#{id}

5). 测试:

/* * 一级缓存: 也就Session级的缓存(默认开启) /@Testpublic void testCache1() {SqlSession session = MybatisUtils.getSession();String statement = “com.hqyj.mybatis.test8.userMapper.getUser”;User user = session.selectOne(statement, 1);System.out.println(user);/ * 一级缓存默认就会被使用 //user = session.selectOne(statement, 1);System.out.println(user);// 1. 必须是同一个Session,如果session对象已经close()过了就不可能用了 //session = MybatisUtils.getSession();user = session.selectOne(statement, 1);System.out.println(user);// 2. 查询条件是一样的 //user = session.selectOne(statement, 2);System.out.println(user);// 3. 没有执行过session.clearCache()清理缓存 //session.clearCache();user = session.selectOne(statement, 2);System.out.println(user);// 4. 没有执行过增删改的操作(这些操作都会清理缓存) *//session.update(“com.hqyj.mybatis.test8.userMapper.updateUser”,new User(2, “user”, 23));user = session.selectOne(statement, 2);System.out.println(user);/}

9.3. Mybatis二级缓存
1). 添加一个在userMapper.xml中

2). 测试
/* * 测试二级缓存 */@Testpublic void testCache2() {String statement = “com.hqyj.mybatis.test8.userMapper.getUser”; SqlSession session = MybatisUtils.getSession();User user = session.selectOne(statement, 1);session.commit();System.out.println(“user=”+user);SqlSession session2 = MybatisUtils.getSession();user = session2.selectOne(statement, 1);session.commit();System.out.println(“user2=”+user);}
3). 补充说明

1. 映射语句文件中的所有select语句将会被缓存。2. 映射语句文件中的所有insert，update和delete语句会刷新缓存。 3. 缓存会使用Least Recently Used（LRU，最近最少使用的）算法来收回。 4. 缓存会根据指定的时间间隔来刷新。5. 缓存会存储1024个对象

<cacheeviction=“FIFO” //回收策略为先进先出flushInterval=“60000” //自动刷新时间60ssize=“512” //最多缓存512个引用对象readOnly=“true”/> //只读