Datenbanken

Kurz gesagt: Eine Datenbank speichert Daten in Tabellen und findet sie mit einer SQL-Abfrage schnell; in Java sprichst du über JDBC mit ihr, und PreparedStatement schützt vor SQL-Injection.

Problem

Ein Katalog in einer Datei

Der Katalog des Shops ist eine CSV-Datei, wie im Modul über Dateien. Solange es hundert Produkte sind, geht das. Dann werden es hunderttausend, und es gibt Probleme:

• um ein Produkt zu finden, liest du die ganze Datei;
• „alles unter 5 €, nach Preis sortiert“ ist eine Schleife, ein if und eine Sortierung, alles von Hand geschrieben;
• zwei Kassen schreiben gleichzeitig in die Datei, und ein Eintrag geht verloren oder die Datei wird beschädigt;
• mitten beim Schreiben fällt der Strom aus, und die halbe Datei ist weg.

Deshalb liegen die Daten fast jedes echten Programms in einer Datenbank: einem Programm, dessen Aufgabe es ist, Daten aufzubewahren, sie schnell zu finden und nie zu verlieren.

Die Idee

Tabellen und SQL

Eine relationale Datenbank speichert Daten in Tabellen. Eine Tabelle hat Spalten (Name, Typ) und Zeilen (eine für jedes Produkt). Die Spalte id ist der Primärschlüssel: Ihr Wert ist in jeder Zeile anders, deshalb findet man mit ihm genau eine Zeile.

Mit der Datenbank sprichst du in SQL. Die fünf häufigsten Befehle:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Kaffee', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE legt eine Tabelle an, INSERT fügt eine Zeile ein, UPDATE ändert Zeilen und DELETE löscht sie.
• SELECT liest: welche Spalten, aus welcher Tabelle, unter welcher Bedingung (WHERE) und in welcher Reihenfolge (ORDER BY).

Du sagst, was du willst, und die Datenbank entscheidet, wie sie es schnell findet.

Genauer, für Neugierige

• Bekannte relationale Datenbanken sind PostgreSQL, MySQL, Oracle und SQL Server. Sie laufen als eigene Server. Hier benutzen wir H2: eine kleine Datenbank, in Java geschrieben, die ganz im Speicher des Programms leben kann. Das ist praktisch für Beispiele und Tests: Jeder Lauf beginnt mit einer leeren Datenbank.
• Text steht in SQL in einfachen Anführungszeichen: 'Kaffee'. DECIMAL(8, 2) ist eine exakte Zahl mit 2 Nachkommastellen, das passt zu Geld.

Sieh es dir an

JDBC: Verbindung, Abfrage, Ergebnis

JDBC ist der Standard, mit dem Java mit jeder relationalen Datenbank spricht. Die Verbindung ist eine Connection, die Abfrage geht über ein Statement, und das Ergebnis ist ein ResultSet. Die Methode setup legt die Tabelle an und fügt drei Produkte ein:

static void setup(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))");
        st.executeUpdate("INSERT INTO products VALUES (1, 'Kaffee', 7.50), (2, 'Tee', 3.20), (3, 'Schokolade', 2.40)");
    }
}

Wir lesen die Zeilen und machen aus jeder ein Objekt von record Product(int id, String name, double price):

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    List<Product> products = new ArrayList<>();
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT id, name, price FROM products ORDER BY price")) {
        while (rs.next()) {
            products.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getDouble("price")));
        }
    }
    for (Product p : products) {
        System.out.println(p);
    }
}

Ausgabe

Product[id=3, name=Schokolade, price=2.4]
Product[id=2, name=Tee, price=3.2]
Product[id=1, name=Kaffee, price=7.5]

• DriverManager.getConnection(address) öffnet die Verbindung. Die Adresse sagt, welche Datenbank: hier H2 im Speicher, mit dem Namen shop.
• executeUpdate ist für Befehle, die etwas ändern (CREATE, INSERT, UPDATE, DELETE), und executeQuery ist für SELECT.
• rs.next() geht zur nächsten Zeile und gibt false zurück, wenn die Zeilen zu Ende sind. getInt, getString und getDouble holen eine Spalte nach ihrem Namen.
• Die Verbindung, das Statement und das Ergebnis müssen geschlossen werden: Deshalb stehen sie in try-with-resources.
• Fast jede JDBC-Methode wirft die geprüfte SQLException. Hier deklarieren main und setup sie mit throws.

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    List<Product> products = new ArrayList<>();
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT id, name, price FROM products ORDER BY price")) {
        while (rs.next()) {
            products.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getDouble("price")));
        }
    }
    for (Product p : products) {
        System.out.println(p);
    }
}

Schritt für Schritt, in Worten

1. Eine Verbindung zur Datenbank im Speicher. setup legt die Tabelle products an und fügt 3 Zeilen ein.
2. Eine leere Liste, in die die Objekte kommen.
3. SELECT mit ORDER BY price: Die Datenbank gibt die Zeilen als ResultSet zurück, die günstigste zuerst.
4. rs.next() geht zu Zeile 1. getInt, getString und getDouble holen die Spalten, und new Product macht daraus ein Objekt: Schokolade.
5. rs.next() geht zu Zeile 2. getInt, getString und getDouble holen die Spalten, und new Product macht daraus ein Objekt: Tee.
6. rs.next() geht zu Zeile 3. getInt, getString und getDouble holen die Spalten, und new Product macht daraus ein Objekt: Kaffee.
7. rs.next() gibt false zurück: Die Zeilen sind zu Ende, und die Schleife hört auf.
8. Die Liste enthält gewöhnliche Java-Objekte. Die Ausgabe zeigt den Record mit seinen Feldern.

Genauer, für Neugierige

• Bei einer echten Datenbank auf einem Server ist die Adresse anders, und es gibt einen Benutzer und ein Passwort. Außerdem brauchst du den Treiber der Datenbank als Abhängigkeit im Projekt (für MySQL: mysql-connector-j):

String url = "jdbc:mysql://localhost:3306/shop";
try (Connection conn = DriverManager.getConnection(url, "shop_app", "geheim")) {
    System.out.println("Mit der Datenbank verbunden!");
}

• Das Passwort steht nicht im Code. Es wird aus den Einstellungen oder aus einer Umgebungsvariable gelesen.

• Die Preise sind hier double, damit es kurz bleibt. In einem echten Shop liest man eine DECIMAL-Spalte mit rs.getBigDecimal("price"), damit es keine Rundungsfehler gibt.

Probier es aus

Was wird ausgegeben?

Die Produkte sind Kaffee 7,50 €, Tee 3,20 € und Schokolade 2,40 €. Was gibt das Programm aus?

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT name FROM products WHERE price < 5 ORDER BY price DESC")) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Antwort

Schokolade
Tee

Tee
Schokolade

Kaffee
Tee
Schokolade

Prüfen

Problem

Die Suche, die alles zeigt

Die Website sucht ein Produkt nach dem Namen, den der Nutzer eintippt. Die Abfrage wird mit + zusammengesetzt:

static void search(Connection conn, String name) throws SQLException {
    String sql = "SELECT name, price FROM products WHERE name = '" + name + "'";
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql)) {
        while (rs.next()) {
            System.out.println("  " + rs.getString("name") + ": " + rs.getDouble("price") + " €");
        }
    }
}

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
    setup(conn);
    System.out.println("Suche: Tee");
    search(conn, "Tee");
    System.out.println("Suche: x' OR '1'='1");
    search(conn, "x' OR '1'='1");
}

Ausgabe

Suche: Tee
  Tee: 3.2 €
Suche: x' OR '1'='1
  Kaffee: 7.5 €
  Tee: 3.2 €
  Schokolade: 2.4 €

Mit „Tee“ funktioniert es. Mit x' OR '1'='1 wird die Abfrage zu ... WHERE name = 'x' OR '1'='1'. Die Bedingung ist immer wahr, und die ganze Tabelle kommt heraus. Die Eingabe des Nutzers ist Teil des SQL geworden. Das ist SQL-Injection, einer der häufigsten Angriffe auf Websites. Mit demselben Trick kann sich jemand ohne Passwort anmelden oder eine Tabelle löschen.

Die Idee

PreparedStatement

Die Lösung ist, die Abfrage und die Werte getrennt zu schicken. Ein PreparedStatement hat ? an den Stellen der Werte, und du übergibst sie mit setString, setInt und setDouble (die Nummern beginnen bei 1):

static void search(Connection conn, String name) throws SQLException {
    String sql = "SELECT name, price FROM products WHERE name = ?";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, name);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println("  " + rs.getString("name") + ": " + rs.getDouble("price") + " €");
            }
        }
    }
}

Ausgabe

Suche: Tee
  Tee: 3.2 €
Suche: x' OR '1'='1

Jetzt ist x' OR '1'='1 nur Text, der als Name gesucht wird. Ein solches Produkt gibt es nicht, und nichts kommt heraus. Was der Nutzer auch eintippt, es kann nicht zu SQL werden.

Die Regel ist einfach: Ein Wert vom Nutzer, aus einer Datei oder aus dem Netzwerk wird nie in SQL hineingeklebt. Immer ?.

Probier es aus

Kaffee mit Apostroph

Die Methode benennt ein Produkt um. Mit dem Namen Kaffee 'Arabica' bricht das Programm mit Syntax error in SQL statement "UPDATE products SET name = 'Kaffee '[*]Arabica'' WHERE id = 1" ab. In welcher Zeile ist der Fehler?

Wähle die Zeile mit dem Fehler.

1static void rename(Connection conn, int id, String newName) throws SQLException {2 try (Statement st = conn.createStatement()) {3 st.executeUpdate("UPDATE products SET name = '" + newName + "' WHERE id = " + id);4 }5}

Prüfen

Die Idee

Transaktion: alles oder nichts

Maria überweist 150 € an Jonas. Das sind zwei Abfragen: bei Jonas hinzufügen, bei Maria abziehen. Die Tabelle hat die Bedingung CHECK (balance >= 0), deshalb schlägt die zweite Abfrage fehl: Maria hat nur 100 €.

static void openAccounts(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))");
        st.executeUpdate("INSERT INTO accounts VALUES (1, 'Maria', 100), (2, 'Jonas', 50)");
    }
}

static void move(Connection conn, int id, int amount) throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        ps.setInt(1, amount);
        ps.setInt(2, id);
        ps.executeUpdate();
    }
}

static void printBalances(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT owner, balance FROM accounts ORDER BY id")) {
        while (rs.next()) {
            System.out.println("  " + rs.getString("owner") + ": " + rs.getInt("balance") + " €");
        }
    }
}

static void transfer(Connection conn, int from, int to, int amount) throws SQLException {
    move(conn, to, amount);
    move(conn, from, -amount);
}

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:bank")) {
    openAccounts(conn);
    try {
        transfer(conn, 1, 2, 150);
    } catch (SQLException e) {
        System.out.println("Die Überweisung von 150 € wurde abgelehnt.");
    }
    printBalances(conn);
}

Ausgabe

Die Überweisung von 150 € wurde abgelehnt.
  Maria: 100 €
  Jonas: 200 €

Die Überweisung wurde abgelehnt, aber Jonas hat schon 150 € aus dem Nichts bekommen. Standardmäßig wird jede Abfrage sofort gespeichert. Die zwei Abfragen müssen eine Transaktion sein: Entweder gehen beide durch, oder keine.

static void transfer(Connection conn, int from, int to, int amount) throws SQLException {
    conn.setAutoCommit(false);
    try {
        move(conn, to, amount);
        move(conn, from, -amount);
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}

Ausgabe

Die Überweisung von 150 € wurde abgelehnt.
  Maria: 100 €
  Jonas: 50 €

• setAutoCommit(false) beginnt eine Transaktion: Die Änderungen sind noch nicht endgültig.
• commit() speichert sie alle auf einmal.
• rollback() macht sie alle rückgängig, wenn etwas fehlschlägt. Die Exception geht weiter nach oben, damit auch der Aufrufer davon erfährt.
• finally stellt die Verbindung wieder auf den normalen Modus.

Genauer, für Neugierige

• Die Meldung von H2 für die fehlgeschlagene Abfrage ist Check constraint violation: "NO_DEBT: ". NO_DEBT ist der Name der Bedingung: In der Tabelle steht balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Datenbanken versprechen bei Transaktionen vier Dinge, bekannt als ACID: alles oder nichts (Atomarität), die Daten bleiben nach den Regeln der Tabelle korrekt (Konsistenz), gleichzeitig laufende Transaktionen stören sich nicht gegenseitig (Isolation), und was gespeichert ist, geht nicht verloren, auch wenn der Strom ausfällt (Dauerhaftigkeit).

Die Idee

ORM: Objekte statt Zeilen

Mit JDBC schreibst du für jede Klasse SQL und machst die Zeilen von Hand zu Objekten. Ein ORM (objektrelationale Abbildung, object-relational mapping) nimmt dir das ab: Du beschreibst die Klasse mit Annotationen, und die Bibliothek macht die Tabelle, die Abfragen und die Objekte selbst. Der Standard in Java ist JPA, und die bekannteste Bibliothek, die ihn umsetzt, ist Hibernate.

Product.java

@Entity
@Table(name = "products")
class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 50)
    private String name;

    private double price;

    protected Product() {
    }

    public Product(String name, double price) {
        this.name = name;
        this.price = price;
    }

    public String getName() {
        return name;
    }

    public double getPrice() {
        return price;
    }
}

• @Entity sagt „diese Klasse ist eine Tabelle“, und @Table gibt ihren Namen an.
• @Id ist der Primärschlüssel, und @GeneratedValue lässt die Datenbank ihm einen Wert geben.
• @Column legt Details der Spalte fest. Felder ohne Annotation werden auch zu Spalten.
• Der leere Konstruktor ist Pflicht: Hibernate erzeugt damit das Objekt, bevor es die Felder füllt.

Speichern und Lesen gehen über den EntityManager:

static void jpa() {
    EntityManagerFactory factory = Persistence.createEntityManagerFactory("shop");
    EntityManager em = factory.createEntityManager();

    em.getTransaction().begin();
    em.persist(new Product("Kaffee", 7.50));
    em.persist(new Product("Tee", 3.20));
    em.getTransaction().commit();

    List<Product> cheap = em.createQuery(
            "SELECT p FROM Product p WHERE p.price < :max ORDER BY p.price", Product.class)
        .setParameter("max", 5.0)
        .getResultList();
    for (Product p : cheap) {
        System.out.println(p.getName() + ": " + p.getPrice() + " €");
    }

    em.close();
    factory.close();
}

persist speichert das Objekt, und zwar in einer Transaktion, wie bei JDBC. Die Abfrage ist in JPQL: Es sieht aus wie SQL, spricht aber von der Klasse Product und ihrem Feld price, nicht von der Tabelle. Das Ergebnis ist eine Liste fertiger Objekte. Der Treiber und die Adresse stehen in META-INF/persistence.xml:

persistence.xml

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" version="2.2">
    <persistence-unit name="shop">
        <class>Product</class>
        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:shop"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
        </properties>
    </persistence-unit>
</persistence>

Hier auf der Website wird dieser Code nur kompiliert: Um ihn auszuführen, brauchst du Hibernate mit allen seinen Bibliotheken und eine Konfiguration.

Genauer, für Neugierige

• Mit der eigenen Schnittstelle von Hibernate geht dasselbe über eine Session, und die Abfragen sind in HQL (der Sprache von Hibernate, aus der JPQL entstanden ist):

static void hibernate() {
    SessionFactory factory = new Configuration().configure().buildSessionFactory();
    try (Session session = factory.openSession()) {
        Transaction tx = session.beginTransaction();
        session.save(new Product("Schokolade", 2.40));
        tx.commit();

        List<Product> cheap = session.createQuery("from Product where price < :max", Product.class)
            .setParameter("max", 5.0)
            .list();
        System.out.println("Günstig: " + cheap.size());
    }
    factory.close();
}

• Du kannst eine Abfrage auch mit Code bauen, ohne Text: mit der Criteria API. Das ist praktisch, wenn die Bedingungen aus der Auswahl des Nutzers zusammengesetzt werden:

static List<Product> named(EntityManager em, String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Product> query = cb.createQuery(Product.class);
    Root<Product> p = query.from(Product.class);
    query.select(p).where(cb.equal(p.get("name"), name));
    return em.createQuery(query).getResultList();
}

• Eine Transaktion mit EntityManager, die bei einem Fehler rückgängig gemacht wird:

EntityTransaction tx = em.getTransaction();
try {
    tx.begin();
    // Änderungen an den Entitäten
    tx.commit();
} catch (RuntimeException e) {
    if (tx.isActive()) {
        tx.rollback();
    }
    throw e;
} finally {
    em.close();
}

• Hibernate 5 und JPA 2.2 benutzen das Paket javax.persistence. Ab JPA 3 (Hibernate 6) heißt das Paket jakarta.persistence, und der Rest ist fast gleich.

Übung

Ein Shop mit Datenbank

Löse die Aufgaben in deinem Editor, mit H2 im Projekt (com.h2database:h2) und der Adresse jdbc:h2:mem:....1. Katalog. Lege eine Tabelle products an und die Methoden add, setPrice, remove und list, alle außer list mit PreparedStatement. Füge Kaffee (7,50 €), Tee (3,20 €) und Schokolade (2,40 €) hinzu, gib sie aus, ändere den Preis des Kaffees auf 6,90 €, lösche die Schokolade und gib sie wieder aus:

Ausgabe

1. Kaffee: 7.5 €
2. Tee: 3.2 €
3. Schokolade: 2.4 €
---
1. Kaffee: 6.9 €
2. Tee: 3.2 €

2. Suche nach einem Teil des Namens. Die Produkte sind Kaffee, Kakao, Tee und Éclair. Eine Methode find(conn, text) findet alle Produkte, deren Name den Text enthält, ohne auf Groß- und Kleinschreibung zu achten: WHERE LOWER(name) LIKE ? mit dem Wert "%" + text.toLowerCase() + "%". Probiere „KA“, „éclair“ und einen Injection-Versuch:

Ausgabe

Suche nach „KA“:
  Kaffee: 7.5 €
  Kakao: 4.8 €
Suche nach „éclair“:
  Éclair: 2.4 €
Suche nach „' OR '1'='1“:
  keine Produkte gefunden

3. Eine Überweisung, die mittendrin fehlschlägt. Die Konten sind Maria (100 €) und Jonas (50 €). transfer(conn, from, to, amount) zieht zuerst von from ab und fügt dann bei to hinzu. Wenn executeUpdate 0 zurückgibt (kein solches Konto), wirf SQLException("Kein Konto " + id). Alles ist in einer Transaktion. Überweise 30 € von 1 an 2, dann 40 € von 1 an das Konto 99, das es nicht gibt:

Ausgabe

Überwiesen: 30 € von 1 an 2
Abgelehnt: Kein Konto 99
  Maria: 70 €
  Jonas: 80 €

Die zweite Überweisung hat Maria 40 € abgezogen, bevor sie fehlschlug, aber rollback hat die 40 € zurückgebucht.

Test

Teste dich

1. Was gibt executeQuery zurück?

Ein ResultSet: die Zeilen des Ergebnisses, durch die du mit next() gehstEine Liste von Product-ObjektenDie Anzahl der geänderten Zeilen

2. Warum ist "... WHERE name = '" + name + "'" gefährlich?

Weil der Text des Nutzers Teil des SQL wird und die Abfrage verändern kannWeil es langsam istEs ist nicht gefährlich, wenn name ein String ist

3. Eine Überweisung besteht aus zwei Abfragen. Die erste gelingt, die zweite schlägt fehl. Was macht rollback()?

Es macht nur die zweite rückgängigEs macht alle Änderungen seit dem Beginn der Transaktion rückgängig, auch die ersteEs wiederholt die zweite Abfrage

4. Was macht ein ORM wie Hibernate?

Es ersetzt die DatenbankEs macht nur SQL-Abfragen schnellerEs macht aus Objekten Zeilen und umgekehrt, ohne dass du für alles SQL schreibst

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Daten in Dateien · Listen · Lambdas · record, switch und Muster
• Geht weiter in: Injektionen: wenn Daten zum Befehl werden · Monolith, modularer Monolith, Services · Transactional Outbox · Eine Transaktion über Services hinweg (Saga)
• Aufgaben zum Thema: Programme in der echten Welt