How the Database Layer Works with Hibernate and MySQL in DD Poker

DD Poker uses a generic JPA-based data access layer built on Hibernate, where JpaBaseDao provides CRUD operations and pagination for entities implementing BaseModel, configured to connect to MySQL via persistence.xml and Spring-managed EntityManager injection.

The DD Poker open-source project implements a lightweight yet robust database layer that bridges Java entities with MySQL using Hibernate as the JPA provider. This architecture centers on a type-safe generic DAO hierarchy that eliminates boilerplate while maintaining full flexibility for custom queries. The implementation resides primarily in the code/db module, with MySQL configuration defined in persistence.xml found under code/pokerserver/src/test/resources/META-INF/.

Core Architecture of the Database Layer

The database layer follows a three-tier generic pattern that decouples domain logic from persistence concerns.

The Generic DAO Contract

At the top of the hierarchy sits BaseDao (code/db/src/main/java/com/donohoedigital/db/dao/BaseDao.java), an interface that declares standard data access operations. This contract defines methods for saving, retrieving, deleting, and querying entities, along with pagination support through PagedList<T> return types. Any concrete DAO implements this interface to ensure consistent API behavior across the application.

The Hibernate Implementation

JpaBaseDao (code/db/src/main/java/com/donohoedigital/db/dao/impl/JpaBaseDao.java) provides the concrete Hibernate implementation of BaseDao. This abstract class injects an EntityManager via Spring's @PersistenceContext annotation and implements generic type resolution at runtime using Java's ParameterizedType reflection. The class supplies standard CRUD methods—save(), get(), update(), delete(), and getAll()—along with utility methods like createQuery(), createNativeQuery(), getPagedList(), and setParametersFromVarargs() for handling JPQL and native SQL with proper parameter binding.

Entity Requirements

Every persistent entity must implement BaseModel (code/db/src/main/java/com/donohoedigital/db/model/BaseModel.java), a minimal interface requiring only a getId() method that returns a Serializable primary key. This constraint allows JpaBaseDao to perform type-safe operations while remaining agnostic to specific entity types.

MySQL Configuration and Connection Pooling

The connection between Hibernate and MySQL is configured through standard JPA persistence unit definitions.

persistence.xml Configuration

The file code/pokerserver/src/test/resources/META-INF/persistence.xml defines the persistence unit named poker using org.hibernate.jpa.HibernatePersistenceProvider as the provider. The configuration specifies the MySQL Connector/J driver (com.mysql.cj.jdbc.Driver) and a JDBC URL pointing to the local database instance:

<persistence-unit name="poker">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <class>com.donohoedigital.games.poker.model.OnlineProfile</class>
    <properties>
        <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver"/>
        <property name="hibernate.connection.url"
                  value="jdbc:mysql://127.0.0.1/pokertest?allowPublicKeyRetrieval=true&amp;useSSL=false"/>
        <property name="hibernate.connection.username" value="pokertest"/>
        <property name="hibernate.connection.password" value="p0k3rdb!"/>
        <!-- connection pool (c3p0) settings -->
        <property name="hibernate.c3p0.min_size" value="1"/>
        <property name="hibernate.c3p0.max_size" value="5"/>
    </properties>
</persistence-unit>

Connection Pool Management

The configuration utilizes c3p0 for connection pooling, with hibernate.c3p0.min_size set to 1 and hibernate.c3p0.max_size set to 5. These settings control the minimum and maximum number of database connections maintained in the pool, optimizing resource usage for the MySQL backend.

Implementing Data Access Objects

Creating a functional DAO requires minimal code beyond defining the entity and extending the base class.

Defining a JPA Entity

Entities must implement BaseModel<ID> and include standard JPA annotations. For example, the OnlineProfile entity maps to the online_profile table:

package com.donohoedigital.games.poker.model;

import com.donohoedigital.db.model.BaseModel;
import jakarta.persistence.*;

@Entity
@Table(name = "online_profile")
public class OnlineProfile implements BaseModel<Long> {

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

    @Column(name = "username", nullable = false, unique = true)
    private String username;

    // getters / setters
    @Override
    public Long getId() { return id; }
}

Extending JpaBaseDao

Concrete DAOs extend JpaBaseDao<EntityType, IdType> and inherit all CRUD functionality immediately:

package com.donohoedigital.games.poker.dao;

import com.donohoedigital.db.dao.impl.JpaBaseDao;
import com.donohoedigital.games.poker.model.OnlineProfile;
import org.springframework.stereotype.Repository;

@Repository
public class OnlineProfileDao extends JpaBaseDao<OnlineProfile, Long> {
    // No additional code needed – all CRUD methods are inherited.
}

Service Layer Integration

Spring's dependency injection wires the DAO into service classes, where PagedList<T> enables efficient pagination for result sets:

@Service
public class ProfileService {

    @Autowired
    private OnlineProfileDao profileDao;

    public OnlineProfile createProfile(String username) {
        OnlineProfile p = new OnlineProfile();
        p.setUsername(username);
        profileDao.save(p);
        return p;
    }

    public OnlineProfile findById(Long id) {
        return profileDao.get(id);
    }

    public List<OnlineProfile> listAll() {
        return profileDao.getAll();
    }

    public PagedList<OnlineProfile> searchByUsername(String pattern, int offset, int pageSize) {
        String jpql = "select p from OnlineProfile p where p.username like ?1 order by p.username";
        return profileDao.getPagedList(jpql, null, offset, pageSize, pattern + "%");
    }
}

Advanced Query Capabilities

Beyond basic CRUD, the layer supports sophisticated data retrieval patterns.

Pagination and Parameter Binding

The getPagedList() method in JpaBaseDao handles offset-based pagination automatically, returning a PagedList<T> object that contains both the result subset and total count information. The setParametersFromVarargs() method ensures type-safe parameter binding for JPQL queries using positional parameters.

Native SQL Support

When JPQL proves insufficient, createNativeQuery() allows execution of raw MySQL SQL statements while still leveraging the generic type resolution and parameter binding infrastructure provided by the base class.

Legacy JDBC Access

For scenarios requiring direct database access, DatabaseManager (code/db/src/main/java/com/donohoedigital/db/DatabaseManager.java) provides a metadata-driven JDBC wrapper. This utility handles raw PreparedStatement execution outside the JPA context, though most application code prefers the type-safe DAO layer for standard operations.

Summary

• Generic DAO hierarchy: BaseDao defines the contract while JpaBaseDao implements it using Hibernate's EntityManager.
• Type-safe entities: All persistent classes implement BaseModel to ensure primary key accessibility.
• MySQL connectivity: Configured via persistence.xml using the MySQL Connector/J driver with c3p0 connection pooling.
• Minimal boilerplate: Concrete DAOs extend JpaBaseDao with zero additional code required for standard CRUD operations.
• Flexible querying: Support for JPQL, native SQL, and pagination through PagedList and helper methods like getPagedList().

Frequently Asked Questions

How does JpaBaseDao determine the entity type at runtime?

JpaBaseDao uses Java reflection on the ParameterizedType of the class declaration to extract the actual entity class passed as the first generic parameter. When OnlineProfileDao extends JpaBaseDao<OnlineProfile, Long>, the abstract parent captures OnlineProfile.class during construction, enabling type-safe operations without explicit class references in the subclass.

What connection pool does DD Poker use with Hibernate and MySQL?

The configuration utilizes c3p0 (C3P0ConnectionProvider) with Hibernate-specific properties. The persistence.xml sets hibernate.c3p0.min_size to 1 and hibernate.c3p0.max_size to 5, maintaining between one and five open connections to the MySQL database depending on load.

Can I use raw JDBC instead of the Hibernate JPA layer?

Yes. The DatabaseManager class (code/db/src/main/java/com/donohoedigital/db/DatabaseManager.java) provides a legacy JDBC wrapper for executing raw SQL and PreparedStatement operations. However, the JPA-based approach through JpaBaseDao is preferred for type safety and automatic transaction participation with Spring.

How is transaction management handled in the database layer?

Spring manages transactions through its declarative transaction manager configured elsewhere in the application context. The DAO layer participates in these transactions automatically, as Hibernate's EntityManager respects the active Spring transaction boundary, allowing service methods to atomically coordinate multiple DAO operations against the MySQL database.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}