Dropwizard + JDBI 增刪改查完整示例：從配置到代碼實戰

環境準備

<dependencies>
  <dependency>
    <groupId>io.dropwizard</groupId>
    <artifactId>dropwizard-core</artifactId>
    <version>2.0.x</version>
  </dependency>
  <dependency>
    <groupId>io.dropwizard</groupId>
    <artifactId>dropwizard-jdbi3</artifactId>
    <version>2.0.x</version>
  </dependency>
  <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
  </dependency>
</dependencies>

配置數據源

# config.yml
database:
  driverClass: org.h2.Driver
  url: jdbc:h2:file:./data/mydb
  user: sa
  password: 
  properties:
    charSet: UTF-8

實體與 DAO

public class User {
    public long id;
    public String name;
    public String email;
}

public interface UserDao {
    @SqlUpdate("CREATE TABLE IF NOT EXISTS users (id IDENTITY, name VARCHAR, email VARCHAR)")
    void createTable();

    @SqlUpdate("INSERT INTO users (name, email) VALUES (:name, :email)")
    @GetGeneratedKeys
    long insert(@BindBean User user);

    @SqlQuery("SELECT * FROM users WHERE id = :id")
    Optional<User> findById(@Bind("id") long id);

    @SqlQuery("SELECT * FROM users")
    List<User> listAll();

    @SqlUpdate("UPDATE users SET name = :name, email = :email WHERE id = :id")
    void update(@BindBean User user);

    @SqlUpdate("DELETE FROM users WHERE id = :id")
    void delete(@Bind("id") long id);
}

資源類與應用啟動

@Path("/users")
@Produces(MediaType.APPLICATION_JSON)
public class UserResource {
    private final UserDao dao;
    public UserResource(UserDao dao) { this.dao = dao; }

    @POST
    public Response create(User user) {
        long id = dao.insert(user);
        user.id = id;
        return Response.status(Response.Status.CREATED).entity(user).build();
    }
    @GET @Path("/{id}") public Optional<User> get(@PathParam("id") long id) { return dao.findById(id); }
    @GET public List<User> list() { return dao.listAll(); }
    @PUT @Path("/{id}") public void update(@PathParam("id") long id, User user) { user.id = id; dao.update(user); }
    @DELETE @Path("/{id}") public void delete(@PathParam("id") long id) { dao.delete(id); }
}

public class MyApp extends Application<MyConfig> {
    @Override public void run(MyConfig cfg, Environment env) {
        final Jdbi jdbi = env.jdbi();
        final UserDao dao = jdbi.onDemand(UserDao.class);
        dao.createTable();
        env.jersey().register(new UserResource(dao));
    }
    public static void main(String[] args) throws Exception { new MyApp().run(args); }
}

關鍵點總結

• 使用 @BindBean 自動映射實體屬性到 SQL 參數。
• @GetGeneratedKeys 配合 @SqlUpdate 獲取自增 ID。
• DAO 接口無需實現類，JDBI 在運行時動態生成代理。
• 事務管理：jdbi.inTransaction(handle -> { ... }) 或 @Transaction 註解。