How To Connect To Databases Using JDBC

Introduction

When working on Java applications, it is very likely that you will run into JDBC at some point. While you are unlikely to have to work directly with JDBC when using frameworks, it is still important to know how the connection is established and how queries are sent, so can detect performance issues in your application.

Databases come in all sizes and features, so the JDBC API was created to act as the only universal interface that your code needs to be aware of.

Goals

At the end of the tutorial, you would have learned how to:

1. Set up an H2 database in memory mode.
2. Prepopulate an H2 database with a SQL script and a CSV file.
3. Connect to a database in your Java program using JDBC.
4. Perform simple queries using PreparedStatement.
5. Read the results from a ResultSet.

Tools Required

1. A Java IDE such as IntelliJ IDEA version 2022.2 (Community Edition).

Prerequisite Knowledge

1. Basic Java.

Project Setup

To follow along with the tutorial, perform the steps below:

1. Import the Daniweb Maven JDBC repository into your IDE.

JDBC Driver

The first topic about JDBC that you need to know is the java.sql.Driver interface. Driver is how your Java application connect to the database, but it is only an interface with no concrete implementation. For each database that you want to connect to, you would need to download the drivers for them. The JAR files that you downloaded or loaded via build tools such as Maven or Gradle will contain the concrete implementations that you can use in code.

As an example, if your database is MySQL, then you would need to either

1. Download the driver JAR from https://dev.mysql.com/downloads/connector/j/ and include it in your class/module path

2. Load it via Maven (or another build tool) as

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>

If you open the pom.xml from the downloaded project, then you will see that there is a dependency to h2.

    <dependency>
       <groupId>com.h2database</groupId>
       <artifactId>h2</artifactId>
       <version>2.1.214</version>
    </dependency>

This dependency includes the entire H2 database as well as the Driver. The FQCN of the Driver implementation is org.h2.Driver, if you are curious.

JDBC Connection

The Connection here actually refers to three different things:

1. The connection to the database.
2. The JDBC interface java.sql.Connection.
3. The implementation of java.sql.Connection from H2, with the FQCN being org.h2.jdbc.JdbcConnection.

Let’s look at the part of our code that establishes the connection to the database.

final var dbUri = "jdbc:h2:mem:;"; //1
final var initScript = "INIT=runscript from 'create.sql';"; //2

try( //2
       final var conn = DriverManager.getConnection(dbUri + initScript); //3

a. At line 1, We establish the connection string that adheres to the JDBC URI syntax.
b. At line 2, we pass extra information specific to the H2 database that will execute the SQL script in the file create.sql.

CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))
   AS SELECT * FROM CSVREAD('Daniweb JDBC Data.csv');

c. The script uses the function CSVREAD() to populate the database with data from the CSV file.

id,name
1,Eve1
2,Eve2
3,Eve3
4,Eve4
5,Eve5
6,Eve6
7,Eve7
8,Eve8
9,Eve9
10,Eve10
11,Eve11
12,Eve12
13,Eve13
14,Eve14
15,Eve15
16,Eve16
17,Eve17
18,Eve18
19,Eve19

d. At line 3, we use DriverManager to get the Connection object. DriverManager will attempt to get the correct driver for you.

Create And Execute Statements

Via the Connection object, we can create PreparedStatement objects and execute them on the database. The PreparedStatement that you receive will be of the concrete type org.h2.jdbc.JdbcPreparedStatement.

final var query = conn.prepareStatement("SELECT * FROM test");
final var rs = query.executeQuery()

Note that other variants of executeQeury() methods also exist, such as executeUpdate() for updating or execute() for any kind of statement. You should use the method that makes the most sense for your query.

Reading ResultSet

executeQuery() returns a ResultSet, which is of the org.h2.jdbc.JdbcResultSet type.

To read the ResultSet, you can simply check whether the next row is empty or not, and then use the getX() methods to get the correct datatype.

while(rs.next()){
   System.out.printf(
           "id: %d, name: %s%n",
           rs.getInt(1),
           rs.getString(2)
   );
}

The code will print the content below.

id: 1, name: Eve1
id: 2, name: Eve2
id: 3, name: Eve3
id: 4, name: Eve4
id: 5, name: Eve5
id: 6, name: Eve6
id: 7, name: Eve7
id: 8, name: Eve8
id: 9, name: Eve9
id: 10, name: Eve10
id: 11, name: Eve11
id: 12, name: Eve12
id: 13, name: Eve13
id: 14, name: Eve14
id: 15, name: Eve15
id: 16, name: Eve16
id: 17, name: Eve17
id: 18, name: Eve18
id: 19, name: Eve19

Summary

We have learned quite a few things from this tutorial. By using the print statements with reflection, we know for sure that we were using the H2 implementations of the JDBC API.

System.out.println(query.getClass().getName());
System.out.println(conn.getClass().getName());
System.out.println(rs.getClass().getName());

org.h2.jdbc.JdbcPreparedStatement
org.h2.jdbc.JdbcConnection
org.h2.jdbc.JdbcResultSet