1

I'm using Spring JdbcBatchItemWriter to flush lots of data to a postgres DB.

INSERT INTO customer (id, name, zip) VALUES (?,?,?)

I only set name + zip as I want the id to be auto generated. But how can I let the db autogenerate the id value? If I execute the statement above, the DB complains as the id is not set.

@Entity
class Customer {
   @Id
   @GeneratedValue(strategy=GenerationType.AUTO)
   long id;
}
membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • Don't include the `id` column in your insert statement. Only include name and `zip`. `INSERT INTO customer (name, zip) values (?,?)`. – M. Deinum Mar 19 '14 at 12:53
  • I tried this before of course and it gives the error: `ERROR : PSQLException: ERROR: NULL-value in column ?id? violates Not-Null-Constraint` – membersound Mar 19 '14 at 13:04
  • Create your tables correctly. See http://stackoverflow.com/questions/7718585/set-auto-increment-primary-key-in-postgresql and http://stackoverflow.com/questions/3108777/how-to-auto-increment-in-postgresql for 2 possible solutions. – M. Deinum Mar 19 '14 at 13:07
  • The table is autogenerated by hibernate as I also work with the entities within the application: `CREATE TABLE customer ( id bigint NOT NULL, name character varying(255), zip character varying(255), CONSTRAINT customer_pkey PRIMARY KEY (id));` But I'd also like to make batch imports with the JdbcBatchItemWriter. So I don't want to create the DB tables myself, anyhow I'd like the id to be autogenerated. – membersound Mar 19 '14 at 13:12
  • Letting hibernate auto-generate your schema, for production, is generally a bad idea. I don't believe the `@GeneratedValue` will be added to your create table statement. If your inserts work with plain hibernate/jpa then why not use a `JpaItemWriter` or `Hibernate ItemWriter`? – M. Deinum Mar 19 '14 at 13:33
  • Indeed the `@GeneratedValue` will result in a `SEQUENCE hibernate_sequence` generated. – membersound Mar 20 '14 at 07:03

1 Answers1

1

Got it:

INSERT INTO customer (id, name, zip) VALUES (nextval('hibernate_sequence'), ?, ?)

membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • 1
    You should be aware when changing your database, because i think `nextval` is a custom oracle function – Oussama Zoghlami Mar 19 '14 at 14:24
  • 1
    @OussamaZoghlami No, it's a built-in PostgreSQL function. If you're talking about changing to a different database vendor, then yes, this code is specific to the PostgreSQL dialect, but when you're bypassing Hibernate you're going to be dealing with that anyway. – Craig Ringer Mar 20 '14 at 01:11