3 Replies Latest reply: May 9, 2008 3:39 PM by abillconsl RSS

    Database Connection design question

    807591
      Hello, I have a design question. Awhile back I needed to create a database connection to SQL Server, so I created a class to do it
      import java.sql.*;
      import java.io.*;
      import java.net.MalformedURLException;
      
      import org.w3c.dom.Document;
      import org.w3c.dom.*;
      
      import javax.xml.parsers.DocumentBuilder;
      import javax.xml.parsers.DocumentBuilderFactory;
      import org.xml.sax.SAXException;
      import org.xml.sax.SAXParseException;
      
      public class SQLServerConnection
      {
           private static Connection connection = null;
           
           public SQLServerConnection(String user, String password, String dbName) throws java.sql.SQLException
           {
                getDBConnection(user, password, dbName);
           }
           
           public SQLServerConnection(String configFileName) throws java.sql.SQLException
           {
                getDBConnection(configFileName);
           }
           
           private void getDBConnection(String user, String password, String dbName) throws java.sql.SQLException
           {
               DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
      
               connection = DriverManager.getConnection(
                    "jdbc:microsoft:sqlserver:" + dbName, user, password);              
           }
           
           private void getDBConnection(String configFileName) throws java.sql.SQLException
           {
                String user;
                String password;
                String dbName;
      
                try
                {
                     DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                     DocumentBuilder db = factory.newDocumentBuilder();
                     Document doc = db.parse(configFileName);
                     
                     doc.getDocumentElement().normalize();
                     
                     // get the configuration information
                     password = getConfigParameter("password", doc);
                     user = getConfigParameter("username", doc);
                     dbName = getConfigParameter("databasename", doc);
                     
                     getDBConnection(user, password, dbName);
                }
                catch (MalformedURLException murle)
                {
                     System.out.println("Unable to connect to: " + configFileName + " -- " + murle);
                     System.exit(1);
                }
                catch (FileNotFoundException fnfe)
                {
                     System.out.println("Configuration file " + configFileName + " not found.");
                     System.exit(1);
                }
                catch (IOException ioe)
                {
                     System.out.println("IOException: " + ioe);
                     System.exit(1);
                }
                catch (javax.xml.parsers.ParserConfigurationException pce)
                {
                     System.out.println ("Parser Configuration Error: " + pce);
                }
                catch (SAXException saxe)
                {
                     System.out.println ("SAXException: " + saxe);
                }          
           }
           
           private String getConfigParameter(String paramName, org.w3c.dom.Document doc)
           {
                NodeList nl = doc.getElementsByTagName(paramName);
                if(nl != null)
                {
                     Node n = null;
                     for (int i = 0; i < nl.getLength(); i++)
                     {
                          n = nl.item(i);          
                          if(n.hasChildNodes())
                          {
                               NodeList children = n.getChildNodes();
                               return ((Node)children.item(0)).getNodeValue();
                          }
                     
                     }
                }
                else
                {
                     System.out.println ("nl is null");
                }
                
                return "";          
           }     
           
           public void setCatalog(String catalogName) throws java.sql.SQLException
           {
                connection.setCatalog(catalogName);
           }
      
           public Connection getConnection()
           {
                return connection;
           }
           
           public void closeConnection()
           {
                try
                {
                     connection.close();
                }
                catch(java.sql.SQLException sqle)
                {
                     System.err.println ("SQL Server Connection failed to close: " + sqle);
                }          
           }
      }
      Later on, I needed to do the same thing for MySQL, so I created a class for that, MySQLServerConnection which is exactly the same as above, except for:
      private void getDBConnection(String user, String password, String dbName) throws java.sql.SQLException
           {
                try
                {
                     Class.forName("com.mysql.jdbc.Driver").newInstance();
                     connection = DriverManager.getConnection(dbName, user, password);     
                }
                catch(java.lang.ClassNotFoundException cnfe)
                {
                     System.out.println (cnfe);
                }
                catch(java.lang.InstantiationException ie)
                {
                     System.out.println (ie);
                }
                catch(java.lang.IllegalAccessException iae)
                {
                     System.out.println (iae);
                }
           }
      Later, on, I did the same thing with OracleServerConnection. My question is, I know this is probably not optimal code. For example, I didn't originally have a close connection method, so I had to go in and code one for all 3. I'm assuming that an interface would be a good idea so that if I have to code another database connection class I make sure and include all of the appropriate methods. I'm also assuming that it would have been smart to have a master class, maybe something like DatabaseConnection and extended these classes from that. Am I on the right track? Totally offbase?
        • 1. Re: Database Connection design question
          800472
          Well you're wrapping the original Connection object but not providing much new functionality. How about a Factory that constructs the appropriate type of Connection object and returns it?
          Connection dbConnection = ConnectionFactory.getConnection("Oracle");
          Or if it can be inferred from the context...
          Connection dbConnection = ConnectionFactory.getConnection();
          • 2. Re: Database Connection design question
            abillconsl
            You could try coding something like this in one class:
            Connection con = null;
            /* ... */
            try { // try for DB 1
              con = DriverManager.getConnection( /* ... */ );
            } catch(SQLException sqle) {
              try { // try for DB 2
                con = DriverManager.getConnection( /* ... */ );
              } catch(SQLException sqle) {
                try { // try for DB 3
                  con = DriverManager.getConnection( /* ... */ );
                } catch(SQLException sqle) {
                  /* failed all three - now actually log the exception somehow */
                }
              }
            }
            • 3. Re: Database Connection design question
              807591
              @nclow - I will work on trying the Factory Pattern for this over the weekend and post when I finish to see what you think.

              @abillconsl - just to make sure I understand, you're saying that I just try to connect and it will cycle through the different database connection possibilities and connect when it finds the right one? If it fails all 3, log an appropriate message. One question I have about this is, I thought I was being object oriented by separating the different types of db connections (Oracle, SQL Server, MySql) into different classes. Am I missing the point of OOP by what I was/am trying to accomplish? Going overboard? Also, does your way try and connect to all 3 even if I connected to one already?

              Thx, Grantarchy

              Edited by: grantarchy on May 9, 2008 9:50 PM