Database Connection question

J

JY

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
 
P

Pete Barrett

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
 
J

JY

Pete Barrett said:
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?
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top