Search code examples
javapostgresqlspring-bootnativequery

How to make db handle auto insert sequence id postgesql

I had an existed entity (TableEntity.java), the table existed in db, and also the data Here is how the column id already delcared in TableEntity.java

@Id
@SequenceGenerator(name = "table_name_id_seq", sequenceName = "table_name_id_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "table_name_id_seq")
@Column(name = "id", nullable = false, updateable = false)
private int id;

In the database, I look at the properties of the table, for id column, the data type is int and set as Not NULL The previous code, for inserting data to db, is only doing "repository.save()" But now, I want to use PreparedStatement to insert data to db per batch Here is how I create the query

String query = "INSERT INTO table_name (column1, column2) values (?, ?)";

the problem is, when the query executed, its violates null constraint for the id column. How to make the database can handle my column id? Since, when I just use repository.save() is work fine, but when I use that (could I say it a native query?) query, that exception appear. How to solve this? or any reference to solve this?

Solution

  • Set a default value for the column:

    alter table table_name
   alter column id set default nextval('table_name_id_seq');

    Now if an INSERT statement does not supply a value for the id column (by not mentioning it), the next sequence value is used to populate the column.