Question about PreparedStatement cache

Skip to first unread message

Jul 15, 2014, 2:06:54 AM7/15/14

i use jooq on firebird database, and i try to optimize the global performance.

my datasource is a c3p0 connection pool with statement cache activated.

after a database monitoring i have seen that a i have a lot of prepared statement that like's that :

update "TEST" set "TEST"."A" = cast(? as varchar(3))
update "TEST" set "TEST"."A" = cast(? as varchar(4))
update "TEST" set "TEST"."A" = cast(? as varchar(5))
update "TEST" set "TEST"."A" = cast(? as varchar(6))
update "TEST" set "TEST"."A" = cast(? as varchar(7))

and the code in java like that

public void test(String a) {
 getDsl().update(TEST).set(TEST.A, a).execute();


so because the key for statement cache is the sql of the request and as the variable is cast with length, this behaviour generates a lot of prepared statement for the same query.

is there a way to tell to jooq to generate a sql like that update "TEST" set "TEST"."A" = ?
or maybe i don't understand something and other solution will be most valuable.

thanks in advance.

ps : my dsl config is quite simple

    org.jooq.impl.DefaultConfiguration res = new DefaultConfiguration();
    res.set(connectionProvider()); // default connection provider

Lukas Eder

Jul 15, 2014, 7:43:26 AM7/15/14

Thanks for reporting. You're right, from a SQL parser perspective, this is far from optimal. Firebird is the only database that seems to "need" a concretely-sized varchar type when casting. Unfortunately, I don't remember the reasons for this logic, which was implemented as part of back in jOOQ 2.5:

The rationale behind bind variable casting in general can be seen here:

is there a way to tell to jooq to generate a sql like that update "TEST" set "TEST"."A" = ?

That should be done indeed, as most databases are able to infer the bind variable types in such "obvious" situations. Unfortunately, they cannot infer the types in "edge cases", like SELECT ?, ? - which is the reason why jOOQ usually casts bind variables in some databases.

I'm very open to further investigate this, though. I have registered an issue for this:


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
For more options, visit

Lukas Eder

Nov 4, 2021, 8:55:06 AM11/4/21
to jOOQ User Group
This issue will be fixed in jOOQ 3.16 for SQLDialect.FIREBIRD_3_0: The FIREBIRD_2_5 implementation will remain intact.
Reply all
Reply to author
0 new messages