【JAVADAY50】Mybatis框架下，一对多关系的查询如何写，完整版

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>top.keyle</groupId>
    <artifactId>myMYbatis</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
            </resource>
        </resources>
    </build>
</project>

2、在resource下创建jdbc.properties和SqlMapConfig.xml文件

<?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>
    <!--1、加载外部配置类型文件-->
    <properties resource="jdbc.properties"/>
    <!--2、设置开启日志-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!--3、注册pojo包驱动，省去写类名路径的麻烦，直接可写类名的驼峰命名法-->
    <typeAliases>
        <package name="com.keyle.computer.pojo"/>
    </typeAliases>
    <!--4、配置连接池，以及默认的工作环境keyle-->
    <environments default="keyle">
        <environment id="keyle">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${prop.driverClassName}"/>
                <property name="url" value="${prop.url}"/>
                <property name="username" value="${prop.username}"/>
                <property name="password" value="${prop.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--注册mapper包驱动，省去在这里写每个类的路径-->
    <mappers>
        <package name="com.keyle.computer.mapper"/>
    </mappers>
</configuration>

prop.driverClassName=com.mysql.cj.jdbc.Driver
prop.url=jdbc:mysql://106.15.191.42:3306/ssm
prop.username=.....
prop.password=.....

问题："http://mybatis.org/dtd/mybatis-3-config.dtd"爆红，去百度搜，第一个就是解决方案。

注意：记得在可视化那里，修改连接的是哪个数据库，否则下面书写XML文件找不到表。

3、手写包装类，order和student

package com.keyle.computer.pojo;

@SuppressWarnings("all")
public class Order {
    private Integer id;
    private String name;
    private Integer count;
    private Double price;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getCount() {
        return count;
    }

    public void setCount(Integer count) {
        this.count = count;
    }

    public Double getPrice() {
        return price;
    }

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

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", count=" + count +
                ", price=" + price +
                '}';
    }

    public Order(Integer id, String name, Integer count, Double price) {
        this.id = id;
        this.name = name;
        this.count = count;
        this.price = price;
    }

    public Order() {
    }
}

package com.keyle.computer.pojo;

import java.util.List;

@SuppressWarnings("all")
public class Student {
    private Integer id;
    private String name;
    private String email;
    private Integer age;

    private List<Order> orderList;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                ", orderList=" + orderList +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

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

    public Student(Integer id, String name, String email, Integer age, List<Order> orderList) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.age = age;
        this.orderList = orderList;
    }

    public Student() {
    }
}

注意：看我每个类写了哪些字段，哪些是数据库中有，但我没写的。

5、创建…Mapper和对应的同名的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.keyle.computer.mapper.CustomerMapper">

</mapper>

package com.keyle.computer.mapper;
@SuppressWarnings("all")
public interface CustomerMapper {
}

注意：XML文件一定要写namespace

6、先在查询控制台写SQL语句

select s.id as 'userid', s.name as 'username', email, age, t.id as 'orderid', t.name as 'ordername', count, price, customer_id
from student s left join t_order t on s.id = t.customer_id
where s.id=1;

7、手写CustomerMapper.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.keyle.computer.mapper.CustomerMapper">
    <resultMap id="customermap" type="student">
        <!--一对多关系-->
        <!--主键绑定
            private Integer id;
            private String name;
            private String email;
            private Integer age;
            private List<Order> orderList;
        -->
        <id property="id" column="userid"/>
        <!--非主键绑定-->
        <result property="name" column="username"/>
        <result property="email" column="email"/>
        <result property="age" column="age"/>
        <!--多的那个属性的绑定orderList
        private Integer id;
        private String name;
        private Integer count;
        private Double price;
        -->
        <collection property="orderList" ofType="order">
            <!--主键绑定-->
            <id property="id" column="orderid"/>
            <!--非主键绑定-->
            <result property="name" column="ordername"/>
            <result property="count" column="count"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>
    <select id="byIdGetStudentAndOrder" parameterType="int" resultMap="customermap">
        select s.id as 'userid', s.name as 'username', email, age, t.id as 'orderid', t.name as 'ordername', count, price, customer_id
        from student s left join t_order t on s.id = t.customer_id
        where s.id = #{id}
    </select>
</mapper>

注意：最重要的莫属：多的那个属性的绑定orderList

8、手写接口，与xml文件同名且方法名同名

package com.keyle.computer.mapper;

import com.keyle.computer.pojo.Student;

@SuppressWarnings("all")
public interface CustomerMapper {
    //根据用户ID查出其信息，以及其拥有的订单信息
    Student byIdGetStudentAndOrder(Integer id);
}

9、手写测试方法

import com.keyle.computer.mapper.CustomerMapper;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class myTest {
    SqlSession sqlSession;
    CustomerMapper cm;
    @Before
    public void before() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = factory.openSession();
        cm = sqlSession.getMapper(CustomerMapper.class);
    }
    @After
    public void after(){
        sqlSession.close();
    }
    @Test
    public void MyTestA(){
        System.out.println(cm.byIdGetStudentAndOrder(1));
    }
}

运行结果：

Student{id=1, name='张三', email='zhangsan@126.com', age=22, orderList=[Order{id=1, name='苹果', count=20, price=12.8}, Order{id=2, name='橘子', count=11, price=21.8}]}

成功！