HSQL: strategy=GenerationType.IDENTITY. for auto-increment?

[H Paul]

	@Column(name = "NUMBER")
    	@GeneratedValue(strategy=GenerationType.IDENTITY) 
	private int number;	

I loaded 10 rows consecutively.
I check the database and it's zero for number.

Am I using the right strategy for auto-increment number?

[kri shan]

What database ? You have to use Sequence for Oracle db.

[H Paul]

From the subject: HSQLDB
Syntax and any thing that I missed.

(I have not tried for other DB like Oracle/DB2/SQLServer. Should not be a problem.)

[Salil Vverma]

Hey H,

How are you creating the corresponding table, via sql or letting hibernate generate the table?
If you are using sql to generate the corresponding table, can you please paste the query used for corresponding table creation ?

[Jaikiran Pai]

Like others have noted, it's the table schema which is important in this case. The schema should be such that, that column's value is auto generated by the DB.

[H Paul]

Educate me :-D (or correct me :-D)

I'm using Spring with Hibernate JPA provider. I do not create the table up front via sql like create table.
I let the table created via java with JPA annotation. The table is created OK but the column number is not auto-incremented when I loaded data.

Spring config:

hibernate.show_sql=false
hibernate.format_sql=false
hibernate.hbm2ddl.auto=create

<property name="jpaProperties">
	<props>
		<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
		<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>	 						
		<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
	</props>
</property>

Domain Model object:

@SuppressWarnings("serial")
@Entity
@Table(name = "CUSTOMER")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "CUSTOMER_TYPE", discriminatorType = DiscriminatorType.STRING)
@DiscriminatorValue("C")
@XmlAccessorType(XmlAccessType.FIELD)
@XmlRootElement
public class Customer implements Serializable {
	@EmbeddedId
	private CustomerId id;
	....
	@Column(name = "NUMBER")
	@GeneratedValue(strategy=GenerationType.IDENTITY) 
	private int number;	
	.....

[H Paul]

From doc:

The GeneratedValue annotation may be applied to a primary key property or field of an entity or
mapped superclass in conjunction with the Id annotation.
The use of the GeneratedValue annotation is only required to be supported for simple primary keys.
Use of the GeneratedValue annotation is not supported for derived primary keys.

So I misread and misunderstand the usage of GeneratedValue since it's for @Id only.
I wanted to have the number generated automatically.

1. So now, possible solution(s):
a. Up front via DDL create Table statement with column number auto-increment.
b. Each time, before we persist the Customer, we manually set the number.
c. Any thing else you see?


A side note: I checked again with another Domain object. It has and worked.

        
        @Id
        @Column(name = "PAYMENT_ID")
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        @Basic
        protected int id;

[H Paul]

Any suggestion/approach to my above questions?

[H Paul]

    From Doc:
    CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    firstname VARCHAR(20),
    lastname VARCHAR(20))
    CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40))
    INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat')
    INSERT INTO movies (starid, movieid, title) VALUES (IDENTITY(), 10, 'Felix in Hollywood')

JPA annotattion: use columnDefinition

	
    @Column(name = "NUMBER", columnDefinition="INTEGER GENERATED BY DEFAULT AS IDENTITY ")
    private int number;	
    
    @Column(name = "NUMBER", columnDefinition="int GENERATED BY DEFAULT AS IDENTITY (start with 1, increment by 1) ")
    private int number;

Don't why it still does not work with JPA annotation.

[Jaikiran Pai]

Have you checked the table schema that gets generated by that annotation, assuming that you indeed let JPA auto generate the schema for you?

[H Paul]

@Column(name = "NUMBER", columnDefinition="INTEGER GENERATED BY DEFAULT AS IDENTITY")
private int number;	


SELECT IS_IDENTITY, IDENTITY_GENERATION, IDENTITY_START, IDENTITY_INCREMENT
FROM "INFORMATION_SCHEMA"."COLUMNS"
WHERE TABLE_NAME ='CUSTOMER'
AND COLUMN_NAME='NUMBER

IS_IDENTITY=YES
IDENTITY_GENERATION=BY DEFAULT
IDENTITY_START=0
IDENTITY_INCREMENT=1

To me, the issue is INSERT but from JAVA or from HSLQDB or something else?

Edit: When I say INSERT, I mean I persist the Customer using java code.
(I'm not using DML INSERT statement directly)

[Jaikiran Pai]

H Paul wrote:[code=java]@Column(name = "NUMBER", columnDefinition="INTEGER GENERATED BY DEFAULT AS IDENTITY")
private int number;


IDENTITY_GENERATION=BY DEFAULT

2 things:

1) You have the number member variable as a primitive int. So by default its value is set to 0
2) Your schema says that the column is identity generation by default which means that if the variable mapped to the column already has a value then the DB will just use it and will not generate a new value for. In this case, the variable does have a value of 0 so the DB just skips the generation.

You have 2 ways to solve this:

1) Change the variable type from int to Integer and let the value be null for that variable when you are persisting
2) Change the identity generation int the schema from by default to always (see this for details http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqlj37836.html). That way, the DB will always generate a value even if the insert query which gets generated by JPA already provides a value for that column. Of course, you'll have to consider your application semantics to decide whether you want to switch the generation type to "always".


Edited: Minor edit - its instead of it's

[H Paul]

Will look into option 2. Option 1 is OK.