Spring Boot整合mybatis,多个数据源动态切换
导入依赖,配置
依赖
<!-- mybatis -->
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- 分页插件 -->
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
<!-- mybatis-plus注解依赖 <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.1</version> </dependency> -->
<!-- sql server 驱动连接 -->
<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>7.0.0.jre8</version>
<scope>test</scope>
</dependency>
<!-- mysql -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4 -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
<scope>system</scope>
<systemPath>${project.basedir}/libs/sqljdbc4-4.0.jar</systemPath>
</dependency>
<!-- 根据实体类生成表 -->
<dependency>
<groupId>com.gitee.sunchenbin.mybatis.actable</groupId>
<artifactId>mybatis-enhance-actable</artifactId>
<version>1.0.8.1.RELEASE</version>
</dependency>
配置
server:
port: 8002
tomcat:
#格式
uri-encoding: UTF-8
#最大
max-threads: 1000
#最小
min-spare-threads: 30
# 别称
tcmtown-datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/tcmtown?characterEncoding=UTF8&serverTimezone=UTC&serverTimezone=GMT%2b8&useSSL=false
username: root
password: root
#别称
health-datasource:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://localhost:1433;DatabaseName=health_tcm360_DB;serverTimezone=GMT%2b8;useSSL=false
username: root
password: root
#别称
culture-datasource:
url: jdbc:sqlserver://localhost:1433;DatabaseName=culture_tcm360_DB;serverTimezone=GMT%2b8;useSSL=false
username: root
password: root
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
#别称
herbal-datasource:
url: jdbc:sqlserver://localhost:1433;DatabaseName=herbal_tcm360_DB;serverTimezone=GMT%2b8;useSSL=false
username: root
password: root
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
#别称
lnyby-datasource:
url: jdbc:sqlserver://localhost:1433;DatabaseName=lnyby_DB;serverTimezone=GMT%2b8;useSSL=false
username: root
password: root
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
#修改最大上传文件限制
spring:
jackson:
time-zone: GMT+8
servlet:
multipart:
max-file-size: 100MB
max-request-size: 100MB
#解决中文乱码
http:
encoding:
force: true
charset: utf-8
enabled: true
#行的sql打印出来，在开发或测试的时候可以用
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
编写工具类
列出所有的数据源key
package com.tcm.common.util;
/**
* 列出所有的数据源key（常用数据库名称来命名）
* 注意：
* 这里的变量是 配置文件中的别称，只取datasource前的
*/
public enum DatabaseType {
/**
* mysql
*/
tcmtown,
/**
* sql server 数据库
*/
herbal,
/**
* sql server 数据库
*/
culture,
/**
* sql server 数据库
*/
health,
lnyby
}
动态数据源
package com.tcm.common.util;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import com.tcm.common.util.DatabaseContextHolder;
/**
* 动态数据源（需要继承AbstractRoutingDataSource）
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
// System.out.println("\"数据源为{} "+DatabaseContextHolder.getDatabaseType());
return DatabaseContextHolder.getDatabaseType();
}
}
保存一个线程安全的DatabaseType容器
package com.tcm.common.util;
/**
* 作用：
* 保存一个线程安全的DatabaseType容器
*/
public class DatabaseContextHolder {
private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
public static DatabaseType getDatabaseType(){
return contextHolder.get();
}
public static void setDatabaseType(DatabaseType type) {
contextHolder.set(type);
}
public static void clearDataSource(){
contextHolder.remove();
}
}
数据源注入对应的方法实例
package com.tcm.common.config;
import com.tcm.common.service.IArticleCategorySqlService;
import com.tcm.common.service.imp.ArticleCategorySqlServiceImp;
import com.tcm.common.util.DatabaseContextHolder;
import com.tcm.common.util.DatabaseType;
import com.tcm.culture.service.CultureSqlService;
import com.tcm.culture.service.CultureTableService;
import com.tcm.culture.service.CultureViewService;
import com.tcm.culture.service.imp.CultureSqlServiceImp;
import com.tcm.culture.service.imp.CultureTableServiceImp;
import com.tcm.culture.service.imp.CultureViewServiceImp;
import com.tcm.health.service.HealthSqlService;
import com.tcm.health.service.HealthTableService;
import com.tcm.health.service.HealthViewService;
import com.tcm.health.service.imp.HealthSqlServiceImp;
import com.tcm.health.service.imp.HealthTableServiceImp;
import com.tcm.health.service.imp.HealthViewServiceImp;
import com.tcm.herbal.service.HerbalSqlService;
import com.tcm.herbal.service.HerbalTableService;
import com.tcm.herbal.service.imp.HerbalSqlServiceImp;
import com.tcm.herbal.service.imp.HerbalTableServiceImp;
import com.tcm.herbal.service.imp.HerbalViewServiceImp;
import com.tcm.lnyby.service.LnybySqlService;
import com.tcm.lnyby.service.LnybyTableService;
import com.tcm.lnyby.service.LnybyViewService;
import com.tcm.lnyby.service.imp.LnybySqlServiceImp;
import com.tcm.lnyby.service.imp.LnybyTableServiceImp;
import com.tcm.lnyby.service.imp.LnybyViewServiceImp;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* 数据源注入对应的方法实例
*/
@Aspect
@Order(1)
@Component
public class DataSourceAspect {
/**
* 使用空方法定义切点表达式
*/
@Pointcut("execution(* com.tcm.*.service.**.*(..))")
public void declareJointPointExpression() {
}
@Before("declareJointPointExpression()")
public void setDataSourceKey(JoinPoint point) {
// 不同的service类连接不同的数据源
// 根据连接点所属的类实例，动态切换数据源
/**
* @herbal
* */
if (point.getTarget() instanceof HerbalTableService || point.getTarget() instanceof HerbalViewServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.herbal);
}
else if (point.getTarget() instanceof HerbalTableService || point.getTarget() instanceof HerbalTableServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.herbal);
}
else if (point.getTarget() instanceof HerbalSqlService || point.getTarget() instanceof HerbalSqlServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.herbal);
}
/**
* @lnyby
* */
else if (point.getTarget() instanceof IArticleCategorySqlService || point.getTarget() instanceof ArticleCategorySqlServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.herbal);
}
/**
* @health
* */
if (point.getTarget() instanceof HealthViewService || point.getTarget() instanceof HealthViewServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.health);
}
else if (point.getTarget() instanceof HealthTableService || point.getTarget() instanceof HealthTableServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.health);
}
else if (point.getTarget() instanceof HealthSqlService || point.getTarget() instanceof HealthSqlServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.health);
}
/**
* @culture
* */
if (point.getTarget() instanceof CultureViewService || point.getTarget() instanceof CultureViewServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.culture);
}
else if (point.getTarget() instanceof CultureTableService || point.getTarget() instanceof CultureTableServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.culture);
}
else if (point.getTarget() instanceof CultureSqlService || point.getTarget() instanceof CultureSqlServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.culture);
}
/**
* @lnyby
* */
if (point.getTarget() instanceof LnybyViewService || point.getTarget() instanceof LnybyViewServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.lnyby);
}
else if (point.getTarget() instanceof LnybyTableService || point.getTarget() instanceof LnybyTableServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.lnyby);
}else if (point.getTarget() instanceof LnybySqlService || point.getTarget() instanceof LnybySqlServiceImp) {
DatabaseContextHolder.setDatabaseType(DatabaseType.lnyby);
}
}
@After("declareJointPointExpression()")
public void after(JoinPoint point) {
DatabaseContextHolder.clearDataSource();
}
}
springboot集成mybatis的基本入口
package com.tcm.common.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.tcm.common.util.DatabaseType;
import com.tcm.common.util.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.config.PropertiesFactoryBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* springboot集成mybatis的基本入口 1）创建数据源(如果采用的是默认的tomcat-jdbc数据源，则不需要)
* 2）创建SqlSessionFactory 3）配置事务管理器，除非需要使用事务，否则不用配置
*/
@Configuration // 该注解类似于spring配置文
@ComponentScan(basePackages = { "com.gitee.sunchenbin.mybatis.actable.manager.*" })
public class MyBatisConfig {
/**
* 创建数据源(数据源的名称：方法名可以取为XXXDataSource(),XXX为数据库名称,该名称也就是数据源的名称)
*/
@Primary
@ConfigurationProperties(prefix = "tcmtown-datasource")
@Bean(name = "tcmtownDataSource")
public DataSource tcmtownDataSource() throws Exception {
return new DruidDataSource();
}
/**
* @health
*/
@ConfigurationProperties(prefix = "health-datasource")
@Bean(name = "healthDataSource")
public DataSource healthDataSource() throws Exception {
return new DruidDataSource();
}
/**
* @herbal
*/
@ConfigurationProperties(prefix = "herbal-datasource")
@Bean(name = "herbalDataSource")
public DataSource herbalDataSource() throws Exception {
return new DruidDataSource();
}
/**
* @culture
*/
@ConfigurationProperties(prefix = "culture-datasource")
@Bean(name = "cultureDataSource")
public DataSource cultureDataSource() throws Exception {
return new DruidDataSource();
}
/**
* @culture
*/
@ConfigurationProperties(prefix = "lnyby-datasource")
@Bean(name = "lnybyDataSource")
public DataSource lnybyDataSource() throws Exception {
return new DruidDataSource();
}
/**
* @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错
* @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例）
*/
@Bean(name = "dynamicDataSource")
public DynamicDataSource dataSource(@Qualifier("tcmtownDataSource") DataSource tcmtownDataSource,
@Qualifier("healthDataSource") DataSource healthDataSource,
@Qualifier("herbalDataSource") DataSource herbalDataSource,
@Qualifier("cultureDataSource") DataSource cultureDataSource,
@Qualifier("lnybyDataSource") DataSource lnybyDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DatabaseType.tcmtown, tcmtownDataSource);
targetDataSources.put(DatabaseType.health, healthDataSource);
targetDataSources.put(DatabaseType.herbal, herbalDataSource);
targetDataSources.put(DatabaseType.culture, cultureDataSource);
targetDataSources.put(DatabaseType.lnyby, lnybyDataSource);
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setDefaultTargetDataSource(tcmtownDataSource);// 默认的datasource设置为tcmtownDataSource
dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
return dataSource;
}
/**
* @sqlSessionFactory 根据数据源创建SqlSessionFactory
*/
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
throws Exception {
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
fb.setDataSource(dynamicDataSource);// 指定数据源(这个必须有，否则报错)
// 下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加
fb.setMapperLocations(resolver.getResources("classpath*:com/gitee/sunchenbin/mybatis/actable/mapping/*/*.xml"));
fb.setTypeAliasesPackage("com.*.entity.*");
fb.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return fb.getObject();
}
@Bean(name = "dataSourceTransactionManager")
public DataSourceTransactionManager dataSourceTransactionManager(
@Qualifier("tcmtownDataSource") DataSource tcmtownDataSource) {
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(tcmtownDataSource);
return dataSourceTransactionManager;
}
@Bean(name = "configProperties")
public PropertiesFactoryBean configProperties() throws Exception {
PropertiesFactoryBean propertiesFactoryBean = new PropertiesFactoryBean();
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
propertiesFactoryBean.setLocations(resolver.getResources("classpath*:application.properties"));
return propertiesFactoryBean;
}
}
将mapper层代码，自动转换成mybatis对应的dao层代码（本人自己的理解）
package com.tcm.common.config;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import io.lettuce.core.dynamic.annotation.CommandNaming;
@Configuration
@AutoConfigureAfter(MyBatisConfig.class)
public class MyBatisMapperScannerConfig {
@Bean(name = "mapperScannerConfigurer")
public MapperScannerConfigurer mapperScannerConfigurer() throws Exception{
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
mapperScannerConfigurer.setBasePackage("com.*.mapper.*;com.gitee.sunchenbin.mybatis.actable.dao.*");
mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
return mapperScannerConfigurer;
}
}
动态切换数据源到此结束，下面的是根据实体类创建在数据库中创建表
根据实体类自动创建数据库表，默认是在
配置
#create：系统启动后，会将所有的表删除掉，然后根据entity类中配置的结构重新建表，该操作会破坏原有数据。
#update：系统启动后，会根据entity类中配置的结构对表字段进行增删改操作
#------增：添加数据库表；根据实体向数据库表中添加字段
#------删：根据实体删除数据库表中的字段；不能实现删除项目实体类而删除数据库表
#------改：修改数据库字段的名字、属性
#none：系统不做任何处理
mybatis.table.auto=update
#用来配置要扫描的用于创建表的实体类的包名 多个就使用逗号分隔
mybatis.model.pack=com.tcm.health.entity,com.tcm.herbal.entity,com.tcm.culture.entity,com.tcm.lnyby.entity,com.tcm.common.entity
#错误的写法
#mybatis.model.pack=com.tcm.*.entity
#用来区别数据库，预计会支持这四种数据库mysql/oracle/sqlserver/postgresql，但目前仅支持mysql
mybatis.database.type=mysql
实体类
package com.tcm.health.entity;
import com.gitee.sunchenbin.mybatis.actable.annotation.Column;
import com.gitee.sunchenbin.mybatis.actable.annotation.Table;
import com.gitee.sunchenbin.mybatis.actable.command.BaseModel;
import com.gitee.sunchenbin.mybatis.actable.constants.MySqlTypeConstant;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.tcm.sys.entity.Pages;
import java.util.*;
@SuppressWarnings("serial")
// 数据库为MyBatisConfig类中 dataSource.setDefaultTargetDataSource(tcmtownDataSource); 设置的
// 表名
@Table(name="view_channel_mother_childer")
@Data
public class HealthChilder extends BaseModel {
@JsonProperty(value = "add_time")
//name 数据库中的字段名称 type 数据库类型
@Column(name = "add_time",type = MySqlTypeConstant.VARCHAR)
private String add_time;
@JsonProperty(value = "call_index")
@Column(name = "call_index",type = MySqlTypeConstant.VARCHAR)
private String call_index;
@JsonProperty(value = "category_id")
@Column(name = "category_id",type = MySqlTypeConstant.VARCHAR)
private String category_id;
@JsonProperty(value = "channel_id")
@Column(name = "channel_id",type = MySqlTypeConstant.VARCHAR)
private String channel_id;
@JsonProperty(value = "click")
@Column(name = "click",type = MySqlTypeConstant.VARCHAR)
private String click;
@JsonProperty(value = "content")
@Column(name = "content",type = MySqlTypeConstant.LONGTEXT)
private String content;
@JsonProperty(value = "groupids_view")
@Column(name = "groupids_view",type = MySqlTypeConstant.VARCHAR)
private String groupids_view;
@JsonProperty(value = "id")
@Column(name = "id",type = MySqlTypeConstant.VARCHAR)
private String id;
@JsonProperty(value = "img_url")
@Column(name = "img_url",type = MySqlTypeConstant.VARCHAR)
private String img_url;
@JsonProperty(value = "is_hot")
@Column(name = "is_hot",type = MySqlTypeConstant.VARCHAR)
private String is_hot;
@JsonProperty(value = "is_msg")
@Column(name = "is_msg",type = MySqlTypeConstant.VARCHAR)
private String is_msg;
@JsonProperty(value = "is_red")
@Column(name = "is_red",type = MySqlTypeConstant.VARCHAR)
private String is_red;
@JsonProperty(value = "is_slide")
@Column(name = "is_slide",type = MySqlTypeConstant.VARCHAR)
private String is_slide;
@JsonProperty(value = "is_sys")
@Column(name = "is_sys",type = MySqlTypeConstant.VARCHAR)
private String is_sys;
@JsonProperty(value = "is_top")
@Column(name = "is_top",type = MySqlTypeConstant.VARCHAR)
private String is_top;
@JsonProperty(value = "link_url")
@Column(name = "link_url",type = MySqlTypeConstant.VARCHAR)
private String link_url;
@JsonProperty(value = "seo_description")
@Column(name = "seo_description",type = MySqlTypeConstant.VARCHAR)
private String seo_description;
@JsonProperty(value = "seo_keywords")
@Column(name = "seo_keywords",type = MySqlTypeConstant.VARCHAR)
private String seo_keywords;
@JsonProperty(value = "seo_title")
@Column(name = "seo_title",type = MySqlTypeConstant.VARCHAR)
private String seo_title;
@JsonProperty(value = "sort_id")
@Column(name = "sort_id",type = MySqlTypeConstant.VARCHAR)
private String sort_id;
@JsonProperty(value = "source")
@Column(name = "source",type = MySqlTypeConstant.VARCHAR)
private String source;
@JsonProperty(value = "status")
@Column(name = "status",type = MySqlTypeConstant.VARCHAR)
private String status;
@JsonProperty(value = "tags")
@Column(name = "tags",type = MySqlTypeConstant.VARCHAR)
private String tags;
@JsonProperty(value = "title")
@Column(name = "title",type = MySqlTypeConstant.VARCHAR)
private String title;
@JsonProperty(value = "update_time")
@Column(name = "update_time",type = MySqlTypeConstant.VARCHAR)
private String update_time;
@JsonProperty(value = "user_name")
@Column(name = "user_name",type = MySqlTypeConstant.VARCHAR)
private String user_name;
@JsonProperty(value = "vote_id")
@Column(name = "vote_id",type = MySqlTypeConstant.VARCHAR)
private String vote_id;
@JsonProperty(value = "zhaiyao")
@Column(name = "zhaiyao",type = MySqlTypeConstant.VARCHAR)
private String zhaiyao;
@JsonProperty(value = "pages")
private Pages pages;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
