Using Custom Converters in SelectWhereStep

Question

What is the recommended way of using custom converters in the SelectWhereStep of a query?

I have the following database table:

product
- id INT
- name VARCHAR
- type VARCHAR

Values in the product table look something like

id	name	type
1	Generic Book	paperback_book
2	Today's News	digital_newspaper
3	Hot Magazine	paper_magazine

The important part is the type column, which is a denormalized VARCHAR in the database. It is a concatenated value of a type of book and the medium.

I have a class in Java called ProductType which represents this:

public class ProductMediumType {
    private ProductType type; // This is an enum
    private Medium medium; // This is an enum
    .....
}

I've created an implementation of org.jooq.Converter as defined here: Custom data type conversion

public class ProductMediumTypeConverter extends AbstractConverter<String, ProductMediumType> {}

I want to use this in the SelectWhereStep:

ProductMediumTypeConverter mediumTypeConverter = .....;
ProductMediumType mediumType = ....;


Product prod = create
    .select(
        PRODUCT.ID, 
        PRODUCT.NAME,
        PRODUCT.TYPE.convert(mediumTypeConverter)
    )
    .from(PRODUCT)
    .where(PRODUCT.TYPE.eq(/* HERE */))
    .fetchInto(Product.class);

I know I can use the .to() method of the converter directly, but I don't think that reads well. Is there a better way of doing this?

Also, I know I can create custom converters and use them during code generation, but unfortunately that is not feasible in my case.

Answer 1

I'm assuming you're using jOOQ's code generator, in case of which you can simply attach your converter to your generated code, and now you don't have to do anything anymore. jOOQ will always automatically convert between the database type (e.g. String) and your user type (e.g. ProductMediumType):

// You'll get the right type in your result sets:
Result<Record3<Integer, String, ProductMediumType>> result =
Product prod = create
    .select(
        PRODUCT.ID, 
        PRODUCT.NAME,
        PRODUCT.TYPE                    // Nothing needed here
    )
    .from(PRODUCT)
    .where(PRODUCT.TYPE.eq(mediumType)) // Nothing needed here either
    .fetch();

If you're not using the code generator, or cannot attach the converter to your generated code for any other reason, just attach the converter to your PRODUCT.TYPE field, and reuse that instead:

Field<ProductMediumType> convertedType = 
    PRODUCT.TYPE.convert(mediumTypeConverter);

// Now, use it everywhere:
Product prod = create
    .select(
        PRODUCT.ID, 
        PRODUCT.NAME,
        convertedType
    )
    .from(PRODUCT)
    .where(convertedType.eq(mediumType))
    .fetchInto(Product.class);