Pattern #2: Get rid of all EXISTS and IN from sql where clauses. For example, consider a typical SQL query to find all employees who have completed a mandatory information security training:
select ee1.* from employee ee1
where exists (
select ‘true’ from employee_training et1
where et1.employeeID = ee1.employeeID
and et1.courseName = ‘Information Security’
split this into two queries:
select * from employee_training
where courseName = ‘Information Security’
select * from employee
where employeeID = ?
and let the middle-tier object graph builder (I am using Ohioedge Jeebuilder) compose it for you as below:
Collection<Employee> colOfTrainedEmployees = EmployeeTraining.query(“findTrainedEmployees”).forEach( return Employee.query(“findByEmployeeID”, e.getEmployeeID()))
The split two queries are Cassandra compliant and EXISTS/IN-logic is now in the middle-tier.