Thread-safety during SQL generation

[jes.c...@gmail.com]

Hi,

I am perplexed by this simple question. Is the usage of the static DSLContext and the Query.getSQL thread-safe?

In my case I am using jOOQ to provide type-safety and a more intuitive SQL query creation in my Spring MVC web application.

My DAO will generate the necessary SQL statement and execute it using Spring's JDBCTemplate.

private static final DSLContext CREATE = DSL.using(SQLDialect.MYSQL);

List<Products> products = new ArrayList<Products>();

String sql = CREATE.select().from(Products.PRODUCTS).getSQL();

products = getJdbcTemplate().query(sql, new SimpleProductMapper());

With CREATE being a static variable, I am not sure of the consequence of running this code on my system where severel threads could use this DAO at the same time.

Is there a way to make the getSQL mechanism safe to render the SQL without influencing other threads?

Is this a fair concern? 

I'll be very grateful if anyone could point me to a solution.

Thanks in advance.

[Lukas Eder]

Hello,

2013/9/20 <jes.c...@gmail.com>

Hi,

I am perplexed by this simple question. Is the usage of the static DSLContext and the Query.getSQL thread-safe?

It depends. Thus: No. :-)

 

In my case I am using jOOQ to provide type-safety and a more intuitive SQL query creation in my Spring MVC web application.

My DAO will generate the necessary SQL statement and execute it using Spring's JDBCTemplate.

private static final DSLContext CREATE = DSL.using(SQLDialect.MYSQL);

This usage is fine, as long as you won't modify the underlying CREATE.configuration() object nor its contents.

 

List<Products> products = new ArrayList<Products>();

String sql = CREATE.select().from(Products.PRODUCTS).getSQL();

products = getJdbcTemplate().query(sql, new SimpleProductMapper());

This usage looks good to me as well, as long as you do not share the Query object created through CREATE.select().from(...) among threads.

 

With CREATE being a static variable, I am not sure of the consequence of running this code on my system where severel threads could use this DAO at the same time.

Is there a way to make the getSQL mechanism safe to render the SQL without influencing other threads?

In general, I'd personally suggest that you do not cache the DSLContext object, but create it every time afresh. The negligible overhead for the garbage collector is worth the trouble, avoiding potential problems should you ever decide to modify CREATE's underlying Configuration. With jOOQ's Configuration, DSLContext and Query objects not being thread-safe, re-instantiation is almost always a better bet.

Is this a fair concern?

Yes it is. You may also be interested in reading this recent, related thread:

https://groups.google.com/forum/#!topic/jooq-user/iomrnfK9cws

Cheers

Lukas