Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
H Paul
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Ranch Hand
Posts: 1479
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database ? You have to use Sequence for Oracle db.
 
H Paul
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 257
Hibernate Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.




Domain Model object:

 
H Paul
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
H Paul
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any suggestion/approach to my above questions?
 
H Paul
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


JPA annotattion: use columnDefinition

Don't why it still does not work with JPA annotation.
 
Jaikiran Pai
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 471
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Will look into option 2. Option 1 is OK.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic