Gain SQL SELECT functionality in Java

Select, filter, and sort collections or arrays of objects with a simple mechanism

Suppose you want to display data contained in an array or collection in a table-like format, i.e., in rows and columns. You want to be able to choose which attributes to display. In addition, you want to only display a subset of the data that meet certain conditions, and you want to sort that data based on numerous sorting criteria and define a preference order for those criteria.

This is the type of functionality that SQL SELECT provides: In a SQL SELECT statement, you can define your datasource (the FROM clause); you define which attributes (columns) you'd like to see (the SELECT clause); you can add conditions (the WHERE clause); and you can define the order in which the data is presented (the ORDER BY clause).

This article describes a number of classes and interfaces that allow you to apply the same functionality to an array or a collection of objects of any type. To achieve this functionality, the mechanism described uses some design patterns.

The approach presented here has the following benefits:

  1. This article's code (available for download from Resources) allows you to present your data in many different ways without affecting the original data
  2. Sorting, filtering, and selecting the appropriate attributes are simplified, and results are quickly achieved
  3. The classes and interfaces permit you to think of each part of the SELECT clause separately, allowing you to produce neat, reusable, and extendable code

However, as you might guess, this mechanism employs generic interfaces that can be abused when misunderstood (more on that later).

The mechanism's classes and interfaces

The following list gives a short description of the classes and interfaces we need. Later you will see how they all work together to achieve the desired functionality. Each class and interface is quite simple, consisting of one or two methods.

  1. Interface Invoker defines only one method: public Object invoke(Object o). It allows you to wrap a method call in an interface and return the method's result. Later, you will see how it can be used.
  2. Interface Condition also defines one method: public boolean passes(Object o). It allows you to check any kind of object for any kind of condition and return either true or false. Again, you will soon learn how to use this interface.
  3. Class SelectStatement represents a complete SELECT statement containing a SELECT, FROM, WHERE, and ORDER BY clause.
  4. Class SelectInstruction represents a single part of a SELECT clause, namely a column and a column name. An entire SELECT clause is represented by an array of SelectInstruction objects.
  5. Class OrderInstruction represents a single part of an ORDER BY clause. An entire ORDER BY clause is represented by an array of OrderInstruction objects.

  6. Class Executor executes the SelectStatement, which contains the entire execution logic.
  7. Class ResultTable represents the data returned by executing the SelectStatement. It contains the result data in the form of an Object[][], allowing you to easily traverse and display the data.

Now, let's see how all these classes and interfaces work together. This class diagram illustrates how they relate to each other.

A SelectStatement takes an array or a collection of objects (the type doesn't matter, as long as the array or collection contains only objects from the same type); an array of SelectInstruction objects; an array of Condition objects; and an array of OrderInstruction objects. You then pass the SelectStatement object to an Executor, which executes the statement and returns a ResultTable.

So what are the SelectInstruction, Condition, OrderInstruction, and ResultTable classes all about and why do we need them? First of all, please don't be overwhelmed! Each one is simple to understand and use.

Each SelectInstruction represents one data column (or object attribute) you would like to see in the ResultTable. You define a name and an Invoker. The name appears as the column head; the Invoker is invoked on each object in the collection. When invoked, Invoker returns that object attribute you want to present in this column. This functionality resembles a SELECT statement, where you can define the attribute to display and its column name using the keyword AS (SELECT SPEED AS MAX_SPEED ..). Note: You can also pass only one SelectInstruction that defines for the column name the string *. In that case, all of the object class's getter methods are wrapped in invokers, and their result is selected.

Each Condition represents one condition that every object in the original data collection must pass. This class corresponds to the WHERE clause, which allows you to exclude any number of rows based on numerous conditions. Note: If you want to display all objects, you can simply pass a null Condition[].

Each OrderInstruction represents one rule of how to order the data. It consists of an Invoker and a Comparator. The Invoker's invoke(Object o) method should return a result (when invoked on an object in the collection) that will be compared with the result returned by another object in the collection to determine the order of the two objects. The Comparator (if supplied) compares the results of the two objects' method invocations. If no comparator is supplied, then the two invocation results are evaluated based on their own compareTo(Object o) methods (assuming they implement java.lang.Comparable).

The order of the OrderInstructions in the array is also significant: the earlier the OrderInstruction appears in the array, the higher its priority. This means that if the priority can be determined by applying the first OrderInstruction, the following OrderInstruction objects are ignored. The OrderInstruction class corresponds to a SELECT statement's ORDER BY clause. Note: If you don't want to order the results, you can simply pass a null OrderInstruction[].

The Executor now takes the SelectStatement as input, filters the objects based on the Condition[], sorts them based on the OrderInstruction[], and finally selects only those attributes you want to display, using SelectInstruction[]. The resulting data is placed in a ResultTable for easy traversal.

The ResultTable has an equally simple interface: It defines a public String[] getColumnNames() method that returns the names of all the columns in order. And it defines a public Object[][] getResultData() method that returns the actual rows and columns of data selected, filtered, and sorted.

That's it!

Generic interfaces, what for?

As I mentioned earlier, this article's solution relies on some generic method calls—namely Invoker's invoke(Object o) method and Condition's passes(Object o) method. Why are these needed and what can they do for us?

Since we want to provide a solution that can treat a collection or an array of objects equally, regardless of the contained objects' type and, at the same time, select, filter, and sort the objects based on their own attributes, we are forced to use either reflection or a generic interface. Reflection allows us to specify the method names to call on the objects and find those methods at runtime. However, using reflection in production environments is generally not advisable since compile-time problems are pushed off until runtime, and reflection doesn't perform as well as compiled code. The alternative to reflection is a generic interface: the generic interface takes any kind of object, but internally casts it to the correct type and then treats the object according to its type. This is exactly what the Invoker interface does: it allows the Executor to treat all kinds of objects equally by letting the Invoker call the type-specific method! The same applies to the Condition interface.

Note: The Apache Foundation's commons.collections API defines a Predicate interface that has the same function as our Condition interface, and a Closure interface that corresponds to our Invoker interface. I don't find those names particularly helpful, but I was partially inspired by that API.

Some design patterns I applied for this solution

This article's solution uses the Chain of Responsibility design pattern by applying a condition and sorting objects one after another on the collection's objects. Only if an object passes the first condition is it subjected to the second one. The second OrderInstruction is applied only if the first OrderInstruction fails to determine the order of the two objects.

The solution also uses the Composite design pattern. The java.util.Collections class's sort() method takes only one list and one comparator as parameters. However, we want to compare the objects based on an array of comparators. To achieve this, we create one comparator that internally calls all the others in order. Look at the source code if you are interested in seeing how such composition is achieved.

Putting our classes to use: Simple example

Let's look at an example: Class Car has three attributes: string color, double maxSpeed, boolean fourWheelDrive.

Your application allows searching for cars based on these criteria. The user can enter the color she prefers, and she can also provide the maximum speed she wants the car to have.

You would also like to limit the display to the two attributes color and maxSpeed, and you'd like to sort the data based on maxSpeed and then color. (Actually, usually you give your user the choice of what to select and how to order it!)

Here are the steps we follow to achieve this result:

  1. Define an array or collection of objects and fill it from some source:

     Object[] data = getDataFromSomewhere();
    
    

    or:

     Collection data = getDataFromSomewhere();
    
    
  2. Define the SelectInstructions for the attributes (columns) you'd like to display:

     //Define the Color column
    Invoker colorInvoker = new Invoker(){
    ...public Object invoke(Object o){
    ......return ((Car)o).getColor();
    ...} 
    };
    SelectInstruction colorSelectInstruction = new SelectInstruction("COLOR", colorInvoker);
    //Define the maxSpeed column
    Invoker speedInvoker = new Invoker(){
    ...public Object invoke(Object o){
    ......return new Integer(((Car)o).getMaxSpeed());
    ...} 
    };
    SelectInstruction maxSpeedSelectInstruction = new SelectInstruction("MAXIMUM_SPEED", speedInvoker);
    
    

    Then add these two SelectInstruction objects to a SelectInstruction[]:

     SelectInstruction [] instructions = new SelectInstruction [2];
    instructions [0] = colorSelectInstruction;
    instructions [1] = maxSpeedSelectInstruction;
    
    
  3. Create two condition objects, one for each criteria the user can choose:

     final Color color = getColorFromUserInput();
    Condition colorCondition = new  Condition {
            public boolean passes(Object o){
            return ((Car)o).getColor().equals(color);
        }
    };
    final int maxSpeed = getSpeedFromUserInput();
    Condition maxSpeedCondition = new Condition {
        public boolean passes(Object o){
            return ((Car)o).getMaxSpeed() >= maxSpeed;
        }
    }
    
    

    Then add these Conditions to a Condition[]:

     Condition[] conditions = new Condition[2];
    conditions[0] = colorCondition;
    conditions[1] = maxSpeedCondition;
    
    
  4. Create two OrderInstruction objects, one for each order criteria:

     //Note: We supply a null Comparator for this OrderInstruction, since
    //the default ordering behavior of String suits our purposes.
    //We can also reuse the invokers we defined for the SelectInstructions.
    OrderInstruction colorOrderInstruction = new  OrderInstruction(colorInvoker, null); 
    //Note: We supply a custom Comparator for this OrderInstruction, since the
    //default ordering behavior of Integer (lower number first) does not suit our purposes.
    //We want to show the higher speed at the top! (This corresponds to specifying DESC for order by 
    //in SQL.)
    OrderInstruction speedOrderInstruction = new  OrderInstruction(speedInvoker, new Comparator(){
       public int compare(Object o1, Object o2){
          if(o1 == null && o2 == null)return 0;
          if(o1 == null) return - 1;
          if(o2 == null) return 1;
          //By comparing o2 to o1, we get the opposite order.
          return ((Integer)o2.compareTo(o1);
       }
    }); 
    
    

    Now add these OrderInstructions to an OrderInstruction []:

     OrderInstruction [] orderInstructions = new OrderInstruction [2];
    orderInstructions[0] = colorOrderInstruction;
    orderInstructions [1] = speedOrderInstruction;
    
    
  5. Add the select instructions, the data, the conditions, and the order instructions to a SelectStatement:

     SelectStatement selectStatement  = new SelectStatement(selectInstructions, data,
                conditions, orderInstructions);
    
    
  6. Execute the statement and get the result:

     ResultTable resultTable  = new Executor().execute(selectStatement );
    
    
  7. Do something with the result. For example:

     System.out.print("Columns:");
            for(int i = 0; i < resultTable.getColumnNames().length; i ++){
                System.out.print("\t");
                System.out.print(resultTable.getColumnNames()[i]);
            }
            
            Object[][] resultData = resultTable.getResultData();
            for(int i = 0; i < resultData.length; i ++){
                System.out.println();
                System.out.print("Row " + i + "  :");
                for(int e = 0; e < resultData[i].length; e ++){
                    System.out.print("\t");
                    System.out.print(resultData[i][e]);
                }
                
            }
    
    

Help, this is too complicated!

What if you just want to display all public getters? Here is the code:

 SelectInstruction[] selectInstructions = new SelectInstruction[]{new SelectInstruction("*", null)};
ResultTable resultTable = new Executor().execute(new SelectStatement(selectInstructions, data);

1 2 Page 1
Page 1 of 2