Java and databases with JDBC

Connection, SQL queries, CRUD, prepared statements and transactions with JDBC, with interactive examples.

Introduction to JDBC

JDBC (Java Database Connectivity) is a Java API that lets applications interact with databases using the SQL standard. You'll learn from basic connections to advanced transactions.

Connection

Establish and manage connections to different databases

Queries

Run SELECT, INSERT, UPDATE, DELETE

PreparedStatement

Parameterized queries to prevent SQL Injection

Transactions

Handling ACID: Atomicity, Consistency, Isolation, Durability

Connection Pool

Resource optimization in enterprise applications

JDBC Architecture

Java Application

Use JDBC API

Driver Manager

Manages JDBC drivers

Driver JDBC

Specific connector (MySQL, PostgreSQL, etc.)

Database

MySQL, Oracle, SQL Server, etc.

Common Drivers

mysql-connector-java

postgresql

ojdbc

mssql-jdbc

Establish Connection

Connection Simulator


// Ejemplo de conexión básicaimport java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConexionBD {
    private static final String URL = "jdbc:mysql://localhost:3306/mi_base_datos";
    private static final String USER = "root";
    private static final String PASSWORD = "password123";
    
    public Connection conectar() {
        Connection conexion = null;
        try {
            // Cargar driver (opcional en JDBC 4.0+)            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // Establecer conexión            conexion = DriverManager.getConnection(URL, USER, PASSWORD);
            
            if (conexion != null) {
                System.out.println("✅ Conexión exitosa a MySQL");
            }
        } catch (ClassNotFoundException e) {
            System.err.println("❌ Driver no encontrado: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("❌ Error de conexión: " + e.getMessage());
        }
        return conexion;
    }
}

CRUD Operations

User Table Simulator

Table: usuarios
ID Name Email Age Actions
Query Users
// Consultar todos los usuariospublic List obtenerUsuarios() {
    List usuarios = new ArrayList<>();
    String sql = "SELECT * FROM usuarios";
    
    try (Connection conn = getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        
        while (rs.next()) {
            Usuario usuario = new Usuario();
            usuario.setId(rs.getInt("id"));
            usuario.setNombre(rs.getString("nombre"));
            usuario.setEmail(rs.getString("email"));
            usuario.setEdad(rs.getInt("edad"));
            usuarios.add(usuario);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return usuarios;
}
Insert New User
// Insertar un usuariopublic void insertarUsuario(Usuario usuario) {
    String sql = "INSERT INTO usuarios (nombre, email, edad) VALUES (?, ?, ?)";
    
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        pstmt.setString(1, usuario.getNombre());
        pstmt.setString(2, usuario.getEmail());
        pstmt.setInt(3, usuario.getEdad());
        
        int filasAfectadas = pstmt.executeUpdate();
        System.out.println("Filas insertadas: " + filasAfectadas);
        
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
Update User
// Actualizar usuariopublic void actualizarUsuario(int id, Usuario usuario) {
    String sql = "UPDATE usuarios SET nombre = ?, email = ?, edad = ? WHERE id = ?";
    
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        pstmt.setString(1, usuario.getNombre());
        pstmt.setString(2, usuario.getEmail());
        pstmt.setInt(3, usuario.getEdad());
        pstmt.setInt(4, id);
        
        int filasAfectadas = pstmt.executeUpdate();
        System.out.println("Filas actualizadas: " + filasAfectadas);
        
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
Delete User
// Eliminar usuariopublic void eliminarUsuario(int id) {
    String sql = "DELETE FROM usuarios WHERE id = ?";
    
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        pstmt.setInt(1, id);
        
        int filasAfectadas = pstmt.executeUpdate();
        if (filasAfectadas > 0) {
            System.out.println("Usuario eliminado: ID " + id);
        } else {
            System.out.println("No se encontró usuario con ID " + id);
        }
        
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

PreparedStatement

SQL Injection Prevention

❌ VULNERABLE (Statement)
// ❌ VULNERABLE A SQL INJECTIONpublic boolean loginVulnerable(String usuario, String password) {
    String sql = "SELECT * FROM usuarios WHERE username = '" + usuario + 
                 "' AND password = '" + password + "'";
    
    try (Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        return rs.next(); // Si encuentra usuario    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// Ejemplo de ataque:// usuario: admin' --
// password: (cualquier cosa)// SQL resultante: SELECT * FROM usuarios WHERE username = 'admin' --' AND password = ''
Injection Test
✅ SAFE (PreparedStatement)
// ✅ SEGURO CONTRA SQL INJECTIONpublic boolean loginSeguro(String usuario, String password) {
    String sql = "SELECT * FROM usuarios WHERE username = ? AND password = ?";
    
    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
        pstmt.setString(1, usuario);  // Los parámetros se escapan automáticamente        pstmt.setString(2, password);
        
        try (ResultSet rs = pstmt.executeQuery()) {
            return rs.next();
        }
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

// Mismo ataque:// usuario: admin' --
// password: (cualquier cosa)// SQL resultante: SELECT * FROM usuarios WHERE username = 'admin'' --' AND password = ''

Automatic SQL Injection prevention

Better performance (precompilation)

Cleaner, more maintainable code

PreparedStatement Generator

Transactions

Banking Transactions Simulator

Bank Accounts
Make Transfer
Execution Mode:
✅ WITH TRANSACTION
// Transferencia con transacciónpublic boolean transferirConTransaccion(int origen, int destino, double monto) {
    Connection conn = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false); // Iniciar transacción        
        // 1. Retirar de cuenta origen        String sqlRetiro = "UPDATE cuentas SET saldo = saldo - ? WHERE id = ? AND saldo >= ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sqlRetiro)) {
            pstmt.setDouble(1, monto);
            pstmt.setInt(2, origen);
            pstmt.setDouble(3, monto);
            int filas = pstmt.executeUpdate();
            
            if (filas == 0) {
                conn.rollback();
                return false; // Saldo insuficiente            }
        }
        
        // 2. Depositar en cuenta destino        String sqlDeposito = "UPDATE cuentas SET saldo = saldo + ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sqlDeposito)) {
            pstmt.setDouble(1, monto);
            pstmt.setInt(2, destino);
            pstmt.executeUpdate();
        }
        
        conn.commit(); // Confirmar transacción        return true;
        
    } catch (SQLException e) {
        if (conn != null) {
            try { conn.rollback(); } catch (SQLException ex) {}
        }
        e.printStackTrace();
        return false;
    } finally {
        if (conn != null) {
            try { conn.setAutoCommit(true); conn.close(); } catch (SQLException e) {}
        }
    }
}
❌ WITHOUT TRANSACTION
// Transferencia sin transacción - PELIGROSOpublic boolean transferirSinTransaccion(int origen, int destino, double monto) {
    try (Connection conn = getConnection()) {
        conn.setAutoCommit(true); // Cada SQL es independiente        
        // 1. Retirar de cuenta origen        String sqlRetiro = "UPDATE cuentas SET saldo = saldo - ? WHERE id = ? AND saldo >= ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sqlRetiro)) {
            pstmt.setDouble(1, monto);
            pstmt.setInt(2, origen);
            pstmt.setDouble(3, monto);
            int filas = pstmt.executeUpdate();
            
            if (filas == 0) {
                return false; // Saldo insuficiente            }
        }
        
        // ⚠️ Si hay un error aquí, el dinero ya se retiró pero no se depositó        // ⚠️ Resultado: Dinero perdido en el limbo        
        // 2. Depositar en cuenta destino        String sqlDeposito = "UPDATE cuentas SET saldo = saldo + ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sqlDeposito)) {
            pstmt.setDouble(1, monto);
            pstmt.setInt(2, destino);
            pstmt.executeUpdate();
        }
        
        return true;
        
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

Connection Pool

Resource Optimization

Connection Pool (HikariCP)
Active: 0
Idle: 10
Maximum: 20
Application
Request 1
Request 2
Request 3
Request 4
Settings HikariCP
// HikariCP Settingsimport com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DatabaseConfig {
    
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mi_base_datos");
        config.setUsername("root");
        config.setPassword("password123");
        
        // Configuración del pool        config.setMaximumPoolSize(20);
        config.setMinimumIdle(10);
        config.setConnectionTimeout(30000); // 30 seconds        config.setIdleTimeout(600000); // 10 minutos        config.setMaxLifetime(1800000); // 30 minutos        config.setConnectionTestQuery("SELECT 1");
        
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    public static void closeDataSource() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}
Advantages of the Connection Pool
Performance

Reuse of existing connections instead of creating new ones

Scalability

Efficient handling of multiple concurrent requests

Stability

Prevention of database connection exhaustion

Management

Centralized configuration of connection parameters

Practical Exercises

User Management System

Create a complete CRUD system for user management with:

  • MySQL/PostgreSQL database connection
  • CRUD Operations with PreparedStatement
  • Data validation and exception handling
  • Advanced search and filtering
  • CSV export

Sales System

Implement a sales system with transactions:

  • Related tables: productos, clientes, ventas, detalles
  • Transactions for sales (ACID)
  • Concurrent inventory handling
  • Reports with complex JOINs
  • Automatic data backup

Data Analysis System

Develop a system for analyzing large volumes of data:

  • Connection pool with HikariCP
  • Batch processing
  • Optimized queries with indexes
  • Connection handling in web applications
  • Performance monitoring

Code Editor

Summary and Resources

What You Learned in This Module

Database Connection
  • DriverManager to establish connections
  • URL, username and password configuration
  • SQLException exception handling
  • Proper resource closing
CRUD Operations
  • Statement for simple queries
  • ResultSet to process results
  • executeQuery() for SELECT
  • executeUpdate() for INSERT/UPDATE/DELETE
PreparedStatement
  • Prevention of SQL Injection
  • Better performance from precompilation
  • setXXX() methods for parameters
  • Query reuse
Transactions
  • setAutoCommit(false)
  • commit() and rollback()
  • ACID Properties
  • Error handling in transactions
Connection Pool
  • Resource optimization
  • HikariCP as the standard solution
  • Parameter configuration
  • Handling in web applications

Additional Resources

JDBC Official Tutorial

Complete Oracle documentation
HikariCP GitHub

High-performance connection pool
JDBC Guide

Baeldung - Examples and best practices
MySQL Connector/J

Official MySQL driver documentation

Download Resources

Knowledge Assessment

Check what you've learned with our interactive quiz