How to make Java talk to multiple databases?

Categories: InfoCaptor,Other

One of the important feature of InfoCaptor is to connect to most of the databases available for which a JDBC driver is available. In this article I will mention the internal details of how this is done. In order to talk to a database, a java application needs a JDBC driver which is an interface between the third party database and Java. Each database vendor has certain protocol defined in order to access its database. So either the vendor provides the JDBC driver or it is available from a third party software who makes and sells (or gives it away for free, for e.g open source).

In order to connect to a database, you need a minimum set of information in order to locate the database. This is very similar to the web URL to locate a page on the Internet.

Here are some of the important parameters

Host : This is the physical location where the database is installed. This could be a server or your PC

Port : This is a numeric value where you Database engine is listening for requests that come in and process accordingly

SID: This is the identifier of the database name. For e.g. The same server could be hosting multiple databases like Database_for_HR, database_for_finance etc. So this SID uniquely identifies the database.

Finally there is the standard username/password parameters that is required for logging into the database.

Now, depending on the database vendor, you may not require all of the following parameters (host, port, sid).

Within InfoCaptor, there is a table which maps the database driver and the associated protocol needed for the connection to be established.

Here is a list of the map

driverMap.put("com.ashna.jturbo.driver.Driver"    ,     "jdbc:JTurbo://[HOST]:[PORT]/[DB]"); driverMap.put("COM.cloudscape.core.JDBCDriver"    ,     "jdbc:cloudscape:[DB]"); driverMap.put("COM.ibm.db2.jdbc.app.DB2Driver"    ,     "jdbc:db2://[HOST]:[PORT]/[DB]"); driverMap.put("com.inet.tds.TdsDriver"    ,     "jdbc:inetdae:[HOST]:[PORT]?database=[DB]"); driverMap.put("com.informix.jdbc.IfxDriver"    ,     "jdbc:informix-sqli://[HOST]:[PORT]/[DB]:INFORMIXSERVER="); driverMap.put("com.microsoft.jdbc.sqlserver.SQLServerDriver"    ,     "jdbc:microsoft:sqlserver://[HOST]:[PORT][;DatabaseName=[DB]]"); driverMap.put("com.pointbase.jdbc.jdbcUniversalDriver"    ,     "jdbc:pointbase://embedded[:[PORT]]/[DB]"); driverMap.put("com.sybase.jdbc.SybDriver"    ,     "jdbc:sybase:Tds:[HOST]:[PORT]"); driverMap.put("com.sybase.jdbc2.jdbc.SybDriver"    ,     "jdbc:sybase:Tds:[HOST]:[PORT]"); driverMap.put("hSql.hDriver"    ,     "jdbc:HypersonicSQL:[DB]"); driverMap.put("ids.sql.IDSDriver"    ,     "jdbc:ids://[HOST]:[PORT]/conn?dsn='[DB]'"); driverMap.put("interbase.interclient.Driver"    ,     "jdbc:interbase://[HOST]/[DB]"); driverMap.put("jdbc.idbDriver"    ,     "jdbc:idb:[DB]"); driverMap.put("oracle.jdbc.driver.OracleDriver"    ,     "jdbc:oracle:thin:@[HOST]:[PORT]:[DB]"); driverMap.put("org.enhydra.instantdb.jdbc.idbDriver"    ,     "jdbc:idb:[DB]"); driverMap.put("org.firebirdsql.jdbc.FBDriver"    ,     "jdbc:firebirdsql:[//[HOST][:[PORT]]/][DB]"); driverMap.put("com.mysql.jdbc.Driver"    ,     "jdbc:mysql://[HOST]:[PORT]/[DB]"); driverMap.put("org.hsql.jdbcDriver"    ,     "jdbc:HypersonicSQL:[DB]"); driverMap.put("org.postgresql.Driver"    ,     "jdbc:postgresql://[HOST]:[PORT]/[DB]"); driverMap.put("postgresql.Driver"    ,     "jdbc:postgresql://[HOST]:[PORT]/[DB]"); driverMap.put("RmiJdbc.RJDriver"    ,     "jdbc:rmi://[HOST]:[PORT]/jdbc:cloudscape:[DB]"); driverMap.put("sun.jdbc.odbc.JdbcOdbcDriver"    ,     "jdbc:odbc:[DB]"); driverMap.put("weblogic.jdbc.mssqlserver4.Driver"    ,     "jdbc:weblogic:mssqlserver4:[DB]@[HOST]:[PORT]");

So when you pick a particular driver from the drop down list within InfoCaptor, it knows which protocol to use.

For e.g if you picked the Oracle driver (“oracle.jdbc.driver.OracleDriver” ) then the associated protocol would be
URL = “jdbc:oracle:thin:@[HOST]:[PORT]:[DB]””

So once the user enters the [HOST],[PORT],[DB] values, all it does is replace these with the user supplied values, so the final URL would look like

URL = “jdbc:oracle:thin:@localhost:1521:orcl”

Similarly if you had to connect to the Access database, then you would select the JDBC ODBC bridge and the

URL= “jdbc:odbc:[DB]”

and in the case of the northwind database (based on the ODBC setup in the Control Panel) the final URL would be

URL = “jdbc:odbc:northwind”

This is a very simple technique to make your Java applications compatible with all the databases and you don’t need to hard code anything within the application.

NOTE: You still require to get the appropriate JDBC driver files and place them in the “../lib/ext” directory.

Let me know if you find any flaws/suggestions/or different technique of doing the same.

« « Discoverer Reports through Oracle Applications                        Dashboard and Reporting Options with Oracle tools » »

Comments are closed.