Day 21: JDBC Database
JDBC (Java Database Connectivity) is the standard API for connecting to databases and executing SQL from Java. It allows you to work with various databases (MySQL, PostgreSQL, Oracle, H2, etc.) through a unified interface. Here we practice with H2, a lightweight in-memory database that requires no setup.
Database Connection and Table Creation
Connect to a database and create tables using JDBC.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcConnection {
// H2 in-memory DB URL
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String DB_USER = "sa";
private static final String DB_PASSWORD = "";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
}
public static void main(String[] args) {
// Automatic resource release with try-with-resources
try (Connection conn = getConnection();
Statement stmt = conn.createStatement()) {
System.out.println("DB connection successful!");
System.out.println("DB product: " + conn.getMetaData().getDatabaseProductName());
// Create table
String createTable = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""";
stmt.execute(createTable);
System.out.println("users table created");
} catch (SQLException e) {
System.err.println("DB error: " + e.getMessage());
}
}
}CRUD Operations (PreparedStatement)
Manage data using PreparedStatement to prevent SQL injection.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
record User(int id, String name, String email, int age) {}
public class JdbcCrud {
private static final String URL = "jdbc:h2:mem:testdb";
static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, "sa", "");
}
// CREATE
static int insertUser(Connection conn, String name, String email, int age)
throws SQLException {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, age);
pstmt.executeUpdate();
try (ResultSet keys = pstmt.getGeneratedKeys()) {
if (keys.next()) return keys.getInt(1);
}
}
return -1;
}
// READ (all)
static List<User> findAllUsers(Connection conn) throws SQLException {
String sql = "SELECT id, name, email, age FROM users ORDER BY id";
List<User> users = new ArrayList<>();
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
users.add(new User(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getInt("age")
));
}
}
return users;
}
// READ (by condition)
static User findById(Connection conn, int id) throws SQLException {
String sql = "SELECT id, name, email, age FROM users WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return new User(rs.getInt("id"), rs.getString("name"),
rs.getString("email"), rs.getInt("age"));
}
}
}
return null;
}
// UPDATE
static boolean updateUser(Connection conn, int id, String name, int age)
throws SQLException {
String sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setInt(2, age);
pstmt.setInt(3, id);
return pstmt.executeUpdate() > 0;
}
}
// DELETE
static boolean deleteUser(Connection conn, int id) throws SQLException {
String sql = "DELETE FROM users WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
return pstmt.executeUpdate() > 0;
}
}
public static void main(String[] args) throws SQLException {
try (Connection conn = getConnection()) {
// Create table
conn.createStatement().execute("""
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50), email VARCHAR(100), age INT)
""");
// INSERT
insertUser(conn, "Alice", "alice@test.com", 25);
insertUser(conn, "Bob", "bob@test.com", 30);
insertUser(conn, "Charlie", "charlie@test.com", 28);
// SELECT ALL
System.out.println("=== All records ===");
findAllUsers(conn).forEach(System.out::println);
// UPDATE
updateUser(conn, 1, "Alice (updated)", 26);
// SELECT ONE
System.out.println("\nAfter update: " + findById(conn, 1));
// DELETE
deleteUser(conn, 2);
System.out.println("\n=== After deletion ===");
findAllUsers(conn).forEach(System.out::println);
}
}
}Transaction Handling
Process multiple SQL operations as a single atomic unit.
import java.sql.*;
public class TransactionExample {
private static final String URL = "jdbc:h2:mem:bankdb";
static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, "sa", "");
}
static void transfer(Connection conn, int fromId, int toId, long amount)
throws SQLException {
// Begin transaction
conn.setAutoCommit(false);
try {
// Withdraw
String withdrawSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
try (PreparedStatement pstmt = conn.prepareStatement(withdrawSql)) {
pstmt.setLong(1, amount);
pstmt.setInt(2, fromId);
pstmt.setLong(3, amount);
int rows = pstmt.executeUpdate();
if (rows == 0) {
throw new SQLException("Withdrawal failed: insufficient balance or account not found");
}
}
// Deposit
String depositSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(depositSql)) {
pstmt.setLong(1, amount);
pstmt.setInt(2, toId);
int rows = pstmt.executeUpdate();
if (rows == 0) {
throw new SQLException("Deposit failed: account not found");
}
}
// Transfer log
String logSql = "INSERT INTO transfer_log (from_id, to_id, amount) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(logSql)) {
pstmt.setInt(1, fromId);
pstmt.setInt(2, toId);
pstmt.setLong(3, amount);
pstmt.executeUpdate();
}
conn.commit(); // Commit if all succeed
System.out.println("Transfer successful: " + fromId + " -> " + toId + " (" + amount + " won)");
} catch (SQLException e) {
conn.rollback(); // Rollback if any fail
System.err.println("Transfer failed (rolled back): " + e.getMessage());
} finally {
conn.setAutoCommit(true);
}
}
public static void main(String[] args) throws SQLException {
try (Connection conn = getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("""
CREATE TABLE accounts (
id INT PRIMARY KEY, name VARCHAR(50), balance BIGINT)
""");
stmt.execute("""
CREATE TABLE transfer_log (
id INT AUTO_INCREMENT PRIMARY KEY,
from_id INT, to_id INT, amount BIGINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
""");
stmt.execute("INSERT INTO accounts VALUES (1, 'Alice', 100000)");
stmt.execute("INSERT INTO accounts VALUES (2, 'Bob', 50000)");
System.out.println("=== Before transfer ===");
printAccounts(conn);
transfer(conn, 1, 2, 30000);
System.out.println("\n=== After transfer ===");
printAccounts(conn);
// Attempt with insufficient balance
transfer(conn, 1, 2, 999999);
}
}
static void printAccounts(Connection conn) throws SQLException {
try (ResultSet rs = conn.createStatement()
.executeQuery("SELECT * FROM accounts")) {
while (rs.next()) {
System.out.printf("%s (ID:%d): %,d won%n",
rs.getString("name"), rs.getInt("id"), rs.getLong("balance"));
}
}
}
}DAO Pattern
A pattern for separating data access logic into a dedicated class.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
record Product(int id, String name, int price, int stock) {}
// DAO interface
interface ProductDao {
int save(Product product);
Optional<Product> findById(int id);
List<Product> findAll();
boolean update(Product product);
boolean delete(int id);
}
// JDBC implementation
class JdbcProductDao implements ProductDao {
private final Connection conn;
JdbcProductDao(Connection conn) {
this.conn = conn;
}
@Override
public int save(Product product) {
String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, product.name());
ps.setInt(2, product.price());
ps.setInt(3, product.stock());
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
return keys.next() ? keys.getInt(1) : -1;
}
} catch (SQLException e) {
throw new RuntimeException("Save failed", e);
}
}
@Override
public Optional<Product> findById(int id) {
String sql = "SELECT * FROM products WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) return Optional.of(mapRow(rs));
}
} catch (SQLException e) {
throw new RuntimeException("Find failed", e);
}
return Optional.empty();
}
@Override
public List<Product> findAll() {
List<Product> list = new ArrayList<>();
try (ResultSet rs = conn.createStatement()
.executeQuery("SELECT * FROM products")) {
while (rs.next()) list.add(mapRow(rs));
} catch (SQLException e) {
throw new RuntimeException("Find all failed", e);
}
return list;
}
@Override
public boolean update(Product p) {
String sql = "UPDATE products SET name=?, price=?, stock=? WHERE id=?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, p.name());
ps.setInt(2, p.price());
ps.setInt(3, p.stock());
ps.setInt(4, p.id());
return ps.executeUpdate() > 0;
} catch (SQLException e) {
throw new RuntimeException("Update failed", e);
}
}
@Override
public boolean delete(int id) {
try (PreparedStatement ps = conn.prepareStatement(
"DELETE FROM products WHERE id=?")) {
ps.setInt(1, id);
return ps.executeUpdate() > 0;
} catch (SQLException e) {
throw new RuntimeException("Delete failed", e);
}
}
private Product mapRow(ResultSet rs) throws SQLException {
return new Product(
rs.getInt("id"), rs.getString("name"),
rs.getInt("price"), rs.getInt("stock"));
}
}Today’s Exercises
-
Bulletin Board DB: Create a
poststable (id, title, content, author, created_at) and implement CRUD methods. Also add afindByTitle(String keyword)method for searching by title. -
Batch Insert: Write a program that inserts 1000 records at once using
PreparedStatement’saddBatch()andexecuteBatch(). Compare the performance of individual inserts vs batch inserts. -
Account Transfer System: Write a program that processes transfers between 3 accounts using transactions. Verify that if an error occurs during an A->B transfer, it rolls back, and record transfer history in a separate table.