Java中一对多的子查询_MyBatis一对多嵌套查询的完整实例

前言

嵌套查询的实现原理为两次查询，比如产品表为主表，图片表为从表通过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 class 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,

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

"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"

}

]

},

总结

到此这篇关于MyBatis一对多嵌套查询的文章就介绍到这了,更多相关MyBatis一对多嵌套查询内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！