0

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:

  1. 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.

  1. 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.

1 Answer 1

1

After a lot of hours of debugging I ended up extending PostgreSQL82Dialect and overriding getIdentitySelectString function to remove quotes from table name.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.