Testing JDBI DAO objects

[Mikey]

In a Dropwizard project, I am looking to write automated tests to ensure that the DAO objects are correctly interacting with the database.

Does anyone have any suggestions (and examples) on the best way to unit test these DAO objects?

public interface MyDAO {

  @SqlQuery("Select name from something where id = :id")

  String findNameById(@Bind("id") int id); 

}

[Tatu Saloranta]

I like using an embedded SQL db like hsql/h2 in in-memory mode, set it
up with default scripts, and run against that. And testing outside
dropwizard itself, i.e. container does not really matter, unless you
are testing wiring of things.

-+ Tatu +-

[mgutz]

I do something like this in my JUnit test class.

private userDao;

@Before

public void setup() {

    DBI dbi = new DBI("jdbc:postgresql://localhost:5432/db_dev", "pgdev", "secret");

    this.userDao = dbi.onDemand(UserDAO.class);

}

@Test

public void shouldFind() {

    userDao.findById("yourid");

    //assert here