• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Data Access Layer Best Practices

 
Ranch Hand
Posts: 279
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use Hibernate.
 
Alfred Kemety
Ranch Hand
Posts: 279
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
James, Hibernate??
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic