I am developed Rest API with sprint boot, jpa, hibernate and PostgreSQL. My goal is to be able to generate auto-incremented id for using with code and using database tool such as D Beaver without writing any extra queries for getting next ID value and etc.
I have created entity User. I tried generating id in two ways:
- GenerationType.IDENTITY
When using GenerationType.IDENTITY it successfully creates table with name user, sequence with name user_id_seq and adds a default value for user.id column nextval('user_id_seq'::regclass). Everything in database is as expected and it works great with database tool, but problem occurs when I am trying to insert new row from my API. When trying to insert new row hibernate executes query
select currval('user_id_seq')
to get id value and I am getting error
org.postgresql.util.PSQLException: ERROR: invalid name syntax
because of those quotes around user. It should execute
select currval('user_id_seq')
I believe that the problem here is because I use table name user which is a reserved keyword, but I want to keep it this way because this naming matches other tables pattern.
- GenerationType.SEQUENCE
If I use annotations:
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_generator")
@SequenceGenerator(name="user_generator", sequenceName = "user_id_seq", allocationSize=1)
it creates table 'user', sequence 'user_id_seq' but doesn't add user.id column default value, so I can't insert new rows using database tool without specifying id value. But using this generation type my API works fine.
It is also worth mentioning that I am using spring.jpa.hibernate.ddl-auto=create-drop and manually dropping and recreating schema each time so there wouldn't be any unnecessary sequences/tables left.
@Entity
@Table(name = "`user`")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// other properties...
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
// other getters and setters...
}
So... It is possible to somehow connect those two ways and create one working solution? I need to have default value for column and that hibernate would also know how to generate that id.
P.S. I don't want to change table/entity naming or execute SQL to correct tables when running application. I believe that there should be a better approach.