How can I map MySQL query results to Java objects?

Question

I am building a Dropwizard application where I want to easily map results from a MySQL database to java objects. I have seen this done before with an Object mapper, so I know it can be done, but unfortunately I can't remember where or how.

I created Immutable classes and I'd like to map the values to them. Here is an example Immutable:

@Value.Immutable
public interface Task {
    int getTaskId();
    int getCreatorId();
    String getTitle();
    String getDescription();
    int getCreatedAt();
}

This is my DAO so far:

public interface TasksDAO {
    @SqlQuery("select task_id, title, description, creator_id, created_at from tasks")
    Set<ImmutableTask> getAllTasks();

    @SqlQuery("select task_id, title, description, creator_id, created_at from tasks where id = :id")
        ImmutableTask getTaskById(@Bind("id") int id);
}

It works with simpler data types like String or int.

Answer 1

To do this, I created a simple mapper for my object. This is what the Mapper looks like:

public class TaskMapper implements ResultSetMapper<ImmutableTask> {
    public ImmutableTask map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        return ImmutableTask.builder()
                .taskId(r.getInt("task_id"))
                .creatorId(r.getInt("creator_id"))
                .title(r.getString("title"))
                .description(r.getString("description"))
                .createdAt(r.getTimestamp("created_at"))
                .build();
    }
}

(The builder is because it's an Immutable, but it can be replaced with new Task() in other cases)

I then updated my DAO with the mapper annotation to look like this:

public interface TasksDAO {
    @SqlQuery("select task_id, title, description, creator_id, created_at from tasks")
    @Mapper(TaskMapper.class)
    Set<ImmutableTask> getAllTasks();

    @SqlQuery("select task_id, title, description, creator_id, created_at from tasks where task_id = :id")
    @Mapper(TaskMapper.class)
    ImmutableTask getTaskById(@Bind("id") int id);
}