...
Below are a couple of examples from our integration tests. Essentially, the
new org.jooq.Binding type will complement the existing org.jooq.Converter
type. It can be associated with all TableFields by the code generator to
allow for a custom JDBC interaction override on a per-column level. I.e.,
not only will you define how to convert between <T> and <U> types
(T=database type, U=user type), but you will also be able to define how
such types are:
- Rendered as SQL
- Bound to PreparedStatements
- Bound to SQLOutput
- Registered in CallableStatements as OUT parameters
- Fetched from ResultSets
- Fetched from SQLInput
- Fetched from CallableStatements as OUT parameters
*A common base class for PostgreSQL's advanced "VARCHAR" types*
import static org.jooq.tools.Convert.convert;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Types;
import org.jooq.Binding;
import org.jooq.BindingGetResultSetContext;
import org.jooq.BindingGetSQLInputContext;
import org.jooq.BindingGetStatementContext;
import org.jooq.BindingRegisterContext;
import org.jooq.BindingSetSQLOutputContext;
import org.jooq.BindingSetStatementContext;
@SuppressWarnings("serial")
public abstract class AbstractPostgresVarcharBinding<U> implements
Binding<Object, U> {
@Override
public void register(BindingRegisterContext<U> ctx) throws SQLException
{
ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
}
@Override
public void set(BindingSetStatementContext<U> ctx) throws SQLException {
ctx.statement().setString(ctx.index(),
convert(ctx.convert(converter()).value(), String.class));
}
@Override
public void get(BindingGetResultSetContext<U> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
}
@Override
public void get(BindingGetStatementContext<U> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
}
@Override
public void set(BindingSetSQLOutputContext<U> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
@Override
public void get(BindingGetSQLInputContext<U> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}
*This type binds PostgreSQL's "json" type to Google's gson library*
import static org.jooq.tools.Convert.convert;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Types;
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 org.jooq.impl.DSL;
import com.google.gson.Gson;
import com.google.gson.JsonElement;
import com.google.gson.JsonNull;
@SuppressWarnings("serial")
public class PostgresJSONGsonBinding extends
AbstractPostgresVarcharBinding<JsonElement> {
@Override
public Converter<Object, JsonElement> converter() {
return new Converter<Object, JsonElement>() {
@Override
public JsonElement from(Object t) {
return t == null ? JsonNull.INSTANCE : new
Gson().fromJson("" + t, JsonElement.class);
}
@Override
public Object to(JsonElement u) {
return u == null || u == JsonNull.INSTANCE ? null : new
Gson().toJson(u);
}
@Override
public Class<Object> fromType() {
return Object.class;
}
@Override
public Class<JsonElement> toType() {
return JsonElement.class;
}
};
}
@Override
public void sql(BindingSQLContext<JsonElement> ctx) throws SQLException
{
ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql(
"::json"); // Custom SQL behaviour here
}
}
*This type implements simple binding for PostgreSQL's "hstore" type,
binding it to Map<String, String>*
import java.io.IOException;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.stream.Collectors;
import org.jooq.BindingSQLContext;
import org.jooq.Converter;
import org.jooq.impl.DSL;
import org.jooq.lambda.Seq;
import org.jooq.tools.csv.CSVParser;
@SuppressWarnings("serial")
public class PostgresHstoreMapBinding extends
AbstractPostgresVarcharBinding<Map<String, String>> {
@Override
public Converter<Object, Map<String, String>> converter() {
return new Converter<Object, Map<String, String>>() {
@Override
public Map<String, String> from(Object t) {
if (t == null)
return null;
try {
String[] kvs = new CSVParser(',').parseLine(t + "");
Map<String, String> result = new LinkedHashMap<>();
for (String kv : kvs) {
String[] split = kv.split("=>");
if (split.length == 2) {
result.put(split[0].replaceAll("^\"?(.*?)\"?$",
"$1"), split[1].replaceAll("^\"?(.*?)\"?$", "$1"));
}
}
return result;
}
catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public Object to(Map<String, String> u) {
return u == null ? null : Seq.seq(u).map(t -> t.v1 + "=>" +
t.v2).collect(Collectors.joining(","));
}
@Override
public Class<Object> fromType() {
return Object.class;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
@Override
public Class<Map<String, String>> toType() {
return (Class) Map.class;
}
};
}
@Override
public void sql(BindingSQLContext<Map<String, String>> ctx) throws
SQLException {
ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql(
"::hstore"); // Custom SQL behaviour here
}
}
2014-11-21 8:24 GMT+01:00 Lukas Eder <[email protected]>:
> Yes - if you wait a bit, we'll be publishing jOOQ 3.5.0 soon, where you
> can take full control of all interaction at the JDBC level for your data
> types via the new org.jooq.Binding SPI. This includes types like JSON,
> HSTORE and many more.
>
> 2014-11-21 0:10 GMT+01:00 Andrey Antukh <[email protected]>:
>
>> Hi folks!
>>
>> I'm relatively new in jOOQ and I have one question.
>>
>> I'm currently working in some kind of "integration" or adding support for
>> custom types (concretely postgresql native types) to suricatta (clojure
>> library of jOOQ).
>>
>> I have some ideas of how handle the input, implementing custom
>> field/querypart:
>> http://www.jooq.org/doc/3.4/manual-single-page/#custom-queryparts. It is
>> pretty clear for me.
>>
>> But I don't know the way for transform values that comes from the
>> database to specific types. One example can be json string to hash map or
>> arrays, convert from jdbc4Array to List.
>>
>> Is jOOQ offers some way to help in this task?
>>
>> Thank you very much.
>>
>> Regards.
>> Andrey
>>
>> --
>> Andrey Antukh - Андрей Антух - <[email protected]> / <
>> [email protected]>
>> http://www.niwi.be <http://www.niwi.be/page/about/>
>> https://github.com/niwibe
>>
>> --
>> 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.
