How to add parameters in PreparedStatement in Postgresql JDBC without manually setting the types

Question

Is this possible? We're using a PreparedStatementSetter, and the code is littered with statements like preparedStatement.setString(4,"string");

We have a lot of columns, so this is unfortunate. It seems like it ought to be possible via the various metadata JDBC methods, and therefore I assume it has already been done (only half-joking).

We're on Spring 3.0 and PGJDBC 9.1-903

Yeah, basically just JdbcTemplate, although I could probably shoehorn in SimpleJdbcTemplate — alex
– alex, Commented Feb 6, 2014 at 18:56

Luiggi Mendoza · Accepted Answer · 2014-02-06 16:40:11Z

2

Use PreparedStatement#setObject and let the JDBC driver identify the type of the parameter by itself.

For example, doing

preparedStatement.setObject(4,"string");

Will execute, in the end

preparedStatement.setString(4,"string");

You can even create a method to insert the parameters for you. Here's a basic example:

public void addParameters(PreparedStatement pstmt, Object ... parameters)
    throws SQLException {
    int paramIndex = 1;
    for (Object param : parameters) {
        pstmt.setObject(paramIndex++, param);
    }
}

And just set the parameters by:

PreparedStatement preparedStatement = ...
addParameters(preparedStatement, 1, "string", new java.util.Date());

answered Feb 6, 2014 at 16:40

Luiggi Mendoza

85.9k16 gold badges159 silver badges354 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alex Over a year ago

And the overhead for setObject as opposed to setSomeNativeType is paid only once, in the statement creation - is that correct?

Luiggi Mendoza Over a year ago

@alex I've never suffered for an overhead in my apps using this approach. If you have a bottleneck, use a profiler to spot it instead of suppose where the problem may lie.

Collectives™ on Stack Overflow

How to add parameters in PreparedStatement in Postgresql JDBC without manually setting the types

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related