IT-eye

I hadn’t used iBatis before a couple of weeks ago. I had read about iBatis, but never had an opportunity to use it on a project. From what i had read, it looked like the perfect tool to use when you have to deal with an existing database where you have to access the data through stored procedures. If you can map your classes to tables and views most ORM frameworks will do the job, but especially in Oracle land, it’s quite common to hide table behind plsql packages. You could call it a Service Oriented Database (SODB) approach.

If you want to query a table you can call a stored procedure and it will either return a collection or a ref cursor with the requested data. There are a couple of reasons for this approach:

• Reuse – multiple applications can reuse the same sql statements.
• Performance – the sql statements can be written by database people with thorough database knowledge, not by java/.net/…. developers lacking the knowledge to correctly use a database.
• Security – the tables are hidden by stored procedures, database sessions do not need direct access to these tables.

Most ORM frameworks i know have problems dealing with all the different ways you can use Oracle pl/sql package, and that’s why a framework like iBatis, where writing your own sql statements is the core of the framework, is probably a better fit. Also ORM frameworks usually assume you have a graph of java objects that you want to persist as a whole. You often find that this is not the way to deal with plsql packages.

People often ask me, when do i use an ORM framework, when do i use iBatis and when do i use plain JDBC? In my opinion there’s not a big difference between iBatis and JDBC. Ibatis is a conventient way to do JDBC, and my advice would be to use iBatis whenever you need to use JDBC, except for real small projects. So the real choice is: ORM or SQL (using jdbc/iBatis). ORM works great when you mostly deal with tables and views, jdbc/ibatis is the way to go when most of the communication with the database is through stored procedures and plsql packages.

So last week i downloaded iBatis to create some examples for a new project, but quickly found the documentation seriously lacking when dealing with Oracle plsql code. Most people try to learn a new technology by looking at different examles, copy the one they think is most fitting to the situation, and then modify the example until it fits their situation. The iBatis documentation doesn’t really contain complete examples. Instead it describes per ‘technology layer’ how you can use it. This is really frustrating when trying to learn a new technology, because it basically means you have to read the documentation from begin to end.

Also the documentation doesn’t contain good examples that show you how to deal with pl/sql packages, ref cursors, collection types and object types. To me this is a bit weird, as to me this is their sweet spot. When mapping classes to tables there are a lot of other frameworks which are equally good or better. But iBatis can really shine when dealing with database properietary code, because you have to write the sql yourself. I would expect iBatis to better highlight their strong points.

Anyway, in the next couple of posts i will provide some examples how you can use iBatis with Oracle plsql:

• The basics of an iBatis application
• Using iBatis for simple sql statements: insert, delete
• Calling a plsql packaged procedure using iBatis
• Calling a plsql packaged function using iBatis
• Calling a plsql stored procedure with an object type parameter
• Using iBatis with a stored function returning a ROWTYPE
• Using iBatis with a stored function returning a Ref Cursor
• Using iBatis to cast a returned Collection Type to a Ref Cursor
• Using iBatis with a stored function returning a Collection Type
• Using iBatis with a stored function returning an Object Type
• Using iBatis with a stored procedure returning a pl/sql Collection Type