Implementing DAO for a single datasource

Question

I will be using only single database for my project. It is pretty fixed that only one datasource (Oracle) will be used. So I create one Datasource class to create connections. I have kept the methods as static as I will be using only one datasource.

public class DataSource {

static Logger log = Logger.getLogger(DataSource.class);
static String connectionURL;
static String userName;
static String password;

static {
    log.debug("Creating ConnectionURL");
    Connection conn;
    Properties properties = new Properties();
    try {
        FileInputStream in = new FileInputStream(getCodesignRoot() + "/DBConnection/config.properties");
        properties.load(in);
    } catch (FileNotFoundException ex) {
        log.error("config.properties file not found", ex);
        log.info("SHUTTING DOWN APPLICATION");
        System.exit(-1);
    } catch (IOException ex) {
        log.error("Can't load the config.properties file", ex);
        log.info("SHUTTING DOWN APPLICATION");
        System.exit(-1);
    }

    String databaseServer = properties.getProperty("jdbc.databaseServer");
    String listenerPort = properties.getProperty("jdbc.listenerPort");        
    String oracleSID = properties.getProperty("jdbc.oracleSID");

    userName = properties.getProperty("jdbc.userName");
    password = properties.getProperty("jdbc.userPassword");

    connectionURL
            = "jdbc:oracle:thin:@//" + databaseServer + ":" + listenerPort
            + "/" + oracleSID;
}

Most importantly, I am having get connection method as static.

public static Connection getConnection() throws SQLException {
    try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException ex) {
            log.error("Driver Not Found", ex);
            throw new SQLException("Driver Not Found");
        }
        return DriverManager.getConnection(connectionURL, userName, password);
}

Is this a good design? Where should I keep my getConnection method? Is it ok for it to be static as I will be using only one datasource?

I forgot to add one part. What If I have to use two databases. One for developemnt and one for production, both oracle databases. How should I go about that? I want to make minimal changes, when I switch from development to production.

A design suiting your application is a good design. – Darshan Lila Dec 05 '14 at 11:52 — Darshan Lila, Dec 05 '14 at 11:52

lmazgon · Accepted Answer · 2014-12-05T16:54:43.143

If it's just an example/student/test project I would say it is okay. The getConnection method can go into the DataSource class. And since you know you won't change the database, you won't be having problems.

In a production environment it would be better, if you rename your DataSource to OracleDatasource and make it a singleton. It could implement an interface Datasource, which would define the method getConnection. That way you would decouple the DAO classes from the implementation of the datasource, meaning you could easily switch the OracleDatasource for example for a MySQLDatasource.

Although this would be better, the best way to go would be with a Connection pool. You could implement your own, but I would suggest using one of many widely used and supported libraries on the internet. To name a few: C3P0, DBCP, HikariCP.

Regarding the test and production database - the thing to do here is to have two (or more) properties files with connection parameters to each database. When deploying on a production environment you simply switch the properties file and this is it. Your implementation already supports that.

If you are deploying on a Tomcat and you use the built in Connection pool, then you have to configure the JNDI resource on the production server and on the test server, so that they are pointing each to the right database. Once this is set up, you don't have to worry any more.

If you are using a library and you read the connection parameters from a properties file, then you can put it in the lib folder in the root of the application server, since that folder is on the classpath of all deployed applications. Once that properties file is in place, the application will read it and connect to the defined database.

Regarding Connection pool, why there are libraries, when creating connection pool in tomcat is so straightforward. Is there some advantage of using these libraries instead of adding entries in context.xml, web.xml and getting datasource through jndi look-up. — Akhil, Dec 05 '14 at 16:35
The configuration through a library is as easy as that of a Tomcat connection pool. Also you could look at this question http://stackoverflow.com/q/20955875/1212463 — lmazgon, Dec 05 '14 at 16:44
I was also thinking of using cache(EHCache), But didn't find anywhere, where and how to use cache in my application. Can you suggest something? — Akhil, Dec 05 '14 at 17:46
I am no expert in this field, I'm afraid you are on your own on from here on out. I'm sure there is a lot written about cache, so you could google that and find out. — lmazgon, Dec 06 '14 at 16:10

Implementing DAO for a single datasource

1 Answers1