Java JDBC Connection

The JDBC Connection class, java.sql.Connection , represents a database connection to a relational database. Before you can read or write data from and to a database via JDBC, you need to open a connection to the database. This JDBC connection tutorial will show you how to do that.

Loading the JDBC Driver

The first thing you need to do before you can open a JDBC connection to a database is to load the JDBC driver for the database. Actually, from Java 6 this is no longer necessary, but doing so will not fail. You load the JDBC driver like this:

Class.forName("driverClassName");

Each JDBC driver has a primary driver class that initializes the driver when it is loaded. For instance, to load the H2Database driver, you write this:

Class.forName("org.h2.Driver");

You only have to load the driver once. You do not need to load it before every connection opened. Only before the first JDBC connection opened.

Opening the JDBC Connection

You open a JDBC Connection by call the java.sql.DriverManager class method getConnection() . There are three variants of this method. I will show each variant in the following sections.

Open Connection With URL

The first method variant only takes a URL to the database as parameter. This is how calling getConnection() only with the URL as parameter looks:

String url      = "jdbc:h2:~/test";   //database specific url.

Connection connection =
    DriverManager.getConnection(url);

The url is the url to your database. You should check the documentation for your database and JDBC driver to see what the format is for your specific database. The url shown above is for a H2Database.

Open Connection With URL, User and Password

The second variant of getConnection() takes both a database URL, a user name and a password as parameters. Here is an example of calling that variant of getConnection() :

String url      = "jdbc:h2:~/test";   //database specific url.
String user     = "sa";
String password = "";

Connection connection =
    DriverManager.getConnection(url, user, password);

The user and password parameters are the user name and password for your database.

Open Connection With URL and Properties

The third variant of getConnection() takes a database URL and a Properties object as parameter. Here is an example of calling this variant of getConnection() :

String url      = "jdbc:h2:~/test";   //database specific url.

Properties properties = new Properties( );
properties.put( "user", "sa" );
properties.put( "password", "" );

Connection connection =
    DriverManager.getConnection(url, properties);

The Properties object is used to pass special properties the database needs when opening the connection. Exactly what properties a given database needs, depends on the database and its features etc. You will have to check the documentation for given database and its JDBC driver to see that.

Closing the JDBC Connection

Once you are done using the database connection you should close it. This is done by calling the Connection.close() method, like this:

connection.close();

It is important to close a JDBC Connection once you are done with it. A database connection takes up an amount of resources, both inside your own application, but especially on the database server. Therefore, keeping database connections open that are unused will require the database to keep unnecessary resources allocated for the connection.

Closing the Connection via Try-With-Resources

It is possible to close a JDBC Connection automatically via the Java Try-with-resources construct that was added in Java 7. Here is an example of how to do that:

String url      = "jdbc:h2:~/test";   //database specific url.
String user     = "sa";
String password = "";

try(Connection connection =
    DriverManager.getConnection(url, user, password)) {

    //use the JDBC Connection inhere
}

As you can see, the JDBC Connection is opened within the parentheses in the try block. Inside the try block you can use the database connection as you normally would. Once the execution exits the try block, the JDBC Connection will get closed automatically for you. That way you do not forget to close the JDBC Connection yourself.

setAutoCommit()

The JDBC Connection setAutoCommit() method is used to switch the connection into, or out of, auto commit mode. In auto commit mode each single update sent to the database will be committed immediately, as if executed within its own transaction. When not in auto commit mode, each database transaction must be explicitly committed by calling the Connection commit() method. This is explained in more detail in the tutorial aboutJDBC Transactions.

Here is an example of switching a JDBC Connection into auto commit mode:

connection.setAutoCommit(true);

And here is an example of switching a JDBC Connection out of auto commit mode:

connection.setAutoCommit(false);

The default mode of a JDBC Connection if the auto commit mode is not specified is to have auto commit mode switched on.

commit()

The JDBC Connection commit() method commits a transaction. Exactly how transactions work and should be handled is covered in the JDBC Transactions Tutorial . Here is a simple example of committing a transaction via a JDBC Connection . Please note that the correct exception handling has been kept out of this example to make it brief.

connection.setAutoCommit(false);


// perform operations on the JDBC Connection
// which are to be part of the transaction

connection.commit();

Keep in mind, that if some of the operations in the transaction fail, you would most likely want to call the rollback() method instead of commit() .

rollback()

The Java JDBC Connection rollback() method rolls back the operations executed within the currently ongoing transaction. Exactly how to handle the calls to commit() and / or rollback() is covered in the JDBC Transactions Tutorial . Here is a simple example of calling the JDBC Connection rollback() method:

try{
    connection.setAutoCommit(false);


    // perform operations on the JDBC Connection
    // which are to be part of the transaction

    connection.commit();
} catch (SQLException e) {
    connection.rollback();
}

Notice how rollback() is called within the catch-block of a try-catch block. In case an exception is thrown while trying to carry out the operations in the transaction, the whole transaction is rolled back.

createStatement()

The JDBC Connection createStatement() creates aJDBC Statement object. A Statement instance can be used to execute SQL updates against the database , or to execute SQL queries against the database . Here is an example of creating a JDBC Statement instance via the JDBC Connection createStatement() method:

Statement statement = connection.createStatement();

prepareStatement()

The JDBC Connection prepareStatement() creates a JDBC PreparedStatement object. A PreparedStatement instance can be used to execute SQL updates against the database , or to execute SQL queries against the database . Here is an example of creating a JDBC PreparedStatement instance via the JDBC Connection prepareStatement() method:

String sql = "select * from people where id=?";

PreparedStatement preparedStatement =
        connection.prepareStatement(sql);

getMetaData()

The JDBC Connection getMetaData() method returns a JDBC DatabaseMetaData object which can be used to introspect the database the JDBC Connection is connected to. What you can do with the DatabaseMetaData is covered in the JDBC DatabaseMetaData Tutorial . Here is an example of creating a JDBC DatabaseMetaData object via the JDBC Connection getMetaData() method:

DatabaseMetaData databaseMetaData = connection.getMetaData();