A Data Access Object is a helper class to keep simple database operations simple.

1. Using a DAO

First we will show how you can use a DAO. After that, we will show you what a mapper looks like for an entity class.

All demo code is maintained at github sebidao, look for the daodemo sub-project.

Sebi DAO is now at version 0.9.0-SNAPSHOT

To use sebidao, your need to add it as dependency in your pom.xml file

add sebidao to your pom.xml, excerpt.

You might want to live on the bleeding edge and update the version number of the sebidao dependency to get the newest features. The Javadoc on the website should always show the latest version.

1.1. Start with a factory.

To be able to use DAOs, you must create a factory, that has a data source. A PGDataSource.DataSource is provided, that will read a connection.properties file from the directory from which you start your application, which by default is the root of your project.

Connection properties
# modify to your needs.
# do NOT put spaces around equal sign, otherwise the shell script
# cannot read these values
Creating DAOs
      // Use the provided data source
      PGDAOFactory pdaof = new PGDAOFactory( PGDataSource.DATA_SOURCE );(1)

      // Register mappers for the classes in this app
      pdaof.registerMapper( Employee.class, new EmployeeMapper() ); (2)
      pdaof.registerMapper( Department.class, new DepartmentMapper() ); (3)

      // get a dao (no transactions yet).
      DAO<Integer, Employee> eDao = pdaof.createDao( Employee.class ); (4)
1 Create a DAO factory using a data source.
2 Register your employee mapper.
3 and a department mapper.
4 and create your first Employee DAO. Note that this uses the generic features of the DAO.
Create an Employee and save to the database using the DAO.
    Employee e = new Employee( 0 );
    e.setFirstname( "Sharon" );
    e.setLastname( "Hendrix" );
    e.setEmail( "s.hendrix@example.com" );
    e.setDepartment( "fin" );

    Employee sharonInDb = eDao.save( e ); (1)

    Collection<Employee> all = eDao.getAll(); (2)
    System.out.println( "now all = " );
    all.stream().forEach( System.out::println ); (3)

    // clean up
    eDao.delete( sharonInDb ); (4)
    dDao.delete( saveDept ); (5)
1 Note that employee e is not changed by the save operation.
If you want to have the real thing from the database, keep the object you get from the save operation. Rationale is that immutable classes are also supported.
2 Get all employees to see if it worked.
3 Print all to the system output
4 and
5 If you want to run this program multiple times, without recreating the database, cleanup .

The interaction can also be shown in a sequence diagram, shown below.

Figure 1. simple use of employee DAO

1.2. Create your own mapper

Assuming that you have an Entity class, like an Employee, you must create mapper to help the Generic DAO to do its stuff.

Sebidao has an AbtractMapper class, which takes away most burden of implementing a Mapper and at the same time avoids some data duplication. In particular, this AbstractMapper can find out quite some things about the mapped class. The secret sauce is reflection. See Horstmann, volume I, chapter 5, section 7.

To make it work, annotate the Primary Key field in the Entity class with @ID like

Using id in Employee class (excerpt).
class Employee implements Entity2<Integer> {
  private Integer employeeid;
  private String lastname;
  // truncated

Then creating a mapper for this Employee is as simple as this:

Creating a Mapper by extending AbstractMapper
public class EmployeeMapper2 extends AbstractMapper<Integer, Employee> { (1)

    public EmployeeMapper2() {
        super( Integer.class, Employee.class ); (2)

    public Function<Employee, Integer> keyExtractor() { (3)
        return Employee::getEmployeeid;
1 Extend AbstractMapper, specifying key en entity type
2 Pass type tokes to super class
3 Given method (function) to obtain key from entity. This is a map function

1.3. Tests for mappers and entities

Use a DAO, get the employee count
      DAO<Integer,Employee> eDao = daof.createDao( Employee.class ); (1)
      int numberOfCustomers = eDao.size();  (2)
1 Get a DAO ready for use.
2 Count the number of entities in the collection.
use a DAO, get a specific employee, verifying it works.
    DAO<Integer, Employee> eDao = daof.createDao( Employee.class );
    int lastId = eDao.lastId(); (1)
    Optional<Employee> e = eDao.get( lastId ); (2)

    assertTrue( "got an employee", e.isPresent() );
    assertEquals( "It's Piet", "Piet", e.get().getFirstname() );
1 Get the latest generated primary key value for the employee table
2 Get the newest employee.
Use a DAO to create new employee in the database
    DAO<Integer, Employee> eDao = daof.createDao( Employee.class );
    int preSize = eDao.size();  (1)
    eDao.save( JAN );           (2)
    Collection<Employee> allEmps = eDao.getAll(); (3)
    assertThat(allEmps)       (4)
      .as("no Jan?")          (5)
      .hasSize( 1 + preSize ) (6)
      .contains( JAN );       (7)
1 Get the current collection of employees.
2 Save Jan to the database
3 and
4 AssertJ in action:
5 describing test (when it fails).
6 Test that the number of employees increased
7 and that JAN is a member.
Meet JAN
 private static final Employee JAN = new Employee( 0, "Klaassen", "Jan",
            "jan@example.com", 1 );
Update the email address of an employee using DAO
    DAO<Integer, Employee> eDao = daof.createDao( Employee.class );
    Employee savedJan = eDao.save( JAN ); (1)
    String nemail ="janklaassen@outlook.com";
    savedJan.setEmail( nemail ); (2)

    eDao.update( savedJan ); (3)
    Employee updatedJan = eDao.get( savedJan.getEmployeeid() ).get(); (4)

    assertThat(savedJan).as( "see if email update worked" ) (5)
       .extracting(j -> j.getEmail())
       .isEqualTo( nemail );
1 Save Jan with old email address, returning a new employee object with Jan’s data.
2 Update the employee instance’s email address.
3 And apply this 'update' in the database too.
4 Get actual data from the database
5 and assert that the update is in effect.

The above code is from the unit tests inside sebidao. Here you see a promised other feature of tests: tests can serve as documentation.

1.4. Using transactions

Since version 0.2 sebidao supports transactions.

Create your dao as before, but now create a transaction token from it and use that to finish the transaction by either a commit() or a rollback().

The use case below is: Dilbert and Alice are hired and are put in a new department, just for them.

Update the email address of an employee, eDao as earlier
try (
        DAO<Integer, Department> ddao = daof.createDao( Department.class ); (1)
        TransactionToken tok = ddao.startTransaction();                     (2)
        DAO<Integer, Employee> edao = daof.createDao( Employee.class, tok ); ) { (3)

    savedDept = ddao.save( engineering );
    int depno = savedDept.getDepartmentid();
    dilbert.setDepartmentid( depno );
    savedDilbert = edao.save( dilbert );
    System.out.println( "savedDilbert = " + savedDilbert );
    tok.commit();                                                             (4)
} catch ( Exception ex ) {
    ttok.rollback();                                                          (5)
    Logger.getLogger( TransactionTest.class.getName() ).
                    log( Level.SEVERE, null, ex );
1 Create a Dao,
2 and have it make a token for all other daos involved in this transaction to use
3 as here with the edao.
4 If this point is reached we have success and commit,
5 otherwise any exception from the try-block above leads us here and we abort the transaction, thereby undoing everything that might have happened, database wise.

Note that it does not matter which DAO starts the transaction, as long as you use the same token in all DAOs that are involved in the transaction. To illustrate this, in the sequence diagram we swapped the roles of the DOAs in that respect.

Figure 2. transaction in a sequence diagram

1.5. Better Plans using DAO 0.7.1 and up

Since version 0.7.1 the PGDAO variant supports timestamp range types (tsrange in PostgreSQL), which are very useful for planning purposes. See the slides Range types.

snippet of truck planner test, showing the test when a plan does not conflict over truck and time ranges.
    Truck t1 = new Truck( 0, "Big Benz" );
    t1 = tDao.save( t1 );
    LocalDateTime start1 = now().plus( 1, DAYS );
    LocalDateTime end1 = now().plus( 1, DAYS ).plus( 2, HOURS );

    TsRange ts1 = new TsRange(start1, end1 );
    TsRange ts2 = new TsRange(end1, end1.plus( 25, MINUTES));
    TruckPlan plan1 = new TruckPlan( t1.getId(),ts1 );
    TruckPlan plan2 = new TruckPlan( t1.getId(),ts2 );

    try ( TransactionToken tok = pDao.startTransaction()){

        pDao.save( plan1 );
        pDao.save( plan2 );
snippet of truck planner test, showing the test that conflicting plans should be rejected.
Truck t1 = new Truck( 0, "Big Benz" );
        t1 = tDao.save( t1 );
        LocalDateTime start1 = now().plus( 1, DAYS );
        LocalDateTime end1 = now().plus( 1, DAYS ).plus( 2, HOURS );

        TsRange ts1 = new TsRange(start1, end1 );
        TsRange ts2 = new TsRange(start1, end1.minus( 20, MINUTES));
        TsRange ts3 = new TsRange(end1, end1.plus( 25, MINUTES));
        TruckPlan plan1 = new TruckPlan( t1.getId(),ts1 );
        TruckPlan plan2 = new TruckPlan( t1.getId(),ts2 );

        try ( TransactionToken tok = pDao.startTransaction()){

            pDao.save( plan1 );
            pDao.save( plan2 );
            fail("should not be reached");

Have fun.

Full disclosure can be found in source code and Javadoc.