MyBatis的关联映射
结果映射（resultMap）–主要使用这两个标签
association – 一个复杂类型的关联；许多结果将包装成这种类型
嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用
collection – 一个复杂类型的集合
嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用
- 多对一：查询的多个对象中有一个属性包含一个复杂对象，使用association标签嵌套
- 一对多：查询的单个对象中的一个属性包含多个其他对象，使用collection标签嵌套
- javaType为实体类中属性的类型，为常用基本数据类型时可以省略
- ofType则是用来指定到List或集合中的实体类类型，泛型中的约束类型
1.一对一查询
问题：根据用户Id查询Person的信息，包括IdCard的信息，用户和IdCard是一对一的关系
1.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);
1.2 实体类
1.2.1 IdCard.java
package com.biem.pojo;import lombok.*;@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class IdCard {private Integer id;private String code;
}1.2.2 Person.java
package com.biem.pojo;import lombok.*;@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Person {private Integer id;private String name;private Integer age;private String sex;private IdCard card;
}1.3 用户配置接口
1.3.1 IdCardMapper.java
package com.biem.mapper;public interface IdCardMapper{
}1.3.2 Person.java
package com.biem.mapper;public interface PersonMapper {
}1.4 用户配置文件
1.4.1 IdCardMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.IdCardMapper"></mapper>
1.4.2 PersonMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.PersonMapper"></mapper>
1.5 解决方法
1.5.1 嵌套查询方式
1.5.1.1 IdCardMapper.java
package com.biem.mapper;import com.biem.pojo.IdCard;public interface IdCardMapper {public IdCard findCodeById(Integer id);
}
1.5.1.2 PersonMapper.java
package com.biem.mapper;import com.biem.pojo.Person;public interface PersonMapper {public Person findPersonById(Integer id);
}1.5.1.3 IdCardMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.IdCardMapper"><!--public IdCard findCodeById(Integer id);--><select id="findCodeById" parameterType="Integer" resultType="IdCard">select * from tb_idcard where id=#{id}</select>
</mapper>
1.5.1.4 PersonMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.PersonMapper"><!-- public Person findPersonById(Integer id); --><resultMap id="IdCardWithPersonResult" type="Person"><id property="id" column="id"></id><result property="name" column="name"/><result property="age" column="age"/><result property="sex" column="sex"/><!-- 一对一：association使用select 属性引入另外一条SQL语句 --><association property="card" column="card_id" javaType="IdCard"select="com.biem.mapper.IdCardMapper.findCodeById"/></resultMap><select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">select * from tb_person where id = #{id}</select>
</mapper>
1.5.1.5 PersonTest.java
package com.biem.test;import com.biem.mapper.PersonMapper;
import com.biem.pojo.Person;
import com.biem.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;public class PersonTest {@Testpublic void testFindPersonById(){SqlSession session = MyBatisUtil.openSession();PersonMapper mapper = session.getMapper(PersonMapper.class);Person person = mapper.findPersonById(1);System.out.println(person);session.close();}
}
1.5.1.6 测试结果
DEBUG 04-21 17:35:29,353 ==> Preparing: select * from tb_person where id = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 17:35:29,412 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 17:35:29,458 ====> Preparing: select * from tb_idcard where id=? (BaseJdbcLogger.java:159)
DEBUG 04-21 17:35:29,458 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 17:35:29,462 <==== Total: 1 (BaseJdbcLogger.java:159)
DEBUG 04-21 17:35:29,465 <== Total: 1 (BaseJdbcLogger.java:159)
Person(id=1, name=Rose, age=22, sex=女, card=IdCard(id=1, code=152221198711020624))
1.5.2 嵌套结果方式
1.5.2.1 PersonMapper.java 添加
public Person findPersonByIdWithNestedResults(Integer id);
1.5.2.2 PersonMapper.xml添加
<!--public Person findPersonByIdWithNestedResults(Integer id);--><resultMap id="IdCardWithPersonResult2" type="Person"><id property="id" column="id"></id><result property="name" column="name"/><result property="age" column="age"/><result property="sex" column="sex"/><association property="card" javaType="IdCard"><id property="id" column="card_id"></id><result property="code" column="code"></result></association></resultMap><select id="findPersonByIdWithNestedResults" parameterType="Integer" resultMap="IdCardWithPersonResult">select p.*, idcard.codefrom tb_person p, tb_idcard idcardwhere p.card_id = idcard.id and p.id = #{id}</select>
1.5.2.3 PersonTest.java添加
@Testpublic void testFindPersonByIdWithNestedResults(){SqlSession session = MyBatisUtil.openSession();PersonMapper mapper = session.getMapper(PersonMapper.class);Person person = mapper.findPersonByIdWithNestedResults(1);System.out.println(person);session.close();}
1.5.2.4 测试结果
DEBUG 04-21 17:31:48,431 ==> Preparing: select p.*, idcard.code from tb_person p, tb_idcard idcard where p.card_id = idcard.id and p.id = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 17:31:48,479 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 17:31:48,529 ====> Preparing: select * from tb_idcard where id=? (BaseJdbcLogger.java:159)
DEBUG 04-21 17:31:48,530 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 17:31:48,533 <==== Total: 1 (BaseJdbcLogger.java:159)
DEBUG 04-21 17:31:48,536 <== Total: 1 (BaseJdbcLogger.java:159)
Person(id=1, name=Rose, age=22, sex=女, card=IdCard(id=1, code=152221198711020624))
2.一对多查询
问题：查询用户以及关联的订单，一个用户有多个订单
2.1 数据库准备
USE mybatis;
# 创建一个名称为tb_user的表
CREATE TABLE tb_user (id int(32) PRIMARY KEY AUTO_INCREMENT,username varchar(32),address varchar(256));
# 插入3条数据
INSERT INTO tb_user VALUES ('1', '小明', '北京');
INSERT INTO tb_user VALUES ('2', '李华', '上海');
INSERT INTO tb_user VALUES ('3', '李刚', '上海');
# 创建一个名称为tb_orders的表
CREATE TABLE tb_orders (id int(32) PRIMARY KEY AUTO_INCREMENT,number varchar(32) NOT NULL,user_id int(32) NOT NULL,FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
# 插入3条数据
INSERT INTO tb_orders VALUES ('1', '1000011', '1');
INSERT INTO tb_orders VALUES ('2', '1000012', '1');
INSERT INTO tb_orders VALUES ('3', '1000013', '2');
2.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;
}
2.2.2 Users.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;
}
2.3 用户配置接口
2.3.1 OrdersMapper.java
package com.biem.mapper;public interface OrdersMapper {
}
2.3.2 UsersMapper.java
package com.biem.mapper;public interface UsersMapper {
}
2.4 用户配置文件
2.4.1 OrdersMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.OrdersMapper">
</mapper>
2.4.2 UsersMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.OrdersMapper">
</mapper>
2.5 解决办法
2.5.1 嵌套查询方式
2.5.1.1 OrdersMapper.java
package com.biem.mapper;public interface OrdersMapper {
}2.5.1.2 UsersMapper.java
package com.biem.mapper;import com.biem.pojo.User;public interface UserMapper {public User findUserById(int id);
}2.5.1.3 OrdersMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//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>
2.5.1.4 UsersMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//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>
2.5.1.5 UsersTest.java
import org.junit.Test;public class UsersTest {@Testpublic void testFindUserById(){SqlSession session = MyBatisUtil.openSession();UsersMapper mapper = session.getMapper(UsersMapper.class);Users user = mapper.findUserById(1);System.out.println("user = " + user);session.close();}
}
2.5.1.6 测试结果
DEBUG 04-21 20:39:59,758 ==> Preparing: select * from tb_user where id = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 20:39:59,897 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 20:39:59,962 ====> Preparing: select * from tb_orders where user_id=? (BaseJdbcLogger.java:159)
DEBUG 04-21 20:39:59,963 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 20:39:59,967 <==== Total: 2 (BaseJdbcLogger.java:159)
DEBUG 04-21 20:39:59,971 <== Total: 1 (BaseJdbcLogger.java:159)
user = Users(id=1, username=小明, address=北京, ordersList=[Orders(id=1, number=1000011, userId=1), Orders(id=2, number=1000012, userId=1)])
2.5.2 嵌套结果方式
2.5.2.1 UsersMapper.java添加
public Users findUserByIdWithNestedResults(Integer id);
2.5.2.2 UsersMapper.xml添加
<!-- public Users findUserByIdWithNestedResults(Integer id); --><!-- 一对多：查看某一个用户及关联的订单信息，注意：当关联查询出来的列名相同时，则需要使用别名区分 --><select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >select u.*, o.id as orders_id , o.number, o.user_idfrom tb_user u, tb_orders owhere 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="userId" column="user_id"/></collection></resultMap>
2.5.2.3 UsersTest.java添加
@Testpublic void testFindUserByIdWithNestedResults(){SqlSession session = MyBatisUtil.openSession();UsersMapper mapper = session.getMapper(UsersMapper.class);Users user = mapper.findUserByIdWithNestedResults(1);System.out.println("user = " + user);session.close();}
2.5.2.4 测试结果
DEBUG 04-21 21:13:51,214 ==> Preparing: 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=? (BaseJdbcLogger.java:159)
DEBUG 04-21 21:13:51,289 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 21:13:51,380 <== Total: 2 (BaseJdbcLogger.java:159)
user = Users(id=1, username=小明, address=北京, ordersList=[Orders(id=1, number=1000011, userId=1), Orders(id=2, number=1000012, userId=1)])
3.多对一查询
解决user_Id为某个值的所有orders的信息
3.1 环境配置
2.1-2.4配置
对于Users–>Orders 1对多
对于Orders->Users 多对1
Orders.java添加属性User
package com.biem.pojo;import lombok.*;@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {private Integer id;private String number;private Integer userId;private Users users;
}3.2 解决方案
3.2.1 嵌套查询方式
3.2.1.1 UsersMapper.java 添加
public Users getUsersById(Integer id);
3.2.1.2 OrdersMapper.java 添加
public List<Orders> getOrders1(Integer userId);
3.2.1.3 UsersMappers.xml
<select id="getUsersById" resultType="users">select * from tb_user where `id` = #{id}</select>
3.2.1.4 OrdersMapper.xml
<select id="getOrders1" resultMap="ordersResult1">select * from tb_orders where user_id = #{userId}</select><resultMap id="ordersResult1" type="orders"><result column="id" property="id"/><result column="number" property="number"/><!--复杂的属性，我们需要单独处理对象：使用association集合：collection--><association column="id" property="users" javaType="users" select="com.biem.mapper.UsersMapper.getUsersById"/></resultMap>
3.2.1.5 OrdersTest.java
package com.biem.test;import com.biem.mapper.OrdersMapper;
import com.biem.pojo.Orders;
import com.biem.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class OrdersTest {@Testpublic void testGetOrders1(){SqlSession session = MyBatisUtil.openSession();OrdersMapper mapper = session.getMapper(OrdersMapper.class);List<Orders> ordersList = mapper.getOrders1(1);System.out.println("ordersList = " + ordersList);}
}3.2.1.6 测试结果
DEBUG 04-21 22:11:09,143 ==> Preparing: select * from tb_orders where user_id = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,203 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,251 ====> Preparing: select * from tb_user where `id` = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,252 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,254 <==== Total: 1 (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,258 ====> Preparing: select * from tb_user where `id` = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,259 ====> Parameters: 2(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,262 <==== Total: 1 (BaseJdbcLogger.java:159)
DEBUG 04-21 22:11:09,262 <== Total: 2 (BaseJdbcLogger.java:159)
ordersList = [Orders(id=1, number=1000011, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null)), Orders(id=2, number=1000012, userId=1, users=Users(id=2, username=李华, address=上海, ordersList=null))]3.2.2 嵌套结果方式
3.2.2.1 OrderMapper.java 添加
ublic List<Orders> getOrders1ByNestedResult(Integer userId);
3.2.2.1 OrderMapper.xml添加
<!-- public List<Orders> getOrders1ByNestedResult(Integer userId); --><select id="getOrders1ByNestedResult" resultMap="ordersResult2"><!-- id为接口中实现的方法名，resultMap为返回值类型，由于返回值包含一个Teacher对象，所以不能用resultType-->select o.id oid, o.number number, o.user_id uid , u.username username, u.address addressfrom tb_orders o,tb_user uwhere o.user_id=u.id and o.user_id = #{userId}</select><resultMap id="ordersResult2" type="orders"><result column="oid" property="id"/><result column="number" property="number"/><result column="uid" property="userId"/><association property="users" javaType="users"><!--property是实体类中的字段属性，复杂的属性，使用了javaType描述的是属性的类型，它是一个Teacher对象--><result property="id" column="uid"/><result property="username" column="username"/><result property="address" column="address"/></association></resultMap>
3.2.2.3 OrderTest.java 添加
@Testpublic void testGetOrders2(){SqlSession session = MyBatisUtil.openSession();OrdersMapper mapper = session.getMapper(OrdersMapper.class);List<Orders> ordersList = mapper.getOrders1ByNestedResult(1);System.out.println("ordersList = " + ordersList);}
3.2.2.4 测试结果
DEBUG 04-21 22:36:45,304 ==> Preparing: select o.id oid, o.number number, o.user_id uid , u.username username, u.address address from tb_orders o,tb_user u where o.user_id=u.id and o.user_id = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 22:36:45,394 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 22:36:45,438 <== Total: 2 (BaseJdbcLogger.java:159)
ordersList = [Orders(id=1, number=1000011, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null)), Orders(id=2, number=1000012, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null))]4 多对多查询
4.1 数据库准备
USE mybatis;
# 创建一个名称为tb_product的表
CREATE TABLE tb_product (id INT(32) PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(32),price DOUBLE );
# 插入3条数据
INSERT INTO tb_product VALUES ('1', 'Java基础入门', '44.5');
INSERT INTO tb_product VALUES ('2', 'Java Web程序开发入门', '38.5');
INSERT INTO tb_product VALUES ('3', 'SSM框架整合实战', '50');
# 创建一个名称为tb_ordersitem 的中间表
CREATE TABLE tb_ordersitem (id INT(32) PRIMARY KEY AUTO_INCREMENT,orders_id INT(32),product_id INT(32),FOREIGN KEY(orders_id) REFERENCES tb_orders(id),
FOREIGN KEY(product_id) REFERENCES tb_product(id)
);
# 插入3条数据
INSERT INTO tb_ordersitem VALUES ('1', '1', '1');
INSERT INTO tb_ordersitem VALUES ('2', '1', '3');
INSERT INTO tb_ordersitem VALUES ('3', '3', '3');
# 订单表tb_orders由于在上面已经创建,所以这里不用再次创建
4.2 实体类
4.2.1 Orders.java
已经创建所以不用再次创建
添加一个属性
private List<Product> productList;
变成：
package com.biem.pojo;import lombok.*;import java.util.List;@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {private Integer id;private String number;private Integer userId;private Users users;private List<Product> productList;
}4.2.2 Product.java
package com.biem.pojo;import lombok.*;import java.util.List;@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product {private Integer id;private String name;private Double price;private List<Orders> ordersList;
}
4.3 用户配置接口
4.3.1 OrdersMapper.java
已经创建
4.3.2 ProductMapper.java
package com.biem.mapper;public interface ProductMapper {
}
4.4 用户配置文件
4.4.1 OrdersMapper.xml
已经创建
4.4.2 ProductMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.ProductMapper"></mapper>
4.5 解决方案
4.5.1 嵌套查询方式
4.5.1.1 OrdersMapper.java 添加
public List<Orders> findOrdersWithProduct(Integer id);
4.5.1.2 ProductMapper.java 添加
public List<Product> findProductById(Integer id);
4.5.1.3 OrdersMapper.xml 添加
<select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">select * from tb_orders where id = #{id}</select><resultMap id="OrdersWithProductResult" type="Orders"><id property="id" column="id"></id><result property="number" column="number"></result><collection property="productList" column="id" ofType="Product" select="com.biem.mapper.ProductMapper.findProductById"></collection></resultMap>
4.5.1.4 ProductMapper.xml 添加
<!--public List<Product> findProductById(Integer id);--><select id="findProductById" parameterType="Integer" resultType="Product">select * from tb_product where id in(select product_id from tb_ordersitem where orders_id = #{id})</select>
4.5.1.5 OrderTest.java添加
@Testpublic void testFindOrdersWithProduct(){SqlSession session = MyBatisUtil.openSession();OrdersMapper mapper = session.getMapper(OrdersMapper.class);List<Orders> ordersList = mapper.findOrdersWithProduct(1);System.out.println("ordersList = " + ordersList);}
4.5.1.6 测试结果
DEBUG 04-21 23:21:46,851 ==> Preparing: select * from tb_orders where id = ? (BaseJdbcLogger.java:159)
DEBUG 04-21 23:21:46,897 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 23:21:46,959 ====> Preparing: select * from tb_product where id in( select product_id from tb_ordersitem where orders_id = ? ) (BaseJdbcLogger.java:159)
DEBUG 04-21 23:21:46,960 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 23:21:46,966 <==== Total: 2 (BaseJdbcLogger.java:159)
DEBUG 04-21 23:21:46,970 <== Total: 1 (BaseJdbcLogger.java:159)
ordersList = [Orders(id=1, number=1000011, userId=1, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]4.5.2 嵌套结果查询
4.5.2.1 OrderMapper.java 添加
public List<Orders> findOrdersWithProductByNestedResult(Integer id);
4.5.2.2 OrdersMapper.xml 添加
<!-- public List<Orders> findOrdersWithProductByNestedResult(Integer id); --><select id="findOrdersWithProductByNestedResult" parameterType="Integer" resultMap="OrdersWithProductResult2">select o.*, p.id as pid, p.name, p.pricefrom tb_orders o, tb_product p, tb_ordersitem oiwhere oi.orders_id=o.id and oi.product_id=p.id and o.id=#{id}</select><resultMap id="OrdersWithProductResult2" type="Orders"><id property="id" column="id"></id><result property="number" column="number"></result><collection property="productList" ofType="product"><id property="id" column="pid"/><result property="name" column="name"/><result property="price" column="price"/></collection></resultMap>
4.5.2.3 OrderTest.java 添加
@Testpublic void findOrdersWithProductByNestedResult(){SqlSession session = MyBatisUtil.openSession();OrdersMapper mapper = session.getMapper(OrdersMapper.class);List<Orders> ordersList = mapper.findOrdersWithProductByNestedResult(1);System.out.println("ordersList = " + ordersList);}
4.5.2.4 测试结果
DEBUG 04-21 23:38:47,365 ==> Preparing: select o.*, p.id as pid, p.name, p.price from tb_orders o, tb_product p, tb_ordersitem oi where oi.orders_id=o.id and oi.product_id=p.id and o.id=? (BaseJdbcLogger.java:159)
DEBUG 04-21 23:38:47,438 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159)
DEBUG 04-21 23:38:47,485 <== Total: 2 (BaseJdbcLogger.java:159)
ordersList = [Orders(id=1, number=1000011, userId=null, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]