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