MyBatis基础:MyBatis关联查询(4)

时间:2024-01-04 19:40:08

1. MyBatis关联查询简介

  MyBatis中级联分为3中:association、collection及discriminator。

  ◊ association:一对一关联

  ◊ collection:一对多关联

  ◊ discriminator:鉴别器,可以根据实际选择采用哪个类作为实例,允许根据特定的条件去关联不同的结果集。

2. 一对一关联查询

  表结构设计:user、user_profile

MyBatis基础:MyBatis关联查询(4)

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

MyBatis基础:MyBatis关联查询(4)

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. 延迟加载