SQL Object Data Manipulation

Update, insert, and data definition statements are indicated in the SQL Object API via the @SqlUpdate annotation. The methods for these statements must have either void or int return types. If the return type is int, then the value will be the number of rows changed. Alternatively, if the method is annotated with @GetGeneratedKeys, then the return value with be the auto-generated keys.

Update methods look like

public static interface Update
{
  @SqlUpdate("create table something (id integer primary key, name varchar(32))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  int insert(@Bind("id") int id, @Bind("name") String name);

  @SqlUpdate("update something set name = :name where id = :id")
  int update(@BindBean Something s);
}

As with queries, arguments to the statements are bound from arguments to the method.

We can exercise this code by creating and instance and just calling the methods:

DBI dbi = new DBI("jdbc:h2:mem:test");
Handle h = dbi.open();
        
Update u = h.attach(Update.class);
u.createSomethingTable();
u.insert(17, "David");
u.update(new Something(17, "David P."));

String name = h.createQuery("select name from something where id = 17")
    .map(StringMapper.FIRST)
    .first();
assertThat(name, equalTo("David P."));

h.close();