what if no ID column in the table for Hibernate?

[Simpson Kumar]

we don't have ID column and primary key coulmn in most of the tables in the DB, but for Hibernate we need at least one ID primary key,

What I have to do without ID column and primary key in DB to test the Hibernate?

[Paul Sturrock]

Its not juts Hibernate - a relational datamodel require primary keys. So what you've got is a broken data model because without a primary key it can't be relational, and this is why its difficult to use with an ORM.

You can fix this by defining a surrogate key.

You can (possibly) work round this by mapping tables without primary keys to use all fields as a composite key. I say possibly because this requires all rows to be unique which may not be the case.

[Simpson Kumar]

Paul Sturrock wrote:Its not juts Hibernate - a relational datamodel require primary keys. So what you've got is a broken data model because without a primary key it can't be relational, and this is why its difficult to use with an ORM.

Actually I'm using HSQLDB and it does already some tables, and there is no PK in each table.

Paul Sturrock wrote:You can fix this by defining a surrogate key.

I never work on surrogate key and not even heard about that. could you please give me tips how to do..

Paul Sturrock wrote:
You can (possibly) work round this by mapping tables without primary keys to use all fields as a composite key. I say possibly because this requires all rows to be unique which may not be the case.

How can we do this also?

[Paul Sturrock]

Actually I'm using HSQLDB and it does already some tables, and there is no PK in each table.

Strictly, those tables have been badly defined. They cannot contain relational data, which is fine if the entities represented by them have no relationships and it doesn't matter you can't uniquelly identify rows, but it does cause difficulties using any tool that expects data to be relational (and Hibernate is one of those tools).

I never work on surrogate key and not even heard about that. could you please give me tips how to do..

A surrogate key is a key that has no business meaning. A key which has business meaning is risky. The logic of the business rules can change and hence the key could change, which breaks one of the defining features of a primary key: that it is immutable. So a surrogate key is usually the best candidate key to use.

Surrogate keys are usually assigned by the database itself, e.g. Oracle's sequences or MySQL's increment data type. HSQLDB should have a simmilar mechanism. Have a read through the docs.

The work around I suggested is only one to consider if there is no way to update the data model. If you are sure it is the way to go, have a read of the Hibernate documentation's composite-id mapping. Personally I wouldn't go down this route at all though.

[Simpson Kumar]

I created table with id field as a PK in HSQLDB in a PUBLIC schema, and inserted 2 records. When I tried to returned the data I got empty records. Is there anything wrong with HSQLDB?

[Paul Sturrock]

Is there anything wrong with HSQLDB?

There are doubtless bugs, but not one that blatant!

Did you commit your transaction after the insert?

[Simpson Kumar]

Yes I commit the TX after I inserted.

How to call the queries with schema in hibernate? like this

   ResultSet result = session.connection().createStatement().executeQuery("select * from INFORMATION_SCHEMA.System_Aliases"); 

When I ran the query without schema, I couldn't get the results.

  
ResultSet result = session.connection().createStatement().executeQuery("select * from System_Aliases");  

If I run with Schema, then I would get.

I think in the same in hibernate, If I call

List list =  session.createQuery("from SystemAliases").list();    

, I don't see results.
We must use schema in hibernate too.. so how to organize them

[chris webster]

Hi Sudhakar,

Sounds like you have several different database-related problems here.

1. Primary keys:

As Paul says, you must have a primary key for each table in a relational database. This can be a "surrogate" key i.e. simple numeric ID generated by the database (which is how Hibernate prefers it) or a "natural" key such as a person's social security number i.e. one or more items of business data that uniquely identify a record in your table. If you don't have a PK, Hibernate will have problems identifying which record you are working on etc.

The easiest solution is probably to add an ID column and let the database/Hibernate populate it automatically for new records (you may need to set the ID for existing rows separately). Or you can find out what the "natural" (business) key is for the records in your table, then define this as a primary key constraint in your database and also make it the PK in your Hibernate mappings. Even if you use an ID as the PK, you should at least identify your natural keys and define a unique constraint (in the database) for each one if possible.

Either way, if you don't have a PK for each table, your database design is incomplete (and probably wrong).

The basic rules for identifying your keys are:

i) The key: each record must have some unique set of one or more attribute values to distinguish it from other records.
ii) The whole key: each attribute in the key must be relevant for uniquely identifying the record.
iii) Nothing but the key: no other attributes in the record are relevant for uniquely identifying the record.

Easy to remember!

2. Schemas:

Most RDBMS have separate schemas for system data and for the data owned by different users. If you don't specify which schema a table is in (i.e. the data owner), the RDBMS may not know where to find it. If you can specify the default schema name to use when you create your DB connection (or Hibernate session), then you should be able to leave the schema name out of your SQL. This approach will also make it easier to change the schema name e.g. from a "Dev" schema to a "Test" schema for testing etc. But you'll need to check the docs for Hibernate and for your database (I'm an Oracle guy so I don't know much about HSQLDB or Hibernate).

Hope this helps!

[Simpson Kumar]

Thank you Paul/Chris for your patience to reply to my questions..
I got sufficient information from you, but here one thing still I got confusion with HSQLDB. Here in this db all tables are read only and no ID colimns (PK). Ok when read the data from JDBC code, I can get all records, when I read the data through hibernate I can't get even single records (not even throwing the exceotion), how wounder it is with HSQLDB.

I'm still struck with this issue

[Paul Sturrock]

Here in this db all tables are read only

Typically there is no such thing as a read only table in a relational database. Usually the user accessing the database has been granted permissions for a table, so its the user not the table I'd check. What permissions do they have? The only type of object that is read only in a database is something like a non-updatable view.

and no ID colimns (PK)

Can you not give this table a surrogate key and use that?


The schema and table names you mention sound a lot like the database's metadata. Are thye? If so, why are you trying to update it yourself?

[chris webster]

Hi Kumar,

Which user/schema owns your application's tables? It sounds like they may have been put in the system schema by mistake, instead of in a separate application schema, so you may need to move them. You may need to create a user/schema, grant it privileges to create tables, indexes and constraints, then re-create your tables (with PKs) in this schema. Check the HSQLDB documentation or get your DBA to help with this.

Which user are you using to connect to your database? This should be a user that owns (or has access to) these tables, not the system user/schema. Normally, you would have one user that owns the tables i.e. the user who created them, plus another user who can SELECT/INSERT/UPDATE/DELETE the data in these tables, which you would use for your application's database connection. This allows you to restrict what the application user can do e.g. you might not give this user permission to delete data, or you might set up a user who can only SELECT data, but cannot change anything.

But right now, just focus on making sure the tables are in a non-system user's schema, and then connect to the database via that user.

Please give your tables a primary key. They are not properly defined if you do not do that, and Hibernate will have problems identifying records uniquely if you cannot tell it how to identify the records. This is not an optional feature, it is fundamental to how relational databases work.

Basically, Hibernate needs to know the correct user/schema to connect to, and how to identify individual records uniquely in the database tables inside that schema. Right now, it seems like neither of these things is properly defined, which is probably why you are having problems.

You really need to get some help from a competent database designer or your DBA to resolve these issues, because from what you tell us, your current database design is not a proper basis for developing any kind of database application. And get a book on basic relational database design so you can spot these problems when somebody else gives you a bad database design in future e.g. having no keys is a really big warning sign!

Good luck!