Sql2o

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:

• Functionality to automatically map the result set of your query with a java class, without the need of any annotations or configuration.
• support for named parameters.
• A smooth and flexible api that is a lot easier to read (and requires much lesser code) than plain JDBC.

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.

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

Sql2o sql2o = new Sql2o(DB_URL, USER, PASS);
List<Task> tasks = sql2o.createQuery(sql)
    .addParameter("category", "foo")
    .executeAndFetch(Task.class);

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)";

// With sql2o, all statements are queries; Even INSERT, UPDATE and DELETE statements
sql2o.createQuery(sql)
    .addParameter("id", task.getId())
    .addParameter("description", task.getDescription())
    .addParameter("dueDate", task.getDueDate())
    .executeUpdate();

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

• Automatic column to property mapping by name. Case sensitive or case-insensitive, you choose.
• Possibility to add custom column mappings if desired.
• Allows chaining of method calls, so you can do a lot of stuff on one line of code.
• Named parameters
• Slick transaction handling.
• Ability to execute multiple queries in a batch
• Compatible with JodaTime for mapping with date/datetime/timestamp fields in your database

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.