Connection Interface in Java

A Connection object represents one session between your Java program and a database. Everything you do in JDBC goes through it: it creates statements, it controls transactions (commit and rollback), and it gives access to information about the database. A connection is a limited and fairly expensive resource: it holds a network socket and memory on the database server. So it must always be closed when you are finished. In this lesson, you will learn the methods of Connection, grouped by what they do, and what happens when a connection is closed.

Methods for Creating Statements

Methods for Transactions

These are covered in "Transaction Management in JDBC".

Other Methods

Example: Inspecting and Closing a Connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Conn {
    public static void main(String[] args) throws Exception {
        Connection con = DriverManager.getConnection("jdbc:h2:mem:school");
        System.out.println("autoCommit: " + con.getAutoCommit());
        System.out.println("readOnly:   " + con.isReadOnly());
        System.out.println("valid:      " + con.isValid(2));
        System.out.println("catalog:    " + con.getCatalog());
        System.out.println("schema:     " + con.getSchema());
        System.out.println("isolation is READ_COMMITTED? "
            + (con.getTransactionIsolation()
                == Connection.TRANSACTION_READ_COMMITTED));
 
        try (Statement st = con.createStatement()) {
            st.execute("CREATE TABLE note (text VARCHAR(20))");
        }
        con.close();
        System.out.println("closed:     " + con.isClosed());
        System.out.println("valid:      " + con.isValid(2));
        try {
            con.createStatement();
        } catch (SQLException e) {
            System.out.println("After close: " + e.getMessage());
        }
    }
}

autoCommit: true
readOnly:   false
valid:      true
catalog:    SCHOOL
schema:     PUBLIC
isolation is READ_COMMITTED? true
closed:     true
valid:      false
After close: The object is already closed [90007-232]

Explanation:

• autoCommit: true is the default for every new JDBC connection: each statement is its own transaction.
• isValid(2) asks the database to answer within 2 seconds. Connection pools use it to test a connection before handing it out.
• The isolation level defaults to READ_COMMITTED in H2, as in PostgreSQL and Oracle. MySQL's default is REPEATABLE_READ.
• After close(), isClosed() is true, isValid is false, and using the connection throws SQLException. Closing a connection also closes the statements and result sets created from it.

Is a Connection Thread-Safe?

Treat a Connection as not safe to share between threads. A transaction belongs to the connection, so two threads using one connection would mix their work into the same transaction. The rule in practice: each thread, or each request, borrows its own connection from a pool and returns it when done.

isClosed vs isValid

Common Mistakes

Not closing connections. The database has a limit on connections; leaks eventually cause "too many connections" errors.

Sharing one connection between threads. Transactions get mixed up.

Trusting isClosed() to detect a broken connection. Use isValid.

Leaving auto-commit off and forgetting to commit. The changes are lost when the connection closes.

Interview Corner

Practice

1. Easy: Print con.getMetaData().getURL() and getDatabaseProductVersion() for a connection.
2. Harder: Open a connection, call setReadOnly(true), and try an INSERT. Record what your database does; the behavior is a hint and varies by driver.

Key Takeaways