MyBatis一对多嵌套查询的完整实例

MyBatis一对多嵌套查询的完整实例

前EaDOwi言

嵌套查询的实现原理为两次查询，比如产品表为主表，图片表为从表通过product_id字段与产品表id字段关联实现一对多，嵌套查询 首先查询 主表的数据 然后将主表id字段赋值给从表实体类中product_id 字段（productId）然后通过dao接口路径映射找到对应的MyBatis XMl文件SQL语句ID如：com.liao.dao.DImgMapper.selectDImgByProductId 进行子查询也就是第二次查询。然后返回数据

数据库建表语句和测试数据如下：

数据库版本为 mysql 8.0

产品表

DROP TABLE IF EXISTS `d_product`;

CREATE TABLE `d_product` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品ID',

`product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '产品名称',

`product_introduction` text CHARACTER SET utf8 COLLATE utf8_bin NULL COMMENT '产品介绍',

`product_category` int(11) NULL DEFAULT NULL COMMENT '产品ID',

`product_status` int(1) NULL DEFAULT NULL COMMENT '产品状态',

`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '产品表' ROW_FORMAT = Dynamic;

INSERT INTO `d_product` VALUES (1, '测试产品名称修改', '测试产品介绍修改', NULL, 1, '2020-02-02 12:40:06');

INSERT INTO `d_product` VALUES (2, '产品名称', '产品介绍', NULL, 1, '2020-03-02 18:15:07');

INSERT INTO `d_product` VALUES (3, 'bbb', 'bbb', NULL, 1, '2020-03-01 22:18:40');

图片表

DROP TABLE IF EXISTS `d_img`;

CREATE TABLE `d_img` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',

`product_id` int(11) NULL DEFAULT NULL COMMENT '产品图片ID',

`img` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '图片',

`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

PRIMARY KEY (`id`) USING BTREE,

INDEX `product_id`(`product_id`) USING BTREE,

CONSTRAINT `d_img_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `d_product` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB AUTO_INCREMENT = 86 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '产品图片' ROW_FORMAT = Dynamic;

INSERT INTO `d_img` VALUES (40, 1, '1491803633034_683819.jpg', '2020-03-03 17:21:20');

INSERT INTO `d_img` VALUES (40, 1, '1491803633034_683819.jpg', '2020-03-03 17:21:20');

INSERT INTO `d_img` VALUES (41, 1, '1568950881751_702421.jpg', '2020-03-03 17:21:20');

java实体类：

// 将这个注解写在类上之后，就会忽略类中不存在的字段,否则可能会报错

@jsonIgnoreProperties(value = {"handler"})

/**

*

* TODO: 产品类

* @author LiAo

* @date 2020/5/20 17:04

*/

public clasEaDOwis DProduct {

private Integer id;

private String productName;

private Integer productCategory;

private Integer productStatus;

private Date createTime;

private String productIntroduction;

private List dImgs; // 用于存放图片集合

// .. get set toString

}

/**

*

* TODO: 产品图片类

* @author LiAo

* @date 2020/5/20 17:05

*/

@JsonIgnoreProperties(value = {"handler"})

public class DImg {

private Integer id;

private Integer productId;

private String img;

private Date createTime;

// .. get set toString

}

实体类创建好后要编写Dao接口 和Mapper XML了

持久层接口DAO：

/**

*

* TODO: 产品 Dao接口

* @author LiAo

* @date 2020/5/20 17:08

*/

public interface DProductMapper {

/**

* 产品图片一对多嵌套

* @param record 查询条件

* @return 返回参数

*/

List productSelect(DProduct record);

}

产品MyBatis xml：

select="com.liao.dao.DImgMapper.selectDImgByProductId" column="{productId = id}"/>

select

d.id,

d.product_name,

d.product_category,

d.product_status,

d.create_time,

d.product_introduction

from d_product d

where 1 = 1

and d.id = #{id}

and d.product_name like concat(#{productName},'%')

and d.product_status = #{productStatus}

and d.create_time like concat(#{createTime},'%')

and d.product_introduction like concat(#{productIntroduction},'%')

图片MyBatis xml：

select i.id as did,

i.product_id,

i.img,

i.create_time

from d_img i

where i.product_id = #{productId}

测试查询结果

查询结果为一个产品对象里有若干个产品图片对象。

{

"id": 18,

"produhttp://ctName": "产品添加图片上传测试",

"productCategory": null,

"productStatus": 1,

"createTime": "2020-04-14T13:40:40.000+0000",

"productIntroduction": "产品添加图片上传测试",

"dImgs": [

{

"id": 92,

"productId": 18,

"img": "01.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

},

{

"id": 93,

"productId": 18,

"img": "1554103835292_610234.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

},

{

"id": 94,

"productId": 18,

"img": "1555484699771_582172.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

},

{

"id": 95,

"productId": 18,

"img": "1554103835292_610234.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

}

]

},

总结

select="com.liao.dao.DImgMapper.selectDImgByProductId" column="{productId = id}"/>

select

d.id,

d.product_name,

d.product_category,

d.product_status,

d.create_time,

d.product_introduction

from d_product d

where 1 = 1

and d.id = #{id}

and d.product_name like concat(#{productName},'%')

and d.product_status = #{productStatus}

and d.create_time like concat(#{createTime},'%')

and d.product_introduction like concat(#{productIntroduction},'%')

图片MyBatis xml：

select i.id as did,

i.product_id,

i.img,

i.create_time

from d_img i

where i.product_id = #{productId}

测试查询结果

查询结果为一个产品对象里有若干个产品图片对象。

{

"id": 18,

"produhttp://ctName": "产品添加图片上传测试",

"productCategory": null,

"productStatus": 1,

"createTime": "2020-04-14T13:40:40.000+0000",

"productIntroduction": "产品添加图片上传测试",

"dImgs": [

{

"id": 92,

"productId": 18,

"img": "01.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

},

{

"id": 93,

"productId": 18,

"img": "1554103835292_610234.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

},

{

"id": 94,

"productId": 18,

"img": "1555484699771_582172.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

},

{

"id": 95,

"productId": 18,

"img": "1554103835292_610234.jpg",

"createTime": "2020-04-26T02:33:04.000+0000"

}

]

},

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。