Java database project: one receipt and its aggregate in one transaction

Java 8+. This program requires the H2 JDBC driver; the downloadable Maven project declares it.

State the invariant and rejection path

The invariant is that the aggregate quantity equals the sum of accepted receipt quantities. Each receipt identifier is unique. Reusing an identifier is a rejected write, not a second accepted receipt with a different quantity.

The store method requires a manual-transaction connection owned by this project. It inserts the receipt, updates the aggregate and commits both changes. On a database failure it attempts rollback, attaches a rollback failure if one occurs, and propagates the original failure.

The demo uses H2 2.3.232 and in-memory tables. It verifies a duplicate rejection and the persisted total within that connection. A deployed repository also needs migrations, pool reset rules, isolation choices, durable storage and tests against its production engine.

Keep bound values out of SQL structure

The identifier and quantity are parameters. Statement structure is fixed, and the aggregate row is a known schema object. Validate the application quantity before touching the connection so an invalid quantity cannot leave partial database work.

Working program

import java.sql.*;
public class ReceiptDatabaseProject {
    static void store(Connection connection,String id,int quantity) throws SQLException {
        if(id==null||id.isEmpty()||quantity<=0)throw new IllegalArgumentException("Invalid receipt");
        if(connection.getAutoCommit())throw new IllegalArgumentException("Manual transaction required");
        try{
            try(PreparedStatement detail=connection.prepareStatement("INSERT INTO receipts(id,quantity) VALUES(?,?)")){
                detail.setString(1,id);detail.setInt(2,quantity);detail.executeUpdate();
            }
            try(PreparedStatement total=connection.prepareStatement("UPDATE totals SET quantity=quantity+? WHERE id=1")){
                total.setInt(1,quantity);if(total.executeUpdate()!=1)throw new SQLException("Aggregate row missing");
            }
            connection.commit();
        }catch(SQLException failure){
            try{connection.rollback();}catch(SQLException rollback){failure.addSuppressed(rollback);}
            throw failure;
        }
    }
    public static void main(String[] args) throws Exception {
        try(Connection connection=DriverManager.getConnection("jdbc:h2:mem:receipt_project")){
            try(Statement schema=connection.createStatement()){
                schema.execute("CREATE TABLE receipts(id VARCHAR(80) PRIMARY KEY,quantity INT CHECK(quantity>0))");
                schema.execute("CREATE TABLE totals(id INT PRIMARY KEY,quantity INT)");
                schema.execute("INSERT INTO totals VALUES(1,0)");
            }
            connection.setAutoCommit(false);store(connection,"R-81",7);
            try{store(connection,"R-81",5);}catch(SQLIntegrityConstraintViolationException duplicate){System.out.println("Duplicate rejected");}
            try(Statement query=connection.createStatement();ResultSet total=query.executeQuery("SELECT quantity FROM totals WHERE id=1")){
                total.next();System.out.println("total="+total.getInt(1));
            }
            try(Statement query=connection.createStatement();ResultSet rows=query.executeQuery("SELECT COUNT(*) FROM receipts")){
                rows.next();System.out.println("rows="+rows.getInt(1));
            }
        }
    }
}

Output

Duplicate rejected
total=7
rows=1

Costs and boundaries

The insert is supported by a primary-key index. Aggregate update cost and contention depend on the database; one aggregate row can become a serialization point under heavy writes. An in-memory H2 fixture proves neither production isolation nor crash recovery. A failed rollback means the connection must not be reused as if its state were known.

Common Mistakes

Do not increment the aggregate after a rejected insert.
A manual transaction can accidentally include unrelated caller work if ownership is unclear.
Do not treat H2 success as certification of another database engine.