sql2o - easy database query library

Execute, fetch and map in just a few lines of code

With sql2o you execute your query, fetch the result and map it to a POJO model, in just a few lines of code.

Often it is a one-liner, as long as you don't count the try-with-resouce block.

Sql2o uses named parameters.

public List<Customer> fetchCustomers(customerId) {
    try (Connection con = sql2o.open()) {
    final String query =
        "SELECT id, name, address " +
        "FROM customers WHERE id = :customerId";

    return con.createQuery(query)
        .addParameter("customerId", customerId)
        .executeAndFetch(Customer.class);
    }
}

public void insertCustomer(Customer customer) {
    final String insertQuery =
        "INSERT INTO customers (id, name, address) " +
        "VALUES (:id, :name, :address)";

    try (Connection con = sql2o.beginTransaction()) {
        con.createQuery(insertQuery)
            .addParameter("id", customer.getId())
            .addParameter("name", customer.getName())
            .addParameter("address", customer.getAddress())
            .executeUpdate();
        // Remember to call commit() when a transaction is opened,
        // default is to roll back.
        con.commit();
    }
}

Inserts, updates and deletes are equally easy

The executeUpdate() method is used for all statements that alters the database, so you will use it for both inserts, updates and deletes.

And, of course, sql2o supports transactions.