DB independent JDBC

[TheObserver]

I'm working on a class that opens a ResourceBundle, pulls out a few key/values, and creates a jdbc DB connection from the info gathered.

I would like to make this class as DB independent as possible, meaning no special exceptions/methods/etc have to be added to add the ability to connect to new DBs as the need arises. My main concern is in regard to the DriverManager.getConnection(...) call. With MySQL, for instance, you only provide a single string that has the connection parameters, username, password, etc, all together. However, Oracle uses three strings - a connection parameter string, a username string, and a password string.

If I could use just one or the other (one string vs. three strings) for all getConnections, that would be ideal - I could just define a single string in the ResourceBundle file, or I define the connection/username/password strings and use the three parameter getConnection. Is this possible, or am I stuck with making special cases for each DB type?

 

[siberian]

What I do is create a new class, DBManager. This class has 2 methods (well 3 because I was testing)

getDB : returns a database handle
releaseDB(Connection conn) : releases a given db handle

I also use tomcat connection pooling, thats why I use the env, to get the JDNI pooled connection out. Sorry teh formatting is off, copy paste to your favorite editor to see the entire thing.

My DBManager looks like this:

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class DBManager {



        static public Connection getDB(){
                Connection conn = null;
                try {
                        Context initCtx = new InitialContext();
                        Context envCtx = (Context) initCtx.lookup("java:comp/env");
                        DataSource ds = (DataSource)envCtx.lookup("jdbc/TEST");
                        conn = ds.getConnection();
                } catch (SQLException e){
                        System.out.println("<HR>SQL Problem<HR>");
                        System.out.println("<HR>"+e.getMessage()+"<HR>");
                } catch (NamingException e) {
                        e.printStackTrace();
                }
                //System.out.println("DB CREATED");
                return conn;
        }

        static public Connection getDB2(){
                Connection conn = null ;

                try {
                        System.out.println("Before it");
                        Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
                        System.out.println("Did this do it?");
                        try {
                                conn = DriverManager.getConnection("proxool.printgate:org.firebirdsql.jdbc.FBDri
ver:jdbc:firebirdsql:localhost/3050:C:/test.gdb");
                                System.out.println("Got connection");
                        } catch (SQLException e) {
                                System.out.println("Problem getting connection");
                                //LOG.error("Problem getting connection", e);
                        }
                        if (conn != null) {
                                System.out.println("Got connection");
                                //LOG.info("Got connection :)");
                        } else {
                                System.out.println("Didn't get connection, which probably means that no Driver a
ccepted the URL");
                                //LOG.error("Didn't get connection, which probably means that no Driver accepted
 the URL");
                        }
                }catch (ClassNotFoundException e) {
                        //LOG.error("Couldn't find driver", e);
                        System.out.println("Could not find driver:"+e.getMessage());
                } finally {
                        try {
                                // Check to see we actually got a connection before we
                                // attempt to close it.
                                if (conn != null) {
                                        conn.close();
                                }
                        } catch (SQLException e) {
                                //LOG.error("Problem closing connection", e);
                                System.out.println("Problem closing connection");
                        }
                }
                //System.out.println("DB CREATED");
                return conn ;
        }
        static public void releaseDB(Connection conn){
                try{
                        conn.close();
                        //System.out.println("DB RELEASED");
                } catch (SQLException e){
                        System.out.println("releasingDB error!:"+e.getMessage());
                }
        }

}

Then my app just instantiates db handles like this :

Connection conn = DBManager.getDB();

and releases them like this

DBManager.releaseDB(conn);

As you can see in my class, I was experimenting with the proxool pooling. This abstraction made my life very easy because I can just change the name of the methods in the class and dynamically adjust my connection method. This would apply to the mysql->oracle move or any other shift.

This has worked well for me on some pretty complex apps, your mileage may vary.

 

[siberian]

Note also, its up to you to write generic ANSI 92 SQL. No software can automate that process for you.

 

[TheObserver]

I'm making a class that works similarly to yours such that code that relies on a DB connection can just instantiate the class and use it until it is done with it (DB abstraction to the outside code).

However, my situation is that I want to make this class such that I DON'T have to modify the code if I change the database that I'm connecting to. I'm currently pulling a URL string, a username string, a password string, and a driver location string out of the ResourceBundle, along with a "dbtype" (set equal to "mysql", "oracle", etc) string, and based on the dbtype string I am setting up connections in the particular manner for each DB type (IE, using one param string for MySQL, using three for Oracle).

However, if I add a third DB that I need to support, I may have to add more code to handle the third DB type, and the fourth, fifth, etc. This won't work once the code is deployed.

So, basically, I need to find a way to use getConnection(...) that will either use ONE string parameter for all DBs, or THREE strings parameters for all DBs I want to use with this code. I should be able to pull this info from the ResourceBundle and use one programmatical path for ALL DBs to establish a connection, rather than different paths for each DB type.

 

[siberian]

Your trying to do somethign that is not possible.

Each DB has its own connection methodology, its just not universal.

Your best bet would be to deploy with as many 'connectors' as required and allow a selection to be made in a configuration file.

As new connectors are required make the DB class available for distribution with a new configuration option to be put in the configuration file.

Then your code is protected from the DB and you can manage it in this way.

 

[siberian]

Let me qualify this

Nothing is impossible, you COULD do something programmatically etc etc but I would argue that the chances of introducing a bug when you have no idea what context this code will be used in and have no way of testing it is very high when it is released into the wild.

The 'distribute a new db class' approach is a lot more maintainable and testable which will yield happier customers.

This is my opinion, your mileage may vary.

 

[stefanwagner]

You could have a properties file:

postgresql=org.postgresql.Driver 1.2.3.4/foo me supersecret
xySql=xySql.Driver:1.2.3.5/bar:me,cryptic

When you read it, you mention how many Strings there are - separated by tab or space, and call the appropriate method.

Are you sure, that mySql has no method for three parameters?
Every db I found till today supported the (url, user, pwd) method, even MsSQL and pseudo-rdbms-CSV - drivers.




seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[hologram]

The Observer wrote

So, basically, I need to find a way to use getConnection(...) that will either use ONE string parameter for all DBs, or THREE strings parameters for all DBs I want to use with this code.  I should be able to pull this info from the ResourceBundle and use one programmatical path for ALL DBs to establish a connection, rather than different paths for each DB type.

You could also put the NUMBER OF PARAMETERS in the Resourcebundle, and code the 2 possibilities : 1 parm or 3 parm.

 

[siberian]

Just my $.02 again, no one cares, but why would you -EVER- deploy a solution to customers that you couldn't test?

That seems to me to be asking for support nightmares..

 

[stefanwagner]

He could use a Abstract class, and create the missing implementer just in time.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[dbleyl]

Consider using hibernate (

http://www.hibernate.org).

Even if you do not use all the really cool O-R mapping
features, you can still use it to manage connections.

1. All of your jdbc configuration properties would be stored
in one xml or java-properties configuration file - it's very
easy to change JDBC class, driver and specifics such as
batch-size.

2. You can switch connection pool managers.

3. You can change to a J2EE DataSource using JNDI from the same file.

4. You have built-in features such as logging and printing sql from the config files.

 

[dbleyl]

I neglected to mention that you can use native sql, or
a sql-like query language that would insulate your code
from SQL implementation idiosyncracies. If you need
those idiosyncracies for performance, you can use the native sql in your code, or even better, add the SQL text to the config file as a named query.