inserting into an (Postgresql 10) interval type column using JPA

[mkr...@trialfire.com]

Hi,

   I have some JSON data represents an eCommerce product subscription, it looks kinda like the example below. I'm trying to insert this data into a Postgres table 

{

  subscriptionID:'1234',

  sku:'12346',

  every: '6 weeks'

}

I have a pojo that reflects the above 

public class Subscription {

      private String subscriptionId;

      private String sku;

      private String every;

//getters and setters...

}

And my table looks similar but uses the interval data type

CREATE TABLE subscription (

    subscription_id     varchar(30),

    sku                          varchar(30),

    every                       interval 

)

We're using JPA with jooq to do bulk inserts so my getters have the annotations like this 

@Column(name="every")

I hoped that using this method I would be able to coerce a string into a interval type so long as the format was recognized by postgres, eg 1 week, 4 months, 3 days etc...

However its not really working. I tried using the org.jooq.types.YearToSecond type but its not exactly a fit as it doesnt support every format of interval that postgres does (for example "6 weeks").

My question is how do insert a string representation of an interval into a postgres interval type column via JPA column mappings? Is there a way to get postgres to cast it somehow?  

[Lukas Eder]

Thanks for your message.

What have you tried, specifically? Did CAST(? AS INTERVAL) not work? Have you considered using a custom data type binding to manually bind your String value to the JDBC API?

https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9f2d879c-caa7-461b-9d5f-979e60ea0159n%40googlegroups.com.

[Max Kremer]

Hi Lukas,

   Where would I be able to do "CAST(? AS INTERVAL)"? I'm using JPA with @column annotation, not building the insert statement by hand, so I;m not sure where to perform the cast.

ᐧ

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/Q24WOOSCBAg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6qhwwO9oji%3DjDg04keh8oH5S%3Dk5t4nRFvVcLg6T%3DA19w%40mail.gmail.com.

[Lukas Eder]

I was assuming you would be building the statement by hand. Otherwise, just use a converter or a binding in your code generator

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAEbO6DVuvviSUofnDPwXZ_ame2degF1%3DUW13_TYWfu5X5x8Kiw%40mail.gmail.com.

[Max Kremer]

The custom binding did the trick. As always you are The Man Lukas! Thanks again

Regards,

Max 

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO507AODO7YhHmQ%3DRNLNYms40-Ww7j3xQigj938-SotgVw%40mail.gmail.com.

[mkr...@trialfire.com]

For reference I'm attaching my custom binding in case anyone runs into this in the future.

package storage.jooq.bindings;

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 org.jooq.impl.DSL;

/**
 * Support for PostgreSQL's INTERVAL datatype adapted from the GSON example at:
 * https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/
 */
@SuppressWarnings("serial")
public class PostgresIntervalBinding implements Binding<Object, String> {

    // The converter does all the work
    @Override
    public Converter<Object, String> converter() {
        return new Converter<Object, String>() {
            @Override
            public String from(Object t) {
                return t == null ? null : t.toString();
            }

            @Override
            public Object to(String u) {
                return u == null ? null : u;
            }

            @Override
            public Class<Object> fromType() {
                return Object.class;
            }

            @Override
            public Class<String> toType() {
                return String.class;
            }
        };
    }
   
    // Rending a bind variable for the binding context's value and casting it to the interval type
    @Override
    public void sql(BindingSQLContext<String> ctx) throws SQLException {
        // Depending on how you generate your SQL, you may need to explicitly distinguish
        // between jOOQ generating bind variables or inlined literals. If so, use this check:
        // ctx.render().paramType() == INLINED
        ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::interval");
    }

    @Override
    public void register(BindingRegisterContext<String> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.OTHER);
    }

    // Converting the JsonNode to a String value and setting that on a JDBC PreparedStatement
    @Override
    public void set(BindingSetStatementContext<String> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
    }

    @Override
    public void get(BindingGetResultSetContext<String> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    @Override
    public void get(BindingGetStatementContext<String> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    @Override
    public void set(BindingSetSQLOutputContext<String> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void get(BindingGetSQLInputContext<String> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
   
}