Well known persistence frameworks like Hibernate generally approach the persistence layer from the Java point of view and try to hide the SQL nature of the database. This has repeatedly been a source of programmers' headache, and we think that it is wrong to ignore the technical nature of the database when working with the persistence layer.
With sqlapi4j we approach the problem from the database/SQL point of view. We create a DSL which we want to look and behave like plain SQL and thus follows the principle of least surprise. Our approach is type safe (in contrast to, e. g., Hibernate criteria API) and even null safe, if you use JSR305 annotations and FindBugs.
Let’s have a look at the following SQL table:
create table person (
id bigint not null,
first_name varchar(255) not null,
last_name varchar(255) not null,
age int not null,
gender varchar(6) not null,
constraint person_pk primary key(id)
);
create sequence person_seq;
and the following Java model:
public class Person {
public static enum Gender {
MALE,
FEMALE,
;
}
@Nonnull private final Long id;
@Nonnull private final String firstName;
@Nonnull private final String lastName;
private final int age;
@Nonnull private final Gender gender;
public Person(@Nonnull final Long id, @Nonnull final String firstName, @Nonnull final String lastName, final int age, @Nonnull final Gender gender) {
super();
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.age = age;
this.gender = gender;
}
@Nonnull
public Long getId() {
return id;
}
@Nonnull
public String getFirstName() {
return firstName;
}
@Nonnull
public String getLastName() {
return lastName;
}
public int getAge() {
return age;
}
@Nonnull
public Gender getGender() {
return gender;
}
}
The mapping for this table and model is very simple:
public class PersonTable {
static final ColumnDef<Long> ID = ColumnDefs.long("id");
static final ColumnDef<String> FIRST_NAME = ColumnDefs.varchar("first_name");
static final ColumnDef<String> LAST_NAME = ColumnDefs.varchar("last_name");
static final ColumnDef<Integer> AGE = ColumnDefs.integer("age");
static final ColumnDef<Gender> GENDER = ColumnDefs.enum("gender", Gender.class);
static final TableDef TABLE = new TableDef("person", FIRST_NAME, LAST_NAME, AGE, GENDER);
}
As you can see, both the column type and the column nullness are fixed in the column mapping.
Building SQL select statements is now very easy, especially if you know SQL:
package com.example;
import static com.example.PersonTable.TABLE;
import static com.example.PersonTable.GENDER;
import static com.example.PersonTable.AGE;
import static com.example.PersonTable.LAST_NAME;
import static com.example.PersonTable.FIRST_NAME;
// ...
// find all female entries
final SqlCommand<SelectResult> command1 = SQL
.select(TABLE.getColumns())
.where(GENDER.eq(Gender.FEMALE));
// find all entries with age greater than 18
final SqlCommand<SelectResult> command2 = SQL
.select(TABLE.getColumns())
.where(AGE.gt(18))
.orderBy(LAST_NAME, FIRST_NAME);
// find the average age grouped by gender
final SqlCommand<SelectResult> command3 = SQL
.select(GENDER, Sql.avg(AGE))
.groupBy(GENDER);
Well, while this looks astonishingly easy, there are some more details involved when executing the commands.
try {
final Connection connection = dataSource.getConnection();
try {
final SelectResult result = command1.execute(connection);
return new Person(
row.get(PersonTable.ID),
row.get(PersonTable.FIRST_NAME),
row.get(PersonTable.LAST_NAME),
row.get(PersonTable.AGE),
row.get(PersonTable.GENDER)
);
} catch (final SQLException e) {
// ...
} finally {
if (connection != null) {
connection.close();
}
}
} catch (final SQLException e) {
// ...
}
As you might expect, this can lead to a lot of boilerplate code. Of course, you can tweak your connection and exception handling by directly accessing the core functionality of sqlapi4j, but in most cases it is more desirable to use the convenience introduced by the sqlapi4j-dao package.
sqlapi4j-dao offers a bunch of convenience wrappers making the everyday life much easier.
The AbstractDao class takes care of connection handling, exception handling, and of transforming the result rows into Java objects.
First we make minor adjustments to the Person model:
public class Person {
public static enum Gender {
MALE,
FEMALE,
;
}
public static class PersonId implements Id {
private PersonId(final long value) {
super(value);
}
@Nonnull
public static PersonId valueOf(final long value) {
return new PersonId(value);
}
}
@Nonnull private final PersonId id;
@Nonnull private final String firstName;
@Nonnull private final String lastName;
private final int age;
@Nonnull private final Gender gender;
public Person(@Nonnull final PersonId id, @Nonnull final String firstName, @Nonnull final String lastName, final int age, @Nonnull final Gender gender) {
super();
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.age = age;
this.gender = gender;
}
@Nonnull
public PersonId getId() {
return id;
}
@Nonnull
public String getFirstName() {
return firstName;
}
@Nonnull
public String getLastName() {
return lastName;
}
public int getAge() {
return age;
}
@Nonnull
public Gender getGender() {
return gender;
}
}
and to the table mapping:
public class PersonTable {
static final PKColumnDef<PersonId> ID = new PKColumnDef<PKColumnDef>("id", PersonId.class, "person_seq");
static final ColumnDef<String> FIRST_NAME = ColumnDefs.varchar("first_name");
static final ColumnDef<String> LAST_NAME = ColumnDefs.varchar("last_name");
static final ColumnDef<Integer> AGE = ColumnDefs.integer("age");
static final ColumnDef<Gender> GENDER = ColumnDefs.enum("gender", Gender.class);
static final TableDef TABLE = new TableDef("person", FIRST_NAME, LAST_NAME, AGE, GENDER);
}
The above SQL select statements now look like this:
public static final ResultTransformer<Person> TRANSFORMER = new ResultTransformer<Person>() {
@Override
public Person apply(final SqlResultRow row) {
return new Person(
row.get(ID),
row.get(FIRST_NAME),
row.get(LAST_NAME),
row.get(AGE),
row.get(GENDER)
);
}
};
// ... create dao instance ..
final List<Person> females = dao.findAll(TRANSFORMER,
TABLE,
GENDER.eq(Gender.FEMALE)
);
final List<Person> over18 = dao.findAll(TRANSFORMER, dao.sql()
.select(TABLE.getColumns())
.where(AGE.gt(18))
.orderBy(LAST_NAME, FIRST_NAME)
);
The SingleTableDao class takes the ease of use even further by defining a default table for SQL statements and a default result transformer. Of course, you still have the power of the AbstractDao at hand when you want to deviate from the defaults.
The above SQL select statements now look like this:
final List<Person> females = dao.findAll(GENDER.eq(Gender.FEMALE);
final List<Person> over18 = dao.findAll(dao.sql()
.select(TABLE.getColumns())
.where(AGE.gt(18))
.orderBy(LAST_NAME, FIRST_NAME)
);