Hibernate and stored procedures

Question

As my understanding on setting hibernate, I need to create

• table meta data file (person.hbm.xml), include all the fields mapping
• java object (person.java)

If we use stored procedures for all transaction, do we still need the above configuration?

It seems hibernate and stored procedures will overlap,

We set up the stored procedure because we don't want the to developer know all the field in db. If tables change, then we need update above files.

Does it mean if we purely use stored procedure, we should just go for JDBC?

If hibernate, we should stay in HQL?

Solution 1

You can use native SQL and map the result to object:

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);

The JDBC syntax to invoke store procedure is like following:

CallableStatement proc =
connection.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, poetName);
proc.setInt(2, age);

So maybe, you can invoke stored procedure and map them to object:

  sess.createSQLQuery("{ call my_stored_proc }").addEntity(Cat.class);

Note also that updates made through stored procedures will escape hibernate, which means that you will need to evict objects from the 1st level and 2nd level cache yourself.

So as you see, hibernate and stored procedure don't really fit naturally together.

we set up the stored procedure because we don't want the to developer know all the field in db. if table change, then we need update above files.

If you're concerned about security, either use:

• database views
• Oracle column priviledges
• provide mapping files and forbid their modification by developpers

Solution 2

Using Hibenate with Stored Procedures is a certain overlap. As you for example need to write astored procedure for INSERT, UPDATE, DELETE and SELECT, Hibernate provides an easiest way to interract with with relational database objects by mappings them into metadata files like you mentioned person.hbm.xml.

Yes, the use of stored procedure wil require you to write these metadata files anyway. A stored procedure will not replace the Hibernate mappings. Those mapping only tell Hibernate how to persist your object-oriented model to the database. A great thing about Hibernate is that you may even, if needed, generate you database model from your JAVA code through the schema generation tool.

As for the stored procedures, one recommended way is to configure your stored procedures as named queries from within the configuration file. This, however, makes you miss the better potential, in my opinion, of Hibernate.

Does this answer your question? Do you need further explanations?

Solution 3

It is possible to use native-sql and to use stored procedure for querying (with limiations/rules). But, as written in the documentation:

Stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

So if you want to work with non-managed entities (i.e. not scalars in an Object[]), you'll have to apply a ResultTransformer in the code.

But at the end, if you want to hide the database to developers, if you don't want to map objects to tables, if you don't want to work with associations, if you don't want to use HQL, if you don't want to use an OO approach, then I really wonder why you want to use Hibernate. You'd better use raw JDBC (with Spring for example) or maybe a data-mapper like iBATIS.