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(); } } On Thursday, March 31, 2022 at 8:43:01 AM UTC-6 [email protected] wrote: > The custom binding did the trick. As always you are The Man Lukas! Thanks > again > Regards, > > Max > > > > ᐧ > > On Thu, Mar 31, 2022 at 7:24 AM Lukas Eder <[email protected]> wrote: > >> I was assuming you would be building the statement by hand. Otherwise, >> just use a converter or a binding in your code generator >> >> On Thu, Mar 31, 2022 at 3:20 PM Max Kremer <[email protected]> wrote: >> >>> >>> 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. >>> >>> >>> >>> ᐧ >>> >>> On Thu, Mar 31, 2022 at 12:38 AM Lukas Eder <[email protected]> wrote: >>> >>>> 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/ >>>> >>>> On Wed, Mar 30, 2022 at 11:37 PM [email protected] < >>>> [email protected]> wrote: >>>> >>>>> 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? >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> 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]. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/jooq-user/9f2d879c-caa7-461b-9d5f-979e60ea0159n%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/jooq-user/9f2d879c-caa7-461b-9d5f-979e60ea0159n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>>> 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 >>>> [email protected]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/jooq-user/CAB4ELO6qhwwO9oji%3DjDg04keh8oH5S%3Dk5t4nRFvVcLg6T%3DA19w%40mail.gmail.com >>>> >>>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO6qhwwO9oji%3DjDg04keh8oH5S%3Dk5t4nRFvVcLg6T%3DA19w%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- >>> 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]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/CAEbO6DVuvviSUofnDPwXZ_ame2degF1%3DUW13_TYWfu5X5x8Kiw%40mail.gmail.com >>> >>> <https://groups.google.com/d/msgid/jooq-user/CAEbO6DVuvviSUofnDPwXZ_ame2degF1%3DUW13_TYWfu5X5x8Kiw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- >> 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 >> [email protected]. >> > To view this discussion on the web visit >> https://groups.google.com/d/msgid/jooq-user/CAB4ELO507AODO7YhHmQ%3DRNLNYms40-Ww7j3xQigj938-SotgVw%40mail.gmail.com >> >> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO507AODO7YhHmQ%3DRNLNYms40-Ww7j3xQigj938-SotgVw%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/96bd9277-6987-485a-b5b3-9b8c52309c24n%40googlegroups.com.
