File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes EJB and other Java EE Technologies and the fly likes Data Access Layer Best Practices Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Data Access Layer Best Practices" Watch "Data Access Layer Best Practices" New topic

Data Access Layer Best Practices

Alfred Kemety
Ranch Hand

Joined: Aug 14, 2002
Posts: 279
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.


Alfred Raouf - Egypt - SCJP 1.4<br />Kemety.equals(Egyptian) // returns true
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
Use Hibernate.

James Carman, President<br />Carman Consulting, Inc.
Alfred Kemety
Ranch Hand

Joined: Aug 14, 2002
Posts: 279
James, Hibernate??
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33125

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
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.

SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Sreenivasa Majji
Ranch Hand

Joined: Jul 12, 2001
Posts: 224
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.

Sreenivasa Majji
I agree. Here's the link:
subject: Data Access Layer Best Practices
It's not a secret anymore!