MySQL #

MySQL is the most widely used relational database in the world — from WordPress blogs to large-scale e-commerce platforms. In Java, there are two layers for interacting with MySQL: JDBC (Java Database Connectivity), which is the standard low-level API, and JPA/Hibernate, which provides a high-level object-relational abstraction. For production applications, both are almost always paired with HikariCP — the fastest connection pool in the Java ecosystem. This article covers how to connect from scratch with pure JDBC, manage connections efficiently with HikariCP, run CRUD operations safe from SQL injection, manage transactions, batch processing for bulk data, and how to integrate Spring Boot with Spring Data JPA for maximum productivity.

Overview #

There are three levels of abstraction for interacting with MySQL in Java:

flowchart TB
    A["Java Application"] --> B["Spring Data JPA\n(Repository, @Entity)"]
    A --> C["JDBC Template\n(Spring)"]
    A --> D["Pure JDBC\n(Connection, Statement)"]
    B --> E["Hibernate / JPA"]
    C --> D
    E --> D
    D --> F["HikariCP\n(Connection Pool)"]
    F --> G["MySQL JDBC Driver\n(mysql-connector-j)"]
    G --> H[("MySQL Server")]

Level	Abstraction	Productivity	Control	Good for
Pure JDBC	Low	Low	Full	Learning, special cases, performance-critical code
Spring JDBC	Medium	Medium	High	Complex queries, stored procedures
Spring Data JPA	High	High	Limited	Standard CRUD, rapid development

Setup — Driver and Database #

Dependencies #

<!-- Maven -->
<!-- MySQL driver -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

<!-- HikariCP — connection pool (already included in Spring Boot) -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

// Gradle
implementation 'com.mysql:mysql-connector-j:8.3.0'
implementation 'com.zaxxer:HikariCP:5.1.0'

Setting Up the Database #

-- Run in a MySQL client or Workbench
CREATE DATABASE IF NOT EXISTS store_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE store_db;

CREATE TABLE products (
    id         BIGINT       AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    price      DECIMAL(15,2) NOT NULL,
    stock      INT          NOT NULL DEFAULT 0,
    category   VARCHAR(100),
    active     BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO products (name, price, stock, category) VALUES
    ('ProBook Laptop', 12000000.00, 5, 'Electronics'),
    ('Wireless Mouse', 150000.00, 20, 'Accessories'),
    ('Mechanical Keyboard', 450000.00, 15, 'Accessories'),
    ('27" Monitor', 3500000.00, 8, 'Electronics');

Pure JDBC #

JDBC is Java’s standard API for communicating with databases. Every database operation goes through three objects: Connection (the connection to the DB), PreparedStatement (the query to execute), and ResultSet (the query results).

Direct Connection (Without a Pool) #

import java.sql.*;

// MySQL connection URL
// Format: jdbc:mysql://host:port/database?parameter=value
String url      = "jdbc:mysql://localhost:3306/store_db"
               + "?useSSL=false"           // disable SSL (development)
               + "&serverTimezone=Asia/Jakarta"
               + "&characterEncoding=utf8mb4"
               + "&allowPublicKeyRetrieval=true";
String username = "root";
String password = "mysecret";

// DriverManager.getConnection() creates a new connection every time
// Use ONLY for testing — in production always use a connection pool
try (Connection conn = DriverManager.getConnection(url, username, password)) {
    System.out.println("Connected to MySQL: " + conn.getMetaData().getDatabaseProductVersion());
} catch (SQLException e) {
    System.err.println("Connection failed: " + e.getMessage());
}

SELECT — Reading Data #

String url = "jdbc:mysql://localhost:3306/store_db?serverTimezone=Asia/Jakarta";

try (Connection conn = DriverManager.getConnection(url, "root", "mysecret")) {

    // ANTI-PATTERN: string concatenation → vulnerable to SQL injection
    String category = "Electronics";
    // String sql = "SELECT * FROM products WHERE category = '" + category + "'";  // ✗ DON'T!

    // CORRECT: PreparedStatement with parameter binding
    String sql = "SELECT id, name, price, stock FROM products WHERE category = ? AND active = ?";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, category);  // first parameter (?)
        ps.setBoolean(2, true);     // second parameter (?)

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                long   id    = rs.getLong("id");
                String name  = rs.getString("name");
                double price = rs.getDouble("price");
                int    stock  = rs.getInt("stock");

                System.out.printf("%-5d %-25s Rp%,.2f (%d units)%n",
                    id, name, price, stock);
            }
        }
    }

} catch (SQLException e) {
    System.err.println("Query failed: " + e.getMessage());
    System.err.println("SQL State: " + e.getSQLState());
    System.err.println("Error Code: " + e.getErrorCode());
}

INSERT — Saving Data #

String sql = "INSERT INTO products (name, price, stock, category) VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

    ps.setString(1, "1TB SSD");
    ps.setBigDecimal(2, new java.math.BigDecimal("750000.00")); // use BigDecimal for money
    ps.setInt(3, 30);
    ps.setString(4, "Storage");

    int rowsAffected = ps.executeUpdate();
    System.out.println("Rows affected: " + rowsAffected); // 1

    // Get the auto-increment generated ID
    try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
        if (generatedKeys.next()) {
            long newId = generatedKeys.getLong(1);
            System.out.println("New ID: " + newId);
        }
    }

} catch (SQLException e) {
    e.printStackTrace();
}

UPDATE and DELETE #

// UPDATE
String updateSql = "UPDATE products SET price = ?, stock = stock + ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(updateSql)) {

    ps.setBigDecimal(1, new java.math.BigDecimal("11500000.00"));
    ps.setInt(2, 3);   // add 3 to the stock
    ps.setLong(3, 1L); // product id

    int rowsUpdated = ps.executeUpdate();
    System.out.println("Products updated: " + rowsUpdated);

} catch (SQLException e) {
    e.printStackTrace();
}

// Soft DELETE — set active to false (safer than a hard delete)
String deleteSql = "UPDATE products SET active = FALSE WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(deleteSql)) {

    ps.setLong(1, 5L);
    ps.executeUpdate();

} catch (SQLException e) {
    e.printStackTrace();
}

Transactions #

Transactions ensure that a series of operations either all succeed or none do — the atomicity principle. By default, JDBC runs in auto-commit mode (every statement is committed immediately).

Connection conn = null;
try {
    conn = DriverManager.getConnection(url, username, password);

    // Disable auto-commit to start a manual transaction
    conn.setAutoCommit(false);

    // Scenario: transfer stock between two warehouses
    String decreaseStock = "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?";
    String increaseStock  = "UPDATE products SET stock = stock + ? WHERE id = ?";

    try (PreparedStatement ps1 = conn.prepareStatement(decreaseStock);
         PreparedStatement ps2 = conn.prepareStatement(increaseStock)) {

        // Decrease the source warehouse's stock
        ps1.setInt(1, 5);  // decrease by 5
        ps1.setLong(2, 1L);
        ps1.setInt(3, 5);  // make sure the stock is sufficient
        int decreased = ps1.executeUpdate();

        if (decreased == 0) {
            throw new SQLException("Insufficient stock for the transfer");
        }

        // Increase the destination warehouse's stock (simulated: different product ID)
        ps2.setInt(1, 5);
        ps2.setLong(2, 2L);
        ps2.executeUpdate();

        // Commit: everything succeeded
        conn.commit();
        System.out.println("Stock transfer successful.");

    } catch (SQLException e) {
        // Rollback: undo all changes
        conn.rollback();
        System.err.println("Transfer failed, rolled back: " + e.getMessage());
    }

} catch (SQLException e) {
    e.printStackTrace();
} finally {
    if (conn != null) {
        try {
            conn.setAutoCommit(true); // restore the default mode
            conn.close();
        } catch (SQLException e) { e.printStackTrace(); }
    }
}

Batch Processing — Bulk Inserts #

Batch processing lets you send many statements to the database in one network round-trip — far faster than one by one.

String sql = "INSERT INTO products (name, price, stock, category) VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(sql)) {

    conn.setAutoCommit(false); // one transaction for the batch

    List<String[]> productData = List.of(
        new String[]{"Item A", "100000", "10", "General"},
        new String[]{"Item B", "200000", "5",  "General"},
        new String[]{"Item C", "300000", "8",  "Premium"}
        // ... could be thousands of items
    );

    int batchSize = 500; // commit every 500 rows
    int count = 0;

    for (String[] data : productData) {
        ps.setString(1, data[0]);
        ps.setBigDecimal(2, new java.math.BigDecimal(data[1]));
        ps.setInt(3, Integer.parseInt(data[2]));
        ps.setString(4, data[3]);

        ps.addBatch(); // add to the batch, not yet executed

        if (++count % batchSize == 0) {
            ps.executeBatch(); // send the batch to the database
            conn.commit();
            System.out.println("Batch " + (count / batchSize) + " committed");
        }
    }

    // Execute the remaining batch
    ps.executeBatch();
    conn.commit();
    System.out.println("All " + count + " rows saved successfully.");

} catch (SQLException e) {
    e.printStackTrace();
}

HikariCP — Connection Pooling #

Creating a new database connection is an expensive operation — it requires a TCP handshake, authentication, and resource allocation on the server. A connection pool keeps a set of ready-to-use connections and lends them to requesting code, then returns them to the pool when done.

flowchart LR
    subgraph "HikariCP Pool (max=10)"
        C1["Connection 1"]
        C2["Connection 2"]
        C3["Connection 3"]
        C4["... etc"]
    end
    A["Request 1"] -->|"getConnection()"| C1
    B["Request 2"] -->|"getConnection()"| C2
    D["Request 3"] -->|"getConnection()"| C3
    C1 -->|"close() → back to the pool"| C1

HikariCP Configuration #

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DatabasePool {

    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();

        // Basic connection
        config.setJdbcUrl("jdbc:mysql://localhost:3306/store_db"
            + "?serverTimezone=Asia/Jakarta"
            + "&characterEncoding=utf8mb4"
            + "&useSSL=false");
        config.setUsername("root");
        config.setPassword("mysecret");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");

        // Pool sizing — rule of thumb: (core_count * 2) + disk_count
        config.setMaximumPoolSize(10);      // max active connections
        config.setMinimumIdle(2);           // min idle connections
        config.setConnectionTimeout(30_000); // connection wait timeout (ms)
        config.setIdleTimeout(600_000);      // remove idle connections after 10 minutes
        config.setMaxLifetime(1_800_000);    // recycle connections every 30 minutes

        // Validate connections are still alive
        config.setConnectionTestQuery("SELECT 1");
        config.setKeepaliveTime(60_000);    // send keepalive every 1 minute

        // Pool name for monitoring
        config.setPoolName("StoreDB-Pool");

        dataSource = new HikariDataSource(config);
    }

    public static java.sql.Connection getConnection() throws java.sql.SQLException {
        return dataSource.getConnection();
    }

    public static void close() {
        if (!dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

Using the Pool #

// With a pool, the usage pattern is exactly the same
// try-with-resources ensures the connection is RETURNED to the pool, not really closed
try (Connection conn = DatabasePool.getConnection()) {
    String sql = "SELECT COUNT(*) FROM products WHERE active = true";
    try (PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            System.out.println("Active products: " + rs.getInt(1));
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

The DAO Pattern — Separating Database Logic #

The Data Access Object (DAO) pattern separates database access logic from business logic. Each table has its own DAO class.

Model and DAO #

// Model
public class Product {
    private Long id;
    private String name;
    private java.math.BigDecimal price;
    private int stock;
    private String category;
    private boolean active;

    // Constructors, getters, setters
    public Product() {}
    public Product(String name, java.math.BigDecimal price, int stock, String category) {
        this.name = name; this.price = price; this.stock = stock; this.category = category;
    }
    // ... getters and setters
}

// DAO interface
public interface ProductDao {
    Optional<Product> findById(Long id);
    List<Product> findAll();
    List<Product> findByCategory(String category);
    Product save(Product product);           // insert if id is null, update if present
    boolean delete(Long id);
    int countTotalStock();
}

// DAO implementation
public class ProductDaoImpl implements ProductDao {

    private static final String SELECT_BASE =
        "SELECT id, name, price, stock, category, active FROM products WHERE active = true";

    @Override
    public Optional<Product> findById(Long id) {
        String sql = SELECT_BASE + " AND id = ?";
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return Optional.of(mapRow(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to find product ID: " + id, e);
        }
        return Optional.empty();
    }

    @Override
    public List<Product> findByCategory(String category) {
        String sql = SELECT_BASE + " AND category = ? ORDER BY name";
        List<Product> results = new ArrayList<>();
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, category);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) results.add(mapRow(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to find products in category: " + category, e);
        }
        return results;
    }

    @Override
    public Product save(Product product) {
        if (product.getId() == null) {
            return insert(product);
        } else {
            return update(product);
        }
    }

    private Product insert(Product p) {
        String sql = "INSERT INTO products (name, price, stock, category) VALUES (?, ?, ?, ?)";
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            ps.setString(1, p.getName());
            ps.setBigDecimal(2, p.getPrice());
            ps.setInt(3, p.getStock());
            ps.setString(4, p.getCategory());
            ps.executeUpdate();

            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) p.setId(keys.getLong(1));
            }
            return p;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to save product", e);
        }
    }

    private Product update(Product p) {
        String sql = "UPDATE products SET name=?, price=?, stock=?, category=? WHERE id=?";
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, p.getName());
            ps.setBigDecimal(2, p.getPrice());
            ps.setInt(3, p.getStock());
            ps.setString(4, p.getCategory());
            ps.setLong(5, p.getId());
            ps.executeUpdate();
            return p;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to update product ID: " + p.getId(), e);
        }
    }

    // Helper: mapping a ResultSet to a Product object
    private Product mapRow(ResultSet rs) throws SQLException {
        Product p = new Product();
        p.setId(rs.getLong("id"));
        p.setName(rs.getString("name"));
        p.setPrice(rs.getBigDecimal("price"));
        p.setStock(rs.getInt("stock"));
        p.setCategory(rs.getString("category"));
        p.setActive(rs.getBoolean("active"));
        return p;
    }

    @Override
    public List<Product> findAll() {
        List<Product> results = new ArrayList<>();
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(SELECT_BASE + " ORDER BY id");
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) results.add(mapRow(rs));
        } catch (SQLException e) {
            throw new RuntimeException("Failed to fetch all products", e);
        }
        return results;
    }

    @Override
    public boolean delete(Long id) {
        String sql = "UPDATE products SET active = FALSE WHERE id = ?";
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, id);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to delete product ID: " + id, e);
        }
    }

    @Override
    public int countTotalStock() {
        String sql = "SELECT SUM(stock) FROM products WHERE active = true";
        try (Connection conn = DatabasePool.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            return rs.next() ? rs.getInt(1) : 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to count stock", e);
        }
    }
}

Spring Boot + Spring Data JPA #

For Spring Boot applications, Spring Data JPA drastically simplifies database access. You define entities and repository interfaces — Spring generates the implementations automatically.

Spring Boot Dependencies #

<!-- Maven -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

application.yml Configuration #

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/store_db?serverTimezone=Asia/Jakarta&characterEncoding=utf8mb4
    username: root
    password: mysecret
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      pool-name: StoreDB-Pool

  jpa:
    hibernate:
      ddl-auto: validate      # validate: check the schema, don't change it
                              # update: update the schema automatically (dev)
                              # create-drop: drop and recreate (test)
    show-sql: false           # true for debugging (logs all SQL)
    open-in-view: false       # disable for better performance
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect
        format_sql: true
        jdbc:
          batch_size: 50      # enable batch inserts
          fetch_size: 100

Entity #

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 255)
    private String name;

    @Column(nullable = false, precision = 15, scale = 2)
    private BigDecimal price;

    @Column(nullable = false)
    private Integer stock = 0;

    @Column(length = 100)
    private String category;

    @Column(nullable = false)
    private Boolean active = true;

    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;

    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = LocalDateTime.now();
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }

    // Constructors, getters, setters
    public Product() {}

    public Product(String name, BigDecimal price, Integer stock, String category) {
        this.name = name; this.price = price; this.stock = stock; this.category = category;
    }

    // ... getters and setters
}

Repository #

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Spring Data JPA generates the implementation from the method name
    List<Product> findByActiveTrue();
    List<Product> findByCategoryAndActiveTrue(String category);
    List<Product> findByNameContainingIgnoreCaseAndActiveTrue(String keyword);
    Optional<Product> findByIdAndActiveTrue(Long id);

    // Find by a price range
    List<Product> findByPriceBetweenAndActiveTrue(BigDecimal min, BigDecimal max);

    // Custom queries with JPQL (works with entity and field names, not tables)
    @Query("SELECT p FROM Product p WHERE p.active = true ORDER BY p.price DESC")
    List<Product> findAllOrderByPrice();

    @Query("SELECT p FROM Product p WHERE p.active = true AND p.price > :minPrice AND p.category = :category")
    List<Product> findExpensiveInCategory(@Param("minPrice") BigDecimal minPrice,
                                          @Param("category") String category);

    // Native queries (direct SQL)
    @Query(value = """
        SELECT category, COUNT(*) as count, SUM(stock) as total_stock
        FROM products
        WHERE active = true
        GROUP BY category
        ORDER BY count DESC
        """, nativeQuery = true)
    List<Object[]> statisticsPerCategory();

    // Direct update without loading the entity first
    @Modifying
    @Query("UPDATE Product p SET p.active = false WHERE p.id = :id")
    int softDelete(@Param("id") Long id);

    // Counts
    long countByCategoryAndActiveTrue(String category);
    boolean existsByNameAndActiveTrue(String name);
}

Service Layer #

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.util.List;

@Service
@Transactional(readOnly = true) // default: all methods are read-only
public class ProductService {

    private final ProductRepository repository;

    public ProductService(ProductRepository repository) {
        this.repository = repository;
    }

    public List<Product> getAllProducts() {
        return repository.findByActiveTrue();
    }

    public Product getProductById(Long id) {
        return repository.findByIdAndActiveTrue(id)
            .orElseThrow(() -> new RuntimeException("Product not found: " + id));
    }

    public List<Product> searchProducts(String keyword) {
        return repository.findByNameContainingIgnoreCaseAndActiveTrue(keyword);
    }

    @Transactional // this method needs write access
    public Product createProduct(String name, BigDecimal price, int stock, String category) {
        if (repository.existsByNameAndActiveTrue(name)) {
            throw new IllegalArgumentException("A product with this name already exists: " + name);
        }
        Product created = new Product(name, price, stock, category);
        return repository.save(created);
    }

    @Transactional
    public Product updatePrice(Long id, BigDecimal newPrice) {
        Product product = getProductById(id);
        product.setPrice(newPrice);
        return repository.save(product); // JPA automatically UPDATEs on commit
    }

    @Transactional
    public void deleteProduct(Long id) {
        int affected = repository.softDelete(id);
        if (affected == 0) throw new RuntimeException("Product not found: " + id);
    }
}

SQL Security — Preventing SQL Injection #

SQL injection is the most dangerous security vulnerability in database applications. Always use PreparedStatement or an ORM — never concatenate user input into an SQL string.

// ANTI-PATTERN: SQL injection! Input: name = "'; DROP TABLE products; --"
String input = request.getParameter("name");
String sql = "SELECT * FROM products WHERE name = '" + input + "'";
// Executed query: SELECT * FROM products WHERE name = ''; DROP TABLE products; --'

// CORRECT: PreparedStatement — input is escaped automatically
String sql = "SELECT * FROM products WHERE name = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, input); // the driver handles escaping
    // ...
}

// CORRECT: Spring Data JPA / JPQL — automatic parameter binding
@Query("SELECT p FROM Product p WHERE p.name = :name")
List<Product> findByName(@Param("name") String name);

// CAREFUL: JPQL with LIKE still needs manual sanitization for wildcards
@Query("SELECT p FROM Product p WHERE p.name LIKE :pattern")
List<Product> search(@Param("pattern") String pattern);

// In the service:
public List<Product> searchProducts(String keyword) {
    // Escape SQL wildcard characters so they can't be abused
    String pattern = "%" + keyword.replace("%", "\\%").replace("_", "\\_") + "%";
    return repository.search(pattern);
}

When to Use JDBC vs JPA #

Use PURE JDBC when:
  ✓ You need maximum performance (bulk inserts, very specific queries)
  ✓ Queries are too complex to express with JPQL
  ✓ Stored procedures with output parameters
  ✓ Learning how databases work at a low level

Use SPRING DATA JPA when:
  ✓ Standard CRUD — repositories with method name conventions are very productive
  ✓ The team doesn't need to write SQL for common operations
  ✓ You need JPA features (lazy loading, caching, lifecycle events)
  ✓ New Spring Boot applications — it's the right default choice

Best practices:
  ✓ Always use HikariCP or another connection pool
  ✓ Always use PreparedStatement — never concatenate input into SQL
  ✓ Use @Transactional(readOnly = true) for queries to be more efficient
  ✓ Use soft deletes (UPDATE active = false) instead of hard deletes
  ✓ Use BigDecimal for money values, not double (floating point precision!)
  ✓ Close Connection, Statement, ResultSet in a finally block or try-with-resources

Summary #

Always use PreparedStatement — never concatenate user input into an SQL string. PreparedStatement prevents SQL injection and is more efficient because the database can cache the query.
HikariCP is a mandatory connection pool — creating a new connection per request is very slow. HikariCP manages a pool of ready-to-use connections. Spring Boot includes it by default.
try-with-resources for Connection, Statement, ResultSet — all three must always be closed. try-with-resources guarantees closing even when an exception occurs.
Use BigDecimal for money values — double and float have floating-point inaccuracies that are unacceptable for financial values. Use DECIMAL(15,2) in MySQL and BigDecimal in Java.
@Transactional(readOnly = true) for queries in Spring — hints JPA not to track entity changes (dirty checking), saving memory and time.
Soft deletes are safer than hard deletes — UPDATE active = FALSE instead of DELETE. Data can still be recovered and data relationships aren’t broken.
Spring Data JPA generates queries from method names — findByCategoryAndActiveTrue(String category) is enough as an interface declaration; Spring implements it automatically.
@Query for complex queries — use JPQL for queries that can’t be expressed with method names. Use nativeQuery = true only when JPQL isn’t sufficient.
Batch processing for bulk data — addBatch() + executeBatch() sends hundreds of inserts in one round-trip to the database, far faster than executing one by one.

← Previous: YAML Next: MSSQL →