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). The JDBC API is implemented by a JDBC driver. 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 exececution 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:

  1. Starting the database access by establishing the connection

  2. Sending a statement

  3. 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.

1.2.2. Build database and execute SQL script ⭐

Captain CiaoCiao wants to store information about pirates in a relational database. A first draft results in storing a pirate’s call name, also email address, saber length, date of birth and a short 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 afterwards. 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 own 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.

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 JDBC driver can supply the generated key. Add a second pirate and output the generated key, a long, to the screen. executeUpdate(…​) returns an int — what does this say about the executed statement?

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?

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 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.

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.

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.

1.2.8. Pirate Repository ⭐⭐

Every major application relies on external data in some way. From 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 the Java class Pirate. (In our case, however, there is nothing to be opposed to a Record.) Objects that are mapped to relational databases are called Entity-Bean in Java terminology.

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
class Pirate {
  public final Long id;
  public final String nickname;
  public final String email;
  public final int swordLength;
  public final LocalDate birthdate;
  public final String description;

  public Pirate( Long id, String nickname, String email, int swordLength,
                 LocalDate birthdate, String description ) {
    this.id = id;
    this.nickname = nickname;
    this.email = email;
    this.swordLength = swordLength;
    this.birthdate = birthdate;
    this.description = description;
  }

  @Override public String toString() {
    return "Pirate[" + "id=" + id + ", nickname='" + nickname + "'"
           + ", email='" + email + '\'' + ", swordLength=" + swordLength
           + ", birthdate=" + birthdate + ", description='" + 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 );

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 are 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.

1.3. Suggested solutions

1.3.1. Query all registered JDBC drivers

com/tutego/exercise/jdbc/DriverInfo.java
Collections.list( DriverManager.getDrivers() ).forEach(
    driver -> System.out.println( driver.getClass().getName() )
);

The static method DriverManager.getDrivers() returns an Enumeration<Driver>. Since we don`t want to run the Enumeration by hand, Collections.list(…​) helps to put the elements into a list. Lists are Iterable, and so we can run over all the installed drivers using a Consumer in a concise way and output the class name.

The output will be:

org.h2.Driver

If at this point the output is empty, this is an error and the following programs will not work due to the fact that the H2 driver is missing. In this case the dependency should be checked again.

1.3.2. Build database and execute SQL script

com/tutego/exercise/jdbc/CreateTable.java
final String filename = "create-table.sql";
String sql;
try ( var inputStream= CreateTable.class.getResourceAsStream( filename ) ) {
  sql = new String( inputStream.readAllBytes(), StandardCharsets.UTF_8 );
}
catch ( Exception e ) { return; }

String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection = DriverManager.getConnection( jdbcUrl );
      Statement  statement  = connection.createStatement() ) {
  statement.execute( sql );
}
catch ( SQLException e ) { e.printStackTrace(); }

For Java to load the SQL file as a resource from the classpath, it is placed in the src/main/resources folder for any Maven project. Instead of putting the SQL file in the root directory, it can be put in the same directory as the class. In the proposed solution, the class is fully qualified com.tutego.exercise.jdbc.CreateTable, which means create-table.sql is in the symmetric directory src/main/resources/com/tutego/exercise/jdbc/create-table.sql.

Accessing resources from the classpath is accomplished with XXX.class.getResourceAsStream(…​), in our case we don’t need to pay attention to the class loader; if Java programs otherwise load resources from the classpath, this may be relevant. getResourceAsStream(…​) returns an InputStream or null if the resource was not found — the program saves the query; if the file does not exist, a NullPointerException is already good.

The InputStream class has a readAllBytes() method, and we can pass the bytes into the constructor of String so that we have the file contents.

After loading the SQL script, it can be executed. The database connection is established and a statement is requested. Statement objects provide the execute(String) method, which executes arbitrary SQL. The method is rarely used, because usually returns are relevant, like the number of changed rows or in case of a SELECT the result.

The JDBC API reports errors via SQLException, a checked exception. In the proposed solution, the exception is caught, and errors are printed on the command line. Exception handling in JDBC is a bit more complicated, because the SQLException object can contain other exceptions in a chain. In addition, SQLException objects contain a status code that documents the exact error. The following proposed solutions are somewhat simplified in that they do not have a catch block for handling SQLException, instead the exceptions are passed up to the method caller with throws.

Java 8 Backport

The readAllBytes() method has only been around since Java 9. In Java 8, we can use different variants to load a file. One variant would be:

try ( Scanner scanner =
      new Scanner( CreateTable.class.getResourceAsStream(
      "create-table.sql" ), "UTF-8" ) ) {
  sql = scanner.useDelimiter( "\\z" ).next();
}

1.3.3. Insert data into the database

com/tutego/exercise/jdbc/InsertData.java
String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection = DriverManager.getConnection( jdbcUrl );
      Statement  statement  = connection.createStatement() ) {

  String sql1 =
      "INSERT INTO Pirate " +
      "(nickname, email, swordlength, birthdate, description) " +
      "VALUES ('CiaoCiao', 'captain@goldenpirate.faith', 18, " +
      "DATE '1955-11-07', 'Great guy')";
  statement.executeUpdate( sql1 );

  String sql2 =
      "INSERT INTO Pirate " +
      "(nickname, email, swordlength, birthdate, description) " +
      "VALUES ('lolalilith', 'fixme@bumblebee.space', 12, " +
      "DATE '1973-07-20', 'I’m 99% perfect')";
  int rowCount = statement.executeUpdate( sql2, Statement.RETURN_GENERATED_KEYS );
  if ( rowCount != 1 )
    throw new IllegalStateException( "INSERT didn’t return a row count of 1" );

  ResultSet generatedKeys = statement.getGeneratedKeys();
  if ( generatedKeys.next() )
    System.out.println( generatedKeys.getLong( 1 ) );
}

executeUpdate(…​) is used whenever an SQL statement like INSERT, UPDATE or DELETE is executed. The first variant of executeUpdate(…​) gets the SQL string and executes it. In the second call to executeUpdate(…​) the first argument is Statement.RETURN_GENERATED_KEYS which tells the database to pass the generated key along.

With the second executeUpdate(…​) the program also remembers the return, which says something about the number of modified records; it is 0 if the SQL statement has no return. If the INSERT in our example inserts a new record, then the return value will be 1, which we can use as a confirmation.

The return is not the generated key. It can’t be, because keys can also be strings, for example, and the return type of executeUpdate(…​) is always just int. The generated key is retrieved in a second statement, getGeneratedKeys(), via the Statement object. The method returns a ResultSet, as we will also see for SELECT statements later. The next() method determines if there is another row and fills the ResultSet with information, and the first column contains the generated primary key of type long. If next() returns false, there is no key to query.

1.3.4. Insert data into the database in batch mode

com/tutego/exercise/jdbc/BatchInsert.java
String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection = DriverManager.getConnection( jdbcUrl ) ) {
  connection.setAutoCommit( false );

  String sqlTemplate = "INSERT INTO Pirate " +
                       "(nickname, email, swordlength, birthdate, description) " +
                       "VALUES (%s)";

  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'" };

  try ( Statement statement = connection.createStatement() ) {
    for ( String value : values )
      statement.addBatch( String.format( sqlTemplate, value ) );

    int[] updateCounts = statement.executeBatch();
    connection.commit();
    System.out.println( Arrays.toString( updateCounts ) );
  }
}

The variable sqlTemplate contains a formatting string into which arbitrary VALUES can be injected later. With an extended for loop, the program runs over the entries of the array and connects the SQL template to the data. The resulting string is passed to addBatch(…​). The executeBatch(…​) method executes the collected SQL statements, and the return is an array containing exactly as many elements as SQL statements were executed in the batch. In the array, the cells contain the number of modified rows, just as we have seen before with the executeUpdate(…​) method.

The proposed solution does something else, and that is it resets the Auto-commit Mode. By default, the JDBC driver puts each SQL statement it sends into its own transactional block. For batch processing, it is undetermined whether the entire batch occurs in a transaction, parts of it, or each individual SQL statement. The Javadoc writes:

The commit behavior of executeBatch is always implementation-defined when an error occurs and auto-commit is true.

In the proposed solution, the batch should take place in a transaction. To do this, the auto-commit mode must first be switched off. After submitting via executeBatch() the transaction is completed with commit(). If the transaction is successful and there is no exception, all statements are committed.

1.3.5. Insert data with prepared statements

com/tutego/exercise/jdbc/PreparedInsert.java
String preparedSql = "INSERT INTO Pirate " +
                     "(nickname, email, swordlength, birthdate, description) " +
                     "VALUES (?, ?, ?, ?, ?)";

String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection  = DriverManager.getConnection( jdbcUrl );
      PreparedStatement stmt = connection.prepareStatement( preparedSql ) ) {

  connection.setAutoCommit( false );

  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" }
  );

  for ( String[] elements : data ) {
    stmt.setString( /* nickname    */ 1, elements[ 0 ] );
    stmt.setString( /* email       */ 2, elements[ 1 ] );
    stmt.setInt(    /* swordlength */ 3, Integer.parseInt( elements[ 2 ] ) );
    stmt.setDate(   /* birthdate   */ 4, Date.valueOf( elements[ 3 ] ) );
    stmt.setObject( /* description */ 5, elements[ 4 ] );
    stmt.executeUpdate();
  }

  connection.commit();
}

The procedure with a PreparedStatement is always the same: First, a PreparedStatement is built, which is always used in the following. The different setXXX(…​) methods occupy the placeholders in the prepared SQL statement. Each ? is identified by an index starting at 1. For example, setString(1, …​) assigns the pirate’s call name to the first question mark. The order is exclusively that of the question marks and not that of the columns. In our SQL statement the column id does not occur.

After filling each ? the prepared statement is sent with executeUpdate(). The automatically generated key can also be queried; we will return to this in a later task.

All operations are to be executed in a transactional block again, so the auto-commit mode is turned off again and at the end the transaction is confirmed with commit().

1.3.6. Request data

com/tutego/exercise/jdbc/Select.java
String sql = "SELECT nickname, swordlength, birthdate FROM Pirate";
String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection = DriverManager.getConnection( jdbcUrl );
      Statement  statement  = connection.createStatement();
      ResultSet  resultSet  = statement.executeQuery( sql ) ) {
  while ( resultSet.next() ) {
    String nickname = resultSet.getString( /* nickname column */1 );
    int swordlength = resultSet.getInt( "swordlength" );
    Date birthdate  = resultSet.getDate( "birthdate" );
    System.out.printf( "%-20s%-20s%10d%n",
      nickname,
      birthdate.toLocalDate().format(
          DateTimeFormatter.ofLocalizedDate(FormatStyle.LONG) ),
      swordlength );
  }
}

The executeXXX() methods of the Statement object return different result types. In the case of executeQuery(…​) the result is a ResultSet. The object allows access to the rows. A ResultSet will always contain only the information about one row at a time. The method call next() sets a kind of cursor on the next row of the result set and returns a boolean value with the information whether the next row could be read or not. If next() returns true, the ResultSet contains the information of one row.

The content of a column can be read using two different approaches: With the column index, which in SQL always starts at 1, or with the column name. There are also different getXXX(…​) methods that convert types. For all SQL data types, there are corresponding Java methods that give us the related Java type. For example, the getString(…​) method returns a String object for text columns. The JDBC driver performs various conversions so that, for example, getString(…​) works for any SQL column type. Java has three own data types for SQL date and time values in the package java.sql: Date, Time (time) and Timestamp (date and time). The datatypes allow converting to the datatypes of the Java Date-Time API. It returns getDate(…​) a java.sql.Date object that toLocalDate() converts to a known LocalDate that can be formatted with the usual API.

1.3.7. Interactive scrolling through the ResultSet

com/tutego/exercise/jdbc/ScrollableResultSet.java
int NICKNAME_COLUMN = 2;
String sql = "SELECT * FROM Pirate ORDER BY nickname";
String jdbcUrl = "jdbc:h2:./pirates-dating";
try ( Connection connection = DriverManager.getConnection( jdbcUrl );
      Statement  statement  = connection.createStatement(
                               ResultSet.TYPE_SCROLL_SENSITIVE,
                               ResultSet.CONCUR_READ_ONLY );
      ResultSet srs = statement.executeQuery( sql ) ) {

  if ( srs.last() )
    System.out.printf( "%d rows%n", srs.getRow() );

  srs.absolute( 1 );
  System.out.println( srs.getString( NICKNAME_COLUMN ) );

  for ( String input;
        !(input = new Scanner( System.in ).next()).equals( "q" ); ) {
    switch ( input.toLowerCase() ) {
      case "u": case "p":
        if ( srs.isFirst() ) System.out.println( "Already first" );
        else srs.previous();
        break;
      case "d": case "n":
        if ( srs.isLast() ) System.out.println( "Already last" );
        else srs.next();
        break;
    }
    System.out.println( srs.getString( NICKNAME_COLUMN ) );
  }
}

Before the ResultSet can be moved, the Statement object must be initialized correctly:

createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
                 ResultSet.CONCUR_READ_ONLY );

The Javadoc names the parameters resultSetType and resultSetConcurrency. The constants originate from ResultSet, and TYPE_SCROLL_SENSITIVE configures the request to be able to move the cursor freely in ResultSet. Not every database and database driver supports this feature, so a SQLFeatureNotSupportedException may be thrown.

If we succeed in building the Statement object, we can use the SELECT statement to build a ResultSet. Already the first question about the total number of elements can be realized using the moving cursor. If we call last() of the ResultSet object, it sets the cursor to the last element. The getRow() method then returns the current row, in our case the number of records in the result set. A call to absolute(1) sets the cursor back to the first row. The ResultSet is always filled with the current data; thus, if we access the column for the call name, we get exactly the contents of the row on which the cursor is currently positioned.

For interactive use, the Scanner helps with console input. The loop is executed as long as the user does not press q (quit). In case of u or p the user wants to move the cursor up. This is possible if the cursor is not already on the first line. This is checked by isFirst(). In the case of d or n, something similar is tested; the cursor can be moved to the next line with next(), unless the cursor is already on the last line.

1.3.8. Pirate Repository

Before we start with the actual implementation, we should think about possible exceptions. Even though any SQL query should succeed, all queries via the JDBC API return checked exceptions, which are inconvenient. Besides, a repository is supposed to hide the storage technology, and a SQLException does not come along very well. The chosen solution introduces a new class DataAccessException:

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
class DataAccessException extends RuntimeException {
  public DataAccessException( Throwable cause ) { super( cause ); }
  public DataAccessException( String message ) { super( message ); }
}

DataAccessException is an unchecked exception that wraps every exception. Internally, our repository methods will catch an SQLException and convert it to a DataAccessException.

The PirateRepository is more extensive, so we will deal with the type declaration, constants and constructor in the first part, and then with the individual methods in the next steps.

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
class PirateRepository {

  private static final String SQL_SELECT_ALL =
   "SELECT id, nickname, email, swordlength, birthdate, description " +
   "FROM Pirate";
  private static final String SQL_SELECT_BY_ID =
   "SELECT id, nickname, email, swordlength, birthdate, description " +
   "FROM Pirate WHERE id=?";
  private static final String SQL_INSERT =
   "INSERT INTO Pirate (nickname, email, swordlength, birthdate, description) " +
   "VALUES (?, ?, ?, ?, ?)";
  private static final String SQL_UPDATE =
   "UPDATE Pirate " +
   "SET nickname=?, email=?, swordlength=?, birthdate=?, description=? " +
   "WHERE id=?";

  private final String jdbcUrl;

  public PirateRepository( String jdbcUrl ) {
    this.jdbcUrl = jdbcUrl;
  }
  // ...
}

The PirateRepository class declares different constants for the SQL statements. We need one SQL statement each to query all pirates, to query a specific pirate with given ID, to insert new pirates and to update existing pirates. Only the first SQL statement to query all pirates does not use placeholders, otherwise the SQL strings will be used later in the PreparedStatement.

The program does not declare any constants for the columns, each index is documented in the code.

The constructor takes the JDBC URL and stores it in an attribute, so that later the individual methods can establish a new connection to this data source. If the identifier is invalid, there will be an exception later; null can be tested early.

We have already implemented the core of the individual methods in the previous tasks, so the JDBC API is not new.

For the first method findAll():

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
public List<Pirate> findAll() {
  try ( Connection connection = DriverManager.getConnection( jdbcUrl );
        Statement  statement  = connection.createStatement();
        ResultSet  resultSet  = statement.executeQuery( SQL_SELECT_ALL ) ) {
    List<Pirate> result = new ArrayList<>();
    while ( resultSet.next() )
      result.add( mapRow( resultSet ) );
    return result;
  }
  catch ( SQLException e ) {
    throw new DataAccessException( e );
  }
}

After the Statement is built, all records are selected. The while loop goes over the result set and calls its own private method mapRow(…​), which transfers a row in the ResultSet to a Pirate object. The result is placed in the list, and when the cursor reaches the end of the result, the list is returned.

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
private static Pirate mapRow( ResultSet resultSet ) throws SQLException {
  return new Pirate( resultSet.getLong(   /* id */          1 ),
                     resultSet.getString( /* nickname */    2 ),
                     resultSet.getString( /* email */       3 ),
                     resultSet.getInt(    /* swordLength */ 4 ),
                     resultSet.getDate(   /* birthdate */   5 ).toLocalDate(),
                     resultSet.getString( /* description */ 6 ) );
}

mapRow(…​) reads the columns with the known methods getLong(…​), getString(…​), getInt(…​), getDate(…​) and transfers the assignments to the constructor of Pirate. The Pirate has nothing to do with the SQL data type Date, so toLocalDate() converts to a LocalDate object. Moving the ResultSet object is no part of the mapRow(…​) duties.

The findById(…​) method also uses mapRow(…​):

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
public Optional<Pirate> findById( long id ) {
  try ( Connection connection = DriverManager.getConnection( jdbcUrl );
        PreparedStatement stmt = connection.prepareStatement(SQL_SELECT_BY_ID) ) {
    stmt.setLong( 1, id );
    ResultSet resultSet = stmt.executeQuery();
    return resultSet.next() ? Optional.of( mapRow( resultSet ) )
                            : Optional.empty();
  }
  catch ( SQLException e ) {
    throw new DataAccessException( e );
  }
}

After building a PreparedStatement object, the SQL query is sent and the ResultSet is evaluated. There are two alternatives: The ResultSet could have exactly one result or none. If there is no result, then there was no pirate for the ID and the return is Optional.empty(). However, if next() returns true, mapRow(…​) constructs a Pirate object from the ResultSet, which comes into the Optional and is returned.

The save(…​) method needs to detect whether the record must be updated or written again based on a ID that has been set.

com/tutego/exercise/jdbc/PirateRepositoryDemo.java
public Pirate save( Pirate pirate ) {
  try ( Connection connection = DriverManager.getConnection( jdbcUrl ) ) {
    return pirate.id == null ? saveInsert( connection, pirate )
                             : saveUpdate( connection, pirate );
  }
  catch ( SQLException e ) {
    throw new DataAccessException( e );
  }
}

private Pirate saveInsert( Connection connection, Pirate pirate )
    throws SQLException {
  try ( PreparedStatement stmt = connection.prepareStatement(
                                            SQL_INSERT,
                                            Statement.RETURN_GENERATED_KEYS ) ) {
    stmt.setString( 1, pirate.nickname );
    stmt.setString( 2, pirate.email );
    stmt.setInt(    3, pirate.swordLength );
    stmt.setDate(   4, Date.valueOf( pirate.birthdate ) );
    stmt.setObject( 5, pirate.description );
    stmt.executeUpdate();

    ResultSet keys = stmt.getGeneratedKeys();
    if ( keys.next() )
      return new Pirate( keys.getLong( 1 ),pirate.nickname,
                         pirate.email, pirate.swordLength, pirate.birthdate,
                         pirate.description );
    throw new DataAccessException(
        "Could not retrieve auto-generated key for " + pirate );
  }
}

private Pirate saveUpdate( Connection connection, Pirate pirate )
    throws SQLException {
  try ( PreparedStatement stmt = connection.prepareStatement( SQL_UPDATE ) ) {
    stmt.setString( 1, pirate.nickname );
    stmt.setString( 2, pirate.email );
    stmt.setInt(    3, pirate.swordLength );
    stmt.setDate(   4, Date.valueOf( pirate.birthdate ) );
    stmt.setObject( 5, pirate.description );
    stmt.setLong(   6, pirate.id );// UPDATE Pirate SET...WHERE id=?
    stmt.executeUpdate();
    return pirate;
  }
}

The if statement checks exactly this case. If id == null, an internal method saveInsert(…​) takes care of inserting a new record, otherwise saveUpdate(…​) updates the row. Both methods return a Pirate object, which is also the return of save().

saveInsert(…​) and saveUpdate(…​) both work with PreparedStatement, where saveInsert(…​) has to do a bit more work by asking for the generated key. Since Pirate objects are immutable, a new Pirate is created with the ID set. saveUpdate(…​) returns the passed Pirate object.

1.3.9. Query column metadata

com/tutego/exercise/jdbc/QueryForListOfMaps.java
public static List<Map<String, Object>> findAllPirates() throws SQLException {
  String jdbcUrl = "jdbc:h2:./pirates-dating";
  try ( Connection connection = DriverManager.getConnection( jdbcUrl );
        Statement  statement  = connection.createStatement();
        ResultSet  resultSet  = statement.executeQuery( "SELECT * FROM Pirate" ) ) {
    ResultSetMetaData metaData = resultSet.getMetaData();

    List<Map<String, Object>> result = new ArrayList<>();
    while ( resultSet.next() ) {
      LinkedHashMap<String, Object> map = new LinkedHashMap<>();
      for ( int col = 1, columns = metaData.getColumnCount();
            col <= columns; col++ ) {
        String columnName = metaData.getColumnName( col );
        map.put( columnName, resultSet.getObject( col ) );
      }
      result.add( map );
    }
    return result;
  }
}

After a query, the ResultSet method getMetaData() returns the metadata as a ResultSetMetaData object, and the number of columns in the result set is returned by getColumnCount(). Since a list is desired as the result, a container of type ArrayList is built. The ResultSet is iterated through as usual and a LinkedHashMap is built as Map to preserve the order of the columns. A for loop goes through the columns one by one from 1 to getColumnCount(), reading the column assignment with the getObject(…​) method and putting a new entry into the LinkedHashMap together with the column name. After a loop traversing all columns, the associative memory of one row contains all column mappings, and the Map is placed in the list. This is repeated for all rows, and at the end there is a list of associative stores that findAllPirates() returns.