Transform database values to specific types before them are will available in Result<Record>

575 views
Skip to first unread message

Andrey Antukh

unread,
Nov 20, 2014, 6:10:47 PM11/20/14
to jooq...@googlegroups.com
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

--

Lukas Eder

unread,
Nov 21, 2014, 2:24:12 AM11/21/14
to jooq...@googlegroups.com
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.

--
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.
For more options, visit https://groups.google.com/d/optout.

Lukas Eder

unread,
Nov 21, 2014, 2:35:43 AM11/21/14
to jooq...@googlegroups.com
...

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
    }
}

Andrey Antukh

unread,
Nov 21, 2014, 4:07:38 AM11/21/14
to jooq...@googlegroups.com
Hi Lukas! It is awesome!

Are you publishing snapshots versions of master branch? It would be fantastic have the opportunity to play with it before final release.

Good work.

Regards.
Andrey

Lukas Eder

unread,
Nov 21, 2014, 5:28:44 AM11/21/14
to jooq...@googlegroups.com
The final release is today. You can do last minute cloning of the GitHub repository, if you want :-)

Andrey Antukh

unread,
Nov 24, 2014, 5:07:13 PM11/24/14
to jooq...@googlegroups.com
Hi again Lukas!

I have readed a big amount of source code, but definitively I don't understand how I can use it without automatic code generation. 

It is possible? Can you give me some hints?

Thank you very much.

Regards.
Andrey

Lukas Eder

unread,
Nov 25, 2014, 2:05:42 AM11/25/14
to jooq...@googlegroups.com
Hi Andrey,

You will essentially have to do what the code generator does. You can either achieve this via the internal API TableImpl.createField(), where you can pass Converters and/or Bindings, or you can pass the "converted" DataType instance to your plain SQL Field methods. An example:

Binding<Object, MyType> binding = ...
Field<MyType> field = DSL.field("json_field_name", SQLDataType.OTHER.asConvertedDataType(binding));

The above example is using:

Let me know if you find any issues, or if you have more questions.

Cheers,
Lukas

Andrey Antukh

unread,
Nov 25, 2014, 5:38:15 PM11/25/14
to jooq...@googlegroups.com
Thank you very much Lukas.
That hint helps me a lot and every thing works as expected.

Lukas Eder

unread,
Nov 26, 2014, 5:46:45 AM11/26/14
to jooq...@googlegroups.com, ni...@niwi.be
For completeness' sake (and for future visitors), I've also documented how a JSON / Jackson binding can be achieved on this Stack Overflow question:
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

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 jooq-user+unsubscribe@googlegroups.com.

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 jooq-user+unsubscribe@googlegroups.com.

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 jooq-user+unsubscribe@googlegroups.com.

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 jooq-user+unsubscribe@googlegroups.com.

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 jooq-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages