Contador Classes - Database Access Tutorial

Overview

The Contador classes demonstrate different approaches to implementing database counters, focusing on concurrency control, atomic operations, and transaction management. These classes illustrate common pitfalls and best practices when working with concurrent database access.

ContadorEnBD

Class Information

Class

public class

ContadorEnBDBasic database counter with initialization logic

Main Method

public static void main(String[] args)

Database Configuration

sqlConsulta

final String

"SELECT cuenta FROM contadores WHERE nombre=?"

sqlActualización

final String

"UPDATE contadores SET cuenta=? WHERE nombre=?"

claveContador

final String

default:"contador1"

Counter identifier key

Connection URL

String

"jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false"

Credentials

Username: SA
Password: (empty)

Key Methods

inicializarBD()

private static void inicializarBD(Connection connection) throws SQLException

Purpose

Creates the counter table and initializes the counter if it doesn’t exist

Implementation

try (Statement stmt = connection.createStatement()) {
    // Create table if not exists
    stmt.execute("CREATE TABLE IF NOT EXISTS contadores (nombre VARCHAR(50) PRIMARY KEY, cuenta INT)");
    
    // Check if counter exists, insert if not
    ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM contadores WHERE nombre='contador1'");
    if (rs.next() && rs.getInt(1) == 0) {
        stmt.execute("INSERT INTO contadores VALUES ('contador1', 0)");
        System.out.println("Contador inicializado a 0");
    }
}

Counter Logic

Increment Loop

for (int i = 0; i < 1000; i++) {
    ResultSet res = consulta.executeQuery();
    if (res.next()) {
        cuenta = res.getInt(1) + 1;
        actualización.setInt(1, cuenta);
        actualización.executeUpdate();
    } else {
        System.out.println("Error: No se encontró el contador");
        break;
    }
}
System.out.println("Valor final: " + cuenta);

Issue

This approach has race condition problems in concurrent scenarios

ContadorEnBDUpdatable

Class Information

Class

public class

ContadorEnBDUpdatableUses updatable ResultSet for counter modification

Main Method

public static void main(String[] args)

Configuration

claveContador

final String

default:"contador1"

Counter key identifier

sqlConsulta

final String

"SELECT nombre,cuenta FROM contadores WHERE nombre=?"

Connection

URL: jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false
User: contadores
Password: 987654321

ResultSet Configuration

ResultSet Type

int

ResultSet.TYPE_SCROLL_INSENSITIVE - Scrollable but not sensitive to changes

Concurrency Mode

int

ResultSet.CONCUR_UPDATABLE - Allows ResultSet updates

Updatable ResultSet Implementation

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

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

ContadorEnBDsoloUpdateSql

Class Information

Class

public class

ContadorEnBDsoloUpdateSqlAtomic counter update using SQL increment operation

Main Method

public static void main(String[] args)

Atomic SQL Update

sqlActualización

final String

"UPDATE contador SET cuenta=cuenta+1 WHERE nombre='contador1';"

Atomicity

boolean

default:"true"

This approach is atomic - the increment happens entirely within the database

Atomic Update Implementation

Class.forName("org.hsqldb.jdbc.JDBCDriver");
Connection connection = DriverManager.getConnection(
    "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false", 
    "contadores", 
    "987654321"
);

PreparedStatement actualización = connection.prepareStatement(sqlActualización);

for (int i=0; i<1000; i++) {
    if (actualización.executeUpdate() != 1) break;
}

Advantage

No race conditions - the database handles the read-modify-write as a single atomic operation

ContadorSqlTransaccional

Class Information

Class

public class

ContadorSqlTransaccionalTransaction-based counter with row locking using FOR UPDATE

Main Method

public static void main(String[] args) throws ClassNotFoundException

SQL Queries

sqlConsulta

String

"select nombre,cuenta from contadores where nombre='contador1' for update;"

The FOR UPDATE clause locks the row for the duration of the transaction

sqlActualización

String

"update contadores set cuenta=? where nombre='contador1';"

Transaction Management

Transactional Implementation

Class.forName("org.hsqldb.jdbc.JDBCDriver");

try (Connection connection = DriverManager.getConnection(
        "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false", 
        "contadores", 
        "987654321"))
{
    PreparedStatement consulta = connection.prepareStatement(sqlConsulta);
    PreparedStatement actualización = connection.prepareStatement(sqlActualización);
    int cuenta = 0;
    
    for (int i=0; i<1000; i++) {
        connection.setAutoCommit(false);  // Start transaction
        
        ResultSet res = consulta.executeQuery();  // Locks row with FOR UPDATE
        if (res.next()) {
            cuenta = res.getInt(2);
            cuenta++;
            actualización.setInt(1, cuenta);
            actualización.executeUpdate();
        } else {
            break;
        }
        
        connection.commit();              // Commit and release lock
        connection.setAutoCommit(false);  // Prepare for next iteration
    }
    
    System.out.println("Valor final: " + cuenta);
}

Concurrency Control

FOR UPDATE locks the row during the transaction
Other processes must wait until commit/rollback
Ensures atomicity of read-modify-write operation

ContadorSqlTransaccionalResUpdateable

Class Information

Class

public class

ContadorSqlTransaccionalResUpdateableCombines transactions, row locking, and updatable ResultSet

Main Method

public static void main(String[] args) throws ClassNotFoundException

Configuration

sqlConsulta

String

"select nombre,cuenta from contadores where nombre='contador1' for update;"

ResultSet Mode

Type: ResultSet.FETCH_FORWARD
Concurrency: ResultSet.CONCUR_UPDATABLE

Implementation

Transaction with Updatable ResultSet

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

int cuenta = 0;

for (int i=0; i<1000; i++) {
    connection.setAutoCommit(false);
    
    ResultSet res = consulta.executeQuery();
    if (res.next()) {
        cuenta = res.getInt(2);
        cuenta++;
        // Requires nombre to be primary key!
        res.updateInt(2, cuenta);
        res.updateRow();
    } else {
        break;
    }
    
    connection.commit();
    connection.setAutoCommit(false);
}

System.out.println("Valor final: " + cuenta);

Requirement

The nombre column must be a PRIMARY KEY for updateRow() to work correctly

ContadorBuggy

Class Information

Class

public class

ContadorBuggyDemonstrates a corrected approach using atomic updates within a single transaction

Main Method

public static void main(String[] args)

Configuration

url

String

"jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false"

Credentials

User: SA
Password: (empty)

Methods

inicializarBD()

private static void inicializarBD(Connection con) throws SQLException

Initialization Logic

try (Statement stmt = con.createStatement()) {
    stmt.execute("CREATE TABLE IF NOT EXISTS contadores (nombre VARCHAR(50) PRIMARY KEY, cuenta INT)");
    
    ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM contadores WHERE nombre='contador1'");
    if (rs.next() && rs.getInt(1) == 0) {
        stmt.execute("INSERT INTO contadores VALUES ('contador1', 0)");
    }
    con.commit();
}

Implementation

Atomic Update with Transaction

try (Connection con = DriverManager.getConnection(url, "SA", "")) {
    con.setAutoCommit(false);
    
    inicializarBD(con);
    
    String sqlActualiza = "update contadores set cuenta = cuenta + 1 where nombre='contador1'";
    
    try (PreparedStatement actualiza = con.prepareStatement(sqlActualiza)) {
        for (int i = 0; i < 1000; i++) {
            actualiza.executeUpdate();
        }
        con.commit();
    } catch (SQLException e) {
        con.rollback();
        throw e;
    }
    
    // Verify result
    try (Statement consulta = con.createStatement();
         ResultSet res = consulta.executeQuery("select cuenta from contadores where nombre='contador1'")) {
        if (res.next()) {
            System.out.println("HSQLDB - Valor final: " + res.getInt(1));
        }
    }
}

Approach

Single transaction for all 1000 updates
Atomic SQL increment (cuenta = cuenta + 1)
Rollback on any SQLException
Verification query at the end

Comparison Table

Class	Concurrency Safe	Uses Transactions	Row Locking	Approach
ContadorEnBD	❌ No	❌ No	❌ No	Separate SELECT and UPDATE
ContadorEnBDUpdatable	❌ No	❌ No	❌ No	Updatable ResultSet
ContadorEnBDsoloUpdateSql	✅ Yes	❌ No	❌ No	Atomic SQL increment
ContadorSqlTransaccional	✅ Yes	✅ Yes	✅ Yes (FOR UPDATE)	Transaction + row lock
ContadorSqlTransaccionalResUpdateable	✅ Yes	✅ Yes	✅ Yes (FOR UPDATE)	Transaction + updatable ResultSet
ContadorBuggy	✅ Yes	✅ Yes	❌ No	Single transaction + atomic SQL

Best Practices Demonstrated

Race Condition Avoidance

Problem Classes: ContadorEnBD, ContadorEnBDUpdatableThese classes separate the read and write operations without proper locking, leading to race conditions in concurrent scenarios.Solution Classes: ContadorEnBDsoloUpdateSql, ContadorSqlTransaccional, ContadorBuggyUse atomic SQL operations or explicit row locking with transactions.

Transaction Management

connection.setAutoCommit(false);  // Start transaction
try {
    // Perform operations
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
    throw e;
}

Row Locking with FOR UPDATE

String sql = "SELECT * FROM table WHERE id=? FOR UPDATE";

Locks the selected rows until the transaction commits or rolls back, preventing concurrent modifications.

Atomic Operations

// Atomic - happens entirely in database
"UPDATE contadores SET cuenta = cuenta + 1 WHERE nombre=?"

// Not atomic - vulnerable to race conditions
int value = SELECT cuenta FROM contadores;
value++;
UPDATE contadores SET cuenta = value;

JDBC Transaction Management
Database Concurrency Control
Optimistic vs Pessimistic Locking
ACID Properties

Getting Started

Core Components

Database Operations

Examples

API Reference

​Overview

​ContadorEnBD

​Class Information

​Main Method

​Database Configuration

​Key Methods

​inicializarBD()

​Counter Logic

​ContadorEnBDUpdatable

​Class Information

​Main Method

​Configuration

​ResultSet Configuration

​ContadorEnBDsoloUpdateSql

​Class Information

​Main Method

​Atomic SQL Update

​ContadorSqlTransaccional

​Class Information

​Main Method

​SQL Queries

​Transaction Management

​ContadorSqlTransaccionalResUpdateable

​Class Information

​Main Method

​Configuration

​Implementation

​ContadorBuggy

​Class Information

​Main Method

​Configuration

​Methods

​inicializarBD()

​Implementation

​Comparison Table

​Best Practices Demonstrated

​Related Topics

Build docs developers (and LLMs) love

Overview

ContadorEnBD

Class Information

Main Method

Database Configuration

Key Methods

inicializarBD()

Counter Logic

ContadorEnBDUpdatable

Class Information

Main Method

Configuration

ResultSet Configuration

ContadorEnBDsoloUpdateSql

Class Information

Main Method

Atomic SQL Update

ContadorSqlTransaccional

Class Information

Main Method

SQL Queries

Transaction Management

ContadorSqlTransaccionalResUpdateable

Class Information

Main Method

Configuration

Implementation

ContadorBuggy

Class Information

Main Method

Configuration

Methods

inicializarBD()

Implementation

Comparison Table

Best Practices Demonstrated

Related Topics