No Query.detach()? Thread-safety of Query objects?

[Eric Schwarzenbach]

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.

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.

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. 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.)

Is there some different way of doing this that I am missing? Such as if there were a means of cloning a Query...?

[Eric Schwarzenbach]

After writing all that, I think I've spotted the answer to both my questions. I hadn't realized that bind() returns another ResultQuery. In my proof-of-concept code I was attaching and binding my DSL-constructed query object and executing that directly. Now I see that I can get a new ResultQuery from the bind, and attach and execute that, leaving the original query object unattached (though not unbound it seems...which seems a little wonky to me). The new ResultQuery will go out of scope which I suppose is a way of "detaching" it. From running in a debugger it looks to me like this new ResultQuery is indeed a separate object from the original query object and so I assume I should have no thread-safety concerns if always create a new ResultQuery with bind and use that. Is that all correct?

Is bind() essentially doing query object "cloning"?

I still wonder if it wouldn't be nicer to have some API distinction between stateful statement building objects and immutable objects obtainable from them representing sql statements (or parts) that you have built. Perhaps this is overly idealistic to expect this in conjunction with a fluent DSL style API.

[Lukas Eder]

Hello,

2013/9/6 Eric Schwarzenbach <ericjs...@gmail.com>

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 

[Lukas Eder]

Hello,

2013/9/6 Eric Schwarzenbach <ericjs...@gmail.com>

After writing all that, I think I've spotted the answer to both my questions. I hadn't realized that bind() returns another ResultQuery. In my proof-of-concept code I was attaching and binding my DSL-constructed query object and executing that directly. Now I see that I can get a new ResultQuery from the bind, and attach and execute that, leaving the original query object unattached (though not unbound it seems...which seems a little wonky to me). The new ResultQuery will go out of scope which I suppose is a way of "detaching" it. From running in a debugger it looks to me like this new ResultQuery is indeed a separate object from the original query object and so I assume I should have no thread-safety concerns if always create a new ResultQuery with bind and use that. Is that all correct?

Is bind() essentially doing query object "cloning"?

No, that's not correct. Many jOOQ methods return a reference of the same type in order to implement a fluent API. In other words, you can chain method calls, such as

    select(...)

    .from(...)

    .where(...)

    .bind(...)

    .bind(...)

    .fetch();

 

As all other DSL methods in the query construction API, bind() returns the *same* instance of the Query, not a new one. In other words, a Select<?> object is mutable. This is hinted at in the manual, here:

http://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/dsl-and-non-dsl/#N10947

Mutability / Immutability is another challenge to tackle in jOOQ 4.0, in the context of a clean separation of a DSL and Model API:

https://github.com/jOOQ/jOOQ/issues/2198

In essence, the Model API will be mutable, whereas the DSL API will be immutable. However, these things are not yet decided. It may well be that jOOQ will gain influence from the Scala community, where immutability is a key feature of most APIs. This would then render the Model API obsolete, in favour of a (hopefully) very composable DSL API.

I still wonder if it wouldn't be nicer to have some API distinction between stateful statement building objects and immutable objects obtainable from them representing sql statements (or parts) that you have built. Perhaps this is overly idealistic to expect this in conjunction with a fluent DSL style API.

No it isn't overly idealistic. It's just that jOOQ is not there yet.

[Lukas Eder]

2013/9/6 Lukas Eder <lukas...@gmail.com>

Hello,

2013/9/6 Eric Schwarzenbach <ericjs...@gmail.com>

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);

        }

    }

I forgot to agree with adding Attachable.detach()

https://github.com/jOOQ/jOOQ/issues/2726

Already now, you can detach attachables, by passing a null Configuration:

    query.attach(null);

This is not very intuitive, though, so I'll add a detach() method.

[Eric Schwarzenbach]

Hi Lukas,

Thanks for your replies. I just want to say that your openness to talking about consider improvements to your API is very refreshing, and an all too rare thing in the software world.

I would swear that when I stepped over my bind() call in the debugger I saw a different object returned than the query on which I executed it. I must have misinterpreted something.

I'm happy to hear that some of these issues may be addressed in a JOOQ 4, and that there is a way to detach queries. Let me just make sure I'm interpreting your replies correctly around query objects and thread safety. It looks like my options are:

1) don't hold onto a query object, rebuild the query object each time I execute it
2) build it once, extract the sql and hold onto that instead of the query object, and when I want to execute it, instantiate new query object, using DSLContext.resultQuery (in my "select" cases, at least)
3) hold onto the query object and synchronize the lines where I attach, bind, and execute (probably not really desirable but just listing it for completeness)

Is that right or are there other options I missed?

Thanks again,

Eric

[Lukas Eder]

Hi Eric,

2013/9/6 Eric Schwarzenbach <ericjs...@gmail.com>

Hi Lukas,

Thanks for your replies. I just want to say that your openness to talking about consider improvements to your API is very refreshing, and an all too rare thing in the software world.

Well, if it wasn't for the great feedback I tend to get on this user group (and on GitHub), jOOQ might be only half as good ;-)

 

I would swear that when I stepped over my bind() call in the debugger I saw a different object returned than the query on which I executed it. I must have misinterpreted something.

I'm happy to hear that some of these issues may be addressed in a JOOQ 4, and that there is a way to detach queries. Let me just make sure I'm interpreting your replies correctly around query objects and thread safety. It looks like my options are:

1) don't hold onto a query object, rebuild the query object each time I execute it

That's the easiest way. Or you can pool query objects to add thread-safety to them, externally. For instance, using Apache Commons Pool:

http://commons.apache.org/proper/commons-pool

I'm sure Guava has some nice polling APIs as well.

 

2) build it once, extract the sql and hold onto that instead of the query object, and when I want to execute it, instantiate new query object, using DSLContext.resultQuery (in my "select" cases, at least)

Yes, you can do that. Others have successfully used jOOQ as a SQL builder only, and executed the statements through Spring JDBC / JdbcTemplate for instance. See also:

http://www.jooq.org/doc/3.1/manual/getting-started/use-cases/jooq-as-a-sql-builder-with-code-generation

I personally recommend:

a) profiling to see if there is a significant performance gain in choosing that approach (I'd be interested in results!)

b) carefully assessing if you will then still profit from enough jOOQ features. For instance, some type information may be lost when you operate on Strings only.

 

3) hold onto the query object and synchronize the lines where I attach, bind, and execute (probably not really desirable but just listing it for completeness)

Again, if you want to reuse the query object, I'd personally prefer pooling over explicit synchronising. But synchronising will do the job as well, of course.

Is that right or are there other options I missed?

Nothing I can think of right now. Of course, as always, the group is invited to join and participate, sharing their own experience.

Regards

Lukas

[Lukas Eder]

Hmm, I wonder though, if jOOQ should maintain a ThreadLocal somewhere, referencing the Configuration for query / routine execution, instead of attaching things... This would result in the following use-cases:

Creating attached objects and executing them:

==================================

DSL.using(...).select().from(...).fetch();

This will behave as today, for backwards-compatibility reasons. Queries created through DSLContext will be attached to their creating Configuration and execute themselves in the context of such a Configuration

Creating unattached objects and executing them:

==================================

DSL.using(...).fetch(

    select().from(...)

)

This will no longer attach the argument query, but pull a Configuration from a ThreadLocal that is set (and unset) in the DSLContext.fetch() method

Executing queries in a "block"

==================================

The above distinction would then allow for creating blocks around several queries. In the long run, such a model might allow for transaction listeners and other useful SPI:

DSL.using(...).run(new Runnable() {

    public void run() {

        // These methods pull a Configuration from a ThreadLocal

        select().from(...).fetch();

        insertInto().values(...).execute();

    }

});

Such blocks would also be very convenient to use with Java 8 lambdas:

DSL.using(...).run(() -> {

    // These methods pull a Configuration from a ThreadLocal

    select().from(...).fetch();

    insertInto().values(...).execute();

});

Supporting such blocks would allow for nesting blocks. Hence, the ThreadLocal would really be a ThreadLocal<Deque<Configuration>>, keeping track of the nesting of Configurations / blocks.

Consequences

==================================

This would mean that jOOQ would:

1. Use an attached Configuration if available, for backwards-compatibility reasons

2. Try to get a Configuration from a ThreadLocal, if the current Query is not "attached"

3. Throw a DetachedException (or similar) if both of the above fail.

Such a solution would allow for a smooth transition towards jOOQ 4.0, when later versions of jOOQ 3.x could deprecate DSLContext's various Query construction DSL entry points, indicating that attaching queries will eventually not be supported anymore.

Such a solution would also allow for using jOOQ in a more threadsafe way, without resorting to client-side pooling tricks, and without having to wait for jOOQ 4.0.

And most importantly, such a solution would be transparent, and backwards-compatible to jOOQ 3.0 and 3.1

Any feedback is welcome. This idea is tracked as: #2732

https://github.com/jOOQ/jOOQ/issues/2732

Cheers

Lukas

2013/9/8 Lukas Eder <lukas...@gmail.com>

[Eric Schwarzenbach]

I'm not sure I entirely get it. How exactly does the connection / threadlocal mechanism in use case 2 operate? Would it try to get the ThreadLocal Configuration, and if it didn't exist, use  / create a Configuration (with connection) from the using() arguments, and if it did exist, just ignore the using() arguments and use the ThreadLocal Configuration instead?

And does this mean if the query object passed into the fetch() (in your example "select().from(...)") is now thread safe and a single copy can be used in multiple threads?

On Monday, September 9, 2013 5:58:24 AM UTC-4, Lukas Eder wrote:

Hmm, I wonder though, if jOOQ should maintain a ThreadLocal somewhere, referencing the Configuration for query / routine execution, instead of attaching things... This would result in the following use-cases:

. . .

[Eric Schwarzenbach]

On Sunday, September 8, 2013 6:34:57 AM UTC-4, Lukas Eder wrote:

. . .

... Or you can pool query objects to add thread-safety to them, externally. For instance, using Apache Commons Pool:

http://commons.apache.org/proper/commons-pool

 
Yeah I considered listing pooling as another option, but adding a complex runtime mechanism like that as a workaround for a scoping issue feels very wrong to me.

 

2) build it once, extract the sql and hold onto that instead of the query object, and when I want to execute it, instantiate new query object, using DSLContext.resultQuery (in my "select" cases, at least)

Yes, you can do that. Others have successfully used jOOQ as a SQL builder only, and executed the statements through Spring JDBC / JdbcTemplate for instance. See also:

http://www.jooq.org/doc/3.1/manual/getting-started/use-cases/jooq-as-a-sql-builder-with-code-generation

I personally recommend:

a) profiling to see if there is a significant performance gain in choosing that approach (I'd be interested in results!)

Some simple timings show the query object reconstruction from the sql to be pretty insignificant. I was logging some timings using  System.currentTimeMillis() and it was basically not measurable that way (always 0). Switching to System.nanoTime(), my simple test ended up being around 7,000-8,000ns for the reconstruction, vs the 2,500,000 - 3,000,000 for the execution and very simple rendering of the result. This is a very simple query, though.

b) carefully assessing if you will then still profit from enough jOOQ features. For instance, some type information may be lost when you operate on Strings only.

 I haven't yet done any comparisons vs executing the sql via JDBC directly, and am undecided on the tradeoff in functionality. I do find the JOOQ datatype handling functionality to be handy, may just stick with it for that, with the idea that I can always change my code to jdbc execution if it becomes a concern (which I think is unlikely), and that some future JOOQ changes may make query object reconstruction unecessary anyway. FWIW, below is what my proof of concept code looks like at the moment (extracted from my class / method structure for simplicity):

Query construction code, executed once during initialization (keep in mind that the tableName, schemaName, and the column name used for the key field are determined at runtime. The jooq generated classes cannot be used directly, and are only used via reflection for column datatypes) :

org.jooq.TableField keyFieldDef = ...// use Java reflection to get TableField from the jooq generated classes,
                                     // from a dynamically sourced column name
DataType keyDataType = tf.getDataType();
Param param = DSL.param("key", keyDataType);
Condition keyCondition = keyFieldDef.equal(param);

ResultQuery<Record> query = DSL.select(getFields(tableName, true)).from(DSL.tableByName(schemaName, tableName))
                                                                            .where(keyCondition);
String sql = query.getSQL();

What gets saved in member variables between query executions is only sql, and keyDataType.
At execution time (code that may be run by multiple threads)

DSLContext create = DSL.using(dataSource, SQLDialect.POSTGRES);
Param param = DSL.param("key", keyDataType);
ResultQuery<Record> resultQuery = create.resultQuery(sql, param);
resultQuery.bind("key", keyValue);
Result<Record> result = resultQuery.fetch();

I left out the java reflection bit, but can provide that if it's useful to anybody. (I did mean to ask if there is a way to get data type information from the jooq-generated classes without using reflection...that would be handy, too, but mine may be an unusual usecase.)

[Lukas Eder]

2013/9/9 Eric Schwarzenbach <ericjs...@gmail.com>

On Sunday, September 8, 2013 6:34:57 AM UTC-4, Lukas Eder wrote:

. . .

... Or you can pool query objects to add thread-safety to them, externally. For instance, using Apache Commons Pool:

http://commons.apache.org/proper/commons-pool

 
Yeah I considered listing pooling as another option, but adding a complex runtime mechanism like that as a workaround for a scoping issue feels very wrong to me.

It does feel wrong. But it is common practice. Take the not-threadsafe javax.xml.transform.Transformer for instance...

2) build it once, extract the sql and hold onto that instead of the query object, and when I want to execute it, instantiate new query object, using DSLContext.resultQuery (in my "select" cases, at least)

Yes, you can do that. Others have successfully used jOOQ as a SQL builder only, and executed the statements through Spring JDBC / JdbcTemplate for instance. See also:

http://www.jooq.org/doc/3.1/manual/getting-started/use-cases/jooq-as-a-sql-builder-with-code-generation

I personally recommend:

a) profiling to see if there is a significant performance gain in choosing that approach (I'd be interested in results!)

Some simple timings show the query object reconstruction from the sql to be pretty insignificant. I was logging some timings using  System.currentTimeMillis() and it was basically not measurable that way (always 0). Switching to System.nanoTime(), my simple test ended up being around 7,000-8,000ns for the reconstruction, vs the 2,500,000 - 3,000,000 for the execution and very simple rendering of the result. This is a very simple query, though.

Ok, so that would probably mean that pooling is hardly necessary.

b) carefully assessing if you will then still profit from enough jOOQ features. For instance, some type information may be lost when you operate on Strings only.

 I haven't yet done any comparisons vs executing the sql via JDBC directly, and am undecided on the tradeoff in functionality. I do find the JOOQ datatype handling functionality to be handy, may just stick with it for that, with the idea that I can always change my code to jdbc execution if it becomes a concern (which I think is unlikely), and that some future JOOQ changes may make query object reconstruction unecessary anyway. FWIW, below is what my proof of concept code looks like at the moment (extracted from my class / method structure for simplicity):

Query construction code, executed once during initialization (keep in mind that the tableName, schemaName, and the column name used for the key field are determined at runtime. The jooq generated classes cannot be used directly, and are only used via reflection for column datatypes) :

Interesting approach :-) I think that this reflection might account for more overhead than the actual query construction. I guess a lookup Map<String, TableField> initialised at application startup might also do the trick...?

 

org.jooq.TableField keyFieldDef = ...// use Java reflection to get TableField from the jooq generated classes,
                                     // from a dynamically sourced column name
DataType keyDataType = tf.getDataType();
Param param = DSL.param("key", keyDataType);
Condition keyCondition = keyFieldDef.equal(param);

ResultQuery<Record> query = DSL.select(getFields(tableName, true)).from(DSL.tableByName(schemaName, tableName))
                                                                            .where(keyCondition);
String sql = query.getSQL();

What gets saved in member variables between query executions is only sql, and keyDataType.
At execution time (code that may be run by multiple threads)

DSLContext create = DSL.using(dataSource, SQLDialect.POSTGRES);
Param param = DSL.param("key", keyDataType);
ResultQuery<Record> resultQuery = create.resultQuery(sql, param);
resultQuery.bind("key", keyValue);
Result<Record> result = resultQuery.fetch();

Note that Param.setValue() exists:

http://www.jooq.org/javadoc/latest/org/jooq/Param.html#setValue(T)

Or setConverted() if you may risk data type confusion:

http://www.jooq.org/javadoc/latest/org/jooq/Param.html#setConverted(java.lang.Object)

That might be a bit better than calling .bind()

 

I left out the java reflection bit, but can provide that if it's useful to anybody. (I did mean to ask if there is a way to get data type information from the jooq-generated classes without using reflection...that would be handy, too, but mine may be an unusual usecase.)

You can traverse the generated metadata using MY_SCHEMA.getTables() and then access each table's fields

Cheers

Lukas 

[Lukas Eder]

2013/9/9 Eric Schwarzenbach <ericjs...@gmail.com>

I'm not sure I entirely get it. How exactly does the connection / threadlocal mechanism in use case 2 operate? Would it try to get the ThreadLocal Configuration, and if it didn't exist, use  / create a Configuration (with connection) from the using() arguments, and if it did exist, just ignore the using() arguments and use the ThreadLocal Configuration instead?

More or less. I may have confused a couple of wordings, or the order of preference. Here's the implementation of DSLContext.fetch(ResultQuery) that I had in mind

    @Override

    public <R extends Record> Result<R> fetch(ResultQuery<R> query) {

        try {

            pushToThreadLocal(configuration);

            return query.fetch();

        }

        finally {

            popFromThreadLocal();

        }

    }

As opposed to the implementation of ResultQuery.fetch():

    @Override

    public final Result<R> fetch() {

        Configuration c = peekFromThreadLocal();

        if (c == null)

            c = configuration();

        // [...]

    }

 

And does this mean if the query object passed into the fetch() (in your example "select().from(...)") is now thread safe and a single copy can be used in multiple threads?

With respect to Configuration? Yes. With respect to changing bind values? No. 

Beware, that there is a more state in a Query object, other than just the Configuration. For instance, a Query may keep a reference to an open PreparedStatement after execution as documented here:

http://www.jooq.org/doc/3.1/manual/sql-execution/reusing-statements

... which makes me think that maybe, I should abandon this ThreadLocal idea for now and see if this can be fixed more generally. Christopher's idea at the time can be seen here:

https://groups.google.com/d/msg/jooq-user/Y_tfF_eaxKM/U6EZvtkPWloJ

Essentially, it would consist of:

using(configuration).bind(Query).bind(... bindings ...).fetch();

In his model, there would be two APIs:

- Query (the static, immutable, thread-safe AST of your query)

- BoundQuery (the mutable, short-lived executable query)

This model still leaves open questions with respect to inline bind values, but it would probably be quite clean...

[Eric Schwarzenbach]

On Tuesday, September 10, 2013 1:27:19 PM UTC-4, Lukas Eder wrote:

2013/9/9 Eric Schwarzenbach <ericjs...@gmail.com>

On Sunday, September 8, 2013 6:34:57 AM UTC-4, Lukas Eder wrote:

b) carefully assessing if you will then still profit from enough jOOQ features. For instance, some type information may be lost when you operate on Strings only.

 I haven't yet done any comparisons vs executing the sql via JDBC directly, and am undecided on the tradeoff in functionality. I do find the JOOQ datatype handling functionality to be handy, may just stick with it for that, with the idea that I can always change my code to jdbc execution if it becomes a concern (which I think is unlikely), and that some future JOOQ changes may make query object reconstruction unecessary anyway. FWIW, below is what my proof of concept code looks like at the moment (extracted from my class / method structure for simplicity):

Query construction code, executed once during initialization (keep in mind that the tableName, schemaName, and the column name used for the key field are determined at runtime. The jooq generated classes cannot be used directly, and are only used via reflection for column datatypes) :

Interesting approach :-) I think that this reflection might account for more overhead than the actual query construction. I guess a lookup Map<String, TableField> initialised at application startup might also do the trick...?

Yeah, I am only doing the reflection at initialization, and storing the field datatype (at the moment, in the proof of concept code, a single one, but it may be a map or such eventually) for use in reconstructing the query.

 

org.jooq.TableField keyFieldDef = ...// use Java reflection to get TableField from the jooq generated classes,
                                     // from a dynamically sourced column name
DataType keyDataType = tf.getDataType();
Param param = DSL.param("key", keyDataType);
Condition keyCondition = keyFieldDef.equal(param);

ResultQuery<Record> query = DSL.select(getFields(tableName, true)).from(DSL.tableByName(schemaName, tableName))
                                                                            .where(keyCondition);
String sql = query.getSQL();

What gets saved in member variables between query executions is only sql, and keyDataType.
At execution time (code that may be run by multiple threads)

DSLContext create = DSL.using(dataSource, SQLDialect.POSTGRES);
Param param = DSL.param("key", keyDataType);
ResultQuery<Record> resultQuery = create.resultQuery(sql, param);
resultQuery.bind("key", keyValue);
Result<Record> result = resultQuery.fetch();

Note that Param.setValue() exists:

http://www.jooq.org/javadoc/latest/org/jooq/Param.html#setValue(T)

Or setConverted() if you may risk data type confusion:

http://www.jooq.org/javadoc/latest/org/jooq/Param.html#setConverted(java.lang.Object)

That might be a bit better than calling .bind()

Ok. What is the difference?

 

I left out the java reflection bit, but can provide that if it's useful to anybody. (I did mean to ask if there is a way to get data type information from the jooq-generated classes without using reflection...that would be handy, too, but mine may be an unusual usecase.)

You can traverse the generated metadata using MY_SCHEMA.getTables() and then access each table's fields

Ah! Yes, that's what I needed. That should avoid all need for reflection except for getting the schema class based on the schema name. Unless there is some method to list the schema objects somewhere?

Thanks,

Eric

[Lukas Eder]

2013/9/10 Eric Schwarzenbach <ericjs...@gmail.com>

.bind() first collects all Params by traversing the AST, looking for Param objects before calling setConverted() on the matching Param object. Since you have a reference to the same Param objects two lines earlier, it might be a bit faster to set the value directly on that Param.

I left out the java reflection bit, but can provide that if it's useful to anybody. (I did mean to ask if there is a way to get data type information from the jooq-generated classes without using reflection...that would be handy, too, but mine may be an unusual usecase.)

You can traverse the generated metadata using MY_SCHEMA.getTables() and then access each table's fields

Ah! Yes, that's what I needed. That should avoid all need for reflection except for getting the schema class based on the schema name. Unless there is some method to list the schema objects somewhere?

Eventually, jOOQ will support catalog code generation. But then you'll still need to discover the catalog somehow :-)

Anyway, no there is no schema registry.

Regards,
Lukas