Database Connection question

Discussion in 'Java' started by JY, Nov 2, 2005.

  1. JY

    JY Guest

    I have 2 applications: one is using JBoss where the database connection is
    obtained through JNDI lookup; the other is a standalone app and is using
    java.sql.DriverManager for Connection. Every time after obtaining a
    Connection, a stored proc is called.
    My task is to write generic code for database connection to be used in both
    application. Please give some input on what should be the best way.
    Thanks
    JY, Nov 2, 2005
    #1
    1. Advertising

  2. JY

    Pete Barrett Guest

    On Wed, 2 Nov 2005 17:56:20 -0500, "JY" <> wrote:

    >I have 2 applications: one is using JBoss where the database connection is
    >obtained through JNDI lookup; the other is a standalone app and is using
    >java.sql.DriverManager for Connection. Every time after obtaining a
    >Connection, a stored proc is called.
    >My task is to write generic code for database connection to be used in both
    >application. Please give some input on what should be the best way.
    >

    DriverManager will work from within JBoss; a JNDI lookup will only
    work from outside if you have an InitialContext from somewhere; so the
    only universal way to obtain a database connection is to use
    DriverManager both within your bean (or servlet, or whatever), and in
    the standalone application. But I can't beleive that that's an answer
    to the question you had in mind, because you obviously know that
    already.

    What exactly do you mean by 'generic' code? All the database code will
    be generic once the Connection is obtained, so what I usually do is to
    centralise all the database access in a single class, and provide the
    Connection in an open() method. Is that the sort of 'generic' code you
    had in mind? (Obviously, there are other equally generic ways of doing
    it apart from the way I prefer.)

    Pete Barrett
    Pete Barrett, Nov 3, 2005
    #2
    1. Advertising

  3. JY

    JY Guest

    "Pete Barrett" <> wrote in message
    news:eek:...
    > On Wed, 2 Nov 2005 17:56:20 -0500, "JY" <> wrote:
    >
    >>I have 2 applications: one is using JBoss where the database connection is
    >>obtained through JNDI lookup; the other is a standalone app and is using
    >>java.sql.DriverManager for Connection. Every time after obtaining a
    >>Connection, a stored proc is called.
    >>My task is to write generic code for database connection to be used in
    >>both
    >>application. Please give some input on what should be the best way.
    >>

    > What exactly do you mean by 'generic' code? All the database code will
    > be generic once the Connection is obtained, so what I usually do is to
    > centralise all the database access in a single class, and provide the
    > Connection in an open() method. Is that the sort of 'generic' code you
    > had in mind? (Obviously, there are other equally generic ways of doing
    > it apart from the way I prefer.)
    >

    yes thats what i have in my mind. I have identified that
    1) to get a javax.sql.DataSource connection, I need:
    1.1) a JNDI name

    2) to ge a java.sql.DriverManager connection, I need:
    2.1) Driver Name
    2.2) Database URL
    2.3) Database User Id
    2.4) Database Password

    Now I can create one class which has 2 methods:
    public class ConnectionUtil {
    // used by the app using JBoss
    public Connection getDataSourceConnection( String jndiName ) {...}

    // used by the app not using JBoss
    public Connection getDriverManagerConnection( String driverName, String
    databaseUrl, String databaseUserId, String databasePassword) {...}
    }


    Another option i can think of which is cleaner is to have a Connection
    interface:
    public interface ConnectionI {
    public Connection getConnection(Properties props);
    }

    Then 2 seperate classes one for DataSource and one for DriverManager which
    implements the interface
    public class DataSourceConnection implements ConnectionI {
    private String jndiName;

    // props contains JNDI Name
    public Connection getConnection(Properties props) {...}
    }

    public class DriverManagerConnection implements ConnectionI {
    private String driverName;
    private String databaseUrl;
    private String databaseUserId;
    private String databasePassword;

    // props contains Driver Name, Database Url, Database User Id, Database
    Password
    public Connection getConnection(Properties props) {...}
    }

    Then a factory:
    public class ConnectionFactory {
    public static final int DATASOURCE = 1;
    public static final int DRIVERMANAGER = 2;

    public ConnectionI getConnectionI( int connType, Properties props ) {
    switch(connType)
    case DRIVERMANAGER:
    return new DriverManagerConnection( props );
    case DATASOURCE:
    return new DataSourceConnection( props);
    default:
    return null;
    }
    }

    So the test code to get a DataSource Connection would be:
    Properties props = new Properties();
    prop.put("JNDINAME", "MSSQLDS");
    ConnectionI connectionI = ConnectionFactory.getConnectionI(1, props);
    java.sql.Connection conn = connectionI.getConnection();

    And the test code to get a DriverManager Connection would be:
    Properties props = new Properties();
    prop.put("DRIVERNAME", "sun.jdbc.odbc.JdbcOdbcDriver");
    prop.put("DATABASEURL", "jdbc:eek:dbc:database_odbc");
    prop.put("DATABASEUSERID", "sa");
    prop.put("DATABASEPASSWORD", "password");
    ConnectionI connectionI = ConnectionFactory.getConnectionI(2, props);
    java.sql.Connection conn = connectionI.getConnection();


    What do you think which solution is more gneric and more maintainable?
    JY, Nov 3, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mythran
    Replies:
    5
    Views:
    4,923
    Mythran
    Oct 5, 2005
  2. Dave Johnson

    Using Connection Class VS Connection Module

    Dave Johnson, Dec 9, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    616
    Dave Johnson
    Dec 11, 2005
  3. Lau Lei Cheong
    Replies:
    7
    Views:
    440
    Lau Lei Cheong
    Jan 14, 2006
  4. apue
    Replies:
    1
    Views:
    308
    Dorian
    Jul 23, 2006
  5. Jennifer

    ASP & Database Connection Question

    Jennifer, May 19, 2006, in forum: ASP General
    Replies:
    1
    Views:
    101
    Ron Hinds
    May 19, 2006
Loading...

Share This Page