Is It Safe to Use a Static Java.Sql.Connection Instance in a Multithreaded System

Is it safe to use a static java.sql.Connection instance in a multithreaded system?

is my use in static Connection object thread safe?

Absolutely not!

This way the connection going to be shared among all requests sent by all users and thus all queries will interfere with each other. But threadsafety is not your only problem, resource leaking is also your other problem. You're keeping a single connection open during the entire application's lifetime. The average database will reclaim the connection whenever it's been open for too long which is usually between 30 minutes and 8 hours, depending on DB's configuration. So if your web application runs longer than that, the connection is lost and you won't be able to execute queries anymore.

This problem also applies when those resources are held as a non-static instance variable of a class instance which is reused multiple times.

You should always acquire and close the connection, statement and resultset in the shortest possible scope, preferably inside the very same try-with-resources block as where you're executing the query according the following JDBC idiom:

public User find(String username, String password) throws SQLException {
    User user = null;

    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email FROM user WHERE username=? AND password=md5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (ResultSet resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getLong("id"));
                user.setUsername(resultSet.getString("username"));
                user.setEmail(resultSet.getString("email"));
            }
        }
    }       

    return user;
}

Note that you should not return a ResultSet here. You should immediately read it and map it to a non-JDBC class and then return it, so that the ResultSet can safely be closed.

If you're not on Java 7 yet, then use a try-finally block wherein you manually close the closeable resources in the reverse order as you've acquired them. You can find an example here: How often should Connection, Statement and ResultSet be closed in JDBC?

If you worry about connecting performance, then you should be using connection pooling instead. This is built-in into many Java EE application servers and even barebones servletcontainers like Tomcat supports it. Just create a JNDI datasource in the server itself and let your webapp grab it as DataSource. It's transparently already a connection pool. You can find an example in the first link of the list below.

Is java.sql.Connection thread safe?

If the JDBC driver is spec-compliant, then technically yes, the object is thread-safe, but you should avoid sharing connections between threads, since the activity on the connection will mean that only one thread will be able to do anything at a time.

You should use a connection pool (like Apache Commons DBCP) to ensure that each thread gets its own connection.

In a multi thread scenario is it safe to use a static instance of the Tomcat JDBC DataSource object?

Generally, yes. While the DataSource interface does not specify this explicitly, typically you'd use some connection pooling DataSource implementation in an application server, which is typically designed to work well in multi threaded environments. On paper some implementations might actually be mutable but typically that should not be the case.

Database connections for servlet threads?

If a database connection is shared between all threads , does this mean I should use connection pooling to the database?

Yes, definitely! JDBC connections and single-threaded and not thread-safe. Just introduce connection pool in between, fetch connection first and close it as soon as you can during the same request.

How often should Connection, Statement and ResultSet be closed in JDBC?

Always. You need to acquire and close them in the shortest possible scope to avoid resource leaking, transactional problems and exhausted connection pools. Not doing so would cause the DB to run out of resources sooner or later, resulting in exceptions like "Too many connections".

The normal JDBC idiom is the following, whereby all resources are opened and closed in the very same try-with-resources block:

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_LIST);
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            entities.add(map(resultSet));
        }
    }

    return entities;
}

Or when you're not on Java 7 yet:

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(SQL_LIST);
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            entities.add(map(resultSet));
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return entities;
}

Using PreparedStatement will give you the benefit of the DB caching of the statements (next to SQL injection prevention when used properly). Acquiring and closing the connection is the most expensive task, but there the connection pools are invented for. If you want to reuse the same statement to do bulk inserts/updates, then you can use batches.

How can I use one database connection object in whole application?

I suppose you need singleton pattern, here is quick example:

public class Connect_db {        
    static Connection con=null;
    public static Connection getConnection()
    {
        if (con != null) return con;
        // get db, user, pass from settings file
        return getConnection(db, user, pass);
    }

    private static Connection getConnection(String db_name,String user_name,String password)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost/"+db_name+"?user="+user_name+"&password="+password);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return con;        
    }
}

then you will be able to use connection like this:

Connect_db.getConnection().somemethods();

but, you should think - how this will work in multi-threaded environment, when several threads are trying to make requests to database.

If I use a singleton class for a database connection, can one user close the connection for everybody?

As long as you don't return the same Connection instance on getConnection() call, then there's nothing to worry about. Every caller will then get its own instance. As far now you're creating a brand new connection on every getConnection() call and thus not returning some static or instance variable. So it's safe.

However, this approach is clumsy. It doesn't need to be a singleton. A helper/utility class is also perfectly fine. Or if you want a bit more abstraction, a connection manager returned by an abstract factory. I'd only change it to obtain the datasource just once during class initialization instead of everytime in getConnection(). It's the same instance everytime anyway. Keep it cheap. Here's a basic kickoff example:

public class Database {

    private static DataSource dataSource;

    static {
        try {
            dataSource = new InitialContext().lookup("jndifordbconc");
        }
        catch (NamingException e) { 
            throw new ExceptionInInitializerError("'jndifordbconc' not found in JNDI", e);
        }
    }

    public static Connection getConnection() {
        return dataSource.getConnection();
    }

}

which is to be used as follows according the normal JDBC idiom.

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();

    try (
        Connection connection = Database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, foo, bar FROM entity");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            Entity entity = new Entity();
            entity.setId(resultSet.getLong("id"));
            entity.setFoo(resultSet.getString("foo"));
            entity.setBar(resultSet.getString("bar"));
            entities.add(entity);
        }
    }

    return entities;
}

Is It Safe to Use a Static Java.Sql.Connection Instance in a Multithreaded System