Data Access Layer Best Practices

[Alfred Kemety]

Dear all,
I am designing an application with a multi-tier architecture. The problem that I face is to find the best way to implement the Data Access Layer. The main problem that I face is where should my SQL statements reside?

Two options are:
1- Stored Procedures:
All insert, update, delete and even the select statements should be written in a parametrized stored procedures and I have methods that call the stored procedure, pass the parameters and get the data.
In my opinion, this separates SQL code from Java code, which is something I like. On the other hand if I would change the DB from Oracle to SQL or to mySql then I would have problems with the Data Layer Implementation.

2- Within Java Code:
All sql statements are hard coded in the java classes. Then I can write standered sql statements and so the layer would be able to access any DB just by changing the provider. On the other hand, even the select statements are hard coded and so any simple change would need recompile, also performance will be less than the case of Stored Procedures.

What do you think is the best solution? Also if you have any reference regarding best practicies in building the Data Access Layer, please let me know.

TIA

[James Carman]

Use Hibernate.

[Alfred Kemety]

James, Hibernate??

[Jeanne Boyarsky]

Alfred,
Hibernate is an Object/Relational Mapping Framework that takes care of the SQL for you. We have an O/R forum a few below this one if you have questions on Hibernate.

If you are writing the SQL yourself, I favor a variant of Option 2. Do the queries through java, but place the SQL in a property file. That way you can make some changes (like of the column names change) without a recompile. Other changes (like a new column) would require a code change anyway. On the performance front, "premature optimization is the root of all evil." Don't worry about it unless it is a proven issue. Then you can tune just those queries that have a problem. In my experience, I have gotten good performance out of PreparedStatements and have not needed to go to stored procs for them.

[Roger Chung-Wee]

In my department, many people like to place the SQL in XML files which can be loaded into a map on server startup. Furthermore, we have a rule about not using stored procedures, although I think that database triggers are allowed.

Also, use the DAO pattern to separate your business logic layer from your persistence layer.

[Sreenivasa Majji]

Stored proecedures locks to you a particular vendor, hard to make control transactions, needs a specialist to make chagnes, but offers better performance.

DAO with sqls inside the source code gives more control on the transactions, minimal sql knowledge is required to change, but needs compilation for every sql change.

Normally I would recommed DAO approach, because it's easy to implement and takes less time understand.