Hi Lukas,
I written data bindings as in the code attached files, can you tell me how
to use the implemented conversion below.
My code is as follows.
MyRecord r = dslCtx.insertInto(PGTABLE1)
.set(PGTABLE1.JSON_COLUMN, PGTABLE1.JSON_COLUMN)
.returning().fetchOne()
Getting the following error.
"message": "ERROR: column \"before\" is of type jsonb but expression is of
type character varying\n Hint: You will need to rewrite or cast the
expression.\n Position: 238",
Can you help here
Thanks
Madhu
On Monday, 12 November 2018 16:34:51 UTC+8, Lukas Eder wrote:
>
> Hi Madhu,
>
> Thanks for your message. You will need to configure a data type binding on
> your POSTGRES_TABLE1.REQUEST column through the code generator as explained
> here:
>
> https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings
>
> You can then bind any client representation to the PostgreSQL JSONB data
> type. I wouldn't use org.jooq.tools.json.JSONObject, if I were you, but
> some other third party JSON library.
>
> I hope this helps,
> Lukas
>
> On Sat, Nov 10, 2018 at 4:46 AM Madhu Mohan <[email protected]
> <javascript:>> wrote:
>
>> Hi Lukas,
>> When i tried with JOOQ,
>> myClass r = create.insertInto(POSTGRES_TABLE1)
>> .set(POSTGRES_TABLE1.CREATEDDATE,
>> currentTimestamp())
>> .set(POSTGRES_TABLE1.REQUEST,
>> DSL.val(activity.request, JSONObject))
>> .returning().fetchOne()
>>
>> In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But
>> unable to insert data using JOOQ, getting the following error.
>> Error:
>> Type class org.jooq.tools.json.JSONObject is not supported in dialect
>> DEFAULT
>>
>> --
>> 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.
package Bindings;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Types;
import java.util.Objects;
import org.jooq.Binding;
import org.jooq.BindingGetResultSetContext;
import org.jooq.BindingGetSQLInputContext;
import org.jooq.BindingGetStatementContext;
import org.jooq.BindingRegisterContext;
import org.jooq.BindingSQLContext;
import org.jooq.BindingSetSQLOutputContext;
import org.jooq.BindingSetStatementContext;
import org.jooq.Converter;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.JsonNodeFactory;
public abstract class AbstractPostgresBinding implements Binding<Object, JsonNode> {
@Override
public Converter<Object, JsonNode> converter() {
return new Converter<Object, JsonNode>() {
@Override
public JsonNode from(Object t) {
try {
return t == null ? JsonNodeFactory.instance.objectNode() : new ObjectMapper().readTree(t.toString());
} catch (IOException e) {
return JsonNodeFactory.instance.objectNode();
}
}
@Override
public Object to(JsonNode u) {
return u == null || u == JsonNodeFactory.instance.objectNode() ? null : u.toString();
}
@Override
public Class<Object> fromType() {
return Object.class;
}
@Override
public Class<JsonNode> toType() {
return JsonNode.class;
}
};
}
// Rending a bind variable for the binding context's value and casting it to which ever type
public abstract void sql(BindingSQLContext<JsonNode> ctx) throws SQLException;
// Registering VARCHAR types for JDBC CallableStatement OUT parameters
@Override
public void register(BindingRegisterContext<JsonNode> ctx) throws SQLException {
ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
}
// Converting the JsonNode to a String value and setting that on a JDBC PreparedStatement
@Override
public void set(BindingSetStatementContext<JsonNode> ctx) throws SQLException {
ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
}
// Getting a String value from a JDBC ResultSet and converting that to a JsonNode
@Override
public void get(BindingGetResultSetContext<JsonNode> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
}
// Getting a String value from a JDBC CallableStatement and converting that to a JsonNode
@Override
public void get(BindingGetStatementContext<JsonNode> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
}
// Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
@Override
public void set(BindingSetSQLOutputContext<JsonNode> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
// Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
@Override
public void get(BindingGetSQLInputContext<JsonNode> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}
package Bindings;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.JsonNodeFactory;
import java.io.IOException;
import java.sql.SQLException;
import org.jooq.BindingSQLContext;
import org.jooq.impl.DSL;
import org.jooq.Converter;
import com.fasterxml.jackson.databind.ObjectMapper;
/**
* Custom data binding for JSONB data type in postgres using JOOQ.
* Details at: https://www.jooq.org/doc/3.9/manual/code-generation/custom-data-type-bindings/
*/
public class PostgresJsonBinding extends AbstractPostgresBinding {
@Override
public Converter<Object, JsonNode> converter() {
return new Converter<Object, JsonNode>() {
/**
* This overrides parent behavior and returns null rather then "{}" when null is found
*/
@Override
public JsonNode from(Object t) {
try {
return t == null ? null : new ObjectMapper().readTree(t.toString());
} catch (IOException e) {
return JsonNodeFactory.instance.objectNode();
}
}
@Override
public Object to(JsonNode u) {
return u == null || u == JsonNodeFactory.instance.objectNode() ? null : u.toString();
}
@Override
public Class<Object> fromType() {
return Object.class;
}
@Override
public Class<JsonNode> toType() {
return JsonNode.class;
}
};
}
@Override
public void sql(BindingSQLContext<JsonNode> ctx) throws SQLException {
// Rendering a bind variable for the binding context's value and casting it to the json type
//Allows for null values to be stored in DB
if (ctx.convert(converter()).value() == null) {
ctx.render().visit(DSL.val(ctx.convert(converter()).value(), JsonNode.class));
} else {
ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::json");
}
}
}
