目录
一、简介
二、商品服务数据库
三、es配置索引
四、搭建商品服务
五、前端页面
一、简介
本篇文章要完成的是搭建一个商品服务搜索引擎，使用elasticsearch(下文简称es)作为搜索引擎；
商品数据存放在mysql中，由canal将mysql的商品增量数据同步到es中。
同步方法参考docker安装canal同步mysql8与elasticsearch7数据，canal-adapter中的es7文件夹下的配置文件内容如下
dataSourceKey: defaultDS
destination: product_topic
groupId: g1
esMapping:
_index: products
_id: _id
# upsert: true
# pk: id
sql: "SELECT
p.product_id AS _id,p.product_id AS productId,p.category_id AS categoryId,p.product_name AS productName,
p.subtitle,p.product_specs AS productSpecs,p.product_price AS productPrice,
p.product_stock AS productStock,p.detail,p.master_pic AS masterPic,
p.revision,p.publish_status AS publishStatus,p.audit_status AS auditStatus,
p.created_time AS createdTime,p.updated_time AS updatedTime
FROM
p_product p"
# objFields:
# _labels: array:;
etlCondition: "where p.created_time>={}"
commitBatch: 3000
二、商品服务数据库
首先创建数据库shop-product
在电商项目中，常见的商品表结构，一般会有品牌信息表、供应商信息表、商品分类信息表、商品规格key表、商品规格值表、商品信息表、商品规格表、商品图片信息表、商品评论表等等。这里简单创建一下，本篇文章主要用到的是商品信息表，需要同步数据到es中。
商品信息表
CREATE TABLE `p_product` (
`product_id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`category_id` MEDIUMINT UNSIGNED DEFAULT NULL COMMENT '类型ID',
`product_core` VARCHAR (50) NOT NULL COMMENT '商品编码',
`product_name` VARCHAR (128) DEFAULT NULL COMMENT '商品名称',
`master_pic` VARCHAR (255) DEFAULT NULL COMMENT '商品主图url',
`bar_code` VARCHAR (50) NOT NULL COMMENT '国条码',
`brand_id` MEDIUMINT UNSIGNED NOT NULL COMMENT '品牌表的ID',
`supplier_id` MEDIUMINT UNSIGNED NOT NULL COMMENT '商品的供应商ID',
`subtitle` VARCHAR (128) DEFAULT NULL COMMENT '小标题',
`detail` text COMMENT '描述',
`product_specs` text DEFAULT NULL COMMENT '商品规格',
`product_price` DECIMAL (18, 2) DEFAULT NULL COMMENT '商品价格',
`average_cost` DECIMAL (18, 2) NOT NULL COMMENT '商品平均成本',
`product_stock` INT (11) DEFAULT NULL COMMENT '商品库存',
`publish_status` TINYINT NOT NULL DEFAULT 0 COMMENT '上下架状态：0下架,1上架',
`audit_status` TINYINT NOT NULL DEFAULT 0 COMMENT '审核状态：0未审核,1已审核',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`product_id`)
) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 COMMENT = '商品信息表';
规格、价格、成本、库存字段是商品的默认规格，这些字段是冗余的，且规格字段存的是中文json数据，好处是方便查询，但是维护会麻烦点，而因为规格key和value是极少会改动的，所以总体来说是利大于弊的。
测试数据insert sql如下：
INSERT INTO `p_product` VALUES ('1', '11', '000001', '华为平板电脑A', 'https://img12.360buyimg.com/n7/jfs/t1/119563/11/6985/235945/5ecdd380E9b984a9e/e007f2c894f13a73.jpg', 'asd010', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-12 6:51:52', '0', '2020-05-12 17:51:56');
INSERT INTO `p_product` VALUES ('2', '11', '000002', '华为平板电脑B', 'https://img14.360buyimg.com/n7/jfs/t1/133442/4/477/216580/5ece302dE5414ea07/2200718eac3eba3a.jpg', 'asd011', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-13 6:51:52', '0', '2020-05-13 17:51:56');
INSERT INTO `p_product` VALUES ('3', '11', '000003', '华为平板电脑C', 'https://img11.360buyimg.com/n7/jfs/t1/118394/33/8018/251520/5ec7ae58E7bc2cb20/f611db2d1f18ef56.jpg', 'asd012', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-14 6:51:52', '0', '2020-05-14 17:51:56');
INSERT INTO `p_product` VALUES ('4', '11', '000004', '华为平板电脑D', 'https://img14.360buyimg.com/n7/jfs/t1/116780/37/7910/318713/5ec7af51E06f50b6c/a758f67ca776cfa6.jpg', 'asd013', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-15 6:51:52', '0', '2020-05-15 17:51:56');
INSERT INTO `p_product` VALUES ('5', '11', '000005', '苹果平板电脑A', 'https://img13.360buyimg.com/n7/jfs/t1/118289/24/8477/153817/5ed0a805Eccf8e5b2/76512fecfdfdcbab.jpg', 'asd0110', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-16 6:51:52', '0', '2020-05-16 17:51:56');
INSERT INTO `p_product` VALUES ('6', '11', '000006', '苹果平板电脑B', 'https://img14.360buyimg.com/n7/jfs/t1/128018/39/3293/150835/5ed0a82aEc7e203e7/b27c625b4b616a14.jpg', 'asd0111', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3788.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-17 6:51:52', '0', '2020-05-17 17:51:56');
INSERT INTO `p_product` VALUES ('7', '11', '000007', '苹果平板电脑C', 'https://img14.360buyimg.com/n7/jfs/t1/112425/14/8565/156716/5ed0a9b6Ed1843861/bf7d3e8a224b545e.jpg', 'asd0112', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-18 6:51:52', '0', '2020-05-18 17:51:56');
INSERT INTO `p_product` VALUES ('8', '11', '000008', '苹果平板电脑D', 'https://img14.360buyimg.com/n7/jfs/t1/115467/38/8553/206863/5ed0a8ddE406d2eec/20692736c48b37b7.jpg', 'asd0113', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3988.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-19 6:51:52', '0', '2020-05-19 17:51:56');
INSERT INTO `p_product` VALUES ('9', '11', '000009', '苹果平板电脑E', 'https://img12.360buyimg.com/n7/jfs/t1/104885/36/17173/457290/5e836b1eE30543027/abf01c02a3bdaa1b.png', 'asd0114', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4088.00', '2000.00', '30', '0', '0', '1', '0', '2020-05-20 6:51:52', '0', '2020-05-20 17:51:56');
INSERT INTO `p_product` VALUES ('10', '11', '000010', '苹果平板电脑F', 'https://img13.360buyimg.com/n7/jfs/t1/126838/29/3303/152591/5ed0a7ceE9376bdf9/4e4e6ec09bc9da62.jpg', 'asd0115', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4188.00', '2000.00', '30', '0', '1', '1', '0', '2020-05-21 6:51:52', '0', '2020-05-21 17:51:56');
INSERT INTO `p_product` VALUES ('11', '11', '000011', '苹果平板电脑G', 'https://img14.360buyimg.com/n7/jfs/t1/121655/8/3367/170064/5ed0a4dfE61b62c99/202236788d2cff9a.jpg', 'asd0116', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4288.00', '2000.00', '30', '1', '0', '1', '0', '2020-05-22 6:51:52', '0', '2020-05-22 17:51:56');
INSERT INTO `p_product` VALUES ('12', '11', '0000012', '华为平板电脑1', 'https://img12.360buyimg.com/n7/jfs/t1/119752/23/6515/149043/5ec7add6E811c69e4/f00ab2dd2f7b1fe8.jpg', 'asd010', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-12 6:51:52', '0', '2020-05-12 17:51:56');
INSERT INTO `p_product` VALUES ('13', '11', '0000013', '华为平板电脑2', 'https://img10.360buyimg.com/n7/jfs/t1/133063/22/34/149081/5ec7adf9Efea69367/f4b5b91cb5afecd0.jpg', 'asd011', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-13 6:51:52', '0', '2020-05-13 17:51:56');
INSERT INTO `p_product` VALUES ('14', '11', '0000014', '华为平板电脑3', 'https://img11.360buyimg.com/n7/jfs/t1/122844/7/3131/104864/5ece4c0fE210c32aa/47b0018018e33282.jpg', 'asd012', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-14 6:51:52', '0', '2020-05-14 17:51:56');
INSERT INTO `p_product` VALUES ('15', '11', '0000015', '华为平板电脑4', 'https://img10.360buyimg.com/n7/jfs/t1/126892/34/3118/252890/5ecdd455Eb881c46c/6466eec5a6488a23.jpg', 'asd013', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-15 6:51:52', '0', '2020-05-15 17:51:56');
INSERT INTO `p_product` VALUES ('16', '11', '0000016', '苹果平板电脑1', 'https://img12.360buyimg.com/n7/jfs/t1/114653/14/8583/209048/5ed0a8bbE4d2f1886/0d411a0112bb959b.jpg', 'asd0110', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-16 6:51:52', '0', '2020-05-16 17:51:56');
INSERT INTO `p_product` VALUES ('17', '11', '0000017', '苹果平板电脑2', 'https://img12.360buyimg.com/n7/jfs/t1/116965/35/8385/156351/5ed0a9a6Eac0109fd/c2a1428b59799df3.jpg', 'asd0111', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3788.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-17 6:51:52', '0', '2020-05-17 17:51:56');
INSERT INTO `p_product` VALUES ('18', '11', '0000018', '苹果平板电脑3', 'https://img12.360buyimg.com/n7/jfs/t1/113826/22/2202/445567/5e9ffbaeEc403693f/638d2a79a1342bba.jpg', 'asd0112', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-18 6:51:52', '0', '2020-05-18 17:51:56');
INSERT INTO `p_product` VALUES ('19', '11', '0000019', '苹果平板电脑4', 'https://img11.360buyimg.com/n7/jfs/t1/137419/36/681/157591/5ed0a9ecEb4dacd40/f282c1aa5b171a05.jpg', 'asd0113', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3988.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-19 6:51:52', '0', '2020-05-19 17:51:56');
INSERT INTO `p_product` VALUES ('20', '11', '0000020', '苹果平板电脑5', 'https://img12.360buyimg.com/n7/jfs/t1/116431/14/8688/205487/5ed0a8cdE17e01166/28f972f5a5afa4e7.jpg', 'asd0114', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4088.00', '2000.00', '30', '0', '0', '1', '0', '2020-05-20 6:51:52', '0', '2020-05-20 17:51:56');
INSERT INTO `p_product` VALUES ('21', '11', '0000121', '苹果平板电脑6', 'https://img13.360buyimg.com/n7/jfs/t1/122335/4/3195/185668/5ecf380dEa232d016/f359b8bb171296f5.jpg', 'asd0115', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4188.00', '2000.00', '30', '0', '1', '1', '0', '2020-05-21 6:51:52', '0', '2020-05-21 17:51:56');
INSERT INTO `p_product` VALUES ('22', '11', '0000122', '苹果平板电脑7', 'https://img12.360buyimg.com/n7/jfs/t1/119554/38/2535/238209/5eaaac27E6d5daf00/4abf045d75f6a06c.jpg', 'asd0116', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4288.00', '2000.00', '30', '1', '0', '1', '0', '2020-05-22 6:51:52', '0', '2020-05-22 17:51:56');
INSERT INTO `p_product` VALUES ('23', '11', '0000023', '华为平板电脑5', 'https://img12.360buyimg.com/n7/jfs/t25924/242/2318109027/297349/c0e6de9/5be3e1b4N58351a43.jpg', 'asd010', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-12 6:51:52', '0', '2020-05-12 17:51:56');
INSERT INTO `p_product` VALUES ('24', '11', '0000024', '华为平板电脑6', 'https://img10.360buyimg.com/n7/jfs/t1/68214/40/14363/70089/5dbbf32bE882c1f08/f4eb748e9650e20e.jpg', 'asd011', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-13 6:51:52', '0', '2020-05-13 17:51:56');
INSERT INTO `p_product` VALUES ('25', '11', '0000025', '华为平板电脑7', 'https://img13.360buyimg.com/n7/jfs/t1/111356/19/8327/235863/5ecdd3b6E9a2817be/5321903a70171189.jpg', 'asd012', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-14 6:51:52', '0', '2020-05-14 17:51:56');
INSERT INTO `p_product` VALUES ('26', '11', '0000026', '华为平板电脑8', 'https://img13.360buyimg.com/n7/jfs/t1/68591/11/14353/295051/5dbbf479Ec5ab60a5/4ed636251695ee17.jpg', 'asd013', '1', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-15 6:51:52', '0', '2020-05-15 17:51:56');
INSERT INTO `p_product` VALUES ('27', '11', '0000027', '苹果平板电脑8', 'https://img13.360buyimg.com/n7/jfs/t1/122335/4/3195/185668/5ecf380dEa232d016/f359b8bb171296f5.jpg', 'asd0110', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-16 6:51:52', '0', '2020-05-16 17:51:56');
INSERT INTO `p_product` VALUES ('28', '11', '0000028', '苹果平板电脑9', 'https://img14.360buyimg.com/n7/jfs/t1/128849/21/3328/178921/5ed0a750Ecdd939aa/e6a2f4ba5ec879ea.jpg', 'asd0111', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3788.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-17 6:51:52', '0', '2020-05-17 17:51:56');
INSERT INTO `p_product` VALUES ('29', '11', '0000029', '苹果平板电脑10', 'https://img13.360buyimg.com/n7/jfs/t1/116670/8/8257/84420/5ece50fbE51fe7612/7af07b87fc773625.jpg', 'asd0112', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3888.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-18 6:51:52', '0', '2020-05-18 17:51:56');
INSERT INTO `p_product` VALUES ('30', '11', '0000030', '苹果平板电脑11', 'https://img12.360buyimg.com/n7/jfs/t1/123344/23/3284/183856/5ecf3819Efe1911ea/c6556b9bb62978fa.jpg', 'asd0113', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '3988.00', '2000.00', '30', '1', '1', '1', '0', '2020-05-19 6:51:52', '0', '2020-05-19 17:51:56');
INSERT INTO `p_product` VALUES ('31', '11', '0000031', '苹果平板电脑12', 'https://img11.360buyimg.com/n7/jfs/t1/116153/16/7446/220905/5ec29167E3b003e49/535941d8757967d8.jpg', 'asd0114', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4088.00', '2000.00', '30', '0', '0', '1', '0', '2020-05-20 6:51:52', '0', '2020-05-20 17:51:56');
INSERT INTO `p_product` VALUES ('32', '11', '0000132', '苹果平板电脑13', 'https://img13.360buyimg.com/n7/jfs/t1/67358/33/12694/465697/5d9f2d1dEd9db3175/e5c1aa68d4697567.png', 'asd0115', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4188.00', '2000.00', '30', '0', '1', '1', '0', '2020-05-21 6:51:52', '0', '2020-05-21 17:51:56');
INSERT INTO `p_product` VALUES ('33', '11', '0000133', '苹果平板电脑14', 'https://img13.360buyimg.com/n7/jfs/t1/118870/30/2103/330459/5e9fb83aE889b702c/5447f1d5870a20f1.png', 'asd0116', '2', '1', '好看又好用的平板电脑', '真好用', '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', '4288.00', '2000.00', '30', '1', '0', '1', '0', '2020-05-22 6:51:52', '0', '2020-05-22 17:51:56');
品牌信息表
CREATE TABLE `p_brand` (
`brand_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '品牌ID',
`brand_name` VARCHAR (50) NOT NULL COMMENT '品牌名称',
`telephone` VARCHAR (50) NOT NULL COMMENT '联系电话',
`brand_web` VARCHAR (100) COMMENT '品牌网络',
`brand_logo` VARCHAR (255) COMMENT '品牌logo URL',
`brand_desc` text COMMENT '品牌描述',
`brand_status` TINYINT NOT NULL DEFAULT 0 COMMENT '品牌状态,0禁用,1启用',
`brand_order` MEDIUMINT NOT NULL DEFAULT 1 COMMENT '排序 值越小排序越前',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`brand_id`)
) ENGINE = INNODB AUTO_INCREMENT = 13 DEFAULT CHARSET = utf8 COMMENT = '品牌信息表';
供应商信息表
CREATE TABLE `p_supplier` (
`supplier_id` MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '供应商ID',
`supplier_code` VARCHAR (50) NOT NULL COMMENT '供应商编码',
`supplier_name` VARCHAR (50) NOT NULL COMMENT '供应商名称',
`supplier_type` TINYINT NOT NULL COMMENT '供应商类型：1.自营，2.平台',
`link_man` VARCHAR (10) NOT NULL COMMENT '供应商联系人',
`phone_number` VARCHAR (50) NOT NULL COMMENT '联系电话',
`bank_name` VARCHAR (50) NOT NULL COMMENT '供应商开户银行名称',
`bank_account` VARCHAR (50) NOT NULL COMMENT '银行账号',
`address` VARCHAR (255) NOT NULL COMMENT '供应商地址',
`supplier_status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0禁止，1启用',
`supplier_order` MEDIUMINT NOT NULL DEFAULT 1 COMMENT '排序 值越小排序越前',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (supplier_id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='供应商信息表';
商品分类信息表
CREATE TABLE `p_product_category` (
`category_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`parent_id` MEDIUMINT UNSIGNED DEFAULT NULL COMMENT '父ID',
`name` VARCHAR (128) DEFAULT NULL COMMENT '名称',
`category_status` TINYINT DEFAULT 0 COMMENT '状态 0=禁用,1=启用',
`category_order` MEDIUMINT DEFAULT 1 COMMENT '分类顺序 值越小排序越前',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`category_id`)
) ENGINE = INNODB AUTO_INCREMENT = 13 DEFAULT CHARSET = utf8 COMMENT = '商品分类信息表';
这个表存的是分类数据，比如电脑办公->电脑整机->笔记本这样的分类，通过parent_id字段关联，可无限层级的关联。
商品规格key表
CREATE TABLE `p_attribute_key` (
`attribute_key_id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`category_id` MEDIUMINT UNSIGNED DEFAULT NULL COMMENT '分类ID',
`attribute_name` VARCHAR (32) DEFAULT NULL COMMENT '属性名称',
`key_status` TINYINT DEFAULT 0 COMMENT '状态 0=禁用,1=启用',
`key_order` INT (11) DEFAULT 1 COMMENT 'key排序 值越小排序越前',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`attribute_key_id`)
) ENGINE = INNODB AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8 COMMENT = '商品规格Key表 ';
在电商中，商品通常都是有多规格的，比如笔记本4G内存和8G内存是不同的价格。而这个表存的是商品的规格key，通过category_id关联商品分类表，比如关联的是笔记本分类，那么规格key可以有内存、颜色、年份、尺寸等。
商品规格值表
CREATE TABLE `p_attribute_value` (
`attribute_value_id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`attribute_key_id` INT (11) UNSIGNED DEFAULT NULL COMMENT '属性ID',
`attribute_value` VARCHAR (32) DEFAULT NULL COMMENT '属性值',
`value_status` TINYINT DEFAULT 0 COMMENT '状态 0=禁用,1=启用',
`value_order` INT (11) DEFAULT 1 COMMENT '值排序 值越小排序越前',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`attribute_value_id`)
) ENGINE = INNODB AUTO_INCREMENT = 15 DEFAULT CHARSET = utf8 COMMENT = '商品规格值表 ';
那么这个表就是存的规格value值了，通过attribute_key_id字段关联规格key表，比如关联的key是内存，那么value的值可以有4G、8G、16G等。
商品规格表
CREATE TABLE `p_product_specs` (
`product_specs_id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`product_id` INT (11) UNSIGNED DEFAULT NULL COMMENT '商品ID',
`product_specs` text COMMENT '商品规格',
`specs_order` INT (11) DEFAULT 1 COMMENT '规格顺序 值越小排序越前',
`product_stock` INT (11) DEFAULT NULL COMMENT '商品库存',
`product_price` DECIMAL (32, 2) DEFAULT NULL COMMENT '商品价格',
`average_cost` DECIMAL (18, 2) NOT NULL COMMENT '商品平均成本',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`product_specs_id`)
) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '商品规格表 ';
商品图片信息表
CREATE TABLE `p_product_pic` (
`product_pic_id` INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '商品图片ID',
`product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
`pic_desc` VARCHAR (50) COMMENT '图片描述',
`pic_url` VARCHAR (200) NOT NULL COMMENT '图片URL',
`is_master` TINYINT NOT NULL DEFAULT 0 COMMENT '是否主图：0.非主图1.主图',
`pic_order` TINYINT NOT NULL DEFAULT 1 COMMENT '图片排序',
`pic_status` TINYINT NOT NULL DEFAULT 0 COMMENT '图片是否有效：0无效 1有效',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (product_pic_id)
) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '商品图片信息表';
商品评论表
CREATE TABLE p_product_comment (
`comment_id` INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '评论ID',
`product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
`order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
`title` VARCHAR (255) NOT NULL COMMENT '评论标题',
`content` text NOT NULL COMMENT '评论内容',
`audit_status` TINYINT NOT NULL DEFAULT 0 COMMENT '审核状态：0未审核，1已审核',
`revision` INT (11) UNSIGNED DEFAULT 1 COMMENT '乐观锁',
`created_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '创建人',
`created_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
`updated_by` INT (11) UNSIGNED DEFAULT NULL COMMENT '更新人',
`updated_time` TIMESTAMP DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (comment_id)
) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '商品评论表 ';
三、es配置索引
使用postman添加es索引products
索引结构如下：
{
"mappings":{
"properties": {
"productId":{
"type": "long"
},
"categoryId": {
"type": "long"
},
"productName": {
"type": "text",
"analyzer":"ik_smart",
"search_analyzer":"ik_smart"
},
"subtitle": {
"type": "text",
"analyzer":"ik_smart",
"search_analyzer":"ik_smart"
},
"productSpecs": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"productPrice": {
"type": "double"
},
"productStock":{
"type": "long"
},
"detail": {
"type": "text",
"analyzer":"ik_smart",
"search_analyzer":"ik_smart"
},
"masterPic": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"revision": {
"type": "long"
},
"publishStatus": {
"type": "long"
},
"auditStatus": {
"type": "long"
},
"createdTime": {
"type": "date",
"store": true
},
"updatedTime": {
"type": "date",
"store": true
}
}
}
}
同步数据后使用postman搜索查看
四、搭建商品服务
1、创建跟商品有关的模块
2、创建商品搜索dto输出类
package com.liazhan.product.output.dto;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
/**
* @version:V1.0
* @Description:
* @author: Liazhan
* @date 2020/5/24 17:11
*/
@Data
@ApiModel(value = "商品搜索输出实体类")
public class ProductSearchOutDto {
@ApiModelProperty(value = "商品主键ID")
private Integer productId;
@ApiModelProperty(value = "类型ID")
private Integer categoryId;
@ApiModelProperty(value = "商品名称")
private String productName;
@ApiModelProperty(value = "小标题")
private String subtitle;
@ApiModelProperty(value = "商品规格")
private String productSpecs;
@ApiModelProperty(value = "商品价格")
private Double productPrice;
@ApiModelProperty(value = "商品库存")
private Integer productStock;
@ApiModelProperty(value = "商品描述")
private String detail;
@ApiModelProperty(value = "主图")
private String masterPic;
@ApiModelProperty(value = "乐观锁")
private Integer revision;
@ApiModelProperty(value = "上下架状态 0下架 1上架")
private Integer publishStatus;
@ApiModelProperty(value = "审核状态 0未审核 1已审核")
private Integer auditStatus;
@ApiModelProperty(value = "创建时间")
private Date createdTime;
@ApiModelProperty(value = "更新时间")
private Date updatedTime;
}
3、shop-service-api-product模块添加dto依赖和创建商品服务搜索相关接口
pom如下：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>shop-service-api</artifactId>
<groupId>com.liazhan</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>shop-service-api-product</artifactId>
<packaging>jar</packaging>
<dependencies>
<!--product dto-->
<dependency>
<artifactId>shop-api-product-dto</artifactId>
<groupId>com.liazhan</groupId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
</dependencies>
</project>
接口如下：
package com.liazhan.product.service;
import com.alibaba.fastjson.JSONObject;
import com.liazhan.base.BaseResponse;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.data.domain.Pageable;
import org.springframework.data.web.PageableDefault;
import org.springframework.web.bind.annotation.GetMapping;
/**
* @version V1.0
* @description:
* @author: Liazhan
* @date: 2020/5/25 22:22
*/
@Api(tags = "商品服务搜索相关接口")
public interface ProductSearchService {
@GetMapping("/search")
@ApiOperation(value = "搜索接口")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "query",name = "keyword",dataType = "String",required = true,value = "搜索关键字"),
@ApiImplicitParam(paramType = "query",name = "page",dataType = "Integer",required = false,value = "第几页,从0开始,默认为0"),
@ApiImplicitParam(paramType = "query",name = "size",dataType = "Integer",required = false,value = "一页几条,默认为10"),
@ApiImplicitParam(paramType = "query",name = "sort",dataType = "String",required = false,value = "排序相关的信息,比如sort=productId&sort=createTime,desc 表示按照主键正序排列基础上按创建时间倒序排列")
})
public BaseResponse<JSONObject> search(String keyword, @PageableDefault(page = 0,value = 10) Pageable pageable);
}
4、shop-service-impl-product模块添加依赖，常量类，es实体类，mapper，接口实现类，启动类，配置文件
目录如下：
pom文件如下：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>shop-service-impl</artifactId>
<groupId>com.liazhan</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>shop-service-impl-product</artifactId>
<packaging>jar</packaging>
<dependencies>
<!-- product api-->
<dependency>
<artifactId>shop-service-api-product</artifactId>
<groupId>com.liazhan</groupId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
<!--elasticsearch-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-elasticsearch</artifactId>
</dependency>
<!--bean映射工具,深度拷贝-->
<dependency>
<groupId>ma.glasnost.orika</groupId>
<artifactId>orika-core</artifactId>
<version>1.5.2</version>
</dependency>
<!--beanutils-->
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
</dependencies>
</project>
常量类如下：
package com.liazhan.product.consts;
/**
* @version:V1.0
* @Description: 常量类
* @author: Liazhan
* @date 2020/5/28 15:36
*/
public interface ProductConst {
//商品表商品主键列
String PRODUCT_ID_COLUMN = "productId";
//商品表商品名称列
String PRODUCT_NAME_COLUMN = "productName";
//商品表商品小标题列
String SUBTITLE_COLUMN = "subtitle";
//商品表商品描述列
String DETAIL_COLUMN = "detail";
//商品表商品上架状态列
String PUBLISH_STATUS_COLUMN = "publishStatus";
//商品表商品审核状态列
String AUDIT_STATUS_COLUMN = "auditStatus";
//商品表商品创建时间列
String CREATED_TIME_COLUMN = "createdTime";
//商品已上架状态
Integer PUBLISHED = 1;
//商品已审核状态
Integer AUDITED = 1;
//筛选起始时间
String SCREEN_START_TIME = "2020-05-11 16:00:00";
//筛选结束时间
String SCREEN_END_TIME = "2020-05-18 16:00:00";
//筛选时间格式
String SCREEN_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
//es关键词高亮前缀
String HIGHLIGHT_PRETAGS = "<span style=\"color:red\">";
//es关键词高亮后缀
String HIGHLIGHT_POSTTAGS = "</span>";
//高亮字段显示的最长长度,不配置可能导致高亮不全,文章内容缺失等问题 最大高亮分片数800000
Integer HIGHLIGHT_FRAGMENTSIZE = 800000;
//从第一个分片获取高亮字段
Integer HIGHLIGHT_NUMOFFRAGMENTS = 0;
}
实体类如下：
package com.liazhan.product.es.entity;
import lombok.Data;
import org.springframework.data.annotation.Id;
import org.springframework.data.elasticsearch.annotations.Document;
import java.util.Date;
/**
* @version V1.0
* @description: es商品索引
* @author: Liazhan
* @date: 2020/5/25 22:40
*/
@Document(indexName = "products",type = "_doc")
@Data
public class ProductEntity {
//主键id
@Id
private Integer productId;
//类型id
private Integer categoryId;
//商品名称
private String productName;
//小标题
private String subtitle;
//商品规格
private String productSpecs;
//商品价格
private Double productPrice;
//商品库存
private Integer productStock;
//商品详情
private String detail;
//主图
private String masterPic;
//乐观锁
private Integer revision;
//上下架状态 0下架 1上架
private Integer publishStatus;
//审核状态 0未审核 1已审核
private Integer auditStatus;
//创建时间
private Date createdTime;
//更新时间
private Date updatedTime;
}
mapper如下：
package com.liazhan.product.es.mapper;
import com.fasterxml.jackson.core.JsonEncoding;
import com.fasterxml.jackson.core.JsonFactory;
import com.fasterxml.jackson.core.JsonGenerator;
import org.apache.commons.beanutils.PropertyUtils;
import org.elasticsearch.action.get.GetResponse;
import org.elasticsearch.action.get.MultiGetItemResponse;
import org.elasticsearch.action.get.MultiGetResponse;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.common.document.DocumentField;
import org.elasticsearch.common.text.Text;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.fetch.subphase.highlight.HighlightField;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.data.domain.Pageable;
import org.springframework.data.elasticsearch.ElasticsearchException;
import org.springframework.data.elasticsearch.annotations.Document;
import org.springframework.data.elasticsearch.annotations.ScriptedField;
import org.springframework.data.elasticsearch.core.AbstractResultMapper;
import org.springframework.data.elasticsearch.core.DefaultEntityMapper;
import org.springframework.data.elasticsearch.core.EntityMapper;
import org.springframework.data.elasticsearch.core.aggregation.AggregatedPage;
import org.springframework.data.elasticsearch.core.aggregation.impl.AggregatedPageImpl;
import org.springframework.data.elasticsearch.core.mapping.ElasticsearchPersistentEntity;
import org.springframework.data.elasticsearch.core.mapping.ElasticsearchPersistentProperty;
import org.springframework.data.elasticsearch.core.mapping.SimpleElasticsearchMappingContext;
import org.springframework.data.mapping.PersistentPropertyAccessor;
import org.springframework.data.mapping.context.MappingContext;
import org.springframework.data.mapping.model.ConvertingPropertyAccessor;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.nio.charset.Charset;
import java.util.*;
/**
* @version:V1.0
* @Description: ResultMapper用来将es文档转换为Java对象，由于SpringDataElasticsearch默认的映射类
* DefaultResultMapper不支持高亮,这里我们自定义一个,步骤如下：
* 1.在idea中按Ctrl+Shift+Alt+N快捷键搜索DefaultResultMapper类
* 2.复制DefaultResultMapper类,修改类名为ExtResultMapper,构造方法名称也改一下
* 3.新增方法populateHighLightedFields,作用是将高亮的内容赋值给需要转换的Java对象中
* 该方法需要添加commons-beanutils依赖
* 4.在mapResults方法中调用我们新增的方法populateHighLightedFields
* @author: Liazhan
* @date 2020/5/28 14:25
*/
@Component
public class ExtResultMapper extends AbstractResultMapper {
private final MappingContext<? extends ElasticsearchPersistentEntity<?>, ElasticsearchPersistentProperty> mappingContext;
private final ConversionService conversionService;
public ExtResultMapper() {
this((MappingContext)(new SimpleElasticsearchMappingContext()));
}
public ExtResultMapper(MappingContext<? extends ElasticsearchPersistentEntity<?>, ElasticsearchPersistentProperty> mappingContext) {
this(mappingContext, initEntityMapper(mappingContext));
}
public ExtResultMapper(EntityMapper entityMapper) {
this(new SimpleElasticsearchMappingContext(), entityMapper);
}
public ExtResultMapper(MappingContext<? extends ElasticsearchPersistentEntity<?>, ElasticsearchPersistentProperty> mappingContext, @Nullable EntityMapper entityMapper) {
super(entityMapper != null ? entityMapper : initEntityMapper(mappingContext));
this.conversionService = new DefaultConversionService();
this.mappingContext = mappingContext;
}
private static EntityMapper initEntityMapper(MappingContext<? extends ElasticsearchPersistentEntity<?>, ElasticsearchPersistentProperty> mappingContext) {
Assert.notNull(mappingContext, "MappingContext must not be null!");
return new DefaultEntityMapper(mappingContext);
}
public <T> AggregatedPage<T> mapResults(SearchResponse response, Class<T> clazz, Pageable pageable) {
long totalHits = response.getHits().getTotalHits();
float maxScore = response.getHits().getMaxScore();
List<T> results = new ArrayList();
Iterator var8 = response.getHits().iterator();
while(var8.hasNext()) {
SearchHit hit = (SearchHit)var8.next();
if (hit != null) {
T result = null;
String hitSourceAsString = hit.getSourceAsString();
if (!StringUtils.isEmpty(hitSourceAsString)) {
result = this.mapEntity(hitSourceAsString, clazz);
} else {
result = this.mapEntity(hit.getFields().values(), clazz);
}
this.setPersistentEntityId(result, hit.getId(), clazz);
this.setPersistentEntityVersion(result, hit.getVersion(), clazz);
this.setPersistentEntityScore(result, hit.getScore(), clazz);
this.populateScriptFields(result, hit);
//在这里调用我们新增的方法,支持高亮查询
this.populateHighLightedFields(result,hit.getHighlightFields());
results.add(result);
}
}
return new AggregatedPageImpl(results, pageable, totalHits, response.getAggregations(), response.getScrollId(), maxScore);
}
/**
* 这是我们新增的方法，将高亮的内容赋值给需要转换的Java对象中
* @param result
* @param highlightFields
* @param <T>
*/
private <T> void populateHighLightedFields(T result, Map<String, HighlightField> highlightFields) {
for (HighlightField field : highlightFields.values()) {
try {
PropertyUtils.setProperty(result, field.getName(), concat(field.fragments()));
} catch (InvocationTargetException | IllegalAccessException | NoSuchMethodException e) {
throw new ElasticsearchException("failed to set highlighted value for field: " + field.getName()
+ " with value: " + Arrays.toString(field.getFragments()), e);
}
}
}
/**
* 这也是新增的方法，供populateHighLightedFields方法调用
* @param texts
* @return
*/
private String concat(Text[] texts) {
StringBuffer sb = new StringBuffer();
for (Text text : texts) {
sb.append(text.toString());
}
return sb.toString();
}
private <T> void populateScriptFields(T result, SearchHit hit) {
if (hit.getFields() != null && !hit.getFields().isEmpty() && result != null) {
Field[] var3 = result.getClass().getDeclaredFields();
int var4 = var3.length;
for(int var5 = 0; var5 < var4; ++var5) {
Field field = var3[var5];
ScriptedField scriptedField = (ScriptedField)field.getAnnotation(ScriptedField.class);
if (scriptedField != null) {
String name = scriptedField.name().isEmpty() ? field.getName() : scriptedField.name();
DocumentField searchHitField = (DocumentField)hit.getFields().get(name);
if (searchHitField != null) {
field.setAccessible(true);
try {
field.set(result, searchHitField.getValue());
} catch (IllegalArgumentException var11) {
throw new ElasticsearchException("failed to set scripted field: " + name + " with value: " + searchHitField.getValue(), var11);
} catch (IllegalAccessException var12) {
throw new ElasticsearchException("failed to access scripted field: " + name, var12);
}
}
}
}
}
}
private <T> T mapEntity(Collection<DocumentField> values, Class<T> clazz) {
return this.mapEntity(this.buildJSONFromFields(values), clazz);
}
private String buildJSONFromFields(Collection<DocumentField> values) {
JsonFactory nodeFactory = new JsonFactory();
try {
ByteArrayOutputStream stream = new ByteArrayOutputStream();
JsonGenerator generator = nodeFactory.createGenerator(stream, JsonEncoding.UTF8);
generator.writeStartObject();
Iterator var5 = values.iterator();
while(true) {
while(var5.hasNext()) {
DocumentField value = (DocumentField)var5.next();
if (value.getValues().size() > 1) {
generator.writeArrayFieldStart(value.getName());
Iterator var7 = value.getValues().iterator();
while(var7.hasNext()) {
Object val = var7.next();
generator.writeObject(val);
}
generator.writeEndArray();
} else {
generator.writeObjectField(value.getName(), value.getValue());
}
}
generator.writeEndObject();
generator.flush();
return new String(stream.toByteArray(), Charset.forName("UTF-8"));
}
} catch (IOException var9) {
return null;
}
}
public <T> T mapResult(GetResponse response, Class<T> clazz) {
T result = this.mapEntity(response.getSourceAsString(), clazz);
if (result != null) {
this.setPersistentEntityId(result, response.getId(), clazz);
this.setPersistentEntityVersion(result, response.getVersion(), clazz);
}
return result;
}
public <T> List<T> mapResults(MultiGetResponse responses, Class<T> clazz) {
List<T> list = new ArrayList();
MultiGetItemResponse[] var4 = responses.getResponses();
int var5 = var4.length;
for(int var6 = 0; var6 < var5; ++var6) {
MultiGetItemResponse response = var4[var6];
if (!response.isFailed() && response.getResponse().isExists()) {
T result = this.mapEntity(response.getResponse().getSourceAsString(), clazz);
this.setPersistentEntityId(result, response.getResponse().getId(), clazz);
this.setPersistentEntityVersion(result, response.getResponse().getVersion(), clazz);
list.add(result);
}
}
return list;
}
private <T> void setPersistentEntityId(T result, String id, Class<T> clazz) {
if (clazz.isAnnotationPresent(Document.class)) {
ElasticsearchPersistentEntity<?> persistentEntity = (ElasticsearchPersistentEntity)this.mappingContext.getRequiredPersistentEntity(clazz);
ElasticsearchPersistentProperty idProperty = (ElasticsearchPersistentProperty)persistentEntity.getIdProperty();
PersistentPropertyAccessor<T> accessor = new ConvertingPropertyAccessor(persistentEntity.getPropertyAccessor(result), this.conversionService);
if (idProperty != null && idProperty.getType().isAssignableFrom(String.class)) {
accessor.setProperty(idProperty, id);
}
}
}
private <T> void setPersistentEntityVersion(T result, long version, Class<T> clazz) {
if (clazz.isAnnotationPresent(Document.class)) {
ElasticsearchPersistentEntity<?> persistentEntity = (ElasticsearchPersistentEntity)this.mappingContext.getPersistentEntity(clazz);
ElasticsearchPersistentProperty versionProperty = persistentEntity.getVersionProperty();
if (versionProperty != null && versionProperty.getType().isAssignableFrom(Long.class)) {
Assert.isTrue(version != -1L, "Version in response is -1");
persistentEntity.getPropertyAccessor(result).setProperty(versionProperty, version);
}
}
}
private <T> void setPersistentEntityScore(T result, float score, Class<T> clazz) {
if (clazz.isAnnotationPresent(Document.class)) {
ElasticsearchPersistentEntity<?> entity = (ElasticsearchPersistentEntity)this.mappingContext.getRequiredPersistentEntity(clazz);
if (!entity.hasScoreProperty()) {
return;
}
entity.getPropertyAccessor(result).setProperty(entity.getScoreProperty(), score);
}
}
}
接口实现类如下：
package com.liazhan.product.service.impl;
import com.alibaba.fastjson.JSONObject;
import com.liazhan.base.BaseResponse;
import com.liazhan.base.BaseServiceImpl;
import com.liazhan.product.consts.ProductConst;
import com.liazhan.product.es.entity.ProductEntity;
import com.liazhan.product.es.mapper.ExtResultMapper;
import com.liazhan.product.output.dto.ProductSearchOutDto;
import com.liazhan.product.service.ProductSearchService;
import ma.glasnost.orika.impl.DefaultMapperFactory;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.fetch.subphase.highlight.HighlightBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.elasticsearch.core.ElasticsearchTemplate;
import org.springframework.data.elasticsearch.core.aggregation.AggregatedPage;
import org.springframework.data.elasticsearch.core.query.NativeSearchQuery;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @version V1.0
* @description: 商品服务搜索相关接口实现类
* @author: Liazhan
* @date: 2020/5/25 22:35
*/
@RestController
public class ProductSearchServiceImpl extends BaseServiceImpl<JSONObject> implements ProductSearchService {
@Autowired
private ElasticsearchTemplate elasticsearchTemplate;
@Autowired
private ExtResultMapper extResultMapper;
@Override
public BaseResponse<JSONObject> search(String keyword, Pageable pageable) {
/*
1.拼接查询条件
根据关键字模糊搜索 productName、subtitle、detail列;
审核状态为审核通过,上架状态为已上架;
范围搜索创建时间,由于时区问题elasticsearch里的时间少了8小时，因此这里搜索日期条件要减8小时
*/
BoolQueryBuilder boolQuery = QueryBuilders.boolQuery();
boolQuery.must(QueryBuilders.multiMatchQuery(keyword,
ProductConst.PRODUCT_NAME_COLUMN,
ProductConst.SUBTITLE_COLUMN,
ProductConst.DETAIL_COLUMN))
.must(QueryBuilders.termQuery(ProductConst.PUBLISH_STATUS_COLUMN,
ProductConst.PUBLISHED))
.must(QueryBuilders.termQuery(ProductConst.AUDIT_STATUS_COLUMN,
ProductConst.AUDITED))
.must(QueryBuilders.rangeQuery(ProductConst.CREATED_TIME_COLUMN)
.format(ProductConst.SCREEN_TIME_FORMAT)
.from(ProductConst.SCREEN_START_TIME)
.to(ProductConst.SCREEN_END_TIME));
/*2.配置关键字高亮 */
HighlightBuilder highlightBuilder = new HighlightBuilder();
//需要高亮的字段
highlightBuilder.field(ProductConst.PRODUCT_NAME_COLUMN);
highlightBuilder.field(ProductConst.SUBTITLE_COLUMN);
highlightBuilder.field(ProductConst.DETAIL_COLUMN);
//多个字段该项设置为false
highlightBuilder.requireFieldMatch(false);
//高亮前缀
highlightBuilder.preTags(ProductConst.HIGHLIGHT_PRETAGS);
//高亮后缀
highlightBuilder.postTags(ProductConst.HIGHLIGHT_POSTTAGS);
//高亮字段显示的最长长度,不配置可能导致高亮不全,文章内容缺失等问题
highlightBuilder.fragmentSize(ProductConst.HIGHLIGHT_FRAGMENTSIZE);
//从第一个分片获取高亮字段
highlightBuilder.numOfFragments(ProductConst.HIGHLIGHT_NUMOFFRAGMENTS);
/*3.若前端没传排序参数,则根据主键和创建时间降序排列*/
Sort sort = pageable.getSort();
if(sort.isUnsorted()) {
Sort.Order order1 = new Sort.Order(Sort.Direction.DESC, ProductConst.PRODUCT_ID_COLUMN);
Sort.Order order2 = new Sort.Order(Sort.Direction.DESC, ProductConst.CREATED_TIME_COLUMN);
sort = Sort.by(order1, order2);
}
/*4.重新组装Pageable*/
int pageNumber = pageable.getPageNumber(); //第几页
int pageSize = pageable.getPageSize(); //一页几条
pageable = PageRequest.of(pageNumber,pageSize,sort);
/*5.调用es接口查询*/
NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
.withQuery(boolQuery)
.withHighlightBuilder(highlightBuilder)
/*
高亮也可以通过这种方式来设置,好处是不同的列可以有不同的配置
.withHighlightFields(
new HighlightBuilder.Field("productName").preTags("<span style=\"color:red\">").postTags("</span>").fragmentSize(800000).numOfFragments(0),
new HighlightBuilder.Field("subtitle").preTags("<span style=\"color:red\">").postTags("</span>").fragmentSize(800000).numOfFragments(0),
new HighlightBuilder.Field("detail").preTags("<span style=\"color:red\">").postTags("</span>").fragmentSize(800000).numOfFragments(0)
)*/
.withPageable(pageable)
.build();
AggregatedPage<ProductEntity> page = elasticsearchTemplate.queryForPage(searchQuery, ProductEntity.class, extResultMapper);
/*6.获取集合数据*/
List<ProductEntity> content = page.getContent();
/*7.将entity转为dto*/
DefaultMapperFactory mapperFactory = new DefaultMapperFactory.Builder().build();
List<ProductSearchOutDto> productSearchOutDtoList = mapperFactory.getMapperFacade().mapAsList(content, ProductSearchOutDto.class);
/*8.返回*/
JSONObject result = new JSONObject();
result.put("productList",productSearchOutDtoList); //商品列表
result.put("totalElements",page.getTotalElements()); //商品总数
result.put("totalPages",page.getTotalPages()); //总页数
return getResultSuccess(result);
}
}
启动类如下：
package com.liazhan;
import com.spring4all.swagger.EnableSwagger2Doc;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* @version V1.0
* @description: 商品服务启动类
* @author: Liazhan
* @date: 2020/5/25 22:33
*/
@SpringBootApplication
@EnableSwagger2Doc
public class AppProduct {
public static void main(String[] args) {
SpringApplication.run(AppProduct.class,args);
}
}
配置文件bootstrap.yml内容如下：
spring:
cloud:
config:
uri: http://localhost:8000 # 配置中心的具体地址，即 config-server
name: product,common # 对应 {application} 部分
profile: dev # 对应 {profile} 部分
label: master # 对应 {label} 部分，即 Git 的分支。如果配置中心使用的是本地存储，则该参数无用
#解决不允许同一个服务中接口中存在多个feign实体bean问题
main:
allow-bean-definition-overriding: true
5、接下来需要在我们的github配置文件仓库中添加商品服务配置文件和修改网关配置文件添加商品服务路由配置
其中prod和test内容都为空，dev内容如下：
server:
port: 8500
spring:
application:
name: liazhan-product
#es配置
data:
elasticsearch:
cluster-name: docker-cluster
cluster-nodes: 47.98.183.103:9300
####swagger相关配置
swagger:
base-package: com.liazhan.product.service
title: 微服务电商项目-商品服务接口
description: 商品服务
version: 1.1
terms-of-service-url: www.baidu.com
contact:
name: liazhan
email: [email protected]
配置文件github地址https://github.com/liazhan/shop-project-config/tree/daf999ce9d80a03dfc3b2c3ea86cbedacd03f22d
修改网关配置文件product-dev.yml，最终内容如下：
#服务端口号
server:
port: 8300
spring:
application:
name: liazhan-member
datasource:
druid:
# 数据库访问配置, 使用druid数据源
db-type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://47.98.183.103:3306/shop-member?serverTimezone=GMT%2b8&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
# 连接池配置
initial-size: 5
min-idle: 5
max-active: 20
# 连接等待超时时间
max-wait: 30000
# 配置检测可以关闭的空闲连接间隔时间
time-between-eviction-runs-millis: 60000
##Jpa配置
jpa:
hibernate:
ddl-auto: update
database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
show-sql: true
#redis配置
redis:
host: 47.98.183.103
password: 123456
port: 6379
pool:
max-idle: 100
min-idle: 1
max-active: 1000
max-wait: -1
####swagger相关配置
swagger:
base-package: com.liazhan.member.service
title: 微服务电商项目-会员服务接口
description: 会员服务
version: 1.1
terms-of-service-url: www.baidu.com
contact:
name: liazhan
email: [email protected]
####会员登陆类型相关配置
login:
type:
max: 3
value: pc,android,ios
配置文件github地址https://github.com/liazhan/shop-project-config/tree/55d992ff690ee78a83cc7c1173929ee1cd781564
6、修改config配置中心服务application.yml配置文件，添加搜索product路径，内容如下：
server:
port: 8000
eureka:
client:
service-url:
defaultZone: http://localhost:8100/eureka/
spring:
application:
name: config-server
cloud:
config:
server:
git:
uri: https://github.com/liazhan/shop-project-config # 配置git仓库的地址
search-paths: common,eureka,gateway,member,weixin,product # git仓库地址下的相对地址，可以配置多个，用,分割。
bus:
enabled: true
trace:
enabled: true
rabbitmq:
host: 47.98.183.103
port: 5672
username: user
password: password
management:
endpoints:
web:
exposure:
include: "*"
ok，如此便完成了，我们分别启动config、eureka、product服务。
访问http://localhost:8500/swagger-ui.html
ok，大工告成。
后台项目github地址https://github.com/liazhan/shop-project/tree/38d409935bb81be0d342e1f97fda74a694f9ca83
五、前端页面
前端不是重点，具体搭建过程不详述。
前端项目github地址https://github.com/liazhan/shop-project-web/tree/8fc4074c84750a7ac462d9da0181e0220de1618e