25_Mybatis_03-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_43407221/article/details/118300043

1 Mybatis_03

1.1 缓存

1.2 Mybatis 对象关系映射

1.2.1 多对一查询

1.2.1.1 创建实体类，建立联系

20201014185138539

Dept.java

package com.cl01.mybatis.mto.po;

/**
 * @Author chenlan
 * @Date 2020/10/13 20:44
 * @Description 
 */
public class Dept {
    private Integer id;
    private String name;
}

Emp.java

package com.cl01.mybatis.mto.po;

/**
 * @Author chenlan
 * @Date 2020/10/13 20:45
 * @Description
 */
public class Emp {
    private Integer id;
    private String name;
    private Dept dept; 
}

1.2.1.2 创建mapper接口

package com.cl01.mybatis.mto.mapper;

import com.cl01.mybatis.mto.po.Emp;

/**
 * 一对多 映射 xml实现
 */

public interface EmpMapper {

    Emp selectEmpById(Integer id);
    Emp selectEmpByPrimaryKey(Integer id);
}

1.2.1.3 编写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">
<mapper namespace="com.cl01.mybatis.mto.mapper.EmpMapper">

    <!--
          多对一
          关联查询
          01 先查询 emp 表 ,返回结果使用 resultMap 进行映射
          02 使用<association>标签 (这是一个对结果映射的引用) 通过结果集中的 列 dept_id 映射 emp 中的属性 dept (一对一映射 )
          03 再次发送 SQL 语句，使用 emp 的外键 dept_id 进行 dept 的查询 将查询后的结果封装到 emp 的属性 dept 中
    -->
    <select id="selectEmpById" parameterType="integer" resultMap="rs_map">
        select * from emp where id =#{id}
    </select>

    <resultMap id="rs_map" type="com.cl01.mybatis.mto.po.Emp">
        <!--
             映射 emp 的属性 id 和 name
        -->
        <id column="id"  property="id" ></id>
        <result column="name" property="name"></result>

        <!--
            映射 emp 的 属性 dept
        -->
        <association column="dept_id" property="dept" javaType="com.cl01.mybatis.mto.po.Dept" select="selectDeptById">
        </association>
    </resultMap>

    <select id="selectDeptById" parameterType="integer" resultType="com.cl01.mybatis.mto.po.Dept">
        select * from dept where id = #{dept_id}
    </select>

    <!--
        多对一
        等值连接查询
        01 对 emp 和 dept 进行等值连接查询
        02 对 结果集 进行 映射
    -->
    <select id="selectEmpByPrimaryKey" resultMap="result_map">
        select e.id eid , e.name ename, d.id did , d.name dname from emp e join dept d on e.dept_id = d.id where e.id = #{id}
    </select>

    <resultMap id="result_map" type="com.cl01.mybatis.mto.po.Emp">
        <!--
            映射 emp 的属性 id name
        -->
        <id column="eid" property="id"></id>
        <result column="ename" property="name"></result>

        <!--
            映射 emp 的属性 dept
        -->
        <association property="dept" javaType="com.cl01.mybatis.mto.po.Dept">
            <!--
                映射 dept 的属性 id name
            -->
            <id column="did" property="id"></id>
            <result column="dname" property="name"></result>
        </association>
    </resultMap>
</mapper>

1.2.1.4 编写测试类

MybatisUtils.java:

package com.cl01.mybatis.mto.utils;

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;

/**
 * @Author chenlan
 * @Date 2020/10/13 20:56
 * @Description TODO
 */
public class MybatisUtils {
    private MybatisUtils() {
    }

    private static SqlSessionFactory sqlSessionFactory = null;

    static {
        try {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }

    public static SqlSession getSqlSession(boolean autoCommit) {
        return sqlSessionFactory.openSession(autoCommit);
    }


}

package com.cl01.mybatis.mto.test;

import com.cl01.mybatis.mto.mapper.EmpMapper;
import com.cl01.mybatis.mto.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @Author chenlan
 * @Date 2020/10/13 21:03
 * @Description TODO
 */
public class AppTest {

    @Test
    public void testSelectEmpById(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            System.out.println(sqlSession.getMapper(EmpMapper.class).selectEmpById(3));
        }
    }

    @Test
    public void testSelectEmpByPrimaryKey(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            System.out.println(sqlSession.getMapper(EmpMapper.class).selectEmpByPrimaryKey(1));
        }

    }
}

testSelectEmpById结果图:
20201014200839446

testSelectEmpByPrimaryKey结果图：

20201014201405036

1.2.1.5 采用annotation方式实现

mapper接口:

package com.cl01.mybatis.mto.mapper;

import com.cl01.mybatis.mto.po.Dept;
import com.cl01.mybatis.mto.po.Emp;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;


/**
 *  多对一查询 注解方式
 */
public interface EmpAnnotationMapper {

    @Select("select * from emp where id = #{id}")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "dept_id",property = "dept",one = @One(select = "selectDeptById"))

    })
    Emp selectEmpById(Integer id);

    @Select("select * from dept where id = #{dept_id}")
    Dept selectDeptById(Integer id);
}

测试输出结果:

20201014193912451

1.2.2 一对多查询

1.2.2.1 创建实体类，建立联系

Emp.java

package com.cl02.mybatis.otm.po;

/**
 * @Author chenlan
 * @Date 2020/10/13 22:57
 * @Description TODO
 */
public class Emp {
    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Emp() {
    }

    public Emp(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

Dept.java

package com.cl02.mybatis.otm.po;

import java.util.List;

/**
 * @Author chenlan
 * @Date 2020/10/13 22:58
 * @Description TODO
 */
public class Dept {

    private Integer id;
    private String name;
    private List<Emp> emps;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    public Dept() {
    }

    public Dept(Integer id, String name, List<Emp> emps) {
        this.id = id;
        this.name = name;
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", emps=" + emps +
                '}';
    }
}

1.2.2.2 创建mapper接口

package com.cl02.mybatis.otm.mapper;

import com.cl02.mybatis.otm.po.Dept;

/**
 * 一对多 映射xml实现
 */

public interface DeptMapper {
    Dept selectDeptById(Integer id);

    Dept selectDeptByPirMaryKey(Integer id);
}

1.2.2.3 编写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">
<mapper namespace="com.cl02.mybatis.otm.mapper.DeptMapper">

    <!--
        一对多 关联查询
    -->
    <select id="selectDeptById" resultMap="rs_map">
        select * from dept where id = #{id}
    </select>

    <resultMap id="rs_map" type="com.cl02.mybatis.otm.po.Dept">
        <!--
            映射Dept 的属性 id name
        -->
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <!--
            使用结果集字段id 建立与dept表的映射，将查询的结果集封装到dept 的属性emps 中
            collection 是对其他映射结果集的引用
        -->
        <collection column="id" property="emps" ofType="com.cl02.mybatis.otm.po.Emp" select="selectEmpById">
        </collection>
    </resultMap>

    <select id="selectEmpById" resultType="com.cl02.mybatis.otm.po.Emp">
        select * from emp where dept_id = #{id}
    </select>

    <!--
        一对多 等值连接查询
    -->

    <!--
        进行等值连接查询
    -->
    <select id="selectDeptByPirMaryKey" resultMap="result_map">
        select d.id did,d.name dname,e.id eid,e.name ename from dept d join emp e on d.id = e.dept_id
        where d.id = #{id}
    </select>

    <!--
        建立结果集的映射
    -->
    <resultMap id="result_map" type="com.cl02.mybatis.otm.po.Dept">
        <id column="did" property="id"></id>
        <result column="dname" property="name"></result>

        <collection property="emps" ofType="com.cl02.mybatis.otm.po.Emp">
            <id column="eid" property="id"></id>
            <result column="ename" property="name"></result>
        </collection>
    </resultMap>
</mapper>

1.2.2.4 编写测试类

package com.cl02.mybatis.otm.test;

import com.cl02.mybatis.otm.mapper.DeptMapper;
import com.cl02.mybatis.otm.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @Author chenlan
 * @Date 2020/10/13 23:14
 * @Description TODO
 */
public class AppTest {
    @Test
    public void testSelectDeptById(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            System.out.println(sqlSession.getMapper(DeptMapper.class).selectDeptById(1));
        }
    }

    @Test
    public void testSelectDeptByPirMaryKey(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            System.out.println(sqlSession.getMapper(DeptMapper.class).selectDeptByPirMaryKey(1));
        }
    }

}

testSelectDeptById运行结果：

20201014201842559

testSelectDeptByPirMaryKey运行结果:
20201014202100645

1.2.2.5 采用annotation方式实现

mapper接口:

package com.cl02.mybatis.otm.mapper;

import com.cl02.mybatis.otm.po.Dept;
import com.cl02.mybatis.otm.po.Emp;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface DeptAnnotationMapper {


    /**
     *  一对多 使用@Many
     * @param id
     * @return
     */

    @Select("select * from dept where id = #{id}")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "id",property = "emps",many = @Many(select = "selectEmpByDeptID"))
    })
    Dept selectDeptById(Integer id);

    @Select("select * from emp where dept_id = #{id}")
    List<Emp> selectEmpByDeptID(Integer deptId);
}

输出结果:

20201014195400362

1.3 Mybatis的逆向工程

文件结构:
20201013202755286

Mybatis的逆向工程能自动生成数据库表对应的pojo实体文件和映射文件，自动生成各种增删改查的SQL语句

但只能用于单表查询，多表查询需要手动配置映射。

1.3.1 添加jar包

20201013191438294

1.3.2 步骤

1.3.2.1 添加配置生成文件

20201013194634893

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>

    <context id="mysql" targetRuntime="MyBatis3">
        <!-- 注释构建 -->
        <commentGenerator>
            <!-- 去掉所有的注释 -->
            <property name="suppressAllComments" value="true"/>
            <property name="suppressDate" value="true"/>
        </commentGenerator>

        <!-- 数据库四要素 -->
        <jdbcConnection connectionURL="jdbc:mysql://localhost:3306/test"
                        driverClass="com.mysql.jdbc.Driver"
                        password="root"
                        userId="root" />
        <!-- 实体类 : pojo
            targetPackage : 实体类生成后存放的包
            targetProject : 存放的目录一般都放在 src下面
          -->
        <javaModelGenerator targetPackage="com.cl.mybatis.po" targetProject="java31_mybatis_auto/src" />
        <!-- 映射文件 -->
        <sqlMapGenerator targetPackage="com.cl.mybatis.mapper" targetProject="java31_mybatis_auto/src" />
        <!-- 操作接口
            type 生成映射的形式
                ANNOTATEDMAPPER : 纯注解的,没有xml映射
                XMLMAPPER : 生成的有xml映射文件
        -->
        <javaClientGenerator type="XMLMAPPER"   targetPackage="com.cl.mybatis.mapper" targetProject="java31_mybatis_auto/src" />

        <!-- 要生成对应表的配置
            tableName : 数据库表名
            //如果下面全部是true,mybatis直接可以使用纯面向对象开发
            enableCountByExample : 是否生成查询总数的 Example
            enableDeleteByExample : 是否生成删除的 Example
            enableSelectByExample : 是否生成查询集合的 Example
            enableUpdateByExample : 是否生成修改的 Example
         -->
        <table  tableName="user"  enableCountByExample="false" enableDeleteByExample="false" enableSelectByExample="true" enableUpdateByExample="false"></table>
    </context>
</generatorConfiguration>

1.3.2.2 添加解析类

package com.cl.mybatis.test;

import org.apache.ibatis.io.Resources;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author chenlan
 * @Date 2020/10/13 19:16
 * @Description Mybatis 逆向工程解析
 */
public class ParseTest {
    public static void main(String[] args) throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        //指定 逆向工程配置文件
        Reader reader = Resources.getResourceAsReader("generatorConfig.xml");

        // config的解析器
        ConfigurationParser cp = new ConfigurationParser(warnings);

        // 拿到config
        Configuration config = cp.parseConfiguration(reader);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);

        // 自动映射器 (根据 数据的表自动生成 pojo类,mapper接口和mapper.xml)
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,
                callback, warnings);
        myBatisGenerator.generate(null);
    }
}

1.3.2.3 运行开始逆向工程

20201013195415547

1.3.2.4 测试逆向工程生成的文件

package com.cl.mybatis.test;

import com.cl.mybatis.mapper.UserMapper;
import com.cl.mybatis.po.User;
import com.cl.mybatis.po.UserExample;
import com.cl.mybatis.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @Author chenlan
 * @Date 2020/10/13 19:52
 * @Description mybatis 逆向工程测试类
 */
public class AppTest {

    /**
     * 测试 int deleteByPrimaryKey(Integer id);
     */
    @Test
    public void testDeleteByPrimaryKey() {
        try (SqlSession sqlSession = MybatisUtils.getSqlSession(true)) {
            final int i = sqlSession.getMapper(UserMapper.class).deleteByPrimaryKey(36);
            System.out.println(i > 0 ? "删除成功" : "删除失败");
        }
    }

    /**
     * 测试 int insertSelective(User record);
     */
    @Test
    public void testInsertSelective(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession(true)) {
            User user = new User();
            user.setName("郭靖");
            user.setPassword("123");
            user.setAge(45);
            final int i = sqlSession.getMapper(UserMapper.class).insertSelective(user);
            System.out.println(i > 0 ? "添加成功" : "添加失败");
        }
    }

    /**
     * 测试使用条件对象查询
     */
    @Test
    public void test01(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {

            // 创建条件对象
            final UserExample userExample = new UserExample();

            // 创建条件构造器
            final UserExample.Criteria criteria = userExample.createCriteria();

            // 查询年龄在20到30之间的user对象
            criteria.andAgeBetween(20, 30);

            // 多条件查询 默认用and连接条件
            criteria.andNameEqualTo("乔峰");

            for (User user : sqlSession.getMapper(UserMapper.class).selectByExample(userExample)) {
                System.out.println(user.toString());
            }

        }
    }


    /**
     *  使用条件对象多条件查询时，默认and连接条件，如果要使用or连接，需要使用多个条件构造器
     *  然后使用or进行关联
     */
    @Test
    public void test02(){
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            final UserExample userExample = new UserExample();
            // 条件构造器1
            final UserExample.Criteria criteria1 = userExample.createCriteria();
            criteria1.andAgeBetween(20, 30);

            // 条件构造器2
            final UserExample.Criteria criteria2 = userExample.createCriteria();
            criteria2.andNameEqualTo("乔峰");

            // 关联
            userExample.or(criteria2);
            for (User user : sqlSession.getMapper(UserMapper.class).selectByExample(userExample)) {
                System.out.println(user);
            }
        }

    }
}