How can I store and lookup IP addresses in a VARBINARY(16) column using mysql's built-in function INET6_ATON with jOOQ?

46 views
Skip to first unread message

John Bai

unread,
Jun 23, 2019, 7:15:16 PM6/23/19
to jOOQ User Group
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 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 data type: VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. If the argument is not a valid address, 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")


Lukas Eder

unread,
Jun 24, 2019, 2:58:24 AM6/24/19
to jOOQ User Group
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):

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

--
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/c3c8b7a9-bdc7-41f9-a6d3-7312b7f529cc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Lukas Eder

unread,
Jun 24, 2019, 3:07:58 AM6/24/19
to jOOQ User Group
For the record, I've created a feature request to think about this particular data type conversion case a bit more thoroughly:

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. 

John Bai

unread,
Jun 25, 2019, 9:30:53 PM6/25/19
to jOOQ User Group
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:

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> 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):

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> 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 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 data type: VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. If the argument is not a valid address, 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.

Lukas Eder

unread,
Jun 26, 2019, 3:09:37 AM6/26/19
to jOOQ User Group
On Wed, Jun 26, 2019 at 3:31 AM John Bai <jthe...@gmail.com> wrote:

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!


That looks like what I understood you wanted. What's not working about this? 

jb...@squareup.com

unread,
Jun 26, 2019, 5:02:29 PM6/26/19
to jOOQ User Group
So I debugged a bit more using a where EVENTS.IP_ADDRESS.eq("4.2.2.1") condition and identified an issue in my Binding's set(BindingSetStatementContext<String> ctx) method.

Here's how I originally defined that method:
  @Override
  public void set(BindingSetStatementContext<String> ctx) throws SQLException {
    ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
  }

I need to provide a String representation of the value to ctx.statement.setString(). The string value I ultimately want to provide is "4.2.2.1". However, using Objects.toString(ctx.convert(converter()).value(), null) produces a string like "[B@329910d0". This kind of string is what you get when calling toString() on a byte[].

I felt like I didn't need to involve the converter at all since the ctx already has the String value I want to pass to setString, so I just did this:

@Override
public void set(BindingSetStatementContext<String> ctx) throws SQLException {
  ctx.statement().setString(ctx.index(), ctx.value());
}

So now my where conditions are working with this Binding. Here's the updated state of it with more overridden methods simply throwing SQLFeatureNotSupportExceptions:

package com.example;

import java.sql.*;
import org.jooq.*;

public class Inet6AtonBinding implements Binding<byte[], String> {

  @Override
  public Converter<byte[], String> converter() {
    return new Converter<byte[], String>() {
      @Override
      public String from(byte[] bytes) {
        return new String(bytes);
      }

      @Override
      public byte[] to(String string) {
        return string.getBytes();
      }

      @Override
      public Class<byte[]> fromType() {
        return byte[].class;
      }

      @Override
      public Class<String> toType() {
        return String.class;
      }
    };
  }

  @Override
  public void sql(BindingSQLContext<String> ctx) throws SQLException {
    ctx.render().sql("INET6_ATON(?)");
  }

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

  @Override
  public void set(BindingSetStatementContext<String> ctx) throws SQLException {
    ctx.statement().setString(ctx.index(), ctx.value());
  }

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

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

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

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

Interested to hear any feedback about my choice to throw exceptions for most of the overridden methods and my approach of not using the converter at all.
Lukas Eder
unread,
Jun 27, 2019, 4:04:53 AM6/27/19
to jOOQ User Group
Ah yes, thanks for the update. I had overlooked that you applied the converter also for binding. It is only needed for fetching, as your binding isn't really a client side two way conversion utility - that was the whole point of it.

You can of course throw exceptions for all other operations. But then again, eventually, you will want to read the `ip_address` value, and you'll run into an exception being thrown from one of the get() methods. I do wonder if perhaps re-implementing the ATON / NTOA conversion functions in Java wouldn't be a more straightforward solution here?

Thanks,
Lukas



-- 

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/1aa3f828-5db3-479a-bd84-02c80818922e%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages