Connecting to MySQL, PostgreSQL and Oracle

Connecting Java to a real database server takes three things: the database's JDBC driver on the class path, a connection URL in that database's format, and a user name and password. Everything after the connection, statements, result sets and transactions, is the same JDBC code for every database. In this lesson, you will learn what to add and what the URL looks like for MySQL, PostgreSQL and Oracle, see a complete example, and learn to recognize the errors that almost everyone meets on the first attempt.

What Each Database Needs

In Maven, the driver goes in pom.xml as a runtime dependency. For MySQL:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.1.0</version>
    <scope>runtime</scope>
</dependency>

Check Maven Central for the current version of whichever driver you use.

Connecting to MySQL

First create a database and a user in the MySQL client:

CREATE DATABASE school;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'choose-a-password';
GRANT ALL PRIVILEGES ON school.* TO 'app'@'localhost';

Then connect from Java:

String url = "jdbc:mysql://localhost:3306/school";
String user = System.getenv("DB_USER");
String password = System.getenv("DB_PASSWORD");
 
try (Connection con = DriverManager.getConnection(url, user, password);
     Statement st = con.createStatement();
     ResultSet rs = st.executeQuery("SELECT id, name FROM student")) {
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
}

Useful options are added to the URL after ?, for example jdbc:mysql://localhost:3306/school?serverTimezone=UTC.

Connecting to PostgreSQL

Only the URL changes:

String url = "jdbc:postgresql://localhost:5432/school";
try (Connection con = DriverManager.getConnection(url, user, password)) {
    // The same JDBC code as before.
}

Connecting to Oracle

Oracle's thin driver uses a service name (recommended) or the older SID form:

// Service name form:
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
// Older SID form:
// String url = "jdbc:oracle:thin:@localhost:1521:xe";
try (Connection con = DriverManager.getConnection(url, user, password)) {
    // The same JDBC code as before.
}

XEPDB1 is the default service of the free Oracle Database XE edition.

Example: MySQL Syntax Without a MySQL Server

You may not have a database server installed yet. H2 can imitate MySQL's SQL dialect, which lets you practice MySQL-style statements such as AUTO_INCREMENT and LIMIT with a single JAR:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class MySqlMode {
    public static void main(String[] args) throws Exception {
        // H2 pretending to be MySQL: the SQL below is MySQL syntax.
        String url = "jdbc:h2:mem:school;MODE=MySQL";
        try (Connection con = DriverManager.getConnection(url, "sa", "");
             Statement st = con.createStatement()) {
            st.execute("CREATE TABLE student ("
                + "id INT AUTO_INCREMENT PRIMARY KEY, "
                + "name VARCHAR(50) NOT NULL, marks INT)");
            st.executeUpdate("INSERT INTO student (name, marks) VALUES "
                + "('Navika', 91), ('Srihitha', 84), ('Advitha', 78)");
            try (ResultSet rs = st.executeQuery(
                    "SELECT id, name FROM student ORDER BY id LIMIT 2")) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + " "
                        + rs.getString("name"));
                }
            }
        }
    }
}

1 Navika
2 Srihitha

Explanation: MODE=MySQL in the URL tells H2 to accept MySQL's syntax. The Java code is exactly what you would write against a real MySQL server; there, the URL would be jdbc:mysql://localhost:3306/school. H2 also has MODE=PostgreSQL and MODE=Oracle. An imitation is good for learning and for fast tests, but always test against the real database before release, because the imitation isn't perfect.

The Errors You Will Meet First

Common Mistakes

Hard-coding credentials. They end up in version control and in every copy of the code.

Using the SID form with a service name. For Oracle, @//host:port/service and @host:port:sid are different.

Assuming SQL is identical everywhere. Auto-increment columns, row limits, date functions and string concatenation differ between databases.

Practice

1. Easy: Write the JDBC URL for a PostgreSQL database named library on host db.example.com, port 5433.
2. Harder: Install MySQL or PostgreSQL, create the school database and a student table, and run FirstQuery from "JDBC in Java" against it by changing only the URL, user and password.

Key Takeaways