JOOQ 3.7.13 IN condition wrong mapping, or wrong use?

[Alessandro Brambilla]

Hi group, 

I've encountered a strange behaviour with IN condition and custom converter/forcedType. 

First, Environment: 

Jooq 3.7.13 (Pro FWIW)

DB: Oracle 19.0

Java: JDK 21

Spring Boot: 2.7.16

Let's Say I have a table with a varchar column that need to be custom maaped to a Set<MyEnum> like: 

public final TableField<MyTableRecord, Set<MyEnum>> MY_COL= createField(DSL.name("MY_COL"), SQLDataType.VARCHAR(20), this, "My custom Column", new MyEnumConverter());

with MyEnum be like (simplified without  extra checks): 

public enum MyEnum{

ENUM_1("A"),
EMUM_2("B");

public static final String ALL = "all";
private String code;

MyEnum(String code){
this.code = code;
}

public String getCode(){
return this.getCode();
}

public static Set<MyEnum> fromCode(final String from) {
if (ALL.equals(from)){
return Set.copyOf(Arrays.asList(MyEnum.values()));
}else{
return Set.of(MyEnum.valueOf(from));
}
}

}

The column on DB should contains the code value if there is just one in the Collection or the placeholder "ALL"  if collection.size() > 1.

To do so I use my custom datatype converter like this (simplified without  extra checks): 

public class MyEnumConverter implements Converter<String, Set<MyEnum>> {

@Override
public Set<MyEnum> from(String myEnumString) {
if (myEnumString == null){
return Set.of();
}else {
return MyEnum.fromCode(myEnumString);
}
}

@Override
public String to(Set<MyEnum> enumSet) {
if (enumSet.size()>1){
return MyEnum.ALL;
}else{
return enumSet.stream().findFirst().map(MyEnum::getCode).orElseThrow();
}
}

@Override
public Class<String> fromType() {
return String.class;
}

@Override
public Class<Set<MyEnum>> toType() {
Set<MyEnum> s = Set.of(MyEnum.ENUM_1);
return (Class<Set<MyEnum>>) s.getClass();
}


}

My Issue rise when I need to do a IN condition. I would expect I can use

Set<MyEnum> enums = Set.of(MyEnum.ENUM_1,MyEnum.EMUM_2);
Condition inCondition = Tables.MY_TABLE.MYCOL.in(enums);

unfortunately this will lead to a sql condition like:

MY_COL in ("ENUM_1","ENUM_2") while I was expecting a condition like  MY_COL in ("A","B")

what am I missing?

I have debuggeed it a bit and find a code I don't understand in org.jooq.impl.ConvertedDataType<T,U> class in method convert where it does: 

@Override
public final U convert(Object object) {
if (getConverter().toType().isInstance(object))
return (U) object;

// [#12155] Avoid double conversion passes between Result and custom List<UserType>
else if (delegate.isMultiset() && !(object instanceof Result))
return (U) object;

// [#12413] Avoid double conversion passes between Record and custom object types
// - List is what we produce when reading XML or JSON nested data in standard SQL
// - Map is what we produce in SQL Server (which doesn't support JSON_ARRAY)
else if (delegate.isRecord() && !(object instanceof Record || object instanceof List || object instanceof Map))
return (U) object;

// [#3200] Try to convert arbitrary objects to T
else
return ((Converter<T, U>) getConverter()).from(delegate.convert(object));
}

the last line looks strange to me.. why the converter.from() method? should it be the converter.to()? 

Or better, if  (of course) the class is correct for what it does.. is correct to use it to parse in conditions values?

Of course I can work around by change the in condition like: 

Condition inCondition = Tables.MY_TABLE.MYCOL.in(enums.stream(MyEnum::getCode).collect(Collectors.toSet()));

but it looks to me like a manual redundant double converions.

Or maybe I should use a Collection<Set<MyEnum>> in the in condition, since the mapped type is Set<Enum>?

Please, help me to get it right. thank you

Alessandro

[Lukas Eder]

Hi Alessandro

Thank you for your message. For historic reasons, Field.in(Collection<?>) isn't type safe. This is because of erasure, we can't have both Collection<? extends T> and Collection<? extends Field<T>> arguments. This is why the Java compiler didn't tell you about your mistake. The IN predicate already accepts a Collection<T>, so you can "safely" pass a Set<MyEnum>. But that's the wrong type - you should have passed a Collection<Set<MyEnum>>, e.g.:

Set.of(Set.of(MyEnum.ENUM_1), Set.of(MyEnum.ENUM_2))

Does this make sense?

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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f21d7934-940c-4416-bf21-f5375e718197n%40googlegroups.com.

[Alessandro Brambilla]

I see, and it make sense, since in the end the "java" type (<U>) of this mapped column is Set<MyEnum>, a "in" would require a Collection<U>, so a Collection<Set<MyEnum>>. Not the cleanest code in the world.. but it make sense.. I would give it a try.

As a side note, if I pass a Collection<String> so instead of Collection<MyEnum> I give a Collection<MyEnum>.map(MyEnum::getCode).collect(Collector.toSet()) - basically I manually do what the custom converter should do - it works.. even if I would expect to require a  Collection<Set<String>>. But maybe damn type erasure is at work once again here...

Anyway. Thank you so much.

Alessandro