I am creating a java application that connects to multiple databases. A user will be able to select the database they want to connect to from a drop down box.
The program then connects to the database by passing the name to a method that creates an initial context so it can talk with an oracle web logic data source.
public class dbMainConnection {
private static dbMainConnection conn = null;
private static java.sql.Connection dbConn = null;
private static javax.sql.DataSource ds = null;
private static Logger log = LoggerUtil.getLogger();
private dbMainConnection(String database) {
try {
Context ctx = new InitialContext();
if (ctx == null) {
log.info("JDNI Problem, cannot get InitialContext");
}
database = "jdbc/" + database;
log.info("This is the database string in DBMainConnection" + database);
ds = (javax.sql.DataSource) ctx.lookup (database);
} catch (Exception ex) {
log.error("eMTSLogin: Error in dbMainConnection while connecting to the database : " + database, ex);
}
}
public Connection getConnection() {
try {
return ds.getConnection();
} catch (Exception ex) {
log.error("Error in main getConnection while connecting to the database : ", ex);
return null;
}
}
public static dbMainConnection getInstance(String database) {
if (dbConn == null) {
conn = new dbMainConnection(database);
}
return conn;
}
public void freeConnection(Connection c) {
try {
c.close();
log.info(c + " is now closed");
} catch (SQLException sqle) {
log.error("Error in main freeConnection : ", sqle);
}
}
}
My problem is what happens if say someone forgets to create the data source for the database but they still add it to the drop down box? Right now what happens is if I try and connect to a database that doesn't have a data source it errors saying it cannot get a connection. Which is what I want but if I connect to a database that does have a data source first, which works, then try and connect to the database that doesn't have a data source, again it errors with
javax.naming.NameNotFoundException: Unable to resolve 'jdbc.peterson'. Resolved 'jdbc'; remaining name 'peterson'.
Which again I would expect but what is confusing me is it then grabs the last good connection which is for a different database and process everything as if nothing happened.
Anyone know why that is? Is weblogic caching the connection or something as a fail safe? Is it a bad idea to create connections this way?
You're storing a unique datasource (and connection, and dbMainConnection) in a static variable of your class. Each time someone asks for a datasource, you replace the previous one by the new one. If an exception occurs while getting a datasource from JNDI, the static datasource stays as it is. You should not store anything in a static variable. Since your dbMainConnection class is constructed with the name of a database, and there are several database names, it makes no sense to make it a singleton.
Just use the following code to access the datasource:
public final class DataSourceUtil {
/**
* Private constructor to prevent unnecessary instantiations
*/
private DataSourceUtil() {
}
public static DataSource getDataSource(String name) {
try {
Context ctx = new InitialContext();
String database = "jdbc/" + name;
return (javax.sql.DataSource) ctx.lookup (database);
}
catch (NamingException e) {
throw new IllegalStateException("Error accessing JNDI and getting the database named " + name);
}
}
}
And let the callers get a connection from the datasource and close it when they have finished using it.