how to persist data to postgres without having to cast specific data types

Question

In my spring-boot application, I'm receiving json data from an API, and I need to persist this data, without having strongly-typed objects.

Currently, I have my code structured this way:

String sql = "INSERT INTO problem " + "(name, favorite_number, favorite_color) VALUES (?, ?, ?)";

           PreparedStatement ps = c.prepareStatement(sql);

           int counter = 1;
           while( keys2.hasNext() ) {
               String key = (String)keys2.next();

               ps.setObject(counter,  dataValues.get(key));
               counter ++;
           }

ps.executeUpdate();
               ps.close();

The structure of the target postgres table is:

name:  string
favorite_number:  int
favorite_color:  string

Where dataValues is a JSONObject:

JSONObject dataValues = (JSONObject) configParameters.get(2);

Iterator<?> keys = dataValues.keys();

The JDBC driver fails with the following message:

org.postgresql.util.PSQLException: ERROR: column "favorite_number" is of type integer but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

Is there any way to create this INSERT statement in such a way, that not explicit type cast is needed?

Not really. (Relational) databases are strongly typed. Postgres does have a json datatype, but that's not really a solution (unless you want to save json data as-is). — Kayaman
– Kayaman, Commented Oct 25, 2016 at 19:57

davidxxx · Accepted Answer · 2016-10-25 20:37:36Z

JDBC is strict on types used in Prepared Statements.

A way of bypassing that would be to use metadata of the PreparedStatement variable in order to retrieve the type associated to each dynamic parameter.
In your case, if one of them is a number, you can do ps.setInt(...) with the integer returned by Integer.ValueOf(dataValues.get(key))

You can call ps.getParameterMetaData() to retrieve a ParameterMetaData instance.
Then from that instance, you can call getParameterType(int param) or getParameterTypeName(int param) to retrieve the parameter type. Do tests with both to see which one is more suitable.

Unfortunately, it is not sure it works because according DBMS, those metadata utilities are more or less good implemented...
And even if it works, it has a cost.

java.sql.ParameterMetaData

An object that can be used to get information about the types and properties for each parameter marker in a PreparedStatement object. For some queries and driver implementations, the data that would be returned by a ParameterMetaData object may not be available until the PreparedStatement has been executed.

Some driver implementations may not be able to provide information about the types and properties for each parameter marker in a CallableStatement object.

Don't hesitate to do a feedback :)

Collectives™ on Stack Overflow

how to persist data to postgres without having to cast specific data types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related