27 june 2014 Version 1.5.1 of sql2o has been released. Click here to see the change log
Sql2o is a small java framework that makes it easy to execute sql statements on your JDBC compliant database from java.

Sql2o uses JDBC internally, so it should work with any database with a JDBC driver (*).

With sql2o you get:

Consider the following java class

public class Task{
    private int id;
    private String category;
    private Date dueDate;

    // getters and setters here
}

Say you want to fetch all tasks from your database with category='foo'. The following comparison shows how this is typically done with sql2o compared to how it is done with plain JDBC.

sql2o

Sql2o sql2o = new Sql2o(DB_URL, USER, PASS);

String sql =
    "SELECT id, category, duedate " +
    "FROM tasks " +
    "WHERE category = :category";

try(Connection con = sql2o.open()) {
    List<Task> tasks = con.createQuery(sql)
        .addParameter("category", "foo")
        .executeAndFetch(Task.class);
}

The mapping between columns and properties are done automatically by name. Of course this is not always possible, so sql2o provides a simple and flexible column mapping solution.

Plain JDBC

List<Task> tasks = new ArrayList<Task>();
Connection conn = null;
PreparedStatement stmt = null;

String sql =
    "SELECT id, category, duedate " +
    "FROM tasks " +
    "WHERE category = ?";

try {
    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.prepareStatement(sql);
    stmt.setString(1, "foo");

    ResultSet rs = stmt.executeQuery();

    while(rs.next()) {
        Task task = new Task();
        task.setId(rs.getLong("id"));
        task.setCategory(rs.getString("category"));
        task.setDueDate(rs.getDate("duedate"));
    }
    rs.close();
} catch(SQLException se) {
    //Handle errors for JDBC. I'll just throw a RuntimeException.
    throw new RuntimeExcetion("error when executing query", se);
} finally {
    try{
        if(stmt != null) {
            stmt.close();
        }
    } catch(SQLException se) {
        se.printStackTrace();
    }
    try {
        if (conn != null) {
            conn.close();
        }
    } catch(SQLException se) {
        se.printStackTrace();
    }
}

INSERT, UPDATE and DELETE statements are also made a lot easier. Just take a look at this example.

String sql =
    "INSERT INTO tasks(id, description, due_date) " +
    "VALUES (:id, :description, :dueDate)";

try (Connection con = sql2o.open()) {
    con.createQuery(sql)
        .addParameter("id", task.getId())
        .addParameter("description", task.getDescription())
        .addParameter("dueDate", task.getDueDate())
        .executeUpdate();
}

// With sql2o, all statements are queries; Even INSERT, UPDATE and DELETE statements

What sql2o is not!

sql2o is not an ORM. It has no sql generation capabilities. Don't use sql2o if you don't want to write sql.

Feature Highlights

Check out the learn section for more examples and features.

Compatibility

Sql2o is tested to work with JDBC drivers for PostgreSQL, MySQL, Oracle, MS Sql Server, IBM DB2, H2 and HyperSQL.

Fork me on GitHub