springboot集成beetlsql支持多数据源源码

之前写过beetlsql的使用，有人留言要源码，本篇把springboot集成beetlsql的多数据源码列出。前文如下:

springboot集成beetlsql以及支持多数据源_wuyang19920226的博客-CSDN博客_beetlsql 多数据源本篇或许是为数不多的展示beetlsql多数据源设计思路，甚至可能是目前网上能找到的唯一一篇介绍beetlsql多数据源的文章。为什么推荐用beetlsql：目前国内最火的支持数据库持久化的DAO工具应该还是mybatis，因为mybatis出现的时间早，并且提供了比较强大的数据库持久化处理能力，而我本人也是一直在用mybatis。但是mybatis仍有许多不足之处，想必每一位开发者在开发过程...https://blog.csdn.net/wuyang19920226/article/details/100084459?spm=1001.2014.3001.5501 

 由于前文简术过beetlsql。本文不在累述。结合本人实际使用过MyBatis、Spring-JPA，beetlsql等JDBC架构，个人觉得还是beetlsql最好用，最强大灵活。下面进入正题

本文使用的springboot版本为2.2.8.RELEASE，beetlsql版本如下：

com.ibeetlbeetlsql2.10.21

com.ibeetlbeetl2.8.1

com.alibabadruid1.1.22


spring:datasource:default:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://xxxx?useunicode=true&useSSL=false&characterEncoding=utf8&serverTimezOne=Asia/Shanghai ##填写自己的第一个数据源username: xxx ##用户名password: xxx ##密码sql-root: /sql ##自定义sql语句的模板目录dao-suffix: Repositoryproduct-mode: falseoffset-start-zero: truedebug-interceptor: truebase-package: com.xxx.xx.repository ##填写自己的数据Repository包路径second:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://xxx?useunicode=true&useSSL=false&characterEncoding=utf8&serverTimezOne=Asia/Shanghai ##填写自己的第二个数据源username: xxxpassword: xxxbase-package: com.xxx.xxx.second.repositorysql-root: /sql2 dao-suffix: Repositoryproduct-mode: falseoffset-start-zero: truedebug-interceptor: truedruid:enable: trueinitial-size: 5min-idle: 8max-active: 15test-while-idle: truetest-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-open-prepared-statements: 20use-global-data-source-stat: truefilters: stat,slf4j,config

 sql-root自定义sql语句的模板目录是这样的

*** 多数据源注解*@date 2018/12/2 21:32*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MultipleDataSource {/*** 多数据源ID** @return*/String value();}

package com.yougu.core.beetlsql.datasource;import com.alibaba.druid.pool.DruidDataSource;
import com.yougu.core.beetlsql.properties.BeetlsqlConstants;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.core.env.Environment;import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;/*** @description DataSourceBuilderFactory* @date 2018/12/2 21:32*/
@Slf4j
public class DataSourceBuilderFactory {/*** 初始化主数据源** @param env*/public static DataSource buildDefaultDataSource(Environment env) {// 读取主数据源Map dsMap = new HashMap<>();String prefix = BeetlsqlConstants.DEFAULT_ID + BeetlsqlConstants.SEQ;dsMap.put(BeetlsqlConstants.URL, env.getProperty(prefix + BeetlsqlConstants.URL));dsMap.put(BeetlsqlConstants.USERNAME, env.getProperty(prefix + BeetlsqlConstants.USERNAME));dsMap.put(BeetlsqlConstants.PASSWORD, env.getProperty(prefix + BeetlsqlConstants.PASSWORD));dsMap.put(BeetlsqlConstants.DRIVER_CLASS_NAME, env.getProperty(prefix + BeetlsqlConstants.DRIVER_CLASS_NAME));log.info("The Default DataSource Properties is: {}", dsMap.toString());DataSource dataSource = buildDataSource(dsMap);dataBinder(BeetlsqlConstants.DEFAULT_ID, dataSource, env);return dataSource;}/*** 初始化自定义数据源** @param env*/public static DataSource buildCustomDataSources(String id, Environment env) {// 读取配置文件获取更多数据源，也可以通过DefaultDataSource读取数据库获取更多数据源String prefix = id + BeetlsqlConstants.SEQ;Map dsMap = new HashMap<>();dsMap.put(BeetlsqlConstants.URL, env.getProperty(prefix + BeetlsqlConstants.URL));dsMap.put(BeetlsqlConstants.USERNAME, env.getProperty(prefix + BeetlsqlConstants.USERNAME));dsMap.put(BeetlsqlConstants.PASSWORD, env.getProperty(prefix + BeetlsqlConstants.PASSWORD));dsMap.put(BeetlsqlConstants.DRIVER_CLASS_NAME, env.getProperty(prefix + BeetlsqlConstants.DRIVER_CLASS_NAME));log.info("The Custom DataSource({}) Properties is: {}", id, dsMap.toString());DataSource dataSource = buildDataSource(dsMap);dataBinder(id, dataSource, env);return dataSource;}/*** 创建DataSource** @param dsMap* @return*/private static DataSource buildDataSource(Map dsMap) {return DataSourceBuilder.create().url(dsMap.get(BeetlsqlConstants.URL).toString()).username(dsMap.get(BeetlsqlConstants.USERNAME).toString()).password(dsMap.get(BeetlsqlConstants.PASSWORD).toString()).type(getDataSourceClass(dsMap.get(BeetlsqlConstants.POOL_TYPE))).driverClassName(dsMap.get(BeetlsqlConstants.DRIVER_CLASS_NAME).toString()).build();}/*** The get DataSource class** @param className* @return*/private static Class getDataSourceClass(Object className) {if (className == null || String.valueOf(className).length() == 0) {return DruidDataSource.class;}try {return (Class) Class.forName(String.valueOf(className));} catch (Exception e) {log.error(e.getMessage(), e);return DruidDataSource.class;}}
}

package com.yougu.core.beetlsql.properties;/*** @description BeetlsqlConstants* 提供beetlsql基本参数定义* @date 2018/12/2 21:32*/
public class BeetlsqlConstants {public static final String UUID = "uuid";public static final String DEFAULT_ID = "spring.datasource.default";public static final String SEQ = ".";public static final String URL = "url";public static final String USERNAME = "username";public static final String PASSWORD = "password";public static final String POOL_TYPE = "pool-type";public static final String DRIVER_CLASS_NAME = "driver-class-name";public static final String ORACLE_DRIVER_CLASS_NAME = "oracle.jdbc.driver.OracleDriver";public static final String MYSQL_DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";//默认数据源配置public static final String DEFAULT_DATA_SOURCE = "dataSource";public static final String DEFAULT_SQL_MANAGER_FACTORY_BEAN = "sqlManagerFactoryBean";public static final String DEFAULT_BEETL_SQL_SCANNER_COnFIGURER= "beetlSqlScannerConfigurer";//第二数据源配置public static final String SECONDARY_ID = "spring.datasource.second";public static final String SECONDARY_DATA_SOURCE = "secondaryDataSource";public static final String SECONDARY_SQL_MANAGER_FACTORY_BEAN = "secondarySqlManagerFactoryBean";public static final String SECONDARY_BEETL_SQL_SCANNER_COnFIGURER= "secondaryBeetlSqlScannerConfigurer";}

package com.yougu.core.beetlsql.properties;import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.core.env.Environment;/*** @description BeetlsqlProperties* @date 2018/12/2 21:32*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class BeetlsqlProperties {/*** 是否加载默认数据源，默认为 True*/private boolean defaultEnabled;/*** SQL存放目录*/private String sqlRoot;/*** DAO接口扫描包路径*/private String basePackage;/*** DAO接口的后缀*/private String daoSuffix;/*** 是否打印SQL执行语句*/private Boolean debugInterceptor;/*** 生产模式开关，默认false表示开发模(修改md的sql文件，不需要重启，但性能较差)，true表示生产模式*

* 建议：线上不要使用开发模式，因为此模式会每次sql调用都会检测md文件是否变化*/private Boolean productMode;/*** 翻页起始参数是0还是1*

* 默认认为1对应于翻页的第1条记录，如果你习惯mysql那种0对应于第一条记录，则需要配置OFFSET_START_ZERO，设置为true*/private Boolean offsetStartZero;/*** SqlManagerFactoryBean创建时使用** @param sqlRoot* @param debugInterceptor*/public BeetlsqlProperties(String sqlRoot, Boolean debugInterceptor) {this.sqlRoot = sqlRoot;this.debugInterceptor = debugInterceptor;}/*** BeetlSqlScannerConfigurer创建时使用** @param daoSuffix* @param basePackage*/public BeetlsqlProperties(String daoSuffix, String basePackage) {this.daoSuffix = daoSuffix;this.basePackage = basePackage;}/*** 根据前缀和环境进行构造BeetlsqlProperties** @param prefix* @param env* @return*/public static BeetlsqlProperties build(String prefix, Environment env) {String tempPrefix = prefix + BeetlsqlConstants.SEQ;BeetlsqlProperties beetlsqlProperties = new BeetlsqlProperties();//setter sqlRootString sqlRoot = tempPrefix + BeetlsqlParamType.SQL_ROOT.getName();beetlsqlProperties.setSqlRoot(env.getProperty(sqlRoot, BeetlsqlParamType.SQL_ROOT.getValue()));//setter basePackageString basePackage = tempPrefix + BeetlsqlParamType.BASE_PACKAGE.getName();beetlsqlProperties.setBasePackage(env.getProperty(basePackage));//setter daoSuffixString daoSuffix = tempPrefix + BeetlsqlParamType.DAO_SUFFIX.getName();beetlsqlProperties.setDaoSuffix(env.getProperty(daoSuffix, BeetlsqlParamType.DAO_SUFFIX.getValue()));//setter productModeString productMode = tempPrefix + BeetlsqlParamType.PRODUCT_MODE.getName();Boolean isProductMode = Boolean.valueOf(env.getProperty(productMode, BeetlsqlParamType.PRODUCT_MODE.getValue()));beetlsqlProperties.setProductMode(isProductMode);//setter offsetStartZeroString offsetStartZero = tempPrefix + BeetlsqlParamType.OFFSET_START_ZERO.getName();Boolean isOffsetStartZero = Boolean.valueOf(env.getProperty(offsetStartZero, BeetlsqlParamType.OFFSET_START_ZERO.getValue()));beetlsqlProperties.setOffsetStartZero(isOffsetStartZero);//setter debugInterceptorString debugInterceptor = tempPrefix + BeetlsqlParamType.DEBUG_INTERCEPTOR.getName();Boolean isDbugInterceptor = Boolean.valueOf(env.getProperty(debugInterceptor, BeetlsqlParamType.DEBUG_INTERCEPTOR.getValue()));beetlsqlProperties.setDebugInterceptor(isDbugInterceptor);return beetlsqlProperties;}
}

package com.yougu.core.beetlsql.properties;import lombok.Getter;
import lombok.Setter;/*** @description BeetlsqlParamType* @date 2018/12/2 21:32*/
@Getter
public enum BeetlsqlParamType {SQL_ROOT("sql-root", "/sql"),BASE_PACKAGE("base-package", "com.yougu"),DAO_SUFFIX("dao-suffix", "Mapper"),PRODUCT_MODE("product-mode", "true"),OFFSET_START_ZERO("offset-start-zero", "true"),DEBUG_INTERCEPTOR("debug-interceptor", "true");@Setterprivate String name;@Setterprivate String value;BeetlsqlParamType(String name, String value){this.name = name;this.value = value;}public String getValue() {return this.value;}
}

package com.yougu.core.beetlsql.config;import com.yougu.core.beetlsql.datasource.DataSourceBuilderFactory;
import com.yougu.core.beetlsql.datasource.MultipleDataSource;
import com.yougu.core.beetlsql.extension.BeetlsqlRuleFactory;
import com.yougu.core.beetlsql.properties.BeetlsqlConstants;
import com.yougu.core.beetlsql.properties.BeetlsqlProperties;
import lombok.extern.slf4j.Slf4j;
import org.beetl.sql.core.ClasspathLoader;
import org.beetl.sql.core.Interceptor;
import org.beetl.sql.core.UnderlinedNameConversion;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.core.db.OracleStyle;
import org.beetl.sql.ext.DebugInterceptor;
import org.beetl.sql.ext.spring4.BeetlSqlDataSource;
import org.beetl.sql.ext.spring4.BeetlSqlScannerConfigurer;
import org.beetl.sql.ext.spring4.SqlManagerFactoryBean;
import org.springframework.context.EnvironmentAware;
import org.springframework.core.env.Environment;import javax.sql.DataSource;/*** @description BaseConfig* @date 2018/12/2 21:32*/
@Slf4j
public class BaseConfig implements EnvironmentAware {private String id;private Environment environment;private BeetlsqlProperties beetlsqlProperties;@Overridepublic void setEnvironment(Environment environment) {MultipleDataSource multipleDataSource = this.getClass().getAnnotation(MultipleDataSource.class);if (multipleDataSource == null) {throw new RuntimeException("The must has @MultipleDataSource annotation with: " + this.getClass());}id = multipleDataSource.value();this.envirOnment= environment;this.beetlsqlProperties = BeetlsqlProperties.build(id, environment);log.info("The {} BeetlsqlProperties is:{}", id, beetlsqlProperties);}/*** The Build DataSource** @return*/protected DataSource buildDataSource() {if (id.equals(BeetlsqlConstants.DEFAULT_ID)) {//创建默认数据源return DataSourceBuilderFactory.buildDefaultDataSource(environment);} else {//创建自定义多数据源return DataSourceBuilderFactory.buildCustomDataSources(id, environment);}}/*** The Build SqlManagerFactoryBean** @param dataSource* @return*/protected SqlManagerFactoryBean buildSqlManagerFactoryBean(DataSource dataSource) {BeetlSqlDataSource beetlSqlDataSource = new BeetlSqlDataSource();beetlSqlDataSource.setMasterSource(dataSource);String driveClassName = environment.getProperty(id + BeetlsqlConstants.SEQ + BeetlsqlConstants.DRIVER_CLASS_NAME);SqlManagerFactoryBean sqlManagerFactoryBean = new SqlManagerFactoryBean();if (driveClassName.equals(BeetlsqlConstants.ORACLE_DRIVER_CLASS_NAME)){sqlManagerFactoryBean.setDbStyle(new OracleStyle());}if (driveClassName.equals(BeetlsqlConstants.MYSQL_DRIVER_CLASS_NAME)){sqlManagerFactoryBean.setDbStyle(new MySqlStyle());}sqlManagerFactoryBean.setCs(beetlSqlDataSource);sqlManagerFactoryBean.setDbStyle(new H2Style());log.info("database : {}, sql : {}", beetlsqlProperties.getBasePackage(), beetlsqlProperties.getSqlRoot());sqlManagerFactoryBean.setSqlLoader(new ClasspathLoader(beetlsqlProperties.getSqlRoot()));sqlManagerFactoryBean.setNc(new UnderlinedNameConversion());sqlManagerFactoryBean.getExtProperties().put("PRODUCT_MODE", beetlsqlProperties.getProductMode().toString());sqlManagerFactoryBean.getExtProperties().put("OFFSET_START_ZERO", beetlsqlProperties.getOffsetStartZero().toString());if (beetlsqlProperties.getDebugInterceptor()) {sqlManagerFactoryBean.setInterceptors(new Interceptor[]{new DebugInterceptor()});}try {//自定义规则BeetlsqlRuleFactory.builderSQLManager(sqlManagerFactoryBean.getObject());} catch (Exception e) {log.error("The builder custom rule is exception", e);}return sqlManagerFactoryBean;}/*** The build BeetlSqlScannerConfigurer** @param sqlManagerFactoryBeanName* @return*/protected BeetlSqlScannerConfigurer buildBeetlSqlScannerConfigurer(String sqlManagerFactoryBeanName) {BeetlSqlScannerConfigurer beetlSqlScannerCOnfigurer= new BeetlSqlScannerConfigurer();beetlSqlScannerConfigurer.setBasePackage(beetlsqlProperties.getBasePackage());beetlSqlScannerConfigurer.setDaoSuffix(beetlsqlProperties.getDaoSuffix());beetlSqlScannerConfigurer.setSqlManagerFactoryBeanName(sqlManagerFactoryBeanName);return beetlSqlScannerConfigurer;}}

package com.yougu.core.beetlsql.config;import com.yougu.core.beetlsql.datasource.MultipleDataSource;
import com.yougu.core.beetlsql.properties.BeetlsqlConstants;
import lombok.extern.slf4j.Slf4j;
import org.beetl.sql.ext.spring4.BeetlSqlScannerConfigurer;
import org.beetl.sql.ext.spring4.SqlManagerFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;import javax.sql.DataSource;/*** @description BeetlsqlConfig* @date 2018/12/2 21:32*/
@Slf4j
@Configuration
@MultipleDataSource(BeetlsqlConstants.DEFAULT_ID)
public class BeetlsqlConfig extends BaseConfig {/*** 创建数据源,并使用@Primary设置为默认数据源** @return*/@Primary@Bean(name = BeetlsqlConstants.DEFAULT_DATA_SOURCE)public DataSource dataSource() {System.out.println("========================= init default");return super.buildDataSource();}/*** 创建SqlManagerFactoryBean,并使用@Primary设置为默认SqlManagerFactoryBean** @param dataSource* @return*/@Bean(name = BeetlsqlConstants.DEFAULT_SQL_MANAGER_FACTORY_BEAN)public SqlManagerFactoryBean sqlManagerFactoryBean(@Qualifier(BeetlsqlConstants.DEFAULT_DATA_SOURCE) DataSource dataSource) {return super.buildSqlManagerFactoryBean(dataSource);}/*** 创建BeetlSqlScannerConfigurer,并使用@Primary设置为默认BeetlSqlScannerConfigurer** @return*/@Bean(name = BeetlsqlConstants.DEFAULT_BEETL_SQL_SCANNER_CONFIGURER)public BeetlSqlScannerConfigurer beetlSqlScannerConfigurer() {return super.buildBeetlSqlScannerConfigurer(BeetlsqlConstants.DEFAULT_SQL_MANAGER_FACTORY_BEAN);}}

package com.yougu.core.beetlsql.config;import com.yougu.core.beetlsql.datasource.MultipleDataSource;
import com.yougu.core.beetlsql.properties.BeetlsqlConstants;
import lombok.extern.slf4j.Slf4j;
import org.beetl.sql.ext.spring4.BeetlSqlScannerConfigurer;
import org.beetl.sql.ext.spring4.SqlManagerFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/*** @description BeetlsqlSecondaryConfig* @date 2019/8/25 12:52* */
@Slf4j
@Configuration
@MultipleDataSource(BeetlsqlConstants.SECONDARY_ID)
public class BeetlsqlSecondConfig extends BaseConfig {/*** 创建数据源，这是第二个数据源* @return*/@Bean(name = BeetlsqlConstants.SECONDARY_DATA_SOURCE)public DataSource dataSource() {System.out.println("========================= init secondary");return super.buildDataSource();}/*** 创建SqlManagerFactoryBean* @param dataSource* @return*/@Bean(name = BeetlsqlConstants.SECONDARY_SQL_MANAGER_FACTORY_BEAN)public SqlManagerFactoryBean sqlManagerFactoryBean(@Qualifier(BeetlsqlConstants.SECONDARY_DATA_SOURCE) DataSource dataSource) {return super.buildSqlManagerFactoryBean(dataSource);}/*** 创建BeetlSqlScannerConfigurer** @return*/@Bean(name = BeetlsqlConstants.SECONDARY_BEETL_SQL_SCANNER_CONFIGURER)public BeetlSqlScannerConfigurer beetlSqlScannerConfigurer() {return super.buildBeetlSqlScannerConfigurer(BeetlsqlConstants.SECONDARY_SQL_MANAGER_FACTORY_BEAN);}}

 以上这些就可以使用了，下面看看具体与数据库交互例子：

package com.yougu.core.repository;import com.yougu.client.db.po.RolePO;
import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.mapper.BaseMapper;
import org.springframework.stereotype.Component;@Component
@SqlResource("role")
public interface RoleRepository extends BaseMapper {}

package com.yougu.core.second.repository;import com.yougu.client.second.db.po.RolePO;
import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.mapper.BaseMapper;
import org.springframework.stereotype.Component;@Component
@SqlResource("role2")
public interface Role2Repository extends BaseMapper {}

 两个Repository中使用的RolePO是数据库表结构对应的实体Bean，根据个人的表创建

package com.yougu.core.controller;import com.yougu.core.repository.RoleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController
public class RoleController implements RoleApi {@Autowiredprivate com.yougu.core.repository.RoleRepository repository;@Autowiredprivate com.yougu.core.second.repository.Role2Repository secondRepository;@RequestMapping(value = "/list2" ,method = RequestMethod.GET) public List list() {/* 查询第一个数据库中的数据 */return repository.all();}@RequestMapping(value = "/list2" ,method = RequestMethod.GET) public List list2() {/* 查询第二个数据库中的数据 */ return secondRepository.all();}
}

 参照以上例子，可以分别对多个数据库进行交互