mybatis 从入门到总结

来源：互联网发布：linux日志级别编辑：程序博客网时间：2024/05/19 22:25

Mybatis

1. Mybatis介绍

MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。

MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录.

JDBCàMyBatisàHibernate

2. mybatis快速入门

编写第一个基于mybaits的测试例子:

2.1. 添加jar包

hamcrest-all-1.3.jar

junit-4.12.jar

log4j-1.2.17.jar

mybatis-3.3.0.jar

mysql-connector-java-5.1.39-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. 定义表所对应的实体类

package com.yr.mybatis.bean;

public class Users {

private Integerid;

private Stringname;

private Stringage;

public Integer getId() {

return id;

}

public void setId(Integerid) {

this.id =id;

}

public String getName() {

return name;

}

public void setName(Stringname) {

this.name =name;

}

public String getAge() {

return age;

}

public void setAge(Stringage) {

this.age =age;

}

@Override

public String toString() {

return "Users [id=" +id + ", name=" +name + ", age=" + age +"]";

}

2.4. 定义操作users表的sql映射文件usersMapper.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">

<!--

根据id查询得到一个user对象

id一定要唯一，在调用时也要用到

parameterType是参数类型，这里参数类型是int

resultType是结果返回类型，这里表示返回一个实体bean -->

select * from users where id = #{id}

</select>

</mapper>

2.5. 添加Mybatis的配置文件conf.xml

编写conf.xml需要注意配置文件的节点的顺序问题

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

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

2.6. 编写测试代码：执行定义的select语句

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Test;

import com.yr.mybatis.bean.Users;

public class Main {

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的sqlSession

SqlSession session =sessionFactory.openSession();

//映射sql的标识字符串

String statement ="com.yr.mybatis.mapper.usersMapper.getUser";

//执行查询返回一个唯一user对象的sql

Users user =session.selectOne(statement, 1);

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

}

2.7. 效果

3. 操作users表的CRUD

3.1. XML的实现,在上面的例子中继续改造

1) conf.xml中引入bean包路径方便usersMapper.xml中使用

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

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<typeAliases>

<package name="com.yr.mybatis.bean"/>

</typeAliases>

<!-- development : 开发模式

work : 工作模式-->

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

2) usesMapper.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">

<!--

根据id查询得到一个user对象

因为在conf.xml中配置了bean的路径，所以在这里的resultType只需要写bean的名称即可

-->

select * from users where id = #{id}

</select>

insert into users(name, age) values(#{name}, #{age})

</insert>

delete from users where id=#{id}

</delete>

update users set name=#{name},age=#{age} where id=#{id}

</update>

</mapper>

3) UserMain.java

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Test;

import com.yr.mybatis.bean.Users;

public class UserMain {

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的sqlSession

SqlSession session =sessionFactory.openSession();

//映射sql的标识字符串

String statement ="com.yr.mybatis.mapper.usersMapper.getUser";

//执行查询返回一个唯一user对象的sql

Users user =session.selectOne(statement, 1);

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

}

private SqlSessionFactory getFactory(){

String resource ="conf.xml";

//加载mybatis的配置文件（它也加载关联的映射文件）

Reader reader;

try {

reader = Resources.getResourceAsReader(resource);

//构建sqlSession的工厂

SqlSessionFactory sessionFactory =new SqlSessionFactoryBuilder().build(reader);

return sessionFactory;

} catch (IOExceptione) {

e.printStackTrace();

}

return null;

}

@Test

public void testAdd() {

SqlSessionFactory factory = getFactory();

//默认是手动提交的

SqlSession session =factory.openSession();

String statement ="com.yr.mybatis.mapper.usersMapper.addUser";

int insert =session.insert(statement ,new Users(-1,"KK4", 23));

//提交

session.commit();

session.close();

System.out.println(insert);

}

@Test

public void testUpate() {

SqlSessionFactory factory = getFactory();

//默认是手动提交的

SqlSession session =factory.openSession(true);

String statement ="com.yr.mybatis.mapper.usersMapper.updateUser";

int update =session.update(statement,new Users(4,"KK444", 25));

session.close();

System.out.println(update);

}

@Test

public void testDelete() {

SqlSessionFactory factory = getFactory();

//默认是手动提交的

SqlSession session =factory.openSession(true)；

String statement ="com.yr.mybatis.mapper.usersMapper.deleteUser";

int delete =session.delete(statement, 4);

session.close();

System.out.println(delete);

}

3.2. 注解的实现

1). 定义sql映射的接口

package com.yr.mybatis.test;

import java.util.List;

import org.apache.ibatis.annotations.Delete;

import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.Update;

import com.yr.mybatis.bean.Users;

public interface UsersMapper {

@Insert("insert into users(name, age) values(#{name}, #{age})")

public int add(Usersuser);

@Delete("delete from users where id=#{id}")

public int deleteById(int id);

@Update("update users set name=#{name},age=#{age} where id=#{id}")

public int update(Usersuser);

@Select("select * from users where id=#{id}")

public User getById(int id);

@Select("select * from users")

public List<Users> getAll();

}

2). 在conf.xml中注册这个映射接口

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

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

</typeAliases>

<!--

development : 开发模式

work : 工作模式

-->

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

3). AnnoMain

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Test;

import com.yr.mybatis.bean.Users;

* 测试: CRUD操作的注解的实现

public class AnnoMain {

private SqlSessionFactory getFactory(){

String resource ="conf.xml";

//加载mybatis的配置文件（它也加载关联的映射文件）

Reader reader;

try {

reader = Resources.getResourceAsReader(resource);

//构建sqlSession的工厂

SqlSessionFactory sessionFactory =new SqlSessionFactoryBuilder().build(reader);

return sessionFactory;

} catch (IOExceptione) {

e.printStackTrace();

}

return null;

}

@Test

public void testAdd() {

SqlSessionFactory factory = getFactory();

//默认是手动提交的

SqlSession session =factory.openSession(true);

UsersMapper mapper =session.getMapper(UsersMapper.class);

int add =mapper.add(new Users(-1,"ann1", 45));

System.out.println(add);

Users users =mapper.getById(1);

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

session.commit();

session.close();

}

3.3. 接口+xml的实现

创建表

CREATE TABLE pro(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), price FLOAT);

INSERT INTO pro(NAME, price) VALUES('苹果', 3.5);

INSERT INTO pro(NAME, price) VALUES('桃子', 6.8);

1). 定义sql映射的接口

package com.yr.mybatis.mapper.interf;

import com.yr.mybatis.bean.Pro;

/**

* 这里面的方法都需要在配置文件中与sql对应

public interface ProMapper {

public Pro getPro(int id);

}

2). 编写配置文件proMapper.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">

</resultMap>

SELECT * FROM pro WHERE id=#{id}

</select>

</mapper>

3). 在conf.xml中注册映射文件

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

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

</typeAliases>

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

4). InterfXmlMain

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Test;

import com.yr.mybatis.bean.Pro;

import com.yr.mybatis.mapper.interf.ProMapper;

* 接口类加mapper xml的方式使用mytbatis

public class InterfXmlMain {

private SqlSessionFactory getFactory(){

String resource = "conf.xml";

//加载mybatis的配置文件（它也加载关联的映射文件）

Reader reader;

try {

reader = Resources.getResourceAsReader(resource);

//构建sqlSession的工厂

SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

return sessionFactory;

} catch (IOException e) {

e.printStackTrace();

}

return null;

}

@Test

public void testAdd() {

SqlSessionFactory factory = getFactory();

//默认是手动提交的

SqlSession session = factory.openSession(true);

ProMapper proMapper = session.getMapper(ProMapper.class);

Pro pro = proMapper.getPro(1);

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

session.commit();

session.close();

}

4. 几个可以优化的地方

4.2. 为实体类定义别名,简化sql映射xml文件中的引用

</typeAliases>

<?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">

select * from users where id = #{id}

</select>

</mapper>

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

1. 添加jar:

log4j-1.2.17.jar（前面已添加）

2.1. log4j.properties(方式一)

log4j.properties，

log4j.rootLogger=DEBUG, Console

#Console

log4j.appender.Console=org.apache.log4j.ConsoleAppender

log4j.appender.Console.layout=org.apache.log4j.PatternLayout

log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n

log4j.logger.java.sql.ResultSet=INFO

log4j.logger.org.apache=INFO

log4j.logger.java.sql.Connection=DEBUG

log4j.logger.java.sql.Statement=DEBUG

log4j.logger.java.sql.PreparedStatement=DEBUG

2.2. log4j.xml(方式二)建议使用

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

<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

</layout>

</appender>

</logger>

</logger>

<root>

<appender-ref ref="STDOUT" />

</root>

</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. orderMapper.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">

<!--

通过别名对应到实体的属性中

-->

SELECT order_id id, order_no orderNo, order_price price FROM orders WHERE order_id=#{id}

</select>

SELECT * FROM orders WHERE order_id=#{id}

</select>

<!--

type对应实体类

id 唯一名称

property 实体类属性名

column 表字段

-->

</resultMap>

</mapper>

5.4. OrderMain:

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.yr.mybatis.bean.Order;

public class OrderMain {

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的sqlSession

SqlSession session = sessionFactory.openSession();

//映射sql的标识字符串

String statement = "com.yr.mybatis.mapper.xml.orderMapper.getOrder";

//执行查询返回一个唯一user对象的sql

Order order = session.selectOne(statement, 1);

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

order = session.selectOne(statement, 1);

System.out.println("第二次: "+order.toString());

session.clearCache();

order = session.selectOne(statement, 1);

System.out.println("第三次: "+order.toString());

}

6.实现关联表查询

6.1. 一对一关联parameterType为int类型

根据班级id查询班级信息(带老师的信息)

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);

2). 定义实体类：

package com.yr.mybatis.bean;

public class Classes {

private int id;

private Stringname;

private Teacherteacher;

public int getId() {

return id;

}

public void setId(int id) {

this.id =id;

}

public String getName() {

return name;

}

public void setName(Stringname) {

this.name =name;

}

public Teacher getTeacher() {

return teacher;

}

public void setTeacher(Teacherteacher) {

this.teacher =teacher;

}

package com.yr.mybatis.bean;

public class Teacher {

private int id;

private Stringname;

public int getId() {

return id;

}

public void setId(int id) {

this.id =id;

}

public String getName() {

return name;

}

public void setName(Stringname) {

this.name =name;

}

@Override

public String toString() {

return "Teacher [id=" +id + ", name=" +name + "]";

}

3). 定义sql映射文件ClassMapper.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">

</association>

</resultMap>

<!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据)

select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1

association表示查询另一个对像

-->

select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}

</select>

<association property="teacher" column="teacher_id"

select="getTeacher">

</association>

</resultMap>

<!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型

SELECT * FROM class WHERE c_id=1;

SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id的值 -->

select * from class where c_id=#{id}

</select>

SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}

</select>

</mapper>

4). 测试ClassMain

package com.yr.mybatis.test;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.yr.mybatis.bean.Classes;

* 测试: 一对一关联表查询

public class ClassMain {

public static void main(String[] args) {

SqlSessionFactory factory = MybatisUtils.getFactory();

SqlSession session = factory.openSession();

String statement = "com.yr.mybatis.mapper.xml.classMapper.getClass";

statement = "com.yr.mybatis.mapper.xml.classMapper.getClass2";

Classes c = session.selectOne(statement , 2);

System.out.println(c);

session.close();

}

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {

public static SqlSessionFactory getFactory() {

String resource ="conf.xml";

//加载mybatis的配置文件（它也加载关联的映射文件）

Reader reader;

try {

reader = Resources.getResourceAsReader(resource);

//构建sqlSession的工厂

SqlSessionFactory sessionFactory =new SqlSessionFactoryBuilder().build(reader);

return sessionFactory;

} catch (IOException e) {

e.printStackTrace();

}

return null;

}

6.2. 一对多关联parameterType为int类型

根据classId查询对应的班级信息,包括学生,老师

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);

2). 定义实体类：

package com.yr.mybatis.bean;

import java.util.List;

public class Classes {

private int id;

private Stringname;

private Teacherteacher;

private List<Student>studentsList;

public int getId() {

return id;

}

public void setId(int id) {

this.id =id;

}

public String getName() {

return name;

}

public void setName(Stringname) {

this.name =name;

}

public Teacher getTeacher() {

return teacher;

}

public void setTeacher(Teacherteacher) {

this.teacher =teacher;

}

@Override

public String toString() {

return "Classes [id=" +id + ", name=" +name + ", teacher=" + teacher + ", studentsList=" +studentsList +"]";

}

package com.yr.mybatis.bean;

public class Student {

private int id;

private Stringname;

public int getId() {

return id;

}

public void setId(int id) {

this.id =id;

}

public String getName() {

return name;

}

public void setName(Stringname) {

this.name =name;

}

@Override

public String toString() {

return "Student [id=" +id + ", name=" +name + "]";

}

3). 定义sql映射文件classMapper.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">

</association>

</resultMap>

<!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据) select * from class

c, teacher t where c.teacher_id=t.t_id and c.c_id=1

association表示查询另一个对像

-->

select *from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}

</select>

</association>

</resultMap>

<!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型 SELECT * FROM class WHERE c_id=1;

SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id的值 -->

select *

from class where c_id=#{id}

</select>

SELECT

t_id id, t_name name FROM teacher WHERE t_id=#{id}

</select>

<!-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集

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 -->

</association>

<!-- collection表示集合

property 对象中的属性名

ofType指定students集合中的对象类型 -->

</collection>

</resultMap>

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>

<!-- 方式二：嵌套查询：通过执行另外一个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字段的值 -->

<association property="teacher" column="teacher_id"

javaType="Teacher" select="getTeacher2"></association>

<collection property="studentsList" ofType="Student" column="c_id"

select="getStudent"></collection>

</resultMap>

select * from class where c_id=#{id}

</select>

SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}

</select>

SELECT s_id id, s_name name FROM student WHERE class_id=#{id}

</select>

</mapper>

4). 在conf.xml中配置

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

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

</typeAliases>

<!--

development : 开发模式

work : 工作模式

-->

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

5). 测试ClassOntToManyMain ：

package com.yr.mybatis.test;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.yr.mybatis.bean.Classes;

* 测试: 一对多关联表查询

public class ClassOntToManyMain {

public static void main(String[] args) {

SqlSessionFactory factory = MybatisUtils.getFactory();

SqlSession session = factory.openSession();

String statement = "com.yr.mybatis.mapper.xml.classMapper.getClass3";

//statement = "com.yr.mybatis.mapper.xml.classMapper.getClass4";

Classes c = session.selectOne(statement , 2);

System.out.println(c);

session.close();

}

7. 动态SQL与模糊查询parameterType为类类型

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" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from d_user where age>=#{minAge} and age<=#{maxAge}

</select>

</mapper>

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.yr.mybatis.mapper.xml.userMapper.getUser";

List<User> 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 $

CREATE PROCEDURE mybatis.ges_user_count(IN sex_id INT, OUT user_count INT)

BEGIN

IF sex_id=0 THEN

SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count;

ELSE

SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count;

END IF;

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

<!--

查询得到男性或女性的数量, 如果传入的是0就女性否则是男性

CALL mybatis.get_user_count(1, @user_count);

-->

call mybatis.get_user_count(?,?)

</select>

</parameterMap>

</mapper>

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 就将清空。

注:

a. 一级缓存是默认开启的,可以通过session.clearCache()来清除缓存;

b. 另:执行过增删改的操作后也会自动清除一级缓存.

c. Session.close()后一级缓存也会被清除(session关闭后,再创建一个session就不是同一个

session则就不是同一个缓存空间,那么之前的session的一级缓存自然也没有了)

2. 二级缓存与一级缓存其机制相同，默认也是采用 PerpetualCache，HashMap存储，

不同在于其存储作用域为 Mapper(Namespace)，并且可自定义存储源，如 Ehcache。

3. 对于缓存数据更新机制，当某一个作用域(一级缓存Session/二级缓存Namespaces)的进行了 C/U/D

操作后，默认该作用域下所有 select 中的缓存将被clear。

一级缓存是session级的缓存,各session之间独立

二级缓存是mapper域的(namespace),同一mapper同一查询条件,session之间共享

参考文献:http://www.360doc.com/content/15/1205/07/29475794_518018352.shtml

9.2. Mybatis一级缓存

工作原理

程序(表在第5章已创建)

package com.yr.mybatis.bean;

public class Order {

private int id;

private StringorderNo;

private float price;

public Order(int id, String orderNo,float price) {

super();

this.id =id;

this.orderNo =orderNo;

this.price =price;

}

public Order() {

super();

}

public int getId() {

return id;

}

public void setId(int id) {

this.id =id;

}

public String getOrderNo() {

return orderNo;

}

public void setOrderNo(StringorderNo) {

this.orderNo =orderNo;

}

public float getPrice() {

return price;

}

public void setPrice(float price) {

this.price =price;

}

@Override

public String toString() {

return "Order [id=" +id +", orderNo=" +orderNo + ", price=" + price + "]";

}

<?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">

<!-- 通过别名对应到实体的属性中 flushCache="true"表示清空一级缓存

useCache=”false” 表示不使用用户缓存 -->

SELECT order_id id, order_no orderNo, order_price price FROM orders

WHERE order_id=#{id}

</select>

SELECT * FROM orders WHERE order_id=#{id}

</select>

update orders set order_no=#{orderNo}, order_price=#{price} where order_id=#{id}

</update>

<!--

type对应实体类

id 唯一名称

property 实体类属性名

column 表字段

-->

</resultMap>

</mapper>

OrderMain(clearCache)

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.yr.mybatis.bean.Order;

public class OrderMain {

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的sqlSession

SqlSession session = sessionFactory.openSession();

//映射sql的标识字符串

String statement = "com.yr.mybatis.mapper.xml.orderMapper.getOrder";

//执行查询返回一个唯一user对象的sql

Order order = session.selectOne(statement, 1);

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

order = session.selectOne(statement, 1);

System.out.println("第二次: "+order.toString());

session.clearCache();

order = session.selectOne(statement, 1);

System.out.println("第三次: "+order.toString());

}

执行结果

Fri Apr 21 09:00:50 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

DEBUG 04-21 09:00:52,154 Created connection 504527234. (PooledDataSource.java:387)

DEBUG 04-21 09:00:52,157 Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e127982] (JdbcTransaction.java:102)

DEBUG 04-21 09:00:52,166 ==> Preparing: SELECT order_id id, order_no orderNo, order_price price FROM orders WHERE order_id=? (BaseJdbcLogger.java:142)

DEBUG 04-21 09:00:52,781 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:142)

DEBUG 04-21 09:00:53,912 <== Total: 1 (BaseJdbcLogger.java:142)

Order [id=1, orderNo=aaaa, price=23.0]

第二次: Order [id=1, orderNo=aaaa, price=23.0]

//第二次查询并没有发起sql执行.这就说明是直接从缓存中取的数据

DEBUG 04-21 09:24:44,248 ==> Preparing: SELECT order_id id, order_no orderNo, order_price price FROM orders WHERE order_id=? (BaseJdbcLogger.java:142)

DEBUG 04-21 09:24:44,251 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:142)

DEBUG 04-21 09:24:44,270 <== Total: 1 (BaseJdbcLogger.java:142)

第三次: Order [id=1, orderNo=aaaa, price=23.0]

//执行第三次查询时,因为先执行了session.clearCache();缓存被清掉了,所以又发起了一次执行sql的请求.

OrderMain(支持增删改操作)

package com.yr.mybatis.test;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.yr.mybatis.bean.Order;

public class OrderMain {

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的sqlSession

SqlSession session = sessionFactory.openSession();

//映射sql的标识字符串

String statement = "com.yr.mybatis.mapper.xml.orderMapper.getOrder";

//执行查询返回一个唯一user对象的sql

Order order = session.selectOne(statement, 1);

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

order = session.selectOne(statement, 1);

System.out.println("第二次: "+order.toString());

/*session.clearCache();

order = session.selectOne(statement, 1);

System.out.println("第三次: "+order.toString());*/

//不清缓存,而是执行一次增加/删除/修改的动作,这里执行修改

session.update("com.yr.mybatis.mapper.xml.orderMapper.update",new Order(1,"新数据",12.3F));

session.commit();

order = session.selectOne(statement, 1);

System.out.println("第四次: "+order.toString());

}

执行结果

Fri Apr 21 09:29:03 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

DEBUG 04-21 09:29:05,230 Created connection 504527234. (PooledDataSource.java:387)

DEBUG 04-21 09:29:05,232 Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e127982] (JdbcTransaction.java:102)

DEBUG 04-21 09:29:05,243 ==> Preparing: SELECT order_id id, order_no orderNo, order_price price FROM orders WHERE order_id=? (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:05,806 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:05,948 <== Total: 1 (BaseJdbcLogger.java:142)

Order [id=1, orderNo=aaaa, price=23.0]

第二次: Order [id=1, orderNo=aaaa, price=23.0]

DEBUG 04-21 09:29:05,956 ==> Preparing: update orders set order_no=?, order_price=? where order_id=? (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:05,959 ==> Parameters: 新数据(String), 12.3(Float), 1(Integer) (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:06,407 <== Updates: 1 (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:06,408 Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e127982] (JdbcTransaction.java:71)

DEBUG 04-21 09:29:06,736 ==> Preparing: SELECT order_id id, order_no orderNo, order_price price FROM orders WHERE order_id=? (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:06,737 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:142)

DEBUG 04-21 09:29:06,741 <== Total: 1 (BaseJdbcLogger.java:142)

第四次: Order [id=1, orderNo=新数据, price=12.3]

执行增删改操作是会清除缓存的

9.3. Mybatis二级缓存

在配置文件中添加

Order类要实现序列化接口

package com.yr.mybatis.bean;

import java.io.Serializable;

public class Orderimplements Serializable{

private static final long serialVersionUID = 1L;

private int id;

private StringorderNo;

private float price;

public Order(int id, String orderNo,float price) {

super();

this.id =id;

this.orderNo =orderNo;

this.price =price;

}

public Order() {

super();

}

public int getId() {

return id;

}

public void setId(int id) {

this.id =id;

}

public String getOrderNo() {

return orderNo;

}

public void setOrderNo(StringorderNo) {

this.orderNo =orderNo;

}

public float getPrice() {

return price;

}

public void setPrice(float price) {

this.price =price;

}

@Override

public String toString() {

return "Order [id=" +id + ", orderNo=" +orderNo + ", price=" + price + "]";

}

OrderMain中添加以下方法

/**

* 二级缓存,用的是不同的session所以这里不是一级缓存

* @throws IOException

@Test

public void testCache2() throws IOException {

String resource ="conf.xml";

//加载mybatis的配置文件（它也加载关联的映射文件）

Reader reader = Resources.getResourceAsReader(resource);

//构建sqlSession的工厂

SqlSessionFactory sessionFactory =new SqlSessionFactoryBuilder().build(reader);

//创建能执行映射文件中sql的sqlSession

SqlSession session =sessionFactory.openSession();

String statement ="com.yr.mybatis.mapper.xml.orderMapper.getOrder";

Order order =session.selectOne(statement, 1);

//session.commit(); //这里一定要提交或close(),提交或关闭后数据才会缓存到二级缓存中去

session.close();

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

SqlSession session2 =sessionFactory.openSession();

Order order2 =session.selectOne(statement, 1);

session2.commit();//这里可以不提交或关闭

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

}

执行结果

Fri Apr 21 14:29:41 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

DEBUG 04-21 14:29:43,780 Created connection 1897115967. (PooledDataSource.java:387)

DEBUG 04-21 14:29:43,784 Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7113b13f] (JdbcTransaction.java:102)

DEBUG 04-21 14:29:43,873 ==> Preparing: SELECT order_id id, order_no orderNo, order_price price FROM orders WHERE order_id=? (BaseJdbcLogger.java:142)

DEBUG 04-21 14:29:44,988 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:142)

DEBUG 04-21 14:29:46,236 <== Total: 1 (BaseJdbcLogger.java:142)

DEBUG 04-21 14:29:46,264 Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7113b13f] (JdbcTransaction.java:124)

DEBUG 04-21 14:29:46,297 Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7113b13f] (JdbcTransaction.java:92)

DEBUG 04-21 14:29:46,298 Returned connection 1897115967 to pool. (PooledDataSource.java:344)

Order [id=1, orderNo=新数据, price=12.3]

DEBUG 04-21 14:29:46,305 Cache Hit Ratio[com.yr.mybatis.mapper.xml.orderMapper]: 0.5 (LoggingCache.java:62)

Order [id=1, orderNo=新数据, price=12.3]

Cache Hit Ratio 表示二级缓存生效,几率是0.5

补充说明

1. 映射语句文件中的所有select语句将会被缓存。

2. 映射语句文件中的所有insert，update和delete语句会刷新缓存。

3. 缓存会使用Least Recently Used（LRU，最近最少使用的）算法来收回。

4. 缓存会根据指定的时间间隔来刷新。

5. 缓存会存储1024个对象

<cache

eviction="FIFO" //回收策略为先进先出

flushInterval="60000" //自动刷新时间60s

size="512" //最多缓存512个引用对象

readOnly="true"/> //只读

10. spring集成mybatis

10.1. 添加Jar包

【mybatis】

mybatis-3.2.0.jar

mybatis-spring-1.1.1.jar

log4j-1.2.17.jar

【spring】

spring-aop-3.2.0.RELEASE.jar

spring-beans-3.2.0.RELEASE.jar

spring-context-3.2.0.RELEASE.jar

spring-core-3.2.0.RELEASE.jar

spring-expression-3.2.0.RELEASE.jar

spring-jdbc-3.2.0.RELEASE.jar

spring-test-3.2.4.RELEASE.jar

spring-tx-3.2.0.RELEASE.jar

aopalliance-1.0.jar

cglib-nodep-2.2.3.jar

commons-logging-1.1.1.jar

【MYSQL驱动包】

mysql-connector-java-5.0.4-bin.jar

10.2. 数据库表

CREATE TABLE s_user(

user_id INT AUTO_INCREMENT PRIMARY KEY,

user_name VARCHAR(30),

user_birthday DATE,

user_salary DOUBLE

)

10.3. 实体类: User

public class User{

private int id;

private Stringname;

private Datebirthday;

private double salary;

//set,get方法

}

10.4. DAO接口: UserMapper (XXXMapper)

public interface UserMapper {

void save(User user);

void update(User user);

void delete(int id);

User findById(int id);

List<User> findAll();

}

10.5. 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">

</resultMap>

insert into s_user(user_name,user_birthday,user_salary)

values(#{name},#{birthday},#{salary})

</insert>

update s_user

set user_name = #{name},

user_birthday = #{birthday},

user_salary = #{salary}

where user_id = #{id}

</update>

delete from s_user

where user_id = #{id}

</delete>

select *

from s_user

where user_id = #{id}

</select>

select *

from s_user

</select>

</mapper>

10.6. spring的配置文件: beans.xml

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

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:p="http://www.springframework.org/schema/p"

xmlns:context="http://www.springframework.org/schema/context"

xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="

http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-3.2.xsd

http://www.springframework.org/schema/context

http://www.springframework.org/schema/context/spring-context-3.2.xsd

http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

<bean id="dataSource"

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

</bean>

<!--

2. mybatis的SqlSession的工厂: SqlSessionFactoryBean

dataSource / typeAliasesPackage

-->

</bean>

<!--

3. mybatis自动扫描加载Sql映射文件 : MapperScannerConfigurer

sqlSessionFactory / basePackage

-->

</bean>

</bean>

<tx:annotation-driven transaction-manager="txManager" />

</beans>

10.7. mybatis的配置文件: mybatis-config.xml

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

<!DOCTYPE configuration

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

</configuration>

10.8. 测试

@RunWith(SpringJUnit4ClassRunner.class)//使用Springtest测试框架

@ContextConfiguration("/beans.xml")//加载配置

public class SMTest {

@Autowired //注入

private UserMapperuserMapper;

@Test

public void save() {

User user = new User();

user.setBirthday(new Date());

user.setName("marry");

user.setSalary(300);

userMapper.save(user);

System.out.println(user.getId());

}

@Test

public void update() {

User user = userMapper.findById(2);

user.setSalary(2000);

userMapper.update(user);

}

@Test

public void delete() {

userMapper.delete(3);

}

@Test

public void findById() {

User user = userMapper.findById(1);

System.out.println(user);

}

@Test

public void findAll() {

List<User> users = userMapper.findAll();

System.out.println(users);

}

Mybatis总结

阅读全文

0 0