Query Object Pattern


Java/Java EE Tutorials Java 8 Tutorial Java Tutorial for Beginners JSP Tutorial Java Collections Tutorial JDBC 4.2 Tutorial Data Structures Tutorial
Java API Guides Google GSON Tutorial Java String API Guide Java XML Tutorial Maven Tutorial
Java Lang Package Tutorial Java SQL Package Tutorial Java Util Package Tutorial Java IO Package Tutorial Java Time Package Tutorial Java Lang Reflect Package
Java Best Practices OOPS Tutorial SOLID Principles J2EE Best Practices Java Utility Classes Free Resources
JUnit 5 Tutorial JUnit 4 Tutorial JUnit Examples
Spring Boot 2 Tutorial Spring Boot Developer Guide Spring Boot Annotations Spring Boot Free Projects
Spring Core 5 Tutorial Spring MVC 5 Tutorial Spring Data JPA Tutorial Spring's Form Tags Tutorial
Java Persistence API Hibernate 5 Tutorial Hibernate Developer Guide Spring Hibernate Tutorials
This pattern belongs to Object-Relational Metadata Mapping Patterns Catalog and this Catalog belongs to Patterns of Enterprise Application Architecture.

Intent

An object that represents a database query.

Real World Examples

The legacy Hibernate Criteria API and the modern JPA2 Criteria API are excellent examples of this pattern which combines it with the Builder Pattern.

Key Points

  • Create this query by referring to classes and fields rather than tables and columns.
  • This makes queries independently of the database schema and changes to the schema can be localized in a single place.In future, if you want to changes to the schema then you can change QueryObject class as it localized in a single place.
  • Query Object pattern actually uses Metadata Mapping Pattern to generate actual database queries.

How It Works

A Query Object is an application of the Interpreter pattern geared to represent a SQL query. Its primary roles are to allow a client to form queries of various kinds and to turn those object structures into the appropriate SQL string.
A common feature of Query Object is that it can represent queries in the language of the in-memory objects rather than the database schema. That means that, instead of using table and column names, you can use object and field names. While this isn’t important if your objects and database have the same structure, it can be very useful if you get variations between the two. In order to perform this change of view, the Query Object needs to know how the database structure maps to the object structure, a capability that really needs Metadata Mapping.
If you are using multiple databases you can design your Query Object so that it produces different SQL depending on which database the query is running against.

Advantages

The advantages of Query Object come with more sophisticated needs: keeping database schemas encapsulated, supporting multiple databases, supporting multiple schemas, and optimizing to avoid multiple queries.

Sample Code

Let's create a simple Query Object, Criteria and Mapper classes to demonstrate this pattern.

class QueryObject {
    private Class klass;
    private List criteria = new ArrayList(); 
    
    public Set execute(UnitOfWork uow) {
        this.uow = uow;
        return uow.getMapper(klass).findObjectsWhere(generateWhereClause());
    }

private String generateWhereClause() {
    StringBuffer result = new StringBuffer();
        for (Iterator it = criteria.iterator(); it.hasNext();) {
            Criteria c = (Criteria)it.next();
            if (result.length()!= 0)
            result.append("AND");
             result.append(c.generateSql(uow.getMapper(klass).getDataMap));
    }
    return result.toString();
    }
    
}

class Criteria {
    private String sqlOperator;
    protected String field;
    protected Object value; 
    
    public static Criteria greaterThan(String fieldName, int value) {
        return Criteria.greaterThan(fieldName, new Integer(value));
    }
    public static Criteria greaterThan(String fieldName, Object value) {
        return new Criteria(" > ", fieldName, value);
    }
    private Criteria(String sql, String field, Object value) {
        this.sqlOperator = sql;
        this.field = field;
        this.value = value;
    }

    public String generateSql(DataMap dataMap) {
        return dataMap.getColumnForField(field) + sqlOperator + value;
    }

    public static Criteria matches(String fieldName, String pattern){
        return new MatchCriteria(fieldName, pattern);
    }

}

public class Person{
    private String lastName;
    private String firstName;
    private int numberOfDependents
    
}

class MatchCriteria extends Criteria{
    public String generateSql(DataMap dataMap) {
    return "UPPER(" + dataMap.getColumnForField(field) + ") LIKE UPPER('" + value + "')";
    }
}

class Mapper {

   public Set findObjectsWhere (String whereClause) {
      String sql = "SELECT" + dataMap.columnList() + " FROM " + dataMap.getTableName() + " WHERE "
         + whereClause;
      PreparedStatement stmt = null;
      ResultSet rs = null;
      Set result = new HashSet();
      try {
         stmt = DB.prepare(sql);
         rs = stmt.executeQuery();
         result = loadAll(rs);
      } catch (Exception e) {
           throw new ApplicationException (e);
      } finally {DB.cleanUp(stmt, rs);
      }
    return result;
   }
}


class DataMap {

  public String getColumnForField (String fieldName) {
      for (Iterator it = getColumns(); it.hasNext();) {
         ColumnMap columnMap = (ColumnMap)it.next();
         if (columnMap.getFieldName().equals(fieldName))
            return columnMap.getColumnName();
      }
      throw new ApplicationException ("Unable to find column for " + fieldName);
  }
}

public class Client{

     public static void main(String []args){
          QueryObject query = new QueryObject(Person.class);
            query.addCriteria(Criteria.greaterThan("numberOfDependents", 0));
            
        UnitOfWork uow = new UnitOfWork();
        query.execute(uow);
     }
}

References


Comments

Post a Comment