Class Query

Simple Query

Join Query

Select Clause

More about Class Query

Suppose that you have a Class named Employee, and another Class named Engineer which extends Class Employee, both Employee and Engineer are stored in an ArrayList of their respective types. Since the ArrayList is filled dynamically in runtime. You also want to pick some object which meet criteria specified beforehand.

										
class Address {
   private String city;
	
   public Address(String city) {
       this.city = city;
   }

   public String getCity() {
       return city;
   }

   public void setCity(String city) {
       this.city = city;
   }
}
										
class Employee {
   private int id;
   private int no;
   private String firstName;
   priavate String lastName;
   private Address address;
   
   public Employee (int id, int no, String firstName, String lastName) {
     this.no = no;
     this.id = id;
     this.firstName = firstName;
     this.lastName = lastName;
   }
   
   public Employee (int id, int no, String firstName, String lastName, Address address) {
	   this(id, no, firstName, lastName);
	   this.address = address;
   }
}

class Engineer extends Employee { 
   int departmentId = 6; 

   public Engineer(int id, int no, String firstName, String lastName) {
     super(id, no, firstName, lastName) 
   }
}

suppose we have the following array list which have mixed objects of generic Employee objects and Engineer.
ArrayList<Employee> arrayList =  new ArrayList<Employee>();
arrayList.add(new Employee(1,1000,"Ahmed","Elharrany"));
arrayList.add(new Employee(2,5000,"osama","oransa", new Address("cairo")));
arrayList.add(new Engineer(3,401,"waleed","amer"), new Address("Giza"));
arrayList.add(new Employee(4,301,"hisham","hassan"));
arrayList.add(new Employee(5,100,"Mahmoud","Azmy"));
arrayList.add(new Employee(6,50,"Ramy", "Sabry"));
arrayList.add(new Employee(7,200,"Abdu'allah", "Ahmed"));

Example 1: Simple Query

	
public static void main(String[] args) {																	
   //Here we just want get all employees with no greater than 300.
   String query = "Select * " 
		+ "FROM EmployeeList "
		+ "WHERE no > 300";       
		
   // We prepare our statement
   Statement<Employee> statement = Statement.getStatement();           
   statement.addEntity(arrayList, "EmployeeList");   

   //Execute our query
   ResultSet<Employee> result= statement.executeQuery(query);   
   
   //Here we get the result
   while (result.hasNext()) {
	    	System.out.println("Employee ID = " + result.get("id")
	    			+ "  Employee First Name = " + result.get("firstName") 
	    			+ "  Employee Last Name = " + result.get("lastName"));
   }
}
The Output:
Employee ID = 1   Employee First Name = Ahmed   Employee Last Name = Elharrany
Employee ID = 2   Employee First Name = osama   Employee Last Name = oransa
Employee ID = 3   Employee First Name = waleed   Employee Last Name = amer
Employee ID = 4   Employee First Name = hisham   Employee Last Name = hassan

Example 2: Simple Or Query

	
public static void main(String[] args) {																	
   //Here we just want get all employees with no greater than 300 or equal 100.
   String query = "Select * " 
		+ "FROM EmployeeList "
		+ "WHERE no > 300 or no = 100";       
		
   // We prepare our statement
   Statement<Employee> statement = Statement.getStatement();           
   statement.addEntity(arrayList, "EmployeeList");   

   //Execute our query
   ResultSet<Employee> result= statement.executeQuery(query);   
   
   //Here we get the result
   while (result.hasNext()) {
	    	System.out.println("Employee ID = " + result.get("id")
	    			+ "  Employee First Name = " + result.get("firstName") 
	    			+ "  Employee Last Name = " + result.get("lastName"));
   }
}
The Output:
Employee ID = 1   Employee First Name = Ahmed   Employee Last Name = Elharrany
Employee ID = 2   Employee First Name = osama   Employee Last Name = oransa
Employee ID = 3   Employee First Name = waleed   Employee Last Name = amer
Employee ID = 4   Employee First Name = hisham   Employee Last Name = hassan
Employee ID = 5   Employee First Name = Mahmoud  Employee Last Name = Azmy

Example 3: Simple And Query

	
public static void main(String[] args) {																	
   //Here we just want get all employees with no greater than 100 and less than 300.
   String query = "Select * " 
		+ "FROM EmployeeList "
		+ "WHERE no > 100 and no < 300";       
		
   // We prepare our statement
   Statement<Employee> statement = Statement.getStatement();           
   statement.addEntity(arrayList, "EmployeeList");   

   //Execute our query
   ResultSet<Employee> result= statement.executeQuery(query);   
   
   //Here we get the result
   while (result.hasNext()) {
	    	System.out.println("Employee ID = " + result.get("id")
	    			+ "  Employee First Name = " + result.get("firstName") 
	    			+ "  Employee Last Name = " + result.get("lastName"));
   }
}
The Output:
Employee ID = 7   Employee First Name = Abdu'allah  Employee Last Name = Ahmed

Example 4: Grouping condition

	
public static void main(String[] args) {																	
   //Here we just want get all employees with no greater than 500 or equal 100 and id greater than 1.
   String query = "Select * " 
		+ "FROM EmployeeList "
		+ "WHERE (no > 500 or no = 100) and id > 1";
		
   // We prepare our statement
   Statement<Employee> statement = Statement.getStatement();           
   statement.addEntity(arrayList, "EmployeeList");   

   //Execute our query
   ResultSet<Employee> result= statement.executeQuery(query);   
   
   //Here we get the result
   while (result.hasNext()) {
	    	System.out.println("Employee ID = " + result.get("id")
	    			+ "  Employee First Name = " + result.get("firstName") 
	    			+ "  Employee Last Name = " + result.get("lastName"));
   }
}
The Output:
Employee ID = 2  Employee First Name = osama  Employee Last Name = oransa
Employee ID = 5  Employee First Name = Mahmoud  Employee Last Name = Azmy

Example 5: Path Coverage

	
public static void main(String[] args) {																	
   //Here we just want get all employees whom are living in Cairo.
   //The address is an object in Employee class, and city is a member of Address.   
   String query = "Select * " 
		+ "FROM EmployeeList "
		+ "WHERE address.city=cairo";
		
   // We prepare our statement
   Statement<Employee> statement = Statement.getStatement();           
   statement.addEntity(arrayList, "EmployeeList");   

   //Execute our query
   ResultSet<Employee> result= statement.executeQuery(query);   
   
   //Here we get the result
   while (result.hasNext()) {
	    	System.out.println("Employee ID = " + result.get("id")
	    			+ "  Employee First Name = " + result.get("firstName") 
	    			+ "  Employee Last Name = " + result.get("lastName"));
   }
}
The Output:
Employee ID = 2  Employee First Name = osama  Employee Last Name = oransa

Note:

Join Query is supported with n-of joined entities(classes).

You could use join with no alias, but it will hurt performance.

Class Query is trying to make our more complex coding, conditions more simple so it had to implement a way to make join one or more enitities(class object) is usable. Suppose you have an array list of Employee, and another array list of Department in your company. Each object of your Employee has a reference to such Department, so you want to filter all employees whom are placed in specific department(s) under specific conditions like salary, age, sex, etc. Instead of writing a much of nested for loops, combined with multiple if statements which may become hard to read specially when it has many business logic, you have Class Query which will help to do such join query in simple, readable manner.

						
public class Department {
  private String depId;
  private String depName;
  
  public Department (String depId, String depName) {
    this.depId = depId;
    this.depName = depName;
  }
}					
					
class Employee {
   private int id;
   private int no;
   private String firstName;
   priavate String lastName;
   private Address address;
   
   public Employee (int id, int no, String firstName, String lastName) {
     this.no = no;
     this.id = id;
     this.firstName = firstName;
     this.lastName = lastName;
   }
   
   public Employee (int id, int no, String firstName, String lastName, String depId) {
	   this(id, no, firstName, lastName);
	   this.depId = depId;
   }
}

class Engineer extends Employee { 
   int departmentId = 6; 

   public Engineer(int id, int no, String firstName, String lastName) {
     super(id, no, firstName, lastName) 
   }
}

suppose we have the following array list for Employee.
ArrayList<Employee> empList =  new ArrayList<Employee>();
empList.add(new Employee(1,1000,"Ahmed","Elharrany", "2"));
empList.add(new Employee(2,5000,"osama","oransa", "1"));
empList.add(new Engineer(3,401,"waleed","amer", "3"));
empList.add(new Employee(4,301,"hisham","hassan"));
empList.add(new Employee(5,100,"Mahmoud","Azmy"));
empList.add(new Employee(6,50,"Ramy", "Sabry"));
empList.add(new Employee(7,200,"Abdu'allah", "Ahmed", "1"));


ArrayList<Department> depList =  new ArrayList<Department>();
depList.add(new Department("1", "Main."));
depList.add(new Department("2", "IT"));
depList.add(new Department("3", "Service"));

Example 1: Simple Join Query

									
public static void main(String[] args) {									
	//Get all employees whom has a department
    String query = "Select * " +
       "FROM EmployeeX a, Department dep "+
       "WHERE dep.depId1 = a.depId";
	
	// We prepare our statement
    Statement statement = Statement.getStatement();	
    statement.addEntity(depList, "Department");
    statement.addEntity(empList, "EmployeeX");
	
	//Execute our query
    ResultSet result= statement.executeQuery(query);}
   
   //Here we get the result
    while (result.hasNext()) {
        System.out.println("Employee ID = " + result.get("id")             
             + "  Employee Last Name = " + result.get("lastName")
             + "  Employee' Dep Id = " + result.get("depId")
             + "  Employee' Dep Name = " + result.get("depName"));
   }
   
The Output: 
Employee ID = 1  Employee Last Name = Elharrany  Employee' Dep Id = 2  Employee' Dep Id = IT
Employee ID = 2  Employee Last Name = oransa  Employee' Dep Id = 1  Employee' Dep Id = Main.
Employee ID = 3  Employee Last Name = amer  Employee' Dep Id = 3  Employee' Dep Id = Service
Employee ID = 7  Employee Last Name = Ahmed  Employee' Dep Id = 1  Employee' Dep Id = Main.

Example 2: Simple Join Query with conditions

									
public static void main(String[] args) {									
	//Get all employees whom has a department whos no > 300 but not 1000
    String query = "Select * " +
       "FROM EmployeeX a, Department dep "+
       "WHERE dep.depId1 = a.depId and ((a.no > :no and a.no <> 1000))";
	
	// We prepare our statement
    Statement statement = Statement.getStatement();	
    statement.setParameter ("no", 300);	
    statement.addEntity(depList, "Department");
    statement.addEntity(empList, "EmployeeX");
	
	//Execute our query
    ResultSet result= statement.executeQuery(query);}
   
   //Here we get the result
    while (result.hasNext()) {
        System.out.println("Employee ID = " + result.get("id")             
             + "  Employee Last Name = " + result.get("lastName")
             + "  Employee' Dep Id = " + result.get("depId")
             + "  Employee' Dep Name = " + result.get("depName"));
   }
   
The Output: 
Employee ID = 2  Employee Last Name = oransa  Employee' Dep Id = 1  Employee' Dep Id = Main.
Employee ID = 3  Employee Last Name = amer  Employee' Dep Id = 3  Employee' Dep Id = Service

I will go through how we do use Select clause in Class Query. You could use it to get selective class's member or get all class memebers. It is also support path navigation. The following samples show how the Select clause is used:

							
//Get all members of such class, don't use unless it is needed.							
"Select * FROM EmployeeList";

//Here we just interested in id, and firstName							
"Select id, firstName FROM EmployeeList";

/**
 * A path coverage is supported in Select clause.
 * address is a member of Employee, and city is a member of Address object.
 */
"Select id, firstName, address.city FROM EmployeeList";

Class Query is trying to make your coding more simple, readable, and easy to understand. Here I will show what does Class Query support. Class Query supports the following;

The Relational Operators

	=       equal to
	<>      not equal to
	>       greater than
	>=      greater than or equal to
	<       less than
	<=      less than or equal to						
	
The following sample show how the operator is used:
	
							
    Snap of Simple Query/Example 1: "Select * FROM EmployeeList WHERE no > 300";

The Conditional Operators

	and      Conditional-AND
	or       Conditional-OR
	
The following sample show how the operator is used:
	
							
    Snap of Simple Query/Example 2: "Select * FROM EmployeeList WHERE no > 300 or no = 100";

The setParameter function
In most of our examples we used inline values like "Where no > 300", setParameter function will avoid this so any changes are dynamic without a need for refactoring. Here is a snap of how to use it:

							
	String query = "Select * FROM EmployeeList WHERE no > :no or no = 100";    
							
	Statement statement = Statement.getStatement(); 
	statement.setParameter ("no", 300);

    If we have many :no in our select it will be replaced by the specified value.

The getSelectedObjects function
Now you want to get the filtered objects you are not interested in while (result.hasNext()). We have two cases one when you have single entity class, and have multi-joined classes. The following sample show how the getSelectedObjects is used:

For single entity:

							
    ResultSet result= statement.executeQuery(query);
	
    List filterList = (List)result.getSelectedObjects();
	
    Alternative:
    while (result.hasNext()) {
      YOUR_Element_TYPE obj = (YOUR_Element_TYPE) result.getObject();
    }

For joined queries:

							
    String query = "SELECT * FROM Your_TYPE_X_TYPE, Your_TYPE_Y_TYPE";

    Statement statement = Statement.getStatement();	
    statement.addEntity(arrayList1, "Your_TYPE_X_TYPE");
    statement.addEntity(arayList2, "Your_TYPE_Y_TYPE");

    ResultSet result= statement.executeQuery(query);
    List filterList= (List)result.getSelectedObjects();
	
    Alternative:
    while (result.hasNext()) {
       Object[] objArray = (Object[]) result.getObject();
       Your_TYPE_OF_LIST_1 typeX = ((Your_TYPE_OF_LIST_1)objArray[0]);
       Your_TYPE_OF_LIST_2 typeY = ((Your_TYPE_OF_LIST_2)objArray[1]);
    }

Note:The result in Object[] is arranged accoroding how you define it in FROM Clause for our example if you shuffle it, then objArray[0] have to be cast to Your_TYPE_OF_LIST_2.

The ClassQuery interface
The ClassQuery interface will help you separate your business logic from its presentation, it has only one method you have to implement valueOf. The following sample show how the interface is used:

							
public class ActionImpl implements ClassQuery{
   private int actionId;
   
   public ActionImpl(int actionId) {
     this.actionId = actionId;
   }

   //Inside this method you have to place your business logic
   //This is a simple one just for presentation
   public Integer valueOf() {
     return actionId;
   }
}

class Employee {
   private int id;
   private int no;
   private String firstName;
   priavate String lastName;
   private Address address;
   private ActionImpl actionImpl; //Here is our implemnted interface
   
   public Employee (int id, int no, String firstName, String lastName) {
     this.no = no;
     this.id = id;
     this.firstName = firstName;
     this.lastName = lastName;
   }
   
   public Employee (int id, int no, String firstName, String lastName, Address address) {
	   this(id, no, firstName, lastName);
	   this.address = address;
   }
}

    //Here as you see actionImpl > :no we compare object of ActionImpl by value of integer 300.
    String query = "Select * FROM EmployeeList WHERE (no > :no or no = 100) and actionImpl > :no";    
							
    Statement statement = Statement.getStatement(); 
    statement.setParameter ("no", 300);