How to use auto-generated @Id in PreparedStatement?

Question

I'm using a PreparedStatement for batch inserting values into a postgres database, as follows:

JdbcBatchItemWriter<FieldSet> w = new JdbcBatchItemWriter<>();
w.setDataSource(ds);
w.setSql("INSERT INTO my_table(firstname,lastname) VALUES(?,?)");
w.setItemPreparedStatementSetter(new ItemPreparedStatementSetter() {
    //pseudocode
    ps.setString(1, "test");
    ps.setString(2, "testname");
});

Problem: my entity class also has an auto generated @Id, which is not set here.

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    private String firstname, lastname;
}

As a result, this causes the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: NULL-value in column ?id? violates Not-Null-Constraint.

Of course, as I didn't set the id. But how can I let it be auto generated?

does your table reflect the postgres standard for auto generated? see http://stackoverflow.com/a/13107516/62201 if not, why not? and how do you want the id to be generated? — Michael Pralow, Nov 05 '15 at 10:27

score 2 · Answer 1 · edited May 23 '17 at 11:44

2

Since you are dealing with a JPA entity, you can't use JdbcBatchItemWriter, who is not aware of JPA annotations, but JpaItemWriter instead.

A sample configuration would be:

<bean id="jpaItemWriter" class="org.springframework.batch.item.database.JpaItemWriter">
    <property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>

where entityManagerFactory is your entity manager factory bean. Then you can use this ItemWriter directly or inject it as a delegate in another ItemWriter.

If you want to do batching with JPA, please refer to this question.

edited May 23 '17 at 11:44

Community

1
1

answered Nov 05 '15 at 09:21

Tunaki

132,869
46
340
423

I cannot use `JpaItemWriter` as I have to insert a few million rows into the database, and `JpaItemWriter` is too slow for this opteration (about 4times slower than `JdbcBatchItemWriter`). – membersound Nov 05 '15 at 09:26
2

@membersound Then you should have stated it in the question instead of downvoting my answer because I proposed it (I'm not in your head). On another note, see [this answer](http://stackoverflow.com/a/10584717/1743880) for batching with JPA. – Tunaki Nov 05 '15 at 09:31
I really appreciate your help. Anyways the question is "how to use an auto-generated in in PreparedStatement". I cannot switch to JPA batching as this is too slow! – membersound Nov 05 '15 at 10:14
@membersound And I answered, "you cannot do that, switch to `JpaItemWriter` instead". I don't think telling "you really can't do that, do this instead" is worth a downvote... If JPA batching is too slow for your requirements, you have to drop JPA and your entities and make your inserts by hand: set a sequence on the DB and select that to get your generated id. – Tunaki Nov 05 '15 at 10:17

How to use auto-generated @Id in PreparedStatement?

1 Answers1