Databases

Problem

A catalog in a file

The store's catalog is a CSV file, as in the files module. While there are a hundred products, it works. Then there are a hundred thousand, and problems show up:

• to find one product, you read the whole file;
• "everything under €5, sorted by price" is a loop, an if and a sort, written by hand;
• two cash registers write to the file at the same time, and one record gets lost or the file gets damaged;
• the power goes out in the middle of a write, and half the file is gone.

That's why the data of almost every real program lives in a database: a program whose job is to keep data, find it fast and never lose it.

The idea

Tables and SQL

A relational database keeps data in tables. A table has columns (name, type) and rows (one for each product). The id column is the primary key: its value is different for each row, so it finds exactly one row.

You talk to the database in SQL. The five most common commands:

SQL

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

INSERT INTO products VALUES (1, 'Coffee', 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 makes a table, INSERT adds a row, UPDATE changes rows and DELETE removes them.
• SELECT reads: which columns, from which table, under what condition (WHERE) and in what order (ORDER BY).

You say what you want, and the database decides how to find it fast.

More precisely, if you are curious

• Well-known relational databases are PostgreSQL, MySQL, Oracle and SQL Server. They run as separate servers. Here we use H2: a small database written in Java that can live entirely in the program's memory. It's handy for examples and tests: every run starts with an empty database.
• Text in SQL goes in single quotes: 'Coffee'. DECIMAL(8, 2) is an exact number with 2 digits after the decimal point, which suits money.

See it

JDBC: connection, query, result

JDBC is the standard that Java uses to talk to any relational database. The connection is a Connection, the query goes through a Statement, and the result is a ResultSet. The setup method makes the table and adds three products:

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, 'Coffee', 7.50), (2, 'Tea', 3.20), (3, 'Chocolate', 2.40)");
    }
}

We read the rows and turn each one into an object of 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);
    }
}

Output

Product[id=3, name=Chocolate, price=2.4]
Product[id=2, name=Tea, price=3.2]
Product[id=1, name=Coffee, price=7.5]

• DriverManager.getConnection(address) opens the connection. The address says which database: here, H2 in memory, named shop.
• executeUpdate is for commands that change things (CREATE, INSERT, UPDATE, DELETE), and executeQuery is for SELECT.
• rs.next() moves to the next row and returns false when the rows run out. getInt, getString and getDouble get a column by name.
• The connection, the statement and the result have to be closed: that's why they're in try-with-resources.
• Almost every JDBC method throws the checked SQLException. Here, main and setup declare it with 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);
    }
}

Step by step, in words

1. A connection to the database in memory. setup makes the products table and adds 3 rows.
2. An empty list that the objects will go into.
3. SELECT with ORDER BY price: the database returns the rows as a ResultSet, cheapest first.
4. rs.next() moves to row 1. getInt, getString and getDouble get the columns, and new Product makes an object from them: Chocolate.
5. rs.next() moves to row 2. getInt, getString and getDouble get the columns, and new Product makes an object from them: Tea.
6. rs.next() moves to row 3. getInt, getString and getDouble get the columns, and new Product makes an object from them: Coffee.
7. rs.next() returns false: the rows ran out and the loop stops.
8. The list holds ordinary Java objects. Printing shows the record with its fields.

More precisely, if you are curious

• With a real database on a server, the address is different and there's a user and a password. You also need the database's driver as a dependency in the project (for MySQL: mysql-connector-j):

String url = "jdbc:mysql://localhost:3306/shop";
try (Connection conn = DriverManager.getConnection(url, "shop_app", "secret")) {
    System.out.println("Connected to the database!");
}

• The password isn't written in the code. It's read from the settings or from an environment variable.

• The prices here are double to keep it short. In a real store, a DECIMAL column is read with rs.getBigDecimal("price"), so there are no rounding errors.

Try it

What will it print?

The products are Coffee €7.50, Tea €3.20 and Chocolate €2.40. What will the program print?

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

Answer

Chocolate
Tea

Tea
Chocolate

Coffee
Tea
Chocolate

Check

Problem

The search that shows everything

The website searches for a product by the name the user types. The query is put together with +:

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("Searching: Tea");
    search(conn, "Tea");
    System.out.println("Searching: x' OR '1'='1");
    search(conn, "x' OR '1'='1");
}

Output

Searching: Tea
  Tea: €3.2
Searching: x' OR '1'='1
  Coffee: €7.5
  Tea: €3.2
  Chocolate: €2.4

With "Tea" it works. With x' OR '1'='1 the query becomes ... WHERE name = 'x' OR '1'='1'. The condition is always true, and the whole table comes out. The user's input has become part of the SQL. This is SQL injection, one of the most common attacks on websites. With the same trick, someone can log in without a password or delete a table.

The idea

PreparedStatement

The solution is to send the query and the values separately. A PreparedStatement has ? in place of the values, and you pass them with setString, setInt and setDouble (the numbers start at 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"));
            }
        }
    }
}

Output

Searching: Tea
  Tea: €3.2
Searching: x' OR '1'='1

Now x' OR '1'='1 is just text that is searched for as a name. There's no such product, and nothing comes out. Whatever the user types, it can't become SQL.

The rule is simple: a value from the user, from a file or from the network is never glued into SQL. Always use ?.

Try it

Coffee with an apostrophe

The method renames a product. With the name Coffee 'Arabica' the program stops with Syntax error in SQL statement "UPDATE products SET name = 'Coffee '[*]Arabica'' WHERE id = 1". Which line has the bug?

Pick the line with the bug.

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}

Check

The idea

Transaction: all or nothing

Ana transfers €150 to Boris. That's two queries: add to Boris, subtract from Ana. The table has the condition CHECK (balance >= 0), so the second query fails: Ana has only €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, 'Ana', 100), (2, 'Boris', 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("The transfer of €150 was refused.");
    }
    printBalances(conn);
}

Output

The transfer of €150 was refused.
  Ana: €100
  Boris: €200

The transfer was refused, but Boris has already received €150 out of nothing. By default, every query is saved right away. The two queries must be a transaction: either both go through, or neither does.

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

Output

The transfer of €150 was refused.
  Ana: €100
  Boris: €50

• setAutoCommit(false) starts a transaction: the changes aren't final yet.
• commit() saves them all at once.
• rollback() undoes them all if something fails. The exception goes on up, so the caller finds out too.
• finally puts the connection back in the normal mode.

More precisely, if you are curious

• H2's message for the failed query is Check constraint violation: "NO_DEBT: ". NO_DEBT is the name of the condition: in the table it's balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Databases promise four things about transactions, known as ACID: all or nothing (atomicity), the data stays correct according to the table rules (consistency), transactions running at the same time don't get in each other's way (isolation), and what's saved isn't lost even if the power goes out (durability).

The idea

ORM: objects instead of rows

With JDBC, for each class you write SQL and turn the rows into objects by hand. An ORM (object-relational mapping) does this for you: you describe the class with annotations, and the library makes the table, the queries and the objects by itself. The standard in Java is JPA, and the best-known library that implements it is 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 says "this class is a table", and @Table gives its name.
• @Id is the primary key, and @GeneratedValue lets the database give it a value.
• @Column sets details for the column. Fields without an annotation become columns too.
• The empty constructor is required: Hibernate uses it to make the object before filling in the fields.

Saving and reading go through the EntityManager:

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

    em.getTransaction().begin();
    em.persist(new Product("Coffee", 7.50));
    em.persist(new Product("Tea", 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 saves the object, and it does so in a transaction, as in JDBC. The query is in JPQL: it looks like SQL, but it talks about the Product class and its price field, not about the table. The result is a list of ready objects. The driver and the address are set 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>

Here on the site, this code is only compiled: to run it, you need Hibernate with all its libraries, and a configuration.

More precisely, if you are curious

• With Hibernate's own interface, the same thing goes through a Session, and the queries are in HQL (Hibernate's language, which JPQL grew out of):

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

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

• You can also build a query with code, without text: the Criteria API. It's handy when the conditions are put together from the user's choices:

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

• A transaction with EntityManager, undone on an error:

EntityTransaction tx = em.getTransaction();
try {
    tx.begin();
    // changes to the entities
    tx.commit();
} catch (RuntimeException e) {
    if (tx.isActive()) {
        tx.rollback();
    }
    throw e;
} finally {
    em.close();
}

• Hibernate 5 and JPA 2.2 use the javax.persistence package. From JPA 3 (Hibernate 6) on, the package is jakarta.persistence, and the rest is almost the same.

Exercise

A store with a database

Solve the tasks in your editor, with H2 in the project (com.h2database:h2) and the address jdbc:h2:mem:....1. Catalog. Make a products table and the methods add, setPrice, remove and list, all with PreparedStatement except list. Add Coffee (€7.50), Tea (€3.20) and Chocolate (€2.40), print them, change the price of the coffee to €6.90, delete the chocolate and print again:

Output

1. Coffee: €7.5
2. Tea: €3.2
3. Chocolate: €2.4
---
1. Coffee: €6.9
2. Tea: €3.2

2. Search by part of the name. The products are Coffee, Cocoa, Tea and Éclair. A method find(conn, text) finds all products whose name contains the text, ignoring upper and lower case: WHERE LOWER(name) LIKE ? with the value "%" + text.toLowerCase() + "%". Try "CO", "éclair" and an injection attempt:

Output

Searching for "CO":
  Cocoa: €4.8
  Coffee: €7.5
Searching for "éclair":
  Éclair: €2.4
Searching for "' OR '1'='1":
  no products found

3. A transfer that fails halfway. The accounts are Ana (€100) and Boris (€50). transfer(conn, from, to, amount) first subtracts from from, then adds to to. If executeUpdate returns 0 (no such account), throw SQLException("No account " + id). Everything is in a transaction. Transfer €30 from 1 to 2, then €40 from 1 to the account 99, which doesn't exist:

Output

Transferred €30 from 1 to 2
Refused: No account 99
  Ana: €70
  Boris: €80

The second transfer took €40 from Ana before it failed, but rollback gave it back.

Test

Check yourself

1. What does executeQuery return?

A list of Product objectsThe number of changed rowsA ResultSet: the rows of the result, which you walk through with next()

2. Why is "... WHERE name = '" + name + "'" dangerous?

Because the text from the user becomes part of the SQL and can change the queryIt is not dangerous if name is a StringBecause it is slow

3. A transfer is two queries. The first one succeeds, the second one fails. What does rollback() do?

It repeats the second queryIt undoes all changes since the start of the transaction, the first one tooIt undoes only the second one

4. What does an ORM like Hibernate do?

It replaces the databaseIt turns objects into rows and back, without you writing SQL for everythingIt only makes SQL queries faster

Check

Start the lesson over