Datenbankzugriffe mit JDBC

1. Datenbankzugriffe mit JDBC

Java Database Connectivity (JDBC) bietet eine Datenbankschnittstelle für den Zugriff auf unterschiedliche relationale Datenbanken und ermöglicht die Ausführung von SQL-Anweisungen auf einem relationalen Datenbankmanagementsystem (RDBMS). Die JDBC-API wird von einem JDBC-Treiber implementiert. In diesem Kapitel geht es um ein Beispiel mit der JDBC-API, damit Captain CiaoCiao für ein Piraten-Dating Personenmerkmale und Nutzerinformationen in einer Datenbank speichern kann.

Voraussetzungen

Maven-Projekt aufbauen und Dependencies ergänzen können
Datenbankmanagementsystem installieren können
Datenbankverbindung aufbauen können
Daten erfragen und einfügen können

Verwendete Datentypen in diesem Kapitel:

Noch mehr Aufgaben findest du im Buch: ›Captain CiaoCiao erobert Java: Das Trainingsbuch für besseres Java. 300 Java-Workshops, Aufgaben und Übungen mit kommentierten Lösungen‹

1.1. Datenbankmanagementsysteme

Für Übungen mit JDBC sind ein Datenbankmanagementsystem, eine Datenbank und Daten Voraussetzung. Die Aufgaben können mit jedem relationalen Datenbankmanagementsystem realisiert werden, weil es JDBC-Treiber für alle wichtigen Datenbankmanagementsysteme gibt und der Zugriff immer gleich aussieht. Das Kapitel greift auf das kompakte Datenbankmanagementsystem H2 zurück.

Es gibt grafische Werkzeuge, die Tabellen anzeigen und die Eingabe von SQL-Abfragen vereinfachen. Für Entwicklungsumgebungen gibt es oftmals Plugins; NetBeans besitzt einen SQL Editor und IntelliJ Ultimate enhält von Haus aus einen Datenbankeditor, für die freie Community-Edition gibt es zum Beispiel https://plugins.jetbrains.com/plugin/1800-database-navigator. Für Eclipse existieren unterschiedliche Plugins, von der Eclipse Foundation selbst das Eclipse Data Tools Platform (DTP) Project unter https://www.eclipse.org/datatools/downloads.php.

1.1.1. H2-Datenbank vorbereiten ⭐

H2 ist so kompakt, dass das Datenbankmanagementsystem, der JDBC-Treiber und eine kleine Admin-Oberfläche zusammen in einem JAR-Archiv verpackt sind.

Nimm in das Maven-POM folgende Dependency auf:

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

1.2. Datenbankabfragen

Jede Datenbankabfrage läuft über folgende Schritte:

Starten des Datenbankzugriffs durch Aufbauen der Verbindung
Absenden einer Anweisung
Einsammeln der Ergebnisse

1.2.1. Alle registrierten JDBC-Treiber abfragen ⭐

Java 6 hat die Service-Provider-API eingeführt, die Code automatisch ausführen kann, wenn er im Klassenpfad liegt und in einer besonderen Textdatei aufgeführt ist. JDBC-Treiber nutzen die Service-Provider-API, um sich automatisch an der Zentrale vom Typ DriverManager anzumelden.

Aufgabe:

Erfrage über den DriverManager alle angemeldeten JDBC-Treiber, und gib den Klassennamen auf den Bildschirm aus.

Lösungsvorschlag

1.2.2. Datenbank aufbauen und SQL-Skript ausführen ⭐

Captain CiaoCiao möchte Informationen über Piraten in einer relationalen Datenbank speichern. Ein erster Entwurf ergibt, dass der Rufname eines Piraten gespeichert werden soll, außerdem die E-Mail-Adresse, die Länge des Säbels, das Geburtsdatum und eine Kurzbeschreibung. Schreibe nach der Modellierung der Datenbank ein SQL-Skript, das die Tabellen aufbaut:

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

Die erste SQL-Anweisung löscht in H2 alle Einträge in der Datenbank. Anschließend erzeugt CREATE TABLE eine neue Tabelle mit unterschiedlichen Spalten und Datentypen. Jeder Pirat hat eine eindeutige ID, die von der Datenbank vergeben wird; wir sprechen von automatisch generierten Schlüsseln.

Das SQL im Buch folgt einer Namenskonvention:

SQL-Schlüsselwörter werden konsequent großgeschrieben.
Die Tabellennamen stehen im Singular und beginnen mit einem Großbuchstaben, genauso wie Klassennamen in Java mit einem Großbuchstaben beginnen.
Die Spaltennamen der Tabellen sind kleingeschrieben.

Ein Java SE-Programm nutzt den DriverManager, um über die Methode getConnection(…) eine Verbindung aufzubauen. In einer JDBC-URL stehen Informationen über die Datenbank und Verbindungsdetails, wie zum Beispiel Server und Port. Im Fall von H2 ist die JDBC-URL einfach, wenn kein Server kontaktiert werden, sondern das RDBMS Teil der eigenen Anwendung sein soll:

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

Existiert die Datenbank pirates-dating nicht, wird sie angelegt. getConnection(…) liefert anschließend die Verbindung zurück. Verbindungen müssen immer geschlossen werden. Das try-mit-Ressourcen übernimmt das Schließen, wie im oberen Code abzulesen.

Läuft das komplette RDBMS als Teil der eigenen Anwendung, nennt sich das Embedded Mode. Im Embedded Mode gilt, dass eine gestartete Java-Anwendung diese Datenbank exklusiv verwendet und sich nicht mehrere Java-Programme zu dieser Datenbank verbinden können. Mehrere Verbindungen sind nur mit einem Datenbankserver möglich. Auch das kann H2; Interessierte können das Vorgehen von der H2-Webseite entnehmen: https://www.h2database.com/html/tutorial.html

Aufgabe:

Lege eine Datei create-table.sql im Ressourcenverzeichnis des Maven-Projektes ab. Kopiere das SQL-Skript in die Datei.
Lege eine neue Java-Klasse an, und lade das SQL-Skript aus dem Klassenpfad.
Baue eine Verbindung zur Datenbank auf, und führe das geladene SQL-Skript aus.

Mit einem Kommandozeilentool können wir zum Schluss die Datenbank abfragen:

$ 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

Greife auf die Methode execute(…) von Statement zurück.

Lösungsvorschlag

1.2.3. Daten in die Datenbank einfügen ⭐

Die bisher aufgebaute Datenbank enthält keine Einträge. In den folgenden drei Programmen sollen Datensätze ergänzt werden. SQL bietet zum Einfügen von Datensätzen die Anweisung INSERT. Ein neuer Pirat kann mit folgendem SQL in die Datenbank eingefügt werden:

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

In der Angabe fehlt ausdrücklich der Primärschlüssel id, denn diese Spalte wird automatisch eindeutig belegt.

Aufgabe:

Baue eine neue Verbindung zur Datenbank auf, erzeuge ein Statement-Objekt, und sende das INSERT INTO mit executeUpdate(…) zur Datenbank.
Ein JDBC-Treiber kann den generierten Schlüssel liefern. Füge einen zweiten Piraten ein, und gib den generierten Schlüssel, ein long, auf dem Bildschirm aus. executeUpdate(…) liefert ein int zurück — was sagt das über die ausgeführte Anweisung aus?

Lösungsvorschlag

1.2.4. Daten im Batch in die Datenbank einfügen ⭐

Sollen mehrere SQL-Anweisungen ausgeführt werden, so lassen sie sich in einem Batch sammeln. Im ersten Schritt werden alle SQL-Anweisungen gesammelt und dann in einem Paket an die Datenbank übermittelt. Der JDBC-Treiber muss dann nicht für jede Abfrage über das Netzwerk zur Datenbank gehen.

Aufgabe:

Lege eine neue Klasse an und setze das folgende Array in das Programm:

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

Erzeuge aus den Informationen im Array SQL-INSERT Anweisungen, füge sie mit addBatch(…) dem Statement hin zu, und sende die Anweisungen mit executeBatch() ab.
Es liefert executeBatch() ein int[] zurück; was ist darin enthalten?

Lösungsvorschlag

1.2.5. Mit vorbereiteten Anweisungen Daten einfügen ⭐

Die dritte Möglichkeit, Daten einzufügen, ist in der Praxis die performanteste. Sie greift auf eine Eigenschaft von Datenbanken zurück, die vorbereiteten Anweisungen. Java unterstützt das mit dem Datentyp PreparedStatement. Dabei wird zunächst ein SQL-Statement mit Platzhaltern zu Datenbank geschickt, und später werden die Daten getrennt übermittelt. Das hat zwei Vorteile: Das Datenvolumen in der Kommunikation mit der Datenbank ist geringer, und die SQL-Anweisung ist im Allgemeinen von einer Datenbank geparst und vorbereitet, sodass die Ausführung schneller ist.

Aufgabe:

Lege eine neue Klasse an, und füge folgende Deklaration mit in den Code ein:

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

Erzeuge einen Prepared-Statement-String mit folgender SQL-Anweisung:

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

Gehe über die Liste data, fülle ein PreparedStatement, und sende die Daten ab.
Alle Einfügeoperationen sollen in einem großen transaktionalen Block vorgenommen werden.

Lösungsvorschlag

1.2.6. Daten erfragen ⭐

Durch unsere Operationen haben wir unterschiedliche Zeilen in die Datenbank gelegt; es wird Zeit, sie auszulesen!

Aufgabe:

Sende mit executeQuery(…) ein

SELECT nickname, swordlength, birthdate FROM Pirate

zur Datenbank.

Lies die Ergebnisse ein, und gib den Rufnamen, die Säbellänge und das Geburtsdatum auf dem Bildschirm aus.

Lösungsvorschlag

1.2.7. Interaktiv durch das ResultSet scrollen ⭐

Bei vielen Datenbanken kann ein Statement so konfiguriert werden, dass

das ResultSet nicht nur gelesen, sondern auch modifiziert werden kann, sodass sich einfach Daten in die Datenbank zurückschreiben lassen, und
der Cursor auf die Ergebnismenge nicht nur mit next() nach unten bewegt, sondern auch beliebig positioniert oder relativ nach oben gesetzt werden kann.

Captain CiaoCiao möchte in einer interaktiven Anwendung durch alle Piraten der Datenbanken scrollen.

Aufgabe:

Zu Beginn soll die Anwendung die Anzahl der Datensätze anzeigen.
Die interaktive Anwendung horcht auf Konsoleneingaben. d (down) oder n (next) soll das ResultSet mit der nächsten Zeile füllen, u (up) oder p (previous) mit der vorherigen Zeile. Nach der Eingabe soll der Rufname des Piraten ausgegeben werden; andere Details sind nicht gefragt.
Berücksichtige, dass next() nicht hinter die letzte Zeile springen kann und previous() nicht vor die erste Zeile.

Lösungsvorschlag

1.2.8. Pirate Repository ⭐⭐

Jede größere Anwendung greift in irgendeiner Weise auf externe Daten zurück. Aus dem Domain-driven-Design (DDD) gibt es das Konzept eines Repositorys. Ein Repository bietet CRUD-Operationen: create, read, update, delete. Das Repository ist ein Vermittler zwischen der Geschäftslogik und dem Datenspeicher. Java-Programme dürfen nur mit Objekten arbeiten, und das Repository bildet die Java-Objekte auf den Datenspeicher ab und konvertiert umgekehrt die nativen Daten in z. B. einer relationalen Datenbank auf Java-Objekte. Im besten Fall hat die Geschäftslogik überhaupt keine Ahnung, in welchem Format die Java-Objekte gespeichert werden.

Zum Austausch von Objekten zwischen der Geschäftslogik und der Datenbank wollen wir ein eigenes Java-Record Pirate nutzen. (Vor Java 16 muss eine Klasse eingesetzt werden, sie kann immutable sein und über einen parametrisierten Konstruktor verfügen.) Objekte, die auf relationale Datenbanken abgebildet werden, und eine ID haben, heißen im Java-Jargon Entity-Bean. Entity-Bean

Listing 1. com/tutego/exercise/jdbc/PirateRepositoryDemo.java

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

Die Geschäftslogik bezieht über das Repository die Daten oder schreibt sie zurück. Jede dieser Operationen wird durch eine Methode ausgedrückt. Jedes Repository sieht dabei ein bisschen anders aus, weil die Geschäftslogik jeweils unterschiedliche Informationen aus dem Datenspeicher erfragen oder in ihn zurückschreiben möchte.

Aufgabe:

In der Modellierung der Anwendung hat sich ergeben, dass ein PirateRepository nötig ist und drei Methoden anbieten muss:

List<Pirate> findAll(): Liefert eine Liste aller Piraten in der Datenbank.
Optional<Pirate> findById(long id): Liefert anhand einer ID einen Piraten oder, wenn kein Pirat mit der ID in der Datenbank enthalten ist, ein Optional.empty().
Pirate save(Pirate pirate): Speichert oder aktualisiert einen Piraten. Hat der Pirat noch keinen Primärschlüssel, ist also id == null, so soll ein SQL-INSERT den Piraten in die Datenbank schreiben. Hat der Pirat einen Primärschlüssel, so wurde der Pirat schon einmal in der Datenbank gespeichert, und die save(…) Methode muss stattdessen ein SQL-UPDATE zur Aktualisierung verwenden. Die save(…)-Methode antwortet mit einem Pirate-Objekt, das immer den gesetzten Schlüssel hat.

Nachdem ein PirateRepository entwickelt ist, soll Folgendes möglich sein:

Listing 2. 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. Spaltenmetadaten erfragen ⭐

Üblicherweise ist in Java-Programmen das Schema einer Datenbank bekannt, und bei den Anfragen können alle Spalten individuell ausgewertet werden. Es gibt jedoch Abfragen und Modellierungen, in denen die Anzahl der Spalten im Vorfeld nicht bekannt sind. JDBC kann nach einer getätigten Abfrage ein ResultSetMetaData erfragen, das Auskunft etwa über die Gesamtanzahl der Spalten und Datentypen der einzelnen Spalten liefert.

Aufgabe:

Schreibe eine Methode List<Map<String, Object>> findAllPirates(), die eine Liste von Assoziativspeichern zurückliefert. Die kleinen Map-Objekte in der Liste speichern die Zeileninhalte, indem der Assoziativspeicher den Namen der Spalte mit dem Eintrag in der Spalte verbindet.
Führe die SQL-Abfrage SELECT * FROM Pirate durch.

Lösungsvorschlag

Noch mehr Aufgaben findest du im Buch: ›Captain CiaoCiao erobert Java: Das Trainingsbuch für besseres Java. 300 Java-Workshops, Aufgaben und Übungen mit kommentierten Lösungen‹