Extensão — Dados que Sobrevivem: SQL Essencial e JDBC com SQLite

Nesta aula as trilhas deixam de viver só na memória. Você vai modelar tabelas, consultar e juntar dados com SQL, e usar o JDBC, a API que todo framework de persistência usa por baixo. O banco é o SQLite, que cabe inteiro num arquivo.

A API de tarefas do Farol tinha um defeito que qualquer usuário notaria no primeiro dia: bastava reiniciar o servidor para todas as tarefas sumirem. Elas moravam num ConcurrentSkipListMap, e a memória de um processo morre com ele. "Recursos que se Fecham Sozinhos" mostrou uma saída, gravar em arquivos, mas arquivos comuns não respondem bem a perguntas como "quais trilhas do Sul têm média de avaliação acima de quatro?", nem garantem que duas gravações simultâneas não se atropelem. Para isso existem os bancos de dados. Esta aula apresenta o modelo relacional e a linguagem SQL, que são usados há cinquenta anos, e o JDBC, a ponte entre o Java e qualquer banco relacional. Tudo o que as próximas aulas fazem com Spring, JPA e Spring Data se apoia no que está aqui.

Tabelas, linhas e uma linguagem para perguntar

Em 1970, Edgar F. Codd, pesquisador da IBM, publicou o artigo "A Relational Model of Data for Large Shared Data Banks". A proposta era guardar os dados em relações, que hoje chamamos de tabelas: cada tabela tem colunas com nome e tipo, e cada linha é um registro. As tabelas se ligam umas às outras por valores, e não por ponteiros, e o programa não precisa saber como os dados estão organizados no disco: ele descreve o que quer, e o banco descobre como obter. Pouco depois, também na IBM, Donald Chamberlin e Raymond Boyce criaram uma linguagem para fazer essas perguntas, a SEQUEL, depois rebatizada de SQL (Structured Query Language), padronizada pela ANSI em 1986 e atualizada até hoje. Cinquenta anos depois, os bancos relacionais continuam no centro de quase todo sistema de informação, e o SQL continua sendo uma das linguagens mais úteis que um programador pode aprender.

Os bancos relacionais mais conhecidos, como PostgreSQL, MySQL e Oracle, funcionam como servidores: um programa separado, que recebe conexões pela rede. O SQLite, criado por D. Richard Hipp em 2000, é diferente: é uma biblioteca que roda dentro do próprio programa, e o banco inteiro é um arquivo. Não há nada para instalar nem para configurar. Essa simplicidade fez dele o banco mais usado do mundo: ele está em todo celular Android e iPhone, em todos os navegadores e em milhares de aplicativos de desktop. Para aprender SQL e JDBC, é perfeito. "PostgreSQL" e "MySQL", mais adiante na trilha, vão levar o mesmo projeto para bancos servidores.

Para experimentar o SQL diretamente, o SQLite tem uma ferramenta de linha de comando, sqlite3, disponível para os três sistemas operacionais no site do projeto (no Linux, geralmente pelo gerenciador de pacotes, e no macOS ela já vem instalada). Os resultados desta aula foram produzidos com o SQLite 3.46, e aparecem no formato do modo column da ferramenta, ativado com .mode column, .headers on e .nullvalue NULL (sem este último, os valores nulos aparecem em branco).

O esquema das Trilhas da Ilha

O primeiro passo é o esquema: as tabelas, suas colunas e as regras que os dados precisam obedecer. O modelo apresentado em "Java no Servidor" tem três partes: a trilha, os pontos de interesse de cada trilha e as avaliações. Um banco relacional não guarda listas dentro de uma linha, então os pontos de interesse e as avaliações ganham tabelas próprias, ligadas à trilha pelo seu identificador:

PRAGMA foreign_keys = ON;

CREATE TABLE trilha (
    id            INTEGER PRIMARY KEY,
    nome          TEXT    NOT NULL UNIQUE,
    regiao        TEXT    NOT NULL,
    distancia_km  REAL    NOT NULL CHECK (distancia_km > 0),
    dificuldade   TEXT    NOT NULL CHECK (dificuldade IN ('FACIL', 'MODERADA', 'DIFICIL')),
    duracao_min   INTEGER NOT NULL CHECK (duracao_min > 0)
) STRICT;

CREATE TABLE ponto_de_interesse (
    trilha_id  INTEGER NOT NULL REFERENCES trilha (id) ON DELETE CASCADE,
    ordem      INTEGER NOT NULL,
    nome       TEXT    NOT NULL CHECK (length(nome) <= 100),
    PRIMARY KEY (trilha_id, ordem)
) STRICT;

CREATE TABLE avaliacao (
    id          INTEGER PRIMARY KEY,
    trilha_id   INTEGER NOT NULL REFERENCES trilha (id) ON DELETE CASCADE,
    autor       TEXT    NOT NULL,
    nota        INTEGER NOT NULL CHECK (nota BETWEEN 1 AND 5),
    comentario  TEXT,
    data        TEXT    NOT NULL
) STRICT;

CREATE INDEX idx_avaliacao_trilha ON avaliacao (trilha_id);

Cada linha do esquema é uma regra que o banco vai garantir, não importa qual programa grave os dados. A chave primária (PRIMARY KEY) identifica cada linha de forma única; no SQLite, uma coluna INTEGER PRIMARY KEY recebe automaticamente o próximo número disponível quando não é informada. Na tabela ponto_de_interesse, a chave é composta: o par (trilha, ordem) é único, e é a ordem que preserva a sequência dos pontos, porque as linhas de uma tabela, ao contrário dos elementos de uma List, não têm ordem nenhuma por si mesmas.

NOT NULL proíbe valores ausentes, UNIQUE proíbe dois valores iguais na coluna, e CHECK impõe uma condição qualquer, como uma distância positiva, uma dificuldade entre três valores permitidos ou um nome de ponto de interesse com até cem caracteres (o SQLite não limita o tamanho de um TEXT, e o length(nome) <= 100 faz o papel do VARCHAR(100) de outros bancos). O CHECK da dificuldade faz no banco o que o enum Dificuldade faz no Java: são as validações na fronteira de "Contratos à Vista", agora do lado dos dados.

REFERENCES declara uma chave estrangeira: o trilha_id de uma avaliação precisa existir na tabela trilha. O ON DELETE CASCADE diz o que fazer quando a trilha é apagada: apagar junto suas avaliações e pontos de interesse. E o PRAGMA foreign_keys = ON, na primeira linha, existe por causa de uma peculiaridade histórica do SQLite: por compatibilidade com versões antigas, as chaves estrangeiras vêm desligadas, e precisam ser ligadas a cada conexão. O exercício 2 mostra o que acontece quando alguém esquece.

A palavra STRICT, disponível desde o SQLite 3.37, de 2021, liga a verificação rigorosa de tipos. Sem ela, o SQLite aceita guardar um texto numa coluna REAL, um comportamento flexível que surpreende quem vem de outros bancos.

Por fim, o índice em avaliacao (trilha_id). Um índice é uma estrutura auxiliar, ordenada, que permite ao banco encontrar as linhas com um certo valor sem ler a tabela inteira, como o índice remissivo de um livro. Ele custa espaço e deixa as gravações um pouco mais lentas, e em troca acelera muito as consultas por aquela coluna.

Inserindo e consultando

Os dados de exemplo entram com INSERT, que aceita várias linhas de uma vez:

INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min) VALUES
    ('Lagoinha do Leste',       'Sul',   4.5, 'MODERADA', 120),
    ('Costão do Santinho',      'Norte', 3.2, 'FACIL',     90),
    ('Morro da Coroa',          'Leste', 6.8, 'DIFICIL',  210),
    ('Praia do Saquinho',       'Sul',   2.1, 'FACIL',     60),
    ('Pico do Ribeirão',        'Sul',   5.4, 'DIFICIL',  180);

INSERT INTO ponto_de_interesse (trilha_id, ordem, nome) VALUES
    (1, 1, 'Mirante do Matadeiro'), (1, 2, 'Pedra do Urubu'), (1, 3, 'Lagoinha'),
    (2, 1, 'Inscrições rupestres'), (2, 2, 'Mirante do Costão'),
    (3, 1, 'Cachoeira da Coroa');

INSERT INTO avaliacao (trilha_id, autor, nota, comentario, data) VALUES
    (1, 'Ana',    5, 'A vista compensa cada passo.',   '2026-08-02'),
    (1, 'Bruno',  4, 'Leve água, não tem onde comprar.', '2026-08-15'),
    (1, 'Carla',  5, NULL,                             '2026-09-01'),
    (2, 'Ana',    4, 'Ótima para iniciantes.',         '2026-07-20'),
    (3, 'Diego',  3, 'Trilha fechada, muito barro.',   '2026-06-11'),
    (3, 'Bruno',  2, 'Sinalização ruim.',              '2026-09-10');

As datas são guardadas como texto no formato ISO, AAAA-MM-DD, que o SQLite recomenda porque se ordena corretamente como texto e é o formato do LocalDate.toString() de "Tempo, Dinheiro e Padrões".

O comando central do SQL é o SELECT: quais colunas, de qual tabela, com qual filtro, em qual ordem:

sqlite> SELECT nome, distancia_km, duracao_min FROM trilha WHERE regiao = 'Sul' ORDER BY distancia_km;
nome               distancia_km  duracao_min
-----------------  ------------  -----------
Praia do Saquinho  2.1           60
Lagoinha do Leste  4.5           120
Pico do Ribeirão   5.4           180

sqlite> SELECT nome, dificuldade FROM trilha WHERE dificuldade <> 'DIFICIL' AND distancia_km < 4 ORDER BY nome;
nome                dificuldade
------------------  -----------
Costão do Santinho  FACIL
Praia do Saquinho   FACIL

A consulta é declarativa: ela descreve o resultado, e não os passos para chegar a ele. Não há laço nem if; o banco decide se lê a tabela inteira ou usa um índice, e em que ordem. É a mesma ideia dos streams de "Pare de Escrever Laços", com vinte anos de antecedência: o WHERE é o filter, a lista de colunas é o map, o ORDER BY é o sorted.

Juntando tabelas

Os dados de uma trilha estão espalhados por três tabelas, e o JOIN os reúne. Ele combina cada linha de uma tabela com as linhas de outra que satisfazem uma condição, quase sempre a igualdade entre uma chave estrangeira e a chave primária:

sqlite> SELECT t.nome AS trilha, a.autor, a.nota FROM trilha t JOIN avaliacao a ON a.trilha_id = t.id ORDER BY t.nome, a.data;
trilha              autor  nota
------------------  -----  ----
Costão do Santinho  Ana    4
Lagoinha do Leste   Ana    5
Lagoinha do Leste   Bruno  4
Lagoinha do Leste   Carla  5
Morro da Coroa      Diego  3
Morro da Coroa      Bruno  2

As letras t e a são apelidos das tabelas, que encurtam as referências às colunas. Repare que a Praia do Saquinho e o Pico do Ribeirão não aparecem: o JOIN comum, chamado de inner join, só mantém as combinações que existem, e essas trilhas não têm avaliações. Para mantê-las, com as colunas da avaliação vazias, usa-se o LEFT JOIN, que preserva todas as linhas da tabela da esquerda.

Com o JOIN, vêm as agregações. O GROUP BY junta as linhas com o mesmo valor, e funções como COUNT, AVG, MIN, MAX e SUM calculam um valor por grupo:

sqlite> SELECT t.nome, COUNT(a.id) AS avaliacoes, ROUND(AVG(a.nota), 2) AS media
   ...>   FROM trilha t LEFT JOIN avaliacao a ON a.trilha_id = t.id
   ...>  GROUP BY t.id ORDER BY media DESC NULLS LAST;
nome                avaliacoes  media
------------------  ----------  -----
Lagoinha do Leste   3           4.67
Costão do Santinho  1           4.0
Morro da Coroa      2           2.5
Praia do Saquinho   0           NULL
Pico do Ribeirão    0           NULL

Com o LEFT JOIN, as trilhas sem avaliação aparecem, com contagem zero e média NULL, a forma do SQL de dizer "não existe". O COUNT(a.id) conta só os valores não nulos, e por isso dá zero para elas; um COUNT(*) contaria a linha vazia do LEFT JOIN e daria um, um erro clássico. O NULLS LAST coloca as médias ausentes no fim.

O WHERE filtra linhas antes do agrupamento; para filtrar grupos, depois do cálculo, existe o HAVING. As trilhas com pelo menos duas avaliações e média de quatro para cima:

sqlite> SELECT t.nome, ROUND(AVG(a.nota), 2) AS media FROM trilha t JOIN avaliacao a ON a.trilha_id = t.id
   ...>  GROUP BY t.id HAVING COUNT(*) >= 2 AND AVG(a.nota) >= 4;
nome               media
-----------------  -----
Lagoinha do Leste  4.67

E há funções que montam texto a partir de um grupo, como o group_concat do SQLite, que reconstrói o roteiro de cada trilha a partir dos pontos de interesse:

sqlite> SELECT t.nome, group_concat(p.nome, ' → ') AS roteiro FROM trilha t
   ...>   JOIN ponto_de_interesse p ON p.trilha_id = t.id GROUP BY t.id ORDER BY t.id;
nome                roteiro
------------------  ------------------------------------------------
Lagoinha do Leste   Mirante do Matadeiro → Pedra do Urubu → Lagoinha
Costão do Santinho  Inscrições rupestres → Mirante do Costão
Morro da Coroa      Cachoeira da Coroa

O group_concat não garante a ordem dos elementos; aqui ela saiu na ordem de inserção, mas o jeito confiável de obter a sequência dos pontos é pedir ORDER BY ordem numa consulta que não agrupe, como o código Java mais adiante faz.

Alterando, e o que o banco recusa

O UPDATE altera linhas existentes, e o DELETE as remove, os dois com um WHERE que diz quais. Um UPDATE ou um DELETE sem WHERE afeta todas as linhas da tabela, e é um dos erros mais caros que se pode cometer num banco em produção.

As regras do esquema aparecem quando alguém tenta violá-las:

sqlite> .changes on
sqlite> UPDATE trilha SET duracao_min = duracao_min + 15 WHERE nome = 'Morro da Coroa';
changes: 1   total_changes: 1

sqlite> INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min) VALUES ('Trilha Fantasma', 'Oeste', -1, 'FACIL', 30);
Runtime error: CHECK constraint failed: distancia_km > 0 (19)

sqlite> INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min) VALUES ('Lagoinha do Leste', 'Sul', 4, 'FACIL', 30);
Runtime error: UNIQUE constraint failed: trilha.nome (19)

sqlite> INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min) VALUES ('Trilha Nova', 'Sul', 'longe', 'FACIL', 30);
Runtime error: cannot store TEXT value in REAL column trilha.distancia_km (19)

sqlite> INSERT INTO avaliacao (trilha_id, autor, nota, data) VALUES (99, 'Eva', 5, '2026-09-29');
Runtime error: FOREIGN KEY constraint failed (19)

sqlite> DELETE FROM trilha WHERE nome = 'Morro da Coroa';
changes: 1   total_changes: 5

sqlite> SELECT COUNT(*) AS avaliacoes_restantes FROM avaliacao;
avaliacoes_restantes
--------------------
4

Distância negativa, nome repetido, texto numa coluna numérica, avaliação de uma trilha inexistente: tudo recusado, com uma mensagem que diz qual regra foi violada. E o DELETE do Morro da Coroa levou junto suas duas avaliações, pelo ON DELETE CASCADE, deixando quatro das seis. O .changes on faz a ferramenta mostrar quantas linhas cada comando alterou; o DELETE conta uma, a trilha, mas o total_changes saltou de 1 para 5, porque inclui as duas avaliações e o ponto de interesse que saíram em cascata. O 19 entre parênteses é o código de erro do SQLite para restrição violada. É por isso que as regras vão no esquema, e não só no código Java: o banco é a última linha de defesa, e ela vale para qualquer programa, qualquer script de manutenção e qualquer pessoa que se conecte a ele.

Pedindo ao banco que explique

O EXPLAIN QUERY PLAN mostra como o banco pretende executar uma consulta, sem executá-la:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM avaliacao WHERE trilha_id = 1;
QUERY PLAN
`--SEARCH avaliacao USING INDEX idx_avaliacao_trilha (trilha_id=?)

sqlite> EXPLAIN QUERY PLAN SELECT * FROM avaliacao WHERE autor = 'Ana';
QUERY PLAN
`--SCAN avaliacao

A primeira consulta usa o índice, SEARCH ... USING INDEX: vai direto às linhas da trilha 1. A segunda, por autor, não tem índice, e o plano é SCAN: ler a tabela inteira. Com seis linhas, a diferença é invisível; com seis milhões, é a diferença entre um milissegundo e vários segundos. Ler o plano de execução é o "Medir Antes de Otimizar" dos bancos de dados, e todos os bancos relacionais têm um comando equivalente.

JDBC: o Java conversa com o banco

O JDBC (Java Database Connectivity) faz parte do Java desde o JDK 1.1, de 1997, no pacote java.sql. Ele é uma coleção de interfaces, Connection, PreparedStatement, ResultSet, e cada fabricante de banco fornece um driver, uma biblioteca que as implementa. O código do programa só conhece as interfaces, e trocar de banco é, em princípio, trocar o driver e o endereço. É o polimorfismo de "Contratos sem Implementação" numa escala industrial.

O projeto desta aula é um projeto Maven comum. O driver do SQLite mais usado em Java, mantido pelo projeto xerial, embute a biblioteca do SQLite para os principais sistemas operacionais, e não exige nada além da dependência:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.53.2.1</version>
</dependency>

Os três primeiros números da versão são os do SQLite embutido: o 3.53.2.1 traz o SQLite 3.53.2. O esquema da seção anterior vai para src/main/resources/esquema.sql, sem a linha do PRAGMA, que o código Java executa por conta própria.

O modelo em Java é o record apresentado em "Java no Servidor", com um detalhe: o id é um Long, e não um long, porque uma trilha que ainda não foi gravada não tem identificador, e null é a forma de dizer isso aqui:

package br.com.exemplo.trilhas;

import java.time.Duration;
import java.util.List;
import java.util.Objects;

/** Uma trilha. O id é nulo enquanto a trilha não foi gravada no banco. */
public record Trilha(Long id, String nome, String regiao, double distanciaKm,
                     Dificuldade dificuldade, Duration duracao, List<String> pontosDeInteresse) {
    public Trilha {
        Objects.requireNonNull(nome, "nome");
        Objects.requireNonNull(regiao, "regiao");
        Objects.requireNonNull(dificuldade, "dificuldade");
        Objects.requireNonNull(duracao, "duracao");
        if (distanciaKm <= 0) throw new IllegalArgumentException("distância deve ser positiva: " + distanciaKm);
        pontosDeInteresse = List.copyOf(pontosDeInteresse);
    }

    /** Uma trilha nova, ainda sem id. */
    public static Trilha nova(String nome, String regiao, double distanciaKm, Dificuldade dificuldade,
                              Duration duracao, String... pontos) {
        return new Trilha(null, nome, regiao, distanciaKm, dificuldade, duracao, List.of(pontos));
    }

    public Trilha comId(long novoId) {
        return new Trilha(novoId, nome, regiao, distanciaKm, dificuldade, duracao, pontosDeInteresse);
    }
}

package br.com.exemplo.trilhas;

public enum Dificuldade { FACIL, MODERADA, DIFICIL }

Abrindo conexões

Uma Connection representa uma sessão com o banco, e é obtida do DriverManager com um endereço, a URL JDBC, que começa com jdbc: seguido do nome do driver. Para o SQLite, o resto da URL é o caminho do arquivo. Em vez de espalhar chamadas ao DriverManager pelo código, o projeto concentra a abertura de conexões numa interface funcional, o que permite trocar a origem das conexões nos testes, no espírito de "Dublês e Disciplina":

package br.com.exemplo.trilhas;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/** Abre conexões com o banco. Uma interface funcional: nos testes, qualquer lambda serve. */
@FunctionalInterface
public interface FabricaDeConexoes {
    Connection abrir() throws SQLException;

    /** Conexões com um arquivo SQLite, com as chaves estrangeiras ligadas (no SQLite, elas vêm desligadas). */
    static FabricaDeConexoes sqlite(String arquivo) {
        return () -> {
            Connection c = DriverManager.getConnection("jdbc:sqlite:" + arquivo);
            try (Statement s = c.createStatement()) {
                s.execute("PRAGMA foreign_keys = ON");
            }
            return c;
        };
    }
}

Uma conexão é um recurso do sistema, como um arquivo ou um socket, e precisa ser fechada, sempre num try-with-resources. Abrir uma conexão com um arquivo SQLite é rápido; com um banco servidor, envolve abrir um socket, autenticar e negociar parâmetros, e custa milissegundos. Por isso, programas reais usam um pool de conexões, que mantém conexões abertas e as empresta a quem pede, e é o que o Spring Boot vai configurar na próxima aula, com o HikariCP.

O script do esquema é executado por uma pequena classe, que o lê dos recursos do projeto e envia um comando por vez, porque o JDBC executa um comando por chamada:

package br.com.exemplo.trilhas;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.Objects;

/** Executa um script SQL guardado nos recursos do projeto, um comando por vez. */
public final class Esquema {
    private Esquema() {}

    public static void criar(FabricaDeConexoes conexoes, String recurso) throws SQLException, IOException {
        String script;
        try (InputStream in = Objects.requireNonNull(Esquema.class.getResourceAsStream(recurso), "recurso não encontrado: " + recurso)) {
            script = new String(in.readAllBytes(), StandardCharsets.UTF_8);
        }
        try (Connection c = conexoes.abrir(); Statement s = c.createStatement()) {
            for (String comando : script.split(";")) {
                if (!comando.isBlank()) s.execute(comando);   // o JDBC executa um comando por chamada
            }
        }
    }
}

Dividir o script por ; é uma simplificação que funciona para esquemas simples, mas quebraria com um ponto e vírgula dentro de um texto. "O Banco que Evolui" vai trocar esse código por uma ferramenta feita para isso, o Flyway.

O repositório

A classe que concentra o acesso ao banco é o repositório, o mesmo nome que "Dublês e Disciplina" deu à interface que o ServicoDeLembretes usava. Cada método é SQL escrito à mão, e vale a pena ler com calma, porque é exatamente o trabalho que o JPA vai fazer automaticamente daqui a duas aulas:

package br.com.exemplo.trilhas;

import java.sql.*;
import java.time.Duration;
import java.util.*;

/** Grava e lê trilhas com JDBC puro: cada linha de SQL escrita à mão. */
public final class RepositorioDeTrilhas {
    private final FabricaDeConexoes conexoes;

    public RepositorioDeTrilhas(FabricaDeConexoes conexoes) {
        this.conexoes = Objects.requireNonNull(conexoes);
    }

    /** Grava a trilha e seus pontos de interesse numa única transação e devolve a trilha com o id gerado. */
    public Trilha inserir(Trilha t) throws SQLException {
        if (t.id() != null) throw new IllegalArgumentException("a trilha já tem id: " + t.id());
        try (Connection c = conexoes.abrir()) {
            c.setAutoCommit(false);                        // começa uma transação
            try {
                long id;
                try (PreparedStatement ps = c.prepareStatement(
                        "INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min) VALUES (?, ?, ?, ?, ?)",
                        Statement.RETURN_GENERATED_KEYS)) {
                    ps.setString(1, t.nome());
                    ps.setString(2, t.regiao());
                    ps.setDouble(3, t.distanciaKm());
                    ps.setString(4, t.dificuldade().name());
                    ps.setLong(5, t.duracao().toMinutes());
                    ps.executeUpdate();
                    try (ResultSet chaves = ps.getGeneratedKeys()) {
                        chaves.next();
                        id = chaves.getLong(1);
                    }
                }
                try (PreparedStatement ps = c.prepareStatement(
                        "INSERT INTO ponto_de_interesse (trilha_id, ordem, nome) VALUES (?, ?, ?)")) {
                    int ordem = 1;
                    for (String ponto : t.pontosDeInteresse()) {
                        ps.setLong(1, id);
                        ps.setInt(2, ordem++);
                        ps.setString(3, ponto);
                        ps.addBatch();                     // acumula; enviados juntos no executeBatch
                    }
                    ps.executeBatch();
                }
                c.commit();                                // tudo ou nada
                return t.comId(id);
            } catch (SQLException | RuntimeException e) {
                c.rollback();                              // desfaz o que a transação já tinha feito
                throw e;
            }
        }
    }

    public Optional<Trilha> buscarPorId(long id) throws SQLException {
        return consultar("WHERE t.id = ?", id).stream().findFirst();
    }

    public List<Trilha> listarPorRegiao(String regiao) throws SQLException {
        return consultar("WHERE t.regiao = ?", regiao);
    }

    public boolean alterarDuracao(long id, Duration duracao) throws SQLException {
        try (Connection c = conexoes.abrir();
             PreparedStatement ps = c.prepareStatement("UPDATE trilha SET duracao_min = ? WHERE id = ?")) {
            ps.setLong(1, duracao.toMinutes());
            ps.setLong(2, id);
            return ps.executeUpdate() == 1;
        }
    }

    public boolean remover(long id) throws SQLException {
        try (Connection c = conexoes.abrir();
             PreparedStatement ps = c.prepareStatement("DELETE FROM trilha WHERE id = ?")) {
            ps.setLong(1, id);
            return ps.executeUpdate() == 1;                // os pontos saem junto: ON DELETE CASCADE
        }
    }

    /**
     * Uma consulta só, com LEFT JOIN: cada trilha aparece em uma linha por ponto de interesse
     * (ou em uma linha só, com o ponto nulo, se não tiver nenhum). O laço junta as linhas de volta.
     */
    private List<Trilha> consultar(String filtro, Object parametro) throws SQLException {
        String sql = """
                SELECT t.id, t.nome, t.regiao, t.distancia_km, t.dificuldade, t.duracao_min, p.nome AS ponto
                  FROM trilha t
                  LEFT JOIN ponto_de_interesse p ON p.trilha_id = t.id
                """ + filtro + " ORDER BY t.nome, p.ordem";
        try (Connection c = conexoes.abrir(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setObject(1, parametro);
            Map<Long, Trilha> porId = new LinkedHashMap<>();
            Map<Long, List<String>> pontos = new HashMap<>();
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    long id = rs.getLong("id");
                    porId.computeIfAbsent(id, k -> lerTrilha(rs, k));
                    String ponto = rs.getString("ponto");
                    List<String> lista = pontos.computeIfAbsent(id, k -> new ArrayList<>());
                    if (ponto != null) lista.add(ponto);
                }
            }
            return porId.values().stream()
                    .map(t -> new Trilha(t.id(), t.nome(), t.regiao(), t.distanciaKm(), t.dificuldade(),
                            t.duracao(), pontos.get(t.id())))
                    .toList();
        }
    }

    private static Trilha lerTrilha(ResultSet rs, long id) {
        try {
            return new Trilha(id, rs.getString("nome"), rs.getString("regiao"), rs.getDouble("distancia_km"),
                    Dificuldade.valueOf(rs.getString("dificuldade")),
                    Duration.ofMinutes(rs.getLong("duracao_min")), List.of());
        } catch (SQLException e) {
            throw new IllegalStateException("linha ilegível", e);   // computeIfAbsent não aceita exceção verificada
        }
    }
}

O PreparedStatement é a peça mais importante do JDBC. O SQL é escrito com marcadores, os pontos de interrogação, e os valores são entregues separadamente, com setString, setLong e os outros métodos set, numerados a partir de 1. O banco recebe o comando e os valores por caminhos diferentes, e nunca interpreta um valor como parte do comando. A seção seguinte mostra por que isso é uma questão de segurança, e não de estilo.

A inserção usa uma transação. Por padrão, uma conexão JDBC está em modo auto-commit: cada comando é gravado definitivamente assim que termina. Aqui, a trilha e seus pontos de interesse precisam ser gravados juntos, e o setAutoCommit(false) agrupa os comandos seguintes numa unidade: o commit() grava tudo, e o rollback(), no catch, desfaz tudo. Sem a transação, uma falha no terceiro ponto de interesse deixaria no banco uma trilha pela metade. É a atomicidade de "O Cadeado e a Memória", agora entre um programa e um banco, e ela é uma das quatro propriedades que os bancos relacionais garantem, conhecidas pela sigla ACID: atomicidade, consistência, isolamento e durabilidade.

O RETURN_GENERATED_KEYS pede ao driver o id que o banco gerou, lido depois com getGeneratedKeys(). O addBatch e o executeBatch enviam os pontos de interesse de uma vez, em vez de um comando por ponto, o que faz diferença com bancos servidores, onde cada ida e volta pela rede custa tempo.

A leitura mostra o trabalho que fica a cargo de quem usa JDBC puro. O ResultSet é um cursor sobre as linhas do resultado: cada next() avança uma linha, e os métodos get leem as colunas, por nome ou por posição. Como uma trilha tem vários pontos de interesse, o LEFT JOIN produz uma linha por ponto, repetindo os dados da trilha, e o laço precisa juntá-las de volta num objeto, com um LinkedHashMap que preserva a ordem. Essa tradução entre linhas e objetos, com seus detalhes, como o Dificuldade.valueOf e o Duration.ofMinutes, é chamada de mapeamento objeto-relacional, e é exatamente o que o JPA automatiza.

Por fim, o SQLException é uma exceção verificada, e todo método do repositório a declara. "Quando o Contrato se Quebra" discutiu esse tipo de escolha; as bibliotecas modernas, como o Spring, traduzem essas exceções para uma hierarquia de exceções não verificadas, e a próxima aula mostra isso.

Pondo para funcionar

Um programa de exemplo cria o banco na primeira execução, grava duas trilhas e lista as do Sul:

package br.com.exemplo.trilhas;

import java.nio.file.*;
import java.time.Duration;

public class Principal {
    public static void main(String[] args) throws Exception {
        Path arquivo = Path.of("trilhas.db");
        boolean novo = Files.notExists(arquivo);
        FabricaDeConexoes conexoes = FabricaDeConexoes.sqlite(arquivo.toString());
        if (novo) Esquema.criar(conexoes, "/esquema.sql");

        var repositorio = new RepositorioDeTrilhas(conexoes);
        Trilha lagoinha = repositorio.inserir(Trilha.nova("Lagoinha do Leste", "Sul", 4.5, Dificuldade.MODERADA,
                Duration.ofMinutes(120), "Mirante do Matadeiro", "Pedra do Urubu", "Lagoinha"));
        repositorio.inserir(Trilha.nova("Praia do Saquinho", "Sul", 2.1, Dificuldade.FACIL, Duration.ofMinutes(60)));
        System.out.println("gravada com id " + lagoinha.id());

        for (Trilha t : repositorio.listarPorRegiao("Sul"))
            System.out.println(t.nome() + " (" + t.distanciaKm() + " km, " + t.duracao().toMinutes() + " min): " + t.pontosDeInteresse());
    }
}

gravada com id 1
Lagoinha do Leste (4.5 km, 120 min): [Mirante do Matadeiro, Pedra do Urubu, Lagoinha]
Praia do Saquinho (2.1 km, 60 min): []

A Praia do Saquinho aparece com a lista vazia, graças ao LEFT JOIN e ao if (ponto != null). Um arquivo trilhas.db aparece na pasta, e pode ser aberto com a ferramenta sqlite3 para conferir o que o Java gravou. Na segunda execução, o inserir da Lagoinha falha com uma SQLiteException, a subclasse de SQLException do driver, de restrição UNIQUE: o banco lembra da execução anterior, que era o objetivo desta aula. Do Java 24 em diante, a primeira conexão também imprime avisos sobre um restricted method: o driver carrega a biblioteca nativa do SQLite com System.load, e o JDK passou a avisar sobre esse tipo de acesso. Os avisos somem com a opção --enable-native-access=ALL-UNNAMED no comando java.

O mesmo roteiro exercita os outros métodos. A busca por id devolve Optional vazio para um id inexistente, alterarDuracao devolve true e a duração lida de volta é PT2H15M, e remover devolve true na primeira vez e false na segunda, porque o executeUpdate informa quantas linhas foram afetadas. Depois da remoção, os pontos de interesse da trilha também sumiram, pelo ON DELETE CASCADE.

A transação também foi posta à prova. Inserindo uma trilha cujo segundo ponto de interesse tem 101 caracteres, o CHECK do esquema recusa o segundo INSERT, o catch chama rollback(), e a trilha, cujo INSERT já tinha sido executado com sucesso, não fica gravada:

falhou no 2º ponto: 19 [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: length(nome) <= 100)
a trilha ficou gravada? false

O 19 vem de getErrorCode(), e é o mesmo código que a ferramenta sqlite3 mostrava entre parênteses; a mensagem traz o código estendido, SQLITE_CONSTRAINT_CHECK. O JDBC tem também um código padronizado, o SQLState, de cinco caracteres, lido com getSQLState(): os que começam com 23 indicam violação de restrição, como chave duplicada, e o 22001 indica dado grande demais para a coluna. Ele serve para tratar erros sem depender do texto das mensagens, que muda de banco para banco, mas nem todo driver o preenche: o do SQLite devolve null, e com ele o caminho é o getErrorCode().

Injeção de SQL

O PreparedStatement parece mais trabalhoso do que montar o comando concatenando textos. A diferença aparece quando o texto vem de um usuário. Uma busca por nome, escrita das duas formas:

// A versão ingênua: concatena o texto do usuário dentro do SQL
static List<String> buscarInseguro(Connection c, String nome) throws SQLException {
    List<String> r = new ArrayList<>();
    try (Statement s = c.createStatement();
         ResultSet rs = s.executeQuery("SELECT nome, regiao FROM trilha WHERE nome = '" + nome + "'")) {
        while (rs.next()) r.add(rs.getString("nome") + " (" + rs.getString("regiao") + ")");
    }
    return r;
}

// A versão correta: o texto do usuário vai como parâmetro, nunca como SQL
static List<String> buscarSeguro(Connection c, String nome) throws SQLException {
    List<String> r = new ArrayList<>();
    try (PreparedStatement ps = c.prepareStatement("SELECT nome, regiao FROM trilha WHERE nome = ?")) {
        ps.setString(1, nome);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) r.add(rs.getString("nome") + " (" + rs.getString("regiao") + ")");
        }
    }
    return r;
}

Com um banco que tem a Lagoinha do Leste e o Morro da Coroa:

inseguro, nome normal:  [Morro da Coroa (Leste)]
inseguro, malicioso:    [Lagoinha do Leste (Sul), Morro da Coroa (Leste)]
seguro, malicioso:      []
seguro, com apóstrofo:  []
inseguro, com apóstrofo: SQLiteException (near "Água": syntax error)

O texto malicioso era x' OR '1'='1. Concatenado, ele fecha a aspa aberta pelo programa e acrescenta uma condição sempre verdadeira, e o comando que o banco recebe é SELECT nome, regiao FROM trilha WHERE nome = 'x' OR '1'='1': todas as trilhas vêm na resposta. Numa tela de login, o mesmo truque entra na conta de qualquer um; com um ; e um DROP TABLE, em alguns bancos, apaga tabelas. Esse ataque, a injeção de SQL, é há mais de vinte anos um dos mais explorados da Web. Com o PreparedStatement, o mesmo texto é tratado como o que ele é, um nome, e simplesmente não encontra nenhuma trilha. E a versão insegura falha até com usuários honestos: uma trilha chamada "Pico d'Água", com um apóstrofo legítimo, quebra o comando com um erro de sintaxe.

A regra não tem exceção: dados do usuário nunca entram no texto do SQL. Vão sempre por marcadores. É a mesma regra do escritor de respostas do Farol, que recusava quebras de linha em cabeçalhos: nunca deixar dados virarem comandos.

O JDBC funciona, e agora você sabe o que ele exige: abrir e fechar conexões, controlar transações, escrever cada comando, numerar cada parâmetro, ler cada coluna e remontar os objetos à mão. Num sistema com cinquenta tabelas, esse código se repete milhares de vezes, e é esse peso que os frameworks das próximas aulas tiram de cima do programador, sem esconder o SQL que roda por baixo.

Fontes e leituras recomendadas

• SQLite, "SQL As Understood By SQLite": a referência da linguagem no SQLite, com diagramas de sintaxe para cada comando, e as páginas sobre tabelas STRICT e chaves estrangeiras. https://www.sqlite.org/lang.html
• SQLite, "Query Planning": como o SQLite usa índices e como ler o EXPLAIN QUERY PLAN. https://www.sqlite.org/queryplanner.html
• The Java Tutorials, "JDBC Database Access": o tutorial oficial do JDBC, com conexões, PreparedStatement, transações e lotes. https://docs.oracle.com/javase/tutorial/jdbc/
• xerial/sqlite-jdbc: o driver JDBC do SQLite usado nesta aula, com a documentação das opções de configuração. https://github.com/xerial/sqlite-jdbc
• OWASP, "SQL Injection Prevention Cheat Sheet": as defesas contra a injeção de SQL, com o uso de parâmetros como a primeira e principal. https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• Edgar F. Codd, "A Relational Model of Data for Large Shared Data Banks" (Communications of the ACM, 1970): o artigo que fundou o modelo relacional. Curto e surpreendentemente legível.

Exercícios

Exercício 1

Escreva uma consulta que liste as trilhas sem nenhuma avaliação. Dê duas versões, uma com LEFT JOIN e outra com NOT EXISTS, e explique por que um JOIN comum com WHERE a.id IS NULL não funciona.

SELECT t.nome FROM trilha t LEFT JOIN avaliacao a ON a.trilha_id = t.id WHERE a.id IS NULL ORDER BY t.nome;

SELECT nome FROM trilha WHERE NOT EXISTS (SELECT 1 FROM avaliacao a WHERE a.trilha_id = trilha.id) ORDER BY nome;

Exercício 2

Sem o PRAGMA foreign_keys = ON, o que acontece com o INSERT de uma avaliação para a trilha 99, que não existe? Por que o FabricaDeConexoes.sqlite executa o PRAGMA a cada conexão, e não uma vez só, junto com o esquema?

Exercício 3

Um colega argumenta que a busca insegura pode ser corrigida trocando cada ' por '' no texto do usuário antes de concatenar. Por que isso é uma má ideia, mesmo que funcione no caso mostrado?

Exercício 4

Remova, mentalmente, o setAutoCommit(false), o commit() e o rollback() do método inserir. Descreva o que acontece quando o segundo ponto de interesse viola uma restrição, e por que o rollback precisa estar num catch que também pega RuntimeException.

Exercício 5

A consulta por autor fazia um SCAN da tabela avaliacao. Crie o índice que a acelera, confirme com o EXPLAIN QUERY PLAN e explique por que não é uma boa ideia criar um índice para cada coluna de cada tabela.

sqlite> CREATE INDEX idx_avaliacao_autor ON avaliacao (autor);

sqlite> EXPLAIN QUERY PLAN SELECT * FROM avaliacao WHERE autor = 'Ana';
QUERY PLAN
`--SEARCH avaliacao USING INDEX idx_avaliacao_autor (autor=?)