Dear All,
My name is Arpan Bansal and I am working as a Manager in Newgen Software Technology.

We have a Document Management Product that is built using J2EE Technology on Server side. To give you an overview:

1. It's repository part is available on MS SQL/Oracle and built as set of Tables and Stored Procedures.

2. We have a middle layer written as TCP/IP based Java/JDBC Server alternatively as EJBs ( stateless Session Beans with JDBC), which interfaces with RDBMS and provide XML API for applications.

As I mentioned our repository is built using Stored Procedure. This has made porting in terms of supporting new database server say DB2 an issue for us.

We want to go away from SPs and sees following as options.

1. Porting SP logic to JDBC. But JDBC it self is not 100% portable?
Any suggestions on this

2. Using any middleware like hibernet or POJO?
Any recommendations? Will it be a right approach? Will performance be an issue?

3. Will any thing else like any specific design pattern ( with JDBC ) can help us.


Your help is highly appreciated.

Thanx in advance
Best Regards
Arpan

food for thoughts:
- JDBC and SQL is more portable than stored procedures, but if your SP are heavy on logic then they could be easier reused by any technology that deals with DB;
- SP might work faster;
- if you need heavy SQL and database optimization then iBatis framework might be a better fit; iBatis also works very well with weird DB schemas
- if operations are mostly CRUDs and DB schema is decent one then Hibernate might be a good fit;
- if DBAs tightly control DB and want to review all the SQL queries then H is pretty much no go :(