pom文件引入
原本项目用的是mysql,因为需要从另一个系统取数据,索性配成双数据库.
引入sqlserver引擎
<!-- sqlserver -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
配置application.yml
spring:
application:
name: wds
mvc:
static-path-pattern: /**
pathmatch:
matching-strategy: ant_path_matcher
datasource:
# 单数据库配置
# type: com.alibaba.druid.pool.DruidDataSource
# driver-class-name: com.mysql.cj.jdbc.Driver
# druid:
# url: jdbc:mysql://127.0.0.1:3306/party?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
# username: root
# password:root
# initial-size: 10
# max-active: 100
# min-idle: 10
# max-wait: 60000
# pool-prepared-statements: true
# max-pool-prepared-statement-per-connection-size: 20
# time-between-eviction-runs-millis: 60000
# min-evictable-idle-time-millis: 300000
# #validation-query: SELECT 1 FROM DUAL
# test-while-idle: true
# test-on-borrow: false
# test-on-return: false
# stat-view-servlet:
# enabled: true
# url-pattern: /druid/*
# login-username: admin
# login-password: admin
# filter:
# stat:
# log-slow-sql: true
# slow-sql-millis: 1000
# merge-sql: false
# wall:
# config:
# multi-statement-allow: true
#####################################################################################
# 双数据库配置
mysqldb:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
url: jdbc:mysql://127.0.0.1:3306/party?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password:root
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
#validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
login-username: admin
login-password: admin
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: false
wall:
config:
multi-statement-allow: true
sqlserverdb:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://127.0.0.1:1433
username: sa
password: 1
#mybatis plus 设置
mybatis-plus:
mapper-locations: classpath*:cn/wantsong/base/modules/**/xml/*Mapper.xml
global-config:
# 关闭MP3.0自带的banner
banner: false
db-config:
#主键类型 0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
id-type: ASSIGN_UUID
# 默认数据库表下划线命名
table-underline: true
configuration:
map-underscore-to-camel-case: false
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
configuration2:
map-underscore-to-camel-case: false
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
原先同事搭的框架,写的configuration,不知道干啥的,干掉.注掉类名上面的注解就行.
import cn.base.utils.oConvertUtils;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
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;
/**
* 单数据源配置(datasource.open = false时生效)
* @Author
*
*/
//@Configuration
//@MapperScan(value={"cn.base.**.mapper*"})
public class MybatisPlusSaasConfig {
/**
* tenant_id 字段名
*/
private static final String TENANT_FIELD_NAME = "tenant_id";
/**
* 哪些表需要做多租户 表需要添加一个字段 tenant_id
*/
private static final List<String> tenantTable = new ArrayList<String>();
static {
tenantTable.add("demo");
// //角色、菜单、部门
// tenantTable.add("sys_role");
// tenantTable.add("sys_permission");
// tenantTable.add("sys_depart");
}
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
@Override
public Expression getTenantId() {
String tenant_id = oConvertUtils.getString(TenantContext.getTenant(),"0");
return new LongValue(tenant_id);
}
@Override
public String getTenantIdColumn(){
return TENANT_FIELD_NAME;
}
// 返回 true 表示不走租户逻辑
@Override
public boolean ignoreTable(String tableName) {
for(String temp: tenantTable){
if(temp.equalsIgnoreCase(tableName)){
return false;
}
}
return true;
}
}));
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
// /**
// * 下个版本会删除,现在为了避免缓存出现问题不得不配置
// * @return
// */
// @Bean
// public ConfigurationCustomizer configurationCustomizer() {
// return configuration -> configuration.setUseDeprecatedExecutor(false);
// }
// /**
// * mybatis-plus SQL执行效率插件【生产环境可以关闭】
// */
// @Bean
// public PerformanceInterceptor performanceInterceptor() {
// return new PerformanceInterceptor();
// }
}
写两个configuration,一个数据源一个.
mysql:
package cn.base.config.mybatis;
import cn.base.utils.oConvertUtils;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
/**
* @author kaisens
* @time 2022/8/29 15:23
* @description sqlserver 数据库
**/
@Configuration
@MapperScan(basePackages = "cn.base.modules.**.mapper*",sqlSessionFactoryRef = "mysqldbSqlSessionFactory")
public class MybatisMySqlConfig {
@Value("${spring.datasource.mysqldb.driver-class-name}")
String driverClass;
@Value("${spring.datasource.mysqldb.druid.url}")
String url;
@Value("${spring.datasource.mysqldb.druid.username}")
String userName;
@Value("${spring.datasource.mysqldb.druid.password}")
String passWord;
/**
* tenant_id 字段名
*/
private static final String TENANT_FIELD_NAME = "tenant_id";
/**
* 哪些表需要做多租户 表需要添加一个字段 tenant_id
*/
private static final List<String> tenantTable = new ArrayList<String>();
static {
tenantTable.add("demo");
// //角色、菜单、部门
// tenantTable.add("sys_role");
// tenantTable.add("sys_permission");
// tenantTable.add("sys_depart");
}
@Bean
@ConfigurationProperties(prefix = "mybatis-plus.configuration")
public MybatisConfiguration mybatisConfiguration() {
return new MybatisConfiguration();
}
@Primary
@Bean(name = "mysqldbDataSource")
@ConfigurationProperties("spring.datasource.mysqldb")
public DataSource masterDataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(passWord);
return dataSource;
}
@Primary
@Bean(name = "mysqldbSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqldbDataSource") DataSource dataSource,MybatisConfiguration mybatisConfiguration) throws Exception {
MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource);
sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath*:cn/base/modules/**/mapper/xml/*Mapper.xml"));
sessionFactoryBean.setConfiguration(mybatisConfiguration);
return sessionFactoryBean.getObject();
}
@Primary
@Bean(name = "mysqldbSqlSessionTemplate")
public SqlSessionTemplate sqlSessionFactoryTemplate(@Qualifier("mysqldbSqlSessionFactory")SqlSessionFactory sqlSessionFactory ) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
@Override
public Expression getTenantId() {
String tenant_id = oConvertUtils.getString(TenantContext.getTenant(),"0");
return new LongValue(tenant_id);
}
@Override
public String getTenantIdColumn(){
return TENANT_FIELD_NAME;
}
// 返回 true 表示不走租户逻辑
@Override
public boolean ignoreTable(String tableName) {
for(String temp: tenantTable){
if(temp.equalsIgnoreCase(tableName)){
return false;
}
}
return true;
}
}));
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
}
sqlserver:
package cn.base.config.mybatis;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;
/**
* @author
* @time 2022/8/29 15:23
* @description sqlserver 数据库
**/
@Configuration
@MapperScan(basePackages = "cn.base.modules.**.maper*",sqlSessionFactoryRef = "sqlserverdbSqlSessionFactory")
public class MybatisSqlServerConfig {
@Value("${spring.datasource.sqlserverdb.driver-class-name}")
String driverClass;
@Value("${spring.datasource.sqlserverdb.url}")
String url;
@Value("${spring.datasource.sqlserverdb.username}")
String userName;
@Value("${spring.datasource.sqlserverdb.password}")
String passWord;
@Bean
@ConfigurationProperties(prefix = "mybatis-plus.configuration2")
public MybatisConfiguration mybatisConfiguration2() {
return new MybatisConfiguration();
}
@Bean(name = "sqlserverdbDataSource")
@ConfigurationProperties("spring.datasource.sqlserverdb")
public DataSource masterDataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(passWord);
return dataSource;
}
@Bean(name = "sqlserverdbSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("sqlserverdbDataSource") DataSource dataSource,MybatisConfiguration mybatisConfiguration2) throws Exception {
MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource);
sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath*:cn/base/modules/**/maper/xml/*Mapper.xml"));
sessionFactoryBean.setConfiguration(mybatisConfiguration2);
return sessionFactoryBean.getObject();
}
@Bean(name = "sqlserverdbSqlSessionTemplate")
public SqlSessionTemplate sqlSessionFactoryTemplate(@Qualifier("sqlserverdbSqlSessionFactory")SqlSessionFactory sqlSessionFactory ) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
项目结构
成功运行.
注意看几个扫描路径是否正确,还有数据库链接信息是否有误(这玩意不会主动报错,都是抓出来的).
总之就这几个点我调试了一天才搞定,主要还是因为新框架以前根本没用过mybatis-plus和boot.