how to set H2 primary key id to auto_increment?

i am using sql, H2, and i am trying to make so that the ID of Usertable is auto_incremented. i tried all sql commands in H2 sql interface, didnot work out.

alter table user alter column int not null auto_increment;

this common one is even not working. is there any annotation of JPA for auto_incement may be?

thanks a lot

回答1:

You should use the JPA annotations @Id and @GeneratedValue for your ID.

Your SQL looks valid. Can you post the error message?

回答2:

I had the same problem. I am not sure if the root-cause is the same. But there turned to be a perfectly logical explanation. So here is what I discovered.

First of all, there are at least 2 distinct ways to create auto-incremented keys.

1st way: (xml)

If you work with an xml-based configuration that holds your class info. Then you can put something as follows in your classname.hbm.xml file.

<id name="id">
    <generator class="sequence"><param name="sequence">my_id_seq</param</generator>
</id>

To import this file you will have something like the following in your hibernate.cfg.xml file: (or possibly with a resource attribute)

<!--  Mapping files  -->
<mapping file="classname.hbm.xml"/>

But the important thing here is that it is now actually JAVA that will increment the keys. If you would have checked the sql that was used to generate the table, you would have noticed that it did not hold an auto-incremented field definition for the id column.

2nd way: (annotations)

A totally different way of doing things is to put everything in annotations, like you showed in your question.

@GeneratedValue(strategy=GenerationType.IDENTITY)

in your hibernate.cfg.xml file you will have something like:

<!--  Mapping files  -->
<mapping class="package.subpackage.MyClassName"/>

The GenerationType.IDENTITY is indeed the default value, so you do not have to supply it per se. But anyway this time the table will be generated differently. Namely as follows:

CREATE CACHED TABLE PUBLIC.MYTABLENAME(
    ID INTEGER DEFAULT 
    (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_9DE2A0D5_28F5_488F_9E4C_2878B3CDA72F) 
    NOT NULL NULL_TO_DEFAULT SEQUENCE
    PUBLIC.SYSTEM_SEQUENCE_9DE2A0D5_28F5_488F_9E4C_2878B3CDA72F, 
...
)

Aha! That's interesting. This time the sequence generation will not be performed by JAVA it will be performed by the database itself.

What went wrong:

We are all experimenting and trying things out of course. If you first decided to do it with the xml-files and afterwards you decided to do it with annotations after all. Then of course that means you will have to regenerate your tables as well. If you forget to do so, then you will get errors like doniyor did.

How to fix it:

just add the following line to your hibernate.cfg.xml and reboot your application.

<!-- Drop and re-create the database schema on startup -->
<property name="hibernate.hbm2ddl.auto">create</property>

The table has been destroyed and regenerated.