Hi Lukas!
Thanks for the answer and the code. I tested it and it works indeed for the
examples that you gave me. Unfortunately, it does not work when using the
inline(true) construct. For us, it is very important as we want to
selectively inline constants (boolean flags (and enum constants) are good
candidates for such inlining) in our SQL statements.
db.selectFrom(TRUTH).where(TRUTH.TRUTH_.eq(inline(true))
The previous statement gets the rendering with 1 and not '1'. The problem
with the binding solution is that it is bound to the generated fields and
not to the boolean type per se.
Cheers!
stan.
On Tuesday, December 15, 2015 at 6:28:48 PM UTC+1, Lukas Eder wrote:
>
> Hi Stan,
>
> Digging further, my analysis proved to be correct. You shouldn't apply the
> forcedType setting and keep the data type at CHAR.length(1). You can then
> use the following BooleanBinding:
>
> import org.jooq.BindingSQLContext;
> import org.jooq.RenderContext;
> import org.jooq.conf.ParamType;
> import org.jooq.impl.AbstractConverter;
> import org.jooq.impl.DefaultBinding;
>
> @SuppressWarnings("serial")
> public class BooleanCharBinding extends DefaultBinding<String, Boolean> {
>
> public BooleanCharBinding() {
> super(new AbstractConverter<String, Boolean>(String.class,
> Boolean.class) {
> @Override
> public Boolean from(String t) {
> return t == null ? null : !"0".equals(t);
> }
>
> @Override
> public String to(Boolean u) {
> return u == null ? null : u ? "1" : "0";
> }
> });
> }
>
> @Override
> public void sql(BindingSQLContext<Boolean> ctx) {
> RenderContext render = ctx.render();
>
> if (render.paramType() == ParamType.INLINED) {
> final Boolean val = ctx.value();
> if (val == null) {
> render.keyword("null");
> } else {
> render.sql(val ? "'1'" : "'0'");
> }
> } else {
> super.sql(ctx);
> }
> }
> }
>
> Using this, the following test runs smoothly:
>
> @Test
> public void testOracleBooleanCharBinding() {
> clean(T_4807);
>
> assertEquals(1,
> create().insertInto(T_4807)
> .columns(T_4807.ID, T_4807.BOOL_CHAR)
> .values(1, null)
> .execute()
> );
>
> assertEquals(2,
> create().insertInto(T_4807)
> .columns(T_4807.ID, T_4807.BOOL_CHAR)
> .values(2, false)
> .values(3, true)
> .execute()
> );
>
> Result<T_4807Record> result =
> create().selectFrom(T_4807)
> .where(T_4807.BOOL_CHAR.isNull())
> .or(T_4807.BOOL_CHAR.in(false, true))
> .orderBy(T_4807.ID)
> .fetch();
>
> assertEquals(asList(1, 2, 3), result.getValues(T_4807.ID));
> assertEquals(asList(null, false, true),
> result.getValues(T_4807.BOOL_CHAR));
> }
>
>
> Producing the following SQL statements (logging with formatting and
> variable inlining):
>
> insert into "TEST"."T_4807" (
> "ID",
> "BOOL_CHAR"
> )
> values (
> 1,
> null
> )
>
>
> insert into "TEST"."T_4807" (
> "ID",
> "BOOL_CHAR"
> )
> (
> select
> 2,
> '0'
> from dual
> )
> union all (
> select
> 3,
> '1'
> from dual
> )
>
>
> select
> "TEST"."T_4807"."ID",
> "TEST"."T_4807"."BOOL_CHAR"
> from "TEST"."T_4807"
> where (
> "TEST"."T_4807"."BOOL_CHAR" is null
> or "TEST"."T_4807"."BOOL_CHAR" in (
> '0', '1'
> )
> )
> order by "TEST"."T_4807"."ID" asc
>
>
> Hope this helps,
> Lukas
>
> 2015-12-15 18:10 GMT+01:00 Lukas Eder <[email protected] <javascript:>>:
>
>> Hi Stan,
>>
>> I'm currently looking into your implementation. There are two things that
>> I can already say:
>>
>> public final TableField<TruthRecord, Boolean> TRUTH_ =
>> createField("TRUTH", org.jooq.impl.SQLDataType.BOOLEAN, this, "", new
>> BooleanBinding());
>>
>> It appears that in addition to applying a data type binding, you have
>> also applied data type rewriting. This should really be
>>
>> public final TableField<TruthRecord, Boolean> TRUTH_ =
>> createField("TRUTH", org.jooq.impl.SQLDataType.CHAR.length(1), this, "",
>> new BooleanBinding());
>>
>> That might already fix the issue, specifically, because otherwise, jOOQ /
>> JDBC will still bind an int value, not a String value.
>>
>> If not, I think you shouldn't extend the DefaultBinding, but implement
>> Binding and implement all the methods.
>>
>> I'll continue to look into this on my side.
>> Lukas
>>
>> 2015-12-15 14:05 GMT+01:00 Stanislas Nanchen <[email protected]
>> <javascript:>>:
>>
>>> Hi Lukas!
>>>
>>> I have a problem with the Binding. Probably doing something wrong. Let
>>> assume the table Truth
>>>
>>> create table TRUTH (TRUTH char(1) not null);
>>>
>>> and the following binding class.
>>>
>>> package ch.example.jooq;
>>>
>>> import org.jooq.BindingSQLContext;
>>> import org.jooq.RenderContext;
>>> import org.jooq.conf.ParamType;
>>> import org.jooq.impl.AbstractConverter;
>>> import org.jooq.impl.DefaultBinding;
>>>
>>> public class BooleanBinding extends DefaultBinding<Boolean, Boolean> {
>>>
>>> public BooleanBinding() {
>>> super(new AbstractConverter<Boolean, Boolean>(Boolean.class,
>>> Boolean.class) {
>>> @Override
>>> public Boolean from(Boolean databaseObject) {
>>> return databaseObject;
>>> }
>>>
>>> @Override
>>> public Boolean to(Boolean userObject) {
>>> return userObject;
>>> }
>>> });
>>> }
>>>
>>> @Override
>>> public void sql(BindingSQLContext<Boolean> ctx) {
>>> RenderContext render = ctx.render();
>>>
>>> if (render.paramType() == ParamType.INLINED) {
>>> final Boolean val = ctx.value();
>>> if (val == null) {
>>> render.keyword("null");
>>> } else {
>>> render.sql(val ? "'1'" : "'0'");
>>> }
>>> } else {
>>> super.sql(ctx);
>>> }
>>> }
>>>
>>> }
>>>
>>> I configured the generator to use the binding and got the following
>>> generated class:
>>>
>>> /**
>>> * This class is generated by jOOQ
>>> */
>>> package ch.examples.tables;
>>>
>>> import javax.annotation.Generated;
>>>
>>> import org.jooq.Field;
>>> import org.jooq.Table;
>>> import org.jooq.TableField;
>>> import org.jooq.impl.TableImpl;
>>> .....
>>>
>>>
>>> /**
>>> * This class is generated by jOOQ.
>>> */
>>> @Generated(
>>> value = {
>>> "http://www.jooq.org";,
>>> "jOOQ version:3.7.0"
>>> },
>>> comments = "This class is generated by jOOQ"
>>> )
>>> @SuppressWarnings({ "all", "unchecked", "rawtypes" })
>>> public class Truth extends TableImpl<TruthRecord> {
>>>
>>> private static final long serialVersionUID = -1911463108;
>>>
>>> /**
>>> * The reference instance of <code>BE.TRUTH</code>
>>> */
>>> public static final Truth TRUTH = new Truth();
>>>
>>> /**
>>> * The class holding records for this type
>>> */
>>> @Override
>>> public Class<TruthRecord> getRecordType() {
>>> return TruthRecord.class;
>>> }
>>>
>>> /**
>>> * The column <code>BE.TRUTH.TRUTH</code>.
>>> */
>>> public final TableField<TruthRecord, Boolean> TRUTH_ =
>>> createField("TRUTH", org.jooq.impl.SQLDataType.BOOLEAN, this, "", new
>>> BooleanBinding());
>>>
>>> /**
>>> * Create a <code>BE.TRUTH</code> table reference
>>> */
>>> public Truth() {
>>> this("TRUTH", null);
>>> }
>>>
>>> /**
>>> * Create an aliased <code>BE.TRUTH</code> table reference
>>> */
>>> public Truth(String alias) {
>>> this(alias, TRUTH);
>>> }
>>>
>>> private Truth(String alias, Table<TruthRecord> aliased) {
>>> this(alias, aliased, null);
>>> }
>>>
>>> private Truth(String alias, Table<TruthRecord> aliased, Field<?>[]
>>> parameters) {
>>> super(alias, Be.BE, aliased, parameters, "");
>>> }
>>>
>>> /**
>>> * {@inheritDoc}
>>> */
>>> @Override
>>> public Truth as(String alias) {
>>> return new Truth(alias, this);
>>> }
>>>
>>> /**
>>> * Rename this table
>>> */
>>> public Truth rename(String name) {
>>> return new Truth(name, null);
>>> }
>>> }
>>>
>>>
>>> When I generate queries, the custom binding is not used:
>>>
>>> System.out.println(db.insertInto(TRUTH).select(select(inline(true))).getSQL());
>>> System.out.println();
>>> System.out.println(db.selectFrom(TRUTH).where(TRUTH.TRUTH_).getSQL());
>>>
>>> -->
>>>
>>> insert into TRUTH (TRUTH)
>>> select 1
>>> from dual
>>>
>>> select TRUTH.TRUTH
>>> from TRUTH
>>> where (TRUTH.TRUTH = 1)
>>>
>>>
>>> What do I do wrong?
>>>
>>> cheers. stan.
>>>
>>> On Tuesday, December 15, 2015 at 1:20:19 PM UTC+1, Lukas Eder wrote:
>>>>
>>>> Thanks for the feedback.
>>>>
>>>> I'm sorry for the misunderstanding. You obviously don't have to replace
>>>> all of the work in DefaultBinding, only do the binding for what you
>>>> consider a boolean type. You will then apply the binding only to the
>>>> relevant columns in the code generator, e.g. by matching on
>>>> <types>CHAR(1)</types>. It shouldn't be too much work.
>>>>
>>>> In any case, I agree that jOOQ should probably add some convenience
>>>> bindings to prevent people from having to do this work themselves. I have
>>>> created:
>>>> https://github.com/jOOQ/jOOQ/issues/4807
>>>>
>>>> In fact, I was thinking of creating a commercial add-on with lots of
>>>> commonly used bindings in them (also for XML, JSON, etc.). Customers of
>>>> the
>>>> Pro and Enterprise Editions would get this add-on for free.
>>>>
>>>> Cheers,
>>>> Lukas
>>>>
>>>> --
>>> 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] <javascript:>.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>
--
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/d/optout.
