explain

Introduce DB module under jc scaffold for automatic addressing of multiple data sources;
Taking dci component database and local database as examples, the following shows how to introduce DB module and configure it under jc scaffold

Necessity of introducing DB module

During development, it is often necessary to obtain data from other component databases. There are usually two methods:
(1) Call through Feign; Directly call the interface of related components to obtain data
However, sometimes the component interface does not have or cannot be called. At this time, the second method can be used:
(2) The DB module is introduced to directly connect to the component database and obtain data directly from the database

1. Related pom dependencies

Introduce the following pom dependencies into the pom file under the business folder:

<!--Multi data source addressing pom rely on-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.6</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-core</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.9</version>
        </dependency>
        <dependency>
            <groupId>com.aries.jc.common</groupId>
            <artifactId>aries-jc-bic-resttemplate</artifactId>
        </dependency>

2. Configuration file

2.1 connection configuration of multiple data sources

Configure the required connection information of multiple data sources in the dev configuration file

#------------This component database--------------
localDburl=jdbc:postgresql://127.0.0.1:5432/postgres
localUserName=postgres
localingPwd=123456
#-----------dci component database information----------
dciDburl=jdbc:postgresql://10.196.1.64:7017/dci_dcidb
dciUserName=dci_dcidb_user
dciPwd=ULCwLO9p

2.2 other configurations

These configurations do not know whether they are necessary, but in addition, they have no impact. After they are removed, they do not know whether an error will be reported

#------Open the paging plug-in of mybatis plus-------------------
starfish.data.jdbc.mybatis-plus.pagination.interceptor=true
#Mybatis plus configuration
mybatis-plus.mapper-locations=classpath*:mapper/*Mapper.xml
mybatis-plus.type-aliases-package=com.aries.jc.dciTest.modules.entity
mybatis-plus.configuration.map-underscore-to-camel-case=true
#Whether to enable mybatis plus SQL execution efficiency plug-in when the profile is dev
starfish.data.jdbc.mybatis-plus.dev.performancce.interceptor=true

3. Introduce DB module

3.1 DataSourceSwitchAspect file

What needs to be changed in this document is the directory address in parentheses after the @ Pointcut annotation
In this article, only two data sources need to be switched. If more data sources need to be switched in the actual development, create the third and fourth data sources in this file according to the db1Aspect() and db2Aspect() methods

package com.aries.jc.dciTest.modules.db;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Component
@Aspect
@Order(-1) //This is to ensure that AOP takes effect before the transaction annotation. The smaller the value of Order, the higher the priority
public class DataSourceSwitchAspect {

    private final static Logger log = LoggerFactory.getLogger(DataSourceSwitchAspect.class);

    //For the data to be obtained from the local database, create a local folder under the mapper folder, define the mapper file under the local folder, and change the address in parentheses according to the actual project directory
    @Pointcut("execution(* com.aries.jc.dciTest.modules.mapper.local..*.*(..))")
    private void db1Aspect() {
    }

    //For the data to be obtained from the dci database, create a dci folder under the mapper folder and define the mapper file under the dci folder. The address in parentheses is changed according to the actual project directory
    @Pointcut("execution(* com.aries.jc.dciTest.modules.mapper.dci..*.*(..))")
    private void db2Aspect() {
    }


    @Before("db1Aspect()")
    public void db1() {
        log.debug("Switch to local data source...");
        DbContextHolder.setDbType(DBTypeEnum.db1);
    }

    @Before("db2Aspect()")
    public void db2() {
        log.debug("Switch to dci data source...");
        DbContextHolder.setDbType(DBTypeEnum.db2);
    }
}

3.2 DbConfig file

Modify 1: the directory address in parentheses after the @ MapperScan annotation
Modification 2: the contents in parentheses after @ Value annotation are modified according to the configuration in the configuration file
In this article, only two data sources need to be switched. If more data sources are needed in actual development, you can refer to @ Bean(name = "db1") and @ Bean(name = "db2") in this document to create beans of other data sources

package com.aries.jc.dciTest.modules.db;


import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@MapperScan("com.aries.jc.dciTest.**.mapper*")
public class DbConfig {

    private final static Logger log = LoggerFactory.getLogger(DbConfig.class);

    //Component representation information: it is also configured in the configuration file
    @Value("${base.application.componentId}")
    private String componentId;

    //Information about the database in the configuration file
    @Value("${localDburl}")
    private String localDburl;
    @Value("${localUserName}")
    private String localUserName;
    @Value("${localingPwd}")
    private String localingPwd;
    @Value("${dciDburl}")
    private String dciDburl;
    @Value("${dciUserName}")
    private String dciUserName;
    @Value("${dciPwd}")
    private String dciPwd;

    private static String driverClass = "org.postgresql.Driver";

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }


    @Bean(name = "db1")
    public DataSource db1() {

        // SegmentInfo segmentInfo = myHikDiscoveryClient.findAmqServer(componentId,dbSegmentId);
    /*    log.info("Initialize the new component database > > > > > > > componentid = "+ componentid +", dbsegmentid = "+ dbsegmentid +", segmentinfo = "+ jsonobject. Tojsonstring (segmentinfo));
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl("jdbc:postgresql://"+segmentInfo.getIp()+":"+segmentInfo.getPort()+"/"+segmentInfo.getDbName());
        dataSource.setUsername(segmentInfo.getDbusername());
        dataSource.setPassword(segmentInfo.getDbpassword());*/

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(localDburl);
        dataSource.setUsername(localUserName);
        dataSource.setPassword(localingPwd);

        return dataSource;


    }

    @Bean(name = "db2")
    public DataSource db2() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(dciDburl);
        dataSource.setUsername(dciUserName);
        dataSource.setPassword(dciPwd);
        return dataSource;
    }

    /**
     * Dynamic data source configuration
     *
     * @return
     */

    @Bean
    @Primary
    public DataSource multipleDataSource(@Qualifier("db1") DataSource db1,
                                         @Qualifier("db2") DataSource db2
    ) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.db1.getValue(), db1);
        targetDataSources.put(DBTypeEnum.db2.getValue(), db2);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(db1); // The default data source of the program, which often takes the frequently called data source as the default according to the frequency of the program calling the data source
        return dynamicDataSource;
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(multipleDataSource(db1(), db2()));

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper.xml"));
        //PerformanceInterceptor(),OptimisticLockerInterceptor()
        //Add paging function
        sqlSessionFactory.setPlugins(new Interceptor[]{
                paginationInterceptor()
        });
//        sqlSessionFactory.setGlobalConfig(globalConfiguration()); // Comment out the global configuration because reading in xml is the global configuration
        return sqlSessionFactory.getObject();
    }
}

3.3 DbContextHolder file

This file does not need to be modified

package com.aries.jc.dciTest.modules.db;

public class DbContextHolder {

    private static final ThreadLocal contextHolder = new ThreadLocal<>();

    /**
     * set up data sources
     *
     * @param dbTypeEnum
     */

    public static void setDbType(DBTypeEnum dbTypeEnum) {
        contextHolder.set(dbTypeEnum.getValue());
    }


    /**
     * Get current data source
     *
     * @return
     */

    public static String getDbType() {
        return (String) contextHolder.get();
    }


    /**
     * Clear context data
     */

    public static void clearDbType() {
        contextHolder.remove();
    }
}

3.4 DBTypeEnum file

In this article, there are only two data sources for switching. If three, four or more data sources are involved, they can be added directly to the file
db1("db1"),
db2("db2"),
db3("db3"),
db4("db4");
...
that will do

package com.aries.jc.dciTest.modules.db;

public enum DBTypeEnum {

    db1("db1"),
    db2("db2");
    private String value;

    DBTypeEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}

3.5 DynamicDataSource file

This file does not need to be modified

package com.aries.jc.dciTest.modules.db;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

    /**
     * Gets which data source is currently used
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DbContextHolder.getDbType();
    }
}

4. Test

4.1 create entity class

Create an entity folder under the business folder, and create dci and local folders under the entity folder
Create an entity class in the dci folder:

package com.aries.jc.dciTest.modules.entity.dci;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.models.auth.In;
import lombok.Data;

import java.sql.Timestamp;

@Data
@TableName("org_info_count")
public class OrgInfoCount {

    @TableField("org_index_code")
    private String orgIndexCode;

    @TableField("org_index_code_in")
    private String orgIndexCodeIn;

    @TableField("org_index_name")
    private String orgIndexName;

    @TableField("org_index_type")
    private String orgIndexType;

    @TableField("insert_count")
    private Integer insertCount;

    @TableField("not_insert_count")
    private Integer notInsertCount;

    @TableField("total")
    private Integer total;

    @TableField("update_time")
    private Timestamp updateTime;
}

Create an entity class in the local folder:

package com.aries.jc.dciTest.modules.entity.local;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.sql.Timestamp;

@Data
@TableName("tb_point_config")
public class pointConfig {

    @TableField("point_name")
    @ApiModelProperty("Point type name")
    private String pointName;

    @TableField("point_type")
    @ApiModelProperty("Point type 0-General type 1-Blue sky guard; two-Key monitoring")
    private Integer pointType;

    @TableField("point_icon_url")
    @ApiModelProperty("Point icon url")
    private String pointIconUrl;

    @TableField("point_icon_name")
    @ApiModelProperty("Point icon name")
    private String pointIconName;

    @TableField("aggre_icon_url")
    @ApiModelProperty("Aggregate Icon url")
    private String aggreIconUrl;

    @TableField("aggre_icon_name")
    @ApiModelProperty("Aggregate Icon name")
    private String aggreIconName;

    @TableField("create_time")
    @ApiModelProperty("Creation time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Timestamp createTime;

    @TableField("create_user")
    @ApiModelProperty("Creator name")
    private String createUser;

    @TableField("update_time")
    @ApiModelProperty("Update time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Timestamp updateTime;

    @TableField("update_user")
    @ApiModelProperty("Updated by name")
    private String updateUser;

    @TableField("delete")
    @ApiModelProperty("Delete 1-Not deleted  -1-To delete")
    private Integer delete;
}

4.2 creating Mapper

Create dci and local folders under the mapper folder,
Create mapper under dci folder:

package com.aries.jc.dciTest.modules.mapper.dci;

import com.aries.jc.dciTest.modules.entity.dci.OrgInfoCount;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hikvision.ga.common.BaseResult;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface OrgInfoCountMapper extends BaseMapper<OrgInfoCount> {

    @Select("select * from org_info_count")
    List<OrgInfoCount> getAllOrgInfo();
}

Create mapper under local folder:

package com.aries.jc.dciTest.modules.mapper.local;

import com.aries.jc.dciTest.modules.entity.local.PointConfig;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface PointConfigMapper extends BaseMapper<PointConfig> {

    @Select("select * from tb_point_config")
    List<PointConfig> getAllPoint();
}

4.3 creating a service interface

In the service layer, there is no need to distinguish between dci data source and local data source. The interfaces of the two can be directly defined in a service interface file

4.4 create a service interface implementation class

In the service layer interface implementation class, there is no need to distinguish between dci data source and local data source. Their interfaces can be directly defined in a service interface implementation class file

package com.aries.jc.dciTest.modules.service.impl;

import com.aries.jc.dciTest.modules.entity.dci.OrgInfoCount;
import com.aries.jc.dciTest.modules.entity.local.PointConfig;
import com.aries.jc.dciTest.modules.mapper.dci.OrgInfoCountMapper;
import com.aries.jc.dciTest.modules.mapper.local.PointConfigMapper;
import com.aries.jc.dciTest.modules.rs.DciClientV1;
import com.aries.jc.dciTest.modules.rs.DciClientV2;
import com.aries.jc.dciTest.modules.rs.DciClientV3;
import com.aries.jc.dciTest.modules.service.DciService;
import com.hikvision.ga.common.BaseResult;
import com.hikvision.ga.dci.api.v1.dto.BasePageDto;
import com.hikvision.ga.dci.api.v1.dto.UnitListDto;
import com.hikvision.ga.dci.api.v2.param.ParamUnitList;
import com.hikvision.ga.dci.api.v3.dto.CountReq;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

@Service
public class DciServiceImpl implements DciService {
    private static final Logger LOGGER = LoggerFactory.getLogger(DciServiceImpl.class);

    @Autowired
    private OrgInfoCountMapper orgInfoCountMapper;
    @Autowired
    private PointConfigMapper pointConfigMapper;

    /**
     * Get org in dci database_ info_ Count all the data in the table, and test the switching of multiple data sources
     * @return
     */
    @Override
    public BaseResult<List<OrgInfoCount>> getAllInfo() {
        BaseResult<List<OrgInfoCount>> baseResult = new BaseResult<>();
        List<OrgInfoCount> list = new ArrayList<>();

        try {
            list = orgInfoCountMapper.getAllOrgInfo();
            baseResult.setData(list);
            baseResult.setCode("0");
            baseResult.setMsg("Get success");
            return baseResult;
        }catch (Exception e){
            LOGGER.error("Get failed:{}", e);
        }
        baseResult.setCode("-1");
        baseResult.setMsg("Acquisition failed");
        return baseResult;
    }

    /**
     * Get TB in local database_ point_ Config table all data, test multi data source switching
     * @return
     */
    @Override
    public BaseResult<List<PointConfig>> getAllPoint() {
        BaseResult<List<PointConfig>> baseResult = new BaseResult<>();
        List<PointConfig> list = new ArrayList<>();

        try {
            list = pointConfigMapper.getAllPoint();
            baseResult.setData(list);
            baseResult.setCode("0");
            baseResult.setMsg("Get success");
            return baseResult;
        }catch (Exception e){
            LOGGER.error("Get failed:{}", e);
        }
        baseResult.setCode("-1");
        baseResult.setMsg("Acquisition failed");
        return baseResult;
    }


}

4.5 create controller layer

The controller layer also does not need to distinguish different data sources, and can be defined in the same controller file

4.6 swagger test

(1) Get data from local data source


(2) Obtaining data from dci data source