Object Relational Mapping

Object-relational mapping (ORM) is an application (library) to map your objects to a relational database; typically, you describe which classes and properties in the code map to which tables and columns in the database, and then the library does all the copying and translating. In this scenario, ORM sits between JDBC and the rest of your Java application.

Sql2o

Sql2o is a small java library, with the purpose of making database interaction easy. To use Sql2o, you must add it as a dependency to your project. Open gradle.build and add the following line to the dependencies block.

compile 'org.sql2o:sql2o:1.5.4'

Sql2oCourseDao

Let's provide an implementation for CourseDao that uses Sql2o:

public class Sql2oCourseDao implements CourseDao {

  private final Sql2o sql2o;

  public Sql2oCourseDao(Sql2o sql2o) {
    this.sql2o = sql2o;
  }

  @Override
  public void add(Course course) throws DaoException {

  }

  @Override
  public List<Course> findAll() {
    return null;
  }
}

When we use a Sql2o object, a convenient way to open a Connection is as follows:

try(Connection con = sql2o.open()) {
  // Do something with database.
} catch (Sql2oException ex) {
  // Deal with exception.
}

In case this is the first time you see a try-with-resources statement, this construct ensures that the resource is closed at the end of the statement.¹

Sql2oCourseDao.add

The power of Sql2o is in binding to an object; let's see that in action:

@Override
public void add(Course course) throws DaoException {
  try (Connection conn = sql2o.open()) {
    String sql = "INSERT INTO Courses(name, url) VALUES (:name, :url);";
    int id = (int) conn.createQuery(sql)
        .bind(course)
        .executeUpdate()
        .getKey();
    course.setId(id);
  } catch (Sql2oException ex) {
    throw new DaoException("Unable to add a course.", ex);
  }
}

Tip

For binding to work seamlessly you need to have the same name for fields in the object and column names in your table.²

Sql2oCourseDao.findAll

With Sql2o, when fetching data from the database, the ResultSet will automatically be filled into your POJO objects.

@Override
public List<Course> findAll() {
  try (Connection conn = sql2o.open()) {
    String sql = "SELECT * FROM Courses;";
    return conn.createQuery(sql).executeAndFetch(Course.class);
  }
}

---

1. A resource is an object that must be closed after the program is finished with it. ↩

2. If fields' name are not the same as column names, you may use column mapping feature of Sql2o. ↩