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 ofOracle 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>Copy

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 anOracleDataSource, Oracle’s implementation of theDataSourceinterface. This is preferable to usingDriverManagersinceDataSourceis more scalable and easier to set up.

First, let’s initialize the connection properties and set the properties and the URL in theOracleDataSource. After that, we’ll callgetConnection()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(); }Copy

It should also be noted thatOracleDataSourcehas the methodssetUser()andsetPassword(),which can be used instead ofsetConnectionProperties(). However, it is helpful to note thatOracleConnectionprovides many property names statically, and this is how we would set other properties such as auto-commit, caching, or fetch sizes.

To test out ourgetConnection()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); }Copy

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.