Mybatis框架六：关联查询

这里搞一个测试场景：

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

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

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

两张表结构：

订单表：

用户表：

对应的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 + "]";

    }

}

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;

    }

}

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

        }

    }

巴特西

Mybatis框架六：关联查询

最新文章

热门文章