MyBatis3 ---- 高级映射

MyBatis(3) ---- 高级映射

mybatis 执行过程

1. 配置MybatisCfg.xml 文件
2. 编写mapper.xml, 编写sql
3. 在MybatisCfg.xml 内注册mapper.xml
4. 创建SqlSessionFactory
5. 创建SqlSession
6. 使用SqlSession操作数据库（如要提交事务，需要使用commit()）
7. 释放资源

Mybatis 开发Dao

• 原始Dao 开发：

1. 编写dao 接口和实现 daoImpl
2. 在dao 实现类内注入SqlSessionFactory

• 使用mapper 代理的方式开发Dao(*)

1. 创建daoMapper.xml 和daoMapper.java（dao 接口） 文件
2. 将daoMapper.xml 的namespace 配置为daoMapper.java 的全路径
3. 将daoMapper.java 内定义的方法名称于daoMapper.xml sql的statement 的id 保持一致
4. 将daoMapper.java 的返回值和参数类型与daoMapper.xml 的resultType 和parameterType 的类型保持一致

Mybatis 高级映射

一对一：

• 使用resutlMap 将查询的结果映射

在resultMap 内 标签实现一对一关系的映射

• 实现一对一（基于mapper 代理的方式实现）

目录结构
\

---

• CardMapper.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.dao.CardMapper">
<!--    一对一查询-->
<!--    根据id 查询证件号, 在此处作为子查询使用-->
    <select id="findCodeById" resultType="Tb_idcard" parameterType="pojoVO">
        SELECT * from tb_idcard where id = #{idcard.id}
    </select>
</mapper>

---

• PersonMapper.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.dao.PersonMapper">
<!--    嵌套查询-->
    <select id="findPersonById" parameterType="pojoVO" resultMap="personMap">
        select * from tb_person p, tb_idcard i where
        p.card_id = i.id and
        p.id = #{idcard.id}
    </select>

    <resultMap id="personMap" type="tb_person">
        <!--
            id：指定查询的列中的唯一标识（主键） ，如果存在多个，就配置多个id
            result:普通的属性名称
            column: 表内对应的属性名
            property: pojo内对应的属性名
        -->
        <id property="id" column="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        
        
        
        -----------使用延时加载----------------
<!--        association：复杂类型的关联，将查询结果包装成指定类型，用于映射关联查询单个对象的信息 ---- 将查询的结果映射到javaType 指定的pojo上
            property：查询的字段
            column：数据库对应的字段
            javaType：将结果封装的类型
            select:执行指定位置（namespace+statementId）的操作-->
        <association property="card" column="card_id" javaType="tb_idcard"
                     select="com.dao.CardMapper.findCodeById" />
                     
        ------------------------------------             
                     这里是association 使用的另一种方式
        
<!--        association：复杂类型的关联，将查询结果包装成指定类型 ---- 将指定的属性映射到javaType 指定的pojo 对应的属性
            property：查询的字段
            column：数据库对应的字段
            javaType：将结果封装的类型
            id: 关联查询的唯一标识（主键）
            result：关联查询的普通属性-->
        <association property="card" column="card_id" javaType="tb_idcard" >
            <id column="id" property="id" />
            <result column="code" property="code" />
        </association>
        
    </resultMap>
</mapper>

---

• pojo

• PojoVO.java(pojo 的视图层)

package com.pojo;


/**
 * @ClassName PojoVO
 * @Author 秃头的JJ
 * Date 2019/5/17 0017 19:38
 * Version 1.0
 */
public class PojoVO {

    private Tb_idcard idcard;
    private Tb_person person;

    public Tb_idcard getIdcard() {
        return idcard;
    }

    public void setIdcard(Tb_idcard idcard) {
        this.idcard = idcard;
    }

    public Tb_person getPerson() {
        return person;
    }

    public void setPerson(Tb_person person) {
        this.person = person;
    }
}

• Tb_idcard.java

package com.pojo;

/**
 * @ClassName Tb_idcard
 * @Author 秃头的JJ
 * Date 2019/5/17 0017 19:34
 * Version 1.0
 */
public class Tb_idcard {

    private Integer id;
    private String code;

    public Integer getId() {
        return id;
    }

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

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

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

• Tb_person.java

package com.pojo;

/**
 * @ClassName Tb_person
 * @Author 秃头的JJ
 * Date 2019/5/17 0017 19:34
 * Version 1.0
 */
public class Tb_person {

    private Integer id;
    private String name;
    private Integer age;
    private String sex;
//    关联证件
    private Tb_idcard card;

    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 Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Tb_idcard getCard() {
        return card;
    }

    public void setCard(Tb_idcard card) {
        this.card = card;
    }

    @Override
    public String toString() {
        return "Tb_person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", card=" + card +
                '}';
    }
}

---

• MybatisCfg.xml(Mybatis 配置文件)

<?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>
    <properties resource="jdbc.properties"/>
    <typeAliases>
        <package name="com.pojo"/>
    </typeAliases>
    <environments default="MybatisCfg">
        <environment id="MybatisCfg">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        扫描包下的mapper文件-->
        <package name="com.dao"/>
    </mappers>
</configuration>

---

• 测试方法

/**
         * 对应的sql 语句
         * select * from tb_person p, tb_idcard i where
         * p.card_id = i.id and
         * p.id in (
         * 	SELECT id from tb_idcard where id = 2
         * )
         */
@Test
public void testFindPersonById(){
    SqlSession sqlSession = sessionFactory.openSession();
    PersonMapper dao = sqlSession.getMapper(PersonMapper.class);

    Tb_idcard idcard = new Tb_idcard();
    idcard.setId(1);
    PojoVO pojoVO = new PojoVO();
    pojoVO.setIdcard(idcard);
    Tb_person person = dao.findPersonById(pojoVO);
    System.out.println(person.toString());

    sqlSession.close();
}

一对多

在resultMap 内使用 标签实现一对多关系的映射

• 实现一对多（基于mapper 代理的方式实现）

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">
<mapper namespace="com.dao.oneToMoreDao.UserMapper">

<!--
        一对多查询：查看用户信息以及关联的信息
        注意：当查询的的列名相同时，使用别名区分
-->
    <select id="findUserWithOrders" resultMap="UserWithOrdersResult" parameterType="pojoVO">
        /*查询时有名字重复的字段（id） 在这里使用别名*/
        SELECT tb_user.*, tb_orders.id as oid,tb_orders.number
        from tb_user, tb_orders
        WHERE
        tb_user.id = tb_orders.user_id
        and tb_user.id = #{user.id}
    </select>

    <resultMap id="UserWithOrdersResult" type="tb_user">
        <id property="id" column="id" />
        <result property="username" column="username"/>
        <result property="address" column="address"/>
<!--       collection：一个复杂类型的集合
            嵌套结果的映射—— 集合本身可以是一个resultMap元素，或者从别处引用
            ofType: 表示属性的集合中元素的类型，即在pojo 内定义的集合泛型的类型
            -->
        <collection property="orders" ofType="tb_orders">
            <id property="id" column="oid" />
            <result property="number" column="number" />
        </collection>
    </resultMap>

</mapper>

---

• USerMapper.java

package com.dao.oneToMoreDao;

import com.pojo.PojoVO;
import com.pojo.oneToMore.Tb_user;

import java.util.List;

/**
 * @CInterfaceName UserMapper
 * @Author 秃头的JJ
 * Date 2019/5/17 0017 22:24
 */
public interface UserMapper {

    Tb_user findUserWithOrders(PojoVO pojoVO);

}

---

• Pojo

package com.pojo.oneToMore;

/**
 * @ClassName Tb_orders
 * @Author 秃头的JJ
 * Date 2019/5/17 0017 22:18
 * Version 1.0
 */
public class Tb_orders {

    private Integer id;
    private String number;

    public Integer getId() {
        return id;
    }

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

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

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

package com.pojo.oneToMore;

import java.util.List;

/**
 * @ClassName Tb_user
 * @Author 秃头的JJ
 * Date 2019/5/17 0017 22:20
 * Version 1.0
 */
public class Tb_user {

    private Integer id;
    private String username;
    private String address;
    private List<Tb_orders> orders;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public List<Tb_orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Tb_orders> orders) {
        this.orders = orders;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Tb_user{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", orders=" + orders +
                '}';
    }
}

• 测试方法

@Test
public void testFindUserWithOrders(){
    SqlSession sqlSession = sessionFactory.openSession();
    UserMapper dao = sqlSession.getMapper(UserMapper.class);

    Tb_user user = new Tb_user();
    user.setId(1);
    PojoVO pojoVO = new PojoVO();
    pojoVO.setUser(user);
    Tb_user u = dao.findUserWithOrders(pojoVO);
    System.out.println(u.toString());

    sqlSession.close();


}

多对多

在resultMap 内使用collection 实现多对多的关系映射

• 实现多对多（基于mapper 代理的方式实现）

• 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">
<mapper namespace="com.dao.moreToMoreDao.OrdersMapper">

    <select id="findOrdersWithProduct" parameterType="pojoVO" resultMap="ordersWithProductResult">
        select * from tb_orders where id = #{product.id}
    </select>
    <resultMap id="ordersWithProductResult" type="orders">
        <id property="id" column="id" />
        <result property="number" column="number" />
        <!--接收com.dao.moreToMoreDao.ProductMapper.findProductById 内的查询结果（product 型）
        并将结果绑定到products（pojo 内定义的变量） 上-->
        <collection property="products" column="id" ofType="product"
        select="com.dao.moreToMoreDao.ProductMapper.findProductById"/>
    </resultMap>


    <select id="findOrdersWithProduct_" parameterType="pojoVO" resultMap="OrdersWithProductResult_">
        /*SQL 查询语句*/
        select tb_orders.*, tb_product.id as pid, tb_product.name, tb_product.price
        from tb_product, tb_orders, tb_ordersitem
        where tb_ordersitem.orders_id = tb_orders.id
        and tb_ordersitem.product_id = tb_product.id
        and tb_orders.id = #{product.id}
    </select>
    <resultMap id="OrdersWithProductResult_" type="orders">
        <id column="id" property="id" />
        <result property="number" column="number" />
        <!--将查询数据绑定到对应的参数上-->
        <collection property="products" ofType="product">
            <id column="pid" property="id" />
            <result property="name" column="name" />
            <result property="price" column="price"/>
        </collection>
    </resultMap>
</mapper>

---

• ProductMapper.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.dao.moreToMoreDao.ProductMapper">

    <!--子查询，返回类型为product，由collection 调用获取值-->
    <select id="findProductById" resultType="product" parameterType="pojoVO">
        select * from tb_product where
        id in(
        SELECT product_id from tb_ordersitem where orders_id = #{product.id}
        )
    </select>

</mapper>

---

• pojo

package com.pojo.moreToMore;

import java.util.List;

/**
 * @ClassName Orders
 * @Author 秃头的JJ
 * Date 2019/5/18 0018 9:59
 * Version 1.0
 */
public class Orders {
    private Integer id;
    private String number;
    private List<Product> products;

    public Integer getId() {
        return id;
    }

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

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public List<Product> getProducts() {
        return products;
    }

    public void setProducts(List<Product> products) {
        this.products = products;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", products=" + products +
                '}';
    }
}

package com.pojo.moreToMore;

import java.util.List;

/**
 * @ClassName Product
 * @Author 秃头的JJ
 * Date 2019/5/18 0018 9:59
 * Version 1.0
 */
public class Product {

    private Integer id;
    private String name;
    private Double price;

    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 Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }


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

package com.pojo.moreToMore;

import java.util.List;

/**
 * @ClassName User
 * @Author 秃头的JJ
 * Date 2019/5/18 0018 9:59
 * Version 1.0
 */
public class User {
    private Integer id;
    private String username;
    private String address;
    private List<Orders> orders;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public List<Orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", orders=" + orders +
                '}';
    }
}

• mapper接口

package com.dao.moreToMoreDao;

import com.pojo.PojoVO;
import com.pojo.moreToMore.Orders;

/**
 * @InterfaceName OrdersMapper
 * @Author 秃头的JJ
 * Date 2019/5/18 0018 10:15
 */
public interface OrdersMapper {

    Orders findOrdersWithProduct(PojoVO pojoVO);
    Orders findOrdersWithProduct_(PojoVO pojoVO);
}

• 测试方法

@Test
    public void testFindOrdersWithProduct(){
        SqlSession sqlSession = sessionFactory.openSession();
        OrdersMapper dao = sqlSession.getMapper(OrdersMapper.class);

        Product product = new Product();
        product.setId(1);
        PojoVO pojoVO = new PojoVO();
        pojoVO.setProduct(product);
        Orders orders = dao.findOrdersWithProduct_(pojoVO);
        Orders orders = dao.findOrdersWithProduct(pojoVO);
        System.out.println(orders.toString());

        sqlSession.close();


    }

resultMap 使用

• 使用association 和collection 实现一对一和一对多的高级映射

• 使用association — 查询信息映射到pojo 对象内pojo 属性

使用association 可以将关联查询的信息映射到一个pojo 对象内

• 使用collection — 查询信息映射到pojo 对象内List 属性

使用collection 可以将关联查询的信息映射到一个list 集合内

延时加载

• 使用resultMap实现（association 和collection 实现延时夹加载）
• 延时查询：先进行单表查询需要时再从关联表去查询（开应提高数据库性能）
• 延时加载开关

延时加载Demo

• 使用association 的select 来实现延时加载

<!-- ******************使用延时加载****************** -->
<!-- association：复杂类型的关联，将查询结果包装成指定类型，用于映射关联查询单个对象的信息 ---- 将查询的结果映射到javaType 指定的pojo上
property：查询的字段
column：数据库对应的字段
javaType：将结果封装的类型
select:执行指定位置（namespace+statementId）的操作 --- 实现延时加载-->
<association property="card" column="card_id" javaType="tb_idcard"
         select="com.dao.CardMapper.findCodeById" />

• 对应SQl 语句是

select * from tb_person p, tb_idcard i where
p.card_id = i.id and
p.id in (
	SELECT id from tb_idcard where id = ?
)

• 测试方法

 @Test
    public void testFindPersonById(){

        /**
         * 对应的sql 语句
         * select * from tb_person p, tb_idcard i where
         * p.card_id = i.id and
         * p.id in (
         * 	SELECT id from tb_idcard where id = 2
         * )
         */
        SqlSession sqlSession = sessionFactory.openSession();
        PersonMapper dao = sqlSession.getMapper(PersonMapper.class);

        Tb_idcard idcard = new Tb_idcard();
        idcard.setId(2);
        PojoVO pojoVO = new PojoVO();
        pojoVO.setIdcard(idcard);
        List<Tb_person> tb_people = dao.findPersonById(pojoVO);
        for (Tb_person p: tb_people
             ) {
            System.out.println(p.getCard());
        }

        sqlSession.close();
    }w

• 调试