#MyBatis多表查询 #多对一、一对多的两种实现方式 @FDDLC

方式一(常用)：

我们知道，一个用户可以对应多个账户，一个账户只能对应一个用户，即账户和用户之间存在多对一的关系。为了表示这种关系，在账户类中额外增加一个用户类的成员：

对应的mapper配置如下：

<?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="cn.liuxingchang.dao.AccountDao">
    <resultMap id="accountWithUser" type="cn.liuxingchang.domain.Account">
        <id property="id" column="id" />
        <result property="uid" column="uid" />
        <result property="money" column="money" />
        <association property="user" javaType="cn.liuxingchang.domain.User">
            <id property="id" column="user_id" />
            <result property="name" column="name" />
            <result property="age" column="age" />
        </association>
    </resultMap>

    <select id="findAll" resultMap="accountWithUser">
        select a.*, u.id as user_id, name, u.age from account a, user u where a.uid = u.id
    </select>
</mapper>

为了对多表查询结果的封闭，先定义了一个<resultMap>，然后在<select>标签里使用。这里的难点是<resultMap>的书写。id、money、uid都好理解，可能疑惑的地方是怎么给Account类里的user成员赋值。如你所见，用了个association标签！

association的property是user好理解，但没有column属性，即数据表中没有对应的列名。没有column，但多了个javaType，其实又相当于来了个javaType到相应数据表的映射，即这个association标签相当于是专用于内嵌的<resultMap>标签！

这里补充几个注意事项：

1、如果在SQL语句中字段取了别名的话，column属性也应该填别名，而不能是原字段名。

2、表取了别名后，别名可以随便用；字段取了别名后在where条件里却只能用原字段名，而不能用别名。

方式二(不常用)：使用继承(也可以直接把Account类和User类的部分或全部属性合并)

对应的ExtendedAccount类：

#MyBatis多表查询 #多对一、一对多的两种实现方式 @FDDLC

对应的SQL配置：

<select id="findAll2" resultType="cn.liuxingchang.domain.ExtendedAccount">
    select a.*, name, u.age from account a, user u where a.uid = u.id
</select>

说明：

1、用的是resultType属性而非像方式一那样必须使用resultMap。

2、account表中的uid和user表中的id是一样的，故只保留了uid。

最后附上源码：

项目结构：

#MyBatis多表查询 #多对一、一对多的两种实现方式 @FDDLC

数据库的相关信息：

MySQL：8.0.16 port：3306 database：multi_table

account表：

#MyBatis多表查询 #多对一、一对多的两种实现方式 @FDDLC

user表：

#MyBatis多表查询 #多对一、一对多的两种实现方式 @FDDLC

Account类：

package cn.liuxingchang.domain;

import java.io.Serializable;

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    private User user;

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

ExtendedAccount类：

package cn.liuxingchang.domain;

public class ExtendedAccount extends Account {
    private String name;
    private Integer age;

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

    @Override
    public String toString() {
        return super.toString() + " ### " + "ExtendedAccount{" +
                "name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

User类：

package cn.liuxingchang.domain;

import java.io.Serializable;

public class User implements Serializable {
    private Integer id;
    private String name;
    private Integer age;

    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 getAge() {
        return age;
    }

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

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

AccountDao接口：

package cn.liuxingchang.dao;

import cn.liuxingchang.domain.Account;
import cn.liuxingchang.domain.ExtendedAccount;

import java.util.List;

public interface AccountDao {
    List<Account> findAll();
    List<ExtendedAccount> findAll2();
}

AccountMapper.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="cn.liuxingchang.dao.AccountDao">
    <resultMap id="accountWithUser" type="cn.liuxingchang.domain.Account">
        <id property="id" column="id" />
        <result property="uid" column="uid" />
        <result property="money" column="money" />
        <association property="user" javaType="cn.liuxingchang.domain.User">
            <id property="id" column="user_id" />
            <result property="name" column="name" />
            <result property="age" column="age" />
        </association>
    </resultMap>

    <select id="findAll" resultMap="accountWithUser">
        select a.*, u.id as user_id, name, u.age from account a, user u where a.uid = u.id
    </select>

    <select id="findAll2" resultType="cn.liuxingchang.domain.ExtendedAccount">
        select a.*, name, u.age from account a, user u where a.uid = u.id
    </select>
</mapper>

mybatis.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>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/multi_table?characterEncoding=UTF8&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/AccountMapper.xml" />
    </mappers>
</configuration>

pom.xml：

<?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>org.example</groupId>
    <artifactId>P074_MyBatis_Many2One</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

Test.java：

import cn.liuxingchang.dao.AccountDao;
import cn.liuxingchang.domain.Account;
import cn.liuxingchang.domain.ExtendedAccount;
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 java.io.InputStream;
import java.util.List;

public class Test {
    private InputStream in;
    private SqlSessionFactory factory;
    private SqlSession sqlSession;
    private AccountDao accountDao;

    @Before
    public void before() throws Exception {
        in = Resources.getResourceAsStream("mybatis.xml");
        factory = new SqlSessionFactoryBuilder().build(in);
        sqlSession = factory.openSession();
        accountDao = sqlSession.getMapper(AccountDao.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
        in.close();
    }

    @org.junit.Test
    public void findAllTest() {
        List<Account> accounts = accountDao.findAll();
        for (Account account: accounts) {
            System.out.println(account);
        }
    }

    @org.junit.Test
    public void findAll2Test() {
        List<ExtendedAccount> extendedAccounts = accountDao.findAll2();
        for(ExtendedAccount extendedAccount: extendedAccounts) {
            System.out.println(extendedAccount);
        }
    }
}

#MyBatis多表查询 #多对一、一对多的两种实现方式 @FDDLC

相关推荐