Mybatis的多表操作之一对一/多查询

目录

 Mybatis的多表操作

1、一对一查询

数据库中

mapper包下orderMapper类下

pojo包下的order类下

pojo下user类下

OrderMapper.xml下

 MybatisTest测试代码下

运行结果

 OrderMap.xml中也可使用

2、一对多查询

 UserMapper接口下

UserMapper.xml下

Mybatis测试下

运行结果

---

 Mybatis的多表操作

1、一对一查询

一对一查询的模型

用户表和订单标的关系为，一个用户有多个订单，一个订单只属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

测试订单

数据库中

mapper包下orderMapper类下

package com_mybatis_table.mapper;
 
 
import com_mybatis_table.pojo.Order;
 
import java.util.List;
 
public interface OrderMapper {
 
    //查询全部的方法
    public List findAll();
 
}

pojo包下的order类下

package com_mybatis_table.pojo;
 
import java.util.Date;
 
public class Order {
 
    private int id;
    private Date ordertime;
    private double total;
 
    //当前订单属于哪一个用户
    private User user;
 
    public int getId() {
        return id;
    }
 
//对应的get和set方法
 
    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

pojo下user类下

package com_mybatis_table.pojo;
 
import java.util.Date;
import java.util.List;
 
public class User {
 
    private int id;
    private String username;
    private String password;
    private Date birthday;
 
    //描述的是当前用户存在哪些订单
    private List orderList;
 
//对应的get和set方法
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

OrderMapper.xml下

"1.0" encoding="UTF-8" ?>
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com_mybatis_table.mapper.OrderMapper">
 
    <resultMap id="orderMap" type="order">
        
        <id column="oid" property="id">id>
        <result column="ordertime" property="ordertime">result>
        <result column="total" property="total">result>
       result column="uid" property="user.id">result>
        <result column="username" property="user.username">result>
        <result column="password" property="user.password">result>
        <result column="birthday" property="user.birthday">result>
 
 
    resultMap>
 
    <select id="findAll" resultMap="orderMap">
         SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
    select>
 
mapper>

sqlMapConfig.xml还是一样的配置，多出了引入OrderMapper.xml

 
    
    <mappers>
         <mapper resource="com_mybatis/mapper/UserMapper_config.xml">mapper>
 
         <mapper resource="com_mybatis/mapper/OrderMapper.xml">mapper>
    mappers>

 MybatisTest测试代码下

 
public class MybatisTest {
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
 
        sqlSession.close();
    }
}

运行结果

 OrderMap.xml中也可使用

    <resultMap id="orderMap" type="order">
        
        <id column="oid" property="id">id>
        <result column="ordertime" property="ordertime">result>
        <result column="total" property="total">result>
        
 
        
        <association property="user" javaType="user">
            <id column="uid" property="id">id>
            <result column="username" property="username">result>
            <result column="password" property="password">result>
            <result column="birthday" property="birthday">result>
        association>
 
    resultMap>

单独的分离出来，结果也是一致的。

2、一对多查询

一对多查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

 UserMapper接口下

package com_mybatis_table.mapper;
 
import com_mybatis_table.pojo.User;
 
import java.util.List;
 
public interface UserMapper {
 
    public List findAll();
 
 
}

UserMapper.xml下

"1.0" encoding="UTF-8" ?>
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com_mybatis_table.mapper.UserMapper">
 
 
    <resultMap id="userMap" type="user">
        <id column="uid" property="id">id>
        <result column="username" property="username">result>
        <result column="password" property="password">result>
        <result column="birthday" property="birthday">result>
        
        <collection property="orderList" ofType="order">
            
            <id column="oid" property="id">id>
            <result column="ordertime" property="ordertime">result>
            <result column="total" property="total">result>
        collection>
    resultMap>
 
    <select id="findAll" resultMap="userMap">
        SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
    select>
 
 
 
mapper>

Mybatis测试下

    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List userList = mapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
 
        sqlSession.close();
    }

运行结果