MyBatis学习四（2）——一对多查询

订单与用户是一对一关系，一个订单对应一个用户
用户和订单是一对多关系，一个用户对应多个订单
基于这个模型进行一对多测试

pom.xml中导入依赖

<dependencies>
        <!--mybatis坐标-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <!--mysql驱动坐标-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
            <scope>runtime</scope>
        </dependency>
        <!--单元测试坐标-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!--日志坐标-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

创建表User对应的实体类

public class User {

    
    private int id;

    private String username;

    private String password;

    //该用户具有的订单信息
    private List<Orders> ordersList;

    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 String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public List<Orders> getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", ordersList=" + ordersList +
                '}';
    }
}

创建表Orders对应的实体类

public class Orders {

    private Integer id;

    private String orderName;

    private Double total;


    public Integer getId() {
        return id;
    }

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

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Double getTotal() {
        return total;
    }

    public void setTotal(Double total) {
        this.total = total;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", orderName='" + orderName + '\'' +
                ", total=" + total +
                '}';
    }
}

创建接口文件UserMapper

public interface UserMapper {

    //查询所有用户信息，查询出每个用户关联的订单信息
    public List<User> findAll();
}

创建数据库语句文件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.zyh.mapper.UserMapper">

    <resultMap id="UserMap" type="com.zyh.pojo.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
<!--        返回的是集合，所以这里用collection标签，property是方法名字，ofType是实体类路径-->
        <collection property="ordersList" ofType="com.zyh.pojo.Orders">
            <id property="id" column="oid"></id>
            <result property="orderName" column="ordername"></result>
            <result property="total" column="total"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="UserMap">
        select u.*,o.id oid,o.ordername,o.total,o.uid from user u left join orders o on o.uid = u.id
    </select>
</mapper>

创建核心配置文件SqlMapConfig.xml。jdbc.properties不与概述

<?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文件-->
    <properties resource="jdbc.properties"></properties>

    <!--给实体类的全限定类名给别名-->
    <typeAliases>
        <!--给单独的实体起别名-->
        <!--  <typeAlias type="com.zyh.pojo.User" alias="user"></typeAlias>-->
        <!--批量起别名：该包下所有的类的本身的类名：别名还不区分大小写-->
        <package name="com.zyh.pojo"/>
    </typeAliases>

    <environments default="dep">
        <environment id="dep">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--    数据库配置启动类-->
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>
            </dataSource>
        </environment>
    </environments>

<!--    引入配置文件-->
    <mappers>
<!--        批量-->
        <package name="com.zyh.mapper"></package>
    </mappers>
</configuration>

编写测试类测试

@Test
    public void test1() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactoryBuilder.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

测试成功，一对多返回