1.4（Mybatis学习笔记）关联映射

一、一对一

　　mybatis处理一对一主要通过<resultMap>中的<association>元素来处理。

　　<association>元素主要使用方方式有两种：　　

<!--方式一 嵌套查询-->
<association property = "card" column = "card_id" javaType = "com.mybatis.associateMapping.IdCard"

                        select = "com.mybatis.mapper.IdCardMapper.findCodeById" />

<!--方式二 嵌套结果-->

<association property = "card" javaType = "com.mybatis.associateMapping.IdCard">
　　　　<id property = "id" column = "card_id"/>
　　　　<property = "code" column = "code"/>
</association>

　　首先我们构建两张表，表结构如下：

　　tb_idcard

　　tb_person

　　建表语句:

CREATE TABLE `tb_idcard` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `code` varchar(18) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_person` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(32) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `sex` varchar(8) DEFAULT NULL,

  `card_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `card_id` (`card_id`),

  CONSTRAINT `tb_person_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `tb_idcard` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

　　tb_person表中的card_id与tb_card的id关联起来。

　　现在通过id查询person信息，同时使用这个id查询对应card信息。

IdCard.java

public class 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 "IdCard [id=" + id + ", code=" + code + "]";

    }

}

Person.java

public class Person {

    private Integer id;

    private String name;

    private Integer age;

    private String sex;

    private 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 IdCard getCard() {

        return card;

    }

    public void setCard(IdCard card) {

        this.card = card;

    }

    @Override

    public String toString() {

        return "Person [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + ", card=" + card + "]";

    }

}

IdCardMapper.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.mybatis.mapper.IdCardMapper" >

        <select id = "findCodeById" parameterType = "Integer" resultType = "com.mybatis.associateMapping.IdCard">

            select * from tb_idcard where id = #{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.mybatis.mapper.PersonMapper" >

        <select id = "findPersonById" parameterType = "Integer" resultMap = "IdCardWithPersonResult">

            select * from tb_person where id = #{id}

        </select>

        <resultMap type="com.mybatis.associateMapping.Person" id = "IdCardWithPersonResult">

            <id property="id" column="id" />

            <result property="name" column="name"/>

            <result property="age" column="age"/>

            <result property="sex" column="sex"/>

            <association property = "card" column = "card_id" javaType = "com.mybatis.associateMapping.IdCard"

                        select = "com.mybatis.mapper.IdCardMapper.findCodeById" />

        </resultMap>

    </mapper>

通过id查询的同时，resultMap中有一个嵌套查询会根据id查询idCard同时将查询的结果绑定到Person类的card属性上。

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>

    <properties resource = "db.properties">

    </properties>

    <!-- 采用扫描包定义别名 -->

    <typeAliases>

        <package name="com.mybatis.associateMapping"/>

    </typeAliases>

    <!-- 配置默认环境为mysql -->

    <environments default = "mysql">

    <!-- 配置id为SQL的数据库环境 -->

        <environment id = "my">

        <!-- 设置事务管理类型为JDBC -->

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

    <!-- 测试mybatis -->

        <mapper resource = "com/mybatis/mapper/CustomerMapper.xml"/>

        <!-- mybatis关联映射 -->

        <mapper resource = "com/mybatis/mapper/IdCardMapper.xml"/>

        <mapper resource = "com/mybatis/mapper/PersonMapper.xml"/>

    </mappers>

</configuration>


MyBatisSessionFactory.java  (工具类，用于获取SqlSessionFactory)

import java.io.IOException;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSessionFactory {

    public static SqlSessionFactory getSqlSessionFactory(String sourceFile) throws IOException {

        String resource = sourceFile;

        //获取配置文件输入流

        InputStream inputStream = Resources.getResourceAsStream(resource);

        //通过配置文件输入流构建sqlSessionFactory,

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"my");

        return sqlSessionFactory;

    }

}

测试：

import java.io.IOException;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.my.util.MyBatisSessionFactory;

public class TestMapper {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sqlSessionFactory = MyBatisSessionFactory.getSqlSessionFactory("mybatis-config.xml");

        SqlSession sqlSession = sqlSessionFactory.openSession();

        Person person = sqlSession.selectOne("com.mybatis.mapper.PersonMapper.findPersonById",1);

        System.out.println(person);

        sqlSession.commit();

        sqlSession.close();

    }

}

通过id查询person后，通过resultMap建立返回映射时，使用id查询code同时将返回的IdCard绑定到Person中的card属性上。

上例是采用嵌套查询来实现的，下面我们使用第二种方法嵌套结果来实现。

只需修改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.mybatis.mapper.PersonMapper" >

        <select id = "findPersonById" parameterType = "Integer" resultMap = "IdCardWithPersonResult">

            <!-- select * from tb_person where id = #{id} -->

            select p.*, idcard.code       <!-- 查询语句 -->

            from tb_person p,tb_idcard idcard

            where p.card_id = idcard.id and p.id = #{id}

        </select>

        <resultMap type="com.mybatis.associateMapping.Person" id = "IdCardWithPersonResult">

            <id property="id" column="id" />

            <result property="name" column="name"/>

            <result property="age" column="age"/>

            <result property="sex" column="sex"/>

            <!-- <association property = "card" column = "card_id" javaType = "com.mybatis.associateMapping.IdCard"

                        select = "com.mybatis.mapper.IdCardMapper.findCodeById" /> -->

            <association property = "card" javaType =  "IdCard">

                <id property = "id" column = "id"/>   <!-- 嵌套结果-->

                <result property = "code" column = "code" />

            </association>

        </resultMap>

    </mapper>

二、一对多

　　　mybatis处理一对一主要通过<resultMap>中的<collection>元素来处理。

　　　<collection>元素主要使用方方式有两种：　　

<!-- 嵌套结果-->
<collection property = "orderList" ofType =  "com.mybatis.oneN.Order">

                <id property = "id" column = "orders_id"/>

                <result property = "number" column = "number" />

            </collection>

首先创建两张表

tb_user

tb_orders

建表语句：

CREATE TABLE `tb_user` (

  `id` int(32) NOT NULL AUTO_INCREMENT,

  `username` varchar(32) DEFAULT NULL,

  `address` varchar(256) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_orders` (

  `id` int(32) NOT NULL AUTO_INCREMENT,

  `number` varchar(32) NOT NULL,

  `user_id` int(32) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `user_id` (`user_id`),

  CONSTRAINT `tb_orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

orders中关联了用户id，即代表该商品被某一用户购买。

Order.java

public class Order {

    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 "Order [id=" + id + ", number=" + number + "]";

    }

}

User.java

import java.util.List;

public class User {

    private Integer id;

    private String username;

    private String address;

    private List<Order> orderList; //用户购物清单

    public User() {

        super();

        // TODO Auto-generated constructor stub

    }

    public User(Integer id, String username, String address, List<Order> orderList) {

        super();

        this.id = id;

        this.username = username;

        this.address = address;

        this.orderList = orderList;

    }

    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<Order> getOrderList() {

        return orderList;

    }

    public void setOrderList(List<Order> orderList) {

        this.orderList = orderList;

    }

    @Override

    public String toString() {

        return "User [id=" + id + ", username=" + username + ", address=" + address + ", orderList=" + orderList + "]";

    }

}

UserMapper.xml (mybatis-config.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.mybatis.mapper.UserMapper" >

        <select id = "findUserWithOrders" parameterType = "Integer" resultMap = "OrdersWithUserResult">

            <!-- select * from tb_person where id = #{id} -->

            select u.*, o.id as orders_id,o.number

            from tb_user u,tb_orders o

            where u.id = o.user_id  and u.id = #{id}

        </select>

        <resultMap type="com.mybatis.oneN.User" id = "OrdersWithUserResult">

            <id property="id" column="id" />

            <result property="username" column="username"/>

            <result property="address" column="address"/>

            <collection property = "orderList" ofType =  "com.mybatis.oneN.Order">

                <id property = "id" column = "orders_id"/>

                <result property = "number" column = "number" />

            </collection>

        </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.mybatis.mapper.UserMapper" >

        <select id = "findUserWithOrders" parameterType = "Integer" resultMap = "OrdersWithUserResult">

            select * from tb_user where id = #{id}

        </select>

        <resultMap type="com.mybatis.oneN.User" id = "OrdersWithUserResult">

            <id property="id" column="id" />

            <result property="username" column="username"/>

            <result property="address" column="address"/>

            <collection property = "orderList"  column = "id" ofType =  "com.mybatis.oneN.Order"

                select = "com.mybatis.mapper.OrdersMapper.findOrderByUserId"

                />

        </resultMap>

    </mapper>

OrdersMapper.xml

<!-- 一对多 订单表与com.mybatis.oneN.Order映射 -->

        <resultMap type="com.mybatis.oneN.Order" id="OrderOneNResult" >

            <id property = "id" column = "id"/>

            <result property = "number" column="nuber"/>

        </resultMap>

<!-- 通过用户id查询订单 -->

        <select id = "findOrderByUserId" parameterType = "Integer" resultMap = "OrderOneNResult">

            select * from tb_orders where user_id = #{id}

        </select>

三、多对多

　　一个订单可以包含多个商品，同样一个商品也可以被多个订单包含。

　　这种多对多关系一般需要一个中间表，用于关联两者。

　　　　如上图所示，1号订单中有1,3号商品。3号商品被1,3号订单预定。两者之间通过一个中间表联系起来。

　　　现在我们想通过输入订单id查询该订单所包含的商品信息。例如查询一号商品订单。

　　　　由于有一个中间表，所以我们先需要通过订单id查询中间表中Order_id为1对应的Product_id的值（1,3）

　　　　然后通过Product_id中的（1,3）查询商品表中id为（1,3）的商品的详细信息。

　　Product.java （商品类，一个商品可以和多个订单关联）

import java.util.List;

public class Product {

    private Integer id;    //商品ID

    private String name;   //商品名称

    private Double price;  //商品价格

    private List<Order> orders; //一个商品中可包含多个订单

    public Product() {

        // TODO Auto-generated constructor stub

    }

    public Product(Integer id, String name, Double price, List<Order> orders) {

        super();

        this.id = id;

        this.name = name;

        this.price = price;

        this.orders = orders;

    }

    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;

    }

    public List<Order> getOrders() {

        return orders;

    }

    public void setOrders(List<Order> orders) {

        this.orders = orders;

    }

    @Override

    public String toString() {

        return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";

    }

}

　　Order.java(订单类，一个订单可以关联多个商品)　

import java.util.List;

public class Order {

    private Integer id;   //订单id

    private String number;//订单编号

    private List<Product> products; //一个订单关联多个商品

    public List<Product> getProducts() {

        return products;

    }

    public void setProducts(List<Product> products) {

        this.products = 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;

    }

    @Override

    public String toString() {

        return "Order [id=" + id + ", number=" + number + ", products=" + products + "]";

    }

}

　　OrdersMapper.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.mybatis.mapper.OrdersMapper">

        <resultMap type="com.mybatis.MN.Order" id="OrdersWithProdecutResult">

            <!-- 映射指定订单id查询到的订单id及订单编号 -->

            <id property = "id" column  = "id"/>

            <result property = "number" column = "number" />

            <!-- 嵌套查询，通过指定订单id查询关联商品 -->

            <!-- column=id代表将id作为查询语句参数，查询中间表中order_id为1的product_id,并将product_id作为参数查询商品表 -->

            <collection property = "products" column = "id"

            ofType="com.mybatis.MN.Product"

                select = "com.mybatis.mapper.ProductMapper.findProductById">

            </collection>

        </resultMap>

        <!-- 通过订单id查询该订单 -->

        <select id="findOrdersWithProduct" parameterType = "Integer"

        resultMap = "OrdersWithProdecutResult">

            select * from tb_orders where id = #{id}

        </select>

    </mapper>

该文件将订单id所对应的订单相关详细查询出来，并通过resultMap映射到Order类中，

Order对象中的 List<Product> products通过调用com.mybatis.mapper.ProductMapper.findProductById（ProductMapper.xml中）进行查询填充。

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.mybatis.mapper.ProductMapper">

        <select id="findProductById" parameterType = "Integer"

        resultType = "com.mybatis.MN.Product">

            <!-- 通过商品id查询对应商品详细信息 -->

            select * from tb_product where id in (

                <!-- 通过订单id在中间表中查询与其关联的商品id -->

                select product_id from tb_ordersitem where order_id = #{id}

            )

        </select>

    </mapper>

该文件将订单id作为查询参数，查询中间表中order_id为1所对应的product_id。（例如订单id为1，查询商品id为1,3）

然后将查询的product_id（1,3）作为参数，查询tb_orders中对应商品详细信息。

这样就通过订单id查询到了该订单对应商品的id，并将商品填充到Order类中的List<Product> products属性中。

测试：

import java.io.IOException;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.my.util.MyBatisSessionFactory;

public class TestMapper {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory = MyBatisSessionFactory.getSqlSessionFactory("mybatis-config.xml");

        SqlSession sqlSession = sessionFactory.openSession();

        Order order = sqlSession.selectOne("com.mybatis.mapper.OrdersMapper.findOrdersWithProduct", 1);

        System.out.println(order);

        sqlSession.commit();

        sqlSession.close();

    }

}

使用嵌套结果方式：

　　OrdersMapper.xml

　　　　<resultMap type="com.mybatis.MN.Order" id="OrdersWithProdecutResult">

            <!-- 映射指定订单id查询到的订单id及订单编号 -->

            <id property = "id" column  = "id"/>

            <result property = "number" column = "number" />

            <!-- 嵌套查询，通过指定订单id查询关联商品 -->

            <!-- column=id代表将id作为查询语句参数 -->

            <collection property = "products" ofType="com.mybatis.MN.Product">

                <id property = "id" column = "pid"/>

                <result property = "name" column = "name"/>

                <result property = "price" column = "price"/>

            </collection>

        </resultMap>

　　　　<!-- 通过订单id查询该订单商品 -->

        <select id="findOrdersWithProduct" parameterType = "Integer"

        resultMap = "OrdersWithProdecutResult">

            select o.*,p.id as pid,p.name,p.price

            from tb_orders o, tb_product p, tb_ordersitem oi

            where oi.order_id = o.id

            and oi.product_id = p.id

            and o.id = #{id}

        </select>

巴特西

1.4（Mybatis学习笔记）关联映射

最新文章

热门文章