MyBatis框架二 Li.064

接口代理方式实现Dao层

• 接口代理方式-实现规则
  • 传统方式实现Dao 层，我们既要写接口，还要与实现类。而MyBatis框架可以帮助我们省略编写 Dao 层接口实现类的步骤
  • 程序员只需要编写接口，由MyBatis 框架根据接口的定义来创建该接口的动态代理对象。
• 实现规则
  • 映射配置文件中的名称空间必须和 Dao 层接口的全类名相同。
  • 映射配置文件中的增删改查标签的 id 属性必须和 Dao 层接口的方法名相同。
  • 映射配置文件中的增删改查标签的 parameterType 属性必须和 Dao 层接口方法的参数相同。
  • 映射配置文件中的增删改查标签的 resultType 属性必须和 Dao 层接口方法的返回值相同。
• 获取动态代理对象
  • SqlSession功能类中的getMapper()方法

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTA Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
mapper: 核心根标签
namespace属性: 名称空间
-->
<mapper namespace="com.lizicai.mapper.StudentMapper">
<!--
    select: 查询功能的标签
    id属性: 唯一标识
    resultType属性: 指定结果映射对象类型
    parameterType属性: 指定参数映射对象类型
-->
    <select id="selectAll" resultType="student">
        SELECT * FROM student
    </select>

    <select id="selectById" resultType="student" parameterType="int">
        SELECT * FROM student WHERE id = #{id}
    </select>

    <insert id="insert" parameterType="student">
        INSERT INTO student VALUES (#{id},#{name},#{age})
    </insert>

    <update id="update" parameterType="student">
        UPDATE student SET name=#{name},age=#{age} WHERE id=#{id}
    </update>

    <delete id="delete" parameterType="int">
        DELETE FROM student WHERE id=#{id};
    </delete>
</mapper

package com.lizicai.service.impl;

import com.lizicai.bean.Student;
import com.lizicai.mapper.StudentMapper;
import com.lizicai.service.StudentService;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class StudentServiceImpl implements StudentService {

    // 查询全部
    @Override
    public List<Student> selectAll() {
        List<Student> list = null;
        InputStream is = null;
        SqlSession sqlSession = null;

        try{
            // 1. 加载核心配置文件
            is = Resources.getResourceAsStream("MyBatisConfig.xml");

            // 2. 获取SqlSession工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

            // 3. 通过工厂对象获取SqlSession对象
            sqlSession = sqlSessionFactory.openSession(true);

            // 4. 获取StudentMapper接口的实现类对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

            // 5. 通过实现类对象调用方法, 接收结果
            list = mapper.selectAll();

        }catch (IOException e){
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if( sqlSession != null){
                sqlSession.close();
            }
            if( is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

        // 7. 返回结果
        return list;
    }

    // 根据id查询
    @Override
    public Student selectById(Integer id) {
        Student stu = null;
        InputStream is = null;
        SqlSession sqlSession = null;

        try{
            // 1. 加载核心配置文件
            is = Resources.getResourceAsStream("MyBatisConfig.xml");

            // 2. 获取SqlSession工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

            // 3. 通过工厂对象获取SqlSession对象
            sqlSession = sqlSessionFactory.openSession(true);

            // 4. 获取StudentMapper接口的实现类对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

            // 5. 通过实现类对象调用方法, 接收结果
            stu = mapper.selectById(id);

        }catch (IOException e){
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if( sqlSession != null){
                sqlSession.close();
            }
            if( is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

        // 7. 返回结果
        return stu;
    }

    @Override
    public Integer insert(Student stu) {
        Integer result = null;
        InputStream is = null;
        SqlSession sqlSession = null;

        try{
            // 1. 加载核心配置文件
            is = Resources.getResourceAsStream("MyBatisConfig.xml");

            // 2. 获取SqlSession工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

            // 3. 通过工厂对象获取SqlSession对象
            sqlSession = sqlSessionFactory.openSession(true);

            // 4. 获取StudentMapper接口的实现类对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

            // 5. 通过实现类对象调用方法, 接收结果
            result = mapper.insert(stu);

        }catch (IOException e){
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if( sqlSession != null){
                sqlSession.close();
            }
            if( is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

        // 7. 返回结果
        return result;
    }

    @Override
    public Integer update(Student stu) {
        Integer result = null;
        InputStream is = null;
        SqlSession sqlSession = null;

        try{
            // 1. 加载核心配置文件
            is = Resources.getResourceAsStream("MyBatisConfig.xml");

            // 2. 获取SqlSession工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

            // 3. 通过工厂对象获取SqlSession对象
            sqlSession = sqlSessionFactory.openSession(true);

            // 4. 获取StudentMapper接口的实现类对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

            // 5. 通过实现类对象调用方法, 接收结果
            result = mapper.update(stu);

        }catch (IOException e){
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if( sqlSession != null){
                sqlSession.close();
            }
            if( is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

        // 7. 返回结果
        return result;
    }

    @Override
    public Integer delete(Integer id) {
        Integer result = null;
        InputStream is = null;
        SqlSession sqlSession = null;

        try{
            // 1. 加载核心配置文件
            is = Resources.getResourceAsStream("MyBatisConfig.xml");

            // 2. 获取SqlSession工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);

            // 3. 通过工厂对象获取SqlSession对象
            sqlSession = sqlSessionFactory.openSession(true);

            // 4. 获取StudentMapper接口的实现类对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

            // 5. 通过实现类对象调用方法, 接收结果
            result = mapper.delete(id);

        }catch (IOException e){
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if( sqlSession != null){
                sqlSession.close();
            }
            if( is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

        // 7. 返回结果
        return result;
    }
}

动态SQL

• 动态SQL介绍
  • MyBatis映射配置文件中, 前面SQL都是简单的, 有些业务逻辑复杂时, SQL是动态变化, 此时前面学习的SQL就不能满足要求了
• 多条件查询
  • SELECT * FORM student WHERE id=? AND name=? AND age=?;
  • SELECT * FORM student WHERE id=? AND name=?;
• 动态SQL标签
  • : 条件判断标签
  • : 循环遍历标签

标签

• : 条件标签. 如果有动态条件, 则使用访标签代替where关键字
• : 条件判断标签

<if test="条件判断">
    查询条件拼接
</if>

    <select id="selectCondition" resultType="student" parameterType="student">
        SELECT * FROM student
        <where>
            <if test="id != null">
                id=#{id}
            </if>
            <if test="name != null">
                AND name=#{name}
            </if>
            <if test="age != null">
                AND age=#{age}
            </if>
        </where>
    </select>

 @Test
    public void selectCondition() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Student stu = new Student();
//        stu.setId(2);
        stu.setName("李四");
        stu.setAge(24);

        List<Student> list = mapper.selectCondition(stu);

        for(Student student : list){
            System.out.println(student);
        }

        sqlSession.close();

        is.close();
    }

标签

• foreach : 循环遍历标签, 适用于多个参数或者的关系

<froeach colloction="" open="" close="" item="" separator="">
    获取参数
</froeach>

• 属性
  • colloction: 参数容器,(list 集合, array 数组)
  • open: 开始的SQL语句
  • close: 结束的SQL语句
  • item: 参数变量名
  • separator: 分隔符

    <select id="selectByIds" resultType="student" parameterType="list">
        SELECT * FROM student
        <where>
            <foreach collection="list" open="id IN (" close=")"  item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

StudentMapper

 // 多个id查询学生数据
    public abstract List<Student> selectByIds(List<Integer> ids);

@Test
public void selectByIds() throws IOException{
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);

    List<Student> list = mapper.selectByIds(ids);

    for(Student student : list){
        System.out.println(student);
    }

    sqlSession.close();

    is.close();
}

SQL 片段抽取

• 我们可以将一些重复的SQL语句进行抽取, 以达到复用的效果

<sql id="select">SELECT * FROM student</sql>

<select id="selectAll" resultType="student">
    <include refid="select"></include>
</select>

<select id="selectById" resultType="student" parameterType="int">
    <include refid="select"></include> WHERE id = #{id}
</select>

分页插件

• 企业开发中, 分页是常见技术. MyBatis是不带分页功能的, 如果想实现分页功能, 需要手动编写LIMIT语句.不同数据库实现分页SQL语句也是不同, 手写成本高.
• PageHelper: 第三方分页助手, 将复杂的分页操作进行封装, 从而让分页功能变得非常简单.

分页插件实现步骤

• 导入 包
• 在核心配置文件中集成分页助手插件
• 在测试类中使用分页助手相关API实现分页功能

分页插件实现步骤

• 导入pagehelper-5.1.10.jar, jsqlparser-3.1.jar包
  • PageHelper下载
  • jsqlparser下载
• 在核心配置文件中集成分页助手插件

<configuration>
    <!-- 集成分页助手插件 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>
</configuration>

• 在测试类中使用分页助手相关API实现分页功能

 @Test
    public void selectCondition() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        // 通过分页助手来实现分页功能
        // 第1页3条数据
//        PageHelper.startPage(1,3);

        // 第2页3条数据
//        PageHelper.startPage(2,3);

        // 第3页3条数据
        PageHelper.startPage(3,3);
        List<Student> list = mapper.selectAll();

        for(Student student : list){
            System.out.println(student);
        }

        sqlSession.close();

        is.close();
    }

分页插件相关参数

• PageInfo: 封装分页相关参数的功能类
• 核心方法

返回值  方法名          说明
long    getTotal()      获取总条数
int     getPages()      获取总页数
int     getPageNum()    获取当前页
int     getPageSize()   获取每页显示条数
int     getPrePage()    获取上一页
int     getNextPage()   获取下一页
boolean isIsFirstPage() 获取是否是第一页
boolean isIsLastPage()  获取是否是最后一页

@Test
public void selectCondition() throws IOException {
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    // 通过分页助手来实现分页功能
    // 第1页3条数据
//        PageHelper.startPage(1,3);

    // 第2页3条数据
//        PageHelper.startPage(2,3);

    // 第3页3条数据
    PageHelper.startPage(3,3);
    List<Student> list = mapper.selectAll();

    for(Student student : list){
        System.out.println(student);
    }

    // 获取页相关参数
    PageInfo<Student> info = new PageInfo<>(list);

    System.out.println("总条数"+info.getTotal());
    System.out.println("总页数"+info.getPages());
    System.out.println("当前页"+info.getPageNum());
    System.out.println("每页显示条数"+info.getPageSize());
    System.out.println("上一页"+info.getPrePage());
    System.out.println("下一页"+info.getNextPage());
    System.out.println("是否第1页"+info.isIsFirstPage());
    System.out.println("是否最后1页"+info.isIsLastPage());

    sqlSession.close();

    is.close();
}

一对一

• resultMap: 配置字段和对象属性的映射关系标签

  • id属性: 唯一标识
  • type属性: 实体对象类型

• id: 配置主键映射关系标签

• result: 配置非主键映射关系标签

  • column属性: 表中字段名称
  • property属性: 实体对象变量名称

• association: 配置被包含对象的映射关系标签

  • property属性: 被包含对象的变量名
  • javaType属性: 被包含对象的数据类型

• 一对一模型: 人和身份证, 一个人只有一个身份证

数据准备

CREATE DATABASE db2;
USE db2;
CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT
);
INSERT INTO person VALUES (NULL,"张三",23);
INSERT INTO person VALUES (NULL,"李四",24);
INSERT INTO person VALUES (NULL,"王五",25);

SELECT * FROM person;

CREATE TABLE card(
    id INT PRIMARY KEY  AUTO_INCREMENT,
    number VARCHAR(30),
    pid INT,
    CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id)
);

INSERT INTO card VALUES (NULL,'12345',1);
INSERT INTO card VALUES (NULL,'22345',2);
INSERT INTO card VALUES (NULL,'32345',3);

public class Person {
    private Integer id;
    private String name;
    private Integer age;
    ...略
}

public class Card {
    private Integer id;
    private String number;
    private Person p;
    ...略
}

<mapper namespace="com.lizicai.table01.OneToOneMapper">
    <!-- 配置字段和实体对象属性的映射关系 -->
    <resultMap id="oneToOne" type="card">
        <id column="cid" property="id" />
        <result column="number" property="number" />

        <!-- 配置被包含对象的映射关系
           property 被包含的变量名
           javaType 被包含对象的数据类型
        -->
        <association property="p" javaType="person">
            <id column="pid" property="id" />
            <result column="name" property="name" />
            <result column="age" property="age" />
        </association>
    </resultMap>
    <select id="selectAll" resultMap="oneToOne">
        SELECT c.id cid,number,pid,NAME,age FROM card c, person p WHERE c.pid = p.id
    </select>
</mapper>

public interface OneToOneMapper {
    // 查询全部
    public abstract List<Card> selectAll();
}

package com.lizicai.table01;

import com.lizicai.bean.Card;
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.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Test01 {
    @Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);

        List<Card> list = mapper.selectAll();

        for(Card c : list){
            System.out.println(c);
        }

        sqlSession.close();

        is.close();
    }
}

一对多

• resultMap: 配置字段和对象属性的映射关系标签

  • id属性: 唯一标识
  • type属性: 实体对象类型

• id: 配置主键映射关系标签

• result: 配置非主键映射关系标签

  • column属性: 表中字段名称
  • property属性: 实体对象变量名称

• collection: 配置被包含对象的映射关系标签

  • property属性: 被包含对象的变量名
  • ofType属性: 集合中保存的对象的数据类型

• 数据准备

CREATE TABLE classes(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO classes VALUES (NULL, '一班');
INSERT INTO classes VALUES (NULL, '二班');

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    cid INT,
    CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);

INSERT INTO student VALUES (NULL,'张三',23,1);
INSERT INTO student VALUES (NULL,'李四',24,1);
INSERT INTO student VALUES (NULL,'王五',25,2);
INSERT INTO student VALUES (NULL,'赵六',26,2);

SELECT * FROM student;
SELECT * FROM classes;

public class Classes {
    private Integer id;
    private String name;

    private List<Student> students;
    ...略
}

public class Student {
    private Integer id;
    private String name;
    private Integer age;
    ...略
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTA Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lizicai.table02.OneToManyMapper">
    <!-- 配置字段和实体对象属性的映射关系 -->
    <resultMap id="oneToMany" type="classes">
        <id column="cid" property="id" />
        <result column="cname" property="name" />

        <!-- collection: 配置被包含的集合对象映射关系
        property: 被包含对象的变量名
        ofType: 被包含对象的实际数据类型
        -->
        <collection property="students" ofType="student">
            <id column="sid" property="id" />
            <result column="sname" property="name" />
            <result column="sage" property="age" />
        </collection>

    </resultMap>
    <select id="selectAll" resultMap="oneToMany">
        SELECT c.id cid,c.NAME cname,s.id sid,s.NAME sname,s.age sage FROM classes c, student s WHERE c.id = s.cid;
    </select>
</mapper>

在MyBatisConfig.xml引入

<mappers>
<!--        引入指定的映射配置文件 resource属性指定映射配置文件的名称-->
    <mapper resource="com/lizicai/one_to_one/OneToOneMapper.xml" />
    <mapper resource="com/lizicai/one_to_many/OneToManyMapper.xml" />
</mappers>

package com.lizicai.table02;

import com.lizicai.bean.Classes;

import java.util.List;

public interface OneToManyMapper {
    // 查询全部
    public abstract List<Classes> selectAll();
}

@Test
public void selectAll() throws IOException {
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);

    List<Classes> list = mapper.selectAll();

    for(Classes cls : list){
        System.out.println(cls.getId()+cls.getName());
        for(Student stu : cls.getStudents()){
            System.out.println("\t"+stu);
        }
    }

    sqlSession.close();

    is.close();
}

多对多

• 多对多模型: 学生和课程, 一个学生可以选择多门课程, 一个课程也可以被多个学生所选择
• 数据准备

CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO course VALUES (NULL,'语文');
INSERT INTO course VALUES (NULL,'数学');

CREATE TABLE stu_cr(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sid INT,
    cid INT,
    CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
    CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_cr VALUES (NULL,1,1);
INSERT INTO stu_cr VALUES (NULL,1,2);
INSERT INTO stu_cr VALUES (NULL,2,1);
INSERT INTO stu_cr VALUES (NULL,2,2);

SELECT * FROM course;
SELECT * FROM stu_cr;

public class Course {
    private Integer id;
    private String name;
}

public class Student {
    private Integer id;
    private String name;
    private Integer age;

    private List<Course> courses;
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTA Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lizicai.table03.ManyToManyMapper">
    <!-- 配置字段和实体对象属性的映射关系 -->
    <resultMap id="manyToMany" type="student">
        <id column="sid" property="id" />
        <result column="sname" property="name" />
        <result column="sage" property="age" />

        <!-- collection: 配置被包含的集合对象映射关系
        property: 被包含对象的变量名
        ofType: 被包含对象的实际数据类型
        -->
        <collection property="courses" ofType="course">
            <id column="cid" property="id" />
            <result column="cname" property="name" />
        </collection>

    </resultMap>
    <select id="selectAll" resultMap="manyToMany">
        SELECT sc.sid,s.NAME sname,s.age sage,sc.cid cid,c.NAME cname
        FROM student s,course c,stu_cr sc WHERE sc.sid=s.id AND sc.cid=c.id;
    </select>
</mapper>

public interface ManyToManyMapper {
    // 查询全部
    public abstract List<Student> selectAll();
}

@Test
public void selectAll() throws IOException {
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);

    List<Student> students = mapper.selectAll();

    for(Student stu : students){
        System.out.println(stu.getId()+stu.getName()+stu.getAge());
        for(Course c : stu.getCourses()){
            System.out.println("\t"+c);
        }
    }

    sqlSession.close();

    is.close();
}

多对多

• resultMap: 配置字段和对象属性的映射关系标签
  • id属性: 唯一标识
  • type属性: 实体对象类型
• id: 配置主键映射关系标签
• result: 配置非主键映射关系标签
  • column属性: 表中字段名称
  • property属性: 实体对象变量名称
• collection: 配置被包含对象的映射关系标签
  • property属性: 被包含对象的变量名
  • ofType属性: 集合中保存的对象的数据类型