JdbcTemplate and inet data type

Question

One of my table has the column type as inet. And when I try to perform insert operation using String as the type for inet column, it's saying "column "ip" is of type inet but expression is of type character varying:", which is perfectly valid exception. Now, my question is how do I instruct jdbcTemplate to use inet type instead of String. I'm trying something like:

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("ip",new SqlParameterValue(Types.??, conn.getIPAddress()));

The inet type is not listed in Types class, what shall I pass?

P.S. I'm using PostgresSql version 8.4.4.

Answer 1

You should use Typer.OTHER to let postgresql try to infer the right type.

You can add "stringtype=unspecified" to the JDBC connection parameters. This instructs the driver to let postgres try to infer the right type.

A third possibility is to subclass PGobject. In older driver such a subclass PGinet was provide, but apparently support for this was dropped for one reason or another. The geometric types are still present, but the network types are no longer in the driver package, I think.

Answer 2

One possibility is to add a cast operation around the placeholder in your sql, e.g.:

insert into t(ip) values(?::inet)

This converts the IP address from text to inet server-side instead, so any errors about a badly-formatted address will have to be decoded from the server... or just validate it before inserting and hope.