More complex JDBI data access.

gistfile1.java

@RegisterMapper(MyObjectResultSetMapper.class) public abstract class MyDao
{
    // get next object

    @Transaction(TransactionIsolationLevel.REPEATABLE_READ) // repeatable read required to maintain read and write lock until end of transaction
    public MyObject getNext(final String status)
    {
        MyObject o = findNextWithStatus1();

        if (o == null)
        {
            o = findNextWithStatus2();
        }

        if (o != null)
        {
            update(o.getId(), status);
        }

        return o;
    }

    @SqlQuery("SELECT * FROM list WHERE status = '1' AND date < NOW() ORDER BY date ASC LIMIT 1 FOR UPDATE")
    abstract MyObject findNextWithStatus1();

    @SqlQuery("SELECT * FROM list WHERE status = '2' AND date < NOW() - INTERVAL 30 MINUTE ORDER BY date DESC LIMIT 1 FOR UPDATE")
    abstract MyObject findNextWithStatus2();

    // crud operations

    @Transaction(TransactionIsolationLevel.REPEATABLE_READ)
    @SqlUpdate("UPDATE list SET status = :status WHERE id = :id")
    public abstract int update(@Bind("id") final String id, @Bind("status") final String status);
    
}