Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JdbcTemplate and inet data type

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.

like image 851
Amit Goyal Avatar asked Jun 20 '10 18:06

Amit Goyal


2 Answers

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.

like image 151
araqnid Avatar answered Sep 23 '22 09:09

araqnid


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.

like image 34
Peter Tillemans Avatar answered Sep 22 '22 09:09

Peter Tillemans