因为项目整合需要同时使用mysql和sql server两个数据库;经过查询各个博客,终于搞定了,现在分享一下,不足之处还请指教。
配置pom依赖
mysql驱动我选择的这个版本,因为使用默认的,经常会报错,查看资料后,改的这个版本。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<!-- mysql驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.0.8</version><!--$NO-MVN-MAN-VER$ -->
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
yml配置:
关于druid配置,不知道为什么,在yml中配置的filters不生效,索性就只配置了数据库的连接数据,其他的均为默认:
spring:
datasource1:
mysql:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://***.***.***.***:3306/gxrl?useSSL=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
username: ***
password: I***
type: com.alibaba.druid.pool.DruidDataSource
datasource2:
sqlserver:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://***.***.0.***:1433;DatabaseName=***
username: sa
password: ***
type: com.alibaba.druid.pool.DruidDataSource
然后就是配置数据源代码:
这里一定要注意前缀必须与yml一致;
要在数据源setfilters,不然监控不到sql的运行情况。
/**
* 配置双数据源
* @author Administrator
*/
@Configuration
public class DataSourceConfig {
@Resource
private DataSourceProperties basicProperties;
/**
* mysql数据源
*
* @return DataSource
* application.yml 中对应属性的前缀
*/
@Bean(name = "dbOneDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource1.mysql")
public DataSource dbOneDataSource() throws SQLException {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setFilters("stat,wall,slf4j");
return druidDataSource;
}
/**
* sql server 数据源
*
* @return DataSource
* application.yml 中对应属性的前缀
*/
@Bean(name = "dbTwoDataSource")
@ConfigurationProperties(prefix = "spring.datasource2.sqlserver")
public DataSource dbTwoDataSource() throws SQLException {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setFilters("stat,wall,slf4j");
return druidDataSource;
}
}
mybatis配置:
对于加了事务的方法,一定要指定事务管理器
添加transactionManager = "sqlSessionTransactionManagerOne"
/**
* @author Administrator
*/
public class MybatisConfig {
/**
* mysql数据库 SqlSessionFactory && SqlSessionTemplate 创建
*/
@Configuration
@MapperScan(basePackages = {"com.itangde.beatcharge.mapper"},
sqlSessionFactoryRef = "sqlSessionFactoryOne",
sqlSessionTemplateRef = "sqlSessionTemplateOne")
public static class dbOne {
@Resource
DataSource dbOneDataSource;
@Bean(name = "sqlSessionFactoryOne")
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dbOneDataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapperxml/*.xml"));
System.out.println("sqlSessionFactoryOne 创建成功。");
return bean.getObject();
}
@Bean(name = "sqlSessionTransactionManagerOne")
public DataSourceTransactionManager transactionManager(){
return new DataSourceTransactionManager(dbOneDataSource);
}
@Bean(name = "sqlSessionTemplateOne")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryOne") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
/**
* sql server数据库 SqlSessionFactory && SqlSessionTemplate 创建
*/
@Configuration
@MapperScan(basePackages = {"com.itangde.beatcharge.dao"},
sqlSessionFactoryRef = "sqlSessionFactoryTwo",
sqlSessionTemplateRef = "sqlSessionTemplateTwo")
public static class dbTwo {
@Resource
DataSource dbTwoDataSource;
@Bean(name = "sqlSessionFactoryTwo")
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dbTwoDataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
System.out.println("sqlSessionFactoryTwo 创建成功。");
return bean.getObject();
}
@Bean(name = "qlSessionTransactionManagerTwo")
public DataSourceTransactionManager transactionManager(){
return new DataSourceTransactionManager(dbTwoDataSource);
}
@Bean(name = "sqlSessionTemplateTwo")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryTwo") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
}
配置druid的监控
initParams.put(“loginUsername”,“admin”);
initParams.put(“loginPassword”,“admin”);
这两行可以注掉,注掉后,登录监控页面就不需要密码账号了。
initParams 不能为空,否则会报错。
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
/**
* @author Administrator
* Druid监控配置
*/
@Configuration
public class DruidMoniterConfig {
/**
* 1、配置一个管理后台的Servlet
*/
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String,String> initParams = new HashMap<>();
initParams.put("loginUsername","admin");
initParams.put("loginPassword","admin");
//默认就是允许所有访问
initParams.put("allow","");
bean.setInitParameters(initParams);
return bean;
}
/**
* 2、配置一个web监控的filter
*/
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
initParams.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
spring监控
这个是要配置你的mapper接口地址,不然也监控不到。
期初我配置的是xml文件地址,一直没有数据。
/**
* spring监控配置
* @author Administrator
*/
@Configuration
public class SpringDaoMethodAspect {
@Bean
public DruidStatInterceptor druidStatInterceptor() {
return new DruidStatInterceptor();
}
/**
* mapper切点
* @return JdkRegexpMethodPointcut
*/
@Bean
@Scope("prototype")
public JdkRegexpMethodPointcut druidStatPointcut() {
JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
pointcut.setPattern("***.***.***.mapper.*");
return pointcut;
}
@Bean
public DefaultPointcutAdvisor druidStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
DefaultPointcutAdvisor defaultPointAdvisor = new DefaultPointcutAdvisor();
defaultPointAdvisor.setPointcut(druidStatPointcut);
defaultPointAdvisor.setAdvice(druidStatInterceptor);
return defaultPointAdvisor;
}
}
启动效果如下:
网上也有去掉阿里广告的帖子,可以自己搜一下,配置上。我比较懒,就没有做查如何去广告。