CRUD Operations

Overview

CRUD (Create, Read, Update, Delete) operations form the foundation of database interactions. This guide demonstrates real-world implementations using JDBC with PreparedStatements for secure and efficient data manipulation.

Reading Data (SELECT)

Establish Connection

Connect to the database using DriverManager with proper credentials.

Prepare Statement

Create a PreparedStatement with your SQL query.

Execute Query

Execute the query and iterate through results.

Close Resources

Use try-with-resources to ensure proper cleanup.

Basic SELECT Query

The verEmpleados() method demonstrates reading all employees with a JOIN operation:

DB_EnterpriseHSQLDB.java

private static void verEmpleados() {
    String sql = "SELECT e.*, d.nombre_departamento FROM t_empleados e " +
                 "JOIN t_departamentos d ON e.codigo_departamento = d.codigo_departamento";

    try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
         PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {

        System.out.println("\n--- EMPLEADOS ---");
        while (rs.next()) {
            System.out.println("ID: " + rs.getInt("codigo_empleado") + 
                             " | Nombre: " + rs.getString("nombre_empleado") +
                             " | Depto: " + rs.getString("nombre_departamento") +
                             " | Salario: " + rs.getDouble("salario_base_empleado") +
                             " | Ingreso: " + rs.getDate("fecha_ingreso_empleado"));
        }
    } catch (SQLException e) {
        System.out.println("Error: " + e.getMessage());
    }
}

Using try-with-resources (introduced in Java 7) automatically closes Connection, Statement, and ResultSet objects, preventing resource leaks.

Parameterized SELECT Query

The buscarEmpleados() method shows how to use parameters to prevent SQL injection:

DB_EnterpriseHSQLDB.java

private static void buscarEmpleados() {
    System.out.print("\nBuscar por nombre: ");
    String nombre = scanner.nextLine();

    String sql = "SELECT * FROM t_empleados WHERE nombre_empleado LIKE ?";

    try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setString(1, "%" + nombre + "%");
        ResultSet rs = pstmt.executeQuery();

        System.out.println("\n--- RESULTADOS ---");
        while (rs.next()) {
            System.out.println("ID: " + rs.getInt("codigo_empleado") + 
                             " | Nombre: " + rs.getString("nombre_empleado") +
                             " | Salario: " + rs.getDouble("salario_base_empleado") +
                             " | Ingreso: " + rs.getDate("fecha_ingreso_empleado"));
        }
    } catch (SQLException e) {
        System.out.println("Error: " + e.getMessage());
    }
}

Never concatenate user input directly into SQL strings. Always use PreparedStatement parameters (?) to prevent SQL injection attacks.

Creating Data (INSERT)

The agregarEmpleado() method demonstrates how to insert new records:

DB_EnterpriseHSQLDB.java

private static void agregarEmpleado() {
    try {
        System.out.println("\n--- NUEVO EMPLEADO ---");
        System.out.print("Código: ");
        int codigo = scanner.nextInt();
        System.out.print("Departamento: ");
        int depto = scanner.nextInt();
        scanner.nextLine();
        System.out.print("Nombre: ");
        String nombre = scanner.nextLine();
        System.out.print("Salario: ");
        double salario = scanner.nextDouble();
        scanner.nextLine(); // Limpiar buffer
        System.out.print("Fecha ingreso (YYYY-MM-DD): ");
        String fecha = scanner.nextLine();

        String sql = "INSERT INTO t_empleados (codigo_empleado, codigo_departamento, " +
                    "extension_telefonica_empleado, fecha_nacimiento_empleado, " +
                    "fecha_ingreso_empleado, salario_base_empleado, comision_empleado, " +
                    "numero_hijos_empleado, nombre_empleado) VALUES (?, ?, 100, '1990-01-01', ?, ?, 0, 0, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, codigo);
            pstmt.setInt(2, depto);
            pstmt.setString(3, fecha);
            pstmt.setDouble(4, salario);
            pstmt.setString(5, nombre);

            pstmt.executeUpdate();
            System.out.println("Empleado agregado!");
        }
    } catch (SQLException e) {
        System.out.println("Error: " + e.getMessage());
    }
}

Key Points for INSERT

Parameter Binding Types

setInt() - for integer values
setString() - for text values
setDouble() - for decimal values
setDate() - for date values
Parameter indices start at 1, not 0

Getting Generated Keys

To retrieve auto-generated IDs:

PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pstmt.executeUpdate();
ResultSet keys = pstmt.getGeneratedKeys();
if (keys.next()) {
    int newId = keys.getInt(1);
}

Updating Data (UPDATE)

Simple UPDATE Pattern

ContadorEnBD.java

final String sqlActualización = "UPDATE contadores SET cuenta=? WHERE nombre=?";

try (Connection connection = DriverManager.getConnection(url, "SA", "")) {
    PreparedStatement actualización = connection.prepareStatement(sqlActualización);
    
    actualización.setInt(1, nuevaCuenta);
    actualización.setString(2, nombreContador);
    
    int rowsAffected = actualización.executeUpdate();
    if (rowsAffected > 0) {
        System.out.println("Actualización exitosa");
    }
}

Atomic UPDATE in SQL

For operations like incrementing counters, perform the calculation in SQL to ensure atomicity:

ContadorEnBDsoloUpdateSql.java

final String sqlActualización = "UPDATE contador SET cuenta=cuenta+1 WHERE nombre='contador1';";

try (Connection connection = DriverManager.getConnection(url, "contadores", "password")) {
    PreparedStatement actualización = connection.prepareStatement(sqlActualización);
    
    for (int i=0; i<1000; i++) {
        if (actualización.executeUpdate() != 1) break;
    }
}

Updating values directly in SQL (e.g., SET cuenta=cuenta+1) is atomic at the database level, making it safer for concurrent operations than reading, modifying, and writing back.

Deleting Data (DELETE)

The eliminarEmpleado() method shows safe deletion with user feedback:

DB_EnterpriseHSQLDB.java

private static void eliminarEmpleado() {
    try {
        System.out.println("Ingresa el código del empleado que quieras eliminar: ");
        int codigo = scanner.nextInt();
        
        String sql = "DELETE FROM t_empleados WHERE codigo_empleado = ?";
        
        try(Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
            PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setInt(1, codigo);
            int filasAfectadas = ps.executeUpdate();
            
            if (filasAfectadas > 0) {
                System.out.println("Empleado eliminado correctamente");
            } else {
                System.out.println("No se encontró el empleado con ese código");
            }
        }
    } catch (Exception e) {
        System.out.println("Error al eliminar: " + e.getMessage());
    }
}

Always check the number of affected rows (executeUpdate() return value) to confirm whether the DELETE operation actually removed any records.

Updatable ResultSets

JDBC also supports updating data directly through ResultSet objects:

ContadorEnBDUpdatable.java

final String sqlConsulta = "SELECT nombre,cuenta FROM contadores WHERE nombre=?";

PreparedStatement consulta = connection.prepareStatement(
    sqlConsulta,
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE
);

consulta.setString(1, claveContador);

if (consulta.execute()) {
    ResultSet res = consulta.getResultSet();
    if (res.next()) {
        int cuenta = res.getInt(2) + 1;
        res.updateInt(2, cuenta);
        res.updateRow();
    }
}

When to Use Updatable ResultSets

Advantages:

Can update multiple columns in one operation
No need to construct UPDATE SQL statements

Disadvantages:

Not all databases fully support them
Can be less efficient than direct UPDATE statements
Requires additional ResultSet flags

Best for: Applications that need to update multiple columns based on complex query results.

Best Practices

Use PreparedStatements

Always use PreparedStatements with parameter binding to prevent SQL injection and improve performance.

Try-with-Resources

Use try-with-resources to automatically close database connections and prevent resource leaks.

Check Affected Rows

Always verify the return value of executeUpdate() to confirm operations succeeded.

Handle Exceptions

Catch SQLException specifically and provide meaningful error messages to users.

Common Patterns

Pattern: Read-Modify-Write (Unsafe for Concurrency)

// 1. Read current value
ResultSet res = consulta.executeQuery();
int cuenta = res.getInt(1);

// 2. Modify in application
cuenta++;

// 3. Write back
actualización.setInt(1, cuenta);
actualización.executeUpdate();

This pattern has a race condition! Multiple threads/processes can read the same value before any writes occur. See Concurrency for solutions.

Pattern: Atomic SQL Operation (Safe)

// All in one atomic SQL operation
String sql = "UPDATE contador SET cuenta = cuenta + 1 WHERE id = ?";
pstmt.setInt(1, id);
pstmt.executeUpdate();

Next Steps

Transactions

Learn how to group multiple operations into atomic transactions.

Concurrency

Understand concurrency issues and how to handle multiple simultaneous database operations.

Getting Started

Core Components

Database Operations

Examples

API Reference

Overview

Reading Data (SELECT)

Basic SELECT Query

Parameterized SELECT Query

Creating Data (INSERT)

Key Points for INSERT

Updating Data (UPDATE)

Simple UPDATE Pattern

Atomic UPDATE in SQL

Deleting Data (DELETE)

Updatable ResultSets

Best Practices

Use PreparedStatements

Try-with-Resources

Check Affected Rows

Handle Exceptions

Common Patterns

Pattern: Read-Modify-Write (Unsafe for Concurrency)

Pattern: Atomic SQL Operation (Safe)

Next Steps

Transactions

Concurrency

Build docs developers (and LLMs) love

Getting Started

Core Components

Database Operations

Examples

API Reference

​Overview

​Reading Data (SELECT)

​Basic SELECT Query

​Parameterized SELECT Query

​Creating Data (INSERT)

​Key Points for INSERT

​Updating Data (UPDATE)

​Simple UPDATE Pattern

​Atomic UPDATE in SQL

​Deleting Data (DELETE)

​Updatable ResultSets

​Best Practices

Use PreparedStatements

Try-with-Resources

Check Affected Rows

Handle Exceptions

​Common Patterns

​Pattern: Read-Modify-Write (Unsafe for Concurrency)

​Pattern: Atomic SQL Operation (Safe)

​Next Steps

Transactions

Concurrency

Build docs developers (and LLMs) love

Overview

Reading Data (SELECT)

Basic SELECT Query

Parameterized SELECT Query

Creating Data (INSERT)

Key Points for INSERT

Updating Data (UPDATE)

Simple UPDATE Pattern

Atomic UPDATE in SQL

Deleting Data (DELETE)

Updatable ResultSets

Best Practices

Common Patterns

Pattern: Read-Modify-Write (Unsafe for Concurrency)

Pattern: Atomic SQL Operation (Safe)

Next Steps