JC框架——DB模块 java

说明在jc脚手架下引入DB模块，进行多数据源的自动寻址；
下面以dci组件数据库和本地数据库为例，展示如何在jc脚手架下引入DB模块并如何进行配置
引入DB模块的必要性在开发中，经常会需要获取其他组件数据库中的数据，通常有两种办法：
（1）通过Feign调用；直接调用相关组件的接口获取数据
但有时候组件的接口没有或者不能调用，这个时候可以采用第二种方法：
（2）引入DB模块，直接连接组件的数据库，从数据库直接获取数据
1.相关的pom依赖在business文件夹下的pom文件中引入以下pom依赖：

com.alibaba druid-spring-boot-starter 1.1.10 org.mybatis mybatis-spring 2.0.6 com.baomidou mybatis-plus-core 3.2.0 com.baomidou mybatis-plus-extension 3.2.0 com.baomidou mybatis-plus-extension 3.2.0 org.springframework spring-jdbc 5.3.9 com.aries.jc.common aries-jc-bic-resttemplate

2.配置文件 2.1 多数据源的连接配置在dev配置文件中配置需要的多数据源的连接信息

#------------本组件数据库-------------- localDburl=jdbc:postgresql://127.0.0.1:5432/postgres localUserName=postgres localingPwd=123456 #-----------dci组件数据库信息---------- dciDburl=jdbc:postgresql://10.196.1.64:7017/dci_dcidb dciUserName=dci_dcidb_user dciPwd=ULCwLO9p

2.2 其他配置 【JC框架——DB模块】这些配置不知道是否有必要配置，但加上没有影响，去掉后不知道是否会报错

#------是否开启mybatis-plus的分页插件------------------- starfish.data.jdbc.mybatis-plus.pagination.interceptor=true #mybatis-plus配置 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 #profile为dev时是否开启mybatis-plus SQL执行效率插件 starfish.data.jdbc.mybatis-plus.dev.performancce.interceptor=true

3.引入DB模块 3.1 DataSourceSwitchAspect文件本文件中需要更改的是@Pointcut注解后面括号中的目录地址
在本文中只有两个数据源需要切换，如果实际开发中有更多的数据源需要切换，则在该文件中按照db1Aspect()、db2Aspect()方法创建第三个、第四个

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) //这是为了保证AOP在事务注解之前生效,Order的值越小,优先级越高 public class DataSourceSwitchAspect {private final static Logger log = LoggerFactory.getLogger(DataSourceSwitchAspect.class); //需要从本地数据库获取的数据，需要在mapper文件夹下创建local文件夹，并将mapper文件定义在local文件夹下，括号中的地址根据实际项目目录进行更改 @Pointcut("execution(* com.aries.jc.dciTest.modules.mapper.local..*.*(..))") private void db1Aspect() { }//需要从dci数据库获取的数据，需要在mapper文件夹下创建dci文件夹，并将mapper文件定义在dci文件夹下，括号中的地址根据实际项目目录进行更改 @Pointcut("execution(* com.aries.jc.dciTest.modules.mapper.dci..*.*(..))") private void db2Aspect() { }@Before("db1Aspect()") public void db1() { log.debug("切换到 local 数据源..."); DbContextHolder.setDbType(DBTypeEnum.db1); }@Before("db2Aspect()") public void db2() { log.debug("切换到 dci 数据源..."); DbContextHolder.setDbType(DBTypeEnum.db2); } }

3.2 DbConfig文件修改1：@MapperScan注解后面括号中的目录地址
修改2：@Value注解后面括号中的内容，根据配置文件中的配置做出具体修改
本文中只有两个数据源需要切换，如果实际开发中需要更多数据源，则可在本文件中参考@Bean(name = "db1")、 @Bean(name = "db2")，创建其他数据源的Bean

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); //组件表示信息：也是在配置文件中进行配置 @Value("${base.application.componentId}") private String componentId; //配置文件中数据库的信息 @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("初始化新组件数据库>>>>>>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; }/** * 动态数据源配置 * * @return */@Bean @Primary public DataSource multipleDataSource(@Qualifier("db1") DataSource db1, @Qualifier("db2") DataSource db2 ) { DynamicDataSource dynamicDataSource = new DynamicDataSource(); Map targetDataSources = new HashMap<>(); targetDataSources.put(DBTypeEnum.db1.getValue(), db1); targetDataSources.put(DBTypeEnum.db2.getValue(), db2); dynamicDataSource.setTargetDataSources(targetDataSources); dynamicDataSource.setDefaultTargetDataSource(db1); // 程序默认数据源，这个要根据程序调用数据源频次，经常把常调用的数据源作为默认 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() //添加分页功能 sqlSessionFactory.setPlugins(new Interceptor[]{ paginationInterceptor() }); //sqlSessionFactory.setGlobalConfig(globalConfiguration()); //注释掉全局配置，因为在xml中读取就是全局配置 return sqlSessionFactory.getObject(); } }

3.3 DbContextHolder文件此文件不需要修改

package com.aries.jc.dciTest.modules.db; public class DbContextHolder {private static final ThreadLocal contextHolder = new ThreadLocal<>(); /** * 设置数据源 * * @param dbTypeEnum */public static void setDbType(DBTypeEnum dbTypeEnum) { contextHolder.set(dbTypeEnum.getValue()); }/** * 取得当前数据源 * * @return */public static String getDbType() { return (String) contextHolder.get(); }/** * 清除上下文数据 */public static void clearDbType() { contextHolder.remove(); } }

3.4 DBTypeEnum文件本文只有两个数据源进行切换，如果涉及到3个、4个或者更多，直接在文件中添加
db1("db1"),
db2("db2"),
db3("db3"),
db4("db4");
...
即可

package com.aries.jc.dciTest.modules.db; public enum DBTypeEnum {db1("db1"), db2("db2"); private String value; DBTypeEnum(String value) { this.value = https://www.it610.com/article/value; }public String getValue() { return value; } }

3.5 DynamicDataSource文件此文件不需要修改

package com.aries.jc.dciTest.modules.db; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class DynamicDataSource extends AbstractRoutingDataSource {/** * 取得当前使用哪个数据源 * * @return */ @Override protected Object determineCurrentLookupKey() { return DbContextHolder.getDbType(); } }

4.测试 4.1 创建实体类在business文件夹下创建entity文件夹，在entity文件夹下创建dci、local文件夹
在dci文件夹下创建实体类：

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; }

在local文件夹下创建实体类：

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("点位类型名称") private String pointName; @TableField("point_type") @ApiModelProperty("点位类型0-一般类型 1-蓝天卫士；2-重点监控") private Integer pointType; @TableField("point_icon_url") @ApiModelProperty("点位图标url") private String pointIconUrl; @TableField("point_icon_name") @ApiModelProperty("点位图标名称") private String pointIconName; @TableField("aggre_icon_url") @ApiModelProperty("聚合图标url") private String aggreIconUrl; @TableField("aggre_icon_name") @ApiModelProperty("聚合图标name") private String aggreIconName; @TableField("create_time") @ApiModelProperty("创建时间") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") private Timestamp createTime; @TableField("create_user") @ApiModelProperty("创建人名称") private String createUser; @TableField("update_time") @ApiModelProperty("更新时间") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") private Timestamp updateTime; @TableField("update_user") @ApiModelProperty("更新人名称") private String updateUser; @TableField("delete") @ApiModelProperty("是否删除1-未删除-1-以删除") private Integer delete; }

文章图片

4.2 创建Mapper 在mapper文件夹下创建dci、local文件夹，
在dci文件夹下创建mapper：

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 {@Select("select * from org_info_count") List getAllOrgInfo(); }

在local文件夹下创建mapper：

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 {@Select("select * from tb_point_config") List getAllPoint(); }

文章图片

4.3 创建service接口在service层中不需要区分dci数据源和local数据源，可直接将两者的接口定义在一个service接口文件中

文章图片

4.4 创建service接口实现类在service层接口实现类中不需要区分dci数据源和local数据源，可直接将两者的接口定义在一个service接口实现类文件中

文章图片

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; /** * 获取dci数据库中org_info_count表所有数据，测试多数据源切换 * @return */ @Override public BaseResult> getAllInfo() { BaseResult> baseResult = new BaseResult<>(); List list = new ArrayList<>(); try { list = orgInfoCountMapper.getAllOrgInfo(); baseResult.setData(list); baseResult.setCode("0"); baseResult.setMsg("获取成功"); return baseResult; }catch (Exception e){ LOGGER.error("获取失败：{}", e); } baseResult.setCode("-1"); baseResult.setMsg("获取失败"); return baseResult; }/** * 获取local数据库中tb_point_config表所有数据，测试多数据源切换 * @return */ @Override public BaseResult getAllPoint() { BaseResult baseResult = new BaseResult<>(); List list = new ArrayList<>(); try { list = pointConfigMapper.getAllPoint(); baseResult.setData(list); baseResult.setCode("0"); baseResult.setMsg("获取成功"); return baseResult; }catch (Exception e){ LOGGER.error("获取失败：{}", e); } baseResult.setCode("-1"); baseResult.setMsg("获取失败"); return baseResult; }}

4.5 创建controller层 controller层同样不需要区分不同的数据源，可以定义在同一个controller文件中