Paginated List Endpoints with Dropwizard Hibernate Bundle: Request-Scoped Transactions and Offset Pagination

The Problem: Building Database-Backed List Endpoints That Don't Fall Over

You're building a Dropwizard service that needs to return lists of entities—users, orders, audit records—from a relational database. The straightforward approach works fine with a few hundred rows. Then the table grows to 50,000 rows, the frontend asks for page 200, and your endpoint times out because the database is still scanning 19,900 skipped rows before returning the 20 it actually needs.

Dropwizard's dropwizard-hibernate bundle gives you a managed SessionFactory tied to the application lifecycle and the @UnitOfWork annotation for request-scoped transactions. Combined with Hibernate's setFirstResult and setMaxResults, you get a workable offset pagination pattern—but only if you understand where it breaks down.

Wiring the Bundle: YAML, Application, and SessionFactory

Start by adding the bundle dependency (coordinates vary by Dropwizard major version; for 4.x it's io.dropwizard:dropwizard-hibernate). In your Application subclass, register a HibernateBundle that exposes the SessionFactory:

public class MyApplication extends Application<MyConfiguration> {
    private final HibernateBundle<MyConfiguration> hibernate =
        new HibernateBundle<MyConfiguration>(User.class, Order.class) {
            @Override
            public DataSourceFactory getDataSourceFactory(MyConfiguration cfg) {
                return cfg.getDataSourceFactory();
            }
        };

    @Override
    public void initialize(Bootstrap<MyConfiguration> bootstrap) {
        bootstrap.addBundle(hibernate);
    }

    @Override
    public void run(MyConfiguration cfg, Environment env) {
        env.jersey().register(new UserResource(hibernate.getSessionFactory()));
    }
}

Your YAML needs a database section for the connection pool and a hibernate section for dialect, hbm2ddl.auto, and any vendor-specific properties:

database:
  driverClass: org.postgresql.Driver
  url: jdbc:postgresql://localhost:5432/myapp
  user: app_user
  password: changeme
  properties:
    charSet: UTF-8

hibernate:
  showSql: false
  formatSql: true
  generateDdl: validate
  namingStrategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl

The bundle starts the SessionFactory on server start and closes it on shutdown. No manual lifecycle code required.

DAO Pattern with @UnitOfWork and Offset Pagination

In a DAO, inject the SessionFactory and annotate query methods with @UnitOfWork. The annotation opens a session, begins a transaction, commits on clean return, and rolls back on any exception. For pagination, apply setFirstResult(offset) and setMaxResults(limit) to the Hibernate query:

public class UserDAO {
    private final SessionFactory sessionFactory;

    public UserDAO(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    @UnitOfWork
    public List<User> findAll(int offset, int limit) {
        Session session = sessionFactory.getCurrentSession();
        Query<User> query = session.createQuery("from User order by id", User.class);
        query.setFirstResult(offset);
        query.setMaxResults(limit);
        return query.list();
    }

    @UnitOfWork
    public long countAll() {
        Session session = sessionFactory.getCurrentSession();
        return (Long) session.createQuery("select count(*) from User").uniqueResult();
    }
}

Key detail: sessionFactory.getCurrentSession() returns the request-scoped session managed by @UnitOfWork. Do not call openSession() here—that creates an unmanaged session you must close yourself.

Worked Example: A Paginated User List Endpoint

The resource assembles the page metadata Dropwizard doesn't provide out of the box: total count, offset, limit, and a hasNext flag.

@Path("/users")
@Produces(MediaType.APPLICATION_JSON)
public class UserResource {
    private final UserDAO dao;

    public UserResource(SessionFactory sessionFactory) {
        this.dao = new UserDAO(sessionFactory);
    }

    @GET
    @UnitOfWork
    public PaginatedResponse<User> list(
            @QueryParam("offset") @DefaultValue("0") int offset,
            @QueryParam("limit") @DefaultValue("20") int limit) {

        if (limit > 100) limit = 100; // guard against runaway requests
        if (offset < 0) offset = 0;

        List<User> users = dao.findAll(offset, limit);
        long total = dao.countAll();
        boolean hasNext = (offset + limit) < total;

        return new PaginatedResponse<>(users, offset, limit, total, hasNext);
    }
}

// Simple wrapper—adapt to your API style
public class PaginatedResponse<T> {
    public final List<T> items;
    public final int offset;
    public final int limit;
    public final long total;
    public final boolean hasNext;

    public PaginatedResponse(List<T> items, int offset, int limit, long total, boolean hasNext) {
        this.items = items;
        this.offset = offset;
        this.limit = limit;
        this.total = total;
        this.hasNext = hasNext;
    }
}

Call it with GET /users?offset=20&limit=20 for page two. The @UnitOfWork on the resource method ensures the DAO calls share the same transaction (read-only in this case).

Trade-off: Offset Pagination Degrades on Large Tables

Offset pagination is simple but has a known cost: the database must still locate and skip the offset rows. On PostgreSQL, a query with OFFSET 100000 LIMIT 20 scans 100,020 index entries before returning 20. MySQL and SQL Server behave similarly. For tables under ~10,000 rows this is usually fine; beyond that, latency grows linearly with page depth.

If your access pattern involves deep paging (admin dashboards, infinite scroll over millions of rows), consider keyset pagination (WHERE id > :lastSeenId ORDER BY id LIMIT 20) instead. It requires a stable, unique sort key and can't jump to arbitrary page numbers, but it stays fast regardless of page depth. Dropwizard's bundle doesn't dictate the pagination strategy—you choose in the DAO query.

Verification Checklist

• Add dropwizard-hibernate to your build (Maven/Gradle) matching your Dropwizard version.
• Register the HibernateBundle in initialize() and wire the SessionFactory into resources.
• Configure database and hibernate sections in YAML; validate with generateDdl: validate.
• Write an integration test against H2 (or Testcontainers PostgreSQL) that inserts 50 rows, requests page 1 (offset=0, limit=20), page 2 (offset=20, limit=20), and asserts the returned slices are disjoint and ordered.
• Confirm @UnitOfWork rollback on exception: throw from a DAO method and verify no partial data persists.

Closing: Ship the Simple Version, Measure, Then Optimize

Offset pagination with @UnitOfWork and Hibernate's setFirstResult/setMaxResults gets you a working, transaction-safe list endpoint in minutes. It's the right starting point for most internal APIs and moderate data volumes. Monitor the slow-query log once the table passes 10k rows; if deep-page latency becomes a user-facing problem, swap the DAO query to keyset pagination without changing the resource contract. The bundle handles the session lifecycle either way.