前言

嵌套查詢的實(shí)現(xiàn)原理為兩次查詢，比如產(chǎn)品表為主表，圖片表為從表通過product_id字段與產(chǎn)品表id字段關(guān)聯(lián)實(shí)現(xiàn)一對多，嵌套查詢 首先查詢 主表的數(shù)據(jù) 然后將主表id字段賦值給從表實(shí)體類中product_id 字段（productId）然后通過dao接口路徑映射找到對應(yīng)的MyBatis XMl文件SQL語句ID如：com.liao.dao.DImgMapper.selectDImgByProductId 進(jìn)行子查詢也就是第二次查詢。然后返回?cái)?shù)據(jù)

數(shù)據(jù)庫建表語句和測試數(shù)據(jù)如下：

數(shù)據(jù)庫版本為 MySQL 8.0

產(chǎn)品表

DROP TABLE IF EXISTS `d_product`;CREATE TABLE `d_product` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ’產(chǎn)品ID’, `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT ’產(chǎn)品名稱’, `product_introduction` text CHARACTER SET utf8 COLLATE utf8_bin NULL COMMENT ’產(chǎn)品介紹’, `product_category` int(11) NULL DEFAULT NULL COMMENT ’產(chǎn)品ID’, `product_status` int(1) NULL DEFAULT NULL COMMENT ’產(chǎn)品狀態(tài)’, `create_time` datetime(0) NULL DEFAULT NULL COMMENT ’創(chuàng)建時間’, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = ’產(chǎn)品表’ ROW_FORMAT = Dynamic;INSERT INTO `d_product` VALUES (1, ’測試產(chǎn)品名稱修改’, ’測試產(chǎn)品介紹修改’, NULL, 1, ’2020-02-02 12:40:06’);INSERT INTO `d_product` VALUES (2, ’產(chǎn)品名稱’, ’產(chǎn)品介紹’, 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 ’產(chǎn)品圖片ID’, `img` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT ’圖片’, `create_time` datetime(0) NULL DEFAULT NULL COMMENT ’創(chuàng)建時間’, 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 = ’產(chǎn)品圖片’ 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實(shí)體類：

// 將這個注解寫在類上之后，就會忽略類中不存在的字段,否則可能會報(bào)錯@JsonIgnoreProperties(value = {'handler'})/** * * TODO: 產(chǎn)品類 * @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<DImg> dImgs; // 用于存放圖片集合 // .. get set toString} /** * * TODO: 產(chǎn)品圖片類 * @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}

實(shí)體類創(chuàng)建好后要編寫Dao接口 和Mapper XML了

持久層接口DAO：

/** * * TODO: 產(chǎn)品 Dao接口 * @author LiAo * @date 2020/5/20 17:08 */public interface DProductMapper { /** * 產(chǎn)品圖片一對多嵌套 * @param record 查詢條件 * @return 返回參數(shù) */ List<DProduct> productSelect(DProduct record);}

產(chǎn)品MyBatis xml：

<!--映射的Dao接口類 可以通過這個路徑找到先關(guān)的SQL語句和resultMap 映射--><mapper namespace='com.liao.dao.DProductMapper'> <resultMap type='com.liao.entity.DProduct'> <id column='id' property='id' jdbcType='INTEGER'/> <result column='product_name' property='productName' jdbcType='VARCHAR'/> <result column='product_category' property='productCategory' jdbcType='INTEGER'/> <result column='product_status' property='productStatus' jdbcType='INTEGER'/> <result column='create_time' property='createTime' jdbcType='TIMESTAMP'/> <result column='product_introduction' property='productIntroduction' jdbcType='LONGVARCHAR'/> </resultMap> <!--產(chǎn)品圖片一對多查詢映射--> <!--id：配置映射的名稱--> <!--type：返回值類型 --> <!--extends：繼承id為BaseResultMap的映射 --> <!--select：子查詢所在的XML綁定的DAO接口路徑.SQL語句id --> <!--column='{productId = id} productId：從表關(guān)聯(lián)主表的實(shí)體類屬性，作為子查詢的條件 id：主表中被關(guān)聯(lián)的數(shù)據(jù)庫字段--> <resultMap type='com.liao.entity.DProduct' extends='BaseResultMap'> <collection property='dImgs' fetchType='lazy' select='com.liao.dao.DImgMapper.selectDImgByProductId' column='{productId = id}'/> </resultMap> <!--查詢語句--> <select parameterType='com.liao.entity.DProduct' resultMap='dProductListMapSelect'> 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 <!-- 使用if標(biāo)簽拼接條件語句 實(shí)現(xiàn)動態(tài)SQL--> <if test='id != null and id != ’’'> and d.id = #{id} </if> <if test='productName != null and productName != ’’'> and d.product_name like concat(#{productName},’%’) </if> <if test='productStatus != null and productStatus != ’’'> and d.product_status = #{productStatus} </if> <if test='createTime != null and createTime != ’’'> and d.create_time like concat(#{createTime},’%’) </if> <if test='productIntroduction != null and productIntroduction != ’’'> and d.product_introduction like concat(#{productIntroduction},’%’) </if></select></mapper>

圖片MyBatis xml：

<mapper namespace='com.liao.dao.DImgMapper'> <resultMap type='com.liao.entity.DImg'> <id column='did' property='id' jdbcType='INTEGER'/> <result column='product_id' property='productId' jdbcType='INTEGER'/> <result column='img' property='img' jdbcType='VARCHAR'/> <result column='create_time' property='createTime' jdbcType='TIMESTAMP'/> </resultMap> <!--一對多嵌套查詢 子查詢語句--> <select resultMap='BaseResultMap'> select i.id as did, i.product_id, i.img, i.create_time from d_img i where i.product_id = #{productId} </select></mapper>

測試查詢結(jié)果

查詢結(jié)果為一個產(chǎn)品對象里有若干個產(chǎn)品圖片對象。

{ 'id': 18, 'productName': '產(chǎn)品添加圖片上傳測試', 'productCategory': null, 'productStatus': 1, 'createTime': '2020-04-14T13:40:40.000+0000', 'productIntroduction': '產(chǎn)品添加圖片上傳測試', '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' } ] },

總結(jié)

到此這篇關(guān)于MyBatis一對多嵌套查詢的文章就介紹到這了,更多相關(guān)MyBatis一對多嵌套查詢內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！