p6spy allows to configure that database connection like this:
url=jdbc:p6spy:hsqldb:mem:data
driver=com.p6spy.engine.spy.P6SpyDriver

That way I can connect to a hsql database without changing the source just by changing the URL and the driver.
see http://p6spy.readthedocs.io/en/latest/index.html
It would be pretty cool if that would be possible using "datasource-proxy". Currently I cannot use it without changing the code of a hibernate application.

Currently in case of query timeout
QueryExecutionListener.afterQuery(ExecutionInfo execInfo, List queryInfoList);
is called, but execInfo.getElapsedTime() is 0.

I think it would make sense for elapsed time to be time that query took before timing out.

As of version 1.4.6 this seems like a simple change in StatementProxyLogic:306 try-catch-finally block:

1. Moving final long beforeTime = System.currentTimeMillis(); before try block.
2. Setting elapsedTime in catch block as well.
   As a consequence we would also have elapsedTime in case of other Exceptions, but I don't see a problem with that.

The methods in QueryExecutionListener don't allow to throw SQLException right now. Unfortunately almost all methods in the Statement (as part of ExecutionInfo) declare to throw SQLException when being invoked.

From what I can tell it should be safe to allow SQLException to be thrown there since all methods in Statement and PreparedStatement that invoke the listener allow to throw SQLException as well.

We are planning to integrate this library in our application with initial goal of enabling query logging at runtime in production environments. So whenever developers want to see what queries are executed, what is its execution time etc, we go to jconsole and set the log level to debug. Later turn off when done.
Right now the logger name is the class name "net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener" when slf4j logging listener is enabled. This is hard to remember especially when the support team is also involved.
For now, what we did is extended AbstractQueryLoggingListener and gave a nice logger name as "com.company-name.sql" so that it is easy to remember.
Wonder if the library could itself integrate this change. Best would be to accept the logger name from the client (in the listener class constructor?) and have a default name like "com.sql.logging".

What do you think?

Thanks !

write README.md for github

The parameter formatting has changed recently, so now it is displayed:

Params:[(1=1,2=foo),(1=2,2=bar)

Probably it is better to define an extension point, so the client can customize the formatting. For me it is much easier to read it like this:

Params: (1,foo),(2,bar)

I would drop both the square brackets and the indexes, which clutter the output.

provide an interceptor that can transform the query statement.

Hello!
Thx for this very helpful library.

I tried to implement some simple hanging queries detecting mechanism using listener, based on ConcurrentHashMap<ExecutionInfo, Long> containing currently executing queries where values are just query execution start time.
In beforeQuery method I register ExecutionInfo and query's execution start time and then in afterQuery I try to deregister it. But in afterQuery map is unable to find corresponding ExecutionInfo because it is new instance.

Code from net.ttddyy.dsproxy.proxy.PreparedStatementProxyLogic:

    listener.beforeQuery(**new ExecutionInfo(dataSourceName, this.ps, isBatchExecution, batchSize, method, args)**, queries);

    // Invoke method on original Statement.
    final ExecutionInfo execInfo = new ExecutionInfo(dataSourceName, this.ps, isBatchExecution, batchSize, method, args);

As you can see two fully identical ExecutionInfo objects are instantiated here. Maybe it would be better to create ExecutionInfo instance just once and then reuse it?

For a CallableStatement if the null parameter is passed then the value shown in the logs is negative. mostly "-5".

I'm looking for a little more documentation on JNDI usage. I would like to be able to use this in the java webapp but I can't really figure out how to use it. Can you provide more details?

Currently, logging the QueryCounter is hardcoded.
Convert it to injectable, so that user can modify the logging message format.

Related Classes:

• CommonsLogUtils
• CommonsQueryCountLoggingFilter
• CommonsQueryCountLoggingHandlerInterceptor
• CommonsQueryCountLoggingRequestListener
• SLF4JLogUtils
• SLF4JQueryCountLoggingFilter
• SLF4JQueryCountLoggingHandlerInterceptor
• SLF4JQueryCountLoggingRequestListener

Testing environment:

    Spring boot, Spring Data JPA, HikariCP, JDK 1.8.0_144;

Datasource proxy configuration:

    @Bean
    public DataSource dataSource(DataSourceProperties dataSourceProperties) {
        final DataSource originalDataSource =
            dataSourceProperties.initializeDataSourceBuilder().build();

        PrettyQueryEntryCreator creator = new PrettyQueryEntryCreator(applicationProperties);
        creator.setMultiline(true);

        SLF4JQueryLoggingListener listener = new SLF4JQueryLoggingListener();
        listener.setQueryLogEntryCreator(creator);
        listener.setLogLevel(SLF4JLogLevel.WARN);

        return ProxyDataSourceBuilder
            .create(originalDataSource)
            .countQuery()
            .listener(listener)
            .jdbcProxyFactory(new ResultSetProxyJdbcProxyFactory())
            .build();
    }

Call entry -> bankRepository.findAll().size()

    @Test
    @Transactional
    public void test() throws Exception {
        int size = bankRepository.findAll().size();
       //do something
          assertThat(....) 
       //do otherthing
    }

StackTrace

java.lang.UnsupportedOperationException: Method 'public native int java.lang.Object.hashCode()' is not supported by this proxy

	at net.ttddyy.dsproxy.proxy.ResultSetProxyLogic.invoke(ResultSetProxyLogic.java:92)
	at net.ttddyy.dsproxy.proxy.jdk.ResultSetInvocationHandler.invoke(ResultSetInvocationHandler.java:38)
	at com.sun.proxy.$Proxy229.hashCode(Unknown Source)
	at java.util.HashMap.hash(HashMap.java:338)
	at java.util.HashMap.put(HashMap.java:611)
	at java.util.HashSet.add(HashSet.java:219)
	at org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl.register(ResourceRegistryStandardImpl.java:203)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.postExtract(ResultSetReturnImpl.java:232)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:76)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2123)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1911)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
	at org.hibernate.loader.Loader.doQuery(Loader.java:932)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
	at org.hibernate.loader.Loader.doList(Loader.java:2615)
	at org.hibernate.loader.Loader.doList(Loader.java:2598)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
	at org.hibernate.loader.Loader.list(Loader.java:2425)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:370)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1481)
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
	at org.hibernate.query.Query.getResultList(Query.java:146)
	at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:329)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:74)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:504)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:489)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)

Hi, I'm writing spring-boot-starter for your library (and similar to your) to easily wrap all datasource beans in context into ProxyDataSource if datasource-proxy exists in classpath.

Currently I have implemented configuration of logging, query and slow-query listeners using Spring Boot's application.properties and ability to declare listeners beans that will be automatically registered, as well declaring custom ParameterTransformer and QueryTransformer is supported and will be used in all ProxyDataSoure instances.

I'm planing add support for your Query Metrics in Spring Boot Actuator, now it returns simple datasource metrics:

  "datasource.primary.active" : 3,
  "datasource.primary.usage" : 0.3

And if user have enabled queryCount I can use counters there, what do you think?

Also I'm looking for some advice what else for I can add integration or improve in general (maybe in documentation).
You can try it by adding datasource-proxy-spring-boot-starter to your application with latest version (now it's 1.1.0), or if you want I can create sample application.

Thanks.

We were thinking if this library could help us to get statistics on how many static statements vs prepared statements are executed by the software (like the query count statistics). As prepared statements give a good performance boost to the database server (hard vs soft parsing in oracle) it would be very helpful if a query execution listener could detect whether the underlying query is a static query or a prepared statement.

Our first thought was to implement the query listener and check if the getQueryArgsList() map is empty, but later came across some half cooked use cases which could turn this logic false like

select emp_name from employee where emp_id = 24 and emp_type = ?

Now this would be termed as a prepared statement but since it has the emp_id parameter value hard coded it is not completely a prepared statement. The database server would still have to do hard parsing for this.

I would like to start this as a discussion and get your experience to brainstorm if there is a better logic for this use case. Our goal is to let developers know that they should revise the queries being executed by their feature.

Thanks !

P.S - Looking at the query transformer interface, I am keen on knowing what are some of the real world use cases of it. Please share them.

In the Brave library, we retain core library support for Java 6 bytecode as we still have many applications that use older codebases. In order to do this, we avoid internal dependencies on new types, and we use tools like animal sniffer and retrolambda to help keep programming neat and safe meanwhile. We use techniques to allow use of new apis.. this mainly code internals that need to avoid Java 8 things: https://cwiki.apache.org/confluence/display/ZIPKIN/Java++Instrumentation+Techniques

If you are up to it, I would offer to help with the build work involved, as I've done this many times.

This https://github.com/ttddyy/datasource-proxy-examples/tree/master/spring-javaconfig-example
example is not working with spring-boot-1.5.1

I have a legacy library that is making a lot of insert statements and I would like to batch them. I don't have access to the code library and would like to eventually see if it would be doable to intercept regular prepare statement execution of executeUpdate method and transform it to something I could do batch inserts. I see in the doc that it is possible to transform a method call with the MethodExecutionListener. I don't know if it is something that is doable and easy to implement with datasource-proxy.

The Spring JDBC data source transaction manager causes the datasource-proxy to throw a NullPointerException when toString is called on the proxied connection.

DataSourceTransactionManager -> Fails here

logger.debug("Acquired Connection [" + newCon + "] for JDBC transaction");

Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is net.ttddyy.dsproxy.DataSourceProxyException: java.lang.NullPointerException
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:305)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:378)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:474)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:289)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy98.getLastJobExecution(Unknown Source)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:101)
	at com.trgrp.service.job.DefaultJobService.onRun(DefaultJobService.java:143)
	... 73 common frames omitted
Caused by: net.ttddyy.dsproxy.DataSourceProxyException: java.lang.NullPointerException
	at net.ttddyy.dsproxy.listener.lifecycle.JdbcLifecycleEventExecutionListener.methodCallback(JdbcLifecycleEventExecutionListener.java:61)
	at net.ttddyy.dsproxy.listener.lifecycle.JdbcLifecycleEventExecutionListener.beforeMethod(JdbcLifecycleEventExecutionListener.java:31)
	at net.ttddyy.dsproxy.listener.CompositeMethodListener.beforeMethod(CompositeMethodListener.java:18)
	at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:31)
	at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.invoke(ConnectionProxyLogic.java:47)
	at net.ttddyy.dsproxy.proxy.jdk.ConnectionInvocationHandler.invoke(ConnectionInvocationHandler.java:25)
	at com.sun.proxy.$Proxy96.toString(Unknown Source)
	at java.lang.String.valueOf(String.java:2994)
	at java.lang.StringBuilder.append(StringBuilder.java:131)
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:264)
	... 82 common frames omitted
Caused by: java.lang.NullPointerException: null
	at net.ttddyy.dsproxy.listener.lifecycle.JdbcLifecycleEventExecutionListener.methodCallback(JdbcLifecycleEventExecutionListener.java:57)
	... 91 common frames omitted

update these google code pages to link to github

• source page
• issues page

add an interceptor that can modify parameters

Log level TRAC should be TRACE

I'm trying to run your standard slow query logging thru slf4j but without any parameter values (might be sensitive values). I think the ParameterReplacer is supposed to solve for this scenario but can't see how it's supposed to be used, and there is only a NoOp implementation to work from?

add variable to ExecutionInfo

• Statement
• PreparedStatement
• CallableStatement

Also, as it to logging

public class ChainListener

    public void addListener(QueryExecutionListener listner) {
        this.listeners.add(listner);
    }

change to

    public void addListener(QueryExecutionListener listener) {
        this.listeners.add(listener);
    }

I think we discovered a major bug in RepeatableReadResultSet .

Let be a repeatable ResultSet 1->"foo".

Working sequence:

• resultSet.next()
• resultSet.getObject(1) returns "foo"
• resultSet.beforeFirst()
• resultSet.next()
• resultSet.getObject(1) returns "foo", everything is OK

Failing sequence:

• resultSet.next()
• resultSet.beforeFirst()
• resultSet.next()
• resultSet.getObject(1) returns null instead of "foo"

A PR has been opened.

package net.ttddyy.dsproxy.support.jndi;
String foramt = this.getContentFromReference(reference, "format");

Correct for

String format = this.getContentFromReference(reference, "format");

Does query time include the pool checkout time as well while determining slow queries?

I checked the code, it seems it will only calculate time spent in executeQuery, and I assume executeQuery will only be called after getting connection from the poll (since it is required to create the PreparedStatement).

Could you please confirm the behaviour ?

I have a Hibernate unit test you can find it here.

The test uses a JDBC batch size of and I'm inserting 5 entities and I get the following log:

DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener - Name: Time:0 Num:2 Query:{[insert into sequenceIdentifier (id) values (?)][1]} {[insert into sequenceIdentifier (id) values (?)][2]} 
DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener - Name: Time:0 Num:4 Query:{[insert into sequenceIdentifier (id) values (?)][1]} {[insert into sequenceIdentifier (id) values (?)][2]} {[insert into sequenceIdentifier (id) values (?)][3]} {[insert into sequenceIdentifier (id) values (?)][4]} 
DEBUG [main]: n.t.d.l.SLF4JQueryLoggingListener - Name: Time:0 Num:5 Query:{[insert into sequenceIdentifier (id) values (?)][1]} {[insert into sequenceIdentifier (id) values (?)][2]} {[insert into sequenceIdentifier (id) values (?)][3]} {[insert into sequenceIdentifier (id) values (?)][4]} {[insert into sequenceIdentifier (id) values (?)][5]} 

So instead of logging the current batch queries, all queries are logged, and some queries are duplicated.

This is because Hibernate calls:

prepareStatement.addBatch();
prepareStatement.addBatch();
prepareStatement.executeBatch();
prepareStatement.addBatch();
prepareStatement.addBatch();
prepareStatement.executeBatch();
prepareStatement.addBatch();
prepareStatement.executeBatch();
prepareStatement.clearBatch();

and PreparedStatementProxyLogic always logs this.batchQueries

for (BatchQueryHolder queryHolder : batchQueries) {
        queries.add(new QueryInfo(queryHolder.getQuery(), queryHolder.getArgs()));
}

I think this should be changed so that when the executeBatch logs the current batch then this.batchQueries.clear() should be called as well instead of doing it in clearBatch().

@bean(name = "skyDataSource")
@primary
public DataSource masterDataSource() {
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 resultDataSource = ProxyDataSourceBuilder.create(dataSource).name("MyDS")
.multiline().logQueryBySlf4j(SLF4JLogLevel.INFO).build();
return resultDataSource;
}
i use this way,but which cannot work,I use spring boot which version 1.4.2 ,datasource-proxy version is 1.4.2

add information whether it was batch exec or not
also add it to logging

adding any variant of logSlowQuery is preventing application to close.

How to reproduce?
Comment(disable line) below line in the sample you can see that application closes once query is printed but not when it is enabled.

https://github.com/rajadilipkolli/POC/blob/master/the-best-way-to-log-jdbc-statements/src/main/java/com/example/TheBestWayToLogJdbcStatementsApplication.java#L76

Hello,

Thank you for providing this library, and for the recent addition of the getGeneratedKeys() proxying.

I would like to retrieve the keys from statements that did not have RETURN_GENERATED_KEYS specified by the app code at creation. They use Statement statement = new Statement(query), not Statement statement = new Statement(query, Statement.RETURN_GENERATED_KEYS), as the calling code does not require keys. The proxy code, however, does need the keys.

Is it possible to add Statement.RETURN_GENERATED_KEYS to a Statement/PreparedStatement to make the generated keys available to datasource-proxy? The original calling code does not need to be given access. Is there a way to set this property in perhaps the beforeQuery method of QueryExecutionListener? I have seen references to Method and MethodArgs however I have not been able to understand if they apply here.

Thank you in advance.

Inside QueryExecutionListener.afterQuery, we have access to the ExecutionInfo.getResult(). Sometimes, this is a ResultSet. However, if you consume the ResultSet in the listener (e.g. to log the query results), then the real application usage of the ResultSet will fail because the ResultSet cannot be consumed more than once.

Some JDBC drivers implement scrollable result sets, so with those it might be possible to use ResultSet.beforeFirst(), but many JDBC drivers are not scrollable.

To solve this, I came up with a ResultSet proxy that caches the results of methods such as ResultSet.getString(int columnIndex) so that the ResultSet can be consumed more than once.

See here for the implementation and here is a main method that demonstrates how it can be used.

Would you be interested in merging this proxy into the datasource-proxy project, as an alternative implementation of JdbcProxyFactory? I will be able to write some decent test coverage for it before then of course, just seeing if you are interested first.

metric for batch size

For example: if we want the proxy object implement oracle.ucp.jdbc.PoolDataSource, I do not see a way to pass in additional interfaces to implement so that we can cast to PoolDataSource instead of sql DataSource.

Currently, parameters are logged as:
Params:[(1=1,2=foo),(1=2,2=bar)]

for PreparedStatement, it is easier to read such like:
Params: (1,foo),(2,bar)

Also, need to handle setNull and registerOutParameter nicely.

There are several places in the code where elapsed time is measured using System.currentTimeMillis(). This can be dangerous because currentTimeMillis() is based on the system clock, which can change unpredictably. Elapsed time should be measured using System.nanoTime() instead, which is not based on the system clock and therefore does not suffer from that problem.

Hi!

I'm running Spring Boot 1.5.1 with Spring Data Jpa 1.11.0 + Hibernate 5.0.11 + Datasource-Proxy 1.4.9 + Datasource-assert 1.0

I have a JPA entity with @GeneratedValue(strategy = GenerationType.IDENTITY) which pushes Hibernate to call getGeneratedKeys in GetGeneratedKeysDelegate.executeAndExtract(PreparedStatement, SessionImplementor) which is proxied eventually by StatementProxyLogic.performQueryExecutionListener(Method, Object[]) which has a finally block where the first line of it calls queryListener.afterQuery(execInfo, queries); and the problem begins. There is a default QueryExecutionFactoryListener (from ProxyTestDataSource) that doesn't check for emptiness of queryInfoList parameter passed to QueryExecutionFactoryListener.afterQuery(ExecutionInfo, List<QueryInfo>) and because of this queryInfoList isn't filled in StatementProxyLogic.performQueryExecutionListener(Method, Object[]) while handling getGeneratedKeys method call it fails subsequnetly when some of the create* methods of the QueryExecutionFactoryListener class is called and none of them're checking that the list could be empty, trying to get 0th element

I've made a liittle workaround for me to work, just by inheriting from QueryExecutionFactoryListener and overriding afterQuery like this:

List<QueryExecutionListener> listeners = proxyTestDataSource.getProxyConfig().getQueryListener().getListeners();
listeners.clear();
listeners.add(new QueryExecutionFactoryListener() {
            @Override
            public void afterQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
                if (!queryInfoList.isEmpty()) {
                    super.afterQuery(execInfo, queryInfoList);
                }
            }
        });

BTW, logging is also generating empty query as following:

Name:, Connection:8, Time:0, Success:True
Type:Prepared, Batch:False, QuerySize:0, BatchSize:0
Query:[]
Params:[]

Maybe, I'm doing something wrong but I didn't find any solution to this in the internet

It appears that while using the SlowQueryListener as shown below, the Time property in the logging is always 0. But the time appears to work correctly when using the QueryListener (without threshold specified, logs everything). I've run it a few times to ensure the query are not too quick and are genuinely being shown as 0. I feel like i'm missing something basic..

I'm testing against both H2 and MySQL via Hikari, JPA+Hibernate is actually executing the queries, full range of SELECTs, INSERTs, UPDATEs etc if that makes any difference. Using the latest versions of everything on Java 8 b121.

return ProxyDataSourceBuilder
                .create(dataSource)
                .logQueryBySlf4j(SLF4JLogLevel.INFO)
                .build();


2017-03-17 11:14:29:174 INFO  QueryLoggingListener - 02cbe638-eb5e-498c-b5c1-9daf154b0601 - Name:, Time:1, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["..."], Params:[(...)]
2017-03-17 11:14:29:191 INFO  QueryLoggingListener - 02cbe638-eb5e-498c-b5c1-9daf154b0601 - Name:, Time:2, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["..."], Params:[(...)]
2017-03-17 11:14:29:207 INFO  QueryLoggingListener - 02cbe638-eb5e-498c-b5c1-9daf154b0601 - Name:, Time:2, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["..."], Params:[(...)]


return ProxyDataSourceBuilder
                .create(dataSource)
                .logSlowQueryBySlf4j(0, TimeUnit.MILLISECONDS)
                .build();


2017-03-17 11:17:14:620 WARN  F4JSlowQueryListener -                                      - Name:, Time:0, Success:False, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["..."], Params:[(...)]
2017-03-17 11:17:14:639 WARN  F4JSlowQueryListener -                                      - Name:, Time:0, Success:False, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["..."], Params:[(...)]
2017-03-17 11:17:14:654 WARN  F4JSlowQueryListener -                                      - Name:, Time:0, Success:False, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:[""], Params:[(...)]

logger to write out to System.out

Currently, JdbcProxyFactory is a static util class using jdk proxies.
convert this to use strategy pattern, so that user can inject different means to create proxies.

Please add method to get original datasource.

https://code.google.com/p/datasource-proxy/

Thank you for this great library !

I am building a custom QueryExecutionListener that will trace all INSERT, UPDATE, DELETE queries in a central database audit table. I need to generate one audit row per atomic statement. This means batch statements of 10 will trigger the creation of 10 audit logs.

Everything is working except for the INSERT queries. For them, I want to be able to trace the database query WITH the generated keys.

I know that my listener is currently triggered twice:

1. After executeUpdate
2. After getGeneratedKeys

But my listener can't link the two events, and I have no guarantee that the caller will call getGeneratedKeys after each executeUpdate.

In step 1, if my listener opens the Statement::getGeneratedKeys ResultSet before the caller, the caller throws an exception later when the caller tries to read it a second time.

After much digging, the only solution I see is to pass the getGeneratedKeys resultSet as a RepeatableReadResultSet in ExecutionInfo at step 1 in PreparedStatementProxyLogic and StatementProxyLogic.

Oversimplified change for PreparedStatementProxyLogic:

final ExecutionInfo execInfo = new ExecutionInfo(this.connectionInfo, this.ps, isBatchExecution, batchSize, method, args);

        queryListener.beforeQuery(execInfo, queries);

        // Invoke method on original Statement.
        try {
            final long beforeTime = System.currentTimeMillis();

            Object retVal = method.invoke(ps, args);

            final long afterTime = System.currentTimeMillis();

            // execInfo.setResult will have proxied ResultSet if enabled
            if (METHODS_TO_RETURN_RESULTSET.contains(methodName)) {
                retVal = proxyFactory.createResultSet((ResultSet) retVal, this.connectionInfo, this.proxyConfig);
            }
            // START OF NEW CODE
            // Would set a repeatable resultset
        execInfo.setGeneratedKeys(proxyFactory.createGeneratedKeysResultSet(ps.getGeneratedKeys()));
            // END OF NEW CODE
            execInfo.setResult(retVal);
            execInfo.setElapsedTime(afterTime - beforeTime);
            execInfo.setSuccess(true);

            return retVal;
        } catch (InvocationTargetException ex) {
            execInfo.setThrowable(ex.getTargetException());
            execInfo.setSuccess(false);
            throw ex.getTargetException();
        } finally {
            queryListener.afterQuery(execInfo, queries);
        }

What do you think of that?

The slow query logger appears to be working for me; it triggers once the query passes the configured threshold.

Is there a mechanism by which we can see the elapsed time of just the slow queries?

One question is if we unwrap the proxy to get the target datasource will logging still work? because we will be using target object further to create connection, execute query etc.

We also have several other cases like OracleCallableStatement/OraclePreparedStatement interfaces etc which when casted to will throws similar errors. Is there a way to get target statement object with ttddyy impl?

Hello,
Is it possible to change the signature of getId in ConnectionIdManager
from
long getId(Connection connection);
to
String getId(Connection connection);
This will allow for better tracking in some environments while keeping the current behavior for others. Example for such better identification would be the combination of Oracle's SID and serial#

I'm using version 1.4.3 of datasource-proxy with Spring 4.3.8 with following configuration:

  <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

<bean id="proxyDataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
	<property name="dataSource" ref="dataSource"/>
  	<property name="listener">
		<bean class="net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener">
	    	<property name="logLevel" value="INFO"/>
	    </bean>
	</property>
</bean>

<bean id="entityManagerFactoryBean" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">		
	<property name="dataSource" ref="proxyDataSource" />		
</bean>

When I start the server, it encounters ClassNotfoundexception for SLF4JQueryLoggingListener. And, I don't see it in the listener package. What's wrong here ?