2013/9/9 Eric Schwarzenbach <[email protected]> > > > 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<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<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 -- 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.
