not able to use setobject for custom binding in jooq

Question

public class DestinationCustomBinding implements Binding<Object, Destination>{


    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private final Converter<Object, Destination> converter = new DestinationConverter();

    public Converter<Object, Destination> converter() {
        // TODO Auto-generated method stub

        return converter;
    }

    public void sql(BindingSQLContext<Destination> ctx) throws SQLException {
        // TODO Auto-generated method stub
        Param<Integer> param = DSL.val(ctx.convert(converter).value(),Integer.class );

        ctx.render().visit(DSL.val(ctx.convert(converter).value(),Integer.class ));
    }

    public void register(BindingRegisterContext<Destination> ctx) throws SQLException {
        // TODO Auto-generated method stub
        ctx.statement().registerOutParameter(ctx.index(), Types.JAVA_OBJECT);
    }

    public void set(BindingSetStatementContext<Destination> ctx) throws SQLException {


        ctx.statement().setObject(ctx.index(), ctx.convert(converter).value(), null);
     //      ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter).value(), null));
    }

    public void set(BindingSetSQLOutputContext<Destination> ctx) throws SQLException {
        // TODO Auto-generated method stub
        throw new SQLFeatureNotSupportedException();
    }

    public void get(BindingGetResultSetContext<Destination> ctx) throws SQLException {
        // TODO Auto-generated method stub
         ctx.convert(converter).value(ctx.resultSet().getObject(ctx.index()));
    }

    public void get(BindingGetStatementContext<Destination> ctx) throws SQLException {
        // TODO Auto-generated method stub
        ctx.convert(converter).value(ctx.statement().getObject(ctx.index()));
    }

    public void get(BindingGetSQLInputContext<Destination> ctx) throws SQLException {
        // TODO Auto-generated method stub
        throw new SQLFeatureNotSupportedException();
    }

}

i want to use setObject instead of setString in public void set(BindingSetStatementContext ctx) , But I am getting the following error

Caused by: java.sql.SQLFeatureNotSupportedException: setObject not implemented
    at java.sql.PreparedStatement.setObject(PreparedStatement.java:1291)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.setObject(DefaultPreparedStatement.java:371)
    at com.shn.analytics.db.connection.utils.DestinationCustomBinding.set(DestinationCustomBinding.java:53)
    at org.jooq.impl.DefaultBindContext.bindValue0(DefaultBindContext.java:62)
    at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:127)
    ... 11 more

Use case: I am using crate db which accepts a Json like (not Json) objects without quotes Ex:

create table test_table ( 
  Id Integer, 
  name STRING, 
  test_Object OBJECT
);

insert into test_table(Id, test_Object) 
values (10, 'test_Name', {city = 'random_city'});

How can implement this use case in jooq

Answer 1

In many JDBC drivers, you cannot use PreparedStatement.setObject(index, null) because the JDBC driver needs to know what type of NULL it should serialise over the wire to the server. I don't know what crate.io expects here (it's not an officially supported database in jOOQ), but the usual options are:

// Using String
stmt.setString(index, null);

// Use setNull()
stmt.setNull(index, Types.OTHER);

In the case of the setNull() method, it may well be that there's a vendor-specific Types value, more specific than Types.OTHER.