Connect to Oracle Database with JDBC Driver

1. Overview

The Oracle Database is one of the most popular relational databases. In this tutorial, we’ll learn how to connect to an Oracle Database using a JDBC Driver.

2. The Database

To get us started, we need a database. If we don’t have access to one, let’s either download and install a free version from Oracle Database Software Downloads or use one of the docker images found at Oracle Database Container Images.

For this article, let’s build and run a docker image of Oracle Database 23ai (23.5.0).

3. Maven Setup

Now that we have a database let’s add the required dependency to our project for Oracle’s JDBC driver. We’ll use ojdbc11 to connect to Oracle 23ai:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>23.5.0.24.07</version>
</dependency>

The most recent version of ojdbc11 can be found in the Central Maven Repository. This dependency requires Java 11 or later and is the recommended driver for more recent versions of Java.

For legacy support, ojdbc8 is available for Java 8. Oracle also recommends ojdbc10 as the driver for Oracle 19c.

4. Connect to Oracle Database

To connect to the database, let’s create an OracleDataSource, Oracle’s implementation of the DataSource interface. This is preferable to using DriverManager since DataSource is more scalable and easier to set up.

First, let’s initialize the connection properties and set the properties and the URL in the OracleDataSource. After that, we’ll call getConnection() to retrieve a new connection:

public static Connection getConnection(String databaseUrl, String userName, String password) throws SQLException {
    var connectionProperties = new Properties();
    connectionProperties.setProperty(OracleConnection.CONNECTION_PROPERTY_USER_NAME, userName);
    connectionProperties.setProperty(OracleConnection.CONNECTION_PROPERTY_PASSWORD, password);

    var oracleDataSource = new OracleDataSource();
    oracleDataSource.setConnectionProperties(connectionProperties);
    oracleDataSource.setURL(databaseUrl);

    return oracleDataSource.getConnection();
}

It should also be noted that OracleDataSource has the methods setUser() and setPassword(), which can be used instead of setConnectionProperties(). However, it is helpful to note that OracleConnection provides many property names statically, and this is how we would set other properties such as auto-commit, caching, or fetch sizes.

To test out our getConnection() method, let’s retrieve the username:

@Test
void whenConnectionRetrieved_thenUserNameIsReturned() throws SQLException {
    var url = "jdbc:oracle:thin:@//localhost:1521/FREEPDB1";
    var userName = "BAELDUNG";
    var password = "baeldung_pw";

    String retrievedUser = null;
    try (var connection = ConnectToOracleDb.getConnection(url, userName, password)) {
        retrievedUser = connection.getMetaData().getUserName();
    }

    assertEquals(userName, retrievedUser);
}

This example creates the connection in a try-with-resources block, automatically closing the connection after we’re done.

If any mistake is made in the URL, username, or password, we would expect to see an ORA error. For example, ORA-17868 would indicate an unknown host and ORA-01017 indicates a credentials error.

5. Optimizing Performance With Connection Pooling

There are many things to consider when optimizing performance.  In particular, if you are setting up a web application, consider using a connection pool.

A connection pool is a cache of database connections that can be reused.

Oracle provides a Universal Connection Pool (ucp11) for Java 11+ and up and Java 8. This is an additional dependency on top of the JDBC dependency, but many other libraries, such as Hikari, Tomcat, and Apache Commons DBCP2, are available for connection pooling.

6. Conclusion

As we now know, retrieving a connection to the database does not require much effort.