Specification Pattern vs Specific Hibernate Query

Question

My question is when to use a specification pattern, and when to use specific SQL query.

I understood that specific pattern need to collect whole collection and post filter using concrete specification. But i dont't understand the advantage in front of specific SQL query.

CarColorSpecification cc = new CarColorSpecification(RED);
CarAgeSpecification ca = new CarAgeSpecification(OLDER, 5);

ISpecification finalSpec = cc.And(ca);

List<Car> res;
List<Car> carColl = service.getCars();
foreach(Car c in carColl) {
  if(finalSpec.isSatisfiedBy(c)) {
    res.add(c);
  }
}

And the same in SQL / Hibernate

FROM Car c WHERE c.color = RED AND c.age > 5

I think it depends of the data volume to process.

Answer 1

The SQL version will run quickly provided the table is appropriately indexed for the columns in question and its size, and it will transfer a smaller volume of data between the DB server and the app server if they're different. It may, however, impose a higher load on the SQL box in terms of CPU and disk I/O usage, and in many environments, the DB server is the most expensive component to scale.

So yes, it depends a great deal on the size of the data.

Answer 2

I think a good compromise would be to combine specification pattern according to hibernate to generate HQL queries. (Maybe Linq for Java ^^ ?)

Answer 3

The Repository is used to abstract the persistence implementation away from your domain classes. In short, SQL/HQL should only exist within your repositories.

If you're dealing with high data volumes, create a new method on your Repository and call that method from your Specification.