Digest for jooq-user@googlegroups.com - 3 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 - 3 updates in 3 topics

http://groups.google.com/group/jooq-user/topics mailing list
[hidden email]: Nov 14 06:07AM -0800

Hi all,
 
On Friday, September 29, 2017 at 2:56:36 PM UTC+2, Lukas Eder wrote:
> create your own general-purpose Binding and use that for every dynamically
> created column expression (you'd have to know these columns in advance,
> though).
 
(sorry for the late reply)
 
While it might be unwise for JOOQ to do so, I found that in our specific
setting, we only use tinyint as a boolean. Therefore I added it to our
mapping:
 
<!-- Associate data type rewrites with database columns -->
<forcedTypes>
<forcedType>
<name>BOOLEAN</name>
<types>tinyint</types>
</forcedType>
</forcedTypes>
 
This works for us, might be useful for others so I thought I'd mention it
here
 
Groeten,
 
Friso
Pavel Finkelshtein <[hidden email]>: Nov 14 05:38AM -0800

Continuing theme started
in https://twitter.com/asm0di0/status/929752657878798336 about JSONB
support.
 
JSONB in postgres supports several operators, which can be found here
<https://www.postgresql.org/docs/current/static/functions-json.html>.
 
Couple example of how we work with it in in JOOQ:
 
return ctx
.select(
sum(field("(short_deal_json -> 'contractInfo' ->>
'price')::NUMERIC", BigDecimal::class.java))
- sum(field("(short_deal_json ->> 'deposit')::NUMERIC",
BigDecimal::class.java))
)
.from(DEAL)
.where(DEAL.STATE.eq(DealState.REGISTRATION))
.fetchOneInto(BigDecimal::class.java)
And even
ctx
.select(DEAL.DEAL_NO)
.from(DEAL)
.where(normalizedField("buyer,person,firstName").eq(firstName))
.and(normalizedField("buyer,person,lastName").eq(lastName))
.and(
if (patronymic == null) {
DSL.field("request_json #>> '{buyer,person,patronymic}'").
isNull
} else {
normalizedField("buyer,person,patronymic").eq(patronymic)
}
)
.and(DSL.field("request_json #>> '{contractInfo,cadastralNum}'",
String::class.java).eq(cadastralNum))
.and(
if (contractNumber == null) {
DSL.field("request_json #>> '{contractInfo,contractNumber}'"
).isNull
} else {
normalizedContractField("contractInfo,contractNumber").eq(
contractNumber)
}
)
.fetch(DEAL.DEAL_NO)
I think it would make sense to implement some type of sub-dsl for work with
JSON values, like GSON's one: asJsonObjetc, asJsonArray and so on.
 
Regarding 3rd point of my tweet, JSON introspection. I think that it may be
possible to convert JSON to Records, by using this
<http://www.jsonschema2pojo.org/> project. Of course, generated source will
need some corrections, according to Record semantics, but it will give
developers ability to work with json fields almost like with related
tables. Moreover, it will be possible to make updates to sub-entities and
then update whole entity without performing ultra-complex queries!
Lukas Eder <[hidden email]>: Nov 14 01:20PM +0100

Hi Niclas,
 
Thanks for your message. Yes you're right, that's a bug:
https://github.com/jOOQ/jOOQ/issues/6745
 
But the fix isn't very easy, unfortunately. MySQL needs a VARCHAR length,
but the total length of a row is 64kb:
https://stackoverflow.com/a/13506920/521799
 
So, what length should jOOQ choose by default? 256 bytes? We'll have to
pick something, but this will need a bit more thought. But clearly, "char"
is incorrect.
 
Thanks,
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].