Digest for jooq-user@googlegroups.com - 9 updates in 3 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 9 updates in 3 topics

http://groups.google.com/group/jooq-user/topics mailing list
[hidden email]: Oct 30 08:36AM -0700

Suppose I have a decomposed query in some format, like "fields=a,b,c;
whereClause='1=1'; groupBy=b,c;" etc. Some parts of the query might be
missing, which corresponds to missing SQL part (e.g. a select without where
clause). Having that, I'd like to compose a jOOQ query which could be run
by some external executor, suppose a default DSL.using( conn, dialect
).fetch( constructedQuery ).
 
Currently my code looks like the following:
 
Select select = select( fieldsList ).from( myTable );
 
if( hasWhereClause )
{
select = ((SelectJoinStep) select).where( whereClause );
}
 
if( hasGroupBy )
{
select = ((SelectConditionStep) select).groupBy( groupByClause );
}
 
if( hasOrderBy )
{
select = ((SelectHavingStep) select).orderBy( orderByClause );
}
 
...
 
return select;
 
 
As you see, because of the class hierarchy (which I actually like and
admire a bit, since I can see it could not be easily designed), I either
need multiple class casts or lots of nested if-statements - and both
options are not really exciting.
 
So maybe I'm missing something, or maybe there's an alternative builder for
jOOQ queries (if not, I suppose one of the best solutions would be to have
one), with which the code would look like the following:
new JooqQueryBuilder()
.withFrom(TableLike)
.withWhereClause(Condition)
.withGroupBy(List<GroupByField>)
...
 
Hope I managed to clearly describe the problem, but if you need any further
clarification - I would be glad to provide such.
Lukas Eder <[hidden email]>: Oct 30 04:40PM +0100

Hi,
 
Your use-case is covered by the model API, much better than the DSL API.
More info here:
https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/dsl-and-non-dsl
 
It's also what the DSL delegates all calls to, internally.
 
Hope this helps,
Lukas
 
[hidden email]: Oct 30 08:55AM -0700

Great, looks like exactly what I was looking for! Sorry for missing that in
the documentation, it's really vast and I probably skipped this small
chapter :)
 
Is the following a correct way to get a SelectQuery without a DSLContext?
I'd prefer to keep this class a mere transformer, not aware of any
contexts, dialects and moreover database connections.
SelectQuery<Record> select = DSL.select( fieldsList )
.from( table )
.getQuery();
 
 
On Monday, October 30, 2017 at 5:40:41 PM UTC+2, Lukas Eder wrote:
Lukas Eder <[hidden email]>: Oct 30 05:06PM +0100


> Great, looks like exactly what I was looking for! Sorry for missing that
> in the documentation, it's really vast and I probably skipped this small
> chapter :)
 
No worries, it's not the main use-case. In most cases, you can still do
dynamic SQL with the DSL API:
https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql
 
Only when you need to add entire clauses conditionally, things get hairy
with the DSL API.
 
 
> SelectQuery<Record> select = DSL.select( fieldsList )
> .from( table )
> .getQuery();
 
Yes, that's one way. An alternative way is to create a DSLContext that is
not connected to anything:
 
DSL.using(new DefaultConfiguration()).selectQuery();
 
 
Hope this helps,
Lukas
[hidden email]: Oct 30 09:11AM -0700

I see that using(new DefaultConfiguration()) is exactly what DSL class
does, so I'll go the shorter path :)
 
Thanks again for your assistance!
 
On Monday, October 30, 2017 at 6:06:35 PM UTC+2, Lukas Eder wrote:
Lukas Eder <[hidden email]>: Oct 30 12:05PM +0100

Thank you very much for your report.
 
This is a bug indeed. I've created issue #6745 for this:
https://github.com/jOOQ/jOOQ/issues/6745
 
jOOQ should attempt to create the biggest possible VARCHAR column size
(65535) for String types of unknown lengths.
 
In the meantime, the safest choice is to specify the type explicitly
(always, regardless of database) by using something like
SQLDataType.VARCHAR(50).
 
I hope this helps,
Lukas
 
Lukas Eder <[hidden email]>: Oct 30 12:14PM +0100

I remember again now, why jOOQ didn't add a default maximum column length.
It's because of MySQL's maximum row size limit, which is also 65535:
https://stackoverflow.com/a/13506920/521799
 
So, there's not really a good default to choose, when writing DDL. Another
reason why you should pick one yourself!
 
Daniel <[hidden email]>: Oct 29 05:28PM -0700

Hi Lukas,
 
I have a similar issue, and it looks like a JDBC bug:
https://bugs.mysql.com/bug.php?id=80631
 
The 6.0.5 release log claims they've solved the issue, but the problem
still applies to normal VARCHAR columns.
I tried converting the table from latin1 to utf8mb4, changing the
characterSetResults to latin1, then utf8, but nothing works.
 
The only thing that works is using ResultSet.getBytes instead of getString:
 
new String(res.getBytes("columnName"), "UTF-8")
 
Is there any way I can get JOOQ to use getBytest instead of getString?
 
Thanks,
Daniel
 
 
jOOQ just passes Strings through to JDBC and fetches them from JDBC without
Lukas Eder <[hidden email]>: Oct 30 08:50AM +0100

Hi Daniel,
 
Thanks for your message. You can easily override jOOQ's DefaultBinding by
implementing your own data type Binding, and by using the code generator to
bind that binding to all the relevant columns:
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings
 
Do note that the encoding used by client / server depends on a variety of
settings, including the collation of the server, of the individual column,
of the driver (you can override things in the JDBC connection string) and
on the JVM.
 
Hope this helps,
Lukas
 
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].