【mybatis】mybatis多对一、一对多查询

1. 建表

CREATE TABLE test_tbl_department(
	id varchar(32) NOT NULL,
	name varchar(32) NOT NULL,
	tel varchar(18) DEFAULT NULL,
	PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE test_tbl_user(
	id varchar(32) NOT NULL,
	version int(11) NOT NULL DEFAULT '0',
	name varchar(32) NOT NULL,
	age int(3) DEFAULT NULL,
	birthday datetime DEFAULT NULL,
	department_id varchar(32) NOT NULL,
	sorder int(11) NOT NULL DEFAULT '1',
	deleted tinyint(1) NOT NULL DEFAULT '0',
	primary key(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

INSERT INTO test_tbl_department(id, name, tel) VALUES('ea9ea24c03884b23a5a8a5c6cbb4ee27', '前端', '123');
INSERT INTO test_tbl_department(id, name, tel) VALUES('3dcdaab024724127964f1cb4dd4870c9', '后端', '321');
INSERT INTO test_tbl_department(id, name, tel) VALUES('b8f5209b27894432a4c9e78455f9bede', '产品', '111');
INSERT INTO test_tbl_department(id, name, tel) VALUES('8c24c0ad9cdc481bb4f2c58fbba4b0f3', '数据', '222');

INSERT INTO test_tbl_user(id, version, name, age, birthday, department_id, sorder, deleted)
	VALUES('37c80f9609ad4418850ce2d51c7bca45', 0, '张三', 18, '2003-08-08 10:00:00', 'ea9ea24c03884b23a5a8a5c6cbb4ee27', 1, 0);

INSERT INTO test_tbl_user(id, version, name, age, birthday, department_id, sorder, deleted)
	VALUES('d6037620cedb45b6bd15990a195cba12', 0, '李四', 20, '2001-08-08 10:00:00', '3dcdaab024724127964f1cb4dd4870c9', 1, 0);

2. 多对一

每个User中都有一个唯一的Department对象。User中的department_id关联Department中的id

2.1 准备pojo对象

@Data
@ToString
public class Department {
	private String id;
	private String name;
	private String tel;
}

@Data
@ToString
public class User {
	private String id;
	private String name;
	private Integer age;
	private Date birthday;
	private Department department;
}

写法有两种，立即加载和延迟加载方式。

2.2 准备Dao层接口

public interface UserDao{
	List<User> findAll();
	List<User> findAllLazy();
}

public interface DepartmentDao {
	Department findById();
}

2.3 立即加载方式

立即加载方式通过一个连接查询SQL将结果一次性全部查出来，再根据ResultMap映射结果集。

2.3.1 准备mapper.xml文件

User.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.com.mybatis.dao.UserDao">
    <resultMap id="userMap" type="cn.com.mybatis.pojo.User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="birthday" column="birthday"/>
        <association property="department" javaType="cn.com.mybatis.pojo.Department">
            <id property="id" column="department_id"/>
            <result property="name" column="department_name"/>
        </association>
    </resultMap>
	
	<select id="findAll" resultMap="userMap">
		select usr.*, dep.name as department_name
		from test_tbl_user usr
		left join test_tbl_department dep on usr.department_id = dep.id;
	</select>
</mapper>

2.3.2 测试结果

public class MybatisApplication {
	public static void main(String[] args) throws IOException {
		InputStream xml = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(xml);
		Sqlsession sqlSession = sqlSessionFactory.openSession();
		UserDao userDao = sqlSession.getMapper(UserDao.class);
		List<User> userList = userDao.findAll();
		userList.forEach(Systom.out::println)
	}
}

2.4 延迟加载方式

延迟加载需要通过fetchType参数开启，如果不开启的话，还是会一次性加载进内存。
延迟加载方式只查询User信息，所有User查询完毕后根据user中的department_id再查询department。

2.4.1 准备mapper.xml文件

UserDao.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.com.mybatis.dao.UserDao">
    <resultMap id="userMap" type="cn.com.mybatis.pojo.User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="birthday" column="birthday"/>
        <!--根据column中定义的department_id作为findById方法的入参-->
        <association property="department" javaType="cn.com.mybatis.pojo.Department" 
        	select="cn.com.mybatis.dao.DepartmentDao.findById" column="department_id"/>
    </resultMap>
	
	<select id="findAllLazy" resultMap="userLazy">
		select * from test_tbl_user;
	</select>
</mapper>

DepartmentDao.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.com.mybatis.dao.DepartmentDao">
	<select id="findById" resultMap="cn.com.mybatis.pojo.Department">
		select * from test_tbl_department where id = #{id}
	</select>
</mapper>

2.4.2 测试结果

public class MybatisApplication {
	public static void main(String[] args) throws IOException {
		InputStream xml = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(xml);
		Sqlsession sqlSession = sqlSessionFactory.openSession();
		UserDao userDao = sqlSession.getMapper(UserDao.class);
		List<User> userList = userDao.findAllLazy();
		userList.forEach(Systom.out::println)
	}
}

3. 一对多

查询出所有的department，每个department中都有一个users属性，存储了该department对应的所有user

3.1 准备Dao层接口

public interface UserDao {
	List<User> findAllByDepartmentId(String departmentId);
}

public interface DepartmentDao {
	List<Department> findAll();
}

3.2 延迟加载方式

演示开启延迟加载方式

3.2.1 准备mapper.xml文件

UserDao.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.com.mybatis.dao.UserDao">
   <select id="findAllByDepartmentId" resultType="cn.com.mybatis.pojo.User">
		select * from test_tbl_user where department_id = #{departmentId}
	</select>
</mapper>

DepartmentDao.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.com.mybatis.dao.DepartmentDao">
	<resultMap id="department" type="cn.com.mybatis.pojo.Department">
		<id property="id" column="id"/>
		<result property="name" columm="name"/>
		<result property="tel" columm="tel"/>
		<collection property="users" ofType="cn.com.mybatis.pojo.User" fetchType="lazy"
			select="cn.com.mybatis.dao.UserDao.findAllByDepartmentId" column="id"/>
	</resultMap>

	<select id="findAll" resultMap="department">
		select * from test_tbl_department;
	</select>
</mapper>

3.2.2 测试结果

public class MybatisApplication {
	public static void main(String[] args) throws IOException {
		InputStream xml = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(xml);
		Sqlsession sqlSession = sqlSessionFactory.openSession();
		DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);
		List<Department> departmentList = departmentDao.findAll();
		userList.forEach(Systom.out::println)
	}
}

由于开启了延迟加载，如果我们debug可以发现，在执行departmentDao.findAll()方法后，departmentList 中的users属性都为null，执行了完userList.forEach(Systom.out::println)方法后，users中才有值。