前言
Spring Framework框架对数据库提供了广泛的支持。从直接使用 JdbcTemplate模版方式进行JDBC 访问到完全的对象关系映射(ORM)技术,例如:Hibernate。Spring Data简化数据库的访问,从接口Repository 进行实现,约定从方法名的关键字生成查询。
一、使用步骤
1.使用JdbcTemplate
- 通过Idea工具创建项目,选择MySQL组件。本项目用Maven和Jdk1.8构建。
- 在yml文件中填写配置。
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
url: jdbc:mysql://192.168.0.101/test01?useUnicode=yes&characterEncoding=UTF8
- 进行测试。
package com.example;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@SpringBootTest
class DemodataApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
System.out.println("CLASS:"+dataSource.getClass());
//获取conn对象就可以直接进行sql增删改了。
Connection connection = dataSource.getConnection();
System.out.println("connection:"+connection);
connection.close();
}
}
测试结果打印:
通过打印出来的Class和Connection信息可以看到默认配置的数据源为class com.zaxxer.hikari.HikariDataSource。没有其他任何配置,说明springboot默认情况下是使用的这种数据源。可以在DataSourceProperties.java源码文件中查看具体的属性配置
CLASS:class com.zaxxer.hikari.HikariDataSource
connection:HikariProxyConnection@1482714257 wrapping com.mysql.cj.jdbc.ConnectionImpl@6a9cd0f8
- 进行CRUD操作。
1、默认的数据源(com.zaxxer.hikari.HikariDataSource)。可以拿到数据库连接(java.sql.Connection),就可以使用连接和原生的 JDBC语句来操作数据库了。
2、即使不使用第三方的数据库操作框架如 MyBatis等,Spring本身也对原生的JDBC做了轻量级的封装,即 org.springframework.jdbc.core.JdbcTemplate。数据库操作的所有 CRUD 方法都在 JdbcTemplate 中。
4、Spring Boot 不仅提供了默认的数据源,同时默认已经配置好了JdbcTemplate 放在了容器中,使用时只需自己注入即可使用。
5、JdbcTemplate的自动配置原理是依赖org.springframework.boot.autoconfigure.jdbc 包下的 org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration 类
新建控制类Controller
package com.example.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
public class JdbcTemplateController {
@Autowired
JdbcTemplate jdbcTemplate;
@GetMapping("/list")
public List<Map<String,Object>> test20List(){
String sql = "select * from test20";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
return maps;
}
@GetMapping("/add")
public String addInfo(){
String sql = "insert into test20(name,sex) values('lisi', '1')";
jdbcTemplate.update(sql);
return "success";
}
@GetMapping("/update/{id}")
public String update(@PathVariable("id") Integer id){
String sql = "update test20 set name=? where id = "+id;
String name = "list";
jdbcTemplate.update(sql,name);
return "update success";
}
@GetMapping("/delete/{id}")
public String delete(@PathVariable("id")Integer id){
String sql = "delete from test20 where id = "+id;
jdbcTemplate.update(sql);
return "delete success";
}
}
进行测试:
新增:http://localhost:8888/add
查询:http://localhost:8888/list
修改:http://localhost:8888/update/1
删除:http://localhost:8888/delete/1
2.自定义数据源
- 这里使用阿里的Druid数据源,添加依赖。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
- 添加数据源配置。
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
url: jdbc:mysql://192.168.0.101/test01?useUnicode=yes&characterEncoding=UTF8
type: com.alibaba.druid.pool.DruidDataSource
- 在测试类中测试。
发现修改为Druid数据源后,打印的结果变成了目前配置的数据源信息,没有使用默认的了。
测试结果打印:
CLASS:class com.alibaba.druid.pool.DruidDataSource
connection:com.mysql.cj.jdbc.ConnectionImpl@3c87fdf2
- 自定义数据源配置源码类,进行参考。
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//
package org.springframework.boot.autoconfigure.jdbc;
import javax.sql.DataSource;
import javax.sql.XADataSource;
import org.springframework.boot.autoconfigure.AutoConfiguration;
import org.springframework.boot.autoconfigure.condition.AnyNestedCondition;
import org.springframework.boot.autoconfigure.condition.ConditionMessage;
import org.springframework.boot.autoconfigure.condition.ConditionOutcome;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.condition.SpringBootCondition;
import org.springframework.boot.autoconfigure.condition.ConditionMessage.Builder;
import org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration.Dbcp2;
import org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration.Generic;
import org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration.Hikari;
import org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration.OracleUcp;
import org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration.Tomcat;
import org.springframework.boot.autoconfigure.jdbc.metadata.DataSourcePoolMetadataProvidersConfiguration;
import org.springframework.boot.autoconfigure.sql.init.SqlInitializationAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.jdbc.EmbeddedDatabaseConnection;
import org.springframework.context.annotation.Condition;
import org.springframework.context.annotation.ConditionContext;
import org.springframework.context.annotation.Conditional;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.ConfigurationCondition.ConfigurationPhase;
import org.springframework.core.env.Environment;
import org.springframework.core.type.AnnotatedTypeMetadata;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.util.StringUtils;
@AutoConfiguration(
before = {SqlInitializationAutoConfiguration.class}
)
@ConditionalOnClass({DataSource.class, EmbeddedDatabaseType.class})
@ConditionalOnMissingBean(
type = {"io.r2dbc.spi.ConnectionFactory"}
)
@EnableConfigurationProperties({DataSourceProperties.class})
@Import({DataSourcePoolMetadataProvidersConfiguration.class})
public class DataSourceAutoConfiguration {
public DataSourceAutoConfiguration() {
}
static class EmbeddedDatabaseCondition extends SpringBootCondition {
private static final String DATASOURCE_URL_PROPERTY = "spring.datasource.url";
private final SpringBootCondition pooledCondition = new DataSourceAutoConfiguration.PooledDataSourceCondition();
EmbeddedDatabaseCondition() {
}
public ConditionOutcome getMatchOutcome(ConditionContext context, AnnotatedTypeMetadata metadata) {
Builder message = ConditionMessage.forCondition("EmbeddedDataSource", new Object[0]);
if (this.hasDataSourceUrlProperty(context)) {
return ConditionOutcome.noMatch(message.because("spring.datasource.url is set"));
} else if (this.anyMatches(context, metadata, new Condition[]{this.pooledCondition})) {
return ConditionOutcome.noMatch(message.foundExactly("supported pooled data source"));
} else {
EmbeddedDatabaseType type = EmbeddedDatabaseConnection.get(context.getClassLoader()).getType();
return type == null ? ConditionOutcome.noMatch(message.didNotFind("embedded database").atAll()) : ConditionOutcome.match(message.found("embedded database").items(new Object[]{type}));
}
}
private boolean hasDataSourceUrlProperty(ConditionContext context) {
Environment environment = context.getEnvironment();
if (environment.containsProperty("spring.datasource.url")) {
try {
return StringUtils.hasText(environment.getProperty("spring.datasource.url"));
} catch (IllegalArgumentException var4) {
}
}
return false;
}
}
static class PooledDataSourceAvailableCondition extends SpringBootCondition {
PooledDataSourceAvailableCondition() {
}
public ConditionOutcome getMatchOutcome(ConditionContext context, AnnotatedTypeMetadata metadata) {
Builder message = ConditionMessage.forCondition("PooledDataSource", new Object[0]);
return DataSourceBuilder.findType(context.getClassLoader()) != null ? ConditionOutcome.match(message.foundExactly("supported DataSource")) : ConditionOutcome.noMatch(message.didNotFind("supported DataSource").atAll());
}
}
static class PooledDataSourceCondition extends AnyNestedCondition {
PooledDataSourceCondition() {
super(ConfigurationPhase.PARSE_CONFIGURATION);
}
@Conditional({DataSourceAutoConfiguration.PooledDataSourceAvailableCondition.class})
static class PooledDataSourceAvailable {
PooledDataSourceAvailable() {
}
}
@ConditionalOnProperty(
prefix = "spring.datasource",
name = {"type"}
)
static class ExplicitType {
ExplicitType() {
}
}
}
@Configuration(
proxyBeanMethods = false
)
@Conditional({DataSourceAutoConfiguration.PooledDataSourceCondition.class})
@ConditionalOnMissingBean({DataSource.class, XADataSource.class})
@Import({Hikari.class, Tomcat.class, Dbcp2.class, OracleUcp.class, Generic.class, DataSourceJmxConfiguration.class})
protected static class PooledDataSourceConfiguration {
protected PooledDataSourceConfiguration() {
}
}
@Configuration(
proxyBeanMethods = false
)
@Conditional({DataSourceAutoConfiguration.EmbeddedDatabaseCondition.class})
@ConditionalOnMissingBean({DataSource.class, XADataSource.class})
@Import({EmbeddedDataSourceConfiguration.class})
protected static class EmbeddedDatabaseConfiguration {
protected EmbeddedDatabaseConfiguration() {
}
}
}
- 可以添加Druid独有的参数配置信息。
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
url: jdbc:mysql://192.168.0.101/test01?useUnicode=yes&characterEncoding=UTF8
type: com.alibaba.druid.pool.DruidDataSource
#Spring Boot 默认是不注入这些属性值的,需要自己绑定
#druid 数据源专有配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
#配置监控统计拦截的filters,stat:监控统计、log4j:日志记录、wall:防御sql注入
#如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority
#则导入 log4j 依赖即可,Maven 地址: https://mvnrepository.com/artifact/log4j/log4j
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
- 测试上面参数是否生效。
package com.example;
import com.alibaba.druid.pool.DruidDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.SQLException;
@SpringBootTest
class DemodataApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
DruidDataSource druidDataSource = (DruidDataSource)dataSource;
System.out.println("getMaxActive:"+druidDataSource.getMaxActive());
System.out.println("getInitialSize:"+druidDataSource.getInitialSize());
}
}
测试结果打印:
发现配置的参数没有生效,打印的默认的配置参数。
getMaxActive:8
getInitialSize:0
那么需要自定义Druid的配置类,才能绑定参数。
package com.example.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
}
测试结果打印:
加上自定义配置后,打印就是自己配置参数。
getMaxActive:20
getInitialSize:5
- Druid自带的监控使用,提供一个web界面进行查看。
需要加入log4j日志,添加依赖:
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
添加Druid后台代码配置:
package com.example.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.servlet.Servlet;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
@Bean
public ServletRegistrationBean druidServletRegistrationBean(){
ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
Map<String,String> initParams = new HashMap<>();
initParams.put("loginUsername","admin");
initParams.put("loginPassword","123456");
//允许谁可以访问
//initParams.put("allow", "localhost"):表示只有本机可以访问
//initParams.put("allow", ""):为空或者为null时,表示允许所有访问
initParams.put("allow","");
//deny:Druid 后台拒绝谁访问,添加黑名单
//initParams.put("xxx", "192.168.0.101");表示禁止此ip访问
servletRegistrationBean.setInitParameters(initParams);
return servletRegistrationBean;
}
//配置Druid监控的filter
//WebStatFilter:用于配置Web和Druid数据源之间的管理关联监控统计
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
//exclusions:设置哪些请求进行过滤排除掉,从而不进行统计
Map<String, String> initParams = new HashMap<>();
initParams.put("exclusions", "*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
//"/*" 表示过滤所有请求
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
测试结果打印:
访问:http://localhost:8888/druid
3.配置多数据源并动态切换
DataSource是和线程绑定的,动态数据源的配置主要是通过继承AbstractRoutingDataSource类实现的,实现在AbstractRoutingDataSource类中的 protected Object determineCurrentLookupKey()方法来获取数据源,所以我们需要先创建一个多线程线程数据隔离的类来存放DataSource,然后在determineCurrentLookupKey()方法中通过这个类获取当前线程的DataSource,在AbstractRoutingDataSource类中,DataSource是通过Key-value的方式保存的,我们可以通过ThreadLocal来保存Key,从而实现数据源的动态切换。
- 修改yml文件中的数据源参数配置。
spring:
datasource:
local:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
jdbc-url: jdbc:mysql://192.168.0.101/test01?useUnicode=yes&characterEncoding=UTF8
remote:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
jdbc-url: jdbc:mysql://192.168.1.111/test01?useUnicode=yes&characterEncoding=UTF8
- 创建一个枚举。
package com.example.more;
public enum DataSourceType {
REMOTE,
LOCAL
}
- 创建一个数据源切换处理类。
对数据源变量的获取、设置和情况的方法,其中threadlocal用于保存某个线程共享变量。
package com.example.more;
public class DynamicDataSourceContextHolder {
/**
* 使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源变量
* @param dataSourceType
*/
public static void setDataSourceType(String dataSourceType){
System.out.printf("切换到{%s}数据源", dataSourceType);
CONTEXT_HOLDER.set(dataSourceType);
}
/**
* 获取数据源变量
* @return
*/
public static String getDataSourceType(){
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDataSourceType(){
CONTEXT_HOLDER.remove();
}
}
- 创建一个继承AbstractRoutingDataSource的类。
动态切换数据源主要依靠AbstractRoutingDataSource。创建一个AbstractRoutingDataSource的子类,重写determineCurrentLookupKey方法,用于决定使用哪一个数据源。这里主要用到AbstractRoutingDataSource的两个属性defaultTargetDataSource和targetDataSources。defaultTargetDataSource默认目标数据源,targetDataSources(map类型)存放用来切换的数据源。
package com.example.more;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
public class DynamicDataSource extends AbstractRoutingDataSource {
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
// afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
super.afterPropertiesSet();
}
/**
* 根据Key获取数据源的信息
*
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
- 创建一个数据源配置类,用来注入相关参数。
package com.example.more;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.remote")
public DataSource remoteDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.local")
public DataSource localDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource(DataSource remoteDataSource, DataSource localDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.REMOTE.name(), remoteDataSource);
targetDataSources.put(DataSourceType.LOCAL.name(), localDataSource);
return new DynamicDataSource(remoteDataSource, targetDataSources);
}
}
- 自定义多数据源切换注解接口。
package com.example.more;
import java.lang.annotation.*;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
/**
* 切换数据源名称
*/
DataSourceType value() default DataSourceType.REMOTE;
}
- 创建AOP拦截类的实现
通过拦截上面的注解,在其执行之前处理设置当前执行SQL的数据源的信息,CONTEXT_HOLDER.set(dataSourceType)这里的数据源信息从我们设置的注解上面获取信息,如果没有设置就是用默认的数据源的信息。
package com.example.more;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
@Aspect
@Order(1)
@Component
public class DataSourceAspect {
@Pointcut("@annotation(com.example.more.DataSource)")
public void dsPointCut() {
}
@Around("dsPointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource dataSource = method.getAnnotation(DataSource.class);
if (dataSource != null) {
DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name());
}
try {
return point.proceed();
} finally {
// 销毁数据源 在执行方法之后
DynamicDataSourceContextHolder.clearDataSourceType();
}
}
}
这里需要添加aspectj依赖:
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
- 创建Controller类。
package com.example.more;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
public class TestController {
@Autowired
JdbcTemplate jdbcTemplate;
@GetMapping("/local")
@DataSource(value = DataSourceType.LOCAL)
public List<Map<String, Object>> local(){
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from test20");
return maps;
}
@GetMapping("/remote")
@DataSource(value = DataSourceType.REMOTE)
public List<Map<String, Object>> remote(){
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from test20");
return maps;
}
}
启动项目如果报了循环依赖的错误,可以在启动类添加配置:
package com.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class DemodataApplication {
public static void main(String[] args) {
SpringApplication.run(DemodataApplication.class, args);
}
}
测试结果打印:
访问本地:http://localhost:8888/local 会去查询本地库数据。
访问远程:http://localhost:8888/remote 会去查询远程库数据。
总结
所有的实现都是基于配置,一气呵成。