We are going to develop web application version for our product which uses an existing oracle DB with lots of tables (~500). The db relationship model is purely hierarchical and descends from a root object/table with a single key that spans across other child tables (~200 or so).

The DB model is around for quite sometime and it would be rather unfeasible to change the model to suit the ORM specific needs. Now, I was going through the book hibernate in action and felt that table-per-subclass is what we are looking for in our case. But the book (page 104) also mentions that this mapping strategy may be unacceptable for complex class hierarchies (because of multiple joins).

The way application is supposed to work is - when user logs in, he/she would typically select a root object to work on. The applicationt then needs to load root object along with other childs and present to the user. After that its mainly CRUD until final save in DB.

--

Could someone help me here? We do not want to risk our project in the middle with performance and other issues.

Thank you very much in advance.

I would like to correct.

The number of child tables associated with root object table will be close to 100 or so. The DB is pretty much a normalized one.

thanks

from our experiences with hibernate, i can say that it absolutely loves perfect data models.. ie: properly setup primary keys and foreign keys, etc.

i know with 500 tables it's a brain-smack deciding on an architecture. we've actually done fairly decent with hibernate on our (very, very) legacy systems despite the lack of true primary keys. but in a not so perfect model, expect you might have to work around conventional hibernate mappings with a lot of HQL or JDBC queries. after a while you'll start get frustrated at the hybrid code and spend a chuck of time drawing red brick walls on your dry erase board to bang your head against.

that said, if you have good data model, hibernate can be an amazing tool - it will really smooth out a lot of development by hydrating all your objects and taking care of a lot of database "behind the scenes" crap. the abstraction it provides to all your database tables is really nice. it's great for when your programmers hate dealing with DBA's and their standard.. uh.. "helpfullness".

Wouldn't iBATIS be a better option? We don't have any transactional aspect and our DB vendor is going to be pretty much fixed i.e. Oracle. We do need flexiblity at the database end in terms of sql optimization- tuning and executing custom SQL procedures. I read somewhere iBATIS works exceptionally well with complex/legacy databases as compared to Hibernate. It also offers simplicity as developers need not have to go through a new learning curve with hibernate. Training 30 developers on hibernate will be adaunting task!

I understand iBATIS is not an ORM but we do need to consider other aspects more relavant to our product requirement. I am still learning about these tools so I may be wrong about hibernate.

thanks

as i dont know your system I cannot suggest a definitive answer - only state that since hibernate 3 you are able to use good old native SQL to do almost anything in Hibernate (e.g. stored procedures and scalar returns)

With 3.1 it even require minimal configuration.

What do you think iBATIS can do that Hibernate cannot?


(AFAIK, Hibernate3 is a strict superset of these JDBC frameworks.)

I have been doing some reading online and through books and have come to realize after initial poc that hibernate (orm) seems to offer lot more features as compared to other persistence framework. But the problem really is with maturity, wide user base and conflicting feedbacks.

M Raible (also 'hibernate in action' reviewer - see introductory pages) makes this comment in one of his book - (Hibernate is quickly becoming a popular choice for persistence in Java applications, but sometimes it doesn’t fit. If you have an existing database schema, or even pre-written SQL, sometimes it’s better to use JDBC or iBATIS (which supports externalized SQL in XML files). I havent' yet looked into h3 spec to counter this.

At many other places including the book (HIA) itself, I have seen that people paint a bleak picture of hibernate when it comes to dealing with existing complex/legacy databases.

Now, as an user I'd really like to use various interesting features of hibernate including auto-change detection, orm basic pojo set mapping and loading, managing class-table associations, dynamic query etc. But I'd also need flexibility, simplicity and maturity with my persistence framework.

Many users are targetting hibernate complexities (configuration and definitions) and make their point by simply saying "why invite trouble when you have more control with simpler tools".

Hibernate should probably position and prove itself by asserting that its a matured framework to work with and it OFFERS Simplicity + ORM set of features to address complexities too. Many times its the latter which gets attention with hibernate.

You have read books and comments about Hibernate 2.x.

We had the same problem...and currently run a mix of Ibatis and Hibernate.
Would love to eliminate one of the frameworks...but there are problems.

Hibernate shines with updates and deletes of objects. Querying capabilities are good too.

Ibatis is a mapper. So, works really well for querying, but is more painful for updates, and deletes.

Case where ibatis works better is when we want to map the same tables to two different objects, sorta as a view on the object. Hibernate usually has problems with that because only one of those objects can be associated with a session at a time. Ibatis will more flexibly map the objects.

Ibatis is better equivalent of Hibernate's Native SQL functionality.

So, if lots of your code ends up involving native SQL in hibernate, then use IBatis. Or, like us, be stuck with a mix.

Please read this as opinion only.

Please don't post out-of-date information. There is absolutely no problem doing this in HB3.

Likewise, I'm quite certain that you will find HB3 native SQL support to be much more sophisticated than what iBATIS does.

since Gavin challenged...I decided to run a simple test for the scenario for which we do use ibatis.

I used hibernate 3.0.5 and ibatis 2

The test code is -

public class HibernateVsIbatis extends JNDIRequiredTestCase {
public HibernateVsIbatis(String name) {
super(name);
HibernateSessionManager.initHibernate();
}
public void testHibernate()
{
PersistHelper.get().startSession();
Session session = HibernateSessionManager.currentThreadSession();
List res = session.getNamedQuery("termQuery")
.list();

Iterator it = res.iterator();
while(it.hasNext())
{
Object item = it.next();
System.out.println(item+ " " +item.getClass().getName() );
}
PersistHelper.get().endSession();
}
public void testIbatis() throws SQLException
{
PersistHelper.get().startSession();
SqlMapSession session = iBatisSessionManager.currentThreadSession();
List res = session.queryForList( "term.getall", null );

Iterator it = res.iterator();
while(it.hasNext())
{
Object item = it.next();
System.out.println(item+ " " +item.getClass().getName() );
}
PersistHelper.get().endSession();
}
}

The ibatis mapping is ...
<sqlMap>
<resultMap id="term" class="TermImpl">
<result property="id" column="id" javaType="string" />
<result property="name" column="name" javaType="string" />
</resultMap>
<select id="term.getall"
resultMap="term">
<![CDATA[
select term.termid as id, term.name as name from Term term ]]>
</select>
</sqlMap>

The hibernate query is ...
<sql-query name="termQuery">
<return alias="term" class="TermImpl">
<return-property name="id" column="id"/>
<return-property name="name" column="name"/>
</return>
SELECT term.name AS name,
term.termid AS id
FROM term term
</sql-query>

Ibatis runs find and returns me expected list of TermImpl objects. It didn't need additional mapping defined for the class.

Hibernate throws exception ...
org.hibernate.MappingException: Unknown entity: com.schemalogic.schemaobjects.TermImpl
at org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:569)
at org.hibernate.loader.custom.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:53)
at org.hibernate.loader.custom.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:119)
at org.hibernate.loader.custom.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:98)
at org.hibernate.loader.custom.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:87)
at org.hibernate.loader.custom.SQLCustomQuery.<init>(SQLCustomQuery.java:105)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:144)
at com.schemalogic.schemaserver.HibernateVsIbatis.testHibernate(HibernateVsIbatis.java:26)

I am guessing this is because TermImpl doesn't have a hibernate mapping. So, I tried to put in the mapped class instead in the return and now I get the exception
java.sql.SQLException: Invalid column name
because not all the columns are returned by the query.

So, I finally give up and continue to use iBatis for mapping type stuff, and Hibernate for ORM.

BTW, I was hoping it would work with hibernate and I could start reducing iBatis usage. Using two frameworks adds overhead.

In 3.1 you can do the following:

<class name="TermImpl>
<id name="id">
<generator ...>
</id>
<property name="name"/>
</class>

<sql-query name="termQuery">
<return class="TermImpl"/>
SELECT term.name AS name,
term.termid AS id
FROM term term
</sql-query>

The trick is to not use alias as that tells hibernate to use auto-generated aliases (the {} syntax)

/max

Of course, you need to be able to read documentation and follow instructions in order to use tools.

Exactly. Note that there is effectively no difference between iBATIS and Hibernate for this simple case. The metadata and code are basically identical. For more complex cases, Hibernate shines, and can do stuff that JDBC frameworks simply can't.

Sorry, that wasn't the point...
The termQuery has to return each of the properties defined in the TermImpl mapping, otherwise it fails

<class name="TermImpl>
<id name="id">
<generator ...>
</id>
<property name="name"/>
<property name="notes"/>
<property name="nextone"/>
<property name="andmore"/>
</class>

<sql-query name="termQuery">
<return class="TermImpl"/>
SELECT term.name AS name,
term.termid AS id
FROM term term
</sql-query>

also results in java.sql.SQLException: Invalid column name . Hence cannot do a id, name only view on TermImpl.

The point is that Ibatis doesn't have this problem. It will map to whatever class I ask it to map to, irrespective of whether the class has additional properties. This is the scenario for which we cannot eliminate use of Ibatis.