<collection property="orderList" ofType="net.biancheng.po.Order" column="id" select="net.biancheng.mapper.OrderMapper.selectOrderById" />在 <collection> 元素中通常使用以下属性。
CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ordernum` int(25) DEFAULT NULL, `userId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `userId` (`userId`), CONSTRAINT `order_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; insert into `order`(`id`,`ordernum`,`userId`) values (1,20200107,1),(2,20200806,2),(3,20206702,3),(4,20200645,1),(5,20200711,2),(6,20200811,2),(7,20201422,3),(8,20201688,4); DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `pwd` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; insert into `user`(`id`,`name`,`pwd`) values (1,'编程帮','123'),(2,'C语言中文网','456'),(3,'赵小红','123'),(4,'李晓明','345'),(5,'杨小胤','123'),(6,'谷小乐','789');
package net.biancheng.po; import java.util.List; public class User { private int id; private String name; private String pwd; private List<Order> orderList; /*省略setter和getter方法*/ @Override public String toString() { return "User [id=" + id + ", name=" + name + ", orderList=" + orderList + "]"; } }Order 类代码如下。
package net.biancheng.po; public class Order { private int id; private int ordernum; /*省略setter和getter方法*/ @Override public String toString() { return "Order [id=" + id + ", ordernum=" + ordernum + "]"; } }
public List<Order> selectOrderById(int id);OrderMapper.xml 中相应的映射 SQL 语句如下。
<!-- 根据id查询订单信息 --> <select id="selectOrderById" resultType="net.biancheng.po.Order" parameterType="Integer"> SELECT * FROM `order` where userId=#{id} </select>UserMapper 类代码如下。
public User selectUserOrderById1(int id);UserMapper.xml 中相应的映射 SQL 语句如下。
<!-- 一对多 根据id查询用户及其关联的订单信息:级联查询的第一种方法(分步查询) --> <resultMap type="net.biancheng.po.User" id="userAndOrder1"> <id property="id" column="id" /> <result property="name" column="name" /> <result property="pwd" column="pwd" /> <!-- 一对多级联查询,ofType表示集合中的元素类型,将id传递给selectOrderById --> <collection property="orderList" ofType="net.biancheng.po.Order" column="id" select="net.biancheng.mapper.OrderMapper.selectOrderById" /> </resultMap> <select id="selectUserOrderById1" parameterType="Integer" resultMap="userAndOrder1"> select * from user where id=#{id} </select>测试代码如下。
public class Test { public static void main(String[] args) throws IOException { InputStream config = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config); SqlSession ss = ssf.openSession(); User us = ss.getMapper(UserMapper.class).selectUserOrderById1(1); System.out.println(us); } }运行结果如下。
DEBUG [main] - ==> Preparing: select * from user where id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - ====> Preparing: SELECT * FROM `order` where userId=?
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <==== Total: 2
DEBUG [main] - <== Total: 1
User [id=1, name=编程帮, orderList=[Order [id=0, ordernum=20200107], Order [id=0, ordernum=20200645]]]
package net.biancheng.po; public class Order { private int oId; private int ordernum; /*省略setter和getter方法*/ @Override public String toString() { return "Order [id=" + oId+ ", ordernum=" + ordernum + "]"; } }UserMapper 类代码如下。
public User selectUserOrderById2(int id);UserMapper.xml 中相关映射 SQL 语句如下。
<!-- 一对多 根据id查询用户及其关联的订单信息:级联查询的第二种方法(单步查询) --> <resultMap type="net.biancheng.po.User" id="userAndOrder2"> <id property="id" column="id" /> <result property="name" column="name" /> <result property="pwd" column="pwd" /> <!-- 一对多级联查询,ofType表示集合中的元素类型 --> <collection property="orderList" ofType="net.biancheng.po.Order"> <id property="oId" column="oId" /> <result property="ordernum" column="ordernum" /> </collection> </resultMap> <select id="selectUserOrderById2" parameterType="Integer" resultMap="userAndOrder2"> SELECT u.*,o.id as oId,o.ordernum FROM `user` u,`order` o WHERE u.id=o.`userId` AND u.id=#{id} </select>测试代码修改调用方法,如下。
public class Test { public static void main(String[] args) throws IOException { InputStream config = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config); SqlSession ss = ssf.openSession(); User us = ss.getMapper(UserMapper.class).selectUserOrderById2(1); System.out.println(us); } }运行结果如下。
DEBUG [main] - ==> Preparing: SELECT u.*,o.id as oId,o.ordernum FROM `user` u,`order` o WHERE u.id=o.`userId` AND u.id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 2
User [id=1, name=编程帮, orderList=[Order [id=1, ordernum=20200107], Order [id=4, ordernum=20200645]]]
本文链接:http://task.lmcjl.com/news/16696.html