Skip to content

10. Acesso a bancos de dados com JDBC

10.1. O driver JDBC

Vamos retomar a arquitetura em camadas usada repetidamente desde o Capítulo 6. Até agora, a camada [dao] da aplicação [Tax] utilizou três tipos de fontes de dados:

  • dados escritos diretamente no código ([HardwiredTax]);
  • dados de um arquivo de texto ([FileTax]);
  • dados de um arquivo JSON ([JsonTax]).

Neste capítulo, examinamos o caso em que os dados vêm de um banco de dados. A arquitetura passa a ser a seguinte:

Image

No diagrama acima, a camada [dao] se comunica com o [1] (Sistema de Gerenciamento de Banco de Dados) [3], que gerencia o banco de dados [4], por meio de uma biblioteca de classes específica do SGBD usado por [2]. Essa biblioteca implementa um recurso de API padrão da plataforma Java, o JDBC (“Java DataBase Connectivity”), que faz parte do JDK desde a versão 1.1, em 1997. Ela é chamada de driver JDBC (“JDBC driver”). Todos os SGBDs comuns incluem um driver JDBC, distribuído como um arquivo [.jar].

Kotlin não tem uma API própria para acesso a bancos de dados: um programa Kotlin executado na JVM usa o JDBC diretamente, assim como usa os arquivos NIO ou as threads do JDK. Essa é uma das grandes vantagens da linguagem: todos os drivers JDBC escritos para Java, e todas as bibliotecas construídas sobre eles, podem ser usados sem modificação. Como veremos ao longo deste capítulo, o que Kotlin traz é uma forma mais concisa e mais segura de escrever código JDBC: uma função [use] para fechar recursos, funções de extensão para ler resultados, data classes para representar linhas e nenhuma exceção verificada.

O JDBC é, antes de tudo, um conjunto de interfaces, definidas nos pacotes [java.sql] e [javax.sql] do módulo [java.sql] do JDK: [Connection], [Statement], [PreparedStatement], [ResultSet], [DataSource]… Um driver JDBC fornece classes que implementam essas interfaces. O código da aplicação, por sua vez, interage apenas com as interfaces: em geral, ele não contém os nomes das classes do driver. Isso difere de outras plataformas, em que o código costuma usar diretamente as classes específicas de cada SGBD ([MySqlConnection], [SqlConnection]… em C#): na JVM, a abstração é a norma desde o início, e trocar de SGBD significa essencialmente trocar o driver e o endereço do banco de dados, como veremos. Ainda assim, cada SGBD tem seu próprio dialeto SQL: essa independência tem seus limites, que também examinaremos.

Usamos um único SGBD, o MySQL 8, que o apêndice “O SGBD MySQL com Laragon” instala no Windows, junto com seu driver JDBC oficial, o MySQL Connector/J, versão 9.7.0. Os bancos de dados são criados por scripts SQL executados com o cliente de linha de comando [mysql]; o apêndice também mostra como criá-los usando o cliente gráfico HeidiSQL, incluído no Laragon.

10.2. As duas formas de usar uma fonte de dados

Um banco de dados pode ser acessado de duas maneiras diferentes:

  • modo conectado;
  • modo desconectado.

No modo conectado, a aplicação:

  • abre uma conexão com a fonte de dados;
  • trabalha com a fonte de dados em modo de leitura/escrita;
  • fecha a conexão.

No modo desconectado, a aplicação:

  • abre uma conexão com a fonte de dados;
  • recupera na memória uma cópia de todos ou de parte dos dados da fonte;
  • fecha a conexão;
  • trabalha com a cópia dos dados na memória, lendo e gravando nela;
  • quando o trabalho termina, abre uma conexão, envia os dados modificados à fonte de dados para que ela seja atualizada e fecha a conexão.

O modo conectado é o da interface [ResultSet] do JDBC: as linhas do resultado de uma consulta são lidas enquanto a conexão está aberta. O modo desconectado é o da interface [CachedRowSet] do pacote [javax.sql.rowset] (Java 5): uma tabela em memória que é preenchida a partir de uma consulta, modificada offline e depois envia suas alterações de volta ao banco de dados. Esses componentes “RowSet” ainda estão presentes no JDK 25, mas raramente são usados em novas aplicações: trabalhar com uma cópia dos dados em memória, com posterior gravação das alterações, é hoje função de um ORM (“Object-Relational Mapper”), que manipula objetos comuns em vez de linhas de tabelas. O ORM mais usado na JVM, o Hibernate, é o assunto do Capítulo 11; ele pode ser usado tanto em Kotlin quanto em Java. Vamos nos concentrar, portanto, principalmente no modo conectado, no qual o Hibernate se baseia, e demonstraremos o modo desconectado apenas por meio de um exemplo no final do capítulo.

10.3. Conceitos básicos de operação de bancos de dados

10.3.1. O banco de dados de exemplo

Explicaremos os principais conceitos de uso de um banco de dados com um banco de dados MySQL chamado [dbarticles]. Ele contém uma única tabela, [articles], na qual cada linha descreve um artigo de uma loja:

  • [ID]: um identificador único do artigo, que é a chave primária da tabela;
  • [NAME]: o nome do artigo, que também é único;
  • [PRICE]: seu preço;
  • [CURRENTSTOCK]: seu nível de estoque atual;
  • [MINIMUMSTOCK]: o nível mínimo de estoque abaixo do qual o artigo deve ser reabastecido.

O banco de dados é criado pelo seguinte script SQL: [sql/dbarticles.sql]:

-- criação do banco de dados [dbarticles] e de sua tabela [articles]
-- execução: mysql -uroot < dbarticles.sql

-- o script é escrito em UTF-8
SET NAMES utf8mb4;

-- o banco de dados é recriado a cada execução do script
DROP DATABASE IF EXISTS dbarticles;
CREATE DATABASE dbarticles CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- escolha do banco de dados atual
USE dbarticles;

-- criação da tabela articles
CREATE TABLE articles (
    ID            INT PRIMARY KEY AUTO_INCREMENT,
    NAME          VARCHAR(30) NOT NULL,
    PRICE         DECIMAL(10,2) NOT NULL,
    CURRENTSTOCK  INT NOT NULL,
    MINIMUMSTOCK  INT NOT NULL,
    -- restrições de integridade
    CONSTRAINT UNQ_NAME UNIQUE (NAME),
    CONSTRAINT CHK_NAME CHECK (NAME <> ''),
    CONSTRAINT CHK_PRICE CHECK (PRICE > 0),
    CONSTRAINT CHK_CURRENTSTOCK CHECK (CURRENTSTOCK >= 0),
    CONSTRAINT CHK_MINIMUMSTOCK CHECK (MINIMUMSTOCK >= 0)
);

-- inserção de dados na tabela
INSERT INTO articles (NAME, PRICE, CURRENTSTOCK, MINIMUMSTOCK) VALUES
    ('bike', 500, 10, 5),
    ('pump', 10, 10, 2),
    ('bow', 600, 4, 1),
    ('arrows - set of 6', 100, 12, 20),
    ('diving suit', 300, 8, 2),
    ('diver''s oxygen tanks', 120, 10, 5);
  • linha 5: o script é um arquivo de texto codificado em UTF-8. [SET NAMES utf8mb4] informa isso ao servidor; caso contrário, os caracteres acentuados dos dados (linhas 31–36) poderiam ser mal interpretados pelo cliente [mysql];
  • linhas 8–9: o banco de dados é excluído, se existir, e depois recriado. O script pode, assim, ser executado várias vezes para restaurar o banco de dados ao seu estado inicial. [utf8mb4] é a codificação Unicode completa do MySQL (a codificação legada [utf8] do MySQL codifica apenas caracteres de até três bytes);
  • linha 12: [dbarticles] torna-se o banco de dados atual: os comandos SQL seguintes serão executados nele;
  • linhas 15–27: a definição da tabela [articles]:
    • linha 16: a coluna [ID] é a chave primária da tabela ([PRIMARY KEY]). O atributo [AUTO_INCREMENT], específico do MySQL, indica que seu valor é gerado pelo próprio SGBD quando uma linha é inserida: 1, 2, 3… Os outros SGBDs têm conceitos equivalentes, mas com sintaxe diferente ([IDENTITY] no SQL Server, [GENERATED … AS IDENTITY] no PostgreSQL e no Oracle);
    • linha 18: o preço é um número decimal exato com 10 dígitos, incluindo 2 casas decimais. Evite usar tipos de ponto flutuante para valores monetários, como [DOUBLE] (veja a diferença entre [Double] e [BigDecimal] no Capítulo 3). O tipo SQL [DECIMAL] corresponde à classe [java.math.BigDecimal], que Kotlin usa como está;
    • linhas 22–26: restrições de integridade. [UNQ_NAME] impede que dois artigos tenham o mesmo nome; as restrições [CHECK] proíbem um nome vazio, um preço negativo ou zero e um estoque negativo. O MySQL ignorava silenciosamente as restrições [CHECK] até a versão 8.0.16; desde então, ele as aplica;
  • linhas 30–36: inserção de seis linhas. A coluna [ID] não é inicializada: o SGBD atribui um valor a ela.

O script é executado com o cliente de linha de comando [mysql], na pasta [sql] (o usuário [root] do MySQL não tem senha na instalação do apêndice; caso contrário, acrescente a opção [-p], e o cliente a solicitará):

> mysql -uroot < dbarticles.sql

O comando não exibe nada se tudo correr bem. Você pode verificar a estrutura e o conteúdo da tabela com o mesmo cliente:

> mysql -uroot --default-character-set=utf8mb4 -t dbarticles -e "describe articles; select * from articles"
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| ID           | int           | NO   | PRI | NULL    | auto_increment |
| NAME         | varchar(30)   | NO   | UNI | NULL    |                |
| PRICE        | decimal(10,2) | NO   |     | NULL    |                |
| CURRENTSTOCK | int           | NO   |     | NULL    |                |
| MINIMUMSTOCK | int           | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
+----+----------------------+--------+--------------+--------------+
| ID | NAME                 | PRICE  | CURRENTSTOCK | MINIMUMSTOCK |
+----+----------------------+--------+--------------+--------------+
|  1 | bike                 | 500.00 |           10 |            5 |
|  2 | pump                 |  10.00 |           10 |            2 |
|  3 | bow                  | 600.00 |            4 |            1 |
|  4 | arrows - set of 6    | 100.00 |           12 |           20 |
|  5 | diving suit          | 300.00 |            8 |            2 |
|  6 | diver's oxygen tanks | 120.00 |           10 |            5 |
+----+----------------------+--------+--------------+--------------+
  • linha 1: a opção [-t] solicita uma exibição em forma de tabela; a opção [-e] especifica os comandos SQL a executar, e a opção [--default-character-set=utf8mb4] garante que os caracteres acentuados sejam exibidos corretamente em um terminal UTF-8;
  • linhas 3–9: a estrutura da tabela, obtida com os comandos MySQL e [describe];
  • linhas 12–19: o conteúdo da tabela. Os valores da coluna [ID] foram gerados pelo SGBD.

Uma observação sobre diferenciação entre maiúsculas e minúsculas: os nomes de colunas nunca diferenciam maiúsculas de minúsculas ([name] e [NAME] se referem à mesma coluna), mas os nomes de tabelas são nomes de arquivos do servidor. Eles não diferenciam maiúsculas de minúsculas no Windows, mas diferenciam no Linux. Para garantir que os exemplos funcionem em qualquer lugar, a tabela se chama [articles], em minúsculas, e as consultas a escrevem assim.

Os programas deste capítulo modificam a tabela [articles]. Os resultados apresentados aqui foram obtidos executando novamente o script [dbarticles.sql] antes de cada programa; os leitores que quiserem reproduzir exatamente os mesmos resultados devem fazer o mesmo.

Agora que temos um banco de dados, podemos utilizá-lo. Antes disso, vamos rever alguns pontos sobre SQL.

10.3.2. Os quatro comandos básicos da linguagem SQL

SQL (“Structured Query Language”) é uma linguagem parcialmente padronizada para consultar e atualizar bancos de dados. Todos os dialetos dos SGBDs respeitam a parte padronizada do SQL, mas acrescentam à linguagem extensões proprietárias que aproveitam seus recursos específicos. Já encontramos dois exemplos disso: a geração automática de chaves primárias e os tipos de dados permitidos para as colunas das tabelas frequentemente dependem do SGBD.

Os quatro comandos básicos da linguagem SQL que apresentamos aqui são padronizados e aceitos por todos os SGBDs:

Comando
Função
[select col1, col2, … from table1, table2, … where condition order by expression]
A consulta usada para recuperar dados de um banco de dados. Somente as palavras-chave [select] e [from] são obrigatórias. É feita uma junção com todas as tabelas que seguem a palavra-chave [from]; somente as colunas que seguem a palavra-chave [select] são mantidas; somente as linhas que satisfazem a condição da palavra-chave [where] são mantidas; as linhas resultantes, ordenadas de acordo com a expressão da palavra-chave [order by], formam o resultado da consulta. Esse resultado é uma tabela.
[insert into table(col1, col2, …) values (val1, val2, …)]
Insere uma linha em [table]. [(col1, col2, …)] especifica as colunas da linha a inicializar com os valores [(val1, val2, …)].
[update table set col1=val1, col2=val2, … where condition]
Atualiza as linhas de [table] de acordo com [condition] (todas as linhas, se não houver [where]). Para essas linhas, a coluna [coli] recebe o valor [vali].
[delete from table where condition]
Exclui todas as linhas de [table] que satisfazem [condition] (todas as linhas, se não houver [where]).

Vamos escrever uma aplicação de console que emite comandos SQL contra o banco de dados [dbarticles]. Eis um exemplo do programa em ação (os comandos SQL digitados no teclado aparecem após o prompt “SQL query”; neste documento, eles foram fornecidos ao programa por redirecionamento da entrada padrão e depois reinseridos após o prompt a que respondem); recomenda-se que o leitor procure entender os comandos SQL emitidos e seus resultados:

> gradlew -q :jdbc:run
JDBC URL of the database: [jdbc:mysql://localhost:3306/dbarticles], user: [root]

SQL query (nothing to stop): select id,name,price,currentstock,minimumstock from articles

---------------------------------------
id,name,price,currentstock,minimumstock
---------------------------------------

1 bike 500.00 10 5
2 pump 10.00 10 2
3 bow 600.00 4 1
4 arrows - set of 6 100.00 12 20
5 diving suit 300.00 8 2
6 diver's oxygen tanks 120.00 10 5

SQL query (nothing to stop): insert into articles(name,price,currentstock,minimumstock) values('x',100,10,1)
1 row(s) modified

SQL query (nothing to stop): select * from articles

---------------------------------------
ID,NAME,PRICE,CURRENTSTOCK,MINIMUMSTOCK
---------------------------------------

1 bike 500.00 10 5
2 pump 10.00 10 2
3 bow 600.00 4 1
4 arrows - set of 6 100.00 12 20
5 diving suit 300.00 8 2
6 diver's oxygen tanks 120.00 10 5
7 x 100.00 10 1

SQL query (nothing to stop): update articles set price=price*1.1 where name='x'
1 row(s) modified

SQL query (nothing to stop): select id,name,price from articles where name='x'

-------------
id,name,price
-------------

7 x 110.00

SQL query (nothing to stop): delete from articles where name='x'
1 row(s) modified

SQL query (nothing to stop): select count(*) from articles

--------
count(*)
--------

6

SQL query (nothing to stop): selectt * from articles
Invalid query. Use select, insert, update, delete or nothing to stop

SQL query (nothing to stop): select * from article
Database access error (Table 'dbarticles.article' doesn't exist)

SQL query (nothing to stop): insert into articles(name,price,currentstock,minimumstock) values('bike',100,10,1)
Database access error (Duplicate entry 'bike' for key 'articles.UNQ_NAME')

SQL query (nothing to stop):
  • linha 2: a URL do banco de dados e o usuário JDBC: esses são os parâmetros usados para se conectar ao banco de dados;
  • linha 4: o conteúdo da tabela [articles] é recuperado;
  • linha 17: uma nova linha é inserida. Observe que a coluna [id] não é inicializada nessa operação, pois o SGBD gerará seu valor;
  • linha 20: verificação. Linha 32: a linha foi de fato adicionada, com [id] 7. Com [select *], os nomes das colunas são os da tabela, em maiúsculas;
  • linha 34: aumentamos em 10% o preço do artigo que acabou de ser adicionado;
  • linha 43: o aumento de preço de fato ocorreu;
  • linha 45: o artigo adicionado anteriormente é removido;
  • linha 48: verificamos que restam apenas seis artigos;
  • linha 56: o programa aceita apenas os quatro comandos [select], [insert], [update], e [delete];
  • linha 60: um erro informado pelo SGBD: a tabela [article] não existe;
  • linha 63: a restrição de unicidade [UNQ_NAME] impede a adição de um segundo artigo [bike]. As mensagens de erro do MySQL, assim como as do driver, estão em inglês.

10.3.3. As interfaces básicas do JDBC para o modo conectado

Voltemos ao diagrama de uma aplicação que acessa um banco de dados por meio de um driver JDBC:

Image

No modo conectado, a aplicação:

  • abre uma conexão com a fonte de dados;
  • trabalha com a fonte de dados em modo de leitura/escrita;
  • fecha a conexão.

Três tipos de objetos participam principalmente dessas operações:

  • a conexão ([Connection]), que encapsula as propriedades e os métodos da conexão com o banco de dados;
  • o comando SQL ([Statement], ou [PreparedStatement] para um comando parametrizado), que encapsula as propriedades e os métodos do comando SQL executado;
  • o resultado ([ResultSet]), que encapsula as propriedades e os métodos do resultado de um comando SQL [select].

Essas são interfaces do pacote [java.sql]. O driver MySQL as implementa com classes de seus próprios pacotes ([com.mysql.cj.jdbc.ConnectionImpl], [ClientPreparedStatement], [ResultSetImpl]…), que a aplicação não precisa conhecer: ela obtém seus objetos por meio de métodos que retornam interfaces. A conexão é obtida da classe [DriverManager] (ou de uma fonte de dados [DataSource], que veremos mais adiante), o comando SQL é criado pela conexão e o resultado é retornado pelo comando SQL.

Essas interfaces são interfaces Java. Kotlin as utiliza de acordo com suas regras padrão de acesso a código Java: um par de acessores [getXxx] / [setXxx] é usado como uma propriedade [xxx] ([result.metaData] em vez de [result.getMetaData()], [connection.autoCommit = false] em vez de [connection.setAutoCommit(false)]), e os valores retornados por um método Java têm um tipo de plataforma (indicado como [String!] nas mensagens do compilador): Kotlin não sabe se eles podem ser [null] e deixa a cargo do desenvolvedor escolher se os trata como [String] ou como [String?] (Capítulo 3). Veremos que isso é importante para colunas que podem conter o valor SQL [NULL].

10.3.3.1. A interface Connection

Ela serve para gerenciar a conexão com o banco de dados. Uma conexão é obtida pelo método estático [DriverManager.getConnection(url, user, password)], que recebe a URL do banco de dados e o JDBC (por exemplo, [jdbc:mysql://localhost:3306/dbarticles]) e as credenciais do usuário. A conexão retornada já está aberta. Os métodos da interface [Connection] que utilizaremos são os seguintes:

Método
Função
[createStatement]
Cria um comando SQL associado a esta conexão.
[prepareStatement]
[prepareStatement(sql)] cria um comando SQL configurado como [PreparedStatement] associado a esta conexão.
[prepareCall]
[prepareCall("{call name(?, ?)}")] cria um comando [CallableStatement] que executa um procedimento armazenado no banco de dados.
[autoCommit]
[autoCommit = false] (o método Java [setAutoCommit]) inicia uma transação (veja adiante).
[commit], [rollback]
confirma e desfaz a transação atual, respectivamente.
[metaData]
(o método Java [getMetaData]) retorna um objeto [DatabaseMetaData] que descreve o SGBD e o driver.
[isValid]
[isValid(seconds)] verifica se a conexão ainda pode ser usada.
[close]
fecha a conexão, junto com os comandos SQL e os resultados que eles produziram.

A abertura de uma conexão tem a seguinte forma (os trechos de código deste parágrafo foram extraídos do arquivo [models/Models.kt] do projeto, que é compilado, mas não executado):

1
2
3
    val connection = DriverManager.getConnection(URL, "root", "")
    // ... trabalhar com a conexão
    connection.close()

A inferência de tipos atribui o tipo [Connection!] a [connection]: uma conexão que pode ser usada sem verificação de nulo ([getConnection] nunca retorna [null]: se falhar, lança uma exceção).

10.3.3.2. A interface Statement

Ela serve para executar um comando SQL. Os métodos dessa interface que utilizaremos são os seguintes:

Método
Função
[executeQuery]
[executeQuery(sql)] executa um comando SQL [select]. Ele retorna um objeto [ResultSet] que representa o resultado de [select].
[executeUpdate]
[executeUpdate(sql)] executa um comando SQL [update], [insert], [delete] (ou um comando de definição, como [create table]). Ele retorna o número de linhas afetadas pela operação (atualizações, inserções, exclusões). [executeLargeUpdate] (Java 8) retorna esse número na forma de um [Long].
[execute]
[execute(sql)] executa qualquer comando SQL. Retorna [true] se o comando produziu um [ResultSet], que é então obtido pela propriedade [resultSet], e [false] se produziu um número de linhas, obtido pela propriedade [updateCount].
[addBatch], [executeBatch]
adicionam um comando a um lote e executam o lote (veja adiante).
[generatedKeys]
(o método Java [getGeneratedKeys]) retorna as chaves primárias geradas pelo SGBD durante um [insert].
[queryTimeout]
[queryTimeout = seconds] define o tempo máximo de execução do comando.
[close]
fecha o comando SQL, bem como seu [ResultSet].

A interface [PreparedStatement] deriva de [Statement]: é um comando SQL parametrizado, cujo texto é definido na criação e cujos parâmetros recebem valores por meio de métodos [setXxx]. Examinaremos isso na seção “Consultas parametrizadas”. A execução de comandos SQL sem transação tem a seguinte forma:

    // abertura da conexão
    val connection = DriverManager.getConnection(URL, "root", "")
    // criação de uma instrução SQL
    val statement = connection.createStatement()
    // execução de uma instrução select
    val result = statement.executeQuery("select ...")
    // ...
    // execução de uma instrução update, insert, delete
    val nbInsertedRows = statement.executeUpdate("insert ...")
    // ...
    // fechamento da conexão
    connection.close()

10.3.3.3. A interface ResultSet

Ela encapsula os resultados de um comando SQL [select]. Um objeto [ResultSet] representa uma tabela com linhas e colunas, que é processada sequencialmente: primeiro a primeira linha, depois a segunda e assim por diante, sem possibilidade de voltar (esse é o tipo padrão, [TYPE_FORWARD_ONLY]; o JDBC também admite resultados que podem ser percorridos nos dois sentidos, mas eles raramente são usados). O [ResultSet] tem um cursor inicialmente posicionado antes da primeira linha: é preciso chamar [next] para ler a primeira linha. Os métodos dessa interface que utilizaremos são os seguintes:

Método
Função
[next]
Avança para a próxima linha da tabela de resultados. Retorna [true] se a linha foi lida com sucesso e [false] se não há mais linhas.
[getInt]
[getInt(i)] retorna o valor inteiro da coluna de número i na linha atual. As colunas são numeradas a partir de 1, e não de 0. [getInt("name")] retorna o valor da coluna chamada [name]. Outros métodos semelhantes incluem [getBoolean], [getBigDecimal], [getDouble], [getLong], [getString]… Como possuem um parâmetro, Kotlin não os converte em propriedades.
[getObject]
[getObject(i)] retorna o valor da coluna i como um [Any] (um [Object] Java), do tipo escolhido pelo driver; [getObject(i, LocalDate::class.java)] o retorna no tipo solicitado (em Kotlin, [X::class.java] designa o objeto Java [Class] de uma classe, o [X.class] de Java).
[wasNull]
retorna [true] se a última coluna lida não tinha valor, o que é representado pelo valor SQL [NULL]. Os métodos que retornam um objeto ([getString], [getBigDecimal], [getObject]…) retornam então [null]; os que retornam um tipo básico ([getInt], [getDouble]…) retornam 0: somente [wasNull] permite distinguir 0 de [NULL].
[metaData]
(o método Java [getMetaData]) retorna um objeto [ResultSetMetaData] que descreve as colunas do resultado: [columnCount] (o número de colunas), [getColumnLabel(i)] (o nome da coluna de número i), [getColumnTypeName(i)] (seu tipo SQL)…
[close]
fecha o resultado.

O processamento de um objeto [ResultSet] costuma ter a seguinte forma:

    // abertura da conexão
    val connection = DriverManager.getConnection(URL, "root", "")
    // execução da instrução select
    val statement = connection.createStatement()
    val result = statement.executeQuery("select ...")
    // uso dos resultados
    while (result.next()) {
        // usar a linha atual
        // ...
    }
    // fechamento do resultado, da instrução e da conexão
    result.close()
    statement.close()
    connection.close()

O laço [while (result.next())] é o mesmo de Java: veremos que, em Kotlin, ele é escrito uma única vez em uma função de extensão, em vez de ser repetido em cada função que lê um resultado.

O tipo de plataforma merece uma observação aqui. Para uma coluna que aceita o valor SQL [NULL], [getString(i)] retorna [null]; se você escrever [val name: String = result.getString(2)], Kotlin insere uma verificação e lança um erro [NullPointerException] assim que a atribuição ocorre, com a mensagem “getString(...) must not be null”. Portanto, você deve declarar os valores de coluna que podem ser *[NULL]* como [String?]*** e depois processá-los com os operadores ***[?.] e ***[?:]* do Capítulo 3. Em nossa tabela, todas as colunas são [NOT NULL]: o problema não surge.

Com o MySQL Connector/J, por padrão, todas as linhas do resultado são transferidas do servidor para a memória da aplicação assim que a consulta é executada; [next] então as percorre. Para um resultado de vários milhões de linhas, é possível instruir o driver a recebê-las à medida que são lidas (parâmetro [useCursorFetch=true] na URL e [fetchSize = n] no comando SQL): trata-se de uma configuração específica do driver, que não usaremos.

10.3.4. Tratamento de erros

A camada [dao] pode encontrar numerosos erros ao operar o banco de dados: servidor fora do ar, banco de dados inexistente, senha incorreta, erro de sintaxe SQL, violação de restrição de integridade etc. Eles são relatados como exceções [SQLException]. Em Java, [SQLException] é uma exceção verificada (checked): todos os métodos do JDBC a declaram em sua cláusula [throws], e o compilador Java exige que o código que os chama a capture ou a declare por sua vez. Kotlin não tem exceções verificadas (Capítulo 3): o compilador não impõe nenhuma restrição, e uma função Kotlin que chama o JDBC não precisa declarar nada. Isso é ao mesmo tempo uma liberdade e uma responsabilidade: nada avisa o desenvolvedor de que uma linha pode falhar; cabe a ele decidir onde os erros são tratados. Uma camada [dao] deve tratar esses erros e, aconteça o que acontecer, liberar os recursos que adquiriu: a conexão, o comando SQL e o resultado devem ser fechados. Escrito com cláusulas [finally], como se fazia em Java antes do Java 7, o código mostrado acima para processar o resultado de um comando [select] passa a ser o seguinte:

    var connection: Connection? = null
    try {
        // abertura da conexão
        connection = DriverManager.getConnection(URL, "root", "")
        // execução da instrução select
        val statement = connection.createStatement()
        val result = statement.executeQuery("select ...")
        try {
            // uso dos resultados
            while (result.next()) {
                // ...
            }
        } finally {
            // fechamento do resultado
            result.close()
        }
    } catch (e: SQLException) {
        // tratamento da exceção
        // ...
    } finally {
        // fechamento da conexão (que também fecha suas instruções)
        try {
            connection?.close()
        } catch (e: SQLException) {
            // nada mais pode ser feito
        }
    }

Esse código é trabalhoso: a conexão deve ser declarada como [var], do tipo [Connection?], antes do [try] para ser visível na cláusula [finally], e seu fechamento pode, ele próprio, provocar uma [SQLException] (linha 23), que deve ser interceptada na cláusula [finally]. Observe a chamada segura a [connection?.close()]: se a conexão não pôde ser aberta, [connection] vale [null] e nada é feito.

O Capítulo 3 apresentou a solução de Kotlin: a função de extensão use, definida para qualquer objeto *[AutoCloseable] , executa o bloco (a lambda) que recebe e fecha o objeto ao sair do bloco, qualquer que seja a forma de saída. Isso equivale à instrução try-with-resources de Java. Os objetos *[Connection]*, *[Statement], e *[ResultSet] implementam a interface *[AutoCloseable] :

    try {
        // cada recurso é fechado ao sair de seu bloco [use], qualquer que seja a forma como se sai
        DriverManager.getConnection(URL, "root", "").use { connection ->
            connection.createStatement().use { statement ->
                statement.executeQuery("select ...").use { result ->
                    // uso dos resultados
                    while (result.next()) {
                        // ...
                    }
                }
            }
        }
    } catch (e: SQLException) {
        // tratamento da exceção
        // ...
    }
  • linhas 3–5: três recursos são abertos, cada um por um *[use]. O objeto aberto é o parâmetro da lambda ([connection], [statement], [result]); damos a ele um nome em vez de usar [it], porque as três lambdas são aninhadas e um [it] se referiria ao objeto do [use] mais interno. Diferentemente do try-with-resources de Java, que aceita vários recursos entre parênteses, *[use] trata apenas um recurso: por isso, aninhamos os [use]s. Os recursos são fechados na ordem inversa da abertura: o resultado, depois o comando SQL e, por fim, a conexão. Uma exceção lançada por um fechamento não apaga a que pode ter causado a saída do bloco: ela é anexada a esta como uma exceção “suprimida” ([suppressedExceptions]), como em Java;
  • linha 13: a cláusula [catch] intercepta tanto os erros de conexão e de execução quanto os erros de fechamento.

Como [use] é uma função [inline] (Capítulo 7), seu bloco não é um objeto lambda: ele é copiado no lugar da chamada, e uma instrução [return] escrita dentro do bloco encerra a função envolvente depois que o recurso foi fechado. Além disso, [use] retorna o valor de seu bloco: [val n = connection.createStatement().use { it.executeUpdate(sql) }] executa o comando, fecha o objeto [Statement] e retorna o número de linhas modificadas. Faremos uso frequente dessas duas propriedades.

O benefício de [use] não está apenas na economia de linhas: ele poupa o desenvolvedor de ter de fechar a conexão manualmente. Esquecer de fechar uma conexão, porém, pode passar despercebido e fazer a aplicação “travar” de modo aparentemente aleatório quando o SGBD (ou o pool de conexões, que veremos mais adiante) atinge o número máximo de conexões abertas que suporta. No restante deste curso, todo recurso JDBC é aberto com uma instrução use.

A classe [SQLException] fornece três informações sobre o erro, que Kotlin apresenta como propriedades:

  • [message]: a mensagem de erro;
  • [errorCode]: o código de erro específico do SGBD (o número de erro do MySQL: 1049, 1062…);
  • [sqlState]: um código de erro de cinco caracteres definido pelo padrão SQL (“SQLSTATE”), comum a todos os SGBDs para os erros mais frequentes: a classe [42] indica um erro de sintaxe ou de acesso, [23] uma violação de restrição de integridade, [08] um erro de conexão…

Observe a propriedade [sqlState], derivada do método Java [getSQLState]: quando um nome começa com várias letras maiúsculas, Kotlin as converte em minúsculas, exceto a última, se ela inicia a palavra seguinte ([SQLState] torna-se [sqlState]). Desde o Java 6 (JDBC 4.0), [SQLException] também tem classes derivadas padrão, que os drivers lançam conforme a natureza do erro. As principais são as seguintes:

Classe
Erro
[SQLSyntaxErrorException]
erro de sintaxe SQL, tabela ou banco de dados inexistente, permissões insuficientes (SQLSTATE, classe 42)
[SQLIntegrityConstraintViolationException]
violação de restrição de integridade: chave duplicada, chave estrangeira, coluna [NOT NULL] (SQLSTATE, classe 23)
[SQLDataException]
dados inválidos: valor grande demais, conversão impossível (SQLSTATE, classe 22)
[SQLTimeoutException]
tempo esgotado ([queryTimeout])
[SQLTransactionRollbackException]
transação revertida pelo SGBD, por exemplo, devido a um deadlock
[SQLNonTransientConnectionException], [SQLRecoverableException]
problema de conexão

O programa [Jdbc02.kt] provoca vários erros e os exibe:

package jdbc02

import java.sql.DriverManager
import java.sql.SQLException
import java.sql.SQLIntegrityConstraintViolationException

// tratamento dos erros de acesso ao banco de dados

// os parâmetros de conexão, no topo do programa
private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

// o número do erro do MySQL "Duplicate entry"
private const val ER_DUP_ENTRY = 1062

fun main() {
    // operações que falham, cada uma por um motivo diferente
    execute("missing database", "jdbc:mysql://localhost:3306/dbunknown", "select * from articles")
    execute("unreachable server", "jdbc:mysql://localhost:3399/dbarticles?connectTimeout=5000",
        "select * from articles")
    execute("syntax error", URL, "select * fromm articles")
    execute("missing table", URL, "select * from article")
    execute("duplicate name", URL,
        "insert into articles(name, price, currentstock, minimumstock) values('bike', 100, 10, 1)")
    execute("negative price", URL,
        "insert into articles(name, price, currentstock, minimumstock) values('canoe', -100, 10, 1)")
}

// executa uma instrução SQL e exibe o possível erro
private fun execute(title: String, url: String, query: String) {
    println("--- $title")
    try {
        DriverManager.getConnection(url, USER, PASSWORD).use { connection ->
            connection.createStatement().use { statement -> statement.execute(query) }
        }
        println("no error")
    } catch (ex: SQLException) {
        when (ex) {
            // um erro específico, reconhecido por sua classe e seu número
            is SQLIntegrityConstraintViolationException if ex.errorCode == ER_DUP_ENTRY ->
                println("this article already exists (${ex.message})")
            // os outros erros
            else -> {
                println("${ex.javaClass.simpleName} : ErrorCode=${ex.errorCode}, SQLState=${ex.sqlState}")
                println("  ${ex.message?.lineSequence()?.first()}")
            }
        }
    }
}

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc02.Jdbc02Kt
--- missing database
SQLSyntaxErrorException : ErrorCode=1049, SQLState=42000
  Unknown database 'dbunknown'
--- unreachable server
CommunicationsException : ErrorCode=0, SQLState=08S01
  Communications link failure
--- syntax error
SQLSyntaxErrorException : ErrorCode=1064, SQLState=42000
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fromm articles' at line 1
--- missing table
SQLSyntaxErrorException : ErrorCode=1146, SQLState=42S02
  Table 'dbarticles.article' doesn't exist
--- duplicate name
this article already exists (Duplicate entry 'bike' for key 'articles.UNQ_NAME')
--- negative price
SQLException : ErrorCode=3819, SQLState=HY000
  Check constraint 'CHK_PRICE' is violated.

Comentários:

  • linhas 10–12: os parâmetros de conexão com o banco de dados, definidos no topo do programa como constantes de nível superior chamadas [const val], privadas ao arquivo;
  • linha 15: o número de erro do MySQL “Duplicate entry”, conforme nomeado na documentação do MySQL;
  • linhas 19–27: seis operações que falham: banco de dados inexistente; servidor inacessível (nenhum servidor MySQL está escutando na porta 3399, o que produz o mesmo erro que um servidor parado); erro de sintaxe; tabela inexistente; nome de artigo duplicado; preço negativo. O parâmetro [connectTimeout=5000] da URL da linha 20 limita o tempo de espera da conexão a 5 segundos (5000 ms);
  • linha 31: a função [execute] executa o comando SQL [query] no banco de dados especificado pela URL [url];
  • linhas 34–35: a conexão e o comando SQL são abertos por dois comandos [use] aninhados. O segundo cabe em uma única linha: a lambda [{ statement -> statement.execute(query) }] tem apenas uma instrução;
  • linha 35: o método [execute] executa qualquer comando SQL: [select] ou [insert]. Se ele lançar uma exceção, a mensagem “no error” da linha 37 não aparece;
  • linha 39: um [when] sobre a exceção, com cláusulas [is Type] (Capítulo 3);
  • linha 41: uma guarda ([if], Kotlin 2.2) permite o tratamento separado de um erro específico: uma violação de restrição de integridade ([SQLIntegrityConstraintViolationException]) com código MySQL igual a 1062, isto é, uma chave duplicada. Isso equivale ao padrão com guarda [case … when …] do [switch] do Java 21. Nesse caso, uma aplicação exibiria uma mensagem compreensível para o usuário;
  • linhas 44–46: os outros erros. A classe da exceção é exibida ([ex.javaClass.simpleName]: [javaClass] retorna o objeto Java [Class]), seguida do código de erro MySQL e do código SQLSTATE e, depois, da primeira linha da mensagem: algumas mensagens do driver ocupam várias linhas. A propriedade [message] é do tipo [String?] (uma exceção pode não ter mensagem): ela é tratada com chamadas seguras, [?.lineSequence()?.first()]; [lineSequence] divide a string em uma sequência de linhas, da qual a primeira é obtida por [first]. Se a mensagem fosse [null], a expressão resultaria em [null] e o template exibiria “null”.

Os resultados mostram que o driver MySQL usa as classes padrão: [SQLSyntaxErrorException] para um banco de dados inexistente (1049), um erro de sintaxe (1064) ou uma tabela inexistente (1146), todos com SQLSTATE derivado de [42xxx]. Um servidor inacessível gera uma [CommunicationsException], uma classe do driver (derivada de [SQLRecoverableException]): não há código de erro do MySQL, pois o servidor não respondeu, mas o SQLSTATE [08S01] indica uma falha de conexão. A violação de uma restrição [CHECK] (erro 3819) é simplesmente uma [SQLException], com SQLSTATE [HY000] (“general error”): o MySQL não lhe atribui um SQLSTATE da classe 23. Portanto, não se pode confiar totalmente nas classes derivadas: o código de erro e o SQLSTATE continuam úteis.

Uma camada [dao] que deseje permanecer independente do SGBD usa apenas as classes padrão e o SQLSTATE; os códigos de erro ([errorCode]) são específicos de cada SGBD.

Uma última observação sobre exceções verificadas. Uma função Kotlin que lança uma [SQLException] não a declara em lugar nenhum; se ela for chamada por código Java, esse código não poderá capturar a exceção com um [catch (SQLException e)], que o compilador Java rejeita quando nenhum dos métodos chamados a declara. Por isso, acrescentamos a anotação [@Throws(SQLException::class)] à função Kotlin, o que gera a cláusula [throws] esperada pelo Java. Nossos programas, escritos inteiramente em Kotlin, não precisam disso.

10.3.5. Configuração do projeto de exemplo

O driver JDBC para MySQL é uma biblioteca publicada no Maven Central, o repositório de bibliotecas de onde o Gradle obtém as dependências (Capítulo 2). A pasta [10-jdbc] deste capítulo é um build Gradle multiprojeto (Capítulo 2): os programas deste capítulo formam o subprojeto [jdbc], e a aplicação [Tax] versão 6, tratada no final do capítulo, é composta por dois outros subprojetos:

10-jdbc/
  settings.gradle.kts               the list of the subprojects
  build.gradle.kts                  the root project
  gradlew, gradlew.bat, gradle/     the Gradle wrapper
  sql/
    dbarticles.sql                  creation script of the [dbarticles] database
    dbtaxes.sql                     creation script of the [dbtaxes] database (Tax application)
  jdbc/                             the subproject of the examples
    build.gradle.kts
    dbarticles.properties           the connection parameters read by Jdbc01
    src/main/kotlin/
      common/Article.kt             the data class of a row of the [articles] table
      common/ResultSets.kt          extension functions to read a ResultSet
      common/Transactions.kt        an extension function for transactions
      jdbc01/Jdbc01.kt ...          the programs of the chapter, one package per program
      models/Models.kt              the code patterns of the beginning of the chapter
    src/main/resources/
      logback.xml                   logging configuration
  TaxV6/                            the Tax application, version 6

O arquivo [settings.gradle.kts] deste capítulo declara os subprojetos:

// capítulo 10: acesso a banco de dados com JDBC

// o Gradle baixa por conta própria o JDK solicitado por jvmToolchain(25) se não o encontrar na máquina
plugins {
    id("org.gradle.toolchains.foojay-resolver-convention") version "1.0.0"
}

rootProject.name = "jdbc-chapter"

// os programas JDBC do capítulo
include("jdbc")
// versão 6 da aplicação [Tax]: uma camada [dao] que lê um banco de dados MySQL
include("TaxV6:tax-dao-mysql", "TaxV6:tax-ui-mysql")

// as camadas [business] e [ui] da versão 5 (capítulo 6) são reutilizadas: composite build
includeBuild("../06-architectures")
  • linhas 3–6: o plugin que permite ao Gradle baixar o próprio JDK caso não o encontre na máquina (Capítulo 2);
  • linha 11: o subprojeto [jdbc], cuja pasta tem o mesmo nome;
  • linhas 13–16: a versão 6 da aplicação [Tax], apresentada no final do capítulo.

O arquivo [build.gradle.kts] do subprojeto [jdbc] é o seguinte:

// os exemplos do capítulo 10: acesso a banco de dados com JDBC
plugins {
    // a versão do plugin é dada pelo projeto raiz
    kotlin("jvm")
    application
}

group = "examples"
version = "1.0"

repositories {
    mavenCentral()
}

dependencies {
    // o driver JDBC do MySQL (o programa Jdbc03 usa uma de suas classes: [implementation] é necessário)
    implementation("com.mysql:mysql-connector-j:9.7.0")
    // o pool de conexões HikariCP
    implementation("com.zaxxer:HikariCP:7.0.2")
    // Logback: o logging usado pelo HikariCP (por meio do SLF4J)
    runtimeOnly("ch.qos.logback:logback-classic:1.5.38")
    // corrotinas (programa Jdbc08Coroutines)
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.11.0")
}

kotlin {
    // o código é compilado e executado com o JDK 25
    jvmToolchain(25)
    compilerOptions {
        // um aviso do compilador é tratado como erro
        allWarningsAsErrors = true
        // os nomes dos parâmetros das funções são mantidos no bytecode
        javaParameters = true
    }
}

application {
    // o programa executado por [gradlew run] é dado pela propriedade [main]:
    // gradlew -q :jdbc:run -Pmain=jdbc02.Jdbc02Kt
    mainClass = providers.gradleProperty("main").orElse("jdbc01.Jdbc01Kt")
}

tasks.named<JavaExec>("run") {
    // o programa em execução pode ler o teclado
    standardInput = System.`in`
}

// no Windows, a saída de tela do programa iniciado pelo Gradle (run, bootRun...) é codificada em UTF-8,
// caso contrário os caracteres acentuados ficam corrompidos no console do IntelliJ IDEA ou no terminal
tasks.withType<JavaExec>().configureEach {
    jvmArgs("-Dstdout.encoding=UTF-8", "-Dstderr.encoding=UTF-8")
}
  • linhas 2–5: o plugin Kotlin para JVM, cuja versão é definida pelo projeto raiz (Capítulo 2), e o plugin [application], que fornece a tarefa [run];
  • linhas 15–23: as quatro bibliotecas do projeto, baixadas pelo Gradle do Maven Central durante o primeiro build:
    • linha 17: o driver JDBC para MySQL, MySQL Connector/J, com as coordenadas [com.mysql:mysql-connector-j]. Até a versão 8.0.30, ele era publicado com as coordenadas [mysql:mysql-connector-java], que ainda são encontradas em muitos projetos. Um programa que usa apenas as interfaces do JDBC precisa do driver somente em tempo de execução: ele é então declarado com [runtimeOnly], o que garante que nenhuma classe do driver possa ser usada por engano no código. Nós o declaramos com [implementation] porque o programa [Jdbc03] usa uma classe do driver, [MysqlDataSource], para uma demonstração;
    • linha 18: o pool de conexões HikariCP, que usaremos a partir do programa [Jdbc03];
    • linha 21: o HikariCP grava suas mensagens de log por meio da API SLF4J (“Simple Logging Facade for Java”), que requer uma implementação: usamos o Logback, necessário apenas em tempo de execução ([runtimeOnly]). Sem ele, o SLF4J exibiria um aviso na inicialização. O arquivo [src/main/resources/logback.xml], mostrado a seguir, exibe apenas avisos e erros;
    • linha 23: a biblioteca de corrotinas, kotlinx-coroutines (Capítulo 9), usada pelo programa [Jdbc08Coroutines], que executa requisições JDBC em corrotinas;
  • linhas 27–33: o compilador, configurado como em todos os projetos do curso: JDK 25, avisos tratados como erros, nomes dos parâmetros preservados nas classes compiladas;
  • linhas 38–40: o programa executado por [gradlew run] é especificado pela propriedade [main]; por padrão, é [jdbc01.Jdbc01Kt];
  • linhas 43–45: o programa executado pode ler do teclado, o que [Jdbc01] exige;
  • linhas 48–51: os programas iniciados pelo Gradle gravam sua saída em UTF-8 para que os caracteres acentuados sejam exibidos corretamente no Windows (Capítulo 2).

O arquivo de configuração do Logback é o seguinte:

<?xml version="1.0" encoding="UTF-8"?>
<!-- configuração do Logback: apenas avisos e erros são exibidos -->
<configuration>
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%-5level %logger{0} - %msg%n</pattern>
        </encoder>
    </appender>
    <root level="WARN">
        <appender-ref ref="CONSOLE"/>
    </root>
</configuration>
  • linhas 4–8: as mensagens são gravadas no console no formato “classe nível - mensagem”;
  • linha 9: somente as mensagens de nível [WARN] (aviso) ou [ERROR] são exibidas. Com o nível [INFO], você veria as mensagens de inicialização e de encerramento do pool HikariCP.

Há dois drivers JDBC comuns para MySQL:

  • [com.mysql:mysql-connector-j] (“ Connector/J”), lançado pela Oracle, proprietária do MySQL, sob a licença GPL v2 com uma exceção (“Universal FOSS Exception”) que permite usá-lo com software distribuído sob outras licenças livres; uma licença comercial cobre todos os outros casos. Esse é o driver oficial, que utilizamos;
  • [org.mariadb.jdbc:mariadb-java-client], o driver do MariaDB (uma variante livre do MySQL), licenciado sob LGPL, que também funciona com o MySQL.

Os dois drivers implementam as mesmas interfaces JDBC: passar de um para o outro exige apenas trocar a dependência em [build.gradle.kts] e o início da URL ([jdbc:mariadb:] em vez de [jdbc:mysql:]).

Uma observação histórica: até o Java 5, era preciso carregar a classe do driver antes de abrir uma conexão, com uma instrução como [Class.forName("com.mysql.jdbc.Driver")], que ainda pode ser encontrada em muitos exemplos na Internet. Desde o Java 6 (JDBC 4.0), isso não é mais necessário: cada arquivo de driver declara sua classe em um arquivo [META-INF/services/java.sql.Driver], e [DriverManager] carrega automaticamente todos os drivers presentes no classpath por meio do mecanismo de serviços ([ServiceLoader]).

Os parâmetros de conexão não devem ser escritos diretamente no código de uma aplicação real: eles dependem da máquina em que a aplicação está instalada. O primeiro programa deste capítulo os lê de um arquivo de propriedades (Capítulo 6), [dbarticles.properties], localizado na pasta do subprojeto:

1
2
3
4
# parâmetros de conexão do banco de dados [dbarticles]
url=jdbc:mysql://localhost:3306/dbarticles
user=root
password=
  • linha 2: a URL JDBC do banco de dados. Uma URL JDBC tem a forma [jdbc:subprotocol:details]. O subprotocolo identifica o driver ([mysql]), e os detalhes, cujo formato depende do driver, identificam o banco de dados. Para o MySQL, são:
    • [//localhost]: o nome da máquina em que está o SGBD MySQL, neste caso [localhost], isto é, a máquina em que o programa está sendo executado;
    • [:3306]: a porta em que o servidor MySQL escuta as conexões; o padrão é 3306;
    • [/dbarticles]: o nome do banco de dados;
    • parâmetros opcionais do driver podem vir em seguida, após um ponto de interrogação, na forma [?param1=value1&param2=value2]. Encontraremos [connectTimeout], [rewriteBatchedStatements], e [allowMultiQueries];
  • linha 3: o usuário MySQL, aqui [root], o administrador do SGBD;
  • linha 4: sua senha, deixada em branco aqui.

A documentação do Connector/J fornece uma lista completa dos parâmetros de URL: Connector/J Configuration Properties.

Os outros programas deste capítulo, que são mais curtos, guardam seus parâmetros de conexão em constantes no topo do programa, como em [Jdbc02.kt]: isso é mais simples para exemplos, mas não deve ser feito em uma aplicação real.

Para executar os programas, vá até a pasta [10-jdbc] e digite:

> gradlew -q :jdbc:run
> gradlew -q :jdbc:run -Pmain=jdbc02.Jdbc02Kt

O primeiro comando executa [Jdbc01], e o segundo executa [Jdbc02]. O prefixo [:jdbc:] refere-se à tarefa [run] do subprojeto [jdbc]; a tarefa [run] é executada na pasta do subprojeto, [10-jdbc/jdbc], onde está o arquivo [dbarticles.properties]. A opção [-q] (“quiet”) suprime as mensagens do Gradle: resta apenas a saída do programa. Naturalmente, você também pode executar os programas pelo IntelliJ IDEA clicando no triângulo verde ao lado da função [main] (nesse caso, será preciso verificar o diretório atual na configuração de execução). A seguir, reproduziremos apenas a saída dos programas.

10.3.6. O programa de exemplo

O programa [Jdbc01.kt], do qual vimos um exemplo de execução, é o seguinte (programa principal):

package jdbc01

import java.io.IOException
import java.nio.file.Path
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.SQLException
import java.util.Properties
import kotlin.io.path.bufferedReader

// aplicação de console: executa instruções SQL digitadas no teclado
// em um banco de dados cujos parâmetros de conexão são lidos de um arquivo de configuração

// as instruções SQL aceitas
private val SQL_COMMANDS = setOf("select", "insert", "update", "delete")

// os parâmetros de conexão de um banco de dados
data class ConnectionParameters(val url: String, val user: String, val password: String) {
    // uma nova conexão com o banco de dados
    fun connect(): Connection = DriverManager.getConnection(url, user, password)
}

// leitura dos parâmetros de conexão a partir de um arquivo de propriedades
fun readConfiguration(file: String): ConnectionParameters {
    val config = Properties()
    Path.of(file).bufferedReader().use { config.load(it) }
    val url = config.getProperty("url") ?: throw IOException("the [url] parameter is missing")
    return ConnectionParameters(url, config.getProperty("user", ""), config.getProperty("password", ""))
}

fun main() {
    // leitura dos parâmetros de conexão a partir de [dbarticles.properties] (pasta atual)
    val parameters = try {
        readConfiguration("dbarticles.properties")
    } catch (e: IOException) {
        println("Configuration error: $e")
        return
    }

    // exibição dos parâmetros de conexão
    println("JDBC URL of the database: [${parameters.url}], user: [${parameters.user}]")

    // loop de entrada-execução das instruções SQL digitadas no teclado
    while (true) {
        // solicitação da consulta
        print("\nSQL query (nothing to stop): ")
        val query = readlnOrNull()?.trim().orEmpty()
        // terminou?
        if (query.isEmpty()) break
        // a primeira palavra da consulta deve ser uma das quatro instruções aceitas
        val firstWord = query.split(Regex("\\s+")).first().lowercase()
        if (firstWord !in SQL_COMMANDS) {
            println("Invalid query. Use select, insert, update, delete or nothing to stop")
            continue
        }
        // execução da consulta
        if (firstWord == "select") {
            executeSelect(parameters, query)
        } else {
            executeUpdate(parameters, query)
        }
    }
}
  • Linhas 5–8: as interfaces e classes JDBC utilizadas, todas do pacote [java.sql]. O programa não contém o nome de nenhuma classe do driver MySQL: somente a URL e o JDBC, lidos da configuração, referem-se ao MySQL;
  • Linha 16: os quatro comandos SQL aceitos, em um conjunto somente leitura criado por [setOf] (Capítulo 5);
  • linhas 19–21: os três parâmetros de conexão são reunidos em uma data class. Em vez de passar três strings a cada função, como faz o programa Java do curso, passamos um objeto [ConnectionParameters], que sabe abrir uma conexão: seu método [connect] chama [DriverManager.getConnection] com as próprias propriedades. O tipo retornado, [Connection], é especificado explicitamente: sem ele, a função retornaria o tipo de plataforma [Connection!];
  • linhas 25–29: leitura do arquivo de propriedades, como descrito no Capítulo 6. A função de extensão [bufferedReader] de [kotlin.io.path] abre o arquivo em UTF-8, e [use] o fecha. [getProperty] retorna o valor de uma chave, ou [null] se a chave não existir: seu tipo em Kotlin é [String?]. Como a URL é obrigatória, o operador Elvis [?:] dispara uma [IOException] quando ela está ausente, o que permite tratar da mesma forma um arquivo ausente e um arquivo incompleto; [getProperty(key, default)] retorna um valor padrão para os outros dois parâmetros;
  • linha 32: a função de nível superior [main], sem parâmetros, já que o programa não usa seus argumentos;
  • linhas 34–38: [try] é uma expressão (Capítulo 3): seu valor é o do bloco [try] se tudo correr bem; em caso de erro, a cláusula [catch] exibe uma mensagem e encerra o programa com [return]. O arquivo é procurado na pasta atual, aquela a partir da qual o programa é iniciado: é a pasta do subprojeto quando você digita [gradlew :jdbc:run];
  • linha 45: o laço de entrada dos comandos SQL digitados no teclado e executados no banco de dados;
  • Linhas 47–48: [print] exibe o prompt sem passar para a linha seguinte, e então [readlnOrNull] lê uma linha digitada no teclado; retorna [null] se a entrada padrão estiver fechada. A chamada segura [?.trim()] seguida de [orEmpty] transforma esse [null] em uma string vazia, o que interrompe o laço na linha seguinte;
  • linha 52: a primeira palavra da consulta, obtida dividindo a consulta nos espaços (expressão regular [\s+], Capítulo 5). Ela é convertida para minúsculas para a comparação, mas a consulta em si não é modificada: converter toda a consulta para minúsculas impediria a inserção de um artigo chamado “Bike”;
  • linhas 53–55: se a consulta for inválida, uma mensagem de erro é exibida e o programa passa para a consulta seguinte. O operador [!in] chama o método [contains] do conjunto (Capítulo 4);
  • linhas 58–61: o comando SQL é executado. Sua forma varia conforme seja um comando [select] ou um comando [insert], [update] ou [delete] : no primeiro caso, o comando recupera dados do banco de dados sem modificá-lo; no segundo, atualiza o banco de dados sem recuperar nenhum dado. Em ambos os casos, a execução é delegada a uma função que recebe os parâmetros de conexão e o comando SQL a executar.

Observe a ausência de qualquer declaração de exceção: em Java, a leitura do arquivo exige interceptar ou declarar a exceção [IOException] . Em Kotlin, isso é uma escolha: capturamos a [IOException] aqui porque é neste ponto que sabemos o que fazer com o erro (avisar o usuário e parar).

10.3.7. Execução de uma consulta SELECT

A execução de comandos SQL envolve as seguintes etapas:

  • conectar-se ao banco de dados;
  • enviar os comandos SQL ao banco de dados;
  • processar os resultados dos comandos SQL;
  • fechar a conexão.

As etapas 2 e 3 podem ser repetidas; a conexão só é fechada ao final do processamento do banco de dados. Conexões abertas são recursos limitados em um SGBD: elas devem ser preservadas, e procuramos sempre limitar o tempo de vida de uma conexão aberta. Em nosso programa, a conexão é fechada após cada comando SQL, e uma nova conexão é aberta para o comando seguinte.

No entanto, abrir uma conexão é custoso: é preciso estabelecer uma conexão de rede com o servidor, possivelmente negociar a criptografia e autenticar-se. [DriverManager] não faz nada para reduzir esse custo: cada chamada a [getConnection] abre uma nova conexão de rede, e cada [close] a fecha de fato. Para uma ferramenta interativa como esta, isso não é um problema. Uma aplicação que executa muitos comandos SQL, um servidor web, por exemplo, usa em seu lugar um pool de conexões: quando a aplicação fecha uma conexão, o pool não a fecha de verdade, mas a devolve ao pool; quando a aplicação solicita uma nova conexão, ela recebe uma das conexões do pool já estabelecidas. A vantagem desse sistema é ser transparente para o desenvolvedor: o código que abre e fecha conexões com [use] não precisa ser modificado. Veremos como usá-lo na seção “Data sources e pools de conexões”. Abrir uma conexão no início de cada operação e fechá-la ao final é, portanto, a melhor prática.

Vamos nos concentrar primeiro na execução dos comandos SQL [select]. A função [executeSelect] de nosso programa de exemplo e a função [displayResultSet] que ela utiliza são as seguintes:

// execução de uma instrução select
private fun executeSelect(parameters: ConnectionParameters, query: String) {
    try {
        // a conexão, a instrução SQL e o resultado são fechados ao sair de seu bloco [use]
        parameters.connect().use { connection ->
            connection.createStatement().use { statement ->
                statement.executeQuery(query).use { result ->
                    // exibição dos resultados
                    displayResultSet(result)
                }
            }
        }
    } catch (e: SQLException) {
        println("Database access error (${e.message})")
    }
}

// exibição das linhas de um resultado de select
private fun displayResultSet(result: ResultSet) {
    // os nomes das colunas, dados pelos metadados do resultado
    val metadata = result.metaData
    val columns = 1..metadata.columnCount
    val header = columns.joinToString(",") { metadata.getColumnLabel(it) }
    val dashes = "-".repeat(header.length)
    println("\n$dashes\n$header\n$dashes\n")
    // as linhas, uma após a outra
    while (result.next()) {
        println(columns.joinToString(" ") { "${result.getObject(it)}" })
    }
}
  • Linha 2: a função recebe os parâmetros de conexão e o comando SQL [select] [query] a executar. Como as outras duas funções do programa, ela é declarada como [private]: é visível apenas no arquivo [Jdbc01.kt];
  • linhas 5–7: três instruções [use] aninhadas abrem três recursos:
    • linha 5: a conexão com o banco de dados, obtida de [DriverManager] por meio do método [connect]. [DriverManager] consulta cada driver presente no classpath: o primeiro que reconhece a URL abre a conexão. É nesse momento que os parâmetros de conexão são usados e que ocorrem os erros de conexão. A conexão é do tipo definido pela interface [Connection]: o código não sabe que se trata de um objeto do driver MySQL;
    • Linha 6: o comando SQL, um objeto [Statement] criado pela conexão. O mesmo tipo de objeto é usado para executar um comando [select] e um comando [update], [insert] e [delete] ;
    • Linha 7: o comando [select] é executado pelo método [executeQuery], que retorna um objeto [ResultSet];
  • Linha 9: a função [displayResultSet] é responsável por exibir os resultados;
  • Linhas 13–14: qualquer operação no banco de dados pode disparar uma [SQLException]. Isso é ainda mais provável aqui, porque os comandos SQL digitados pelo usuário podem conter erros de sintaxe. Precisamos poder informá-lo disso: a exceção é capturada e sua mensagem é exibida. Quando a cláusula [catch] é executada, seja a exceção originada da conexão, da execução ou da leitura das linhas, as instruções [use] já fecharam o conjunto de resultados, o comando SQL e a conexão;
  • Linha 19: a função recebe um objeto [ResultSet]. Seus métodos podem lançar uma [SQLException]; em Java, o método correspondente deve declará-la como [throws SQLException]. Em Kotlin, nenhuma declaração é necessária: a exceção simplesmente se propaga até a função chamadora, que a captura;
  • linhas 21–25: os nomes das colunas da tabela de resultados do [select] são exibidos. São as colunas [coli] da consulta [select col1, col2, … from table …]. Elas são fornecidas pelos metadados do resultado, um objeto [ResultSetMetaData] obtido pela propriedade [metaData]: [columnCount] fornece o número de colunas, e [getColumnLabel(i)] fornece o nome da coluna de número i, ou seu alias, se a consulta especificar um ([select count(*) as total …]). Como as colunas são numeradas a partir de 1, constrói-se o intervalo [1..metadata.columnCount] (linha 22), e então [joinToString] (Capítulo 5) converte cada número em um nome e concatena os nomes, separando-os por vírgulas. Nenhum array intermediário é necessário, ao contrário da versão Java. ["-".repeat(n)] produz uma string de n hifens;
  • linhas 27–28: percorremos a tabela de resultados e exibimos os valores de cada linha, separados por espaços, usando o mesmo intervalo [columns] e a mesma função [joinToString];
  • linha 28: o tipo da coluna i do resultado é desconhecido, porque a consulta digitada pelo usuário é desconhecida. Portanto, não podemos usar a sintaxe [result.getXxx(i)], em que [Xxx] é o tipo da coluna. Em vez disso, usamos [getObject(i)], que retorna o valor na forma de um [Any!]; o template ["${result.getObject(it)}"] o converte em string por meio de seu método [toString] (e exibe “null” para um valor SQL [NULL]). Para a coluna [price], que é do tipo SQL [DECIMAL(10,2)], o objeto é um [BigDecimal] com duas casas decimais, que seu método [toString] exibe com ponto decimal ([500.00]): ao contrário de [String.format], [toString] não depende do idioma da estação de trabalho. Uma exibição “à francesa” usaria ["%.2f".format(price)] ou a classe [NumberFormat] (Capítulo 5).

10.3.8. Execução de uma consulta de atualização: INSERT, UPDATE, DELETE

O código da função [executeUpdate] é o seguinte:

// execução de uma instrução de atualização (insert, update, delete)
private fun executeUpdate(parameters: ConnectionParameters, query: String) {
    try {
        parameters.connect().use { connection ->
            connection.createStatement().use { statement ->
                // execução da instrução SQL
                val nbRows = statement.executeUpdate(query)
                // exibição do resultado
                println("$nbRows row(s) modified")
            }
        }
    } catch (e: SQLException) {
        println("Database access error (${e.message})")
    }
}

A execução de um comando de atualização difere da de um comando [select] apenas no método usado para o comando SQL: [executeQuery] para [select], [executeUpdate] para [update], [insert], [delete]:

  • linhas 4–5: desta vez há apenas dois recursos: a conexão e o comando SQL;
  • Linha 7: o comando [update], [insert], ou [delete] é executado pelo método [executeUpdate]. Se bem-sucedido, esse método retorna o número de linhas atualizadas ([update]), inseridas ([insert]) ou excluídas ([delete]);
  • Linha 9: esse número de linhas é exibido.

Recomenda-se ao leitor rever o exemplo de execução apresentado na seção “Os quatro comandos básicos da linguagem SQL”.

10.4. JDBC e independência em relação ao SGBD

10.4.1. Os drivers JDBC atuais

O código que examinamos não contém o nome de nenhuma classe do driver MySQL: ele usa apenas as interfaces de [java.sql]. Para que funcione com outro SGBD, em princípio basta adicionar ao projeto o driver desse SGBD e alterar a URL JDBC. Eis os principais drivers JDBC atuais, todos publicados no Maven Central:

SGBD
Dependência Gradle
URL JDBC
Observações
MySQL
[com.mysql:mysql-connector-j]
[jdbc:mysql://host:3306/database]
O driver oficial usado neste curso
MariaDB, MySQL
[org.mariadb.jdbc:mariadb-java-client]
[jdbc:mariadb://host:3306/database]
 
PostgreSQL
[org.postgresql:postgresql]
[jdbc:postgresql://host:5432/database]
 
SQL Server, Azure SQL
[com.microsoft.sqlserver:mssql-jdbc]
[jdbc:sqlserver://host:1433;databaseName=database]
o driver da Microsoft
Oracle
[com.oracle.database.jdbc:ojdbc11]
[jdbc:oracle:thin:@//host:1521/service]
 
H2
[com.h2database:h2]
[jdbc:h2:mem:database] ou [jdbc:h2:./file]
um SGBD escrito em Java, embutido na aplicação: banco de dados em memória ou em arquivo, sem servidor; muito usado para testes
SQLite
[org.xerial:sqlite-jdbc]
[jdbc:sqlite:file.db]
Um banco de dados em um arquivo, sem servidor

Observe que o formato da URL, após [jdbc:], é específico de cada driver.

A estrutura é a seguinte:

Image

A aplicação conhece apenas as interfaces do JDBC. Os objetos concretos são criados pelo driver, que [DriverManager] seleciona com base na URL: cada driver presente no classpath declara sua classe principal, uma implementação da interface [java.sql.Driver], cujo método [connect(url, properties)] retorna [null] se a URL não se destina a ele ([acceptsURL(url)] também permite consultá-lo). O programa [Jdbc03.kt] demonstra isso. Ele recebe como argumento a URL JDBC do banco de dados:

package jdbc03

import com.mysql.cj.jdbc.MysqlDataSource
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import java.sql.Connection
import java.sql.DriverManager
import java.sql.SQLException
import javax.sql.DataSource
import kotlin.time.measureTime

// JDBC é uma abstração: o código usa apenas as interfaces de java.sql e javax.sql
// o driver realmente usado é escolhido pela URL JDBC (1º argumento)

fun main(args: Array<String>) {
    // a URL JDBC do banco de dados
    val url = args.firstOrNull() ?: "jdbc:mysql://localhost:3306/dbarticles"

    // 1 - os drivers JDBC encontrados no classpath
    println("JDBC drivers loaded:")
    for (driver in DriverManager.drivers()) {
        println("  ${driver.javaClass.name} ${driver.majorVersion}.${driver.minorVersion}")
    }

    // 2 - DriverManager escolhe o driver que aceita a URL
    try {
        DriverManager.getConnection(url, "root", "").use { connection ->
            val metadata = connection.metaData
            println("DBMS: ${metadata.databaseProductName} ${metadata.databaseProductVersion}")
            println("Driver: ${metadata.driverName} ${metadata.driverVersion} " +
                    "(JDBC ${metadata.jdbcMajorVersion}.${metadata.jdbcMinorVersion})")
            displayArticles(connection)
        }
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
        return
    }

    // 3 - uma fonte de dados sem pool: cada getConnection abre uma conexão de rede
    val withoutPool = MysqlDataSource().apply {
        setURL(url)
        user = "root"
        password = ""
    }
    measure("MysqlDataSource (without pool)", withoutPool)

    // 4 - uma fonte de dados com um pool de conexões HikariCP
    val config = HikariConfig().apply {
        jdbcUrl = url
        username = "root"
        password = ""
        maximumPoolSize = 5
    }
    HikariDataSource(config).use { withPool ->
        measure("HikariDataSource (pool)", withPool)
    }
}

// uma consulta parametrizada executada apenas por meio das interfaces JDBC
private fun displayArticles(connection: Connection) {
    connection.prepareStatement("select id, name, price from articles where price > ?").use { statement ->
        statement.setInt(1, 200)
        statement.executeQuery().use { result ->
            println("Objects created: ${connection.javaClass.simpleName}, ${statement.javaClass.simpleName}, " +
                    result.javaClass.simpleName)
            while (result.next()) {
                println("  ${result.getInt(1)} ${result.getString(2)} ${result.getBigDecimal(3)}")
            }
        }
    }
}

// 100 aberturas / fechamentos de uma conexão obtida de uma fonte de dados
private fun measure(title: String, source: DataSource) {
    var className = ""
    try {
        val duration = measureTime {
            repeat(100) {
                source.connection.use { connection -> className = connection.javaClass.simpleName }
            }
        }
        println("$title : 100 connections $className in ${duration.inWholeMilliseconds} ms")
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    }
}

Resultados com a URL do MySQL (valor padrão):

> gradlew -q :jdbc:run -Pmain=jdbc03.Jdbc03Kt
JDBC drivers loaded:
  com.mysql.cj.jdbc.Driver 9.7
DBMS: MySQL 8.0.46-0ubuntu0.24.04.4
Driver: MySQL Connector/J mysql-connector-j-9.7.0 (Revision: 0aade1f13bcc98faf7dda5c02e782481eb291f62) (JDBC 4.2)
Objects created: ConnectionImpl, ClientPreparedStatement, ResultSetImpl
  1 bike 500.00
  3 bow 600.00
  5 diving suit 300.00
MysqlDataSource (without pool) : 100 connections ConnectionImpl in 3293 ms
HikariDataSource (pool) : 100 connections HikariProxyConnection in 40 ms

Resultados com a URL de um banco de dados PostgreSQL, cujo driver não está no classpath:

1
2
3
4
> gradlew -q :jdbc:run -Pmain=jdbc03.Jdbc03Kt --args="jdbc:postgresql://localhost:5432/dbarticles"
JDBC drivers loaded:
  com.mysql.cj.jdbc.Driver 9.7
Database access error (No suitable driver found for jdbc:postgresql://localhost:5432/dbarticles)

Aqui abordamos a primeira parte do programa (a função [main] até a linha 36, e a função [displayArticles]); a segunda parte é tratada na seção seguinte:

  • Linha 17: a URL JDBC do banco de dados, passada como argumento; caso contrário, usa-se a URL padrão do MySQL [dbarticles] do banco de dados. [firstOrNull] retorna o primeiro elemento do array de argumentos, ou [null] se ele estiver vazio; o operador Elvis [?:] então substitui [null] pela URL padrão. Com o Gradle, o argumento é passado pela opção [--args];
  • Linhas 21–22: [DriverManager.drivers] (Java 9) retorna um fluxo ([Stream]) dos drivers carregados. Um stream Java não é uma coleção Kotlin, mas possui um método [iterator()]: isso basta para que o laço Kotlin [for] o percorra. Para cada driver, [driver.javaClass.name] retorna o nome completo de sua classe, e as propriedades [majorVersion] e [minorVersion] (os métodos Java [getMajorVersion] e [getMinorVersion]) fornecem sua versão. Apenas um driver está presente no classpath: o do MySQL, da classe [com.mysql.cj.jdbc.Driver], versão 9.7 (linha 3 dos resultados);
  • linha 27: [DriverManager] procura um driver que suporte a URL. Se nenhum a suportar, [getConnection] dispara uma [SQLException] “No suitable driver found” (linha 4 do segundo conjunto de resultados). É o erro que se obtém quando se esquece a dependência do driver no [build.gradle.kts] ou se comete um erro de digitação na URL;
  • linhas 28–31: os metadados da conexão, um objeto [DatabaseMetaData], descrevem o SGBD (nome, versão), o driver e a versão do JDBC que ele implementa: JDBC 4.2, a versão do Java 8. O JDK 25 define o JDBC 4.3 (Java 9), que inclui apenas adições menores; [DatabaseMetaData] também descreve as capacidades do SGBD (transações, lotes etc.), suas tabelas e as colunas delas. Todas essas informações são obtidas por meio dos métodos [getXxx] sem parâmetros, que o Kotlin apresenta como propriedades ([databaseProductName], [driverVersion]…). A propriedade [jdbcMajorVersion] corresponde ao método Java [getJDBCMajorVersion]: como em [sqlState], as letras maiúsculas iniciais são convertidas em minúsculas;
  • linha 32: execução de uma consulta parametrizada pela função [displayArticles];
  • linhas 61–62: a consulta tem um parâmetro, indicado por [?], que recebe seu valor de [setInt(1, 200)]. As consultas parametrizadas são discutidas mais adiante;
  • linhas 64–65: os resultados mostram que os objetos manipulados por meio das interfaces são objetos do driver MySQL: [ConnectionImpl], [ClientPreparedStatement], [ResultSetImpl] (linha 6 dos resultados);
  • Linhas 66–67: como os tipos das colunas são conhecidos, são usados os métodos [getInt], [getString] e [getBigDecimal] , que retornam diretamente um valor do tipo esperado.

Para que este programa funcione com o PostgreSQL, basta adicionar a dependência [runtimeOnly("org.postgresql:postgresql:…")] ao [build.gradle.kts] e criar o banco de dados [dbarticles] no PostgreSQL: [DriverManager] encontraria então dois drivers e selecionaria o do PostgreSQL para uma URL [jdbc:postgresql:…]. Apenas o MySQL está instalado na máquina usada para escrever este curso: não fizemos isso.

10.4.2. Data sources e pools de conexões

[DriverManager] é a forma tradicional de obter uma conexão. Desde o JDBC 2.0, as aplicações preferem usar um data source, isto é, um objeto que implementa a interface [javax.sql.DataSource]. Um data source é configurado uma única vez (URL, nome de usuário, senha, configurações do driver) e depois fornece conexões por meio de seu método [getConnection()] sem parâmetros (a propriedade [connection] em Kotlin). O código que usa o data source desconhece tanto a URL quanto as credenciais: em uma aplicação, é o próprio data source que é registrado no contêiner de injeção de dependência (Capítulo 6), em vez dos parâmetros de conexão. Há duas categorias principais de data sources:

  • os fornecidos pelos drivers, como [com.mysql.cj.jdbc.MysqlDataSource] para o MySQL. Como [DriverManager], eles abrem uma nova conexão de rede a cada chamada de [getConnection];
  • os que gerenciam um pool de conexões. O mais usado é o HikariCP, o pool de conexões padrão do Spring Boot. [getConnection] retorna uma conexão do pool, e [close] devolve essa conexão ao pool em vez de fechá-la.

A segunda parte do programa, [Jdbc03], compara os dois (linhas 39–55 e a função [measure]):

  • linhas 40–43: um data source [MysqlDataSource]. A função de escopo [apply] (Capítulo 7) executa o bloco com o objeto criado como receptor ([this]) e depois retorna esse objeto: isso permite configurar o objeto logo após sua criação, sem repetir seu nome. [user = "root"] chama o método Java [setUser], e [password = ""] chama o método [setPassword]. Para a URL, é preciso chamar [setURL(url)]: a classe [MysqlDataSource] tem dois pares de acessores, [getUrl] / [setUrl] e [getURL] / [setURL], ambos os quais se tornariam uma propriedade Kotlin [url]; o compilador então se recusa a aceitar [url = …] (“overload resolution ambiguity between candidates”). Esta é a única parte do programa que faz referência a uma classe do driver MySQL: é para essa classe que o driver é declarado por [implementation] no [build.gradle.kts];
  • linha 45: a função [measure] recebe o data source com o tipo da interface [DataSource]: ela serviria para qualquer driver;
  • Linhas 48–52: um objeto [HikariConfig] contém a configuração do pool: URL JDBC, credenciais e o tamanho máximo do pool (5 conexões; o valor padrão é 10). Ele também é configurado por [apply], com suas propriedades [jdbcUrl], [username], [password], e [maximumPoolSize]. O HikariCP usa [DriverManager] para abrir suas conexões: ele funciona com qualquer driver JDBC;
  • Linha 54: o data source [HikariDataSource] é criado com base nessa configuração. Ele abre imediatamente suas primeiras conexões. Como possui conexões abertas, deve ser fechado ao final do programa: [HikariDataSource] implementa [Closeable], e [use] cuida disso;
  • linhas 74–82: a função [measure] solicita uma conexão ao data source ([repeat(100) { … }]) 100 vezes e a fecha imediatamente. A função [measureTime] de [kotlin.time] executa o bloco que recebe e retorna sua duração, um objeto [Duration], do qual [inWholeMilliseconds] obtém o número de milissegundos (em Java, você mesmo calcula a diferença entre duas chamadas a [System.nanoTime]). O bloco modifica a variável [className], declarada com [var] fora do bloco: as lambdas Kotlin podem modificar as variáveis que capturam (Capítulo 7), o que o Java proíbe. Os resultados falam por si (linhas 10–11 dos resultados iniciais): sem pool, abrir uma conexão leva várias dezenas de milissegundos para a conexão de rede e a autenticação; com o pool, a conexão é obtida em uma fração de milissegundo. As durações dependem da máquina e variam de uma execução para outra, mas a proporção permanece aproximadamente a mesma;
  • linha 79: com o HikariCP, a conexão retornada é um objeto [HikariProxyConnection], um “proxy” que envolve a conexão do driver e cujo método [close] a devolve ao pool.

Uma aplicação que atende muitos usuários usa, portanto, sempre um pool de conexões. Voltaremos a um caso de uso na seção sobre threads virtuais.

10.4.3. Que nível de abstração escolher?

A independência do JDBC em relação ao SGBD parece ideal. Na prática, o JDBC não consegue ocultar todos os detalhes específicos de um SGBD:

  • o próprio SQL difere de um SGBD para outro assim que se ultrapassam os comandos básicos: geração de chaves primárias, limitação do número de linhas retornadas ([limit 10] no MySQL e no PostgreSQL, [top 10] ou [offset … fetch] no SQL Server), tipos de coluna, funções de data etc.;
  • os tipos retornados por [getObject] podem variar: veremos que [count(*)] no MySQL retorna um [Long];
  • os códigos de erro ([errorCode]) são específicos de cada SGBD, e os SQLSTATE nem sempre são usados da mesma forma;
  • a URL e os parâmetros de configuração são específicos de cada driver.

Os parâmetros das consultas, por outro lado, são uniformes: o JDBC impõe a notação [?] (parâmetros posicionais, sem nomes), que cada driver traduz para a sintaxe de seu SGBD.

Para superar essas limitações ou simplificar a escrita do código JDBC, usa-se hoje principalmente:

  • um ORM, na maioria das vezes o Hibernate, como discutido no Capítulo 11, em que a camada [dao] manipula objetos e escreve suas consultas em HQL, uma linguagem de consulta orientada a objetos; o Hibernate gera o SQL específico de cada SGBD (seu “dialeto”);
  • bibliotecas que preservam o SQL, mas eliminam o código repetitivo do JDBC: [JdbcTemplate] e [JdbcClient] do Spring (este último desde o Spring Framework 6.1), que transformam diretamente as linhas de um [select] em objetos e convertem [SQLException] em exceções não verificadas; jOOQ, que constrói consultas SQL no código com validação em nível de compilador e oferece extensões para Kotlin; MyBatis, que associa a métodos consultas SQL escritas em arquivos XML ou em anotações;
  • Do lado do Kotlin, Exposed, a biblioteca de acesso a banco de dados da JetBrains, oferece os dois estilos: uma linguagem de consulta escrita em Kotlin ([Articles.selectAll().where { Articles.price greater 200 }]) e uma camada de objetos leve.

Todas essas ferramentas se apoiam no JDBC: entender o JDBC continua sendo útil para compreender o que elas fazem e para resolver problemas com elas. E, como veremos, bastam algumas funções de extensão para tornar o código JDBC escrito em Kotlin muito menos repetitivo.

10.5. Consultas parametrizadas

10.5.1. O conceito

Uma consulta parametrizada é um comando SQL em que certos valores são substituídos por parâmetros, cujos valores são fornecidos separadamente do texto da consulta. Com o JDBC, um parâmetro é indicado por um ponto de interrogação [?], e o comando SQL é um objeto [PreparedStatement], criado por [connection.prepareStatement(query)]. Os parâmetros são designados por sua posição na consulta, começando em 1, e recebem seus valores pelos métodos [setXxx] do comando: [setString(1, "bike")], [setInt(2, 10)]… Como esses métodos têm dois parâmetros, o Kotlin não os trata como propriedades: eles são chamados exatamente como em Java.

Os programas a seguir representam uma linha da tabela [articles] por meio de um objeto [Article]. Como essa classe é usada por vários programas, ela é armazenada, junto com funções utilitárias, no [common] pacote do projeto. O arquivo [common/Article.kt] é o seguinte:

package common

import java.math.BigDecimal
import java.sql.ResultSet

// um artigo: uma linha da tabela [articles]
data class Article(
    val id: Int,
    val name: String,
    val price: BigDecimal,
    val currentStock: Int,
    val minimumStock: Int,
)

// a linha atual de um ResultSet [id, name, price, currentstock, minimumstock] torna-se um Article
fun ResultSet.toArticle() = Article(
    id = getInt("id"),
    name = getString("name"),
    price = getBigDecimal("price"),
    currentStock = getInt("currentstock"),
    minimumStock = getInt("minimumstock"),
)
  • linhas 7–12: uma data class (Capítulo 4), o equivalente Kotlin do [record] do Java: uma classe cujo construtor primário define as propriedades, neste caso todas somente leitura ([val]), e para a qual o compilador gera os métodos [equals], [hashCode], [toString], [copy], e as funções [componentN] para desestruturação. O preço é um *[BigDecimal], assim como a coluna *[DECIMAL] correspondente. A vírgula após o último parâmetro (linha 12) é permitida pelo Kotlin: ela facilita adicionar um parâmetro e ler as diferenças entre duas versões do arquivo;
  • Linhas 16–21: uma função de extensão (Capítulo 4) da interface [ResultSet]. Ela é chamada como um método do resultado, [result.toArticle()], e converte sua linha atual em um objeto [Article]. Em seu corpo, [this] é [ResultSet]: [getInt("id")] é uma chamada a [this.getInt("id")]. As colunas são identificadas por seus nomes, assim como os argumentos do construtor (argumentos nomeados, Capítulo 3): vê-se de relance qual coluna preenche qual propriedade. A função pressupõe que o resultado contém as colunas [id], [name], [price], [currentstock] e [minimumstock]; uma consulta que não forneça essas colunas resulta em um erro [SQLSyntaxErrorException] : “Column 'xxx' not found.” O tipo de retorno não é especificado explicitamente: a função tem um corpo de expressão ([= Article(…)]), do qual o compilador infere o tipo.

A interface [ResultSet] obviamente não foi escrita para os nossos artigos: a função de extensão nos permite adicionar a ela, em nosso projeto, a operação de que precisamos, sem derivar uma classe nem escrever uma classe utilitária com métodos estáticos, como faríamos em Java (o C# oferece o mesmo recurso com seus métodos de extensão). Da mesma forma, o arquivo [common/ResultSets.kt] adiciona duas funções a [ResultSet] que leem todas as suas linhas, qualquer que seja o tipo da linha:

package common

import java.sql.ResultSet

// funções de extensão para ler as linhas de um ResultSet

// todas as linhas (restantes) do resultado, cada uma transformada por [transformation]
fun <T> ResultSet.toList(transformation: (ResultSet) -> T): List<T> {
    val list = mutableListOf<T>()
    while (next()) {
        list.add(transformation(this))
    }
    return list
}

// as mesmas linhas, lidas sob demanda: a sequência deve ser percorrida antes que o resultado seja fechado
fun <T> ResultSet.asSequence(transformation: (ResultSet) -> T): Sequence<T> =
    generateSequence { if (next()) transformation(this) else null }
  • linhas 8–13: a função genérica [toList] percorre as linhas do resultado com o laço [while (next())] e transforma cada linha por meio da função [transformation] passada como parâmetro, do tipo [(ResultSet) -> T] (Capítulo 7). Ela retorna a lista de objetos resultante. Este é o laço que normalmente se escreve em Java dentro de cada método que lê um resultado; aqui, ele é escrito uma única vez. Como o último parâmetro é uma função, a chamada é escrita com uma lambda colocada após os parênteses, que podem ser omitidos: [result.toList { it.toArticle() }] retorna uma [List<Article>];
  • linhas 17–18: a mesma coisa na forma de uma sequência (Capítulo 5), isto é, uma sequência de elementos calculados sob demanda. [generateSequence] constrói uma sequência a partir de uma função chamada cada vez que o próximo elemento é solicitado; a sequência termina quando essa função retorna [null]. Aqui, a função avança para a próxima linha do resultado e retorna sua transformação, ou [null] se não houver mais linhas. Nenhuma linha é lida até que a sequência seja percorrida: ela deve, portanto, ser percorrida antes de o resultado ser fechado, isto é, dentro do bloco do [use] que o abriu. Além disso, uma sequência só pode ser percorrida uma vez, já que o cursor do [ResultSet] não retrocede.

[toList] é apropriada quando se quer devolver as linhas ao chamador (a lista sobrevive ao fechamento do resultado); [asSequence] é apropriada quando se processam as linhas à medida que chegam, sem acumulá-las na memória.

O programa [Jdbc04.kt] implementa consultas parametrizadas: ele limpa a tabela [articles], insere cinco registros usando uma consulta parametrizada e depois os lê de volta usando outra consulta parametrizada:

package jdbc04

import common.Article
import common.toArticle
import common.toList
import java.math.BigDecimal
import java.sql.DriverManager
import java.sql.SQLException
import java.sql.Statement

// consultas parametrizadas

private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

fun main() {
    println("JDBC URL of the database: [$URL]")

    // criação de uma lista de 5 artigos
    val articles = (1..5).map { i -> Article(0, "article$i", BigDecimal(i * 100), i * 10, i) }

    try {
        // os artigos existentes são excluídos
        println("${executeUpdate("delete from articles")} row(s) deleted")
        // a lista de artigos é inserida
        insertArticles(articles)
        // os artigos da tabela são exibidos
        getArticles("%").forEach(::println)
        // os artigos cujo nome termina com 3
        println("Articles whose name ends with 3:")
        getArticles("%3").forEach(::println)
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    }
}

// inserção de uma lista de artigos com uma consulta parametrizada
private fun insertArticles(articles: List<Article>) {
    // a consulta parametrizada: cada ? é um parâmetro, numerado a partir de 1
    val query = "insert into articles(name, price, currentstock, minimumstock) values(?, ?, ?, ?)"
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS).use { statement ->
            // inserção das linhas: apenas o valor dos parâmetros muda
            for (article in articles) {
                statement.setString(1, article.name)
                statement.setBigDecimal(2, article.price)
                statement.setInt(3, article.currentStock)
                statement.setInt(4, article.minimumStock)
                statement.executeUpdate()
                // a chave primária gerada pelo SGBD
                statement.generatedKeys.use { keys ->
                    keys.next()
                    println("article [${article.name}] inserted with id ${keys.getInt(1)}")
                }
            }
        }
    }
}

// os artigos cujo nome corresponde ao padrão [like]
private fun getArticles(pattern: String): List<Article> {
    val query = """
        select id, name, price, currentstock, minimumstock from articles
        where name like ? order by id
    """.trimIndent()
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.prepareStatement(query).use { statement ->
            statement.setString(1, pattern)
            // cada linha do resultado torna-se um objeto Article
            return statement.executeQuery().use { result -> result.toList { it.toArticle() } }
        }
    }
}

// execução de uma instrução de atualização
private fun executeUpdate(query: String): Int =
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.createStatement().use { statement -> statement.executeUpdate(query) }
    }

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc04.Jdbc04Kt
JDBC URL of the database: [jdbc:mysql://localhost:3306/dbarticles]
6 row(s) deleted
article [article1] inserted with id 7
article [article2] inserted with id 8
article [article3] inserted with id 9
article [article4] inserted with id 10
article [article5] inserted with id 11
Article(id=7, name=article1, price=100.00, currentStock=10, minimumStock=1)
Article(id=8, name=article2, price=200.00, currentStock=20, minimumStock=2)
Article(id=9, name=article3, price=300.00, currentStock=30, minimumStock=3)
Article(id=10, name=article4, price=400.00, currentStock=40, minimumStock=4)
Article(id=11, name=article5, price=500.00, currentStock=50, minimumStock=5)
Articles whose name ends with 3:
Article(id=9, name=article3, price=300.00, currentStock=30, minimumStock=3)

Comentários:

  • Linhas 3–5: a classe [Article] e as funções de extensão do pacote [common] devem ser importadas: uma função de extensão só é visível onde é importada (ou dentro de seu próprio pacote);
  • Linha 21: uma lista de cinco itens, gerada por [map] a partir do intervalo [1..5] (Capítulo 5). [id] 0 é irrelevante: ele não será inserido no banco de dados. [BigDecimal(i * 100)] chama o construtor de [BigDecimal], que recebe um inteiro;
  • linha 25: todos os registros são excluídos da tabela. A função [executeUpdate] (linha 77) retorna o número de linhas excluídas. A chamada é colocada diretamente no template da string exibida, entre [${ }];
  • Linha 29: [forEach(::println)] exibe cada item da lista usando uma referência de função (Capítulo 7). Os resultados mostram o método [toString] gerado para a data class, na forma [Article(id=7, name=article1, …)] (um [record] Java exibe [Article[id=7, …]]);
  • linha 39: a função para inserir uma lista de itens:
    • linha 41: a consulta para inserir um item. Ela tem quatro parâmetros, indicados por [?]: o nome (parâmetro nº 1), o preço (nº 2), o estoque atual (nº 3) e o estoque mínimo (nº 4);
    • linha 43: [prepareStatement] cria o comando configurado. O segundo argumento, [Statement.RETURN_GENERATED_KEYS], instrui o driver a recuperar as chaves primárias geradas pelo SGBD durante as inserções;
    • Linhas 45–50: os itens são inseridos um a um. Cada um dos quatro parâmetros recebe um valor pelo método [setXxx] correspondente ao seu tipo, e então a consulta, agora completa, é executada por [executeUpdate], desta vez sem nenhum argumento: o texto da consulta foi especificado quando o comando foi criado. Os valores dos parâmetros são mantidos de uma execução para a seguinte até serem alterados (ou até que [clearParameters] seja chamado);
    • linhas 52–54: a propriedade [generatedKeys] (o método Java [getGeneratedKeys]) retorna, na forma de um [ResultSet], a chave primária gerada pelo SGBD para a linha inserida. Esse resultado também é um recurso, fechado por [use]. Os resultados mostram [id] de 7 a 11: o MySQL não reutiliza os valores 1 a 6 das linhas excluídas. É assim que se determina o identificador de uma linha que acabou de ser inserida;
  • linha 62: a função que retorna os itens cujos nomes correspondem a um padrão [like] do SQL (o caractere [%] representa qualquer sequência de caracteres: [%3] refere-se a nomes terminados em 3):
    • linhas 63–66: a consulta tem um parâmetro, o modelo. Ela é escrita como uma raw string (Capítulo 5), delimitada por três aspas, que pode se estender por várias linhas; [trimIndent] remove a indentação comum das linhas, bem como a primeira e a última linhas vazias. As consultas longas são assim lidas como seriam em um cliente SQL, sem concatenação. A quebra de linha preservada entre as duas linhas da consulta serve, para o SQL, como um separador como qualquer outro;
    • linha 69: o parâmetro recebe seu valor;
    • linha 71: o comando é executado, e cada linha do resultado se torna um objeto [Article] graças às duas funções de extensão: [result.toList { it.toArticle() }]. Essas poucas linhas substituem a lista modificável, o laço [while] e a chamada ao construtor da versão Java. O [use] mais interno retorna a lista (o valor de seu bloco), e a instrução [return], colocada dentro de lambdas de funções [inline], encerra a função [getArticles]: as três instruções [use] fecham então, em ordem, o resultado, o comando SQL e a conexão. A lista retornada, por sua vez, não depende mais de nenhum recurso JDBC;
  • Linhas 77–79: a função [executeUpdate] tem um corpo de expressão: seu valor é o do primeiro [use], que é o do segundo, que é o número de linhas retornado por [executeUpdate]. Isso ilustra a vantagem de um [use] que retorna um valor.

As colunas são lidas pelo nome na função [toArticle]. Os métodos [getXxx] também aceitam o número da coluna: [getInt(1)], [getString(2)]… O acesso por nome é mais legível e lida melhor com mudanças na consulta; o acesso por número é ligeiramente mais rápido.

O nome [PreparedStatement] (comando “preparado”) vem do fato de que um SGBD pode “preparar” uma consulta parametrizada, isto é, compilá-la uma única vez. Quando um SGBD executa um comando SQL, ele faz a análise e a otimização antes de executá-lo (estabelece um “plano de execução”). Uma consulta parametrizada se destina a ser executada várias vezes com parâmetros diferentes, mas seu texto não muda: esse trabalho só precisa ser feito uma vez. Com o MySQL Connector/J, a preparação é simulada pelo driver por padrão: ele insere os valores dos parâmetros, devidamente escapados, no próprio texto da consulta antes de enviá-la. O parâmetro [useServerPrepStmts=true] da URL faz com que ele use a preparação fornecida pelo servidor MySQL. Em ambos os casos, o código Kotlin é o mesmo.

As principais correspondências entre os tipos SQL do MySQL, os métodos do JDBC e os tipos Kotlin são as seguintes:

Tipo de coluna do MySQL
Métodos JDBC
Tipo Kotlin
[INT]
[setInt], [getInt]
[Int] ([java.lang.Integer] para [getObject])
[BIGINT]
[setLong], [getLong]
[Long]
[DECIMAL(p,d)]
[setBigDecimal], [getBigDecimal]
[BigDecimal]
[DOUBLE]
[setDouble], [getDouble]
[Double]
[VARCHAR(n)], string de comprimento variável
[setString], [getString]
[String] ([String?] se a coluna aceitar [NULL])
[DATETIME]
[setObject(i, value)], [getObject(i, LocalDateTime::class.java)]
[LocalDateTime]
[DATE]
[setObject(i, value)], [getObject(i, LocalDate::class.java)]
[LocalDate]
[BOOL] (sinônimo de [TINYINT(1)])
[setBoolean], [getBoolean]
[Boolean]

As datas são representadas pelas classes de [java.time] (Capítulo 5), que o JDBC suporta desde a versão 4.2 (Java 8) por meio de [setObject] e [getObject]. As classes mais antigas [java.sql.Date], [java.sql.Time] e [java.sql.Timestamp], derivadas de [java.util.Date], e os métodos [getDate], [getTimestamp] que as implementam, não devem mais ser usados em código novo. Para atribuir o valor SQL ou [NULL] a um parâmetro, escreve-se [setNull(i, Types.INTEGER)], em que a classe *[java.sql.Types]* (ou o enum *[JDBCType]*, Java 8) especifica o tipo SQL do parâmetro. Para ler uma coluna inteira que aceita [NULL], pode-se escrever [result.getInt(i).takeUnless { result.wasNull() }] em Kotlin, que retorna um [Int?], o valor lido, ou [null] se a coluna não tinha valor.

10.5.2. A injeção de SQL

A preparação não é a única vantagem das consultas parametrizadas, nem a mais importante. Considere a busca de um artigo por seu nome. Os templates de string do Kotlin tornam muito tentador construir o texto da consulta programaticamente, inserindo o termo de busca:

    val query = "select id, name, price from articles where name = '$name'"

(esta é uma das linhas do programa [Jdbc05] abaixo). Se o nome for “bike”, a consulta se torna:

select id, name, price from articles where name = 'bike'

Mas se o nome for “diver's oxygen tanks”, ela se torna:

select id, name, price from articles where name = 'diver's oxygen tanks'

e é sintaticamente incorreta por causa do apóstrofo no nome. Fica pior: se o nome vier de uma entrada do usuário, ele poderia digitar um texto que altera o sentido da consulta. Isso é conhecido como injeção de SQL, uma das vulnerabilidades de segurança mais comuns e graves em aplicações baseadas em banco de dados. O template ["… '$name'"] nada mais é que uma concatenação escrita de forma mais legível: ele tem exatamente a mesma falha que o ["… '" + name + "'"] do Java, e sua própria legibilidade o torna ainda mais perigoso, já que o código parece limpo. O programa [Jdbc05.kt] evidencia a vulnerabilidade na busca de um artigo por nome:

package jdbc05

import common.asSequence
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.SQLException

// injeção de SQL: por que consultas parametrizadas devem ser usadas

private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

fun main() {
    // nomes de artigos, como um usuário poderia digitá-los
    val names = listOf("bike", "diver's oxygen tanks", "x' or '1'='1")

    println("1 - query built with a string template")
    names.forEach(::searchWithTemplate)
    println("\n2 - parameterized query")
    names.forEach(::searchWithParameter)
}

// busca de um artigo pelo nome: o nome é inserido no texto da consulta (NÃO FAÇA ISSO)
private fun searchWithTemplate(name: String) {
    val query = "select id, name, price from articles where name = '$name'"
    println("[$name] -> $query")
    try {
        DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
            connection.createStatement().use { statement ->
                statement.executeQuery(query).use(::display)
            }
        }
    } catch (ex: SQLException) {
        println("   error: ${ex.message}")
    }
}

// busca de um artigo pelo nome: o nome é o valor de um parâmetro
private fun searchWithParameter(name: String) {
    println("[$name]")
    try {
        DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
            connection.prepareStatement("select id, name, price from articles where name = ?").use { statement ->
                statement.setString(1, name)
                statement.executeQuery().use(::display)
            }
        }
    } catch (ex: SQLException) {
        println("   error: ${ex.message}")
    }
}

// exibição do resultado de um select: cada linha é exibida assim que é lida, e depois contada
private fun display(result: ResultSet) {
    val nbRows = result.asSequence { "${it.getInt(1)} ${it.getString(2)} ${it.getBigDecimal(3)}" }
        .onEach { println("   $it") }
        .count()
    println("   $nbRows row(s)")
}

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc05.Jdbc05Kt
1 - query built with a string template
[bike] -> select id, name, price from articles where name = 'bike'
   1 bike 500.00
   1 row(s)
[diver's oxygen tanks] -> select id, name, price from articles where name = 'diver's oxygen tanks'
   error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's oxygen tanks'' at line 1
[x' or '1'='1] -> select id, name, price from articles where name = 'x' or '1'='1'
   1 bike 500.00
   2 pump 10.00
   3 bow 600.00
   4 arrows - set of 6 100.00
   5 diving suit 300.00
   6 diver's oxygen tanks 120.00
   6 row(s)

2 - parameterized query
[bike]
   1 bike 500.00
   1 row(s)
[diver's oxygen tanks]
   6 diver's oxygen tanks 120.00
   1 row(s)
[x' or '1'='1]
   0 row(s)

Comentários:

  • linha 16: três nomes que um usuário poderia digitar: um nome comum, um nome contendo um apóstrofo e um texto malicioso;
  • Linhas 19 e 21: cada nome é passado sucessivamente às duas funções de busca, designadas por referências de função;
  • linha 25: a primeira função de busca insere o nome no texto da consulta por meio de um template de string (linha 26), executado por um [Statement] comum:
    • linha 3 dos resultados: com um nome comum, tudo funciona bem;
    • linha 6: o apóstrofo no nome encerra prematuramente a string SQL; a consulta é incorreta (linha 7). Um artigo cujo nome contém um apóstrofo não pode ser encontrado;
    • linha 8: o usuário digitou um texto com apóstrofos posicionados de modo a produzir uma consulta válida, mas para a qual a condição [name = 'x' or '1'='1'] é verdadeira para todas as linhas: a consulta retorna a tabela inteira (linha 15). Se a consulta estivesse verificando um nome de usuário e uma senha, o mesmo procedimento permitiria ao usuário entrar sem conhecer a senha. Com um driver que aceita vários comandos SQL em uma única consulta, o usuário poderia até acrescentar um comando de sua escolha após um ponto e vírgula, como um [delete]. O Connector/J rejeita isso por padrão (parâmetro [allowMultiQueries=false]), o que limita o alcance da injeção sem eliminá-la;
  • linha 31: o resultado é passado à função [display] por meio de uma referência de função: [use(::display)] equivale a [use { display(it) }];
  • linha 40: a segunda função usa uma consulta parametrizada (linhas 44–45). O valor do parâmetro nunca é interpretado como SQL: ele é tratado como dado, qualquer que seja seu conteúdo. O nome com apóstrofo é encontrado (linha 22 dos resultados), e o texto malicioso é simplesmente o nome de um artigo que não existe (linha 25);
  • linhas 55–59: a exibição do resultado usa a função de extensão [asSequence] do pacote [common]. A sequência converte cada linha em uma string; [onEach] exibe cada string à medida que avança e retorna a mesma sequência, que [count] percorre até o fim para contar seus elementos. Como uma sequência é preguiçosa, é [count] que dispara a leitura das linhas: cada uma é lida, exibida e contada antes que a seguinte seja lida, sem lista intermediária. A travessia ocorre durante a chamada a [display], isto é, dentro do bloco do [use] que fecha o resultado, como deve ser.

Seria possível “neutralizar” os apóstrofos duplicando-os, mas as regras dependem do SGBD, e é fácil esquecer um caso. A regra é simples: os valores fornecidos pelo usuário (ou por qualquer fonte externa) nunca devem ser inseridos no texto de uma consulta SQL, nem por concatenação nem por um template de string; usamos sempre um PreparedStatement e parâmetros. Os templates continuam úteis para as partes fixas de uma consulta escolhidas pelo próprio programa (um nome de tabela ou de coluna tirado de uma lista conhecida, por exemplo), mas nunca para valores. O programa interativo [Jdbc01.kt] não segue essa regra por sua própria natureza: ele executa os comandos SQL digitados pelo usuário. É uma ferramenta de administrador, não um exemplo a seguir.

10.6. Transações

10.6.1. Generalidades

Uma transação é uma sequência de comandos SQL executados “atomicamente”:

  • ou todas as operações são bem-sucedidas;
  • ou uma delas falha, caso em que todas as operações anteriores são desfeitas.

Em última análise, ou todas as operações de uma transação são aplicadas com sucesso, ou nenhuma é. O exemplo clássico é uma transferência bancária: o débito de uma conta e o crédito em outra devem ser ambos concluídos, ou nenhum dos dois. Quando a própria aplicação controla a transação, ela a confirma com um comando [COMMIT] ou a desfaz com um comando [ROLLBACK].

Em nossos exemplos anteriores, não usamos uma transação. E, no entanto, havia transações, porque em um SGBD um comando SQL é sempre executado dentro de uma transação. Se a aplicação não inicia ela mesma uma transação explícita, o SGBD usa uma transação implícita. Há então dois casos:

  • cada comando SQL faz parte de uma transação, aberta pelo SGBD antes do comando e confirmada depois dele. Fala-se então em modo autocommit. Isso se comporta como se a aplicação iniciasse uma transação para cada comando SQL;
  • o SGBD não está em modo autocommit: ele inicia uma transação implícita com o primeiro comando SQL emitido fora de uma transação e deixa que a aplicação a conclua. Todos os comandos SQL seguintes fazem parte dessa transação implícita, que pode terminar por diversos eventos (fechamento da conexão, início de uma nova transação etc.), segundo regras específicas de cada SGBD. Esse modo deve ser evitado.

O JDBC impõe o primeiro caso: uma conexão JDBC está em modo autocommit quando é aberta, independentemente da configuração do SGBD (o MySQL, com o mecanismo de armazenamento padrão InnoDB, também está em modo autocommit).

Os comandos de usuários diferentes são executados simultaneamente, em transações que rodam em paralelo. As operações realizadas por uma transação podem afetar as realizadas por outra. Existem quatro níveis de isolamento entre transações de usuários diferentes:

  • Read Uncommitted;
  • Read Committed;
  • Repeatable Read;
  • Serializable.

Read Uncommitted

Esse nível de isolamento também é conhecido como “Dirty Read”. Eis um exemplo do que pode acontecer nesse modo:

  • um usuário chamado U1 inicia uma transação na tabela T;
  • um usuário chamado U2 inicia uma transação na mesma tabela T;
  • o usuário U1 modifica linhas da tabela T, mas ainda não confirmou as alterações;
  • o usuário U2 “vê” essas alterações e toma decisões com base no que vê;
  • o usuário U1 cancela sua transação com um [ROLLBACK].

Na etapa 4, o usuário U2 tomou uma decisão com base em dados que depois se revelaram incorretos.

Read Committed

Esse nível de isolamento evita a armadilha anterior: na etapa 4, o usuário U2 não “vê” as alterações feitas pelo usuário U1 na tabela T. Ele só as verá depois que U1 confirmar sua transação com um [COMMIT]. Nesse modo, porém, pode ocorrer a seguinte situação:

  • um usuário U1 inicia uma transação na tabela T;
  • um usuário U2 inicia uma transação na mesma tabela T;
  • o usuário U2 executa um [select] para calcular a média da coluna C das linhas de T que atendem a uma certa condição;
  • o usuário U1 modifica ([update]) certos valores da coluna C de T e confirma as alterações ([commit]);
  • o usuário U2 repete o mesmo [select] da etapa 3. Ele descobre que a média da coluna C mudou devido às modificações feitas por U1.

O usuário U2 agora vê apenas as alterações confirmadas por U1, mas, ainda dentro da mesma transação, duas operações idênticas produzem resultados diferentes. Essa situação é chamada de “leitura não repetível”. Ela é problemática para quem deseja uma visão consistente da tabela T.

Repeatable Read

Nesse nível de isolamento, o usuário tem a garantia de obter os mesmos resultados nas leituras do banco de dados enquanto permanecer na mesma transação. Ele trabalha sobre um “instantâneo” (snapshot) dos dados que não reflete as alterações feitas por outras transações, mesmo que elas tenham sido confirmadas. Ele só verá essas alterações depois de concluir a própria transação.

Esse nível de isolamento ainda não é perfeito em teoria: outra transação pode adicionar ([insert]) linhas que satisfazem a condição da etapa 3, o que alteraria o resultado de uma leitura posterior. Essas linhas são chamadas de “leituras fantasmas”. Este é o nível de isolamento padrão do MySQL (InnoDB), que também evita leituras fantasmas nas leituras simples.

Serializable

Nesse modo de isolamento, as transações são completamente isoladas umas das outras: o resultado de duas transações executadas simultaneamente é o mesmo que se elas tivessem sido executadas uma após a outra. Para isso, o SGBD bloqueia os dados lidos e faz as transações que tentam modificá-los esperarem (ou falharem). Esse nível reduz o paralelismo e, consequentemente, o desempenho.

Os quatro níveis de isolamento não estão disponíveis em todos os SGBDs. O nível de isolamento padrão geralmente é Read Committed (SQL Server, PostgreSQL, Oracle) ou Repeatable Read (MySQL). O nível de isolamento desejado pode ser especificado explicitamente pela aplicação.

10.6.2. Gerenciamento de transações na API

Com o JDBC, as transações são gerenciadas pela conexão. Não existe um objeto “transação”: uma conexão tem no máximo uma transação ativa, e todos os comandos SQL executados nessa conexão fazem parte dela. Os métodos relevantes da interface [Connection] são os seguintes. O Kotlin trata os pares getter-setter Java [getXxx] / [setXxx] como propriedades (Capítulo 4): [connection.setAutoCommit(false)] é escrito [connection.autoCommit = false], e [connection.getAutoCommit()] é escrito [connection.autoCommit]. A coluna da esquerda mostra a forma Kotlin:

Método ou propriedade
Função
[autoCommit]
[autoCommit = false] ([setAutoCommit(false)] em Java) sai do modo autocommit: os comandos SQL seguintes fazem parte de uma transação, que começa com o primeiro deles e termina com [commit] ou [rollback]. Uma nova transação começa então com o comando seguinte. [autoCommit = true] volta ao modo autocommit, confirmando a transação atual.
[commit()]
confirma a transação: os resultados dos comandos SQL emitidos dentro da transação tornam-se definitivos e visíveis para as outras transações.
[rollback()]
desfaz a transação: os resultados dos comandos SQL emitidos na transação são anulados.
[setSavepoint()]
define um ponto de salvamento (um objeto [Savepoint]) na transação atual. Não é um acessor (não tem [getSavepoint] associado): o Kotlin o chama como uma função.
[rollback(Savepoint)]
desfaz os comandos SQL executados desde o ponto de salvamento, sem desfazer os que o precederam: a transação continua.
[transactionIsolation]
o nível de isolamento das transações da conexão ([setTransactionIsolation] em Java). Deve ser definido antes do início de uma transação.

O nível de isolamento é uma das constantes da interface [Connection]:

Valor
Significado
[TRANSACTION_READ_UNCOMMITTED]
Uma transação pode ler dados escritos por outra transação que ainda não os confirmou. Isso deve ser evitado.
[TRANSACTION_READ_COMMITTED]
A transação não pode ler dados escritos por outra transação que ainda não os confirmou. No entanto, dados lidos duas vezes seguidas dentro da transação podem mudar, porque outra transação pode tê-los modificado nesse intervalo; outra transação também pode ter adicionado linhas (linhas fantasmas).
[TRANSACTION_REPEATABLE_READ]
Os dados lidos pela transação não mudam até que ela termine. Em teoria, isso não impede que outra transação adicione linhas.
[TRANSACTION_SERIALIZABLE]
Tudo se comporta como se a transação estivesse rodando sozinha. Isso reduz o desempenho, porque as transações deixam de rodar em paralelo.

São constantes [static] de uma interface Java: o Kotlin as referencia pelo nome da interface, [Connection.TRANSACTION_READ_COMMITTED], assim como os membros de um [companion object].

Uma conexão que saiu do modo autocommit deve ser fechada depois que sua transação tiver sido confirmada ou desfeita. Se for fechada enquanto uma transação ainda está em andamento, o resultado depende do driver (o Connector/J a desfaz): é preciso sempre confirmar explicitamente uma transação.

10.6.3. O programa de exemplo

O programa [Jdbc06.kt] demonstra o efeito de uma transação:

package jdbc06

import common.Article
import common.transaction
import java.math.BigDecimal
import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.SQLException

// transações

private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

fun main() {
    println("JDBC URL of the database: [$URL]")

    // dois artigos com o mesmo nome: a restrição de unicidade UNQ_NAME rejeitará o segundo
    val articles = listOf(
        Article(0, "article", BigDecimal("100"), 10, 1),
        Article(0, "article", BigDecimal("200"), 20, 2),
    )

    try {
        // 1 - inserção sem transação explícita
        println("\nInsertion without a transaction...")
        executeUpdate("delete from articles")
        try {
            insertArticlesOutOfTransaction(articles)
        } catch (ex: SQLException) {
            println("Database access error (${ex.message})")
        }
        displayArticles()

        // 2 - a mesma coisa em uma transação
        println("\nInsertion in a transaction...")
        executeUpdate("delete from articles")
        insertArticlesInTransaction(articles)
        displayArticles()

        // 3 - uma transação com um savepoint
        println("\nInsertion in a transaction with a savepoint...")
        executeUpdate("delete from articles")
        insertArticlesWithSavepoint(articles)
        displayArticles()

        // 4 - uma transação gerenciada pela função de ordem superior [transaction]
        println("\nInsertion in a transaction managed by the [transaction] function...")
        executeUpdate("delete from articles")
        try {
            insertArticlesWithFunction(articles)
        } catch (ex: SQLException) {
            println("transaction rolled back (${ex.message})")
        }
        insertArticlesWithFunction(articles.take(1))
        displayArticles()
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    }
}

// inserção de uma lista de artigos sem transação explícita (modo autocommit)
private fun insertArticlesOutOfTransaction(articles: List<Article>) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        createInsertStatement(connection).use { statement ->
            articles.forEach { insertArticle(statement, it) }
        }
    }
}

// inserção de uma lista de artigos em uma transação
private fun insertArticlesInTransaction(articles: List<Article>) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        // início da transação: o modo autocommit é abandonado
        connection.transactionIsolation = Connection.TRANSACTION_READ_COMMITTED
        connection.autoCommit = false
        try {
            createInsertStatement(connection).use { statement ->
                articles.forEach { insertArticle(statement, it) }
            }
            // tudo correu bem: a transação é confirmada (commit)
            connection.commit()
            println("transaction committed...")
        } catch (ex: SQLException) {
            // uma inserção falhou: a transação é desfeita
            connection.rollback()
            println("transaction rolled back (${ex.message})")
        }
    }
}

// inserção em uma transação: a falha de uma inserção só cancela o que vem depois
// do último savepoint
private fun insertArticlesWithSavepoint(articles: List<Article>) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.autoCommit = false
        try {
            createInsertStatement(connection).use { statement ->
                for (article in articles) {
                    // um savepoint antes de cada inserção
                    val beforeInsertion = connection.setSavepoint()
                    try {
                        insertArticle(statement, article)
                    } catch (ex: SQLException) {
                        // apenas a inserção que falhou é desfeita
                        connection.rollback(beforeInsertion)
                        println("insertion of [${article.name}, ${article.price}] cancelled (${ex.message})")
                    }
                }
            }
            connection.commit()
            println("transaction committed...")
        } catch (ex: SQLException) {
            connection.rollback()
            println("transaction rolled back (${ex.message})")
        }
    }
}

// inserção em uma transação gerenciada pela função [transaction]: as exceções são propagadas
private fun insertArticlesWithFunction(articles: List<Article>) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        val nbArticles = connection.transaction(Connection.TRANSACTION_READ_COMMITTED) {
            // no bloco, [this] é a conexão
            createInsertStatement(this).use { statement ->
                articles.forEach { insertArticle(statement, it) }
            }
            articles.size
        }
        println("transaction committed: $nbArticles article(s) inserted")
    }
}

// a instrução parametrizada que insere um artigo
private fun createInsertStatement(connection: Connection): PreparedStatement = connection.prepareStatement(
    "insert into articles(name, price, currentstock, minimumstock) values(?, ?, ?, ?)"
)

// inserção de um artigo com a instrução parametrizada
private fun insertArticle(statement: PreparedStatement, article: Article) {
    with(article) {
        statement.setString(1, name)
        statement.setBigDecimal(2, price)
        statement.setInt(3, currentStock)
        statement.setInt(4, minimumStock)
        statement.executeUpdate()
        println("article [$name, $price] inserted")
    }
}

// execução de uma instrução de atualização
private fun executeUpdate(query: String) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.createStatement().use { statement ->
            println("${statement.executeUpdate(query)} row(s) modified")
        }
    }
}

// exibição do conteúdo da tabela
private fun displayArticles() {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.createStatement().use { statement ->
            val query = "select id, name, price, currentstock, minimumstock from articles"
            statement.executeQuery(query).use { result ->
                println("Content of the [articles] table:")
                while (result.next()) {
                    // no bloco with, [this] é o resultado: getInt(1) é result.getInt(1)
                    val row = with(result) {
                        "${getInt(1)} ${getString(2)} ${getBigDecimal(3)} ${getInt(4)} ${getInt(5)}"
                    }
                    println("  $row")
                }
            }
        }
    }
}

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc06.Jdbc06Kt
JDBC URL of the database: [jdbc:mysql://localhost:3306/dbarticles]

Insertion without a transaction...
6 row(s) modified
article [article, 100] inserted
Database access error (Duplicate entry 'article' for key 'articles.UNQ_NAME')
Content of the [articles] table:
  7 article 100.00 10 1

Insertion in a transaction...
1 row(s) modified
article [article, 100] inserted
transaction rolled back (Duplicate entry 'article' for key 'articles.UNQ_NAME')
Content of the [articles] table:

Insertion in a transaction with a savepoint...
0 row(s) modified
article [article, 100] inserted
insertion of [article, 200] cancelled (Duplicate entry 'article' for key 'articles.UNQ_NAME')
transaction committed...
Content of the [articles] table:
  11 article 100.00 10 1

Insertion in a transaction managed by the [transaction] function...
1 row(s) modified
article [article, 100] inserted
transaction rolled back (Duplicate entry 'article' for key 'articles.UNQ_NAME')
article [article, 100] inserted
transaction committed: 1 article(s) inserted
Content of the [articles] table:
  15 article 100.00 10 1

Comentários:

  • Linhas 21–23: uma lista de dois itens com o mesmo nome, [article] ([Article] é a data class do pacote [common]). A restrição de unicidade [UNQ_NAME] na tabela [articles] impede que ambos existam no banco de dados ao mesmo tempo: a inserção do segundo disparará uma [SQLException]. Para demonstrar o efeito de uma transação, os dois itens serão inseridos de quatro maneiras diferentes:
    • primeiro, fora de qualquer transação explícita. Como a conexão está em modo autocommit, cada inserção é executada em sua própria transação. O primeiro registro será inserido, mas o segundo não;
    • depois, dentro de uma transação que engloba as duas inserções. Como a segunda inserção falhará, a primeira será desfeita: no final, nenhuma das inserções será efetivada;
    • em seguida, dentro de uma transação com um ponto de salvamento antes de cada inserção: a falha da segunda inserção desfará apenas a si mesma, e a transação será confirmada com a primeira;
    • por fim, em uma transação gerenciada por uma função de ordem superior, [transaction], que apresentamos no parágrafo seguinte;
  • linhas 27–35: a tabela é esvaziada, e depois os dois itens são inseridos sem transação explícita. Como sabemos que a segunda inserção lançará uma exceção, ela é capturada por um [try] / [catch]; por fim, a tabela é exibida;
  • linhas 37–40: repetimos a mesma sequência, mas com uma transação. Desta vez, a exceção é tratada na função [insertArticlesInTransaction];
  • linhas 43–46: o mesmo vale para os pontos de salvamento;
  • linha 65: a função de inserção sem transação. Ela usa duas funções: [createInsertStatement] (linha 137), que cria o comando de inserção parametrizado, e [insertArticle] (linha 142), que atribui valores aos parâmetros e executa o comando. As duas funções [use] aninhadas fecham o comando e depois a conexão, exatamente como fazia o try-with-resources do Java;
  • linha 74: a função de inserção dentro de uma transação:
    • linha 77: o nível de isolamento [TRANSACTION_READ_COMMITTED], definido antes do início da transação por meio de uma atribuição de propriedade;
    • linha 78: a conexão sai do modo autocommit; a transação começará com a primeira inserção;
    • linhas 80–81: as inserções;
    • linha 84: tudo correu bem: a transação é confirmada, e as inserções são adicionadas definitivamente ao banco de dados;
    • linhas 86–89: uma inserção falhou: a transação é desfeita;
  • linha 96: a função de inserção com pontos de salvamento:
    • linha 103: um ponto de salvamento é definido antes de cada inserção. [setSavepoint] não é um acessor de propriedade (não tem parâmetros): ele é chamado como uma função;
    • linha 108: se a inserção falhar, voltamos ao ponto de salvamento: somente a inserção que falhou é desfeita, e o laço continua;
    • linha 113: a transação é confirmada, com as inserções bem-sucedidas;
  • linhas 142–149: a função [with] da biblioteca padrão executa seu bloco com o item como receptor: dentro do bloco, [name] refere-se a [article.name]. A função [displayArticles] a utiliza da mesma forma com o resultado da consulta (linha 171), o que evita repetir [result.] cinco vezes;
  • Linha 154: como todas as funções do programa, [executeUpdate] não declara nenhuma exceção: o Kotlin não tem exceções verificadas (Capítulo 3), e a cláusula [throws SQLException] que o Java exigia em cada método não é mais necessária. A [SQLException] sobe até o [try] da função [main] (linha 59).

Nos resultados:

  • linha 5: exibida pelo comando [delete]: havia seis linhas na tabela;
  • linha 7: a exceção causada pela segunda inserção. A mensagem indica que a restrição [UNQ_NAME] na tabela [articles] não foi satisfeita;
  • linha 9: a tabela não está vazia: o primeiro registro foi inserido;
  • linha 12: exibida pelo comando [delete]: havia uma linha na tabela;
  • Linha 13: a primeira inserção foi bem-sucedida… dentro da transação;
  • linha 14: a mensagem exibida quando a transação falha;
  • linha 15: a tabela está vazia: o [rollback] da transação desfez a primeira inserção;
  • linhas 19–21: graças aos pontos de salvamento, a segunda inserção foi desfeita e a transação confirmada;
  • linha 23: a tabela contém o primeiro item. Seu valor de [id] é 11, e não 8: cada tentativa de inserção, mesmo desfeita ou rejeitada, consumiu um valor da coluna [AUTO_INCREMENT], e o MySQL não reutiliza esses valores.

Com o MySQL, a falha de um comando SQL cancela apenas esse comando, e a transação pode continuar: o ponto de salvamento, portanto, não é essencial aqui. Isso não vale para todos os SGBDs: com o PostgreSQL, qualquer erro, por menor que seja, torna a transação inutilizável até que ela seja desfeita ou até que se volte a um ponto de salvamento. O código com pontos de salvamento funciona com ambos.

10.6.4. Uma função de ordem superior: transaction { }

As funções [insertArticlesInTransaction] e [insertArticlesWithSavepoint] seguem o mesmo padrão: sair do modo autocommit, executar o trabalho, confirmar e desfazer em caso de exceção. Esse padrão é sempre o mesmo; só a tarefa muda: este é o caso de uso típico de uma função de ordem superior (Capítulo 7), que recebe a tarefa na forma de uma lambda, exatamente como [use] recebe a tarefa a executar antes de fechar um recurso. Vamos escrevê-la uma única vez, como uma função de extensão de [Connection], no arquivo [Transactions.kt] dentro do pacote [common]:

package common

import java.sql.Connection
import java.sql.SQLException

// executa [block] em uma transação da conexão:
// - confirmada (commit) se o bloco terminar normalmente;
// - desfeita (rollback) se ele lançar uma exceção, que é então relançada;
// o modo autocommit da conexão é restaurado no final
// [crossinline]: o bloco não pode conter um return que sairia da função chamadora
inline fun <T> Connection.transaction(isolation: Int? = null, crossinline block: Connection.() -> T): T {
    // o possível nível de isolamento é definido antes do início da transação
    isolation?.let { transactionIsolation = it }
    val initialAutoCommit = autoCommit
    autoCommit = false
    try {
        val result = block()
        commit()
        return result
    } catch (e: Throwable) {
        try {
            rollback()
        } catch (ex: SQLException) {
            // a falha do rollback não deve esconder a exceção original
            e.addSuppressed(ex)
        }
        throw e
    } finally {
        autoCommit = initialAutoCommit
    }
}
  • Linha 11: a função [transaction] é uma função de extensão da interface [Connection]: ela é chamada como um método da conexão, [connection.transaction { … }]. Ela tem dois parâmetros:
    • [isolation], o nível de isolamento, do tipo [Int?] e com valor padrão [null] (caso em que o nível da conexão permanece inalterado);
    • [block], a tarefa a executar na transação. Seu tipo, [Connection.() -> T], é o de uma lambda com receptor: dentro do bloco, [this] é a conexão, e [prepareStatement(…)] pode ser escrito no lugar de [connection.prepareStatement(…)]. Esse é o mecanismo usado por [with], [apply], e [buildList] na biblioteca padrão. O bloco retorna um valor do tipo [T], que [transaction] por sua vez retorna. Como o bloco é o último parâmetro, ele é escrito após os parênteses na chamada, ou sem parênteses algum se for o único argumento;
  • [inline]: o código da função e o código do bloco são copiados para o local da chamada (Capítulo 7): nenhum objeto lambda é criado a cada transação. No entanto, uma lambda passada a uma função [inline] pode conter um [return] que encerra a função chamadora (um return “não local”, como no corpo de um laço). Aqui, isso seria uma armadilha: um *[return] no meio do bloco sairia do *[try] sem passar nem por [commit] nem por *[rollback]*, e a transação ficaria pendente. O modificador [crossinline] no parâmetro [block] impede isso. O compilador rejeita, portanto, um bloco como [connection.transaction { if (name.isEmpty()) return … }]:
1
2
3
Trial.kt:8:29: error: 'return' is prohibited here.
        if (name.isEmpty()) return
                            ^^^^^^

O bloco, portanto, sempre termina com o valor que retorna (um [return@transaction value] continua permitido: ele sai apenas da lambda);

  • linha 13: se um nível de isolamento for especificado, ele é definido antes do início da transação;
  • linhas 14–15: o modo da conexão é salvo, e depois sai-se do modo autocommit;
  • linhas 17–19: o bloco é executado, depois a transação é confirmada e o resultado do bloco é retornado;
  • linhas 20–27: se o bloco lançar qualquer exceção, a transação é desfeita e a exceção é relançada: o chamador a recebe como se a função [transaction] não existisse. Se o próprio [rollback] falhar (conexão perdida), sua exceção não deve mascarar a original, mais relevante: ela é anexada à original como uma exceção suprimida ([addSuppressed]), de modo semelhante ao que o try-with-resources do Java ou a função *[use] do Kotlin fazem com as exceções de [close]*;
  • Linhas 28–29: o modo autocommit é restaurado em todos os casos. Isso é útil com um pool de conexões: a conexão devolvida ao pool volta ao seu estado inicial.

O programa [Jdbc06] usa essa função em sua quarta seção:

        // 4 - uma transação gerenciada pela função de ordem superior [transaction]
        println("\nInsertion in a transaction managed by the [transaction] function...")
        executeUpdate("delete from articles")
        try {
            insertArticlesWithFunction(articles)
        } catch (ex: SQLException) {
            println("transaction rolled back (${ex.message})")
        }
        insertArticlesWithFunction(articles.take(1))
        displayArticles()
// inserção em uma transação gerenciada pela função [transaction]: as exceções são propagadas
private fun insertArticlesWithFunction(articles: List<Article>) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        val nbArticles = connection.transaction(Connection.TRANSACTION_READ_COMMITTED) {
            // no bloco, [this] é a conexão
            createInsertStatement(this).use { statement ->
                articles.forEach { insertArticle(statement, it) }
            }
            articles.size
        }
        println("transaction committed: $nbArticles article(s) inserted")
    }
}
  • Linhas 2–11: o código da transação se reduz ao trabalho a ser feito. O bloco recebe a conexão como receptor ([createInsertStatement(this)]) e retorna o número de registros inseridos, que se torna o valor de [connection.transaction(…)]. Nenhuma exceção é capturada: em caso de falha, a transação é desfeita e a exceção é repassada ao chamador;
  • Na parte 4 de [main] (primeira listagem acima), a segunda inserção da lista de dois itens falha: a função [transaction] desfaz a transação e relança a exceção, que o chamador captura; ele exibe “transaction rolled back” (linha 28 dos resultados); a tabela permanece vazia, já que a inserção da linha 27 foi desfeita. A inserção de uma lista com um único item, [articles.take(1)], é bem-sucedida (linhas 29–30): a tabela contém o item (linha 32).

A função [transaction] tem dez linhas de código, mas expressa em Kotlin o que os templates oferecem: o Spring fornece [TransactionTemplate] e a anotação [@Transactional] (Capítulo 11), e a biblioteca Exposed da JetBrains, apresentada na conclusão, por coincidência tem uma função chamada [transaction { … }]. Vamos reutilizá-la nos programas [Jdbc08] e [Jdbc09].

10.7. Consultas que retornam um único valor

Algumas consultas retornam apenas um valor, como [select count(*) from articles]: uma única linha de uma única coluna. O JDBC não tem um método específico para elas: executamos a consulta com [executeQuery] e lemos a primeira coluna da primeira linha do resultado. O programa [Jdbc07.kt] escreve duas funções para esse fim e demonstra algumas maneiras de usá-las:

package jdbc07

import java.math.BigDecimal
import java.math.RoundingMode
import java.sql.DriverManager

// consultas que devolvem um único valor

private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

fun main() {
    try {
        // os valores únicos devolvidos pelos selects, no tipo escolhido pelo driver
        val averagePrice = singleValue("select avg(price) from articles")
        println("Average price of the articles = $averagePrice (type ${type(averagePrice)})")
        val nbArticles = singleValue("select count(id) from articles")
        println("Number of articles = $nbArticles (type ${type(nbArticles)})")
        val name = singleValue("select name from articles where price = (select max(price) from articles)")
        println("Most expensive article = $name (type ${type(name)})")
        val missing = singleValue("select name from articles where price > 10000")
        println("Article over 10000 euros: $missing")
        val empty = singleValue("select max(price) from articles where price > 10000")
        println("Maximum price above 10000 euros: $empty")

        // os mesmos valores no tipo solicitado: o driver faz a conversão
        val average = typedSingleValue<BigDecimal>("select avg(price) from articles")
        val number = typedSingleValue<Int>("select count(id) from articles")
        println("Rounded average = ${average?.setScale(2, RoundingMode.HALF_UP)}, number = $number")
        // um valor ausente: o operador Elvis fornece um valor padrão
        val maximum = typedSingleValue<BigDecimal>("select max(price) from articles where price > 10000")
            ?: BigDecimal.ZERO
        println("Maximum price above 10000 euros, or 0: $maximum")

        // o cast seguro as? devolve null se o objeto não for do tipo solicitado
        println("nbArticles as? Int = ${nbArticles as? Int}, nbArticles as? Long = ${nbArticles as? Long}")
        // o smart cast após um teste de tipo
        if (nbArticles is Number) {
            println("nbArticles.toInt() = ${nbArticles.toInt()}")
        }
        // um cast direto falha: count devolve um Long e não um Int
        val n = nbArticles as Int
        println("n = $n")
    } catch (ex: Exception) {
        // SQLException ou ClassCastException
        println("${ex::class.simpleName} : ${ex.message}")
    }
}

// execução de um select que devolve um único valor: primeira coluna da primeira linha
// o resultado é null se o select não devolver nenhuma linha ou se o valor for SQL NULL
private fun singleValue(query: String): Any? =
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.createStatement().use { statement ->
            statement.executeQuery(query).use { result ->
                if (result.next()) result.getObject(1) else null
            }
        }
    }

// a mesma coisa, sendo o valor convertido pelo driver no tipo solicitado T
// [reified]: o tipo T é conhecido em tempo de execução, T::class.java é a classe Java de T
private inline fun <reified T : Any> typedSingleValue(query: String): T? =
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.createStatement().use { statement ->
            statement.executeQuery(query).use { result ->
                if (result.next()) result.getObject(1, T::class.java) else null
            }
        }
    }

// o nome simples da classe de um objeto
private fun type(obj: Any?): String = obj?.let { it::class.simpleName } ?: "null"

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc07.Jdbc07Kt
Average price of the articles = 271.666667 (type BigDecimal)
Number of articles = 6 (type Long)
Most expensive article = bow (type String)
Article over 10000 euros: null
Maximum price above 10000 euros: null
Rounded average = 271.67, number = 6
Maximum price above 10000 euros, or 0: 0
nbArticles as? Int = null, nbArticles as? Long = 6
nbArticles.toInt() = 6
ClassCastException : class java.lang.Long cannot be cast to class java.lang.Integer (java.lang.Long and java.lang.Integer are in module java.base of loader 'bootstrap')

Comentários:

  • Linha 53: a função [singleValue] executa a consulta e retorna a primeira coluna da primeira linha por meio de [getObject(1)], ou [null] se a consulta não retornar nenhuma linha (linha 57). Seu tipo de retorno é [Any?]: qualquer objeto, ou [null]. O corpo da função é uma expressão: as três instruções [use] aninhadas retornam o valor de seu bloco, e o valor da instrução [if] torna-se o valor da função;
  • Linha 16: o preço médio dos itens. O tipo do resultado depende do tipo do valor (SQL): a média de uma coluna ([DECIMAL]) é um [BigDecimal], neste caso com seis casas decimais (linha 2 dos resultados). A função [type] (linha 74) retorna o nome simples da classe do objeto, [it::class.simpleName], ou “null”;
  • linha 18: o número de itens. O MySQL retorna um inteiro do tipo [BIGINT], que se torna um [java.lang.Long], que o Kotlin chama de [Long] (linha 3);
  • linha 20: o nome do item mais caro, uma string. A consulta contém uma subconsulta, entre parênteses;
  • linha 22: se a consulta não retornar nenhuma linha, a função retorna [null] (linha 5);
  • linha 24: se a consulta retornar uma linha cujo valor é SQL ou [NULL] (o máximo de um conjunto vazio), [getObject] também retorna [null] (linha 6). Nossa função, portanto, não distingue os dois casos, nenhuma linha ou um valor [NULL]; se a distinção for importante, é preciso verificar você mesmo o resultado de [next];
  • linha 64: uma segunda função, genérica, retorna um valor do tipo solicitado [T], ou [null]. Em Java, o método equivalente teria de aceitar o tipo como um parâmetro do tipo [Class<T> type], porque os tipos genéricos são apagados em tempo de compilação: em tempo de execução, o método não sabe o que é [T]. Uma função [inline] cujo parâmetro de tipo é [reified] (“reificado”, Capítulo 4) não tem essa limitação: seu código é reescrito com o tipo real a cada chamada, e [T::class.java] (linha 68) é a classe Java desse tipo, que [getObject(1, type)] usa para pedir ao driver que converta o valor. A chamada é escrita simplesmente como [typedSingleValue<BigDecimal>(…)];
  • linhas 28–30: o próprio driver converte o valor para o tipo solicitado: a média para [BigDecimal], o número de itens ([BIGINT]) para [Int]. Como o resultado é do tipo [BigDecimal?], a chamada a [setScale], que arredonda a média para duas casas decimais, passa pelo operador [?.] (linha 7);
  • linhas 32–33: o operador Elvis [?:] substitui um valor ausente por um valor padrão: [maximum] é do tipo [BigDecimal], que não aceita nulo (linha 8);
  • linha 37: a conversão de tipo segura [as?] retorna [null] em vez de lançar uma exceção quando o objeto não é do tipo solicitado: [nbArticles] não é um [Int], mas sim um [Long] (linha 9);
  • linhas 39–40: depois da verificação [is Number], [nbArticles] é convertido automaticamente para [Number] dentro do bloco [if] (conversão inteligente de tipo, Capítulo 4), e então é possível chamar [toInt]. Esta é a maneira robusta de tratar um valor numérico cujo tipo exato é desconhecido (linha 10);
  • Linha 43: uma conversão de tipo direta falha porque o objeto é um [java.lang.Long]: na JVM, um [Int] armazenado em uma variável do tipo [Any?] é um [java.lang.Integer], e um [Long] não é. Uma [ClassCastException] é instanciada (linha 11): sua mensagem menciona as classes Java [java.lang.Long] e [java.lang.Integer]. Este código funcionaria com um SGBD em que [count] retorna um inteiro do tipo [INT], mas falha com MySQL: este é um exemplo das particularidades dos SGBDs que o JDBC não esconde. É mais seguro ler usando [getInt], [getLong] ou a função [typedSingleValue], que deixa a conversão a cargo do driver;
  • Linha 45: Kotlin não tem uma cláusula [catch] múltipla como a do Java, [catch (SQLException | ClassCastException ex)]: aqui, interceptamos a classe comum a ambas, [Exception]. Para tratar vários tipos de maneira diferente, é possível escrever várias cláusulas [catch], ou uma só seguida de um [when (ex)] com ramos [is SQLException ->].

Por que dois nomes, [singleValue] e [typedSingleValue], em vez de duas sobrecargas de [singleValue] como em Java? Porque as duas funções teriam a mesma assinatura para a JVM: como o parâmetro do tipo [T] é omitido, a função genérica se torna, como a outra, uma função que recebe uma [String] e retorna um [Object]. O compilador rejeita as duas declarações:

1
2
3
4
5
Jdbc07.kt:53:9: error: platform declaration clash: The following declarations have the same JVM signature (singleValue(Ljava/lang/String;)Ljava/lang/Object;):
    fun singleValue(query: String): Any? defined in jdbc07
    inline fun <reified T : Any> singleValue(query: String): T? defined in jdbc07
private fun singleValue(query: String): Any? =
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Em Java, os dois métodos eram distinguidos pelo parâmetro [Class<T>] do segundo, que o parâmetro reified torna desnecessário em Kotlin. (A anotação [@JvmName("…")] permitiria dar a um deles outro nome para a JVM, mas um nome diferente é mais claro aqui.)

10.8. JDBC é bloqueante: threads virtuais e corrotinas

10.8.1. Threads virtuais

O Capítulo 9 mostrou que as operações de E/S não devem bloquear uma thread da aplicação em aplicações que atendem muitos usuários (servidores, serviços web): enquanto um comando SQL é executado no servidor, a thread que o emitiu fica esperando. Ora, o JDBC é uma API bloqueante: [getConnection], [executeQuery], [next], [commit]… não devolvem o controle até que o SGBD responda. Não existe versão assíncrona do JDBC: um projeto de API assíncrona (ADBA) foi abandonado em 2019, e a API reativa R2DBC, que existe independentemente do JDK, não substituiu o JDBC. Também não existe uma versão “suspensa” do JDBC para corrotinas: uma função JDBC chamada dentro de uma corrotina bloqueia a thread que executa a corrotina.

Desde o Java 21, a solução da plataforma Java é a thread virtual (Capítulo 8): uma thread leve, gerenciada pela JVM, da qual é possível criar milhares. Quando uma thread virtual aguarda uma resposta da rede, a JVM a coloca de lado e libera a thread do sistema que a executava, permitindo que ela execute outra thread virtual. O código continua sequencial e bloqueante, simples de escrever e de ler, mas a espera deixa de ter custo. Os drivers JDBC recentes, incluindo o Connector/J 9, são escritos para funcionar com threads virtuais, e, desde o Java 24 (JEP 491), uma thread virtual que aguarda dentro de um bloco [synchronized] não monopoliza mais a thread do sistema. Kotlin, que roda na JVM, se beneficia disso sem precisar fazer nada de especial.

Resta uma limitação: o número de conexões. Mil threads virtuais consultando o banco de dados não têm mil conexões; elas compartilham as conexões do pool e esperam que uma conexão fique disponível. O programa [Jdbc08.kt] demonstra isso, com um pool HikariCP de quatro conexões:

package jdbc08

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import common.Article
import common.toArticle
import common.transaction
import java.sql.SQLException
import java.sql.SQLTimeoutException
import java.util.concurrent.Callable
import java.util.concurrent.ExecutionException
import java.util.concurrent.Executors
import javax.sql.DataSource
import kotlin.time.DurationUnit
import kotlin.time.TimeSource
import kotlin.time.measureTime

// JDBC é bloqueante: consultas concorrentes executadas por threads virtuais

fun main() {
    // a fonte de dados: um pool de no máximo 4 conexões com o banco de dados [dbarticles]
    val config = HikariConfig().apply {
        jdbcUrl = "jdbc:mysql://localhost:3306/dbarticles"
        username = "root"
        password = ""
        maximumPoolSize = 4
    }

    try {
        HikariDataSource(config).use { dataSource ->
            // 1 - uma instrução de atualização, em uma transação
            dataSource.connection.use { connection ->
                val nbRows = connection.transaction {
                    prepareStatement("update articles set price = price * 1.1 where price < ?").use { statement ->
                        statement.setInt(1, 200)
                        statement.executeUpdate()
                    }
                }
                println("$nbRows price(s) increased by 10 %")
            }

            // 2 - seis consultas concorrentes, cada uma em sua thread virtual
            val duration = measureTime {
                val tasks = Executors.newVirtualThreadPerTaskExecutor().use { executor ->
                    (1..6).map { id -> executor.submit(Callable { getArticleSlowly(dataSource, id) }) }
                } // fechar o executor espera o fim das seis tarefas
                tasks.forEach { println(it.get()) }
            }
            println("6 one-second queries executed in %.1f s with %d connections"
                .format(duration.toDouble(DurationUnit.SECONDS), config.maximumPoolSize))

            // 3 - um único valor
            dataSource.connection.use { connection ->
                connection.createStatement().use { statement ->
                    statement.executeQuery("select sum(price * currentstock) from articles").use { result ->
                        result.next()
                        println("Value of the stock: ${result.getBigDecimal(1)} euros")
                    }
                }
            }

            // 4 - uma consulta bloqueada por outra transação, abandonada após um segundo
            // a transação [blocking] modifica o artigo nº 1 e não termina:
            // a linha permanece bloqueada
            dataSource.connection.use { blocking ->
                blocking.autoCommit = false
                blocking.createStatement().use { statement ->
                    statement.executeUpdate("update articles set currentstock = currentstock - 1 where id = 1")
                }
                // outra conexão quer modificar a mesma linha: ela deve esperar o fim de [blocking]
                val stopwatch = TimeSource.Monotonic.markNow()
                try {
                    dataSource.connection.use { connection ->
                        connection.createStatement().use { statement ->
                            statement.queryTimeout = 1
                            statement.executeUpdate("update articles set price = price * 2 where id = 1")
                        }
                    }
                } catch (ex: SQLTimeoutException) {
                    val seconds = stopwatch.elapsedNow().inWholeSeconds
                    println("${ex::class.simpleName} after $seconds s : ${ex.message}")
                }
                // a transação [blocking] não foi confirmada: ela é desfeita
                blocking.rollback()
            }
        }
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    } catch (ex: ExecutionException) {
        println("Database access error (${ex.message})")
    }
}

// um artigo lido por uma consulta que dura um segundo (a função sleep do MySQL simula uma consulta lenta)
private fun getArticleSlowly(dataSource: DataSource, id: Int): Article =
    dataSource.connection.use { connection ->
        connection.prepareStatement(
            "select id, name, price, currentstock, minimumstock, sleep(1) from articles where id = ?"
        ).use { statement ->
            statement.setInt(1, id)
            statement.executeQuery().use { result ->
                result.next()
                result.toArticle()
            }
        }
    }

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc08.Jdbc08Kt
3 price(s) increased by 10 %
Article(id=1, name=bike, price=500.00, currentStock=10, minimumStock=5)
Article(id=2, name=pump, price=11.00, currentStock=10, minimumStock=2)
Article(id=3, name=bow, price=600.00, currentStock=4, minimumStock=1)
Article(id=4, name=arrows - set of 6, price=110.00, currentStock=12, minimumStock=20)
Article(id=5, name=diving suit, price=300.00, currentStock=8, minimumStock=2)
Article(id=6, name=diver's oxygen tanks, price=132.00, currentStock=10, minimumStock=5)
6 one-second queries executed in 2.1 s with 4 connections
Value of the stock: 12550.00 euros
MySQLTimeoutException after 1 s : Statement cancelled due to timeout or client request

Comentários:

  • Linhas 22–26: configuração de um pool com no máximo quatro conexões. No bloco [apply], [this] é o objeto [HikariConfig]: os acessores [setJdbcUrl], [setUsername]… viram atribuições de propriedades;
  • Linha 30: a fonte de dados [HikariDataSource], fechada no fim do programa por [use];
  • Linhas 31–39: um comando de atualização dentro de uma transação, em uma conexão obtida do pool ([dataSource.connection], a propriedade correspondente a [getConnection()]). A transação é gerenciada pela função [transaction] do pacote [common]: o bloco retorna o número de linhas modificadas, que se torna o valor de [nbRows]. Fechar a conexão por meio de [use] a devolve ao pool. Os resultados mostram que os três preços abaixo de 200 euros foram aumentados em 10% (linha 2 dos resultados);
  • linha 95: a função [getArticleSlowly] lê um artigo em uma consulta que leva um segundo: a função [sleep(1)] do MySQL (linha 98) espera um segundo antes de retornar 0. Ela simula uma consulta lenta. A linha atual do resultado se torna um [Article] por meio da função de extensão [toArticle] do pacote [common];
  • linhas 42–50: seis leituras de artigos, executadas simultaneamente:
    • linha 43: a função [measureTime] da biblioteca padrão ([kotlin.time]) executa seu bloco e retorna sua duração, um objeto [Duration]; [duration.toDouble(DurationUnit.SECONDS)] a expressa em segundos. Isso é mais legível do que as diferenças de [System.nanoTime()] do código Java;
    • linha 44: [Executors.newVirtualThreadPerTaskExecutor] (Java 21, Capítulo 8) cria um executor que inicia cada tarefa em uma nova thread virtual. Um [ExecutorService] é [AutoCloseable] desde o Java 19: [use] o fecha no fim do bloco (linha 46), e seu método [close] aguarda a conclusão de todas as tarefas. O valor do bloco de [use], a lista de tarefas, se torna o de [tasks];
    • linha 45: [map] transforma os identificadores de 1 a 6 em seis tarefas submetidas ao executor. Cada tarefa é uma lambda convertida em [Callable<Article>] (conversão SAM, Capítulo 7). A interface é nomeada explicitamente [Callable { … }], porque [submit] também existe para um [Runnable]. [submit] retorna um [Future<Article>]. A lambda usa diretamente a variável [id]: diferentemente do Java, que exigia uma cópia da variável de laço, Kotlin não tem essa restrição, e [id] é, de todo modo, um parâmetro da lambda, diferente para cada elemento;
    • Linha 47: os artigos são exibidos na ordem das tarefas. [Future.get] retorna o resultado da tarefa; se a tarefa lançou uma exceção, [get] lança uma [ExecutionException] que a encapsula (interceptada na linha 89). Em Java, [get] também exigia declarar ou capturar a exceção verificada [InterruptedException]; Kotlin não impõe essa exigência;
    • linhas 49–50: duração total. Seis consultas de um segundo executadas uma após a outra levariam seis segundos; executadas simultaneamente com seis conexões, um segundo. Com quatro conexões, leva cerca de dois segundos (linha 9 dos resultados): quatro threads virtuais obtêm uma conexão e executam suas consultas simultaneamente, enquanto as outras duas esperam em [getConnection] que uma conexão seja devolvida ao pool, e depois executam as suas. O tempo restante é gasto na abertura das conexões do pool. Uma thread que espera demais por uma conexão (30 segundos por padrão, configuração [connectionTimeout] no HikariCP) recebe uma [SQLTransientConnectionException]. A função [format] usa o locale da máquina;
  • linhas 52–57: um único valor, o valor do estoque;
  • linhas 62–84: interrupção de uma consulta que demora demais:
    • linhas 65–68: uma primeira conexão sai do modo autocommit e modifica o artigo nº 1 sem confirmar sua transação. O SGBD bloqueou a linha modificada até o fim da transação;
    • Linhas 71–81: uma segunda conexão tenta modificar a mesma linha. Ela precisa esperar o término da primeira transação… que não terminará. [statement.queryTimeout = 1] (linha 75, [setQueryTimeout(1)] em Java) limita o tempo de execução do comando a um segundo: após um segundo, o Connector/J instrui o servidor a abortar a requisição (comando [KILL QUERY] do MySQL), e [executeUpdate] dispara uma [SQLTimeoutException], neste caso da classe [MySQLTimeoutException] do driver (linha 11 dos resultados). Sem esse timeout, a requisição teria esperado o tempo limite de bloqueio do MySQL, 50 segundos por padrão, e então falharia. O cronômetro é um carimbo de tempo ([TimeSource.Monotonic.markNow()]), e [elapsedNow] indica o tempo decorrido;
    • linha 84: a transação [blocking] nunca é confirmada; ela sofre rollback. A conexão é então devolvida ao pool;
  • linhas 87–89: duas cláusulas [catch], que substituem a cláusula múltipla do Java.

As threads virtuais não trazem benefício a um pequeno programa de console que executa uma tarefa por vez: elas não tornam uma consulta mais rápida. Mas permitem que uma aplicação servidora, como os serviços web Spring Boot do Capítulo 13 (em que a propriedade [spring.threads.virtual.enabled=true] é suficiente), atenda muitos usuários ao mesmo tempo usando código JDBC comum. O tamanho do pool de conexões passa então a ser o verdadeiro controle do paralelismo em relação ao banco de dados.

10.8.2. Corrotinas

Uma aplicação Kotlin escrita com corrotinas (Capítulo 9) enfrenta o mesmo problema: o JDBC não possui funções de suspensão. Chamar [executeQuery] em uma corrotina executada pelo dispatcher [Dispatchers.Default] bloquearia uma das poucas threads desse dispatcher (igual ao número de núcleos da CPU), que são reservadas para cálculos. Aplica-se a regra discutida no Capítulo 9: uma chamada bloqueante é encapsulada em [withContext(Dispatchers.IO) { … }]***, que a executa em uma thread do dispatcher *[IO]*, projetado para esperar, e suspende a corrotina chamadora nesse meio-tempo. O resultado *é uma função de suspensão que esconde a chamada bloqueante. O programa [Jdbc08Coroutines.kt] continua a Parte 2 do programa anterior usando corrotinas, com três dispatchers diferentes. Ele usa a biblioteca kotlinx-coroutines, declarada no arquivo [build.gradle.kts] do subprojeto por [implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.11.0")]:

package jdbc08coroutines

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import common.Article
import common.toArticle
import java.util.concurrent.Executors
import javax.sql.DataSource
import kotlin.time.DurationUnit
import kotlin.time.measureTime
import kotlinx.coroutines.CoroutineDispatcher
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.TimeoutCancellationException
import kotlinx.coroutines.asCoroutineDispatcher
import kotlinx.coroutines.async
import kotlinx.coroutines.awaitAll
import kotlinx.coroutines.coroutineScope
import kotlinx.coroutines.runBlocking
import kotlinx.coroutines.withContext
import kotlinx.coroutines.withTimeout

// JDBC é bloqueante: consultas concorrentes executadas por corrotinas

fun main() = runBlocking {
    // a fonte de dados: um pool de no máximo 4 conexões com o banco de dados [dbarticles]
    val config = HikariConfig().apply {
        jdbcUrl = "jdbc:mysql://localhost:3306/dbarticles"
        username = "root"
        password = ""
        maximumPoolSize = 4
    }
    HikariDataSource(config).use { dataSource ->
        // 1 - seis consultas concorrentes executadas pelas threads do dispatcher IO
        readSixArticles(dataSource, Dispatchers.IO, "Dispatchers.IO")
        // 2 - não mais consultas simultâneas que conexões: uma view limitada do dispatcher IO
        readSixArticles(dataSource, Dispatchers.IO.limitedParallelism(4), "IO.limitedParallelism(4)")
        // 3 - um dispatcher que executa cada corrotina em uma thread virtual
        Executors.newVirtualThreadPerTaskExecutor().asCoroutineDispatcher().use { virtual ->
            readSixArticles(dataSource, virtual, "virtual threads")
        }
        // 4 - withTimeout não interrompe uma chamada JDBC bloqueante
        val duration = measureTime {
            try {
                withTimeout(300) { getArticleSlowly(dataSource, 1, Dispatchers.IO) }
            } catch (ex: TimeoutCancellationException) {
                println("withTimeout(300) : ${ex.message}")
            }
        }
        println("... but the exception only arrived after ${duration.toString(DurationUnit.SECONDS, 1)}")
    }
}

// leitura concorrente dos artigos 1 a 6, cada um por uma consulta de um segundo
private suspend fun readSixArticles(
    dataSource: DataSource,
    dispatcher: CoroutineDispatcher,
    name: String,
) {
    val duration = measureTime {
        val articles = coroutineScope {
            (1..6).map { id -> async { getArticleSlowly(dataSource, id, dispatcher) } }.awaitAll()
        }
        println(articles.joinToString { it.name })
    }
    println("  $name : 6 one-second queries executed in ${duration.toString(DurationUnit.SECONDS, 1)}")
}

// um artigo lido por uma consulta que dura um segundo: uma função suspensa
// que executa o código JDBC bloqueante em uma thread do dispatcher recebido
private suspend fun getArticleSlowly(
    dataSource: DataSource,
    id: Int,
    dispatcher: CoroutineDispatcher,
): Article =
    withContext(dispatcher) {
        dataSource.connection.use { connection ->
            connection.prepareStatement(
                "select id, name, price, currentstock, minimumstock, sleep(1) from articles where id = ?"
            ).use { statement ->
                statement.setInt(1, id)
                statement.executeQuery().use { result ->
                    result.next()
                    result.toArticle()
                }
            }
        }
    }

Resultados:

1
2
3
4
5
6
7
8
9
> gradlew -q :jdbc:run -Pmain=jdbc08coroutines.Jdbc08CoroutinesKt
bike, pump, bow, arrows - set of 6, diving suit, diver's oxygen tanks
  Dispatchers.IO : 6 one-second queries executed in 2.1s
bike, pump, bow, arrows - set of 6, diving suit, diver's oxygen tanks
  IO.limitedParallelism(4) : 6 one-second queries executed in 2.0s
bike, pump, bow, arrows - set of 6, diving suit, diver's oxygen tanks
  virtual threads : 6 one-second queries executed in 2.0s
withTimeout(300) : Timed out waiting for 300 ms
... but the exception only arrived after 1.0s

Comentários:

  • Linha 70: a leitura lenta de um artigo agora é uma função de suspensão. Seu corpo é uma chamada a [withContext(dispatcher)] (linha 75): o código JDBC, idêntico ao do programa anterior, roda em uma thread do dispatcher passado como parâmetro, enquanto a corrotina chamadora fica suspensa. O valor retornado pelo bloco, o artigo, é o de *[withContext]* e, portanto, o da função. Na prática, o dispatcher seria fixado dentro da função (***[withContext(Dispatchers.IO)]***); aqui ele é um parâmetro para permitir a comparação de vários dispatchers;
  • linha 54: a função [readSixArticles] lê os artigos de 1 a 6 de forma concorrente:
    • linhas 60–61: dentro de um escopo chamado [coroutineScope] (concorrência estruturada, Capítulo 9), seis corrotinas são iniciadas por [async], e então [awaitAll] aguarda seus seis resultados. Se qualquer uma delas falhar, as outras são abortadas e a exceção é propagada ao chamador;
    • linha 63: os nomes dos artigos, na ordem de seus identificadores (linhas 2, 4 e 6 dos resultados);
  • linha 24: o programa principal é uma corrotina, executada por [runBlocking];
  • linha 34: as seis corrotinas executam seu código JDBC nas threads do dispatcher [IO] (até 64 threads). Como com as threads virtuais, quatro consultas rodam simultaneamente e duas esperam por uma conexão do pool: aproximadamente dois segundos (linha 3 dos resultados), mais o tempo de abertura das conexões do pool, que ocorre apenas uma vez;
  • linha 36: [limitedParallelism(4)] cria uma view do dispatcher [IO] que não executa mais de quatro blocos por vez (Capítulo 9). Com um paralelismo igual ao tamanho do pool, nenhuma thread espera em [getConnection]: as corrotinas excedentes esperam, suspensas, até que um dos quatro espaços fique disponível, o que não tem custo em threads. Esta é a melhor prática quando muitas corrotinas acessam o banco de dados: um dispatcher cujo paralelismo corresponde ao do pool de conexões. O tempo de execução é o mesmo (linha 5);
  • linhas 38–39: um dispatcher que executa cada bloco em uma nova thread virtual, criado a partir do executor Java por [asCoroutineDispatcher] (Capítulo 9) e fechado por [use]. Corrotinas e threads virtuais trabalham juntas: as corrotinas estruturam o código, enquanto as threads virtuais tornam a espera barata (linha 7);
  • Linhas 41–49: um aviso. [withTimeout(300)] aborta a corrotina após 300 ms, e a exceção [TimeoutCancellationException] é de fato lançada (linha 8 dos resultados)… mas após um segundo (linha 9). O término de uma corrotina é cooperativo (Capítulo 9): ele só é detectado em pontos de suspensão. Ora, o código JDBC, bloqueado em [executeQuery], não tem esses pontos de suspensão: ele percorre toda a consulta, e [withContext] só devolve o controle depois, lançando a exceção. A consulta não foi interrompida no servidor. Para limitar de verdade a duração de uma consulta, é preciso usar o timeout do JDBC, [statement.queryTimeout = n], como no programa anterior.

Observe que as corrotinas não introduzem assincronia no JDBC: elas deslocam as chamadas bloqueantes para threads projetadas para esperar, o que permite integrar o acesso ao banco de dados a uma aplicação escrita com corrotinas (um servidor Ktor ou os controllers com suspend do Spring, por exemplo). O ganho real no número de usuários atendidos vem, como em Java, das threads virtuais e do tamanho do pool. O Capítulo 9 apresentou uma comparação mais geral dos dois modelos.

10.9. Execução em lote

Cada execução de um comando SQL implica uma ida e volta entre a aplicação e o servidor. Quando vários comandos SQL precisam ser executados, eles podem ser enviados ao SGBD em uma única troca, na forma de um lote (batch). O JDBC oferece suporte a isso desde a versão 2.0:

  • para um [Statement], [addBatch(sql)] adiciona um comando SQL ao lote;
  • para um [PreparedStatement], [addBatch()] adiciona ao lote os valores atuais dos parâmetros: o lote contém o mesmo comando SQL executado com valores diferentes;
  • [executeBatch] executa o lote e retorna um array de inteiros ([IntArray] em Kotlin): o número de linhas modificadas por cada comando do lote. Se um comando falhar, lança [BatchUpdateException], cujo método [getUpdateCounts] (a propriedade [updateCounts] em Kotlin) retorna os resultados dos comandos executados.

O programa [Jdbc09.kt] usa os seguintes lotes:

package jdbc09

import common.transaction
import java.math.BigDecimal
import java.sql.DriverManager
import java.sql.SQLException
import kotlin.time.measureTimedValue

// execução em lote (batch): várias instruções SQL enviadas ao SGBD em uma única troca

private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

fun main() {
    try {
        DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
            println("The driver can execute batches: ${connection.metaData.supportsBatchUpdates()}")

            // 1 - um batch de três instruções de atualização diferentes
            connection.createStatement().use { statement ->
                statement.addBatch(
                    "insert into articles(name, price, currentstock, minimumstock) values('canoe', 800, 5, 1)"
                )
                statement.addBatch(
                    "update articles set currentstock = currentstock + 10 where currentstock < minimumstock"
                )
                statement.addBatch("delete from articles where name = 'pump'")
                val nbRows: IntArray = statement.executeBatch()
                println("Rows modified by each statement: ${nbRows.contentToString()}, "
                    + "total: ${nbRows.sum()}")
            }
        }
        // 2 - 1000 inserções por um batch da mesma instrução parametrizada, sem e depois com reescrita de batch
        insertBatch(URL, 1000)
        insertBatch("$URL?rewriteBatchedStatements=true", 1000)
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    }

    // 3 - dois selects enviados em uma única troca: uma instrução, dois resultados sucessivos
    try {
        val url = "$URL?allowMultiQueries=true"
        DriverManager.getConnection(url, USER, PASSWORD).use { connection ->
            connection.createStatement().use { statement ->
                statement.execute(
                    """
                    select name, currentstock from articles where currentstock < minimumstock + 5;
                    select count(*), sum(price * currentstock) from articles
                    """.trimIndent()
                )
                println("Articles to watch:")
                statement.resultSet.use { result ->
                    while (result.next()) {
                        println("  ${result.getString(1)} : ${result.getInt(2)}")
                    }
                }
                // passando ao próximo resultado
                statement.getMoreResults()
                statement.resultSet.use { result ->
                    result.next()
                    val (count, value) = result.getLong(1) to result.getBigDecimal(2)
                    println("$count articles, value of the stock: $value euros")
                }
            }
        }
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    }
}

// inserção de n artigos por um batch, em uma transação, e depois exclusão desses artigos
private fun insertBatch(url: String, n: Int) {
    DriverManager.getConnection(url, USER, PASSWORD).use { connection ->
        // measureTimedValue devolve o valor do bloco e seu tempo de execução
        val (nbRows, duration) = measureTimedValue {
            connection.transaction {
                prepareStatement(
                    "insert into articles(name, price, currentstock, minimumstock) values(?, ?, ?, ?)"
                ).use { statement ->
                    for (i in 1..n) {
                        statement.setString(1, "batch-$i")
                        statement.setBigDecimal(2, BigDecimal.TEN)
                        statement.setInt(3, i)
                        statement.setInt(4, 0)
                        // os valores dos parâmetros são adicionados ao batch
                        statement.addBatch()
                    }
                    statement.executeBatch()
                }
            }
        }
        val database = url.substringAfterLast('/')
        val ms = duration.inWholeMilliseconds
        println("${nbRows.size} insertions ($database) in $ms ms : ${nbRows.take(3)}...")
        // a tabela é devolvida ao seu estado anterior
        connection.createStatement().use { statement ->
            statement.executeUpdate("delete from articles where name like 'batch-%'")
        }
    }
}

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc09.Jdbc09Kt
The driver can execute batches: true
Rows modified by each statement: [1, 1, 1], total: 3
1000 insertions (dbarticles) in 535 ms : [1, 1, 1]...
1000 insertions (dbarticles?rewriteBatchedStatements=true) in 37 ms : [-2, -2, -2]...
Articles to watch:
  bow : 4
  arrows - set of 6 : 22
  canoe : 5
6 articles, value of the stock: 17200.00 euros

Comentários:

  • Linha 18: o método [supportsBatchUpdates] dos metadados da conexão ([connection.metaData], a propriedade correspondente a [getMetaData()]) indica se o driver consegue executar lotes;
  • Linhas 20–31: um lote de três comandos de atualização diferentes, adicionados ao lote de um [Statement] por [addBatch]:
    • linha 29: os três comandos são executados. [executeBatch] retorna o número de linhas modificadas por cada um: uma inserção ([canoe]), uma atualização (o estoque de flechas, que está abaixo do estoque mínimo) e uma exclusão ([pump]). O tipo [IntArray] (o [int[]] do Java), que é inferido, é escrito aqui para fins didáticos;
    • linhas 30–31: [contentToString] exibe o conteúdo do array ([toString] de um array exibe apenas seu endereço), e [sum], uma função de extensão de arrays, calcula a soma de seus elementos (linha 3 dos resultados). Java usava [Arrays.toString] e [Arrays.stream(…).sum()];
  • linhas 35–36: a função [insertBatch] é chamada duas vezes, com duas funções de URL diferentes;
  • linha 73: a função [insertBatch] insere [n] artigos em um lote:
    • linha 76: a função [measureTimedValue] de [kotlin.time] executa seu bloco e retorna um objeto [TimedValue], uma data class que contém o valor retornado pelo bloco e a duração de sua execução; a desestruturação atribui esses valores a duas variáveis;
    • Linha 77: as inserções são realizadas dentro de uma transação gerenciada pela função [transaction] do pacote [common], o que também é muito mais rápido do que confirmar cada uma individualmente;
    • linhas 81–87: para cada artigo, os parâmetros recebem seus valores, e então [addBatch()] adiciona esses valores ao lote;
    • Linha 89: o lote é executado; seu resultado é o do bloco [use], seguido do bloco [transaction], que valida a transação antes de retorná-lo, e por fim do bloco [measureTimedValue];
    • Linhas 93–95: são exibidos o número de inserções, o banco de dados (que segue o último [/] da URL, por meio de [substringAfterLast]), a duração em milissegundos e os três primeiros elementos do array retornado por [executeBatch] ([take(3)] retorna uma lista, que [println] exibe entre colchetes);
    • linhas 96–98: as linhas inseridas são excluídas para restaurar a tabela ao estado anterior. Como a função [transaction] reativou o modo autocommit, a exclusão é confirmada assim que é executada;
  • linha 4 dos resultados: com a URL simples, o driver MySQL envia as 1.000 inserções uma após a outra: o lote quase não agrega valor. Cada elemento do array é 1;
  • Linha 5 dos resultados: com o parâmetro [rewriteBatchedStatements=true] da URL, o Connector/J reescreve o lote como vários comandos [insert into articles(…) values (…), (…), (…)…], cada um dos quais insere muitas linhas. A execução é de dez a vinte vezes mais rápida. Os elementos do array passam então a valer -2, o valor da constante [Statement.SUCCESS_NO_INFO]: o comando foi bem-sucedido, mas o driver não sabe quantas linhas cada elemento do lote modificou. Este é um parâmetro importante a conhecer: sem ele, os lotes têm pouca utilidade com o MySQL. Os tempos de execução variam de uma execução para outra; apenas sua razão é significativa;
  • linhas 41–63: dois comandos [select] enviados em uma única troca:
    • linha 43: a conexão é aberta com o parâmetro [allowMultiQueries=true], que permite vários comandos SQL separados por ponto e vírgula em uma única requisição. Como vimos, isso é desativado por padrão para limitar o alcance das injeções de SQL;
    • linhas 46–50: os dois comandos, escritos como uma string bruta em duas linhas, são executados por [execute], que aceita qualquer comando;
    • linha 53: a propriedade [resultSet] ([getResultSet()]) retorna o resultado do primeiro comando;
    • linha 59: [getMoreResults] passa ao resultado seguinte (fecha o resultado atual e retorna [true] se o próximo resultado é um [ResultSet]). Kotlin permitiria escrever [statement.moreResults], como numa propriedade; porém, a leitura de uma propriedade não deve alterar o estado de um objeto, e a chamada explícita do método esclarece melhor o que ele faz;
    • linhas 60–63: o resultado da segunda consulta: uma linha com duas colunas, armazenadas em duas variáveis por meio da desestruturação de um par ([a to b]).

Os lotes são úteis para inserir e atualizar grandes quantidades de registros (por exemplo, na importação de dados). O Hibernate (Capítulo 11) também sabe usá-los.

10.10. Modo offline: CachedRowSet

O modo offline do JDBC baseia-se na interface [javax.sql.rowset.CachedRowSet], derivada de [ResultSet]: um [CachedRowSet] é o resultado de uma consulta copiado para a memória, que pode ser percorrido nos dois sentidos e modificado offline, após o que as alterações são enviadas de volta ao banco de dados. O programa [Jdbc10.kt] demonstra isso:

package jdbc10

import java.math.BigDecimal
import java.sql.DriverManager
import java.sql.SQLException
import javax.sql.rowset.RowSetProvider

// modo desconectado: uma cópia em memória das linhas, modificada offline e depois enviada de volta ao banco de dados

private const val URL = "jdbc:mysql://localhost:3306/dbarticles"
private const val USER = "root"
private const val PASSWORD = ""

fun main() {
    try {
        // a fábrica de RowSet da plataforma Java
        val factory = RowSetProvider.newFactory()
        factory.createCachedRowSet().use { articles ->
            // os parâmetros de conexão e a consulta
            articles.url = URL
            articles.username = USER
            articles.password = PASSWORD
            articles.command = "select id, name, price, currentstock, minimumstock from articles"
            // a tabela a atualizar e sua chave primária (coluna nº 1)
            articles.tableName = "articles"
            articles.keyColumns = intArrayOf(1)
            // conexão, cópia das linhas para a memória, desconexão
            articles.execute()
            println("${articles.size()} rows copied into memory")

            // trabalho offline: reabastecimento dos artigos sem estoque
            while (articles.next()) {
                val currentStock = articles.getInt("currentstock")
                val minimumStock = articles.getInt("minimumstock")
                if (currentStock < minimumStock) {
                    articles.updateInt("currentstock", minimumStock + 10)
                    articles.updateRow()
                    val name = articles.getString("name")
                    println("[$name] : stock $currentStock -> ${articles.getInt("currentstock")}")
                }
            }
            // adição de uma linha: o id 0 pede ao MySQL que gere um
            with(articles) {
                moveToInsertRow()
                updateInt("id", 0)
                updateString("name", "canoe")
                updateBigDecimal("price", BigDecimal("800"))
                updateInt("currentstock", 5)
                updateInt("minimumstock", 1)
                insertRow()
                moveToCurrentRow()
            }
            // exclusão da 2ª linha
            articles.absolute(2)
            println("deletion of [${articles.getString("name")}]")
            articles.deleteRow()

            // o banco de dados ainda não foi modificado
            displayArticles("Database before acceptChanges:")
            // conexão, envio das modificações em uma transação, desconexão
            DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
                connection.autoCommit = false
                articles.acceptChanges(connection)
            }
        }
        displayArticles("Database after acceptChanges:")
    } catch (ex: SQLException) {
        println("Database access error (${ex.message})")
    }
}

// exibição do conteúdo da tabela, lido em modo conectado
private fun displayArticles(title: String) {
    DriverManager.getConnection(URL, USER, PASSWORD).use { connection ->
        connection.createStatement().use { statement ->
            val query = "select id, name, price, currentstock, minimumstock from articles"
            statement.executeQuery(query).use { result ->
                println(title)
                while (result.next()) {
                    val row = with(result) {
                        "${getInt(1)} ${getString(2)} ${getBigDecimal(3)} ${getInt(4)} ${getInt(5)}"
                    }
                    println("  $row")
                }
            }
        }
    }
}

Resultados:

> gradlew -q :jdbc:run -Pmain=jdbc10.Jdbc10Kt
6 rows copied into memory
[arrows - set of 6] : stock 12 -> 30
deletion of [pump]
Database before acceptChanges:
  1 bike 500.00 10 5
  2 pump 10.00 10 2
  3 bow 600.00 4 1
  4 arrows - set of 6 100.00 12 20
  5 diving suit 300.00 8 2
  6 diver's oxygen tanks 120.00 10 5
Database after acceptChanges:
  1 bike 500.00 10 5
  3 bow 600.00 4 1
  4 arrows - set of 6 100.00 30 20
  5 diving suit 300.00 8 2
  6 diver's oxygen tanks 120.00 10 5
  7 canoe 800.00 5 1

Comentários:

  • Linhas 17–18: [RowSetProvider.newFactory] (Java 7) retorna uma fábrica ([RowSetFactory]) dos diversos tipos de RowSet derivados do JDK, um dos quais, [createCachedRowSet], cria um [CachedRowSet]. Um [CachedRowSet] é um recurso ([AutoCloseable], como qualquer [ResultSet]), fechado por [use];
  • linhas 20–23: [CachedRowSet] recebe os parâmetros de conexão e a consulta que o preencherá. Os métodos [setUrl], [setUsername], [setPassword] e [setCommand] têm, cada um, um acessor associado, [getXxx]: Kotlin os representa como propriedades;
  • Linhas 25–26: para poder enviar as alterações de volta ao banco de dados, ele precisa conhecer a tabela modificada e sua chave primária — neste caso, a coluna 1, [id]. [intArrayOf(1)] cria o array [int[]] esperado pelo método Java;
  • linha 28: [execute] abre uma conexão, executa a consulta, copia todas as linhas do resultado para a memória e fecha a conexão. O restante do trabalho é feito offline. [articles.size()] (linha 29) retorna o número de linhas: como [size] não é um acessor de [getSize], ele continua sendo um método;
  • Linhas 32–39: um [CachedRowSet] é um [ResultSet]: nós o percorremos com [next] e lemos as colunas com [getXxx]. Os métodos [updateXxx] modificam uma coluna da linha atual, e [updateRow] salva a alteração… na cópia em memória. O estoque de flechas, que está abaixo do estoque mínimo, é elevado para 30 (linha 3 dos resultados);
  • linhas 43–51: adição de uma linha. As oito chamadas usam [CachedRowSet]: a função [with] o designa como receptor do bloco, o que evita repetir [articles.]. [moveToInsertRow] coloca o cursor em uma linha especial, a “linha de inserção”, cujas colunas são especificadas por [updateXxx]; [insertRow] a adiciona ao [CachedRowSet], e [moveToCurrentRow] devolve o cursor à sua posição anterior. [CachedRowSet] exige um valor para todas as colunas [NOT NULL], inclusive [id]: o valor 0, inserido em uma coluna [AUTO_INCREMENT], instrui o MySQL a gerar um;
  • linhas 54–56: [absolute(2)] coloca o cursor na segunda linha, o que é possibilitado por [CachedRowSet] — que permite mover o cursor em qualquer direção — e [deleteRow] a remove;
  • linha 59: o banco de dados ainda não foi modificado (linhas 5–11 dos resultados);
  • linhas 61–63: [acceptChanges] confirma as alterações no banco de dados: um [update], um [insert] e um [delete], executados em uma transação confirmada no final. Para cada linha modificada, a implementação do JDK verifica se a linha do banco de dados não foi modificada por outra pessoa desde que foi lida (uma verificação “otimista”); caso contrário, lança uma [SyncProviderException]. A versão sem parâmetros de [acceptChanges] abre ela mesma a conexão, mas a deixa em modo autocommit e depois chama [commit], o que o Connector/J rejeita; por isso, é passada a ela uma conexão que saiu do modo autocommit;
  • linha 66: o banco de dados foi modificado (linhas 12–18 dos resultados).

[CachedRowSet] tem a vantagem de ser uma API padrão derivada do JDK, mas suas limitações são evidentes: ele manipula linhas e nomes ou números de colunas, em vez de objetos de negócio; as alterações são feitas por meio de uma implementação sobre a qual temos pouco controle do SQL. É por isso que ele raramente é usado hoje: trabalhar com objetos em memória e depois salvar as alterações é o domínio dos frameworks ORM, como o Hibernate (Capítulo 11).

10.11. Aplicação de exemplo — versão 6

Vamos retomar a aplicação de exemplo [Tax]. A última versão, a versão 5, foi tratada no Capítulo 6. Era uma aplicação de três camadas cuja camada [dao] obtinha seus dados de um arquivo de texto, de um arquivo JSON ou de seu próprio código, e cujas camadas eram integradas pelo contêiner de injeção de dependência do Spring. Vamos escrever uma nova camada, [dao], que obtém seus dados de um banco de dados, o MySQL:

Image

Reutilizamos os subprojetos da versão 5 como estão, por meio de dependências do Gradle, e escrevemos apenas o que é novo: a camada [dao] e o programa que integra as camadas.

10.11.1. O banco de dados

As faixas do imposto são armazenadas na tabela [brackets] de um banco de dados MySQL chamado [dbtaxes], criado pelo script [sql/dbtaxes.sql]:

-- criação do banco de dados [dbtaxes] da aplicação [Tax] (versão 6)
-- execução: mysql -uroot < dbtaxes.sql

-- o script é escrito em UTF-8
SET NAMES utf8mb4;

-- o banco de dados é recriado a cada execução do script
DROP DATABASE IF EXISTS dbtaxes;
CREATE DATABASE dbtaxes CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE dbtaxes;

-- a tabela das faixas de imposto (tabela de 2004 sobre a renda de 2003)
CREATE TABLE brackets (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    limits  DECIMAL(10,2) NOT NULL,
    coeffr  DECIMAL(6,3) NOT NULL,
    coeffn  DECIMAL(10,2) NOT NULL
);
INSERT INTO brackets (limits, coeffr, coeffn) VALUES
    (4962, 0, 0),
    (8382, 0.068, 291.09),
    (14753, 0.191, 1322.92),
    (23888, 0.283, 2668.39),
    (38868, 0.374, 4846.98),
    (47932, 0.426, 6883.66),
    (0, 0.481, 9505.54);

-- um usuário específico da aplicação, que só tem direitos sobre o banco de dados [dbtaxes]
CREATE USER IF NOT EXISTS 'admtaxes'@'localhost' IDENTIFIED BY 'pwdtaxes';
GRANT ALL PRIVILEGES ON dbtaxes.* TO 'admtaxes'@'localhost';
  • Linhas 13–17: a tabela [brackets] tem quatro colunas: a chave primária [id], gerada pelo SGBD, seguida das três colunas [limits], [coeffr] e [coeffn], que representam uma faixa do imposto, do tipo [DECIMAL]: são valores e coeficientes que precisam ser exatos. Eles serão lidos em Kotlin como [BigDecimal], o tipo das propriedades da data class [TaxBracket];
  • linhas 19–26: as sete faixas da tabela do imposto de 2004 para a renda de 2003, usadas desde o início do curso (trata-se de um exercício: a tabela do imposto não está atualizada). As faixas são listadas em ordem crescente de seus limites, e a coluna [id] reflete essa ordem;
  • linhas 29–30: criação de um usuário MySQL [admtaxes], com a senha [pwdtaxes], que recebe todas as permissões ([GRANT ALL PRIVILEGES]) sobre todos os objetos do banco de dados [dbtaxes] ([dbtaxes.*]), e somente sobre eles. A aplicação será executada com esse usuário, e não com o administrador [root]: se a senha do administrador fosse comprometida, apenas o banco de dados [dbtaxes] estaria em risco. [IF NOT EXISTS] permite executar o script várias vezes.

O script é executado pelo administrador:

> mysql -uroot < dbtaxes.sql

em seguida, verificamos que o usuário [admtaxes] tem acesso às faixas (a opção [-p] sozinha solicita a senha, que não é exibida enquanto é digitada):

> mysql -uadmtaxes -p -t dbtaxes -e "select * from brackets"
Enter password:
+----+----------+--------+---------+
| id | limits   | coeffr | coeffn  |
+----+----------+--------+---------+
|  1 |  4962.00 |  0.000 |    0.00 |
|  2 |  8382.00 |  0.068 |  291.09 |
|  3 | 14753.00 |  0.191 | 1322.92 |
|  4 | 23888.00 |  0.283 | 2668.39 |
|  5 | 38868.00 |  0.374 | 4846.98 |
|  6 | 47932.00 |  0.426 | 6883.66 |
|  7 |     0.00 |  0.481 | 9505.54 |
+----+----------+--------+---------+

10.11.2. O projeto TaxV6

A versão 6 é composta por dois subprojetos Gradle, localizados na pasta [10-jdbc/TaxV6] do build do capítulo, que usam os três subprojetos da versão 5:

kotlin-oct-2026/
  06-architectures/                       Gradle build of chapter 6
    settings.gradle.kts
    TaxV5/                                version 5, reused
      tax-dao/                            entities, ITaxDao interface, implementations of version 5
      tax-business/                       [business] layer
      tax-ui/                             [ui] layer (Dialog object) and programs of version 5
  10-jdbc/                                Gradle build of chapter 10
    settings.gradle.kts                   declares the subprojects and the composite build
    build.gradle.kts                      root project
    sql/
      dbtaxes.sql                         creation script of the database
    jdbc/                                 the programs Jdbc01 to Jdbc10
    TaxV6/
      tax-dao-mysql/                      the new [dao] layer
        build.gradle.kts
        src/main/kotlin/tax/dao/
          MySqlTax.kt
        src/test/kotlin/tax/dao/
          MySqlTaxTests.kt                JUnit tests of the new [dao] layer
      tax-ui-mysql/                       integration of the layers
        build.gradle.kts
        tax.properties
        tax-base.properties
        tax-password.properties
        tax-server.properties
        tax-query.properties
        tax-postgresql.properties
        src/main/kotlin/tax/ui/mysql/
          Main.kt

Os subprojetos da versão 5 não fazem parte do build do Capítulo 10; eles são dependências, como o driver JDBC ou o Spring. O curso de Java, construído com Maven, precisava primeiro instalá-los no repositório local da máquina ([mvn install]). O Gradle oferece um mecanismo mais direto, o “build composto ”: o arquivo [settings.gradle.kts] do Capítulo 10, que já encontramos, inclui todo o build do Capítulo 6:

// capítulo 10: acesso a banco de dados com JDBC

// o Gradle baixa por conta própria o JDK solicitado por jvmToolchain(25) se não o encontrar na máquina
plugins {
    id("org.gradle.toolchains.foojay-resolver-convention") version "1.0.0"
}

rootProject.name = "jdbc-chapter"

// os programas JDBC do capítulo
include("jdbc")
// versão 6 da aplicação [Tax]: uma camada [dao] que lê um banco de dados MySQL
include("TaxV6:tax-dao-mysql", "TaxV6:tax-ui-mysql")

// as camadas [business] e [ui] da versão 5 (capítulo 6) são reutilizadas: composite build
includeBuild("../06-architectures")
  • linhas 3–6: o plugin que permite ao Gradle baixar o próprio JDK caso não o encontre na máquina (Capítulo 2);
  • linha 11: o subprojeto que contém os programas [Jdbc01] a [Jdbc10];
  • linha 13: os dois subprojetos da versão 6. A pasta [TaxV6] não contém um arquivo [build.gradle.kts]: é apenas uma pasta de agrupamento, como [TaxV5] no Capítulo 6;
  • linha 16: o build do Capítulo 6 é incluído. Quando um subprojeto do Capítulo 10 declara uma dependência com as coordenadas [examples.tax:tax-business:5.0], o Gradle detecta que um subprojeto do build incluído tem essas coordenadas (o [group] declarado em seu [build.gradle.kts], Capítulo 6, e seu nome, [tax-business]; a versão é irrelevante): ele o substitui pela biblioteca, compila-o se necessário e usa o resultado. Nenhuma instalação prévia é necessária, e uma alteração na versão 5 seria refletida no build seguinte.

Aqui vemos o valor da arquitetura em camadas. As camadas [business] e [ui] conhecem apenas a interface [ITaxDao], definida no subprojeto [tax-dao] da versão 5; a nova camada [dao] implementa essa interface em um novo subprojeto. Nenhuma linha de código da versão 5 é modificada.

10.11.3. A camada [dao]

O arquivo [build.gradle.kts] do subprojeto [tax-dao-mysql] é o seguinte:

// TaxV6: o subprojeto tax-dao-mysql, uma biblioteca (a camada [dao] que lê um banco de dados MySQL)
plugins {
    kotlin("jvm")
    `java-library`
}

// as coordenadas da biblioteca: examples.tax:tax-dao-mysql:6.0
group = "examples.tax"
version = "6.0"

repositories {
    mavenCentral()
}

dependencies {
    // as entidades e a interface ITaxDao da versão 5 (composite build ../06-architectures):
    // elas fazem parte da API da nova camada [dao]
    api("examples.tax:tax-dao:5.0")
    // o driver JDBC do MySQL: o código só o usa em tempo de execução
    runtimeOnly("com.mysql:mysql-connector-j:9.7.0")
    // para os testes: a camada [business] da versão 5, kotlin.test e JUnit
    testImplementation("examples.tax:tax-business:5.0")
    testImplementation(kotlin("test"))
    testImplementation(platform("org.junit:junit-bom:6.0.3"))
    testImplementation("org.junit.jupiter:junit-jupiter")
    testRuntimeOnly("org.junit.platform:junit-platform-launcher")
}

kotlin {
    jvmToolchain(25)
    compilerOptions {
        allWarningsAsErrors = true
        javaParameters = true
    }
}

tasks.test {
    useJUnitPlatform()
    testLogging {
        events("passed", "skipped", "failed")
    }
}
  • linhas 3–4: o plugin Kotlin, cuja versão é especificada pelo projeto raiz, e o plugin de bibliotecas, que adiciona a configuração [api];
  • linhas 8–9: as coordenadas da nova camada, [examples.tax:tax-dao-mysql:6.0];
  • linha 18: o subprojeto [tax-dao] da versão 5, que contém as entidades ([TaxBracket], [TaxException]) e a interface [ITaxDao]. A dependência é declarada por suas coordenadas, e o build composto a resolve. Ela é declarada com [api] e não com [implementation]: a classe [MySqlTax] implementa [ITaxDao], e sua propriedade [taxBrackets] é do tipo [List<TaxBracket>], de modo que um projeto que usa [MySqlTax] precisa desses tipos para compilar;
  • Linha 20: o driver JDBC do MySQL, na configuração [runtimeOnly]: ele não é usado em tempo de compilação, mas está presente em tempo de execução e durante os testes. Isso é uma garantia: se o código usasse por engano uma classe do driver, não compilaria. A camada [dao] depende apenas do JDBC. Os projetos que dependem de [tax-dao-mysql] recebem o driver em tempo de execução, por transitividade;
  • linhas 22–26: somente para testes, a camada [business] da versão 5, [kotlin.test], e o JUnit da versão 6;
  • linhas 29–40: a configuração do compilador e dos testes, a mesma do Capítulo 6.

A classe [MySqlTax] implementa a interface [ITaxDao]:

package tax.dao

import tax.entities.TaxException
import tax.entities.TaxBracket
import java.math.BigDecimal
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.SQLException

// uma camada [dao] que lê as faixas de imposto de um banco de dados MySQL
// a consulta tem um valor padrão: um único construtor basta
class MySqlTax(
    url: String,
    user: String,
    password: String,
    query: String = DEFAULT_QUERY,
) : ITaxDao {

    // faixas de imposto, lidas uma única vez quando o objeto é construído
    override val taxBrackets: List<TaxBracket>

    init {
        val brackets: List<TaxBracket> = try {
            DriverManager.getConnection(url, user, password).use { connection ->
                connection.createStatement().use { statement ->
                    statement.executeQuery(query).use { result ->
                        // uma faixa por linha do resultado: limit, coeffR, coeffN
                        buildList {
                            while (result.next()) {
                                add(readBracket(result))
                            }
                        }
                    }
                }
            }
        } catch (e: SQLException) {
            // erro de acesso ao banco de dados: ele é encapsulado em uma TaxException
            throw TaxException("Error accessing the tax brackets database", e, ACCESS)
        }
        // uma tabela vazia também é um erro
        if (brackets.isEmpty()) {
            throw TaxException("The database contains no tax bracket", code = DATA)
        }
        // buildList devolve uma lista somente leitura
        taxBrackets = brackets
    }

    companion object {
        // códigos de erro específicos desta implementação
        const val ACCESS = 101
        const val DATA = 102

        // a consulta padrão: as faixas devem ser devolvidas em ordem crescente dos limites
        const val DEFAULT_QUERY = "select limits, coeffr, coeffn from brackets order by id"

        // a faixa da linha atual: três colunas numéricas, sem valor NULL
        private fun readBracket(result: ResultSet): TaxBracket {
            // getBigDecimal devolve null para um valor SQL NULL: o tipo é portanto BigDecimal?
            val columns: List<BigDecimal?> = try {
                (1..3).map { result.getBigDecimal(it) }
            } catch (e: SQLException) {
                // uma coluna ausente ou não numérica
                throw TaxException("The tax brackets of the database are incorrect", e, DATA)
            }
            val (limit, coeffR, coeffN) = columns
            if (limit == null || coeffR == null || coeffN == null) {
                throw TaxException("A tax bracket of the database has a NULL value", code = DATA)
            }
            // aqui, os três valores são do tipo BigDecimal (smart cast)
            return TaxBracket(limit, coeffR, coeffN)
        }
    }
}
  • linha 1: a classe está no pacote [tax.dao], como as outras implementações de [ITaxDao], embora esteja em um subprojeto diferente;
  • Linhas 12–17: o construtor primário recebe os parâmetros de conexão e a consulta que retorna as faixas. A consulta tem um valor padrão, [DEFAULT_QUERY]: onde Java exigia dois construtores, o segundo chamando o primeiro por meio de [this(…)], Kotlin usa apenas um. Os parâmetros não são propriedades (não têm [val] nem [var]): são usados apenas para construir o objeto, e a senha não é armazenada;
  • linha 20: a propriedade da interface [ITaxDao], que a classe sobrescreve. Ela não tem valor inicial: recebe seu valor no bloco [init], de uma vez por todas;
  • linha 22: as faixas são lidas de uma vez por todas quando o objeto é construído: a camada [dao] é um singleton na aplicação, e o banco de dados não é consultado novamente depois. Uma conexão aberta por [DriverManager] é, portanto, suficiente: um pool de conexões não teria utilidade;
  • Linhas 23–26: a conexão, o comando SQL e o resultado, envolvidos por três [use] aninhados, como nos exemplos anteriores. [try] é uma expressão: seu valor, o dos blocos aninhados, se torna o valor de [brackets];
  • linhas 28–30: a função [buildList] da biblioteca padrão constrói uma lista: dentro de seu bloco, [this] é uma lista modificável, à qual [add] adiciona uma faixa por linha do resultado; em seguida, ela retorna essa lista como do tipo [List], somente leitura. O tipo da variável, [List<TaxBracket>], é especificado explicitamente: o compilador precisa conhecer o tipo dos elementos da lista antes de analisar o bloco;
  • linhas 36–38: um erro de acesso ao banco de dados é encapsulado em uma [TaxException] com o código [ACCESS], assim como [FileTax] e [JsonTax] faziam com os erros de entrada/saída. A camada [ui] não precisa conhecer a [SQLException] nem o JDBC;
  • Linhas 41–42: uma tabela vazia é um erro de dados. A [TaxException] do Capítulo 6 tem apenas um construtor, [TaxException(message, cause = null, code = 0)]: para gerar um código sem uma causa, o argumento [code] deve ser especificado;
  • linha 45: a propriedade recebe a lista de faixas;
  • linhas 48–51: códigos de erro específicos desta implementação, seguindo o modelo dos de [FileTax] e [JsonTax]: 101 para um erro de acesso ao banco de dados, 102 para dados incorretos. São constantes ([const val]) do objeto companheiro: elas são referenciadas como [MySqlTax.ACCESS];
  • linha 54: a consulta que retorna as faixas. A cláusula [order by id] é importante: a camada [business] espera as faixas em ordem crescente de limites, e um [select] sem [order by] retorna as linhas em uma ordem que o SQL não garante;
  • linha 57: leitura de uma faixa, uma função privada do objeto companheiro (ela não usa nenhuma das propriedades do objeto):
    • Linhas 59–60: [getBigDecimal(i)] lê a coluna de número i como um [BigDecimal]. O tipo declarado, [List<BigDecimal?>], é importante: para Kotlin, um método Java retorna um valor de tipo “de plataforma”, [BigDecimal!], e Kotlin não sabe se ele pode ser [null]. Ora, [getBigDecimal] retorna [null] para um valor SQL [NULL]: ao declarar elementos do tipo [BigDecimal?], obrigamos o compilador a nos deixar verificar esse caso;
    • linhas 61–63: uma consulta que retorna menos de três colunas, ou uma coluna não numérica, causa uma [SQLException] em [getBigDecimal]: isso também é um erro de dados;
    • linhas 65–67: a lista é desempacotada em três variáveis; um valor [null] é um erro de dados;
    • linha 70: após a verificação, as três variáveis são do tipo [BigDecimal] e não mais [BigDecimal?] (conversão inteligente de tipo): o compilador aceita passá-las ao construtor de [TaxBracket], que espera valores não nulos. Sem a verificação, ele teria se recusado a compilar. A [TaxException] instanciada por [readBracket] não é uma [SQLException]: ela atravessa o [catch] do bloco [init] sem ser interceptada, e as três instruções [use] fecham, mesmo assim, o resultado, o comando e a conexão.

Observe que o nome da classe é o único lugar em que o MySQL aparece: o código usa apenas as interfaces de [java.sql]. Voltaremos a isso.

10.11.4. Testes da camada [dao]

Os testes da camada [dao] estão na pasta [src/test/kotlin] do subprojeto:

package tax.dao

import tax.entities.TaxException
import tax.business.ITaxBusiness
import tax.business.TaxBusiness
import java.sql.SQLException
import kotlin.test.Test
import kotlin.test.assertEquals
import kotlin.test.assertFailsWith
import kotlin.test.assertIs
import kotlin.test.assertNotEquals
import org.junit.jupiter.params.ParameterizedTest
import org.junit.jupiter.params.provider.CsvSource

// testes da camada [dao] MySqlTax (o banco de dados [dbtaxes] deve existir)
class MySqlTaxTests {

    // o banco de dados de teste
    private val url = "jdbc:mysql://localhost:3306/dbtaxes"
    private val user = "admtaxes"
    private val password = "pwdtaxes"

    // as faixas lidas do banco de dados são as da versão "hardwired"
    @Test
    fun `same brackets as the hardwired version`() {
        val reference = HardwiredTax().taxBrackets
        val brackets = MySqlTax(url, user, password).taxBrackets
        // equals também compara a escala dos BigDecimals: 4962.00 (banco de dados) não é igual a 4962 (hardwired)
        assertNotEquals(reference, brackets)
        // compareTo compara apenas os valores
        assertEquals(reference.size, brackets.size)
        reference.zip(brackets).forEach { (expected, read) ->
            assertEquals(0, expected.limit.compareTo(read.limit))
            assertEquals(0, expected.coeffR.compareTo(read.coeffR))
            assertEquals(0, expected.coeffN.compareTo(read.coeffN))
        }
    }

    // a camada [business] da versão 5 com a nova camada [dao]
    @ParameterizedTest
    @CsvSource(
        "true, 2, 60000, 4282",
        "true, 3, 60000, 2959",
        "false, 0, 60000, 11519",
    )
    fun `tax calculation`(married: Boolean, nbChildren: Int, salary: Int, tax: Int) {
        val business: ITaxBusiness = TaxBusiness(MySqlTax(url, user, password))
        assertEquals(tax, business.calculateTax(married, nbChildren, salary))
    }

    // banco de dados ausente (ou proibido para o usuário)
    @Test
    fun `missing database`() {
        val e = assertFailsWith<TaxException> {
            MySqlTax(url.replace("dbtaxes", "dbtaxesunknown"), user, password)
        }
        assertEquals(MySqlTax.ACCESS, e.code)
        // assertIs aceita uma classe derivada
        assertIs<SQLException>(e.cause)
    }

    // senha incorreta
    @Test
    fun `incorrect password`() {
        val e = assertFailsWith<TaxException> { MySqlTax(url, user, "x") }
        assertEquals(MySqlTax.ACCESS, e.code)
    }

    // servidor inacessível
    @Test
    fun `unreachable server`() {
        val e = assertFailsWith<TaxException> {
            MySqlTax(url.replace("3306", "3399"), user, password)
        }
        assertEquals(MySqlTax.ACCESS, e.code)
    }

    // consulta que devolve apenas duas colunas
    @Test
    fun `incorrect query`() {
        val e = assertFailsWith<TaxException> {
            MySqlTax(url, user, password, "select limits, coeffr from brackets")
        }
        assertEquals(MySqlTax.DATA, e.code)
    }

    // consulta que não devolve nenhuma linha
    @Test
    fun `no bracket`() {
        val e = assertFailsWith<TaxException> {
            MySqlTax(url, user, password, "select limits, coeffr, coeffn from brackets where id < 0")
        }
        assertEquals(MySqlTax.DATA, e.code)
    }

    // consulta com uma coluna NULL: argumento nomeado para a consulta
    @Test
    fun `NULL value`() {
        val e = assertFailsWith<TaxException> {
            MySqlTax(url, user, password, query = "select limits, coeffr, null from brackets")
        }
        assertEquals(MySqlTax.DATA, e.code)
    }
}
  • Linhas 19–21: os testes usam o banco de dados [dbtaxes], que deve ter sido criado pelo script [dbtaxes.sql]. Portanto, eles não são mais testes unitários no sentido estrito, que testariam uma classe isoladamente, mas sim testes de integração: verificam que a camada [dao] funciona com o SGBD. Para esses testes, costuma-se usar um banco de dados H2 em memória, criado no início dos testes, ou uma instância do SGBD iniciada em um contêiner Docker pela biblioteca Testcontainers; aqui, usaremos simplesmente o banco de dados MySQL da máquina. O JUnit cria uma instância da classe de teste para cada teste: as três propriedades poderiam também ser constantes de um [companion object];
  • Linhas 25–35: as faixas lidas do banco de dados são as da versão “fixa no código” [HardwiredTax]… mas não no sentido de [equals] (linha 29)! O método [equals] gerado para uma data class compara suas propriedades com o método [equals] de cada uma, e o método [BigDecimal] compara o valor e a escala (o número de casas decimais): os valores [BigDecimal] lidos do banco de dados têm duas casas decimais ([4962.00]), assim como a coluna [DECIMAL(10,2)], ao passo que os de [HardwiredTax] não têm nenhuma ([4962]). [compareTo], por outro lado, compara apenas os valores. Esta é uma armadilha clássica de [BigDecimal] (Capítulo 3), que o operador Kotlin [==] não evita, já que ele chama [equals]. A função [zip] (linha 32) emparelha elementos de mesma posição das duas listas, que são então desestruturados em [expected] e [read];
  • linhas 40–48: a camada [business] da versão 5 calcula os impostos esperados usando a nova camada [dao]. [@CsvSource] fornece os parâmetros das três execuções do teste; em Kotlin, os argumentos de uma anotação do tipo array são simplesmente listados um após o outro ([value] é um [vararg]);
  • linhas 53–59: um banco de dados inexistente causa uma [TaxException] com o código [ACCESS]. [assertFailsWith<TaxException> { … }] de [kotlin.test] executa o bloco, verifica que ele lança uma exceção desse tipo e a retorna. [assertIs<SQLException>(e.cause)] verifica que a causa é uma [SQLException] (ou uma classe derivada);
  • linhas 64–66: o mesmo vale para uma senha incorreta;
  • linhas 71–75: um servidor inacessível (nenhum servidor está escutando na porta 3399) também causa um erro de acesso;
  • testes de [incorrect query] (linha 80), [no bracket] (linha 89) e [NULL value] (linha 98): uma consulta que retorna apenas duas colunas, não retorna nenhuma linha ou tem uma coluna com o valor [NULL] causa uma [TaxException] com o código [DATA]. O último passa a consulta por meio de um argumento nomeado, [query = "…"], o que torna a chamada mais legível.

Os testes são executados com o seguinte comando, digitado na pasta [10-jdbc]:

> gradlew :TaxV6:tax-dao-mysql:test
MySqlTaxTests > unreachable server() PASSED

MySqlTaxTests > missing database() PASSED

MySqlTaxTests > incorrect password() PASSED

MySqlTaxTests > NULL value() PASSED

MySqlTaxTests > no bracket() PASSED

MySqlTaxTests > incorrect query() PASSED

MySqlTaxTests > tax calculation(boolean, int, int, int) > [1] married = "true", nbChildren = "2", salary = "60000", tax = "4282" PASSED

MySqlTaxTests > tax calculation(boolean, int, int, int) > [2] married = "true", nbChildren = "3", salary = "60000", tax = "2959" PASSED

MySqlTaxTests > tax calculation(boolean, int, int, int) > [3] married = "false", nbChildren = "0", salary = "60000", tax = "11519" PASSED

MySqlTaxTests > same brackets as the hardwired version() PASSED
(Gradle messages omitted, before and after the test reports)

O Gradle exibe uma linha por teste executado (a ordem de execução dos testes não é a mesma da ordem no arquivo), graças à configuração [testLogging { events("passed", "skipped", "failed") }] no arquivo [build.gradle.kts]. Para o teste parametrizado, cada linha lista o nome da função seguido dos tipos de seus parâmetros como vistos pela JVM ([(boolean, int, int, int)]: [Boolean] e [Int] do Kotlin são compilados para tipos primitivos), seguidos do número da execução entre colchetes e dos nomes e valores dos parâmetros, com esses valores entre aspas, como aparecem na anotação [@CsvSource] (Capítulo 6). Como cada conjunto de valores conta como um teste, os dez testes passaram.

10.11.5. Integração das camadas

O subprojeto [tax-ui-mysql] contém o programa que integra as camadas. Seu arquivo [build.gradle.kts] é o seguinte:

// TaxV6: o subprojeto tax-ui-mysql (integração das camadas)
plugins {
    kotlin("jvm")
    application
}

group = "examples.tax"
version = "6.0"

repositories {
    mavenCentral()
}

dependencies {
    // a nova camada [dao] (e, transitivamente, o driver JDBC do MySQL em tempo de execução)
    implementation(project(":TaxV6:tax-dao-mysql"))
    // as camadas [business] e [ui] da versão 5, inalteradas (composite build ../06-architectures)
    implementation("examples.tax:tax-business:5.0")
    implementation("examples.tax:tax-ui:5.0")
    // o contêiner de injeção de dependência do Spring
    implementation("org.springframework:spring-context:7.0.9")
}

kotlin {
    jvmToolchain(25)
    compilerOptions {
        allWarningsAsErrors = true
        javaParameters = true
    }
}

application {
    // gradlew -q :TaxV6:tax-ui-mysql:run [--args="tax-base.properties"]
    mainClass = "tax.ui.mysql.MainKt"
}

tasks.named<JavaExec>("run") {
    // o programa lê o teclado
    standardInput = System.`in`
}

// no Windows, a saída de tela do programa iniciado pelo Gradle (run, bootRun...) é codificada em UTF-8,
// caso contrário os caracteres acentuados ficam corrompidos no console do IntelliJ IDEA ou no terminal
tasks.withType<JavaExec>().configureEach {
    jvmArgs("-Dstdout.encoding=UTF-8", "-Dstderr.encoding=UTF-8")
}
  • Linhas 2–4: o plugin Kotlin e o plugin [application], que fornece a tarefa [run]. O plugin [kotlin("plugin.spring")] não é necessário: o programa não tem classes anotadas pelo Spring ([@Configuration]…), pois registra as camadas por programa;
  • Linha 16: a nova camada [dao], um subprojeto de mesma estrutura, identificado por seu caminho. Por transitividade, o driver JDBC do MySQL — que depende de [runtimeOnly] neste subprojeto — é incluído nas bibliotecas da aplicação em tempo de execução;
  • linhas 18–19: as camadas [business] e [ui] da versão 5, identificadas por suas coordenadas e fornecidas pelo build composto. O subprojeto [tax-ui] contém os programas principais da versão 5, mas usamos apenas seu objeto [Dialog];
  • linha 21: o contêiner de injeção de dependência do Spring, como na versão 5;
  • linhas 32–34: o programa executado por [gradlew run]. O arquivo [Main.kt] do pacote [tax.ui.mysql] gera a classe [tax.ui.mysql.MainKt];
  • linhas 37–39: o programa lê do teclado;
  • linhas 42–45: os programas iniciados pelo Gradle escrevem sua saída em UTF-8, para que os caracteres acentuados sejam exibidos corretamente no Windows (Capítulo 2).

O arquivo de configuração [tax.properties] é o seguinte:

1
2
3
4
5
6
# configuração da aplicação [Tax], versão 6: a camada [dao] lê um banco de dados
dao.implementation=MySqlTax
dao.url=jdbc:mysql://localhost:3306/dbtaxes
dao.user=admtaxes
dao.password=pwdtaxes
dao.query=select limits, coeffr, coeffn from brackets order by id
  • Linha 2: a implementação da camada [dao];
  • linhas 3–5: a URL JDBC do banco de dados [dbtaxes] e as credenciais de [admtaxes];
  • linha 6: a consulta que retorna as faixas. Incluí-la na configuração permite usar outro banco de dados — mesmo um com tabelas ou colunas de nomes diferentes — sem modificar o código.

O programa [Main.kt] é o programa da versão 5, exceto pelo registro da camada [dao]:

package tax.ui.mysql

import tax.dao.HardwiredTax
import tax.dao.ITaxDao
import tax.dao.MySqlTax
import tax.entities.TaxException
import tax.business.ITaxBusiness
import tax.business.TaxBusiness
import tax.ui.Dialog
import java.util.Properties
import kotlin.io.path.Path
import kotlin.io.path.bufferedReader
import kotlin.system.exitProcess
import org.springframework.beans.factory.getBean
import org.springframework.context.support.GenericApplicationContext
import org.springframework.context.support.registerBean

// a aplicação [Tax], versão 6: a camada [dao] lê as faixas de imposto de um banco de dados
fun main(args: Array<String>) {
    // o arquivo de configuração: tax.properties por padrão, ou o passado como argumento
    val configFile = args.firstOrNull() ?: "tax.properties"

    // integração das camadas
    val business: ITaxBusiness = try {
        // leitura da configuração, na pasta atual
        val config = Properties()
        Path(configFile).bufferedReader().use { config.load(it) }
        val implementation = config.getProperty("dao.implementation", "MySqlTax")
        val url = config.getProperty("dao.url")
            ?: throw TaxException("The [dao.url] parameter is missing from the configuration")
        val user = config.getProperty("dao.user", "")
        val password = config.getProperty("dao.password", "")
        val query = config.getProperty("dao.query", MySqlTax.DEFAULT_QUERY)

        // o contêiner Spring: as camadas são registradas nele
        val context = GenericApplicationContext()
        // a camada [dao]: a implementação é escolhida pela configuração
        context.registerBean<ITaxDao> {
            when (implementation) {
                "MySqlTax" -> MySqlTax(url, user, password, query)
                "HardwiredTax" -> HardwiredTax()
                else -> throw TaxException("Unknown implementation [$implementation] of the [dao] layer")
            }
        }
        // a camada [business] da versão 5: o contêiner passará a camada [dao] ao seu construtor
        context.registerBean<TaxBusiness>()

        // inicialização do contêiner: ele cria a camada [dao] e depois a camada [business]
        context.refresh()
        context.getBean<ITaxBusiness>()
    } catch (e: Exception) {
        // exibição do erro e fim do programa
        Dialog.displayError(e)
        exitProcess(1)
    }

    // o diálogo com o usuário: a camada [ui] da versão 5
    Dialog.run(business)
}
  • linha 1: o programa está em um pacote específico da versão 6, [tax.ui.mysql], para evitar confusão com o programa [tax.ui.MainKt] da versão 5, que também está presente no classpath;
  • linha 21: o arquivo de configuração — [tax.properties] por padrão, ou o passado como argumento — [firstOrNull] retorna [null] se o array de argumentos estiver vazio, e o operador Elvis então retorna o nome padrão;
  • linha 24: como na versão 5, [try] é uma expressão: seu valor é o da última expressão do bloco, a camada [business] obtida do contêiner (linha 50). O bloco [catch] não retorna um valor: [exitProcess] é do tipo [Nothing], portanto nunca devolve o controle;
  • linhas 26–27: leitura do arquivo de propriedades na pasta atual;
  • linhas 28–33: os parâmetros da camada [dao]. [getProperty] é um método Java que retorna [null] para uma propriedade ausente: seu tipo em Kotlin é [String!]. Para a URL (linhas 29–30), o operador Elvis seguido de um [throw] trata a ausência como erro, e [url] é do tipo [String] (não nulo). Os outros parâmetros têm um valor padrão, que para esta consulta é a constante [DEFAULT_QUERY] proveniente de [MySqlTax];
  • linhas 38–42: a camada [dao] é registrada no contêiner do Spring sob a interface [ITaxDao], com uma lambda que a cria conforme a configuração: um [MySqlTax] (linha 40) ou, para comparação, a versão “fixa no código” da versão 5 (linha 41). [registerBean] é aqui a função de extensão Kotlin do Spring, com um parâmetro de tipo reified, usada no Capítulo 6. As implementações [FileTax] e [JsonTax] da versão 5 poderiam ser adicionadas da mesma maneira: elas estão no classpath;
  • linha 46: a camada [business] da versão 5;
  • Linha 49: quando o contêiner é iniciado, ele cria a camada [dao] e, portanto, lê o banco de dados, seguida da camada [business];
  • Linhas 51–54: um erro de configuração ou de acesso ao banco de dados é exibido pela função [displayError] do objeto [Dialog] da versão 5, e o programa termina;
  • linha 58: o diálogo da versão 5, inalterado.

Para executar a aplicação, inicie a tarefa [run] do subprojeto [tax-ui-mysql] na pasta [10-jdbc]; o Gradle compila a versão 5 e a nova camada [dao] conforme necessário. A tarefa [run] executa o programa na pasta do subprojeto, que contém os arquivos de configuração. Execute-a com o arquivo [tax.properties] (as entradas são reinseridas após as perguntas, como nos capítulos anteriores):

1
2
3
4
5
6
7
8
> gradlew -q :TaxV6:tax-ui-mysql:run
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 2 60000
Tax=4282 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 3 60000
Tax=2959 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: n 0 60000
Tax=11519 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop:

Vemos os valores de controle do curso: 4.282 euros para um casal com dois filhos, 2.959 euros para um casal com três filhos e 11.519 euros para uma pessoa solteira sem filhos, com um salário anual de 60.000 euros.

Com o arquivo [tax-base.properties], em que a URL se refere a um banco de dados [dbtaxesunknown] (a opção [--args] da tarefa [run] passa os argumentos do programa):

> gradlew -q :TaxV6:tax-ui-mysql:run --args="tax-base.properties"
Oct 04, 2026 12:49:32 PM org.springframework.context.support.GenericApplicationContext refresh
WARNING: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
Exception chain:
----------------------------------------
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
----------------------------------------
tax.entities.TaxException: Error accessing the tax brackets database
----------------------------------------
java.sql.SQLSyntaxErrorException: Access denied for user 'admtaxes'@'localhost' to database 'dbtaxesunknown'
----------------------------------------
  • linhas 2–3: uma mensagem de log do Spring escrita na saída de erro: o contêiner informa que a criação da camada [dao] falhou. Isso já acontecia na versão 5;
  • linhas 4–11: a cadeia de exceções exibida por [Dialog.displayError]: o contêiner do Spring encapsulou a exceção [TaxException] lançada pela camada [dao] — que por sua vez encapsula a exceção [SQLException] do driver — em uma exceção [BeanCreationException]. O usuário [admtaxes] tem permissões apenas sobre o banco de dados [dbtaxes]: o MySQL nega acesso a qualquer outro banco de dados, exista ou não (linha 10).

O programa termina com o código 1 ([exitProcess(1)]): o Gradle então considera que a tarefa [run] falhou e acrescenta suas próprias mensagens, que não são reproduzidas aqui.

Com o arquivo [tax-password.properties], que tem uma senha incorreta:

> gradlew -q :TaxV6:tax-ui-mysql:run --args="tax-password.properties"
Oct 04, 2026 12:49:36 PM org.springframework.context.support.GenericApplicationContext refresh
WARNING: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
Exception chain:
----------------------------------------
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
----------------------------------------
tax.entities.TaxException: Error accessing the tax brackets database
----------------------------------------
java.sql.SQLException: Access denied for user 'admtaxes'@'localhost' (using password: YES)
----------------------------------------

Com o arquivo [tax-server.properties], em que a URL especifica a porta 3399, na qual nenhum servidor está escutando (é o que aconteceria se o servidor MySQL fosse parado):

> gradlew -q :TaxV6:tax-ui-mysql:run --args="tax-server.properties"
Oct 04, 2026 12:49:39 PM org.springframework.context.support.GenericApplicationContext refresh
WARNING: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
Exception chain:
----------------------------------------
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
----------------------------------------
tax.entities.TaxException: Error accessing the tax brackets database
----------------------------------------
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
----------------------------------------
com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
----------------------------------------
java.net.ConnectException: Connection refused (connect failed)
----------------------------------------

A cadeia de exceções mostra aqui que a própria exceção do driver encapsula outras exceções, até a exceção de rede [ConnectException]: a conexão foi recusada.

Com o arquivo [tax-query.properties], cuja consulta solicita apenas as colunas [limits] e [coeffr]:

> gradlew -q :TaxV6:tax-ui-mysql:run --args="tax-query.properties"
Oct 04, 2026 12:49:44 PM org.springframework.context.support.GenericApplicationContext refresh
WARNING: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
Exception chain:
----------------------------------------
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tax.dao.ITaxDao': Instantiation of supplied bean failed
----------------------------------------
tax.entities.TaxException: The tax brackets of the database are incorrect
----------------------------------------
java.sql.SQLException: Column Index out of range, 3 > 2.
----------------------------------------

Em todos os casos, a camada [dao] encapsulou a exceção original em uma [TaxException], que é exibida pela função [Dialog.displayError] da versão 5, e o programa termina.

10.11.6. Trocar de banco de dados

A camada [dao] ([MySqlTax]) usa apenas as interfaces do JDBC: apesar do nome, ela não é específica do MySQL (um nome como [JdbcTax] seria mais apropriado). Para usar um banco de dados PostgreSQL (ou SQL Server, ou Oracle), basta:

  • criar a tabela de faixas em um banco de dados PostgreSQL — por exemplo, uma tabela chamada [data] com as colunas [data1], [data2], [data3], para destacar a vantagem de colocar a consulta na configuração;
  • adicionar a dependência do driver JDBC do PostgreSQL, [runtimeOnly("org.postgresql:postgresql:…")] (ou [com.microsoft.sqlserver:mssql-jdbc], [com.oracle.database.jdbc:ojdbc11]);
  • escrever um arquivo de configuração semelhante a [tax.properties], como o arquivo [tax-postgresql.properties] do subprojeto:
1
2
3
4
5
6
# a mesma aplicação com um banco de dados PostgreSQL (driver org.postgresql:postgresql a adicionar em build.gradle.kts)
dao.implementation=MySqlTax
dao.url=jdbc:postgresql://localhost:5432/dbtaxes
dao.user=admtaxes
dao.password=pwdtaxes
dao.query=select data1, data2, data3 from data order by id

Apenas o MySQL está instalado na máquina usada para escrever este curso: não implementamos essa configuração. As camadas [business] e [ui], a classe [MySqlTax] e o programa [Main.kt] não precisam ser modificados: mudam apenas a configuração e o driver no classpath. Observe que a consulta de [MySqlTax] não tem parâmetros e usa apenas recursos padrão do SQL: é isso que permite que seja executada por qualquer SGBD. Uma camada [dao] mais rica em recursos (buscas, atualizações) logo encontraria as características específicas do SQL de cada SGBD, como discutido acima: é aí que um ORM como o Hibernate assume.

10.12. Conclusão

Este capítulo tratou do acesso a bancos de dados com JDBC a partir de Kotlin:

  • o JDBC é um conjunto de interfaces ([Connection], [Statement], [PreparedStatement], [ResultSet], [DataSource]…) dos pacotes [java.sql] e [javax.sql] , implementadas por um driver específico de cada SGBD (MySQL Connector/J para o MySQL), que o [DriverManager] seleciona com base em na URL JDBC do banco de dados. Kotlin não tem API própria: usa o JDBC diretamente, com seus acessores tratados como propriedades ([connection.autoCommit = false]);
  • uma conexão é aberta o mais tarde possível e fechada o mais cedo possível, por meio da função [use]; um pool de conexões como o HikariCP, por meio da interface [DataSource], torna essa prática barata;
  • [executeQuery] executa um [select] e retorna um [ResultSet], que é percorrido linha a linha com [next], lendo as colunas, numeradas a partir de 1, com os métodos [getXxx]; funções de extensão ([toList], [asSequence], [toArticle]) convertem as linhas em objetos Kotlin. [executeUpdate] executa um [insert], [update], ou [delete] e retorna o número de linhas modificadas;
  • os erros são [SQLException], que trazem um código de erro específico do SGBD e um código SQLSTATE padronizado. Eles são tratados como em Java, não em Kotlin, que não impõe exigências: cabe ao desenvolvedor decidir onde interceptá-los, tipicamente na camada [dao], que os encapsula em exceções próprias;
  • os valores externos são sempre passados pelos parâmetros de um [PreparedStatement], nunca inseridos no texto da consulta — nem por concatenação, nem por um template de string — o que protege contra injeção de SQL;
  • as transações ([autoCommit = false], [commit], [rollback], savepoints) tornam atômica uma sequência de comandos SQL; uma função de ordem superior como a nossa [connection.transaction { … }] esconde o funcionamento interno;
  • o JDBC é bloqueante: as threads virtuais permitem atender muitos usuários com código JDBC padrão, dentro dos limites do tamanho do pool; as corrotinas incorporam isso encapsulando as chamadas em [withContext(Dispatchers.IO)], de preferência com o paralelismo limitado ao tamanho do pool. Os lotes ([addBatch], [executeBatch]) reduzem o número de trocas com o servidor;
  • o modo offline ([CachedRowSet]) existe, mas trabalhar com objetos em memória é hoje o domínio dos ORMs.

A versão 6 da aplicação [Tax] demonstrou que, graças à arquitetura em camadas, passar de um arquivo para um banco de dados exige apenas escrever uma nova camada [dao] e modificar a configuração; o sistema de build Gradle nos permitiu reutilizar a versão 5 sem copiá-la nem instalá-la. O próximo capítulo criará uma sétima versão dessa camada usando o Hibernate, que gera ele mesmo os comandos SQL.

Entre o JDBC “puro” deste capítulo e um ORM completo como o Hibernate, o ecossistema Kotlin e Java oferece bibliotecas intermediárias que mantêm o controle sobre o SQL eliminando o código repetitivo:

  • Exposed, a biblioteca de acesso a bancos de dados da JetBrains, escrita em Kotlin: as tabelas são descritas por objetos Kotlin, e as consultas são escritas em uma linguagem dedicada e tipada (DSL), [Articles.selectAll().where { Articles.price less 200 }], dentro de blocos [transaction { … }]; ela também oferece uma camada de objetos (DAO) mais próxima de um ORM;
  • jOOQ, que gera classes Java a partir do esquema do banco de dados, utilizáveis a partir de Kotlin, e permite escrever consultas SQL verificadas em tempo de compilação;
  • Spring JDBC, cuja interface [JdbcClient] (Spring 6.1) executa uma consulta parametrizada e transforma suas linhas em objetos em uma única instrução fluente, ao mesmo tempo que gerencia conexões, fechamentos e exceções.

Para saber mais: