Hi Stan, You're right, thanks for the updated info. This is a manifestation of the following issue: https://github.com/jOOQ/jOOQ/issues/4556
Currently, when you run an operation of the type Field<BindingType>.operation(BindingType), jOOQ will apply the DataType of the Field, which uses your Binding also to the bind variable that is passed. However, when you already wrap the bind variable using DSL.val() or DSL.inline(), the DataType (and Binding) is not coerced onto the argument. After all, you might want to use a different DataType for that specific bind variable instance. The current workaround here would be to run: db.selectFrom(TRUTH).where(TRUTH.TRUTH_.eq(inline(true, TRUTH.TRUTH_)) This would explicitly set the DataType from TRUTH.TRUTH_ onto the bind variable. I do agree, of course, that this is not the desired behaviour of jOOQ. jOOQ should apply the DataType of the left hand side of an expression also to the right hand side, just as when you pass the unwrapped bind variable. At least as long as you don't *explicitly* specify a DataType as in my suggested workaround, above. I hope this helps. I will increase the priority of #4556, as this seems to be a substantial issue when using Converters and/or Bindings. Cheers, Lukas 2015-12-16 11:26 GMT+01:00 Stanislas Nanchen <[email protected]>: > 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]>: >> >>> 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]>: >>> >>>> 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]. >>>> 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. > -- 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.
