MyBatis基礎：MyBatis關聯查詢（4）

libingql發表於2017-09-13
MyBatis

1. MyBatis關聯查詢簡介

　　MyBatis中級聯分為3中：association、collection及discriminator。

　　◊ association：一對一關聯

　　◊ collection：一對多關聯

　　◊ discriminator：鑑別器，可以根據實際選擇採用哪個類作為例項，允許根據特定的條件去關聯不同的結果集。

2. 一對一關聯查詢

　　表結構設計：user、user_profile

2.1 方式一

<?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.libing.helloworld.dao.IUserProfileDao">
    <resultMap id="baseResultMap" type="com.libing.helloworld.model.UserProfile">
        <id property="id" column="profile_id" />
        <result property="userId" column="user_id" />
        <result property="name" column="name" />
        <result property="phone" column="phone" />
        <result property="email" column="email" />
        <result property="address" column="address" />
        <association property="user" javaType="com.libing.helloworld.model.User">
            <id property="id" column="id"/>
            <result property="userName" column="user_name" />
            <result property="password" column="password" />
        </association>
    </resultMap>
    
    <select id="findById" resultMap="baseResultMap">
        SELECT
            u.id,
            u.user_name,
            u.`password`,
            user_profile.id profile_id,
            user_profile.user_id,
            user_profile.`name`,
            user_profile.phone,
            user_profile.email,
            user_profile.address
        FROM
            user_profile,
            `user` u
        WHERE
            user_profile.user_id = u.id
        AND user_profile.id = #{id}
    </select>
</mapper>
DEBUG [main] - ==>  Preparing: SELECT u.id, u.user_name, u.`password`, user_profile.id profile_id, user_profile.user_id, user_profile.`name`, user_profile.phone, user_profile.email, user_profile.address FROM user_profile, `user` u WHERE user_profile.user_id = u.id AND user_profile.id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1

2.2 方式二

　　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.libing.helloworld.dao.IUserDao">
    <resultMap id="baseResultMap" type="com.libing.helloworld.model.User">
        <id property="id" column="id" />
        <result property="userName" column="user_name" />
        <result property="password" column="password" />
    </resultMap>
    
    <select id="findById" resultMap="baseResultMap">
        SELECT
            id,
            user_name,
            password
        FROM
            user
        WHERE id = #{id}
    </select>
</mapper>

　　UserProfileMapper.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.libing.helloworld.dao.IUserProfileDao">
    <resultMap id="baseResultMap" type="com.libing.helloworld.model.UserProfile">
        <id property="id" column="id" />
        <result property="userId" column="user_id" />
        <result property="name" column="name" />
        <result property="phone" column="phone" />
        <result property="email" column="email" />
        <result property="address" column="address" />
        <association property="user" column="user_id" select="com.libing.helloworld.dao.IUserDao.findById" />
    </resultMap>
    
    <select id="findById" resultMap="baseResultMap">
        SELECT
            id,
            user_id,
            name,
            phone,
            email,
            address
        FROM
            user_profile
        WHERE id = #{id}
    </select>
</mapper>

　　UserProfileTest.java：

package com.libing.helloworld.test;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.PropertyConfigurator;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import com.libing.helloworld.dao.IUserProfileDao;
import com.libing.helloworld.model.UserProfile;

public class UserProfileTest {

    SqlSession sqlSession = null;

    @Before
    public void init() {
        PropertyConfigurator.configure(UserProfileTest.class.getClassLoader().getResourceAsStream("log4j.properties"));
        String resource = "mybatis-config.xml";
        InputStream inputStream = UserProfileTest.class.getClassLoader().getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void findById() {
        try {
            IUserProfileDao userProfileDao = sqlSession.getMapper(IUserProfileDao.class);
            UserProfile userProfile = userProfileDao.findById(1);
            
            Assert.assertNotNull(userProfile);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

}

　　執行執行的SQL語句：

DEBUG [main] - ==>  Preparing: SELECT id, user_id, name, phone, email, address FROM user_profile WHERE id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - ====>  Preparing: SELECT id, user_name, password FROM user WHERE id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 1

3. 一對多關聯查詢

　　表結構設計：user、task

3.1 方式一

3.2 方式二

package com.libing.helloworld.model;

import java.util.List;

public class User {

    private int id;

    private String userName;

    private String password;

    private List<Task> tasks;

    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<Task> getTasks() {
        return tasks;
    }

    public void setTasks(List<Task> tasks) {
        this.tasks = tasks;
    }

}
User.java
package com.libing.helloworld.model;

public class Task {

    private int id;

    private int userId;

    private String taskName;

    private String content;

    public int getId() {
        return id;
    }

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

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getTaskName() {
        return taskName;
    }

    public void setTaskName(String taskName) {
        this.taskName = taskName;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

}
Task.java
<?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.libing.helloworld.dao.ITaskDao">
    <resultMap id="baseResultMap" type="com.libing.helloworld.model.Task">
        <id property="id" column="id" />
        <result property="userId" column="user_id" />
        <result property="taskName" column="task_name" />
        <result property="content" column="content" />
    </resultMap>
    
    <select id="findTasksByUserId" resultMap="baseResultMap">
        SELECT
            id,
            user_id,
            task_name,
            content
        FROM
            task
        WHERE user_id = #{userId}
    </select>
</mapper>
TaskMapper.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.libing.helloworld.dao.IUserDao">
    <resultMap id="baseResultMap" type="com.libing.helloworld.model.User">
        <id property="id" column="id" />
        <result property="userName" column="user_name" />
        <result property="password" column="password" />
        <collection property="tasks" column="id" select="com.libing.helloworld.dao.ITaskDao.findTasksByUserId"></collection>
    </resultMap>
    
    <select id="findById" resultMap="baseResultMap">
        SELECT
            id,
            user_name,
            password
        FROM
            user
        WHERE id = #{id}
    </select>
</mapper>
@Test
public void findAll() {
    try {
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = userDao.findById(1);

        Assert.assertNotNull(user);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

　　執行的SQL語句：

DEBUG [main] - ==>  Preparing: SELECT id, user_name, password FROM user WHERE id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - ====>  Preparing: SELECT id, user_id, task_name, content FROM task WHERE user_id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 0
DEBUG [main] - <==      Total: 1

4. N + 1問題

5. 延遲載入

相關文章