Database access with JDBC

1. Database access with JDBC

Java Database Connectivity (JDBC) provides access to various relational databases and enables the execution of SQL statements on a relational database management system (RDBMS). A JDBC driver implements the JDBC API. This chapter focuses on an example using the JDBC API to allow Captain CiaoCiao to store user information in a database for a pirate dating service.

Prerequisites

be able to build Maven project and add dependencies
be able to install database management system
be able to establish database connection
be able to query and insert data

Data types used in this chapter:

1.1. Database management systems

Exercises with JDBC require a database management system, a database, and data. The exercises can be realized with any relational database management system because there are JDBC drivers for all major database management systems and the access always looks the same. The chapter uses the compact database management system H2.

There are graphical tools that display tables and simplify the execution of SQL queries. For IDEs there are often plugins; NetBeans has a SQL editor and IntelliJ Ultimate includes a database editor out of the box, for the free community edition there is https://plugins.jetbrains.com/plugin/1800-database-navigator for example. For Eclipse there are different plugins, from the Eclipse Foundation itself the Eclipse Data Tools Platform (DTP) Project at https://www.eclipse.org/datatools/downloads.php.

1.1.1. Prepare H2 database ⭐

H2 is such a compact program that the database management system, the JDBC driver, and a small admin interface are bundled together in a JAR archive.

Include the following dependency in the Maven POM:

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

1.2. Database queries

Each database access runs through the following stages:

Starting the database access by establishing the connection
Sending a statement
Collecting the results

1.2.1. Query all registered JDBC drivers ⭐

Java 6 introduced the Service Provider API, which can automatically execute code if it is on the classpath and listed in a special text file. JDBC drivers use the Service Provider API to automatically register with the DriverManager.

Task:

Using the DriverManager, query all logged-in JDBC drivers, and output the class name to the screen.

Solution

1.2.2. Build database and execute SQL script ⭐

Captain CiaoCiao wants to store information about pirates in a relational database. An initial draft results in storing a pirate’s nickname, also email address, saber length, date of birth, and a brief description. After modeling the database, write an SQL script that builds the tables:

DROP ALL OBJECTS;

CREATE TABLE Pirate (
  id           IDENTITY,
  nickname     VARCHAR(255) UNIQUE NOT NULL,
  email        VARCHAR(255) UNIQUE NOT NULL,
  swordlength  INT,
  birthdate    DATE,
  description  VARCHAR(4096)
);

The first SQL statement deletes all entries in the database in H2. Then CREATE TABLE creates a new table with different columns and data types. Each pirate has a unique ID assigned by the database; we refer to automatically generated keys.

The SQL in the book follows a naming convention:

SQL keywords are consistently capitalized.
Table names are singular and start with a capital letter, just as class names in Java start with a capital letter.
Table column names are lowercase.

A Java SE program uses the DriverManager to establish a connection using the getConnection(…) method. A JDBC URL contains details about the database and connection details, such as server and port. In the case of H2, the JDBC URL is simple if no server should be contacted but the RDBMS should be part of the own application:

String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection = DriverManager.getConnection( jdbcUrl ) {
 …
}

If the database pirates-dating does not exist, it will be created. getConnection(…) returns the connection afterward. Connections must always be closed. The try-with-resources handles the closing, as can be seen in the code above.

If the complete RDBMS runs as part of its application, this is called embedded mode. In embedded mode, a started Java application uses this database exclusively and multiple Java programs cannot connect to this database. Multiple connections are only possible with one database server. H2 can do this as well; those interested can learn the details from the H2 website: https://www.h2database.com/html/tutorial.html.

Task:

Put a file create-table.sql in the resources directory of the Maven project. Copy the SQL script into the file.
Create a new Java class, and load the SQL script from the classpath.
Establish a connection to the database, and execute the loaded SQL script.

We can use a command-line tool to query the database at the end:

$ java -cp h2-1.4.200.jar org.h2.tools.Shell -url jdbc:h2:C:\pfad\zum\ordner\pirates-dating

Welcome to H2 Shell 1.4.200 (2019-10-14)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> SHOW TABLES;
TABLE_NAME | TABLE_SCHEMA
PIRATE     | PUBLIC
(1 row, 15 ms)
sql> exit
Connection closed

Access the execute(…) method of Statement.

Solution

1.2.3. Insert data into the database ⭐

The database built so far does not contain any records. In the following three programs, data records shall be added. SQL offers the INSERT statement for inserting new rows. A new pirate can be inserted into the database with the following SQL:

INSERT INTO Pirate (nickname, email, swordlength, birthdate, description)
VALUES ('CiaoCiao', 'captain@goldenpirates.faith', 18, DATE '1955-11-07', 'Great guy')

The primary key id is explicitly absent from the statement because this column is automatically uniquely assigned.

Task:

Establish a new connection to the database, create a Statement object, and send the INSERT INTO to the database with executeUpdate(…).
A generated key can be supplied by a JDBC driver. To add a second pirate, you can output the generated key (which is a long) to the screen. When executeUpdate(…) is called, it returns an int. This indicates something about the executed statement, but what is it?

Solution

1.2.4. Insert data into the database in batch mode ⭐

If several SQL statements are to be executed, they can be collected in a batch. In the first step, all SQL statements are collected and then transmitted to the database in one batch. The JDBC driver does have to send each query over the network to the database.

Task:

Create a new class and put the following array into the program:

String[] values = {
    "'anygo', 'amiga_anker@cutthroat.adult', 11, DATE '2000-05-21', 'Living the dream'",
    "'SweetSushi', 'muffin@berta.bar', 11, DATE '1952-04-03', 'Where are all the bad boys?'",
    "'Liv Loops', 'whiletrue@deenagavis.camp', 16, DATE '1965-05-11', 'Great guy'" };

From the data in the array, create SQL-INSERT statements, add them to the Statement with addBatch(…), and submit the statements with executeBatch().
executeBatch() returns an int[]; what is inside?

Solution

1.2.5. Insert data with prepared statements ⭐

The third way of inserting data is the most performant in practice. It makes use of a database feature, the prepared statements. Java supports this with the data type PreparedStatement. Here, first, an SQL statement with placeholders is sent to the database, and later the data is transmitted separately. This has two advantages: The volume of data in communication with the database is smaller, and the SQL statement is generally parsed and prepared by a database, so the execution is faster.

Task:

Create a new class, and include the following declaration in the code:

List<String[]> data = Arrays.asList(
    new String[]{ "jacky overflow", "bullet@jennyblackbeard.red", "17",
                  "1976-12-17", "If love a crime" },
    new String[]{ "IvyIcon", "array.field@graceobool.cool", "12",
                  "1980-06-12", "U&I" },
    new String[]{ "Lulu De Sea", "arielle@dirtyanne.fail", "13",
                  "1983-11-24", "You can be my prince" }
);

Create a prepared statement string with the following SQL statement:

String preparedSql = "INSERT INTO Pirate " +
                     "(nickname, email, swordlength, birthdate, description) " +
                     "VALUES (?, ?, ?, ?, ?)";

Loop over the list data, fill a PreparedStatement, and submit the data.
All insert operations should be done in one large transactional block.

Solution

1.2.6. Request data ⭐

Our recent program inserted new rows in the database; it’s time to read them out!

Task:

Send with executeQuery(…) a.

SELECT nickname, swordlength, birthdate FROM Pirate

to the database.

Read the results, and output the nickname, swordlength, and birthdate to the screen.

Solution

1.2.7. Interactive scrolling through the ResultSet ⭐

For many databases, a Statement can be configured so that.

the ResultSet can not only be read, but also modified, so that data can easily be written back to the database, and
the cursor on the result set can not only be moved down with next(), but can also be arbitrarily positioned or set relatively upwards.

Captain CiaoCiao wants to scroll through all the pirates of the databases in an interactive application.

Task:

Initially, the application should display the number of records.
The interactive application listens for console input. d (down) or n (next) shall fill the ResultSet with the next row, u (up) or p (previous) with the previous row. After the input, the call name of the pirate shall be output; no other details are asked.
Consider that next() cannot jump after the last line and previous() cannot jump before the first line.

Solution

1.2.8. Pirate Repository ⭐⭐

Every major application relies on external data in some way. From the domain-driven design (DDD), there is the concept of a repository. A repository provides CRUD operations: create, read, update, delete. The repository is an intermediary between the business logic and the data store. Java programs should only work with objects, and the repository maps the Java objects to the data store and, conversely, converts the native data in, for example, a relational database to Java objects. In the best case, the business logic has no idea whatsoever what format the Java objects are stored in. (Repository

To exchange objects between the business logic and the database, we want to use a custom Java record Pirate. (Before Java 16, you must use a class, it can be immutable and have a parameterized constructor). Objects that are mapped to relational databases, and have an ID, are called Entity-Bean in Java jargon. Entity Bean

com/tutego/exercise/jdbc/PirateRepositoryDemo.java

record Pirate(
    Long id,
    String nickname,
    String email,
    int swordLength,
    LocalDate birthdate,
    String description
) { }

The business logic retrieves or writes the data via the repository. Each of these operations is expressed by a method. Each repository looks a bit different because the business logic wants to retrieve or write back different information from or to the data store.

Task:

In modeling the application, it has been found that a PirateRepository is needed and must provide three methods:

List<Pirate> findAll(): returns a list of all pirates in the database.
Optional<Pirate> findById(long id): Returns a pirate using an ID or, if there is no pirate with the ID in the database, an Optional.empty().
Pirate save(Pirate pirate): Saves or updates a pirate. If the pirate does not have a primary key yet, which means id == null, a SQL INSERT shall be used to write the pirate to the database. If the pirate has a primary key, then the pirate has already been stored in the database before, and the save(…) method must use an SQL UPDATE to update it instead. The save(…) method responds with a Pirate object that always has the set key.

After a PirateRepository is developed, the following should be possible:

com/tutego/exercise/jdbc/PirateRepositoryDemo.java

PirateRepository pirates = new PirateRepository( "jdbc:h2:./pirates-dating" );
pirates.findAll().forEach( System.out::println );
System.out.println( pirates.findById( 1L ) );
System.out.println( pirates.findById( -1111L ) );
Pirate newPirate = new Pirate(
    null, "BachelorsDelight", "GoldenFleece@RoyalFortune.firm", 15,
    LocalDate.of( 1972, 8, 13 ), "Best Sea Clit" );
Pirate savedPirate = pirates.save( newPirate );
System.out.println( savedPirate );
Pirate updatedPirate = new Pirate(
    savedPirate.id(), savedPirate.nickname(), savedPirate.email(),
    savedPirate.swordLength() + 1, savedPirate.birthdate(),
    savedPirate.description() );
pirates.save( updatedPirate );
pirates.findAll().forEach( System.out::println );

Lösungsvorschlag

1.2.9. Query column metadata ⭐

Usually, in Java programs, the schema of a database is known, and queries can access all columns individually. However, there are queries and modeling where the number of columns is not known in advance. After a query, JDBC can request a ResultSetMetaData, which provides information about the total number of columns and data types of the individual columns.

Task:

Write a method List<Map<String, Object>> findAllPirates(). The small Map objects in the list contain an association between the column name and the content of that column.
Execute the SQL query SELECT * FROM Pirate.

Solution