一对多:查询的单个对象中的一个属性包含多个其他对象,使用collection标签嵌套
1.数据库准备
代码如下:
USE mybatis;
# 创建一个名称为tb_idcard的表
CREATE TABLE tb_idcard(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18)
);
# 插入2条数据
INSERT INTO tb_idcard(CODE) VALUES('152221198711020624');
INSERT INTO tb_idcard(CODE) VALUES('152201199008150317');
# 创建一个名称为tb_person的表
CREATE TABLE tb_person(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
age INT,
sex VARCHAR(8),
card_id INT UNIQUE,
FOREIGN KEY(card_id) REFERENCES tb_idcard(id)
);
# 插入2条数据
INSERT INTO tb_person(name,age,sex,card_id) VALUES('Rose',22,'女',1);
INSERT INTO tb_person(name,age,sex,card_id) VALUES('jack',23,'男',2);
2.实体类
2.1 Orders.java
代码如下:
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
private Integer id;
private String number;
private Integer userId;
}
2.2Users.java
代码如下:
package com.biem.pojo;
import lombok.*;
import java.util.List;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
private Integer id;
private String username;
private String address;
private List<Orders> ordersList;
}
3. 用户配置接口
3.1OrdersMapper.java
代码如下:
package com.biem.mapper;
public interface OrdersMapper {
}
3.2UsersMapper.java
代码如下:
package com.biem.mapper;
public interface UsersMapper {
}
4.用户配置文件
4.1rdersMapper.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.biem.mapper.OrdersMapper">
</mapper>
4.2UsersMapper.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.biem.mapper.UsersMapper">
</mapper>
5.解决方法
5.1嵌套查询方式
5.1.1OrdersMapper.java
package com.biem.mapper;
public interface OrdersMapper {
public List<Orders> selectOrders(Integer uid);
}
5.1.2UsersMapper.java
package com.biem.mapper;
import com.biem.pojo.User;
public interface UserMapper {
public User findUserById(int id);
}
5.1.3OrdersMapper.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.biem.mapper.OrdersMapper">
<!-- public List<Orders> selectOrders(Integer user_id); -->
<select id="selectOrders" parameterType="Integer" resultType="Orders">
select * from tb_orders where user_id=#{userId}
</select>
</mapper>
5.1.4UsersMapper.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.biem.mapper.UsersMapper">
<resultMap id="usersWithOrders1" type="Users">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="address" column="address"/>
<!--由于查询老师中有一个属性为所有学生对象集合,所一使用collection标签进行嵌套,
方法的返回值是List,所以javaType使用ArrayList来接收,而list中泛型约束类型放的
是orders对象所以使用ofType为orderst来进行映射,使用select进行子查询,并用column
将查询出的id字段传给子查询需要的id-->
<!--此处javaType可以省略-->
<collection property="ordersList" javaType="ArrayList" ofType="orders" select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
</resultMap><!-- public Users findUserById(Integer id); -->
<select id="findUserById" parameterType="Integer" resultMap="usersWithOrders1">
select * from tb_user where id = #{id}
</select>
</mapper>
5.2测试方法
import org.junit.Test;
public class UsersTest {
@Test
public void testFindUserById(){
SqlSession session = MyBatisUtil.openSession();
UsersMapper mapper = session.getMapper(UsersMapper.class);
Users user = mapper.findUserById(1);
System.out.println("user = " + user);
session.close();
}
}
5.3测试结果
6.嵌套结果方式
6.1UsersMapper.java添加
public Users findUserByIdWithNestedResults(Integer id);
6.2UserMapper.xml添加
<!-- public Users findUserByIdWithNestedResults(Integer id); -->
<!-- 一对多:查看某一个用户及关联的订单信息,
注意:当关联查询出来的列名相同时,则需要使用别名区分 -->
<select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >
select u.*, o.id as orders_id , o.number, o.user_id
from tb_user u, tb_orders o
where u.id = o.user_id and u.id=#{id}
</select>
<resultMap id="UsersWithOrdersResult" type="Users">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="address" column="address"/>
<collection property="ordersList" ofType="Orders">
<id property="id" column="orders_id"></id>
<result property="number" column="number"/>
<result property="Id" column="user_id"/>
</collection>
</resultMap>
6.3UsersTest.java添加
@Test
public void testFindUserByIdWithNestedResults(){
SqlSession session = MyBatisUtil.openSession();
UsersMapper mapper = session.getMapper(UsersMapper.class);
Users user = mapper.findUserByIdWithNestedResults(1);
System.out.println("user = " + user);
session.close();
}
6.4测试结果