I will add one note however there seems to be a minor bug/corner case in the SubnetInfo class (they seem to have noticed it but only fixed half way atm)
As of 3.5 common-net they deprecated getAddressCount in favour of getAddressCountLong to be able to get the amount of addresses a netmask covers. This was due to the fact that using a netmask of /0 results in a number larger than the max int value. While all that is well and good, they did not change the toString() to use getAddressCountLong instead, and hence that Binding is still susceptible to that corner case. If you have a guarantee that you won't have fully open netmasks go ahead and use it, but otherwise i would create a simple class yourself for it. On Friday, January 20, 2017 at 11:35:19 AM UTC+1, Lukas Eder wrote: > > Thank you very much for documenting this here. That's greatly appreciated! > > 2017-01-20 11:05 GMT+01:00 <[email protected] <javascript:>>: > >> I had the same scenario for the CIDR field rather than INET (the same >> can be done for it with a differnet field such as INETAddress) >> >> In my case i binding the cidr data type to SubnetUtils.SubnetInfo from >> apache-commons-net >> >> >> package my.package; >> >> >> import org.apache.commons.net.util.SubnetUtils; >> 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; >> >> >> import java.sql.SQLException; >> import java.sql.SQLFeatureNotSupportedException; >> import java.sql.Types; >> import java.util.Objects; >> >> >> /** >> * We're binding <T> = Object (unknown JDBC type), and <U> = >> SubnetUtils.SubnetInfo (user type) >> * @author aaron.axisa >> */ >> public class PostgresCIDRSubnetInfoBinding implements Binding<Object, >> SubnetUtils.SubnetInfo> { >> >> >> @Override >> public Converter<Object, SubnetUtils.SubnetInfo> converter() { >> return CidrConverter.getInstance(); >> } >> >> >> /** >> * The converter does all the conversion >> */ >> private static class CidrConverter implements Converter<Object, >> SubnetUtils.SubnetInfo> { >> >> >> private static CidrConverter INSTANCE = new CidrConverter(); >> >> >> private CidrConverter() { >> // we want spring to override the static INSTANCE when it >> launches >> INSTANCE = this; >> } >> >> >> /** >> * @return The singleton instance for {@link CidrConverter} >> */ >> public static CidrConverter getInstance() { >> return INSTANCE; >> } >> >> >> @Override >> public SubnetUtils.SubnetInfo from(final Object t) { >> // convert the Postgres data type to json >> return t == null ? null : new SubnetUtils(t.toString()). >> getInfo(); >> } >> >> >> @Override >> public Object to(final SubnetUtils.SubnetInfo u) { >> // convert the json to the Postgres data type >> return u == null ? null : u.getAddress()+u.getCidrSignature >> (); // TODO >> } >> >> >> @Override >> public Class<Object> fromType() { >> return Object.class; >> } >> >> >> @Override >> public Class<SubnetUtils.SubnetInfo> toType() { >> return SubnetUtils.SubnetInfo.class; >> } >> >> >> } >> >> >> // Rending a bind variable for the binding context's value and >> casting it to the cidr type >> @Override >> public void sql(BindingSQLContext<SubnetUtils.SubnetInfo> ctx) throws >> SQLException { >> ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql >> ("::cidr"); >> } >> >> >> // Registering VARCHAR types for JDBC CallableStatement OUT >> parameters >> @Override >> public void register(BindingRegisterContext<SubnetUtils.SubnetInfo> >> ctx) throws SQLException { >> ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR); >> } >> >> >> // Converting the SubnetInfo to a String value and setting that on a >> JDBC PreparedStatement >> @Override >> public void set(BindingSetStatementContext<SubnetUtils.SubnetInfo> >> ctx) throws SQLException { >> ctx.statement().setString(ctx.index(), Objects.toString(ctx. >> convert(converter()).value(), null)); >> } >> >> >> // Getting a String value from a JDBC ResultSet and converting that >> to a SubnetInfo >> @Override >> public void get(BindingGetResultSetContext<SubnetUtils.SubnetInfo> >> ctx) throws SQLException { >> ctx.convert(converter()).value(ctx.resultSet().getString(ctx. >> index())); >> } >> >> >> // Getting a String value from a JDBC CallableStatement and >> converting that to a JsonElement >> @Override >> public void get(BindingGetStatementContext<SubnetUtils.SubnetInfo> >> ctx) throws SQLException { >> ctx.convert(converter()).value(ctx.statement().getString(ctx. >> index())); >> } >> >> >> // Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT >> types) >> @Override >> public void set(BindingSetSQLOutputContext<SubnetUtils.SubnetInfo> >> ctx) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> >> >> // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT >> types) >> @Override >> public void get(BindingGetSQLInputContext<SubnetUtils.SubnetInfo> ctx >> ) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> } >> >> >> And then in your pom file for the generation plugin add the following: >> >> <customTypes> >> <customType> >> <name>SubnetInfo</name> >> <type>org.apache.commons.net.util.SubnetUtils.SubnetInfo</type> >> <binding>my.package.PostgresCIDRSubnetInfoBinding</binding> >> </customType> >> </customTypes> >> >> >> <forcedTypes> >> <forcedType> >> <name>SubnetInfo</name> >> <types>CIDR</types> >> </forcedType> >> </forcedTypes> >> >> -- >> 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] <javascript:>. >> 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.
