CODEWITHSUNDEEP
javaspringjdbcprepared-statement
gantners
gantners

Reputation: 471

Spring JDBC PreparedStatementCreator Invalid Api Usage

I´d like to know how to use the PreparedStatementCreatorFactory. As far as i read it goes like the following code, except that in rare examples not only the sql is given to the factory but also an int[] types, but i dont´t want to pass in hundreds of types, as jdbc usually retrieves the types based on the java types if you pass params as object[]. I´m using Spring 3.1.3 RELEASE.

   public int addEntityAndGetId(String name, String address){
        String sql = "INSERT INTO mytable (NAME,ADDRESS) VALUES (?,?)";
        Object[] params = new Object[]{name,address};
        PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(sql);
        PreparedStatementCreator psc = factory.newPreparedStatementCreator(params);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(psc, generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
   }

But all I get is an org.springframework.dao.InvalidDataAccessApiUsageException.

Do i really need the types? As on every jdbc update statement you can just pass an object[] and its fine.

The purpose of this question is to get around the need to specify any column type and to avoid PreparedStatementSetters on crud queries by letting jdbc determine which column type is appropriate for which object.

Upvotes: 1

Views: 3348

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 148890

You have 2 different errors. One symptom and one cause.

First the symptom : you get a ClassNotFoundException: org.springframework.dao.InvalidDataAccessApiUsageException because you are missing spring-tx in classpath as M.Deinum said. Add it because you will get troubles if you don't.

Next the cause. You create your factory with new PreparedStatementCreatorFactory(sql);. The javadoc says : Create a new factory. Will need to add parameters via the addParameter(org.springframework.jdbc.core.SqlParameter) method or have no parameters

You declare the type of parameters neither in the constructor of the PreparedStatementCreatorFactory, nor in the call to newPreparedStatementCreator(). So Spring issues the exception.

And no, you cannot simply pass an Object[] to an update statement, because the underlying JBDC call will need the actual type of the objects (there are setInt, setString, ... methods).

Now for the fix. As you will pass 2 strings to the update method, you should create the factory this way : 

PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(sql,
    new int[]{Types.VARCHAR, Types.VARCHAR});

(assuming the type of the columns is VARCHAR)

But that's not enough. As you want to get back the generated primary key for the new inserted row, you must declare it : 

factory.setReturnGeneratedKeys(true);

And lastly, if you use PosgreSQL, you could have to use 

generatedKeyHolder.getKeys().get("id");

instead of simply generatedKeyHolder.getKey().intValue() (assuming the name of the column containing the generated key is id)

Upvotes: 2

Related Questions