jooq dsl without a connection?

[ericjs]

Is there any way to construct the query with jooq without first having
the connection? That is, instead of building the DSL from the Factory
(which can only be constructed from a Connection so far as I can
tell), use the same DSL with needing the connection and not provide
the connection until the query execution / fetch / etc.?

My reason for wanting to do so has to do with constructing a utility
class to encapsulate all the boilerplate of creating and cleaning up
the connection and exception handling, into one place across many jooq
operations, which is made more awkward by needing the connection
before you can construct the query. If you want, I can provide the
long-winded details and show you the code.

Cheers,
Eric

[Lukas Eder]

> Is there any way to construct the query with jooq without first having
> the connection? That is, instead of building the DSL from the Factory
> (which can only be constructed from a Connection so far as I can
> tell), use the same DSL with needing the connection and not provide
> the connection until the query execution / fetch / etc.?

The connection is only needed at execute time, not at render time. So
you can safely pass null as a connection if you don't need it at
construct time.

You can always set the connection on the factory later:
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#setConnection(java.sql.Connection)

Note, jOOQ 2.3.0 will also ship with connection-less constructors:
https://sourceforge.net/apps/trac/jooq/ticket/1303

> My reason for wanting to do so has to do with constructing a utility
> class to encapsulate all the boilerplate of creating and cleaning up
> the connection and exception handling, into one place across many jooq
> operations, which is made more awkward by needing the connection
> before you can construct the query. If you want, I can provide the
> long-winded details and show you the code.

Yes, why not. It might be interesting for the record.

Cheers
Lukas

[Lukas Eder]

... note, you may also want to have a look at the FactoryProxy, which
handles the Connection through a DataSource. This was contributed by
Sergey Epik to better integrate jOOQ with Spring

[ericjs]

Excellent!

And where may I find FactoryProxy? I don't see it in the 2.2.2
javadoc. A quick search on this group turned up mention of a jooq-
spring module which I did not spot when I downloaded everything. And
you FAQ refers only to the StackOverfow discussion on spring so I did
not realize further development of spring integration had occurred.

[Sergey Epik]

Hello,

Source code of FactoryProxy you can find here:
https://github.com/jOOQ/jOOQ/blob/0cdbbb9a116faa8624edb8ab5bed5b12cd80365d/jOOQ/src/main/java/org/jooq/impl/FactoryProxy.java

Here is configuration example:

<bean id="jooQFactoryProxy" class="org.jooq.impl.FactoryProxy">

        <property name="dataSource" ref="transactionAwareDataSource"/>

        <property name="settings">

            <bean class="org.jooq.conf.Settings">

                <property name="renderMapping">

                    <bean class="org.jooq.conf.RenderMapping">

                        <property name="schemata">

                            <list>

                                <bean class="org.jooq.conf.MappedSchema">

                                    <property name="input" value="AA"/>

                                    <property name="output" value="${aa.db.schema}"/>

                                </bean>

                            </list>

                        </property>

                    </bean>

                </property>

            </bean>

        </property>

        <property name="dialect">

            <value type="org.jooq.SQLDialect">ORACLE</value>

        </property>

</bean>

--

Sergey

[ericjs]

Thanks, Sergey! I see, pretty much a pure proxy, allowing the
datasource to be injected in just a single place. Nice!

I think I see a problem though. I don't see anything ever closing the
connections being created. Am I missing something?

[Sergey Epik]

The magic happens when you use transactionAwareDataSource together with transaction interceptor. It cares about closing.

For example:

   <tx:annotation-driven transaction-manager="transactionManager"/>

    <jdbc:embedded-database id="dataSource" type="HSQL">
        <jdbc:script location="classpath:db-init.sql"/>
    </jdbc:embedded-database>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="transactionAwareDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
        <constructor-arg ref="dataSource"/>
    </bean>

[Lukas Eder]

I'm sorry for the confusion. The FactoryProxy was added for jOOQ 2.3.0
only. I see that Sergey has already pointed you to the right place...

Cheers
Lukas

[Lukas Eder]

> The magic happens when you use transactionAwareDataSource together with
> transaction interceptor. It cares about closing.

As far as I understand it, this works well with Spring, and might also
work with J2EE, although you may get a warning in Weblogic, for
instance. Maybe there is potential for enhancing the FactoryProxy, in
order to (optionally) handle the connection's lifecycle, in case no
such magic is available?

Maybe the FactoryProxy should contain a flag to indicate whether the
delegate's connection should be closed explicitly, after use. An
example delegate method:

------------------------------------------------------------------------------
@Override
public final <R extends Record> Result<R> fetch(Table<R> table) {
Factory delegate = getDelegate();
try {
return delegate.fetch(table);
}
finally {
close(delegate);
}
}

private void close(Factory delegate) {
if (closeDelegate && delegate != null) {
try {
delegate.getConnection().close();
}
catch (SQLException e) {
// Handle this, here
}
}
}
------------------------------------------------------------------------------

Please, share your thoughts

Cheers
Lukas

[Lukas Eder]

> Source code of FactoryProxy you can find here:
> https://github.com/jOOQ/jOOQ/blob/0cdbbb9a116faa8624edb8ab5bed5b12cd80365d/jOOQ/src/main/java/org/jooq/impl/FactoryProxy.java
>

> Here is configuration example: [...]

Thank you for sharing these things, Sergey. I will add a section in
the manual for jOOQ 2.3.0 showing how to use your FactoryProxy with
Spring

Cheers
Lukas

[ericjs]

A few comments:

* The applicability of getting your connection from a DataSource goes
beyond even Java EE, it's been the recommended way to get a Connection
since at least JDK 4.

* It is always better to have the class which creates a resource
manage its cleanup rather than scattering that between disconnected
classes. To not close the connection in this class creates a hidden
dependency to on something else far away.

* In the Spring DataSourceTransactionManager case, closing the
connection will not hurt. It will not really close the underlying
connection. The next connection you get from the DataSource within the
same Spring-managed transaction boundary will be another proxy to the
same connection, using the same underlying real connection and the
same actual database transaction. I assume this will be the case with
other transaction managers as well, and I suspect this is intentional
so that code using the DataSource is abstracted from the actual
Connection details and does not have to know whether it is in a
situation where it should close or not close the connection. (This is
similar to the situation with connection pools...you are still
supposed to close the connection...however this does not close the
real connection, it just make it eligible to be returned to the pool).
I can't find an authoritative source for this, but I think the best
practice with DataSources and Connections is ALWAYS close the
Connection when you are done with it, regardless of what kind of
infrastructure you have going on at a higher level--it is the
infrastructure's responsibility to ensure that your closing of the
connection does not interfere with what it is doing.

Cheers,

Eric

[ericjs]

To come back to my earlier offer for long-winded explanation and code,
here is the utility class I was writing that prompted my question. I
wasn't trying to create a proxy, and it hadn't dawned on me the what I
was writing might easily become one, until I saw Sergey's class. I
only added a couple of fetch/execute variations for what I was using
so far, and expected that to change, as I evaluated how I really
wanted to do that. It is hardly finished, but perhaps some of the
ideas in it could be adapted to Sergey's class to handle the
awkwardness of connection closing.

My motivation in writing it was that I was acculating a number of
methods in some "service classes" which were doing JOOQ queries and I
realized they had a lot of redundant boilerplate, and I wanted to
encapsulate that somewhere. In particular the code to get and close
connections, catch exceptions, and rethrow those as domain-specific
exceptions. (That is what the "ExceptionFactory" is about...this may
not be the right term for it...and if you don't provide one it just
throws RuntimeExceptions wrapping the underlying exception).

Anyway, FWIW, here it is:

---------------------------------------------------

package com.wrycan.db.util;


import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SelectFinalStep;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.Factory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class JooqExecutor {

private final ExceptionFactory<? extends RuntimeException>
exceptionFactory;
private final DataSource dataSource;

private final Logger logger =
LoggerFactory.getLogger(JooqExecutor.class);

public JooqExecutor(DataSource dataSource, ExceptionFactory<? extends
RuntimeException> exceptionFactory) {
this.dataSource = dataSource;
this.exceptionFactory = exceptionFactory;
logger.info("JooqExecutor created");
}

public JooqExecutor(DataSource dataSource) {
this.dataSource = dataSource;
this.exceptionFactory = new DefaultExceptionFactory();
logger.info("JooqExecutor created");
}


private abstract class Command <T> {
protected abstract T execute(SelectFinalStep select) throws
DataAccessException;
}

public final Record doFetchOne(Factory jooqFactory, SelectFinalStep
select) throws RuntimeException {

return executeCommand(jooqFactory, select, new Command<Record>() {
@Override
protected Record execute(SelectFinalStep select) {
return select.fetchOne();
}
});
}

public final Result<Record> doFetch(Factory jooqFactory,
SelectFinalStep select) throws RuntimeException {

return executeCommand(jooqFactory, select, new
Command<Result<Record>>() {
@Override
protected Result<Record> execute(SelectFinalStep select) {
return select.fetch();
}
});
}

public final Integer doExecute(Factory jooqFactory, SelectFinalStep
select) throws RuntimeException {

return executeCommand(jooqFactory, select, new Command<Integer>() {
@Override
protected Integer execute(SelectFinalStep select) {
return select.execute();
}
});
}

protected final <T> T executeCommand(Factory jooqFactory,
SelectFinalStep select, Command<T> doer) throws RuntimeException {

Connection conn = null;
try {
conn = dataSource.getConnection();

jooqFactory.setConnection(conn);

return doer.execute(select);

} catch (DataAccessException e) {
exceptionFactory.throwException("Error executing backend query" ,
e);

} catch (SQLException e) {
exceptionFactory.throwException("Error connecting backend query");
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
exceptionFactory.throwException("Error closing backend
connection");
}
}
}

exceptionFactory.throwException("Impossible exception handling
failure");
return null;
}


public static interface ExceptionFactory<E extends RuntimeException>
{
public void throwException() throws E;
public void throwException(String msg) throws E;
public void throwException(Exception e) throws E;
public void throwException(String msg, Exception e) throws E;
}

private static final class DefaultExceptionFactory implements
ExceptionFactory<RuntimeException> {
@Override
public final void throwException(String msg) throws
RuntimeException{
throw new RuntimeException(msg);
}

@Override
public final void throwException() throws RuntimeException {
throw new RuntimeException();
}

@Override
public final void throwException(Exception e) throws
RuntimeException {
throw new RuntimeException(e);
}

@Override
public final void throwException(String msg, Exception e) throws
RuntimeException {
throw new RuntimeException(msg, e);
}
}

}

[ericjs]

Before I got the answer that I didn't need a connection to instantiate
the JOOQ Factory, I had resorted to a modified version of this with a
seperate JooqExecutorFactory handling the connection getting and
closing, and providng a JooqExecutor used execute the DSL. This seemed
more awkward to me and created more boilerplate of own boilerplate,
and I went back to the single class approach once I knew I didn't have
to have the connection up front. However thinking about it now, there
may be value in this second option for cases where you want to execute
several commands through jooq, which using the single-class version I
pasted above, would get and close a connection for each operation.

[Lukas Eder]

Eric,

> * It is always better to have the class which creates a resource
> manage its cleanup rather than scattering that between disconnected
> classes. To not close the connection in this class creates a hidden
> dependency to on something else far away.

Yes, I agree with that. Letting some dependency (that might be
configured correctly) do some magic under jOOQ's hood doesn't seem
right to me either. This is OK in some contexts, but the default for
jOOQ should be to explicitly close any connection that was actively
obtained from a DataSource.

Without DataSources (i.e. the regular jOOQ Factory), this can be
safely ignored, as it will be up to client code to handle.

> [...]

> I can't find an authoritative source for this, but I think the best
> practice with DataSources and Connections is ALWAYS close the
> Connection when you are done with it, regardless of what kind of
> infrastructure you have going on at a higher level--it is the
> infrastructure's responsibility to ensure that your closing of the
> connection does not interfere with what it is doing.

That is how I understand it, too. At least when the connection
originates from a connection pool or from a distributed transaction -
i.e. from a DataSource. If the connection is obtained from a
DriverManager, then we're likely to be in some test or batch mode,
where you're not going to use a DataSource anyway.

Cheers
Lukas

[Lukas Eder]

> [...] It is hardly finished, but perhaps some of the

> ideas in it could be adapted to Sergey's class to handle the
> awkwardness of connection closing.

This type of solution works well for methods that immediately execute
a query. For construction, a Connection is not needed and can be
supplied later using various means. If, however, the connection is
supplied, then it must be closed in various places, scattered across
the internal API. Think of creating an org.jooq.Cursor using
fetchLazy(), and then slowly iterating to the end of the cursor.
Similar issues are caused by fetchLater(), which execute
asynchronously.

While at first, adding many ResultQuery.fetchXXX() methods seemed like
a very convenient API, it now leads to many questions. The real
problem is probably the fact that query construction and query
execution both originate in the same Factory object. To thoroughly
separate those concerns, I have been planning to change the jOOQ
architecture towards the following facts for jOOQ 3.0 (due in late
2012):

1. The Factory is only used for Query and QueryPart construction. All
methods are static (including select(), insertInto(), etc), Factory
has no state.
2. Query objects can no longer execute themselves. They are no longer
"attached".
3. An "Executor" type is created. It inherits today's Factory's state
(Connection/DataSource, SQLDialect, Settings). It initiates query
rendering and binding contexts before executing any Query. It will
also inherit all sorts of fetch methods, which will no longer be on
the query object itself.

(the above is now tracked as https://sourceforge.net/apps/trac/jooq/ticket/1372)

This will lead to the following client code:

Result<?> r1 = executor.fetch(select(A, B).from(C));
int r2 = executor.execute(insertInto(C).values("A", "B"));

This design will resolve quite a few thread-safety issues as well.
Once constructed, the same Query object could be executed by several
threads / executors at the same time. Eric's solution involving
commands and custom exception factories will become applicable to the
Executor easily. Also, an Executor can base its operations on a JDBC
Connection or on a JDBC DataSource.

In the short run, however, I do think that Eric's ideas of the
Executor can be applied to Sergey's FactoryProxy. As a matter of fact,
I prefer the term "Executor" over "Proxy", thinking about long-term
plans.

> My motivation in writing it was that I was acculating a number of
> methods in some "service classes" which were doing JOOQ queries and I
> realized they had a lot of redundant boilerplate, and I wanted to
> encapsulate that somewhere. In particular the code to get and close
> connections, catch exceptions, and rethrow those as domain-specific
> exceptions. (That is what the "ExceptionFactory" is about...this may
> not be the right term for it...and if you don't provide one it just
> throws RuntimeExceptions wrapping the underlying exception).

Sergey also had the idea of writing something similar to your
ExceptionFactory. Those ideas were discussed in this thread:
https://groups.google.com/d/topic/jooq-user/v3PJRm5gs2E/discussion

I very much like the idea of a pluggable ExceptionHandler with a
default implementation.

[Lukas Eder]

> However thinking about it now, there
> may be value in this second option for cases where you want to execute
> several commands through jooq, which using the single-class version I
> pasted above, would get and close a connection for each operation.

Could you show an example of this?

[Eric Schwarzenbach]

The future changes you describe make a lot of sence. I've played around with my utility classes a little more since my last post (I realized the silliness of my ExceptionFactory

throwException methods and changed them to getException methods) and combined the ability to do single-execute-and-cleanup methods into my 2-class approach, to have the best of both worlds.

I mispoke in saying that the factory did the connection getting and closing...it is actually the executor class. Because there are now multiple files its awkward to paste in so I'll attach a zip. A quick example of its use though:

 

 

public Collection<Something> getSomething() throws ServiceException {

    JooqExecutorF jooqExecutor = jooqExecutorFactory.getExecutor();

    SelectLimitStep query = jooqExecutor.getJooqFactory().select(SOMETHING.SOMETHING)...

    // etc. etc. lots of DSL query building here

    Result<Record> result = jooqExecutor.doFetch(query);

    // do something with result; more statements could be executed

    jooqExecutor.cleanUp();

    return something;

}

If the code calls doFetch1Shot(query), it can omit the cleanUp() call. Also this code assumes a

JooqExecutorFactory has already been created (and perhaps dependency-injected) giving it a DataSource and ExceptionFactory that throws ServiceExceptions

At the moment I have 2 sets of methods for execution methods, one that doesn't clean itself up right away, and one that that does (...1Shot). This is rather ugly, but it is a work in progress.

[Eric Schwarzenbach]

BTW, what is the state of JOOQ 2.3? Can we download that code from github to get a taste if where you are going? Do you / will you have alphas and betas that we can try out?

[Lukas Eder]

> BTW, what is the state of JOOQ 2.3? Can we download that code from github to
> get a taste if where you are going?

Yes, GitHub is the latest version:
https://github.com/jOOQ/jOOQ

There are also more stable versions available from the Sonatype Maven
repositories:
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

There is currently no automatic build process running

> Do you / will you have alphas and betas that we can try out?

No. I'm currently trying out to release several parallel minor release
streams (2.0.x, 2.1.x, 2.2.x). As of now, I don't see a huge demand
for those on SourceForge, so I think the infrequent SNAPSHOT releases
will do for now...

[Eric Schwarzenbach]

Here is a nicer version where the factory offers getExecutor() for an executor you should call cleanup on, and getSelfCleaningExecutor() for one shot executors that you don't. I know you were just looking for the gist, but that 1Shot business was too embarrasing to leave alone...

[Lukas Eder]

Hello,

Thanks for these contributions. I can see where you're trying to go.
When I have some time, I will work these first steps into a new jOOQ
3.0 branch on GitHub, where the community can provide pull requests to
contribute further ideas. Nevertheless, I think we should not
overcomplicate things before implementing a "final" version for jOOQ
3.0. Before these ideas are stabilising, anything contributed can only
be published as "experimental" on the jOOQ 2.x version stream

Cheers
Lukas

[Lukas Eder]

Hello Eric, community

Following up on recent requests by Eric (and some time ago by Sergey),
I have implemented a custom Exception handler in the ExecuteListener
interface. I do not believe that this should be part of the Factory,
or the Factory proxy, as exception handling is not necessarily
specific to the way we connect to the database. Also, if the Exception
mapping is taken out of the current FactoryProxy / Executor
discussion, things will greatly simplify.

Your custom ExecuteListener can receive exception notification events
through ExecuteListener.exception(ExecuteContext ctx). The
ExecuteContext then contains the DataAccessException about to be
thrown, as well as the underlying SQLException. You can change that
exception to something else and let jOOQ throw your own custom
RuntimeException.

For more details about the ExecuteListener, see the manual:
http://www.jooq.org/manual/ADVANCED/ExecuteListener/

and Javadoc sections:
http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html

The Trac ticket:
https://sourceforge.net/apps/trac/jooq/ticket/910

This is now committed on GitHub and will be included in jOOQ 2.3.0

Cheers
Lukas