spring boot整合mybatis+druid和多数据源
- 单个数据源
- pom文件:
- application.properties文件配置内容
- 单数据代码奉献上
- 多个数据源
- 目录结构
- application.properties
- 数据源1的配置
- 数据源2的配置
整合MyBatis之前,先搭建一个基本的Spring Boot项目开启Spring Boot
单个数据源
pom文件:
<!--引入druid数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 数据库 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
application.properties文件配置内容
单数据源
#datasource1
spring.datasource.druid.type=com.alibaba.druid.pool.DataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql:///myredis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456
#连接池配置
spring.datasource.initial-size=5
spring.datasource.min-idle=5
spring.datasource.max-active=20
#连接等待超时时间
spring.datasource.max-wait=30000
#配置检测可以关闭的空闲连接间隔时间
spring.datasource.time-between-eviction-runs-millis=60000
#配置连接在池中的最小生存时间
spring.datasource.min-evictable-idle-time-millis=300000
spring.datasource.validation-query=select '1' from dual
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
#打开PSCache,并且指定每个连接上PSCache的大小
spring.datasource.pool-prepared-statements=true
spring.datasource.max-open-prepared-statements=20
spring.datasource.max-pool-prepared-statement-per-connection-size=20
单数据代码奉献上
DemoController 控制层的代码
@RestController
public class DemoController {
@Autowired
public DemoService demoService;
@RequestMapping("/demo")
String getStringAll() {
return demoService.getQueryAll().toString();
}
}
service层
public interface DemoService {
/**
* 查询数据源1所有信息
*
* @return
*/
List<Teacher> getQueryAll();
}
@Service
public class DemoServiceImpl implements DemoService {
@Autowired
DemoQuery demoQuery;
@Override
public List<Teacher> getQueryAll() {
return demoQuery.getQueryAll();
}
dao 层我这里为了方便没有写.xml映射文件
@Component
@Mapper
public interface DemoQuery {
/**
* 查询所有老师信息
* @return List<Teacher>
*/
@Select("select * from teacher")
@Results(id = "teacher",value= {
@Result(property = "sno", column = "sno", javaType = String.class),
@Result(property = "name", column = "sname", javaType = String.class),
@Result(property = "sex", column = "ssex", javaType = String.class)
})
List<Teacher> getQueryAll();
}
需要在启动类加上MapperScan 后面跟上要扫描的dao
@SpringBootApplication
@MapperScan("study.startspringboot.mapper")
public class StartSpringBootApplication {
public static void main(String[] args) {
SpringApplication.run(StartSpringBootApplication.class, args);
}
}
sql语句
CREATE TABLE teacher (
SNO VARCHAR(5),
SNAME VARCHAR(9),
SSEX CHAR(3)
);
INSERT INTO teacher VALUES ('001', 'KangKang', 'M ');
INSERT INTO teacher VALUES ('002', 'Mike', 'M ');
INSERT INTO teacher VALUES ('003', 'Jane', 'F ');
到这里单个数据源就可以使用了,下面进入多个数据源模式
多个数据源
一般用于读写分离,或者分库来使用,有的教程会加上 @Primary表示主数据源,既然分开来说明有这个需求没有主次之分,这里我也使用这样的方式吧
pom文件和上面的单个数据源的!你们复制即可!然后从application.properties默认配置改成了注解方式来进行
@bean druidServlet这个是开启druid的sql时间监控的运行项目打开这个链接即可:http://localhost:8080/druid/login.html
目录结构
application.properties
#数据库访问配置,使用数据源
#datasource1
ds1.datasource.url=jdbc:mysql:///myredis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
ds1.datasource.username=root
ds1.datasource.password=123456
ds1.datasource.driverClassName=com.mysql.cj.jdbc.Driver
#datasource2
ds2.datasource.url=jdbc:mysql:///myredis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
ds2.datasource.username=root
ds2.datasource.password=123456
ds2.datasource.driverClassName=com.mysql.cj.jdbc.Driver
##
datasource.initialSize=20
datasource.minIdle=20
datasource.maxActive=200
datasource.maxWait=-1
datasource.timeBetweenEvictionRunsMillis=60000
datasource.minEvictableIdleTimeMillis=300000
datasource.testWhileIdle=true
datasource.testOnBorrow=false
datasource.testOnReturn=false
datasource.poolPreparedStatements=true
datasource.maxPoolPreparedStatementPerConnectionSize=20
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
datasource.filters=stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
datasource.useGlobalDataSourceStat=true
数据源1的配置
@Configuration
@MapperScan(basePackages = Datasource1Config.PACKAGE, sqlSessionFactoryRef = "ds1SqlSessionFactory")
public class Datasource1Config {
/**
* 数据的Dao层,你要扫描的类包的全称
*/
static final String PACKAGE = "study.startspringboot.mapper.datasource2";
/**
* 映射的资源文件
*/
static final String MAPPER_LOCATION = "classpath*:mapper/datasource1dao/*.xml";
@Value("${ds1.datasource.url}")
private String url;
@Value("${ds1.datasource.username}")
private String user;
@Value("${ds1.datasource.password}")
private String password;
@Value("${ds1.datasource.driverClassName}")
private String driverClass;
@Value("${datasource.maxActive}")
private Integer maxActive;
@Value("${datasource.minIdle}")
private Integer minIdle;
@Value("${datasource.initialSize}")
private Integer initialSize;
@Value("${datasource.maxWait}")
private Long maxWait;
@Value("${datasource.timeBetweenEvictionRunsMillis}")
private Long timeBetweenEvictionRunsMillis;
@Value("${datasource.minEvictableIdleTimeMillis}")
private Long minEvictableIdleTimeMillis;
@Value("${datasource.testWhileIdle}")
private Boolean testWhileIdle;
@Value("${datasource.testWhileIdle}")
private Boolean testOnBorrow;
@Value("${datasource.testOnBorrow}")
private Boolean testOnReturn;
@Value("${datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${datasource.filters}")
private String filters;
@Value("${datasource.connectionProperties}")
private String connectionProperties;
@Value("${datasource.useGlobalDataSourceStat}")
private boolean useGlobalDataSourceStat;
@Bean(name = "ds1DataSource")
@Primary
public DataSource ds1DataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(user);
datasource.setPassword(password);
datasource.setDriverClassName(driverClass);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery("SELECT 'x'");
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
return datasource;
}
@Bean(name = "ds1TransactionManager")
@Primary
public DataSourceTransactionManager ds1TransactionManager() {
return new DataSourceTransactionManager(ds1DataSource());
}
@Bean(name = "ds1SqlSessionFactory")
@Primary
public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("ds1DataSource") DataSource ds1DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(ds1DataSource);
sessionFactory.setTypeAliasesPackage("com.uchat.entity");
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(Datasource1Config.MAPPER_LOCATION));
return sessionFactory.getObject();
}
@Bean
public ServletRegistrationBean druidServlet() {
//访问路径为/druid时,跳转到StatViewServlet
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//启动druid 监控路径(不设置没有办法启动)
servletRegistrationBean.addUrlMappings("/druid/*");
// IP白名单
servletRegistrationBean.addInitParameter("allow", "127.0.0.1,127.0.0.1");
// IP黑名单(共同存在时,deny优先于allow)
servletRegistrationBean.addInitParameter("deny", "192.16.1.100");
//控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "1111");
//是否能够重置数据 禁用HTML页面上的“Reset All”功能
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
数据源2的配置
@Configuration
@MapperScan(basePackages = Datasource2Config.PACKAGE, sqlSessionFactoryRef = "ds2SqlSessionFactory")
public class Datasource2Config {
/**
* 数据的Dao层,打出他的包路径
*/
static final String PACKAGE = "study.startspringboot.mapper.datasource2";
/**
* 映射的资源文件
*/
static final String MAPPER_LOCATION = "classpath*:mapper/datasource2dao/*.xml";
@Value("${ds1.datasource.url}")
private String url;
@Value("${ds1.datasource.username}")
private String user;
@Value("${ds1.datasource.password}")
private String password;
@Value("${ds1.datasource.driverClassName}")
private String driverClass;
@Value("${datasource.maxActive}")
private Integer maxActive;
@Value("${datasource.minIdle}")
private Integer minIdle;
@Value("${datasource.initialSize}")
private Integer initialSize;
@Value("${datasource.maxWait}")
private Long maxWait;
@Value("${datasource.timeBetweenEvictionRunsMillis}")
private Long timeBetweenEvictionRunsMillis;
@Value("${datasource.minEvictableIdleTimeMillis}")
private Long minEvictableIdleTimeMillis;
@Value("${datasource.testWhileIdle}")
private Boolean testWhileIdle;
@Value("${datasource.testWhileIdle}")
private Boolean testOnBorrow;
@Value("${datasource.testOnBorrow}")
private Boolean testOnReturn;
@Value("${datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${datasource.filters}")
private String filters;
@Value("${datasource.connectionProperties}")
private String connectionProperties;
@Value("${datasource.useGlobalDataSourceStat}")
private boolean useGlobalDataSourceStat;
@Bean(name = "ds2DataSource")
public DataSource ds2DataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(user);
datasource.setPassword(password);
datasource.setDriverClassName(driverClass);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery("SELECT 'x'");
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
return datasource;
}
@Bean(name = "ds2TransactionManager")
public DataSourceTransactionManager ds2TransactionManager() {
return new DataSourceTransactionManager(ds2DataSource());
}
@Bean(name = "ds2SqlSessionFactory")
public SqlSessionFactory ds2SqlSessionFactory(@Qualifier("ds2DataSource") DataSource ds2DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(ds2DataSource);
sessionFactory.setTypeAliasesPackage("com.uchat.entity");
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(Datasource2Config.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
有什么问题不懂得评论我会解答!
我把代码放在了git上:https://github.com/aloneoneself/springStudy 记得给星星呀,不会Git的我会把源文件上传到附件的!!!!