• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Data Access Layer Best Practices

 
Alfred Kemety
Ranch Hand
Posts: 279
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use Hibernate.
 
Alfred Kemety
Ranch Hand
Posts: 279
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James, Hibernate??
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic