#MyBatis多表查詢 #多對一、一對多的兩種實現方式 @FDDLC

凡我出品，皆屬精品發表於2020-10-14
MyBatis

 

方式一(常用)：

我們知道，一個使用者可以對應多個賬戶，一個賬戶只能對應一個使用者，即賬戶和使用者之間存在多對一的關係。為了表示這種關係，在賬戶類中額外增加一個使用者類的成員：

對應的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類：

對應的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。

 

最後附上原始碼：

 

專案結構：

 

資料庫的相關資訊：

MySQL：8.0.16    port：3306    database：multi_table

account表：

user表：

 

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

 

相關文章