10 Java-Database Patterns That Fix Performance Bottlenecks and Prevent Production Crashes

The first time I saw a Java application fall over under load, the database was not even sweating. The app was waiting on connections, thread pools were jammed, and the graph on the wall looked like a cliff. That day I learned something I have never forgotten: most Java performance problems are actually database problems wearing a costume.

So today I want to walk you through ten patterns I use whenever Java meets a relational database. I will keep the explanations plain, show real code, and tell you the cost of each one. No magic, no hand waving.

Before anything else, I need you to picture what JDBC actually does. Java opens a connection, prepares a statement, sends it, reads rows back, and closes everything. Every pattern below is just a smarter version of that loop, fixing one specific way the naive loop breaks. If you remember that, everything else clicks.

Let me start with the pattern that saves more production incidents than any other.

A connection is not a cheap object. Opening one usually means a TCP handshake, an authentication round trip, a session setup, and some memory on the database side. On a good day that costs 50 to 200 milliseconds. If your application opens one per request, and you serve a thousand requests per second, you are burning most of your time on connect and disconnect instead of asking questions.

Connection pooling fixes this by keeping a set of connections open and warm, handing them out on demand. In Java, HikariCP is the pool I reach for. It is small, it is fast, and it gets out of my way.

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

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class Database {

    private static final DataSource DATA_SOURCE = buildDataSource();

    private static DataSource buildDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
        config.setUsername("app");
        config.setPassword("secret");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(3000);
        config.setIdleTimeout(600_000);
        config.setMaxLifetime(1_800_000);
        config.setLeakDetectionThreshold(5_000);
        return new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return DATA_SOURCE.getConnection();
    }
}

The mistake almost everyone makes is believing that bigger pools are better. They are not. A pool of one hundred connections against a database with four CPU cores will just create a traffic jam at the database. A smaller pool that is always busy beats a big pool that is always waiting.

Now, the second pattern.

If you have ever written SQL by gluing strings together, please stop reading for ten seconds and promise me you will never do it again. Here is why. Consider this innocent line:

String sql = "SELECT * FROM users WHERE email = '" + email + "'";

If a user types ' OR 1=1 -- into that email field, your query returns every user in the table. That is not a bug. That is an open door. The fix is to make the database treat your input as data, not as code.

String sql = "SELECT id, email, created_at FROM users WHERE email = ?";

try (Connection connection = Database.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {

    statement.setString(1, email);

    try (ResultSet rs = statement.executeQuery()) {
        if (rs.next()) {
            return new User(
                rs.getLong("id"),
                rs.getString("email"),
                rs.getTimestamp("created_at").toInstant()
            );
        }
    }
}
return null;

There is a second benefit most people miss. When you use a prepared statement, most databases prepare and plan that query once, then reuse that plan. Run the same statement a thousand times and you save the planning cost a thousand times. Free speed, better safety, zero downside.

The third pattern is the one I wish I had learned on day one.

Resources in Java are not garbage collected when you want them to be. If you open a connection and forget to close it, it stays open until the pool gives up. Do that enough times and the pool runs dry. Users see timeouts. You restart the app. The cycle repeats.

Try-with-resources closes things for you, in the right order, even when an exception happens.

public Optional<Order> findOrder(long orderId) throws SQLException {
    String sql = "SELECT id, customer_id, total_cents, status " +
                 "FROM orders WHERE id = ?";

    try (Connection connection = Database.getConnection();
         PreparedStatement statement = connection.prepareStatement(sql)) {

        statement.setLong(1, orderId);

        try (ResultSet rs = statement.executeQuery()) {
            if (rs.next()) {
                return Optional.of(mapOrder(rs));
            }
            return Optional.empty();
        }
    }
}

The old try / catch / finally version worked, but it was easy to get wrong. You could close a connection before its statements, or forget one in a branch. Try-with-resources removes that whole class of mistakes. In Java, resources are closed in reverse order of declaration, so the result set closes before the statement, and the statement closes before the connection. That is exactly what you want.

Now we reach the fourth pattern, which is where most applications quietly die.

Picture an admin screen that exports every order a company has ever placed. It runs fine in your test environment with two hundred rows. In production, with four million rows, the JVM first fills its heap, then throws OutOfMemoryError, then dies. The database was never the problem. Your code asked for the whole table.

The fix has two parts. First, set a fetch size so the driver pulls rows in chunks instead of loading everything at once. Second, set a maximum on the SQL itself.

public void streamOrders(Consumer<Order> consumer) throws SQLException {
    String sql = "SELECT id, customer_id, total_cents, status " +
                 "FROM orders ORDER BY id LIMIT 50000";

    try (Connection connection = Database.getConnection()) {
        connection.setAutoCommit(false);

        try (PreparedStatement statement = connection.prepareStatement(
                sql,
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY)) {

            statement.setFetchSize(500);

            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    consumer.accept(mapOrder(rs));
                }
            }
        }

        connection.commit();
    }
}

For PostgreSQL, streaming only works if autoCommit is off. For MySQL, you need useCursorFetch=true in the JDBC URL. This is one of those small details that turns a crashing application into a calm one.

If you need to insert ten thousand rows, do not send ten thousand separate insert statements. The network round trip usually costs more than the actual database work.

Batching is the fifth pattern. You group rows into one call, send them together, and let the driver do its thing.

String sql = "INSERT INTO events (user_id, kind, created_at) VALUES (?, ?, ?)";

try (Connection connection = Database.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {

    connection.setAutoCommit(false);

    int count = 0;
    for (Event event : events) {
        statement.setLong(1, event.userId());
        statement.setString(2, event.kind());
        statement.setTimestamp(3, Timestamp.from(event.createdAt()));
        statement.addBatch();

        if (++count % 1000 == 0) {
            statement.executeBatch();
            statement.clearBatch();
        }
    }
    statement.executeBatch();
    connection.commit();
}

Three things make this fast. The batch, the transaction, and the reused prepared statement. Take any of them away and the speedup collapses. Also, if you are on MySQL, add rewriteBatchedStatements=true to your JDBC URL. Without it, the MySQL driver quietly sends each insert as its own statement and you get none of the speed.

Now, the sixth pattern. This is the one that has caused me the most personal pain.

You fetch one hundred orders. For each order, you look up the customer. That is one query for the list, then one hundred more. One hundred and one queries where there should have been two. This is the N+1 problem, and ORMs hand it to you on a silver platter.

List<Order> orders = orderRepository.findRecent(100);

for (Order order : orders) {
    Customer customer = customerRepository.findById(order.customerId());
    render(order, customer);
}

The fix depends on how much data you actually need. If you only need the customer name, join once and map both objects from a single result set.

String sql = """
    SELECT o.id AS order_id, o.total_cents, o.status,
           c.id AS customer_id, c.name AS customer_name, c.email
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.created_at > ?
    ORDER BY o.created_at DESC
    LIMIT 100
    """;

Map<Long, Customer> customers = new HashMap<>();
List<Order> orders = new ArrayList<>();

try (Connection connection = Database.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {

    statement.setTimestamp(1, Timestamp.from(Instant.now().minus(7, ChronoUnit.DAYS)));

    try (ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            long customerId = rs.getLong("customer_id");
            Customer customer = customers.computeIfAbsent(customerId, id -> {
                try {
                    return new Customer(id, rs.getString("customer_name"),
                                        rs.getString("email"));
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            });
            orders.add(mapOrderRow(rs, customer));
        }
    }
}

If the join gets too wide, use IN with a chunked list of customer IDs. Fetch the orders, collect their customer IDs, fetch those customers in one query. Two round trips, not one hundred and one. Keep your IN lists under a thousand items, because parameters count against database limits.

The seventh pattern is lazy loading, and it is N+1 wearing a disguise.

Every ORM supports fetching associations on demand. The order loads, then the customer loads the first time you touch it, then the address, then the shipping lines. Ten orders can generate fifty hidden queries before anyone notices. I once watched a page make 412 database calls because one developer used a lazy collection inside a loop, three levels deep.

The answer is not to disable lazy loading everywhere. It is to be deliberate. When you know a screen needs the full object graph, tell the ORM to fetch it in one shot. In JPA terms, that is a fetch join.

String jpql = """
    SELECT o FROM Order o
    JOIN FETCH o.customer c
    LEFT JOIN FETCH o.lines
    WHERE o.id = :id
    """;

Order order = entityManager
    .createQuery(jpql, Order.class)
    .setParameter("id", orderId)
    .getSingleResult();

The trade off is real. Fetch joins produce wider result sets and duplicate parent rows, so you burn more memory when you fetch collections you do not need. Fetch what the screen shows. Nothing more.

The eighth pattern is transaction isolation, and it is where correctness and speed argue with each other.

You write the same Java code, and it behaves differently on Oracle, PostgreSQL, and MySQL, because their defaults differ. PostgreSQL and Oracle default to read committed. MySQL InnoDB defaults to repeatable read. If you never set isolation explicitly, you are trusting the database vendor to make a decision for you.

try (Connection connection = Database.getConnection()) {
    connection.setAutoCommit(false);
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

    try {
        transferFunds(connection, fromAccount, toAccount, amount);
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    }
}

The levels go up in strictness. Read uncommitted lets you see rows other transactions have not yet committed, which is dirty data and almost never a good idea. Read committed means you only see committed rows, but the same row can change value between two reads inside your transaction. Repeatable read means rows you read will not change during your transaction. Serializable means the database guarantees your transactions behave as if they ran one after another, and that usually comes with locks and blocking.

My advice is boring and it works: use read committed unless you have a specific reason not to. Pay for stricter isolation only where you need it. Keep transactions short. A transaction held open while calling a slow external API will block other threads and turn a fast system into a queue.

The ninth pattern is deadlock handling, and no matter how careful you are, you will need it eventually.

A deadlock happens when two transactions each hold something the other one wants. The database notices and kills one of them, usually with a SQLTransientException or vendor-specific error. The victim gets an exception. Your job is to retry, not to crash.

public void updateWithRetry(Callable<Void> operation, int maxAttempts) {
    for (int attempt = 1; attempt <= maxAttempts; attempt++) {
        try {
            operation.call();
            return;
        } catch (SQLTransientException | SQLTransactionRollbackException e) {
            if (attempt == maxAttempts) {
                throw new IllegalStateException("Giving up after retries", e);
            }
            sleepWithBackoff(attempt);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

private void sleepWithBackoff(int attempt) {
    long base = 50L * (1L << attempt);
    long jitter = ThreadLocalRandom.current().nextLong(base);
    try {
        Thread.sleep(base + jitter);
    } catch (InterruptedException ie) {
        Thread.currentThread().interrupt();
    }
}

Retrying too quickly makes deadlocks worse, because your retries collide again. Add jitter. Also, deadlocks often come from accessing tables in different orders across different code paths. Pick an order and stick to it everywhere.

The tenth pattern is about caching, and it deserves honest treatment because it trades speed for truth.

Caching is the fastest thing you will ever do, until it is the wrongest thing you will ever do. Put an account balance in a query cache and round it out to two decimal places, and you can watch how quickly a stale read turns into a support ticket. I have been there. It is not fun.

The right shape is to cache only what is expensive to compute and cheap to invalidate. Set a short time-to-live, and clear the entry on every write.

public final class ProductCache {

    private final Cache<Long, Product> cache = Caffeine.newBuilder()
        .maximumSize(10_000)
        .expireAfterWrite(Duration.ofMinutes(2))
        .build();

    private final DataSource dataSource;

    public ProductCache(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Product find(long id) throws SQLException {
        return cache.get(id, key -> {
            try {
                return loadFromDatabase(key);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }

    public void invalidate(long id) {
        cache.invalidate(id);
    }

    private Product loadFromDatabase(long id) throws SQLException {
        String sql = "SELECT id, name, price_cents FROM products WHERE id = ?";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setLong(1, id);
            try (ResultSet rs = statement.executeQuery()) {
                if (!rs.next()) {
                    return null;
                }
                return new Product(rs.getLong("id"),
                                   rs.getString("name"),
                                   rs.getInt("price_cents"));
            }
        }
    }
}

Second level caches in ORMs work the same way, but they are usually bigger and more complicated, so the stale data risk is bigger too. The rule I follow is simple. Never cache anything a customer would be upset to see out of date.

Now the part nobody talks about enough: when things are already slow, how do you find out why?

I start with logs. HikariCP can log every query with its duration. In development, this shows me exactly which queries are being called and how often. The N+1 problem becomes visible in about ten seconds. For production, I turn on Java Flight Recorder with the JDBC events. You get per query timings with almost no overhead, and you can see the slow ones without slowing the system down further.

On the database side, EXPLAIN ANALYZE is the truth teller.

EXPLAIN ANALYZE
SELECT o.id, o.total_cents
FROM orders o
WHERE o.customer_id = 42
  AND o.created_at > NOW() - INTERVAL '30 days';

It shows the plan the database chose, the actual rows it processed, and whether it used your index or scanned the whole table. If you see a sequential scan over millions of rows, that is your problem. Add the index, or rewrite the query to help the planner.

Connection leaks are the slowest way for an application to die. The pool has twenty connections. A few leak. Then a few more. Threads wait. Requests pile up. Timeouts cascade. Nobody knows why the app feels broken even though the database is fine.

Try-with-resources prevents leaks at the source. On top of that, HikariCP’s leakDetectionThreshold is worth its weight in gold. Set it to a few seconds in development. If code holds a connection longer than that, the pool logs a stack trace showing exactly where. I have fixed more leaks with that one setting than with any code review.

For rollbacks, do not guess. The exception message often tells you why. A constraint violation is a SQLIntegrityConstraintViolationException. A lock timeout has its own code. When the message is not enough, look at the database logs and the transaction id. Most engines log exactly what happened and when.

Testing database code needs two tools, and they solve different problems.

H2 is an in-memory database you can start inside a unit test in milliseconds. It is perfect for checking that your SQL is wired up correctly and your mappings work. It is not perfect for checking that your SQL is right, because H2 speaks its own dialect. I have seen code pass beautifully against H2 and fall over on PostgreSQL because a function name was slightly different.

That is why Testcontainers exists. You spin up a real PostgreSQL or MySQL inside Docker, run your tests against it, and throw it away.

@Testcontainers
class OrderRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine")
            .withDatabaseName("shop")
            .withUsername("test")
            .withPassword("test");

    static DataSource dataSource;

    @BeforeAll
    static void setUp() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(postgres.getJdbcUrl());
        config.setUsername(postgres.getUsername());
        config.setPassword(postgres.getPassword());
        dataSource = new HikariDataSource(config);
    }

    @Test
    void findsRecentOrdersForCustomer() throws SQLException {
        OrderRepository repository = new OrderRepository(dataSource);
        List<Order> orders = repository.findRecentForCustomer(42L, 10);
        assertNotNull(orders);
    }
}

Run H2 on every commit. Run Testcontainers on every pull request. That combination catches almost everything before it reaches production.

The last thing I want to leave you with is about maintainability, because it is quietly the most important part.

Schemas change. Columns get added. Types get narrowed. Names get cleaned up. If your Java code and your schema move together in lockstep, every change is a nightmare.

The trick I have used for years is the additive migration. Add new columns as nullable. Backfill them in the background. Make them required when the backfill is done. Never rename a column in one step. Add the new one, write to both, read from both, then delete the old one when nothing references it.

That way, old code and new code can run side by side during a deployment. You get zero-downtime releases, and you stop being afraid of your own database.

If I could give you one final thought, it is this. The database is not a dumb storage bucket at the end of your application. It is a collaborating system with its own rules, its own costs, and its own personality. When you write Java that respects that, everything gets faster, smaller, and easier to reason about. When you do not, you end up staring at a dashboard at 2 a.m. wondering why your perfectly fine code stopped working.

The pattern that once saved me the most pain was embarrassingly simple: turning on rewriteBatchedStatements=true on a MySQL connection. A nightly import that took four hours finished in six minutes. One line. That is the kind of win that is waiting in almost every Java database codebase, and I hope some of these patterns help you find it in yours.