Object Relational Mapping (ORM)

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.

implementation group: 'org.sql2o', name: 'sql2o', version: '1.6.0'

Sql2oAuthorDao

Let's provide an implementation for AuthorDao interface that uses Sql2o:

package persistence;
import exception.DaoException;
import model.Author;
import java.util.List;
import org.sql2o.*;
public class Sql2oAuthorDao implements AuthorDao {
private final Sql2o sql2o;
public Sql2oAuthorDao(Sql2o sql2o) {
this.sql2o = sql2o;
}
@Override
public int add(Author author) throws DaoException {
// implementation given below
}
@Override
public List<Author> listAll() {
// implementation given below
}
}

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.1

Sql2oAuthorDao.add

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

@Override
public int add(Author author) throws DaoException {
try (Connection con = sql2o.open()) {
String query = "INSERT INTO Authors (name, numOfBooks, nationality)" +
"VALUES (:name, :numOfBooks, :nationality)";
int id = (int) con.createQuery(query, true)
.bind(author)
.executeUpdate().getKey();
author.setId(id);
return id;
}
}

Note that we return the generated id integer value by the database from the add method for confirmation.

info

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

Sql2oBookDao.listAll

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

@Override
public List<Author> listAll() {
String sql = "SELECT * FROM Authors";
try (Connection con = sql2o.open()) {
return con.createQuery(sql).executeAndFetch(Author.class);
}
}
info

Similarly, you can define a Sql2oBookDao class.

Important note on the foreign key constraint

When implementing BookDao.Add to insert a new record into the Books table using Sql2oBookDao.add, you need a valid authorId, since there is a foreign key constraint on that attribute. This means any value you use there must already exist in Authors table; an author with that particular id value must be present in the Authors table before trying to add the book.3

  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.↩
  3. We will revisit this issue in more details when we get into implementing MyBooksApp as a wb application. For now, make sure when adding a new book, authorId is valid.↩
Edit this page