Hello,
2013/9/6 Eric Schwarzenbach <[email protected]>
> I'm trying to use JOOQ to dynamically create some statements once and
> store them, and then as needed attach them to a connection and bind
> parameters to them and execute them. In my initial development this seems
> to be working, and JOOQ has been a great help. At first I was unsure JOOQ
> was going to give me much value in this particular case, as the table and
> field names to be used were coming in dynamically and so I was not using
> the generated classes. (It may be a slightly odd use case, I'll spare you
> the details!) But then I ran into datatype issues in binding values and
> formatting result data and found that introspecting the generated classes
> to get datatypes solved my problems.
>
> Making this work so far has been all well and good, however, in
> refactoring my proof-of-concept code to really separate the storing and
> usage of Queries, it occurs to me that seems to be no way to detach queries
> when I am done with them. I'm not sure this is a pragmatic concern, perhaps
> it is just aesthetic. I kind of expect the semantics to be consistent and
> leaving a Query hanging around still attached between uses just seems wrong.
>
Your concern is not "just aesthetic". In your use case, you should probably
detach queries again. This is illustrated in
DefaultDSLContext.fetch(ResultQuery):
@Override
public <R extends Record> Result<R> fetch(ResultQuery<R> query) {
final Configuration previous = Utils.getConfiguration(query);
try {
query.attach(configuration);
return query.fetch();
}
finally {
query.attach(previous);
}
}
And I've realized a pragmatic concern that sort of relates, but is bigger
> than the attachment issue. What about thread safety? Conceptually I would
> think I ought to be able to use a constructed representation of a sql
> statement in multiple threads with different connections simultaneously,
> but since bind and attach are state-changing operations on the query,
> surely this ins't the case and I need to rethink by design around saving
> queries. I suppose I can render the SQL and save that and then instantiate
> a new Query every time I want to use it. I wonder about the performance
> cost of doing this, but even that aside this seems...ugly.
>
Yes, your analyses are correct. And yes, there is some ugliness inherited
from pre-jOOQ 3.0 days. The relevant discussion can be seen here:
https://groups.google.com/d/msg/jooq-user/Y_tfF_eaxKM/jINIU3RYmiYJ
Your criticism was first raised by Christopher Deckers, the developer of
the jOOQ Console. Essentially, there are two models / modes of operation
with jOOQ:
1. The "attached" mode of operation, where queries can execute themselves.
2. The "thread-safe", "less stateful" mode of operation, where queries are
executed by an external entity.
The advantage of the first model is clearly its fluency, although some
developers have already indicated that .execute() and .fetch() are easy to
forget.
The advantage of the second model is clean separation of query building
from query execution.
One open question remains. "Initial" bind values are added to a query in a
fluent manner, although this could be circumvented by using named
parameters. Another way of operation that I'm aware of is to create a pool
of reusable Query objects in order to make them "thread-safe" by
guaranteeing that a query can never be obtained from the pool more than
once, at the same time. However, before implementing such a thing, I guess
a benchmark to measure Query construction overhead is worth the trouble.
I never disagreed with Christopher on how the current API is and how it
*should* be. But the discussion was too late for jOOQ 3.0, so relevant
changes in this area are scheduled for jOOQ 4.0, when I'm also hoping to
cleanly separate DSL and "Model" API, allowing for generating custom DSL
APIs. Another discussion initiated by Christopher:
https://groups.google.com/d/msg/jooq-user/mLcxnwvglf0/d_K2SCRWRaAJ
I guess that brings me not much to a question as a suggestion that perhaps
> JOOQ API's separation of SQL statement construction and execution is
> incomplete and could (should, even) be taken a step further.
>
Indeed, it is incomplete, and it should be taken a step further.
> Or am I looking at some of the parts of the API in the wrong way
> conceptually? (Conceptually this is how I'm looking at it: I see SQL
> building objects...or objects representing built-SQL, do not need to be and
> ought not have any state related to execution-specific things like
> connection attachment or parameter binding and as such inherently ought to
> be able to be thread-safe with regard to execution, though perhaps not with
> regard to SQL building which could change state approrpiate for them to
> carry, i.e. state representing what SQL they equate to.)
>
I agree mostly with connection attachment being execution-specific. I have
not yet deeply thought about parameter binding being execution-specific. In
particular, you can dynamically choose to inline all / some parameters, in
case of which they probably need to be present from the beginning. I guess
there are different use-cases for using jOOQ. I'm very open to discussing
this, though!
Note, in JDBC, a parameter is just a "?" in the middle of a SQL string,
with no type information attached. jOOQ's QueryPart model holds an
org.jooq.DataType reference for every parameter
> Is there some different way of doing this that I am missing? Such as if
> there were a means of cloning a Query...?
>
Currently, there is no way of cloning a Query.
Cheers
Lukas
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
