Mybatis 使用 mapper 接口规范的 一对一， 一对多，多对多映射

首先的 是 最原始的 pojo 类来 做简单映射

简单 pojo 映射：

<mapper namespace="com.ghc.dao.UserDao">

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

 

 

以下都是对于原始基本的 pojo 类 进行包装扩展后来实现 其他列的映射

一对一：

<resultMap id="findUserOrderMapper" type="com.ghc.pojo.UserOrderWrapperFirst"> <!--因为继承自Order类，所以这些都可以看做是Order这个对象的基本属性-->
        <id column="id" property="id"/>
        <result column="user_id" property="user_id"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>

        <!--配置映射关联的用户信息-->
        <!-- association: 用于映射关联查询单个对象的信息-->
        <!---->
        <association property="user" javaType="com.ghc.pojo.User"> <!--此处本可以写成user 但是为了突出就不用别名了 下面都是封装 wrapper 包装类的user对象到一个User的bean中-->
           <id column="user_id" property="id"/> <!--唯一标识用户的列就是主外键列 user_id 是 Order 的基本属性-->
            <result column="username" property="userName"/>
            <result column="birthday" property="birthday"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

    <select id="findUserOrder"  resultMap="findUserOrderMapper">
        select o.*,u.* from user u join orders o on u.id = o.user_id
    </select>

 

 

一对多：

 <resultMap id="findOneToManyMap" type="com.ghc.pojo.UserOrderWrapper">
        <id property="id" column="id"/>
        <result column="user_id" property="user_id"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <association property="user" javaType="user">
            <id column="user_id" property="id"/>
            <result column="username" property="userName"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>

        <collection property="orderDetails" ofType="com.ghc.pojo.OrderDetail">
            <id column="odtid" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
        </collection>
    </resultMap>

    <select id="findOneToMany" resultMap="findOneToManyMap">
        select o.*,
        u.username,
        u.sex,
        u.address,
        odt.id as odtid,
        odt.items_id,
        odt.items_num,
        odt.orders_id
       from user u join orders o on u.id = o.user_id
                   join orderdetail odt on o.id = odt.orders_id
    </select>

 

 

 

多对多：

关键还是在于 mapper.xml 文件的书写

<resultMap id="findManyToManyMap" type="user">
        <!--主表 user 表-->
        <id column="user_id" property="id"/>
        <result column="username" property="userName"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!--从表 order 表映射到 orderList-->
        <collection property="orderList" ofType="com.ghc.pojo.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="user_id"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>

            <!--从表 orderDetail 表映射到 orderDetailList-->
            <collection property="orderDetailList" ofType="com.ghc.pojo.OrderDetail">
                <id column="odtid" property="id"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>
                <!-- 而 orderDetail 这张表的 itemid 外键只对应 items 表中一条数据,所以此处用association-->
                <association property="item" javaType="com.ghc.pojo.Items">
                    <id column="item_id" property="id"/>
                    <result column="name" property="name"/>
                    <result column="price" property="price"/>
                    <result column="detail" property="detail"/>
                    <result column="pic" property="pic"/>
                    <result column="createtime" property="createtime"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <select id="findManyToMany" resultMap="findManyToManyMap">
        select o.id,
       o.user_id,
       o.number,
       o.createtime,
       o.note,
        u.username,
        u.sex,
        u.address,
        odt.id as odtid,
        odt.items_id,
        odt.items_num,
        odt.orders_id,
         i.id as item_id,
        i.name,
        i.price,
        i.detail,
        i.pic,
        i.createtime
       from user u join
                       orders o on u.id = o.user_id
                   join orderdetail odt on o.id = odt.orders_id
                   join items i on odt.items_id = i.id
    </select>

 

上面配置文件都写完后，就可以进行 mapper 接口 mapper.java 的编写了

 

顺便贴上 相应的 pojo (User 里 包含一个 List<Orders>  然后 Orders 里又包含一个 List<OrderDetail>  最后 OrderDetail 里包含一个 Item ) 修改后的样子：

也就是 数据库的 主从表之间的外键关系，

User ------> Order  (1:n)   

Order  -----> OrderDetail  (1:n)

OrderDetail ----> Item (1:1)

 

下面开始 贴 多对多的  pojo 部分的 代码了 (我觉得 多对多都会了 其他的都不在话下，所以其他的没必要贴了)：

package com.ghc.pojo;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String userName;
    private Date birthday;
    private String sex;
    private String address;
    // 一个人有多个订单
    private List<Orders> orderList;
    public List<Orders> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Orders> orderList) {
        this.orderList = orderList;
    }


    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

 

package com.ghc.pojo;

import java.util.Date;
import java.util.List;

public class Orders {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;

    public List<OrderDetail> getOrderDetailList() {
        return orderDetailList;
    }

    public void setOrderDetailList(List<OrderDetail> orderDetailList) {
        this.orderDetailList = orderDetailList;
    }

    // 一个订单可以有多个订单明细
    private List<OrderDetail> orderDetailList;

    public int getId() {
        return id;
    }

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

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public String getNumber() {
        return number;
    }

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

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }
}

 

package com.ghc.pojo;

public class OrderDetail {
    private int id;
    private int ordersId;
    private int itemsId;
    private int itemsNum;

    //而一个订单明细对应一条项目
    private  Items item;

    public Items getItem() {
        return item;
    }

    public void setItem(Items item) {
        this.item = item;
    }

    public int getId() {
        return id;
    }

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

    public int getOrdersId() {
        return ordersId;
    }

    public void setOrdersId(int ordersId) {
        this.ordersId = ordersId;
    }

    public int getItemsId() {
        return itemsId;
    }

    public void setItemsId(int itemsId) {
        this.itemsId = itemsId;
    }

    public int getItemsNum() {
        return itemsNum;
    }

    public void setItemsNum(int itemsNum) {
        this.itemsNum = itemsNum;
    }
}

package com.ghc.pojo;

import java.util.Date;

public class Items {
    private int id;
    private String name;
    private float price;
    private String detail;
    private String pic;
    private Date createtime;


    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public float getPrice() {
        return price;
    }

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

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

    public String getPic() {
        return pic;
    }

    public void setPic(String pic) {
        this.pic = pic;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
}

 

 

package com.ghc.dao;

import com.ghc.pojo.User;
import com.ghc.pojo.UserOrderWrapper;
import com.ghc.pojo.UserOrderWrapperFirst;

import java.util.List;

public interface UserDao {
    User findUserById(int id) throws Exception;
    List<UserOrderWrapperFirst> findUserOrder() throws Exception;
    List<UserOrderWrapper> findOneToMany() throws Exception;
    List<User> findManyToMany() throws Exception;
}

 

如果经常要打印输出，建议在 pojo 中 覆写 toString，不然就要跟下面这么苦逼的 拼 String 打印了

import com.ghc.dao.UserDao;

import com.ghc.pojo.*;
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.Before;
import org.junit.Test;

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

public class UserDaoTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void injectSqlSessionFactory() throws IOException {
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("config/SqlMappingConfig.xml"));
    }

    @Test
    public void testFindUserById() throws Exception {
        UserDao userDao = sqlSessionFactory.openSession().getMapper(UserDao.class);
        User user = userDao.findUserById(1);
        System.out.println(user.getUserName());

        List<UserOrderWrapperFirst> userOrderWrapperFirstList = userDao.findUserOrder();
        for (UserOrderWrapperFirst userOrder : userOrderWrapperFirstList) {
            String userName = userOrder.getUser().getUserName();
            int orderId = userOrder.getId();
            System.out.println(userName + " <-----> " + String.valueOf(orderId));

        }
    }

    @Test
    public void testFindOneToMany() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<UserOrderWrapper> userOrderWrappers = userDao.findOneToMany();
        for (int i = 0; i < userOrderWrappers.size(); i++) {
            UserOrderWrapper uow = userOrderWrappers.get(i);
            List<OrderDetail> orderDetails = uow.getOrderDetails();
            User user = uow.getUser();
            for (int j = 0; j < orderDetails.size(); j++) {
                System.out.print(uow.getId() + "--" + uow.getUser_id() + "--" + uow.getNumber() + "--" + uow.getCreatetime() + "--" + uow.getNote() + "--");
                System.out.print(user.getUserName() + "--" + user.getSex() + "--" + user.getAddress() + "--");
                OrderDetail odt = orderDetails.get(j);
                System.out.println(odt.getId() + "--" + odt.getItemsId() + "--" + odt.getItemsNum() + "--" + odt.getOrdersId());
            }
        }
    }

    @Test
    public void testFindManyToMany() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.findManyToMany();
        for (User user : userList) {
            List<Orders> orderList = user.getOrderList();
            for (Orders order : orderList) {
                List<OrderDetail> orderDetailList = order.getOrderDetailList();
                for (OrderDetail orderDetail : orderDetailList) {
                    Items item = orderDetail.getItem();
                    System.out.println(order.getId() + "--" + order.getUser_id() + "--" + order.getNumber() + "--" + order.getCreatetime() +
                            "--" + order.getNote() +"--" + user.getUserName() + "--" + user.getSex() + "--" +
                            user.getAddress() + "--" + orderDetail.getId() + "--" + orderDetail.getItemsId() + "--" + orderDetail.getItemsNum() + "--" +
                            orderDetail.getOrdersId() + "--" + item.getId() + "--" + item.getName() + "--" + item.getName() + "--" + item.getPrice() + "--" +
                            item.getDetail() + "--" + item.getPic() + "--" + item.getCreatetime());
                }
            }
        }
    }
}

 

测试结果 与数据库基本一致 ：

 

 

 至此， mybatis 的 所谓高级部分全部完成(动态 sql 部分另开一篇去说好了)，实际项目开发的时候，单表是采用 generator 逆向工程生成 pojo,mapper.xml , mapper.java 的

但是也需要配置数据库连接，表信息等等。。。到一个 generatorconfig.xml 这样类似的 一个 配置文件里，然后调用生成工具的代码进行生成。

但是懂了原理，任他怎么来都容易理解了。