首页 > > 程序设计 > Java技术 >

Mybatis框架六：关联查询

2018-08-03 07:30:31来源：博客园阅读 ()

这里搞一个测试场景：

用户和订单，一位用户可以有多个订单，而每个订单只属于一位用户

以用户为中心，相对于订单：一对多

以订单为中心，当对于用户：一对一

两张表结构：

订单表：

用户表：

对应的POJO类：

User:

package pojo;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    private Integer id;
    private String username;
    private String sex;
    private Date birthday;
    private String address;
    
    //附加对象List
    private List<Orders> ordersList;


    public List<Orders> getOrdersList() {
        return ordersList;
    }
    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }
    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 getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address + "]";
    }

}

View Code

Orders:

package pojo;

import java.io.Serializable;
import java.util.Date;

public class Orders implements Serializable {

    private static final long serialVersionUID = 1L;


    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //附加对象  用户对象
    private User user;

    
    @Override
    public String toString() {
        return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
                + ", note=" + note + ", user=" + user + "]";
    }

    public Integer getId() {
        return id;
    }

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

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

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

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
    
    
}

View Code

OrderMapper:

package mapper;

import java.util.List;

import pojo.Orders;
import pojo.User;

public interface OrderMapper {

    //以订单为中心一对一关联查询
    public List<Orders> selectOrders();
    
    //以订单为中心一对多关联查询
    public List<User> selectUserList();
    
}

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="mapper.OrderMapper">
    <!-- 一对一 -->
    <resultMap type="pojo.Orders" id="order">
        <result column="id" property="id" />
        <result column="user_id" property="userId" />
        <result column="number" property="number" />
        <association property="user" javaType="pojo.User">
            <id column="user_id" property="id" />
            <result column="username" property="username" />
        </association>
    </resultMap>
    <select id="selectOrders" resultMap="order">
        SELECT
        o.id,
        o.user_id,
        o.number,
        o.createtime,
        u.username
        FROM orders o
        left join user u
        on o.user_id = u.id
    </select>

    <!--一对多 -->
    <resultMap type="pojo.User" id="user">
        <id column="user_id" property="id" />
        <result column="username" property="username" />
        <collection property="ordersList" ofType="pojo.Orders">
            <id column="id" property="id" />
            <result column="number" property="number" />
        </collection>
    </resultMap>
    <select id="selectUserList" resultMap="user">
        SELECT
        o.id,
        o.user_id,
        o.number,
        o.createtime,
        u.username
        FROM user u
        left join orders o
        on o.user_id = u.id
    </select>

</mapper>

测试两个方法：

    @Test
    public void testOrderList() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //SqlSEssion帮我生成一个实现类  （给接口）
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<Orders> selectOrdersList = orderMapper.selectOrders();
        
        for (Orders orders : selectOrdersList) {
            System.out.println(orders);
        }
    }
    @Test
    public void testUserList() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //SqlSEssion帮我生成一个实现类  （给接口）
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<User> users = orderMapper.selectUserList();
        for (User user : users) {
            System.out.println(user);
        }
        
    }