Best Way to write application which can use different database

[alps pathak]

Hello
Currently my application is deployed on weblogice and the database is oracle. Now it is required to use DB2 . Can somebdy pl. tell me what will be the best practise so that in future if i have to use any other database I would nt be required to do a lot of coding.
Right now the Queries are in the code.
One approach what we have taken is taking out all the queries from the code and put in the xml file.
Load the sml file when the application start. We are using DOM Parser to parse and Xpath to select single node.

Is there any better approach than this.

Thanks
Alpa Pathak

[Shailesh Chandra]

Well Purpose JDBC is same that single piece code can interact with many database.
best practice is that out your queries outside of code.
This will remove lot dependency on database, only change the query in XML as per database and move ahead.

I am also having queries in XML but I don't load then on application start
rather I cache parsed query into a HashMap using singleton class.

The advantage of this is if any specific module is not being used it's query would not load in memory

Shailesh

[Sripathi Krishnamurthy]

Originally posted by alps pathak:

Is there any better approach than this.

If it is just required to store the queries in a file so that the code need not be changed periodically, you can use a properties file. Properties file will really solve the purpose if you just want to store and retrieve the query to execute at any point of time. That way you dont have to use XML, parse it or traverse it.
Is there any specific reason you have used XML for storing the queries?

[Shailesh Chandra]

I would still recommend XML file instead of property file.
I didnt analyse property file too much
but It may cause a problem .

consider a property file like

Myquery1= Select col1,col2,col3 form mytable Where
Col1 = ?

now I have one query against attribte Myquery1 but it will be treated as file is having two attribte Myquery1 and Col1.
I dont find such proplems with XML with number of carriage retruns which makes readability of query even better.


Shailesh

[Sripathi Krishnamurthy]

Originally posted by Shailesh Chandra:
I would still recommend XML file instead of property file.
I didnt analyse property file too much but It may cause a problem .

consider a property file like

Myquery1= Select col1,col2,col3 form mytable Where
Col1 = ?

XML is definitely an overhead if it is just storing the queries in a file.
Disadvantages of using XML in this case
1) You dont have to use any parent child relatinship.
2) You just need a place to store the query and retrieve it to execute in the code.
3) You have to use a XML parser for parsing which can be avoided.
4) Parsing the XML for validity, well formedness is a overhead which can be avoided.

It is difficult to buy the arguement that if one writes the query in 2 lines, it will cause problem. DONT write in 2 lines. write in a single line. If one knows to use a property file, he should write the query in a single line.

[Paul Sturrock]

Originally posted by alps pathak:
Hello
Currently my application is deployed on weblogice and the database is oracle. Now it is required to use DB2 . Can somebdy pl. tell me what will be the best practise so that in future if i have to use any other database I would nt be required to do a lot of coding.
Right now the Queries are in the code.
One approach what we have taken is taking out all the queries from the code and put in the xml file.
Load the sml file when the application start. We are using DOM Parser to parse and Xpath to select single node.

Is there any better approach than this.

Thanks
Alpa Pathak

Sounds like a perfect case for an ORM solution, i.e. Hibernate, OJB, Toplink etc.

[Shailesh Chandra]

Originally posted by Sripathi Krishnamurthy:

XML is definitely an overhead if it is just storing the queries in a file.

I dont see any overhead here,because all queries are cached in hashmap they are parsed only once.
Had XML shown disadvantage then today there wont be any XML, every server runs on XML configuration of files

You dont have to use any parent child relatinship.

I can have queries of sql server and oracle in my XML file Same time and Just changing default tag I can use query of sql server or Oracle or My sql

You just need a place to store the query and retrieve it to execute in the code.

One has his own choice, one can choose property file or XML.I see XML extends readability. Also there are more advantages

You have to use a XML parser for parsing which can be avoided.

Don't we require any IO operation for reading property file

Parsing the XML for validity, well formedness is a overhead which can be avoided.

One time effort when writting XML,even writing a java code is one time effort




Shailesh