Created
July 13, 2016 21:26
-
-
Save saem/49a53ce19951ca8b6918c1088f2e8821 to your computer and use it in GitHub Desktop.
JOOQ MySQL DateTime Converter to handle '0000-00-00 00:00:00', which are returned as null, if configured, by JDBC. The code should be modified to not throw an exception if the field is nullable.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * This code hasn't been tested, but this gives you an idea of what to throw together to get this to work | |
| * | |
| * You also need to go into your jooq generator config and setup the following: | |
| * | |
| * <forcedTypes> | |
| * <forcedType> | |
| * <name></name> | |
| * <expression>(?i:Table.field)</expression> | |
| * <types>(?i:DATETIME(\s*\(\d+\))?)</types> | |
| * </forcedType> | |
| * </forcedTypes> | |
| * | |
| * Might be a good idea to also make one for Date <-> LocalDate | |
| */ | |
| import org.jooq.Binder; | |
| import java.time.Instant; | |
| import java.sql.Timestamp; | |
| import java.sql.SQLException; | |
| import java.util.Optional; | |
| import org.jooq.exceptions.SQLFeatureNotSupportedException; | |
| public final class TimestampToOptionalInstantBinder extends Binder<Timestamp, Optional<Instant>> { | |
| @Override | |
| public Converter<Timestamp, Optional<Instant>> converter() { | |
| return new TimestampToOptionalInstantConverter(); | |
| } | |
| @Override | |
| public void sql(final BindingSQLContext<Optional<Instant>> ctx) throw SQLException { | |
| ctx.value() | |
| .map(v -> ctx.render().visit(DSL.timestamp(Timestamp.from(v)))) | |
| .orElseGet(() -> ctx.render.visit(DSL.val("0000-00-00 00:00:00"))); | |
| } | |
| @Override | |
| public void register(final BindingRegisterContext<Optional<Instant>> ctx) throws SQLException { | |
| ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR); | |
| } | |
| @Override | |
| public void set(final BindingSetStatementContext<Optional<Instant>> ctx) throws SQLException { | |
| ctx.statement().setString(ctx.index(), ctx.value().map("dd-MMM-yyyy HH:mm:ss").orElse("0000-00-00 00:00:00")); | |
| } | |
| @Override | |
| public void set(final BindingSetSQLOutputContext<Optional,Instant>> ctx) throws SQLException { | |
| throw new SQLFeatureNotSupportedException(); | |
| } | |
| @Override | |
| public void get(final BindingGetResultSetContext<Optional<Instant>> ctx) throws SQLException { | |
| ctx.convert(converter()).value(ctx.resultSet().getTimestamp(ctx.index()); | |
| } | |
| @Override | |
| public void get(final BindingGetStatementContext<Optional<Instant>> ctx) throws SQLException { | |
| ctx.convert(converter()).value(ctx.statement().getTimestamp(ctx.index())); | |
| } | |
| @Override | |
| public void get(final BindingGetSQLInputContext<Optional<Instant>> ctx) throws SQLException { | |
| throw new SQLFeatureNotSupportedException(); | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Requires the JDBC connection to convert zero'd dates/datetimes into null | |
| * | |
| * This code hasn't been tested, but this gives you an idea of what to throw together to get this to work | |
| * | |
| * You also need to go into your jooq generator config and setup the following: | |
| * | |
| * <customTypes> | |
| * <customType> | |
| * <name>OptionalInstant</name> | |
| * <type>java.util.Optional<java.time.Instant></type> | |
| * <converter>com.foo.jooq.TimestampToOptionalInstantConverter</converter> | |
| * </customType> | |
| * </customTypes> | |
| * | |
| * Might be a good idea to also make one for Date <-> LocalDate | |
| */ | |
| import org.jooq.Converter; | |
| import java.time.Instant; | |
| import java.sql.Timestamp; | |
| import java.util.Optional; | |
| import org.jooq.exception.DataTypeException; | |
| public final class TimestampToOptionalInstantConverter implements Converter<Timestamp, Optional<Instant>> { | |
| @Override | |
| public Optional<Instant> from(final Timestamp databaseObject) { | |
| return Optional.ofNullable(databaseObject).map(Timestamp::toInstant); | |
| } | |
| @Override | |
| public Timestamp to(final Optional<Instant> userObject) { | |
| return userObject.map(Timestamp::from).orElseThrow( | |
| new DataTypeException("Instant could not be converted to Timestamp: " + userObject.toString()) | |
| ); | |
| } | |
| @Override | |
| public Class<Timestamp> fromType() { return Timestamp.class; } | |
| @Override | |
| @SupressWarnings("unchecked") | |
| public Class<Optional<Instant>> toType() { | |
| final TypeToken<Optional<Instant>> optionalTypeToken = new TypeToken<Optional<Instant>>() {}; | |
| return (Class<Optional<Instant>>) optionalTypeToken.getRawType(); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment