CODEWITHSUNDEEP
javadatabasedatabase-connection
Kalla
Kalla

Reputation: 165

Best way for creating database connections

What is the best way to create database connections in a Java application? Using Singleton, Static Method, Service Locator, Connection Pool or something else?

Please would you let me know the pros and cons of each of the above approaches?

Upvotes: 1

Views: 5888

Answers (3)

kisileno
kisileno

Reputation: 787

If your app will works with many connections it's preferred to use pool of connections. It's already realized in Java and you may easily use it. This example of using pool in web app that uses tomcat(if you writing web-app you also can use tomcat's pool, and it would be better)

package usepool;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

/**
*
* @author brainless
*/
public class ConnectionPool {

private static DataSource datasource;
public static String dbURL = "jdbc:mysql://localhost:3306/"
                + "<YourDataBase>?useUnicode=true&useEncoding=true&characterEncoding=UTF-8";
public static String driverClass = "com.mysql.jdbc.Driver";
public static String userName = "root";
public static String password = "password";
public static boolean jmx = true;
public static boolean testIdle = false;
public static boolean testBorrow = true;
public static boolean testReturn = false;
public static int validationInterval = 30000;
public static int timeBetweenEviction = 30000;
public static int maxActive = 100;
public static int initialSize = 10;
public static int maxWait = 10000;
public static int removeAbandonedTimeout = 60;
public static int minEvictableIdle = 30000;
public static int minIdle = 10;
public static boolean logAbandoned = true;
public static boolean removeAbandoned = true;
public static String jdbcInterceptors = "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
        + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer";

private ConnectionPool() {
}

public static synchronized DataSource getInstance() {
    if (datasource == null) {
        PoolProperties p = new PoolProperties();
        p.setUrl(dbURL);
        p.setDriverClassName(driverClass);
        p.setUsername(userName);
        p.setPassword(password);
        p.setJmxEnabled(jmx);
        p.setTestWhileIdle(testIdle);
        p.setTestOnBorrow(testBorrow);
        p.setTestOnReturn(testReturn);
        p.setValidationInterval(validationInterval);
        p.setTimeBetweenEvictionRunsMillis(timeBetweenEviction);
        p.setMaxActive(maxActive);
        p.setInitialSize(initialSize);
        p.setMaxWait(maxWait);
        p.setRemoveAbandonedTimeout(removeAbandonedTimeout);
        p.setMinEvictableIdleTimeMillis(minEvictableIdle);
        p.setMinIdle(minIdle);
        p.setLogAbandoned(logAbandoned);
        p.setRemoveAbandoned(removeAbandoned);
        p.setJdbcInterceptors(jdbcInterceptors);
        datasource = new DataSource();
        datasource.setPoolProperties(p);
    }
    return datasource;
}

public static synchronized void closePool() {
    if (datasource != null) {
        datasource.close();
    }
}

}

This class is singleton. To get connection in your code you need to use something like this

import usepool.ConnectionPool;
/* 
* code
*/
connect = ConnectionPool.getInstance().getConnection();

Upvotes: 2

Solubris
Solubris

Reputation: 3763

There are 2 approaches to handling connections:

  • one connection per request
  • connection on-demand

One connection per request means you get the connection at the start of the process, and return it at the end. This is not a good idea for web requests, as it means you application can only handle as many concurrent web requests as there are db connections available. However, it can be a good idea for batch processing, where you know you only need N connections.

Connection on-demand means you get the connection when you are about to do make a db call, and then return it as soon as the db call has finished.

In both cases, its recommended to use a connection pool, however its especially important for the connection on-demand strategy, as you want the connection to remain open after it is returned, because there is a significant overhead to opening connections.

There are generally two ways to handle connection pools:

  • container managed
  • application managed

Container managed means the application server provides the connection pool to the application via JNDI. This is what JBoss typically does. If it is there, you might as well use this approach. Otherwise, you should use an application managed pool.

Most DI frameworks will provide a connection pool for you with a little configuration. There is really no significant downside to using a connection pool. The two most common in java are DBCP and C3P0, both of which are very mature libraries. If you are not using a DI framework, you should review the getting started guides for those libs and implement what they suggest.

Upvotes: 3

Mike Thomsen
Mike Thomsen

Reputation: 37526

That's less important in most Java apps than using a connection pool. Compared to the convenience and gains you'll make from using a connection pool instead of manually managing the connections, your question is more of a matter of personal preference.

Upvotes: 0

Related Questions