AOP 更新 ElasticSearch 索引---数据准备

  |   0 评论   |   189 浏览

商品数据

  • 登录阿里妈妈,淘宝联盟,联盟产品,单品店铺推广,下载精选清单

image.png

  • 放入项目位置 src\test\resources\精选优质商品清单(内含优惠券)-2020-04-22.xls
  • 建立 对应数据表,读取 Excel 存入数据库

image.png

  • 表结构 DDL
/*
 Navicat Premium Data Transfer

 Source Server         : 1.6 TiDB
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : 192.168.1.6:4000
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50725
 File Encoding         : 65001

 Date: 22/04/2020 16:15:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for item
-- ----------------------------
DROP TABLE IF EXISTS `item`;
CREATE TABLE `item`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品id',
  `item_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `master_pic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品主图',
  `detail_link` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品详情页链接地址',
  `category` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品一级类目',
  `tbk_link` varchar(800) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '淘宝客链接',
  `item_price` decimal(11, 2) NULL DEFAULT NULL COMMENT '商品价格',
  `monthly_sales` bigint(11) NULL DEFAULT NULL COMMENT '商品月销量',
  `income_ratio` decimal(11, 2) NULL DEFAULT NULL COMMENT '收入比率',
  `commission` decimal(11, 2) NULL DEFAULT NULL COMMENT '佣金',
  `seller_wangwang` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '卖家旺旺',
  `seller_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '卖家id',
  `shop_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店铺名称',
  `platform_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '平台类型',
  `coupon_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券id',
  `total_coupons` bigint(11) NULL DEFAULT NULL COMMENT '优惠券总量',
  `coupon_surplus` bigint(11) NULL DEFAULT NULL COMMENT '优惠券剩余量',
  `coupon_face_value` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券面额',
  `coupon_start_time` date NULL DEFAULT NULL COMMENT '优惠券开始时间',
  `coupon_end_time` date NULL DEFAULT NULL COMMENT '优惠券结束时间',
  `coupon_link` varchar(800) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券链接',
  `coupon_promotion_link` varchar(800) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券推广链接',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品表' ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

  • 对应实体
package com.lab.es.demo.entity;

import com.alibaba.fastjson.annotation.JSONField;
import lombok.Data;
import lombok.experimental.Accessors;
import org.springframework.format.annotation.DateTimeFormat;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class Item {
    @JSONField(ordinal = 1, serialize = true) private String id;
    @JSONField(ordinal = 2) private String itemName;
    @JSONField(ordinal = 3) private String masterPic;
    @JSONField(ordinal = 4) private String detailLink;
    @JSONField(ordinal = 5) private String category;
    @JSONField(ordinal = 6) private String tbkLink;
    @JSONField(ordinal = 7) private BigDecimal itemPrice;
    @JSONField(ordinal = 8) private Long monthlySales;
    @JSONField(ordinal = 9) private BigDecimal incomeRatio;
    @JSONField(ordinal = 10) private BigDecimal commission;
    @JSONField(ordinal = 11) private String sellerWangwang;
    @JSONField(ordinal = 12) private String sellerId;
    @JSONField(ordinal = 13) private String shopName;
    @JSONField(ordinal = 14) private String platformType;
    @JSONField(ordinal = 15) private String couponId;
    @JSONField(ordinal = 16) private Long totalCoupons;
    @JSONField(ordinal = 17) private Long couponSurplus;
    @JSONField(ordinal = 18) private String couponFaceValue;

    @JSONField(ordinal = 19,format="yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date couponStartTime;

    @JSONField(ordinal = 20,format="yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date couponEndTime;

    @JSONField(ordinal = 21) private String couponLink;
    @JSONField(ordinal = 22) private String couponPromotionLink;
}
  • 用 MyBatisPlus 批量插入数据库,阿里巴巴 easyexcel 读取 Excel
  • pom.xml
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE</version>
        <relativePath/>
    </parent>
    <groupId>com.lab</groupId>
    <artifactId>book-es</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>book-es</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <elasticsearch.version>7.6.2</elasticsearch.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.22</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>LATEST</version>
        </dependency>
        <dependency>
            <groupId>org.apache.rocketmq</groupId>
            <artifactId>rocketmq-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.62</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-elasticsearch</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.2</version>
                <configuration>
                    <forkMode>once</forkMode>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>
  • application.properties
server.port=80

spring.thymeleaf.cache=false

rocketmq.name-server=192.168.1.6:9876
rocketmq.producer.group=delayProducer
rocketmq.producer.sendMessageTimeout=300000
rocketmq.topic.testDelayTopic=testDelayTopic

mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml

mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.global-config.db-config.id-type=assign_id
mybatis-plus.global-config.db-config.table-underline=true
#mybatis-plus.global-config.db-config.table-prefix=
mybatis-plus.global-config.db-config.capital-mode-=true

#配置数据库连接信息
spring.datasource.druid.url=jdbc:mysql://192.168.1.6:4000/test?autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
spring.datasource.druid.username=root
spring.datasource.druid.password=Aa123456
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver

#配置Druid的连接池
spring.datasource.druid.initialSize=10
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.minIdle=1
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=true
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxOpenPreparedStatements=20
spring.datasource.druid.validationQuery=SELECT 1
spring.datasource.druid.validation-query-timeout=500
spring.datasource.druid.filters=stat

#Druid的监控配置
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin

相关 MyBatisPlus 类

  • MyBaseMapper
package com.lab.es.demo.config;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.query.QueryChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.LambdaUpdateChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.UpdateChainWrapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface MyBaseMapper<T> extends BaseMapper<T> {
    default QueryChainWrapper<T> queryChain() {
        return new QueryChainWrapper<>(this);
    }

    default LambdaQueryChainWrapper<T> lambdaQueryChain() {
        return new LambdaQueryChainWrapper<>(this);
    }

    default UpdateChainWrapper<T> updateChain() {
        return new UpdateChainWrapper<>(this);
    }

    default LambdaUpdateChainWrapper<T> lambdaUpdateChain() {
        return new LambdaUpdateChainWrapper<>(this);
    }

    int insertBatchSomeColumn(List<T> entityList);

    int alwaysUpdateSomeColumnById(@Param(Constants.ENTITY) T entity);

    int deleteByIdWithFill(T entity);
}
  • MySqlInjector
package com.lab.es.demo.config;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.AlwaysUpdateSomeColumnById;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import com.baomidou.mybatisplus.extension.injector.methods.LogicDeleteByIdWithFill;

import java.util.List;

public class MySqlInjector extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);

        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        methodList.add(new AlwaysUpdateSomeColumnById());
        methodList.add(new LogicDeleteByIdWithFill());
        return methodList;
    }
}
  • MybatisPlusConfig
package com.lab.es.demo.config;

import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.parsers.BlockAttackSqlParser;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.SqlExplainInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

@Configuration
@MapperScan("com.lab.es.demo.mapper")
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
    }

    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }

    @Bean
    public SqlExplainInterceptor sqlExplainInterceptor() {
        SqlExplainInterceptor sqlExplainInterceptor = new SqlExplainInterceptor();
        List<ISqlParser> sqlParserList = new ArrayList<>();
        sqlParserList.add(new BlockAttackSqlParser());
        sqlExplainInterceptor.setSqlParserList(sqlParserList);
        return sqlExplainInterceptor;
    }

    @Bean
    public MySqlInjector sqlInjector() {
        return new MySqlInjector();
    }
}
  • ItemMapper
package com.lab.es.demo.mapper;

import com.lab.es.demo.config.MyBaseMapper;
import com.lab.es.demo.entity.Item;

public interface ItemMapper extends MyBaseMapper<Item> {
}

读取 Excel 相关类

  • ItemListener
package com.lab.es.demo.xls;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.lab.es.demo.entity.Item;
import com.lab.es.demo.mapper.ItemMapper;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

@Slf4j
@NoArgsConstructor
public class ItemListener extends AnalysisEventListener<Item> {
    private static final int BATCH_COUNT = 3000;
    List<Item> list = new ArrayList<Item>();

    private ItemMapper itemMapper;

    public ItemListener(ItemMapper itemMapper) {
        this.itemMapper = itemMapper;
    }

    @Override
    public void invoke(Item data, AnalysisContext context) {
        //log.info("解析到一条数据:{}", JSON.toJSONString(data, SerializerFeature.PrettyFormat));
        list.add(data);

        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成!");
    }

    private void saveData() {
        log.info("{}条数据,开始存储数据库!", list.size());
        itemMapper.insertBatchSomeColumn(list);
        log.info("存储数据库成功!");
    }
}
  • ReadXlsTest
package com.lab.es.demo.xls;

import com.alibaba.excel.EasyExcel;
import com.lab.es.demo.BookESApplication;
import com.lab.es.demo.entity.Item;
import com.lab.es.demo.mapper.ItemMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.ResourceUtils;

import java.io.File;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = BookESApplication.class)
public class ReadXlsTest {
    @Autowired
    private ItemMapper itemMapper;

    @Test
    public void simpleRead() throws Exception {
        File file = ResourceUtils.getFile("classpath:精选优质商品清单(内含优惠券)-2020-04-22.xls");
        EasyExcel.read(file.getAbsolutePath(), Item.class, new ItemListener(itemMapper)).sheet().doRead();
    }
}

导入数据到 TiDB

  • 删除 Excel 重复数据,并保存

image.png

image.png

  • 执行测试用例,导入 TiDB test库 item表
mvn test -Dtest=com.lab.es.demo.xls.ReadXlsTest#simpleRead

image.png

image.png


标题:AOP 更新 ElasticSearch 索引---数据准备
作者:shuikan95
地址:http://javadaily.cn/articles/2020/04/22/1587546665964.html

评论

发表评论