Bases de datos

En resumen: Una base de datos guarda los datos en tablas y los encuentra rápido con una consulta SQL; en Java hablas con ella a través de JDBC, y PreparedStatement protege contra la inyección SQL.

Problema

Un catálogo en un archivo

El catálogo de la tienda es un archivo CSV, como en el módulo de archivos. Mientras hay cien productos, funciona. Luego hay cien mil, y aparecen problemas:

• para encontrar un producto, lees el archivo entero;
• «todo lo que cuesta menos de 5 €, ordenado por precio» es un bucle, un if y una ordenación, escritos a mano;
• dos cajas escriben en el archivo a la vez, y un registro se pierde o el archivo se estropea;
• se va la luz en mitad de una escritura, y desaparece la mitad del archivo.

Por eso los datos de casi cualquier programa real viven en una base de datos: un programa cuyo trabajo es guardar datos, encontrarlos rápido y no perderlos nunca.

La idea

Tablas y SQL

Una base de datos relacional guarda los datos en tablas. Una tabla tiene columnas (nombre, tipo) y filas (una por cada producto). La columna id es la clave primaria: su valor es distinto en cada fila, así que con ella se encuentra exactamente una.

Con la base de datos se habla en SQL. Los cinco comandos más comunes:

SQL

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

INSERT INTO products VALUES (1, 'Café', 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 crea una tabla, INSERT añade una fila, UPDATE cambia filas y DELETE las borra.
• SELECT lee: qué columnas, de qué tabla, con qué condición (WHERE) y en qué orden (ORDER BY).

Tú dices qué quieres, y la base de datos decide cómo encontrarlo rápido.

Más precisamente, para curiosos

• Bases de datos relacionales conocidas son PostgreSQL, MySQL, Oracle y SQL Server. Funcionan como servidores aparte. Aquí usamos H2: una base de datos pequeña escrita en Java, que puede vivir entera en la memoria del programa. Es práctica para ejemplos y tests: cada ejecución empieza con una base de datos vacía.
• El texto en SQL va entre comillas simples: 'Café'. DECIMAL(8, 2) es un número exacto con 2 decimales, adecuado para dinero.

Míralo

JDBC: conexión, consulta, resultado

JDBC es el estándar con el que Java habla con cualquier base de datos relacional. La conexión es una Connection, la consulta se envía a través de un Statement, y el resultado es un ResultSet. El método setup crea la tabla y añade tres productos:

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

Leemos las filas y convertimos cada una en un objeto de 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);
    }
}

Salida

Product[id=3, name=Chocolate, price=2.4]
Product[id=2, name=Té, price=3.2]
Product[id=1, name=Café, price=7.5]

• DriverManager.getConnection(dirección) abre la conexión. La dirección dice qué base de datos: aquí, H2 en memoria, con el nombre shop.
• executeUpdate es para los comandos que cambian algo (CREATE, INSERT, UPDATE, DELETE), y executeQuery es para SELECT.
• rs.next() pasa a la fila siguiente y devuelve false cuando se acaban las filas. getInt, getString y getDouble toman una columna por su nombre.
• La conexión, la consulta y el resultado hay que cerrarlos: por eso están en try-with-resources.
• Casi todos los métodos de JDBC lanzan la excepción comprobada SQLException. Aquí, main y setup la declaran con 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);
    }
}

Paso a paso, en palabras

1. Una conexión con la base de datos en memoria. setup crea la tabla products y añade 3 filas.
2. Una lista vacía, donde irán los objetos.
3. SELECT con ORDER BY price: la base de datos devuelve las filas como un ResultSet, de la más barata a la más cara.
4. rs.next() pasa a la fila 1. getInt, getString y getDouble toman las columnas, y new Product crea con ellas un objeto: Chocolate.
5. rs.next() pasa a la fila 2. getInt, getString y getDouble toman las columnas, y new Product crea con ellas un objeto: Té.
6. rs.next() pasa a la fila 3. getInt, getString y getDouble toman las columnas, y new Product crea con ellas un objeto: Café.
7. rs.next() devuelve false: se acabaron las filas y el bucle se detiene.
8. La lista tiene objetos Java normales. Al imprimirlos se ve el record con sus campos.

Más precisamente, para curiosos

• Con una base de datos real en un servidor, la dirección es otra y hay un usuario y una contraseña. Además necesitas el driver (controlador) de la base de datos como dependencia en el proyecto (para MySQL: mysql-connector-j):

String url = "jdbc:mysql://localhost:3306/shop";
try (Connection conn = DriverManager.getConnection(url, "shop_app", "secreto")) {
    System.out.println("¡Conectado a la base de datos!");
}

• La contraseña no se escribe en el código. Se lee de la configuración o de una variable de entorno.

• Aquí los precios son double para que sea breve. En una tienda real, una columna DECIMAL se lee con rs.getBigDecimal("price"), para que no haya errores de redondeo.

Pruébalo

¿Qué se imprimirá?

Los productos son Café 7,50 €, Té 3,20 € y Chocolate 2,40 €. ¿Qué imprimirá el programa?

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

Respuesta

Chocolate
Té

Té
Chocolate

Café
Té
Chocolate

Comprobar

Problema

El buscador que lo muestra todo

La web busca un producto por el nombre que escribe el usuario. La consulta se arma con +:

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

Salida

Buscando: Té
  Té: 3.2 €
Buscando: x' OR '1'='1
  Café: 7.5 €
  Té: 3.2 €
  Chocolate: 2.4 €

Con «Té» funciona. Con x' OR '1'='1 la consulta se convierte en ... WHERE name = 'x' OR '1'='1'. La condición siempre es verdadera, y sale la tabla entera. La entrada del usuario ha pasado a formar parte del SQL. Esto es la inyección SQL (SQL injection), uno de los ataques más comunes contra sitios web. Con el mismo truco, alguien puede entrar sin contraseña o borrar una tabla.

La idea

PreparedStatement

La solución es enviar la consulta y los valores por separado. Un PreparedStatement tiene ? en el lugar de los valores, y tú se los pasas con setString, setInt y setDouble (los números empiezan en 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") + " €");
            }
        }
    }
}

Salida

Buscando: Té
  Té: 3.2 €
Buscando: x' OR '1'='1

Ahora x' OR '1'='1 es solo un texto que se busca como nombre. No existe ese producto, y no sale nada. Escriba lo que escriba el usuario, no puede convertirse en SQL.

La regla es sencilla: un valor del usuario, de un archivo o de la red nunca se pega dentro del SQL. Usa siempre ?.

Pruébalo

Café con apóstrofo

El método cambia el nombre de un producto. Con el nombre Café 'Arábica' el programa se detiene con Syntax error in SQL statement "UPDATE products SET name = 'Café '[*]Arábica'' WHERE id = 1". ¿En qué línea está el error?

Elige la línea con el error.

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}

Comprobar

La idea

Transacción: todo o nada

Ana le transfiere 150 € a Carlos. Son dos consultas: sumarle a Carlos y restarle a Ana. La tabla tiene la condición CHECK (balance >= 0), así que la segunda consulta falla: Ana solo tiene 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, 'Carlos', 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("La transferencia de 150 € fue rechazada.");
    }
    printBalances(conn);
}

Salida

La transferencia de 150 € fue rechazada.
  Ana: 100 €
  Carlos: 200 €

La transferencia se rechazó, pero Carlos ya ha recibido 150 € de la nada. Por defecto, cada consulta se guarda enseguida. Las dos consultas tienen que ser una transacción: o pasan las dos, o ninguna.

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

Salida

La transferencia de 150 € fue rechazada.
  Ana: 100 €
  Carlos: 50 €

• setAutoCommit(false) empieza una transacción: los cambios todavía no son definitivos.
• commit() los guarda todos de una vez.
• rollback() los deshace todos si algo falla. La excepción sigue hacia arriba, para que también se entere quien llamó.
• finally devuelve la conexión al modo normal.

Más precisamente, para curiosos

• El mensaje de H2 para la consulta que falla es Check constraint violation: "NO_DEBT: ". NO_DEBT es el nombre de la condición: en la tabla es balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Las bases de datos prometen cuatro cosas sobre las transacciones, conocidas como ACID: todo o nada (atomicidad, atomicity), los datos siguen siendo correctos según las reglas de las tablas (consistencia, consistency), las transacciones que se ejecutan a la vez no se estorban (aislamiento, isolation), y lo que se ha guardado no se pierde aunque se vaya la luz (durabilidad, durability).

La idea

ORM: objetos en lugar de filas

Con JDBC, para cada clase escribes SQL y conviertes las filas en objetos a mano. Un ORM (object-relational mapping, mapeo objeto-relacional) lo hace por ti: describes la clase con anotaciones, y la biblioteca crea sola la tabla, las consultas y los objetos. El estándar en Java es JPA, y la biblioteca más conocida que lo implementa es 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 dice «esta clase es una tabla», y @Table le da el nombre.
• @Id es la clave primaria, y @GeneratedValue deja que la base de datos le dé un valor.
• @Column fija detalles de la columna. Los campos sin anotación también se convierten en columnas.
• El constructor vacío es obligatorio: Hibernate lo usa para crear el objeto antes de rellenar los campos.

Guardar y leer pasa por el EntityManager:

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

    em.getTransaction().begin();
    em.persist(new Product("Café", 7.50));
    em.persist(new Product("Té", 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 guarda el objeto, y lo hace en una transacción, como en JDBC. La consulta está en JPQL: se parece a SQL, pero habla de la clase Product y de su campo price, no de la tabla. El resultado es una lista de objetos ya hechos. El driver y la dirección se indican en 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>

Aquí en el sitio, este código solo se compila: para ejecutarlo hacen falta Hibernate con todas sus bibliotecas y una configuración.

Más precisamente, para curiosos

• Con la interfaz propia de Hibernate, lo mismo pasa por una Session, y las consultas están en HQL (el lenguaje de Hibernate, del que salió JPQL):

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("Baratos: " + cheap.size());
    }
    factory.close();
}

• También puedes construir una consulta con código, sin texto: la Criteria API. Es práctica cuando las condiciones se arman según lo que elige el usuario:

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

• Una transacción con EntityManager, que se deshace si hay un error:

EntityTransaction tx = em.getTransaction();
try {
    tx.begin();
    // cambios en las entidades
    tx.commit();
} catch (RuntimeException e) {
    if (tx.isActive()) {
        tx.rollback();
    }
    throw e;
} finally {
    em.close();
}

• Hibernate 5 y JPA 2.2 usan el paquete javax.persistence. Desde JPA 3 (Hibernate 6), el paquete es jakarta.persistence, y lo demás es casi igual.

Ejercicio

Una tienda con base de datos

Resuelve las tareas en tu editor, con H2 en el proyecto (com.h2database:h2) y la dirección jdbc:h2:mem:....1. Catálogo. Crea una tabla products y los métodos add, setPrice, remove y list, todos con PreparedStatement menos list. Añade Café (7,50 €), Té (3,20 €) y Chocolate (2,40 €), imprímelos, cambia el precio del café a 6,90 €, borra el chocolate e imprime otra vez:

Salida

1. Café: 7.5 €
2. Té: 3.2 €
3. Chocolate: 2.4 €
---
1. Café: 6.9 €
2. Té: 3.2 €

2. Búsqueda por parte del nombre. Los productos son Café, Cacao, Té y Éclair. Un método find(conn, text) encuentra todos los productos cuyo nombre contiene el texto, sin importar mayúsculas y minúsculas: WHERE LOWER(name) LIKE ? con el valor "%" + text.toLowerCase() + "%". Prueba «CA», «éclair» y un intento de inyección:

Salida

Buscando «CA»:
  Cacao: 4.8 €
  Café: 7.5 €
Buscando «éclair»:
  Éclair: 2.4 €
Buscando «' OR '1'='1»:
  no se encontraron productos

3. Una transferencia que falla a medias. Las cuentas son Ana (100 €) y Carlos (50 €). transfer(conn, from, to, amount) primero resta de from y luego suma a to. Si executeUpdate devuelve 0 (no existe esa cuenta), lanza SQLException("No existe la cuenta " + id). Todo va en una transacción. Transfiere 30 € de la 1 a la 2, y luego 40 € de la 1 a la cuenta 99, que no existe:

Salida

Transferidos 30 € de 1 a 2
Rechazada: No existe la cuenta 99
  Ana: 70 €
  Carlos: 80 €

La segunda transferencia le quitó 40 € a Ana antes de fallar, pero rollback se los devolvió.

Test

Ponte a prueba

1. ¿Qué devuelve executeQuery?

Un ResultSet: las filas del resultado, que recorres con next()Una lista de objetos ProductEl número de filas cambiadas

2. ¿Por qué es peligroso "... WHERE name = '" + name + "'"?

Porque el texto del usuario pasa a formar parte del SQL y puede cambiar la consultaNo es peligroso si name es un StringPorque es lento

3. Una transferencia son dos consultas. La primera sale bien, la segunda falla. ¿Qué hace rollback()?

Deshace todos los cambios desde el inicio de la transacción, también el primeroDeshace solo la segundaRepite la segunda consulta

4. ¿Qué hace un ORM como Hibernate?

Solo hace más rápidas las consultas SQLSustituye a la base de datosConvierte objetos en filas y al revés, sin que escribas SQL para cada cosa

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Datos en archivos · Listas · Lambdas · record, switch y patrones
• Continúa en: Inyecciones: cuando los datos se vuelven una orden · Monolito, monolito modular, servicios · Transactional Outbox · Una transacción entre servicios (Saga)
• Ejercicios sobre el tema: Programas en el mundo real