JDBC: Share a connection or use connection pools

Question

In my project, I have many threads running for concurrent downloading from the web and inserting into/selecting from a MySQL database. My database manager looks like this:

private Connection conn;

private DatabaseManager() {
    // Initialize conn
}

public static synchronized DatabaseManager getInstance() {
    if (DatabaseManager.instance == null) {
        DatabaseManager.instance = new DatabaseManager();
    }
    return DatabaseManager.instance;
}

public void deleteX() {
    try {
        Statement stmt = conn.createStatement();
        String sql = //query
        stmt.executeUpdate(sql);
        stmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}



public X getX(...) {
    try {
        Statement stmt = conn.createStatement();
        ResultSet result= // Grab and do some thing
        result.close();
        stmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

public void insert(String query) {
    try {
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(query);
        stmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

In 100+ Threads, the call is for example:

DatabaseManager.getInstance().getX(1, 1);

or

DatabaseManager.getInstance().insert("INSERT INTO ...");

My question is: Am I on the right approach with a single connection being shared or is it better use for example 10 connections hold in a pool?

Problem is, I always get a ExecutionException (NullPointerException) with my current solution.

A connection pool will be better, check this : http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc — neohope, Aug 23 '16 at 03:47

score 0 · Accepted Answer · answered Sep 03 '16 at 17:05

In my software, connection pooling did not bring up the expected success, cause I tried to insert data into the same tables from different connections which lead to a deadlock. Therefore I kept implemented connection pooling, which gives 1 connection for 1 table.

JDBC: Share a connection or use connection pools

1 Answers1