Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hi All,

Hibernate version: 3.3.1
Hibernate annotation version: 3.4.0
Hibernate tools version: 3.2.4.beta1
Java Version: 1.5

Name and version of the database you are using: postgresql 8.3.5-2

I am writing an application that needs to support multiple databases. I am trying to add support for PostgreSQL, however I have run into a problem. I am also using Hibernate tools ant task hbm2dll to generate all my db schema's. I am using annotations to map my classes to tables (see below)



This results in the following schema definition:



However what I need to be generated is the following. Note the type of the id field and the individual sequences:



I have solved the individual sequences per table using the idea's suggested here:
http://www.hibernate.org/296.html

But I notice that the default native Id generator class is SequenceGenerator. This works fine for me when I have manually defined my id column to be a serial or bigserial in PostgreSQL. However when using hbm2ddl, the only generator that creates a field as serial/bigserial is the IdentityGenerator. If I explicitly set that as my generation strategy then I get errors later on. Also I think the sequnce generator works well with PostgreSQL and I dont really want to deviate from that if possible.

So finally my questions.

Is the behaviour described above intentional?
Is there a way to use the native Id generator for Postgres (i.e. SequenceGenerator) and be able to generate my schema ddl where my id fields are of type serial/bigserial?
Is there a way I can specify the name of the sequence for my id fields?

Cheers,
Ben

In case anyone is interested. After looking at the Postgres documentation http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL, I ended up resolving my problem by manually creating my schema definition for PostreSQL.



N.B. I renamed my id column to "country_id" from "id" and used my own defined sequence above not the default name (which would be country_country_id_seq).

This will do for now, but I think the that hbm2ddl should be able to generate a serial/bigserial column type for Postgres DB when using the default (SEQUENCE) id generation or at least generate an id column definition like so:

I would be keen to know why this is not the case. I could not figure it out when I quickly tried to trace through the code and if some one could explain to me I would appreciate it.

I have been using the following approach for PostgreSQL without any problem.


where the bm_user_id_seq is the sequence automatically generated by PostgreSQL when you have a table as


I used to create a sequence separately and found the approach could lead to some problems later on.

One question which I would like to ask is :

Does this generate random unique ID?

I need to generate random unique Ids but as till now what I saw, only SequenceGenerator is there in hibernate through which I can get sequential IDs. IS there any way to get random Ids? I can use sort of function to generate random ids my self, but that will be of 16 characters. Can I get random unique numbers of around 10 digits using some generator in hibernate.

Iam using Java, Hibernate, Sql Server 2k8.

Thanks

As its name indicated, a sequence generate a sequence of numbers, which is not a random value. To my knowledge, any DB built-in mechanism doesn't generate a random value.

Hi wei725,

What version of Hibernate and Hibernate tools are using? Are you using the hbm2ddl task to generate your schema? Which Dialect are you using or is it a custom Dialect?

I am using an id generation strategy of "auto", as I need to support multiple DB types, not just PostgreSQL. That being said, from my investigation into the Hibernate code, the default generation strategy for PostgreSQL (PostgreSQLDialect) is "sequence". From what I can tell, the only time an id will be generated with a type of "serial" is if the generation type is "identifier". A type of "sequence" will result in the following output:



Please correct if I am wrong.

I have been using Hibernate since the 3.0 and don't use any Hibernate tools at all. I use the Spring in conjugation with Hibernate.

I don't think that you can use a single set of Hibernate configuration for various DBs. Primary key generation is one of reasons. There are two schools of primary key (or the entity ID in the object world) generation approaches.

If you want to have a primary key in random value, you might not want to use the DB primary key generation since it would not give you a random set of values. To keep your id unique, you need to use the unique constraint.

BTW, the above can be learned by yourself from related documents.

Please rate if it helps.

It seems that I wasted my time here.

Actually you have wasted everyone's time here.

I didn't want to get into this, and at the risk of being rude, but at best you didn't read or understand my question at worst I dont think you understand how hibernate works



Actually I think that is kinda the point of hibernate. To abstract away configuration and connection details used to interact with various databases. Such that if I wanted to support multiple database's I could, without having to recompile my code. Thus the point of delegating the id generation to the native type for that database would be that I don't need to care what that particular database uses for unique identifier generation.

(For anyone still following this thread) From what I have observed, if I use the native id generation for the PostgreSQL dialect (defaults to sequence) then i do not get a sequence associated with the id for that table when I try to generate my schema using hbm2dll, which I believe is not the correct behavior. But this is probably a question for the hibernate developers.

Note that I am generating my schema using hbm2dll (as stated in my original post), something you stated you weren't even using:



So how you could claim in your original response to get auto schema generation to create a data type of "SERIAL" for an id field is a little mystifying.

Also I have never said I want to randomize the id. It goes against the idea of using a sequence. I think some other guy hijacked this thread to ask about randomizing the id.

Lastly, telling me go read the manual, i found a little a little ironic (especially since I actually included links to bits of the manual I researched and I mentioned that i have attempted to trace through the code). I am quite keen to discuss the technology, but to be honest it just seems like you are trolling.

How so?

You mentioned that you were not using hibernate tools, of which the hbm2ddl task is a component. This task is used to auto generate a DB schema based off your Hibernate mappings (either xml or annotations). So I am curious as to how you are able to generate your schema from the hibernate mappings if, as you stated you were not using the hbm2dll task? In particular, I am curious as to how you were able to generate a schema definition where the id column was of type 'SERIAL'. Are you hand crafting your schema? or does your DB already exist? Or are you using some other tool to generate your schema?

Because based on what I have seen it is not possible to use a sequence id generator to get a definition of :


which in PostgreSQL is syntactically equivalent to (see the reference to the postgres docs i mentioned previously)


when using the PostgreSQLDialect.

I should not jump into this question at all in the first place since I don't use hbm2dll. In the environment of a Spring-Hibernate combination, DB schema need to be hand written for some reason at least from the demo samples. That might resolve the "mystery". Sorry for misread the hijacker's response as yours.
BTW, I have read that these are some issues with the big int data type in PostgreSQL. Please let me know whether it works for you or not.