JDBC, SQL Injection, and Transaction Management

Part of: Java for Beginners › Database Access with JDBC and Safe SQL

Data Access Architecture

The Layered Architecture of JDBC

Your code never talks to a specific database engine: it programs against the java.sql interfaces, and each vendor ships the Driver that implements them in binary.

Engine selector Switch databases without touching a single line of ProductoDAO

jdbc:postgresql://localhost:5432/tienda 0 lines changed in the application layer

Data Layer Security

SQL Injection vs PreparedStatement: The Structural Separation

Concatenating user input into SQL confuses data with executable code. PreparedStatement compiles the query as a fixed tree before the data even exists.

Statement Insecure

String sql = "SELECT * FROM usuarios "
    + "WHERE email = '" + entrada + "'";
rs = statement.executeQuery(sql);

Query sent to the engine:

SELECT * FROM usuarios WHERE email = 'facundo@mail.com'

1 row

PreparedStatement Safe

String sql = "SELECT * FROM usuarios "
    + "WHERE email = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, entrada);

Precompiled template + parameter:

SELECT * FROM usuarios WHERE email = ? → "facundo@mail.com"

1 row

Console: try a payload Pick an input and see what each query receives

Data Reading

The ResultSet Cursor: Traversing Records in Memory

ResultSet is not a list loaded into memory: it is a cursor pointing at a buffer that talks to the database row by row via rs.next().

id	name	price
1	Teclado	45.0
2	Mouse	20.0
3	Monitor	220.0

[BEFORE THE FIRST ROW]

// rs.next() hasn't been called yet

Consistency and Atomicity

ACID Transactions: The Atomic Transfer Protocol

If a $100 transfer between two accounts is interrupted halfway, the money cannot evaporate: either every change is confirmed with commit, or none is applied with rollback.

Account 1 $500 committed

Account 2 $300 committed

conn.setAutoCommit(false);

Performance and Data Access Architecture

Connection Pools (HikariCP) and the DAO Pattern

Opening a TCP/TLS connection with authentication costs hundreds of milliseconds. A pool like HikariCP keeps connections already open, ready to lend out.

Use HikariCP (10-connection pool)

Latency per request:

Connection taken from the pool 2 ms

conn.close() doesn't destroy the connection: it returns it to the pool for the next request.