PDO Avançado, Prepared Statements e Padrão Repository

PDO (PHP Data Objects) é a camada de abstração de banco de dados nativa do PHP. Ela permite escrever código que funciona com MySQL, PostgreSQL, SQLite e outros SGBDs com mudanças mínimas. Mas a maioria dos desenvolvedores usa apenas a superfície do PDO — conectar, executar uma query, buscar resultados. O uso profissional vai muito além: configuração correta de atributos de conexão, prepared statements reutilizáveis, transações com savepoints, modos de fetch tipados e o padrão Repository para isolar a lógica de acesso a dados do restante da aplicação.

Este artigo cobre o PDO do jeito que é usado em sistemas de produção: sem vulnerabilidades de SQL injection, com tratamento correto de erros, performance otimizada e uma camada de abstração que permite trocar o banco de dados sem reescrever a lógica de negócio.

Configuração correta da conexão PDO

A maioria dos tutoriais mostra o mínimo para conectar. Em produção, há uma série de atributos que precisam ser configurados explicitamente para garantir segurança, performance e comportamento previsível.

<?php
declare(strict_types=1);

function criarConexao(
    string $host,
    string $banco,
    string $usuario,
    string $senha,
    int    $porta = 3306,
): \PDO {
    $dsn = "mysql:host={$host};port={$porta};dbname={$banco};charset=utf8mb4";

    $opcoes = [
        // Lança PDOException em vez de retornar false — essencial
        \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,

        // Retorna arrays associativos por padrão
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,

        // Desativa emulação de prepared statements — usa prepared real do MySQL
        // Mais seguro e permite tipos corretos (int retorna como int, não string)
        \PDO::ATTR_EMULATE_PREPARES   => false,

        // Timeout de conexão em segundos
        \PDO::ATTR_TIMEOUT            => 5,

        // Desativa autocommit — transações explícitas
        // \PDO::ATTR_AUTOCOMMIT      => false, // ative se usar transações por padrão

        // MySQL específico: modo estrito — rejeita dados truncados/inválidos.
        // Atenção: atribuir sql_mode direto APAGA os modos que já vinham
        // por padrão (ONLY_FULL_GROUP_BY, NO_ZERO_DATE, NO_ENGINE_SUBSTITUTION).
        // Concatene em vez de substituir.
        \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci,
                                          sql_mode = CONCAT(@@sql_mode, ',STRICT_ALL_TABLES')",
    ];

    try {
        $pdo = new \PDO($dsn, $usuario, $senha, $opcoes);
        return $pdo;
    } catch (\PDOException $e) {
        // NÃO exponha detalhes de conexão em produção
        // Logue $e->getMessage() internamente, relance genérico
        throw new \RuntimeException(
            "Falha ao conectar ao banco de dados.",
            (int) $e->getCode(),
            $e
        );
    }
}

// Uso com variáveis de ambiente — nunca hardcode credenciais
$pdo = criarConexao(
    host:    getenv('DB_HOST')     ?: 'localhost',
    banco:   getenv('DB_DATABASE') ?: 'meuapp',
    usuario: getenv('DB_USERNAME') ?: 'root',
    senha:   getenv('DB_PASSWORD') ?: '',
);

Prepared statements — segurança e performance

Prepared statements são a defesa primária contra SQL injection. O banco compila a query uma vez e a executa com parâmetros separados — os parâmetros nunca são interpolados na query, então valores maliciosos não alteram a estrutura SQL. Um segundo benefício: quando a mesma query é executada múltiplas vezes, o banco reutiliza o plano de execução compilado.

<?php
declare(strict_types=1);

// ── PARÂMETROS POSICIONAIS (?) ────────────────────────────────────────
// Simples, mas a ordem importa
$stmt = $pdo->prepare("
    SELECT id, nome, email, criado_em
    FROM usuarios
    WHERE ativo = ? AND perfil = ?
    ORDER BY nome
    LIMIT ?
");

// execute() recebe array de valores na ordem dos ?
$stmt->execute([true, 'admin', 50]);
$usuarios = $stmt->fetchAll(); // array de arrays associativos

// ── PARÂMETROS NOMEADOS (:nome) ────────────────────────────────────────
// Mais legível, a ordem não importa, pode repetir o mesmo parâmetro
$stmt = $pdo->prepare("
    SELECT *
    FROM produtos
    WHERE categoria = :categoria
      AND preco BETWEEN :min AND :max
      AND estoque > :estoque_minimo
    ORDER BY preco
");

$stmt->execute([
    'categoria'       => 'Eletrônicos',
    'min'             => 100.00,
    'max'             => 5000.00,
    'estoque_minimo'  => 0,
]);
$produtos = $stmt->fetchAll();

// ── REUTILIZANDO O MESMO PREPARED STATEMENT ───────────────────────────
// Prepara uma vez, executa muitas vezes — performance real
$stmtInserir = $pdo->prepare("
    INSERT INTO logs (usuario_id, acao, ip, criado_em)
    VALUES (:usuario_id, :acao, :ip, NOW())
");

$eventos = [
    ['usuario_id' => 1, 'acao' => 'login',   'ip' => '192.168.1.1'],
    ['usuario_id' => 1, 'acao' => 'busca',   'ip' => '192.168.1.1'],
    ['usuario_id' => 2, 'acao' => 'logout',  'ip' => '10.0.0.5'],
];

foreach ($eventos as $evento) {
    $stmtInserir->execute($evento);
    // lastInsertId() após cada execute() se precisar do ID gerado
}

// ── BINDPARAM vs BINDVALUE ─────────────────────────────────────────────
// bindParam() — vincula por referência (valor é lido na execução)
// bindValue() — vincula por valor (valor é capturado agora)
$stmtBuscar = $pdo->prepare("SELECT * FROM pedidos WHERE id = :id");

// bindValue com tipo explícito — garante que o banco recebe um inteiro
$stmtBuscar->bindValue(':id', 42, \PDO::PARAM_INT);
$stmtBuscar->execute();
$pedido = $stmtBuscar->fetch();

// bindParam — útil em loops onde a variável muda
$id = 0;
$stmtBuscar->bindParam(':id', $id, \PDO::PARAM_INT);
foreach ([1, 2, 3, 4, 5] as $id) {
    $stmtBuscar->execute(); // usa o valor atual de $id
    $resultado = $stmtBuscar->fetch();
}

Modos de fetch — controlando o formato dos resultados

O PDO oferece múltiplos modos de fetch que determinam como os dados são retornados. Escolher o modo correto reduz código desnecessário de mapeamento.

<?php
declare(strict_types=1);

$stmt = $pdo->prepare("SELECT id, nome, email, preco FROM produtos WHERE ativo = 1");
$stmt->execute();

// FETCH_ASSOC — array associativo (padrão configurado)
$linha = $stmt->fetch(\PDO::FETCH_ASSOC);
// ['id' => 1, 'nome' => 'Teclado', 'email' => null, 'preco' => '350.00']

// FETCH_OBJ — objeto stdClass com propriedades
$linha = $stmt->fetch(\PDO::FETCH_OBJ);
// $linha->id, $linha->nome, $linha->preco

// FETCH_CLASS — mapeia diretamente para uma classe
// As propriedades são preenchidas ANTES de __construct() ser chamado
class Produto
{
    public int    $id    = 0;
    public string $nome  = '';
    public float  $preco = 0.0;
}

$stmt->setFetchMode(\PDO::FETCH_CLASS, Produto::class);
/** @var Produto $produto */
$produto = $stmt->fetch();
echo $produto->nome  . "\n"; // Teclado
echo $produto->preco . "\n"; // 350

// fetchAll com FETCH_CLASS
$produtos = $stmt->fetchAll(\PDO::FETCH_CLASS, Produto::class);
// array de objetos Produto

// FETCH_COLUMN — retorna apenas a primeira coluna como array simples
$stmt = $pdo->prepare("SELECT nome FROM produtos ORDER BY nome");
$stmt->execute();
$nomes = $stmt->fetchAll(\PDO::FETCH_COLUMN);
// ['Mouse', 'Teclado', 'Monitor']

// FETCH_KEY_PAIR — array [primeira_coluna => segunda_coluna]
$stmt = $pdo->prepare("SELECT id, nome FROM produtos");
$stmt->execute();
$mapa = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);
// [1 => 'Teclado', 2 => 'Mouse', 3 => 'Monitor']

// FETCH_UNIQUE — primeira coluna como chave, resto como array/objeto
$stmt = $pdo->prepare("SELECT id, nome, preco FROM produtos");
$stmt->execute();
$porId = $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_ASSOC);
// [1 => ['nome' => 'Teclado', 'preco' => '350.00'], ...]

Transações com savepoints

Transações garantem que um conjunto de operações seja atômico — ou todas têm sucesso, ou nenhuma. Savepoints permitem desfazer apenas parte de uma transação, criando pontos de restauração intermediários.

<?php
declare(strict_types=1);

function transferirSaldo(\PDO $pdo, int $deId, int $paraId, float $valor): void
{
    if ($valor <= 0) {
        throw new \InvalidArgumentException("Valor de transferência deve ser positivo.");
    }

    $pdo->beginTransaction();
    try {
        // Trava as duas linhas de uma vez e sempre na mesma ordem de id.
        // Com FOR UPDATE só na origem, duas transferências simúltaneas em
        // sentidos opostos (A para B e B para A) travam uma linha cada e entram
        // em deadlock ao tentar atualizar a outra.
        $stmt = $pdo->prepare(
            "SELECT id, saldo FROM contas WHERE id IN (?, ?) ORDER BY id FOR UPDATE"
        );

        $stmt->execute([$deId, $paraId]);
        $saldos = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);

        if (!isset($saldos[$deId], $saldos[$paraId])) {
            throw new \RuntimeException("Conta de origem ou de destino inexistente.");
        }

        $saldoOrigem = (float) $saldos[$deId];

        if ($saldoOrigem < $valor) {
            throw new \RuntimeException(
                "Saldo insuficiente: disponível R$ {$saldoOrigem}, solicitado R$ {$valor}"
            );
        }

        // Débito
        $pdo->prepare("UPDATE contas SET saldo = saldo - ? WHERE id = ?")
            ->execute([$valor, $deId]);

        // Crédito
        $pdo->prepare("UPDATE contas SET saldo = saldo + ? WHERE id = ?")
            ->execute([$valor, $paraId]);

        // Registra a transferência
        $pdo->prepare(
            "INSERT INTO transferencias (de_id, para_id, valor, criado_em)
             VALUES (?, ?, ?, NOW())"
        )->execute([$deId, $paraId, $valor]);

        $pdo->commit();
    } catch (\Throwable $e) {
        // Sem o inTransaction(), um erro que já tenha encerrado a transação
        // faz o rollBack() lançar PDOException e a exceção original se perde.
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e; // relança para o chamador decidir o que fazer
    }
}

// Savepoints — controle granular dentro de uma transação
function processarPedidoComSavepoint(\PDO $pdo, array $pedido): void
{
    $pdo->beginTransaction();
    try {
        // Insere o pedido
        $pdo->prepare("INSERT INTO pedidos (cliente_id, total) VALUES (?, ?)")
            ->execute([$pedido['cliente_id'], $pedido['total']]);
        $pedidoId = (int) $pdo->lastInsertId();

        // Savepoint antes dos itens — se falhar, desfaz só os itens
        $pdo->exec("SAVEPOINT sp_itens");
        try {
            foreach ($pedido['itens'] as $item) {
                $pdo->prepare(
                    "INSERT INTO pedido_itens (pedido_id, produto_id, quantidade, preco)
                     VALUES (?, ?, ?, ?)"
                )->execute([$pedidoId, $item['produto_id'], $item['quantidade'], $item['preco']]);

                // Atualiza estoque
                $affected = $pdo->prepare(
                    "UPDATE produtos SET estoque = estoque - ?
                     WHERE id = ? AND estoque >= ?"
                );
                $affected->execute([$item['quantidade'], $item['produto_id'], $item['quantidade']]);

                if ($affected->rowCount() === 0) {
                    throw new \RuntimeException(
                        "Estoque insuficiente para produto #{$item['produto_id']}"
                    );
                }
            }
            $pdo->exec("RELEASE SAVEPOINT sp_itens");
        } catch (\RuntimeException $e) {
            // Desfaz só os itens, mantém o pedido
            $pdo->exec("ROLLBACK TO SAVEPOINT sp_itens");
            // Marca o pedido como falho em vez de cancelar tudo
            $pdo->prepare("UPDATE pedidos SET status = 'falhou' WHERE id = ?")
                ->execute([$pedidoId]);
        }

        $pdo->commit();
    } catch (\Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

O padrão Repository

O padrão Repository isola a lógica de acesso a dados do restante da aplicação. Serviços de domínio não sabem se os dados vêm de MySQL, PostgreSQL, uma API REST ou um arquivo JSON — eles apenas chamam métodos do repositório e recebem objetos de domínio.

<?php
declare(strict_types=1);

// Entidade de domínio — sem dependência de PDO ou SQL
final class Produto
{
    public function __construct(
        public readonly int    $id,
        public readonly string $nome,
        public readonly float  $preco,
        public readonly int    $estoque,
        public readonly bool   $ativo,
    ) {}

    public function comEstoque(int $novo): static
    {
        return new static($this->id, $this->nome, $this->preco, $novo, $this->ativo);
    }
}

// Interface do repositório — contrato de domínio, sem SQL
interface ProdutoRepositorioInterface
{
    public function buscarPorId(int $id): ?Produto;
    /** @return Produto[] */
    public function buscarAtivos(): array;
    /** @return Produto[] */
    public function buscarPorCategoria(string $categoria, int $limite = 20): array;
    public function salvar(Produto $produto): Produto;
    public function deletar(int $id): bool;
}

// Implementação PDO — traduz dados relacionais para objetos de domínio
class ProdutoRepositorioPDO implements ProdutoRepositorioInterface
{
    public function __construct(private readonly \PDO $pdo) {}

    public function buscarPorId(int $id): ?Produto
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, nome, preco, estoque, ativo FROM produtos WHERE id = ?"
        );
        $stmt->execute([$id]);
        $linha = $stmt->fetch();
        return $linha ? $this->hidratar($linha) : null;
    }

    public function buscarAtivos(): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, nome, preco, estoque, ativo FROM produtos WHERE ativo = 1 ORDER BY nome"
        );
        $stmt->execute();
        return array_map($this->hidratar(...), $stmt->fetchAll());
    }

    public function buscarPorCategoria(string $categoria, int $limite = 20): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT p.id, p.nome, p.preco, p.estoque, p.ativo
             FROM produtos p
             JOIN categorias c ON c.id = p.categoria_id
             WHERE c.slug = :categoria AND p.ativo = 1
             ORDER BY p.nome
             LIMIT :limite"
        );
        $stmt->bindValue(':categoria', $categoria);
        $stmt->bindValue(':limite',    $limite, \PDO::PARAM_INT);
        $stmt->execute();
        return array_map($this->hidratar(...), $stmt->fetchAll());
    }

    public function salvar(Produto $produto): Produto
    {
        if ($produto->id === 0) {
            // INSERT
            $stmt = $this->pdo->prepare(
                "INSERT INTO produtos (nome, preco, estoque, ativo)
                 VALUES (:nome, :preco, :estoque, :ativo)"
            );
            $stmt->execute([
                'nome'    => $produto->nome,
                'preco'   => $produto->preco,
                'estoque' => $produto->estoque,
                'ativo'   => $produto->ativo,
            ]);
            $novoId = (int) $this->pdo->lastInsertId();
            return new Produto($novoId, $produto->nome, $produto->preco, $produto->estoque, $produto->ativo);
        }

        // UPDATE
        $this->pdo->prepare(
            "UPDATE produtos
             SET nome = :nome, preco = :preco, estoque = :estoque, ativo = :ativo
             WHERE id = :id"
        )->execute([
            'nome'    => $produto->nome,
            'preco'   => $produto->preco,
            'estoque' => $produto->estoque,
            'ativo'   => $produto->ativo,
            'id'      => $produto->id,
        ]);
        return $produto;
    }

    public function deletar(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM produtos WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->rowCount() > 0;
    }

    // Hidratação — transforma array do banco em objeto de domínio
    private function hidratar(array $linha): Produto
    {
        return new Produto(
            id:      (int)  $linha['id'],
            nome:           $linha['nome'],
            preco:   (float) $linha['preco'],
            estoque: (int)  $linha['estoque'],
            ativo:   (bool) $linha['ativo'],
        );
    }
}

// Serviço de domínio — não sabe nada sobre PDO ou SQL
class EstoqueService
{
    public function __construct(
        private readonly ProdutoRepositorioInterface $repositorio,
    ) {}

    public function reduzirEstoque(int $produtoId, int $quantidade): Produto
    {
        $produto = $this->repositorio->buscarPorId($produtoId);
        if ($produto === null) {
            throw new \RuntimeException("Produto #{$produtoId} não encontrado.");
        }
        if ($produto->estoque < $quantidade) {
            throw new \RuntimeException(
                "Estoque insuficiente: disponível {$produto->estoque}, solicitado {$quantidade}."
            );
        }
        $atualizado = $produto->comEstoque($produto->estoque - $quantidade);
        return $this->repositorio->salvar($atualizado);
    }
}

O que separa o PDO de tutorial do PDO de produção não é nenhuma função exótica: é a configuração explícita dos atributos de conexão, o hábito de nunca interpolar valor dentro da query e a disciplina de manter o SQL atrás de uma interface. Um repositório que devolve objetos de domínio, e não arrays soltos, muda o resto do sistema por efeito dominó: o serviço deixa de conhecer o nome das colunas, e trocar o banco vira detalhe de infraestrutura.

Duas linhas merecem desconfiança toda vez que aparecerem no seu código. O sql_mode atribuído direto no INIT_COMMAND apaga os modos que o servidor já trazia — concatene com @@sql_mode. E o rollBack() solto dentro do catch troca a exceção que interessa por um "there is no active transaction" quando a transação já havia caído.

Fontes e leituras recomendadas

• PHP Manual — PDO: https://www.php.net/manual/pt_BR/book.pdo.php
• PHP Manual — Prepared Statements: https://www.php.net/manual/pt_BR/pdo.prepared-statements.php
• PHP Manual — PDO::FETCH_* constants: https://www.php.net/manual/pt_BR/pdo.constants.php
• Fowler, M. — Patterns of Enterprise Application Architecture — Repository Pattern: https://www.martinfowler.com/eaaCatalog/repository.html
• PHP: The Right Way — Databases: https://phptherightway.com/#databases

Exercícios

Exercício 1

Crie uma classe Conexao com método estático obter(): PDO que retorna uma instância singleton configurada com todos os atributos de produção discutidos no artigo. Use variáveis de ambiente para as credenciais.

<?php

declare(strict_types=1);

final class Conexao
{
    private static ?PDO $instancia = null;

    // Singleton de verdade: ninguém instancia nem clona de fora.
    private function __construct() {}
    private function __clone() {}

    public static function obter(): PDO
    {
        if (self::$instancia instanceof PDO) {
            return self::$instancia;
        }

        $dsn = sprintf(
            'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4',
            getenv('DB_HOST') ?: '127.0.0.1',
            getenv('DB_PORT') ?: '3306',
            getenv('DB_NAME') ?: throw new RuntimeException('DB_NAME não definida'),
        );

        self::$instancia = new PDO(
            $dsn,
            getenv('DB_USER') ?: throw new RuntimeException('DB_USER não definida'),
            getenv('DB_PASS') ?: '',
            [
                // Erro vira exceção, em vez de retorno falso silencioso.
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                // O ponto mais importante da lista: sem emulação, quem monta a
                // query com os parâmetros é o servidor, não o driver.
                PDO::ATTR_EMULATE_PREPARES   => false,
                // INT no banco chega como int no PHP, não como string.
                PDO::ATTR_STRINGIFY_FETCHES  => false,
            ],
        );

        return self::$instancia;
    }
}

// Uso:
$pdo = Conexao::obter();

Exercício 2

Implemente um UsuarioRepositorio com os métodos buscarPorEmail(string $email): ?Usuario, buscarPorId(int $id): ?Usuario, criar(string $nome, string $email, string $senhaHash): Usuario e atualizarUltimoLogin(int $id): void. Use prepared statements nomeados em todos os métodos.

<?php

declare(strict_types=1);

final class Usuario
{
    public function __construct(
        public readonly int $id,
        public readonly string $nome,
        public readonly string $email,
        public readonly string $senhaHash,
        public readonly ?DateTimeImmutable $ultimoLogin = null,
    ) {}

    public static function daLinha(array $linha): self
    {
        return new self(
            (int) $linha['id'],
            $linha['nome'],
            $linha['email'],
            $linha['senha_hash'],
            $linha['ultimo_login'] !== null
                ? new DateTimeImmutable($linha['ultimo_login'])
                : null,
        );
    }
}

final class UsuarioRepositorio
{
    public function __construct(private readonly PDO $pdo) {}

    private const CAMPOS = 'id, nome, email, senha_hash, ultimo_login';

    public function buscarPorEmail(string $email): ?Usuario
    {
        $stmt = $this->pdo->prepare(
            'SELECT ' . self::CAMPOS . ' FROM usuarios WHERE email = :email LIMIT 1'
        );
        $stmt->execute([':email' => $email]);
        $linha = $stmt->fetch();

        return $linha ? Usuario::daLinha($linha) : null;
    }

    public function buscarPorId(int $id): ?Usuario
    {
        $stmt = $this->pdo->prepare(
            'SELECT ' . self::CAMPOS . ' FROM usuarios WHERE id = :id LIMIT 1'
        );
        $stmt->execute([':id' => $id]);
        $linha = $stmt->fetch();

        return $linha ? Usuario::daLinha($linha) : null;
    }

    public function criar(string $nome, string $email, string $senhaHash): Usuario
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO usuarios (nome, email, senha_hash, criado_em)
             VALUES (:nome, :email, :senha_hash, NOW())'
        );

        try {
            $stmt->execute([
                ':nome'       => $nome,
                ':email'      => $email,
                ':senha_hash' => $senhaHash,
            ]);
        } catch (PDOException $e) {
            // 23000 = violação de constraint. Aqui, o UNIQUE do e-mail.
            if ($e->getCode() === '23000') {
                throw new RuntimeException("E-mail já cadastrado: {$email}", 0, $e);
            }
            throw $e;
        }

        return new Usuario((int) $this->pdo->lastInsertId(), $nome, $email, $senhaHash);
    }

    public function atualizarUltimoLogin(int $id): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE usuarios SET ultimo_login = NOW() WHERE id = :id'
        );
        $stmt->execute([':id' => $id]);
    }
}

Exercício 3

Implemente a função transferirPontos(\PDO $pdo, int $deId, int $paraId, int $pontos): void que usa uma transação para debitar pontos de um usuário e creditar em outro, garantindo que o saldo nunca fique negativo.

<?php

declare(strict_types=1);

function transferirPontos(\PDO $pdo, int $deId, int $paraId, int $pontos): void
{
    if ($pontos <= 0) {
        throw new InvalidArgumentException('A quantidade de pontos tem de ser positiva.');
    }

    if ($deId === $paraId) {
        throw new InvalidArgumentException('Origem e destino são o mesmo usuário.');
    }

    $pdo->beginTransaction();

    try {
        // A trava real está no WHERE, não numa leitura anterior: o próprio
        // banco recusa o débito que deixaria o saldo negativo. Entre um
        // SELECT de saldo e um UPDATE cabe outra transação — aqui, não cabe.
        $debito = $pdo->prepare(
            'UPDATE carteiras
                SET saldo = saldo - :pontos
              WHERE usuario_id = :id
                AND saldo >= :pontos'
        );
        $debito->execute([':pontos' => $pontos, ':id' => $deId]);

        if ($debito->rowCount() === 0) {
            throw new RuntimeException(
                "Saldo insuficiente ou carteira inexistente (usuário {$deId})."
            );
        }

        $credito = $pdo->prepare(
            'UPDATE carteiras SET saldo = saldo + :pontos WHERE usuario_id = :id'
        );
        $credito->execute([':pontos' => $pontos, ':id' => $paraId]);

        if ($credito->rowCount() === 0) {
            throw new RuntimeException("Carteira de destino inexistente ({$paraId}).");
        }

        $pdo->commit();
    } catch (Throwable $e) {
        // Sem rollback, a conexão fica com transação aberta e contamina
        // a próxima operação que a reaproveitar.
        $pdo->rollBack();
        throw $e;
    }
}

Exercício 4

Crie um RelatorioRepositorio com método produtosMaisVendidos(int $limite, \DateTimeImmutable $de, \DateTimeImmutable $ate): array que retorna produtos com a soma de quantidades vendidas em um período. Use FETCH_CLASS para hidratar os resultados.

<?php

declare(strict_types=1);

final class ProdutoVendido
{
    // O FETCH_CLASS escreve nestas propriedades pelo nome da coluna,
    // então os nomes têm de bater com os alias do SELECT.
    public int $id = 0;
    public string $nome = '';
    public int $quantidade = 0;
    public string $receita = '0.00';

    public function receitaFormatada(): string
    {
        return 'R$ ' . number_format((float) $this->receita, 2, ',', '.');
    }
}

final class RelatorioRepositorio
{
    public function __construct(private readonly PDO $pdo) {}

    /** @return ProdutoVendido[] */
    public function produtosMaisVendidos(
        int $limite,
        \DateTimeImmutable $de,
        \DateTimeImmutable $ate,
    ): array {
        $stmt = $this->pdo->prepare(
            'SELECT  pr.id                                    AS id,
                     pr.nome                                  AS nome,
                     SUM(ip.quantidade)                       AS quantidade,
                     SUM(ip.quantidade * ip.preco_unitario)   AS receita
               FROM  itens_pedido ip
               JOIN  pedidos  pe ON pe.id = ip.pedido_id
               JOIN  produtos pr ON pr.id = ip.produto_id
              WHERE  pe.criado_em BETWEEN :de AND :ate
                AND  pe.status = :status
           GROUP BY  pr.id, pr.nome
           ORDER BY  quantidade DESC
              LIMIT  :limite'
        );

        $stmt->bindValue(':de',     $de->format('Y-m-d H:i:s'));
        $stmt->bindValue(':ate',    $ate->format('Y-m-d H:i:s'));
        $stmt->bindValue(':status', 'pago');
        // LIMIT tem de ser inteiro. Com EMULATE_PREPARES = false o valor vai
        // ao servidor como string se você não disser PARAM_INT — e o MySQL
        // recusa `LIMIT '10'` com erro de sintaxe.
        $stmt->bindValue(':limite', $limite, PDO::PARAM_INT);

        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_CLASS, ProdutoVendido::class);
    }
}

// Uso:
$relatorio = new RelatorioRepositorio(Conexao::obter());

$top = $relatorio->produtosMaisVendidos(
    10,
    new DateTimeImmutable('first day of last month 00:00:00'),
    new DateTimeImmutable('last day of last month 23:59:59'),
);

foreach ($top as $produto) {
    echo "{$produto->nome}: {$produto->quantidade} un — {$produto->receitaFormatada()}\n";
}

Exercício 5

Desafio: implemente um QueryBuilder simples com interface fluente: select(string ...$cols), from(string $tabela), where(string $condicao, mixed $valor), orderBy(string $col, string $dir), limit(int $n), build(): array (retorna [$sql, $params]). Integre-o com PDO num método executar(\PDO $pdo): array.

<?php

declare(strict_types=1);

final class QueryBuilder
{
    private array $colunas = ['*'];
    private string $tabela = '';
    private array $condicoes = [];
    private array $params = [];
    private array $ordenacao = [];
    private ?int $limite = null;
    private int $contador = 0;

    // Identificador (tabela, coluna) nunca vira placeholder — placeholder é
    // só para VALOR. Por isso o que é identificador passa por esta peneira.
    private const IDENTIFICADOR = '/^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$/';

    private function validarIdentificador(string $nome): string
    {
        if ($nome !== '*' && !preg_match(self::IDENTIFICADOR, $nome)) {
            throw new InvalidArgumentException("Identificador inválido: {$nome}");
        }
        return $nome;
    }

    public function select(string ...$cols): static
    {
        if ($cols !== []) {
            $this->colunas = array_map($this->validarIdentificador(...), $cols);
        }
        return $this;
    }

    public function from(string $tabela): static
    {
        $this->tabela = $this->validarIdentificador($tabela);
        return $this;
    }

    /** where('idade >=', 18) → "idade >= :p0", com 18 nos parâmetros */
    public function where(string $condicao, mixed $valor): static
    {
        if (!preg_match('/^\s*([A-Za-z_][A-Za-z0-9_.]*)\s*(=|<>|!=|<=|>=|<|>|LIKE)\s*$/i',
                        $condicao, $m)) {
            throw new InvalidArgumentException("Condição inválida: {$condicao}");
        }

        $ph = ':p' . $this->contador++;
        $this->condicoes[] = "{$m[1]} " . strtoupper($m[2]) . " {$ph}";
        $this->params[$ph] = $valor;

        return $this;
    }

    public function orderBy(string $col, string $dir = 'ASC'): static
    {
        $this->ordenacao[] = $this->validarIdentificador($col) . ' '
            . (strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC');
        return $this;
    }

    public function limit(int $n): static
    {
        $this->limite = max(1, $n);
        return $this;
    }

    /** @return array{0: string, 1: array<string, mixed>} */
    public function build(): array
    {
        if ($this->tabela === '') {
            throw new LogicException('Chame from() antes de build().');
        }

        $sql = 'SELECT ' . implode(', ', $this->colunas) . ' FROM ' . $this->tabela;

        if ($this->condicoes !== []) {
            $sql .= ' WHERE ' . implode(' AND ', $this->condicoes);
        }
        if ($this->ordenacao !== []) {
            $sql .= ' ORDER BY ' . implode(', ', $this->ordenacao);
        }
        if ($this->limite !== null) {
            // Já é int validado: interpolar aqui é seguro e evita o
            // problema do PARAM_INT em LIMIT.
            $sql .= ' LIMIT ' . $this->limite;
        }

        return [$sql, $this->params];
    }

    public function executar(\PDO $pdo): array
    {
        [$sql, $params] = $this->build();

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        return $stmt->fetchAll();
    }
}

// Uso:
[$sql, $params] = (new QueryBuilder())
    ->select('id', 'nome', 'email')
    ->from('usuarios')
    ->where('ativo =', 1)
    ->where('criado_em >=', '2025-01-01')
    ->orderBy('nome')
    ->limit(20)
    ->build();

// SELECT id, nome, email FROM usuarios
//  WHERE ativo = :p0 AND criado_em >= :p1 ORDER BY nome ASC LIMIT 20
// [':p0' => 1, ':p1' => '2025-01-01']