We help IT Professionals succeed at work.

Application with Multiple databases

ayeshanasreen
on
We are developing an intranet application which should be compatible with 5 databases.  I need advises on how to proceed with this.  

Thanks,
Nasreen.
Comment
Watch Question

Nasreen what you meen with compatible with five database ?

if you use JDBC you are compatible with every SQL Database.
lbg
Commented:
The standard way of talking to databases is through JDBC. Everything you need is included in the JDK except for the specific JDBC-Driver for the database. You can use any number of connections to any number of databases using JDBC.
I suppose that there are a bunch of examples out there on the net, but here's an other one for SQL-Server 7:

try
{
  //Register your database specific driver
  DriverManager.registerDriver(new Weblogic.jdbc.mssqlserver4.Driver());
     
  while (true)
  {
    //Obtain a connection to the database
    //DriverManager.getConnection(<URL>, <username>, <password>);
    con = DriverManager.getConnection("jdbc:weblogic:mssqlserver4:myDatabase@127.0.0.1:1433?sql7=true;codeset=Cp850", "myUserName", "myPassword");

    Statement statement = con.createStatement();

    //String select = "select * from myTable";
    //Input select string from commandline
    String select = new java.io.BufferedReader(new java.io.InputStreamReader(System.in)).readLine();

    if (statement.execute(select))
    {
      ResultSet res = statement.getResultSet();

      //Display the result the way you want. (See the JDK dokumentation on ResultSet)
      showResultSet(res);
    }
     
    con.close();
  }
}
catch (Throwable e)
{
  System.out.println(e.getClass().getName() + " " + e.getMessage());

  try
  {
    con.close();
  }
  catch (Throwable ex)
  {
    System.out.println(ex.getClass().getName() + " " + ex.getMessage());
  }
}
Commented:
You need to write a DataAccess class which has the JDBC settings for the Connection configured through a properties file. Then you can configure a properties file which contains settings for each database you intend to support.

Author

Commented:
Thanks

Oliver_Dornauf, lbg, dorothy2 for your suggestions.  Another thing is how to write a business logic.  A simple example I want select system date the sql query is going to vary for every database.  I need advises regarding this also.

Regards,
Nasreen.

Author

Commented:
Thanks

Oliver_Dornauf, lbg, dorothy2 for your suggestions.  Another thing is how to write a business logic.  A simple example I want select system date the sql query is going to vary for every database.  I need advises regarding this also.

Regards,
Nasreen.
lbg

Commented:
I'm not sure that I have understood your question correctly...
If you need the SQL-string to vary from occation to occation you just have to build it up peace by peace.

Ex:

String table = "matrix";
String keyName1 = "name1";
String keyValue1 = "value1";
String keyName2 = "name2";
String keyValue2 = "value2";

String sqlString = "select * from " + myTable + " where " + keyName1 + "='" + keyValue1 + "' AND " + keyName2 + "='" + keyValue2 + "'";

The result is:
select * from matrix where name1='value1' AND name2='value2'

This is actually not a very good way to concaternate strings. It should be done by using StringBuffer, it is a lot faster. This way is just easier to read.

If you want to talk to another database and not just another table you have to get a new Connection from DriverManager.

/lbg

Commented:
Nasreen,

Not clear on your question. Are you worried about creating queries where the resultset returned or tables you search differ between databases? Are you creating queries where only the values you search on are changing, but resultset columns are same? Are you concerned that system date has a different date format in the different databases? Are you concerned that the SQL query required to retrieve the system date varies from database to database?

Can you give us a specific example?

Thanks,

Dorothy

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- Split points between Oliver_Dornauf, dorothy2 and lbg

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Venabili
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.