Have you ever heard about datasource proxies? No worries, it’s not complicated at all.
Let’s say you want to access the database from an application. First you have to configure a DataSource object which creates the actual Connections to the underlying database. With the Connection objects you can execute queries, get the metadata of the database and you can do other things as well.
Of course, not many people are using the Connection class directly as there are many frameworks (for example JPA implementations) which are hiding it for us. Now let’s say you want to add some metrics and measure the execution time of the queries you are sending to the database, or let’s say you wanna log the queries. For this type of task, altering the behavior of the Connection object is the best candidate and it can be easily done by creating a custom DataSource object – which acts as a factory to create Connection objects but now it will create custom Connection objects with the additional logic. However, we are not discarding any of the existing functionality but we are going to extend it using the Proxy design pattern. I don’t want to go into the details of the pattern as there are plenty of good articles about it and it’s relatively simple to understand.
In this article, I’m going to use an existing library called datasource-proxy which has multiple features built-in like query logging, query metrics, slow query detection. Configuring this in a standard application is not complicated and can be done with a few lines of code:
DataSource dataSource = ProxyDataSourceBuilder.create(originalDataSource).logQueryBySlf4j(INFO).build();
where originalDataSource is the actual DataSource object which is creating the Connections.
Configuration issue with Spring Boot
Okay, now we know how to configure the proxy DataSource object. In a standard Spring application one could do the following way:
@Bean
public DataSource dataSource() {
DataSource originalDataSource = ...
DataSource dataSource = ProxyDataSourceBuilder.create(originalDataSource).logQueryBySlf4j(SLF4JLogLevel.INFO).build();
return dataSource;
}
Spring Boot is a really great tool as it’s easy to use, almost no configuration is necessary and everything is working out-of-the-box. There is no difference with the DataSource objects. Spring Boot is built on top of so called AutoConfigurations which are working when certain classes are on the classpath or certain type of beans are already defined, etc.
If you try to define a custom DataSource bean in your Spring Boot app, the AutoConfiguration will be aware that “oh there is a custom DataSource bean, I don’t want to override that” and it won’t do the automatic configuration. In this case, you have to manually configure a DataSource which is a pain in the ass when we have Spring Boot, right?
BeanPostProcessor for the help
BeanPostProcessors are a great things, you can read more about them here. Long story short, you can define classes implementing the BeanPostProcessor interface that have two methods. The method we care about is the postProcessAfterInitialization which will be called by the Spring container after the a bean is fully configured. Here we can simply check whether the bean instance is a DataSource and if yes, we can simply wire in our custom proxy DataSource object. After we have the class, we can simply register this class as a bean into the container.
@Component
public class DatasourceProxyBeanPostProcessor implements BeanPostProcessor {
@Override
public Object postProcessBeforeInitialization(final Object bean, final String beanName) throws BeansException {
return bean;
}
@Override
public Object postProcessAfterInitialization(final Object bean, final String beanName) throws BeansException {
if (bean instanceof DataSource) {
DataSource dataSourceBean = (DataSource) bean;
return ProxyDataSourceBuilder.create(dataSourceBean).logQueryBySlf4j(SLF4JLogLevel.INFO).build();
}
return bean;
}
}
Now, if you execute a simple test which does some database operation you can see the following log message:
Name:dataSource, Time:0, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["insert into persons (name, id) values (?, ?)"], Params:[(Arnold,1)]
This simple trick with the BeanPostProcessor can be easily used by any Spring application whether it’s Boot or not.
The source can be found on GitHub. Feel free to reach me out in case of questions in the comments or on Twitter.
UPDATE: So it seems that this approach is not that good on the long-run as it’s not working with Spring Boot 2.0.0.M3+.
After a bit of discussion with Andy Wilkinson and Dave Syer, I ended up using a CGLib proxy in order to keep the original bean types.
Now the DatasourceProxyBeanPostProcessor looks like this:
@Component
public class DatasourceProxyBeanPostProcessor implements BeanPostProcessor {
@Override
public Object postProcessBeforeInitialization(final Object bean, final String beanName) throws BeansException {
return bean;
}
@Override
public Object postProcessAfterInitialization(final Object bean, final String beanName) throws BeansException {
if (bean instanceof DataSource) {
ProxyFactory factory = new ProxyFactory(bean);
factory.setProxyTargetClass(true);
factory.addAdvice(new ProxyDataSourceInterceptor((DataSource) bean));
return factory.getProxy();
}
return bean;
}
private static class ProxyDataSourceInterceptor implements MethodInterceptor {
private final DataSource dataSource;
public ProxyDataSourceInterceptor(final DataSource dataSource) {
this.dataSource = ProxyDataSourceBuilder.create(dataSource).countQuery().logQueryBySlf4j(SLF4JLogLevel.INFO).build();
}
@Override
public Object invoke(final MethodInvocation invocation) throws Throwable {
Method proxyMethod = ReflectionUtils.findMethod(dataSource.getClass(), invocation.getMethod().getName());
if (proxyMethod != null) {
return proxyMethod.invoke(dataSource, invocation.getArguments());
}
return invocation.proceed();
}
}
}
The updated code can be found on GitHub. In case of questions, feel free to contact me.
Nice article
I assume there is some overhead when logging the queries using the proxy and that one would like to only see the metrics in pre-production environment or in very limited cases production?
From my understanding the bean post processor would be picked up by spring boot for every environment, when looking at the example.
Do you not think that a @Profile with @Configuration (for constructing the proxy data source) would work better for the specific environment that you are testing rather than the bean post processor?
You can always add the proper profile over the DatasourceProxyBeanPostProcessor instead of creating separate configuration for it – Cheers mate !:)
It definitely have an overhead, I haven’t measured it though.
If you want, you can create custom configurations for different environments but you can also add the @Profile annotation to the BeanPostProcessor to run only in the specified environments.
How would you configure multiple data source in this case?
Depends on what you want to achieve. Note that in the BeanPostProcessor, you have the bean names, thus you can differentiate between multiple datasources.
Spring-boot-autoconfiguration is working only till springboot 2.0.0.M2 but not from 2.0.0.M3, any alternative way to fix this?
You can put spring.jmx.enabled=false into your application properties as a hotfix. In the meantime, I’m gonna discuss this with the guys. 🙂
i test two way,its cannot work,I use spring boot which version 1.4.2,test method is
@Bean(name = “skyDataSource”)
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = (DruidDataSource) DataSourceBuilder.create().type(DruidDataSource.class).build();
// DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setInitialSize(2);
dataSource.setMaxActive(50);
dataSource.setMinIdle(2);
dataSource.setMaxWait(5000L);
DataSource source = ProxyDataSourceBuilder.create(dataSource).logQueryBySlf4j(SLF4JLogLevel.INFO).build();
return source;
}
i test two way,its cannot work,I use spring boot which version 1.4.2,test method is
@Bean(name = “skyDataSource”)
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = (DruidDataSource) DataSourceBuilder.create().type(DruidDataSource.class).build();
// DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setInitialSize(2);
dataSource.setMaxActive(50);
dataSource.setMinIdle(2);
dataSource.setMaxWait(5000L);
DataSource source = ProxyDataSourceBuilder.create(dataSource).logQueryBySlf4j(SLF4JLogLevel.INFO).build();
return source;
}
can tell me how use it?
I’d say your case is very specific, so I suggest you opening a stackoverflow question and link it here, so I can help.
@Override
public Object postProcessAfterInitialization(final Object bean, final String beanName) throws BeansException {
if (bean instanceof DataSource) {
ProxyFactory factory = new ProxyFactory(bean);
factory.setProxyTargetClass(true);
factory.addAdvice(new ProxyDataSourceInterceptor((DataSource) bean));
return factory.getProxy();
}
return bean;
}
private static class ProxyDataSourceInterceptor implements MethodInterceptor {
private final DataSource dataSource;
public ProxyDataSourceInterceptor(final DataSource dataSource) {
super();
this.dataSource = ProxyDataSourceBuilder.create(dataSource).countQuery().logQueryBySlf4j(SLF4JLogLevel.INFO).build();
}
@Override
public Object invoke(final MethodInvocation invocation) throws Throwable {
Method proxyMethod = ReflectionUtils.findMethod(dataSource.getClass(), invocation.getMethod().getName());
if (proxyMethod != null) {
return proxyMethod.invoke(dataSource, invocation.getArguments());
}
return invocation.proceed();
}
}
i use this method,but if (bean instanceof DataSource) is always false,it need any config ?
If this is the case, then you are definitely missing some configuration. As I said, please open a stackoverflow question, within a comment, it’s really hard to help.
I cannot open stackoverflow which is so slow,I change something make public Object invoke(final MethodInvocation invocation) throws Throwable {
Method proxyMethod = ReflectionUtils.findMethod(dataSource.getClass(), invocation.getMethod().getName());
if (proxyMethod != null) {
return proxyMethod.invoke(dataSource, invocation.getArguments());
}
return invocation.proceed();
}
will be processed,but log cannot print
i use this by
public ProxyDataSourceInterceptor(final DataSource dataSource) {
super();
this.dataSource = ProxyDataSourceBuilder.create(dataSource).logQueryBySlf4j(SLF4JLogLevel.INFO).build();
}
I solve it ,thanks
Interesting, looks like I’ve done the same (but not only for datasource-proxy) – https://github.com/gavlyukovskiy/spring-boot-data-source-decorator.
I wasn’t aware of changes done in Spring Boot 2.0.0.M3+, when I submitted a pull request (https://github.com/spring-projects/spring-boot/pull/8753) philwebb recommended to do post processing as late as possible, but seems this has been changed over time.
Can I log the database session id along with the actual query ? I am using spring boot, hibernate,jpa
Thanks for your update about Spring Boot 2.x. Worked for me!
Kenan, glad I was able to help!
Thank you for this. Just tried this with Spring Boot 2.2.6 and works fine!
Awesome, glad I could help!
Hi there! Nice post, thanks. The problem I recently faces is double logging. The query seems to be shown twice in console output. Found the possible solution here – https://github.com/ttddyy/datasource-proxy-examples/issues/15, but it doesn’t help me. Maybe smbdy has the same issue and solved it? Thanks
p.s. I’ve checked that hubernate makes one query. The problem is with datasource-proxy setting
Same issue, no idea yet.