JDBI and the Specification Pattern

No comments
Advertisements

JDBI is a library to access to relation data in Java, it provides a convenient and idiomatic way to interact easily with the persistence layer. As other persistence frameworks/libraries it’s build on top of JDBC and provides a different approach to ORM (JPA, Hibernate, Spring Data) to interact with the database, oriented to a execute the SQL sentences through a Fluent API or a Declarative API (using annotations).

JDBI is a nice fit for some projects, where the overhead of JPA mapping, and at the same the overwhelmed of working directly with JDBC are not required or desired. It will provide a lot of helpful features to simplify the life of the developers.

However, it doesn’t provide an out of the box feature to implement the Specification Pattern, as Spring JPA Data already provides. So, if we want to implement the Specification Pattern using JDBI we will need to create some custom code to support it. Fortunately, it requires just a few classes to do it.

On this article, I share some code to implement the Specification Pattern in JDBI.

Implementing the Specification Pattern on JDBI

Specification Interface

We start by creating a new Interface JdbiSpecification with some abstract methods, that will be implemented.

import org.skife.jdbi.v2.Query;

public interface JdbiSpecification {
  String toSQL();
  Query<Map<String, Object>> bind(Query<Map<String, Object>> query);
}

The method toSQL will return the fragment of a SQL for that specification. The method bind will be used to bind the parameters on SQL fragment.

Creating a Custom Specification

With the Specification interface, we can create our owns custom specification.

import org.skife.jdbi.v2.Query;

public class PersonIdEquals implements JdbiSpecification{
  private long id;
  public PersonIdEquals(long id){
    this.id = id;
  }

  public String toSQL(){
    return "p.id = :personId";
  }
  public Query<Map<String, Object>> bind(Query<Map<String, Object>> query){
    return query.bind("personId", id);
  }
}

We can create as many “custom specification”s as we need, using other fields and other operations like; not equals, in, not in, greater than, etc.

import org.skife.jdbi.v2.Query;

public class PersonStatusIn implements JdbiSpecification{
  private List<String> status;
  private final String bindingName = "personaStatus";
  
  public PersonStatusIn(List<String> status){
    this.status = status;
  }

  public String toSQL(){
    var index = 0;
    var preparedInParameters = "";
    var statusSize = status.size();
    while (index < statusSize) {
      preparedInParameters = preparedInParameters + ":"+bindingName+"_"+index;
      index++;
      if (index < statusSize) {
        preparedInParameters = preparedInParameters + ", ";
      }
    }
    return "p.status IN ("+preparedInParameters+")";
  }
  
  public Query<Map<String, Object>> bind(Query<Map<String, Object>> query){
    var boundedQuery = query;
    var index = 0;
    while (index < statusSize.size) {
      boundedQuery = boundedQuery.bind(bindingName+"_"+index, status.get(index));
      index++;
    }
    return boundedQuery;
  }
}

SQL Operation Implementations

In order to get the expected results from a database, we use different SQL operations while creating the SQL query sentence, like: AND, OR, GroupBy, OrderBy. So let’s provide support for some of those operations. So, let’s create some custom specification for that purpose.

For those operations that will contain only one specification we can use the following class.

public abstract class AbstractClauseSpecification extends JdbiSpecification{
  
  protected JdbiSpecification specification;
  
  public AbstractClauseSpecification(JdbiSpecification specification) {
    this.specification = specification;
  }

  @Override
  public Query<Map<String, Object>> bind(Query<Map<String, Object>> query){
    return specification.bind(query);
  }
}

For those operations that interact with 2 specifications we can use the following class.

public abstract class AbstractCompositeClauseSpecification extends JdbiSpecification{
  
  protected JdbiSpecification first;
  protected JdbiSpecification second;
  
  public AbstractClauseSpecification(JdbiSpecification first, JdbiSpecification second) {
    this.first = first;
    this.second = second;
  }

  @Override
  public Query<Map<String, Object>> bind(Query<Map<String, Object>> query){
    var newQuery = first.bind(query);
    return second.bind(newQuery);
  }
}

And now, we can create the operations.

Advertisements

AndSpecification

public class AndSpecification extends AbstractCompositeClauseSpecification {
  public final String AND_OPERATOR = "AND";
  
  public AndSpecification(JdbiSpecification first, JdbiSpecification second){
    super(first, second);
  }
  
  @Override 
  public String toSQL(){
    return first.toSQL+" "+AND_OPERATOR+" "+second.toSQL();
  }
}

OrSpecification

public class OrSpecification extends AbstractCompositeClauseSpecification {
  public final String OR_OPERATOR = "OR";
  
  public OrSpecification(JdbiSpecification first, JdbiSpecification second){
    super(first, second);
  }
  
  @Override 
  public String toSQL(){
    return first.toSQL+" "+OR_OPERATOR+" "+second.toSQL();
  }
}

WhereSpecification

public class WhereSpecification extends AbstractClauseSpecification {
  public final String WHERE_OPERATOR = "WHERE";
  
  public WhereSpecification(JdbiSpecification specification){
    super(specification);
  }
  
  @Override 
  public String toSQL(){
    return " "+WHERE_OPERATOR+" "+specification.toSQL();
  }
}

GroupBySpecification

public class GroupBySpecification extends AbstractClauseSpecification {
  public final String GROUPBY_OPERATOR = "GROUP BY";
  private List<String> groupFields;
  
  public GroupBySpecification(JdbiSpecification specification, List<String> groupFields){
    super(specification);
    this.groupFields = groupFields;
  }
  
  @Override 
  public String toSQL(){
    var groupBy = groupFields.stream()
      .collect(Collectors.joining(", "));
    return specification.toSQL()+" "+GROUPBY_OPERATOR+" "+groupBy";
  }
}

OrderBySpecification

public class OrderBySpecification extends AbstractClauseSpecification {
  public final String ORDERBY_OPERATOR = "ORDER BY";
  private List<SortField> sortFields;
  
  public OrderBySpecification(JdbiSpecification specification, List<SortField> sortFields){
    super(specification);
    this.sortFields = sortFields;
  }
  
  @Override 
  public String toSQL(){
    var orderBy = sortFields.stream()
      .map(sf -> {
        var sortOrder = sf.sortOrder != null ? " "+sf.sortOrder.toString : "";
        return " "+sf.field+sortOrder;
      })
      .collect(Collectors.joining(", "));
    return specification.toSQL()+" "+ORDERBY_OPERATOR+" "+orderBy";
  }
  
  public static class SortField {
    private String field;
    private SortOrder sortOrder;
    
    public SortField(String field){
      this.field = field;
    }
    
    public SortField(String field, SortOrder order){
      this.field = field;
      this.sortOrder = order;
    }
  }
  
  public enum SortOrder {
    ASC, DESC;
  }
}

GroupedSpecification

public class GroupedSpecification extends AbstractClauseSpecification {

  public GroupedSpecification(JdbiSpecification specification){
    super(specification);
  }
  
  @Override 
  public String toSQL(){
   return "("+specification.toSQL+")";
  }
}

Those are the main clause specification that we might need to use to create most of the queries.

The Specification Executor

Because the idea behind the specification pattern is to have some common methods that will create and execute the queries for us, based on the specifications send as a parameter. In Spring Framework, this is done through the JpaSpecificationExecutor , following the same pattern we will create our JdbiSpecificationExecutor .

It’s done using an interface with some default methods. Why not an abstract class? We could use an abstract class for that purpose, but we are providing behavior, not inheritance, so an interface is a better approach. Also, another reason is that in JDBI we can create the DBI objects (DAO or Repository) as interfaces or abstract classes, therefore this approach will allows to use the JdbiSpecificationExecutor on both cases.

import org.skife.jdbi.v2.sqlobject.mixins.GetHandle;
import org.skife.jdbi.v2.tweak.ResultSetMapper;

public interface JdbiSpecificationExecutor<T> extends GetHandle {

  protected ResultSetMapper<T> getMapper();
  protected String getSelectQuery();
  
  default List<T> getBy(JdbiSpecification specification) {
      withHandle(
        handle -> {
          var mapper = getMapper();  //Method implemented in the DAO
          var filterClause = specification.toSQL();
          if(sql.trim.startsWith(WhereSpecification.WHERE_OPERATOR){
            filterClause = " WHERE " + sql;
          }
          var sql = getSelectQuery() + filterClause;
          var query = handle.createQuery(sql);
          specification.bind(query)
            .map(mapper)
            .list();
        }
      )
  }

  default Optional<T> findBy(JdbiSpecification specification) {
      withHandle(
        handle => {
          var mapper = getMapper();  //Method implemented in the DAO
          var filterClause = specification.toSQL();
          if(sql.trim.startsWith(WhereSpecification.WHERE_OPERATOR){
            filterClause = " WHERE " + sql;
          }
          var sql = mapper.getSelectQuery() + filterClause;
          var query = handle.createQuery(sql);
          var result = specification.bind(query)
            .map(mapper)
            .first();
          Optional.ofNullable(result)
        }
      )
  }
}

Utility methods in the JdbiSpecification interface

At the very beginning we created the JdbiSpecification interface, that was not the final result on that interface. We will a couple or default and static methods on it, to provide some utility methods that help us to simplify creating the chain of specifications later.

import org.skife.jdbi.v2.Query;

public interface JdbiSpecification {
  String toSQL();
  Query<Map<String, Object>> bind(Query<Map<String, Object>> query);
  
  public static JdbiSpecification where(JdbiSpecification specification){
    return new WhereSpecification(specification);
  }
  
  public static JdbiSpecification grouped(JdbiSpecification specification) {
    return new GroupedSpecification(specification);
  }
  
  default AndSpecification and(JdbiSpecification second){
    return new AndSpecification(this, second);
  }

  default OrSpecification or(JdbiSpecification second){
    return new OrSpecification(this, second);
  }

  default JdbiSpecification grouped() {
    return new GroupedSpecification(this);
  }

  default OrderBySpecification orderBy(List[SortField] sortFields){
    return new OrderBySpecification(this, sortFields);
  }

  default GroupBySpecification groupBy(List[String] groupFields){
    return new GroupBySpecification(this, groupFields);
  }
}

Using the Specification Pattern

Now, we are ready to start using the Specification Pattern on JDBI.

Let’s start by creating our Repository or DAO class, and extend or implement the JdbiSpecificationExecutor on it. We will need to override and implement the methods getMapper and getSelectQuery.

Remember on JDBI we can create the DBI objects as an interface or an abstract class. This is an example of a DBI as an interface. 

public interface PersonRepository extends JdbiSpecificationExecutor<Person>{
  
  @Override 
  default ResultSetMapper<Person> getMapper(){
    return new PersonMapper(); 
  }
  
  @Override 
  default String getSelectQuery(){
    return "SELECT * FROM Person p";
  }
  
  //Still can have the common JDBI annotation approach
  @SqlUpdate("INSERT INTO \"person\" (id, \"name\") VALUES (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);
}

This is the equivalent with a DBI as an abstract class.

public abstract class PersonRepository implements JdbiSpecificationExecutor<Person>{
  
  @Override 
  public ResultSetMapper<Person> getMapper(){
    return new PersonMapper(); 
  }
  
  @Override 
  public String getSelectQuery(){
    return "SELECT * FROM Person p";
  }
  
  //Still can have the common JDBI annotation approach
  @SqlUpdate("INSERT INTO \"person\" (id, \"name\") VALUES (:id, :name)")
  public void insert(@Bind("id") int id, @Bind("name") String name);
}

Finally let’s call the methods in the PersonRepository 

//Creating the DBI object somewhere !!
Jdbi jdbi = Jdbi.create...; // Instatiate Jdbi
var personRepository = jdbi.onDemand(PersonRepository.class); //Create an ondeman DBI object

//-------
//Using the findBy method
Optional<Person> person = personRepository.findBy(where(new PersonIdEquals(42)));
Optional<Person> person2 = personRepository.findBy(new PersonIdEquals(83));
Optional<Person> person3 = personRepository.findBy(
  where(new PersonIdEquals(100)
  	.and(new PersonStatusEquals("Active")));
  
//Using the getBy method  
List<Person> persons = personRepository.getBy(new PersonStatusEquals("Active"));
  
//List of person in active status and age greater than 49, or status Inactive or default and age less than 49  
List<Person> persons2 = personRepository.getBy(where(   //this where method is the static method in JdbiSpecification
    grouped(new PersonStatusEquals("Active")
              .and(PersonAgeGreater(49))
    .or(
      grouped(new PersonStatusIn(List.of("Inactive", "Defaulter"))
              .and(PersonAgeLessThan(49)))
    )
  );

Conclusions

As we can see, by following the previous steps (adding some classes), we can provide a nice support to the Specification Pattern on JDBI. As I have indicated, the Specification Pattern is not the best fit for all the applications, sometimes we only have a few queries per table the the regular approach of using specific methods for each query is good enough.

However, for cases where the table could have several (dozens or more) of queries with some repetitive filter criteria on them, the Specification Pattern is a good fit to reduce the number of methods on our DAO or Repositories. In the case we are using JDBI, this tutorial will help you to implement it.

References

Post Views: 1,814

Advertisements
Tags

Leave a Reply

Your email address will not be published. Required fields are marked *