Simplifying Your DAO Layer

What Code Should Be in Your DAO Layer?

Methods in your DAO (Data Access Object) layer should only be doing three things.

Executing Queries

This can be a select, update, delete, insert, etc. Generally, a DAO method will only have one query, but there are exceptions to this. Queries should be in the same method that they pertain for easier readability.

Transforming Data

Transforming data can be done going from your domain objects to database objects or going from database objects to domain objects. This generally happens before querying in the case of an insert, update, or delete and after the query in the case of a select.

Returning Data

This is after all the work is completed and you are returning the result.

Using The Three Steps

Methods may or may not have each of these steps, and they don't have to be in the order listed above. An example of these three steps would be:

public List<User> findByLastName(final String lastName) {
    // Query
    final List<Object[]> resultSet = connection.sql(query);
    
    // Transform
    final var result =  resultSet.stream()
        .map(row -> {
            final User user = new User();
            
            user.setId(row[0]);
            user.setFirstName(row[1]);
            user.setLastName(row[2]);
            
            return user;
        })
        .collect(Collectors.toList());

    // Return
    return result;
}

Business Logic

You should try to make your DAO layer as logicless as possible. Logic at the DAO layer should not be business logic but logic transforming the data. One example of logic in your DAO layer would be throwing an exception if a user isn't found by their ID.

public User findById(final String id) {
    final Optional<User> result = connection.findById(id);
    
    if (!result.isPresent()) {
        throw new NoResultException();
    }
    
    // ...
}

Depending on the API (Application Programming Interface) you are using to query the database, this might be done for you. If it isn't, the call could be wrapped so when connection.findById(String) is called, it throws the exception, eliminating this logic at this level.

Another example of logic is transforming the data from the database to your domain objects or from your domain objects to the database. An example would be:

public List<User> findByLastName(final String lastName) {
    // ...
    
    // Transform
    final var result = resultSet.stream()
        .map(row -> {
            final User user = new User();

            if (row[0] != null) {
                user.setId(Uuid.of(row[0]));
            }

            if (row[1] != null) {
                user.setFirstName(String.valueOf(row[1]));
            }

            if (row[2] != null) {
                user.setLastName(String.valueOf(row[2]));
            }

            return user;
        })
        .collect(Collectors.toList());

    // ...
}

Outside of these examples, there really shouldn't be any logic.

Conclusion

Make sure you are putting the correct type of code at the data layer. This will simplify your DAO layer, and as a side effect, you will see that it ends up simplifying your application in other places as well.