Hi Lukas,
Thanks for your recommendation. I tried your suggested approach using a
custom data type binding but wasn't able to get it working, I believe
because my binding and its associated converter were required to transform
between byte[] and String (byte[] because of the VARBINARY(16) database
type), otherwise the compiler would complain. Here's the updated generated
JOOQ for the table with the Binding:
public class Events extends TableImpl<EventsRecord> {
public static final Events EVENTS = new Events();
// other stuff...
public final TableField<EventsRecord, String> IP_ADDRESS = createField(
"ip_address", org.jooq.impl.SQLDataType.VARBINARY(16),this, "", new
Inet6AtonBinding());
}
This is the current state of my custom Binding class:
package com.example;
import java.sql.*;
import org.jooq.*;
import java.util.Objects;
public class Inet6AtonBinding implements Binding<byte[], String> {
@Override
public Converter<byte[], String> converter() {
return new Converter<byte[], String>() {
@Override
public String from(byte[] databaseObject) {
return databaseObject.toString();
}
@Override
public byte[] to(String userObject) {
return userObject.getBytes();
}
@Override
public Class<byte[]> fromType() {
return byte[].class;
}
@Override
public Class<String> toType() {
return String.class;
}
};
}
// Rendering a bind variable for the binding context's value and casting it
to the json type
@Override
public void sql(BindingSQLContext<String> ctx) throws SQLException {
ctx.render().sql("INET6_ATON(?)");
}
// Registering VARCHAR types for JDBC CallableStatement OUT parameters
@Override
public void register(BindingRegisterContext<String> ctx) throws SQLException {
ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
}
// Converting the String 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));
}
// Getting a String value from a JDBC ResultSet and converting that to a
String
@Override
public void get(BindingGetResultSetContext<String> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.resultSet().getBytes(ctx.index()));
}
// Getting a String value from a JDBC CallableStatement and converting that
to a String
@Override
public void get(BindingGetStatementContext<String> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.statement().getBytes(ctx.index()));
}
// Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
@Override
public void set(BindingSetSQLOutputContext<String> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
// Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
@Override
public void get(BindingGetSQLInputContext<String> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}
And my config to register the binding to the code generator:
<database>
<forcedTypes>
<forcedType>
<userType>java.lang.String</userType>
<binding>com.example.Inet6AtonBinding</binding>
<expression>.*\.(ip_address)</expression>
</forcedType>
</forcedTypes></database>
When I pretty print the SQL from a select with a
.where(EVENTS.IP_ADDRESS.eq("4.2.2.1")) following these instructions, I see
this:
where (
`events`.`ip_address` = INET6_ATON(?)
)
What do I need to change in order to get this working? Thanks!
On Monday, June 24, 2019 at 12:07:58 AM UTC-7, Lukas Eder wrote:
>
> For the record, I've created a feature request to think about this
> particular data type conversion case a bit more thoroughly:
> https://github.com/jOOQ/jOOQ/issues/8842
>
> It's great that the existing Binding can already accomodate 80% of your
> use-case, but it would be much better if you could automatically transform
> your columns using INET6_NTOA as well, if you're projecting them.
>
> On Mon, Jun 24, 2019 at 8:58 AM Lukas Eder <luka...@gmail.com
> <javascript:>> wrote:
>
>> Hi John,
>>
>> You can write your own custom data type Binding and re-implement the
>> sql() method to produce the correct SQL string ("INET6_ATON(?)") for your
>> bind variables. That would make it work for writing to the database using
>> this function (both with insert/updates, and with queries):
>>
>> https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/
>>
>> Using a binding, you can also make sure that your client facing data type
>> is not byte[], but a more reasonable format, e.g. String, or whatever IP
>> Address data type you have.
>>
>> In order to read the type, however, you'd have to convert the byte[] to
>> your type in the client, or call a MySQL function explicitly.
>>
>> I hope this helps,
>> Lukas
>>
>> On Mon, Jun 24, 2019 at 1:15 AM John Bai <jthe...@gmail.com <javascript:>>
>> wrote:
>>
>>> I have an ip_address column with a VARBINARY(16) data type in a mysql
>>> table. I want to store IP addresses in that column using the INET6_ATON
>>> <https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton>
>>>
>>> built-in function provided by mysql. I also want to lookup records by IP
>>> address using that built-in function as well (SQL example: WHERE ip_address
>>> = INET6_ATON('4.2.2.1')"). Here's the docs for that function:
>>>
>>> Given an IPv6 or IPv4 network address as a string, returns a binary
>>>> string that represents the numeric value of the address in network byte
>>>> order (big endian). Because numeric-format IPv6 addresses require more
>>>> bytes than the largest integer type, the representation returned by this
>>>> function has the VARBINARY
>>>> <https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html> data
>>>> type: VARBINARY(16)
>>>> <https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html> for
>>>> IPv6 addresses and VARBINARY(4)
>>>> <https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html> for
>>>> IPv4 addresses. If the argument is not a valid address, INET6_ATON()
>>>> <https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton>
>>>> returns NULL.
>>>>
>>>
>>> Here's a snippet of my generated jOOQ for the table and ip_address
>>> column:
>>>
>>> public class Events extends TableImpl<EventsRecord> {
>>>
>>> public static final Events EVENTS = new Events();
>>>
>>> // other stuff...
>>>
>>> public final TableField<EventsRecord, byte[]> IP_ADDRESS =
>>> createField("ip_address", org.jooq.impl.SQLDataType.VARBINARY(16), this,
>>> "");
>>> }
>>>
>>> *Q1. How can I insert/update records using INET6_ATON('4.2.2.1') as a
>>> value for the IP_ADDRESS field?*
>>>
>>> I have a new EventsRecord instance via EventsRecord eventsRecord =
>>> dslContext.newRecord(EVENTS). There's existing code using this object to
>>> set values for all the fields. How can I set a value
>>> "INET6_ATON('4.2.2.1')"
>>> on my ip_address field? eventsRecord.setIpAddress() method accepts byte[]
>>> only, so I can't pass "INET6_ATON('4.2.2.1')" as a value.
>>>
>>> *Q2. How do I lookup records using INET6_ATON('4.2.2.1') as an equals
>>> value for the IP_ADDRESS field?*
>>>
>>> I tried using EVENTS.IP_ADDRESS.eq("INET6_ATON('4.2.2.1')") but that
>>> doesn't work because the field's eq method expects a byte[] value. I
>>> couldn't figure out from reading the docs if it was possible to get around
>>> this byte[] data type constraint, so I ended up doing this instead and it
>>> worked. Is there a way I can do this better?
>>>
>>> DSL.condition("{0} = INET6_ATON({1})", EVENTS.IP_ADDRESS, "4.2.2.1")
>>>
>>>
>>> --
>>> 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...@googlegroups.com <javascript:>.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/jooq-user/c3c8b7a9-bdc7-41f9-a6d3-7312b7f529cc%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/jooq-user/c3c8b7a9-bdc7-41f9-a6d3-7312b7f529cc%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>> 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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/f90eba4c-cd11-4e97-9207-14c54114c299%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
