This example shows how to use EasyORM to retrieve records for a custom query. The method getRecordsForCustomQuery() implements paging for several popular databases including PostgreSQL, MySQL, SQL Server, Oracle and DB2.

Custom query example code:

DBSelect dbSelect = new DBSelect();
String query="select emp.first_name,emp.last_name,emp.department, cer.cert_name from employee emp, certificate cer, employee_certificate ec" " where emp.id=ec.emp_id and ec.cert_id=cer.id";
List<EmployeeDB> employeeList = dbSelect.getRecordsForCustomQuery(query, EmployeeDB.class, 0, 0,"emp.id");

The sql query above is basically an example of a database join. In cases where a join query returns most or all records from two or more tables, the EasyORM library will not always populate the child objects. In the example above, the parent object is an instance of the EmployeeDB class (passed as an argument to the getRecordsForCustomQuery() method ), whereas the child object is an Address class instance (a class member of EmployeeDB). In order to save resources and because child objects are not present in many queries, they are not populated automatically by the EasyORM library. Still, it's possible to achieve this functionality by using the @AttributeInfo annotation described below.

Custom query example code demonstrating how to popluate a child object:

DBSelect dbSelect = new DBSelect();
String query="select employee.*, address.* from employee left outer join address on employee.address_id=address.id " ;
List<EmployeeDB> employeeList = dbSelect.getRecordsForCustomQuery(query, EmployeeDB.class, 0, 0,"employee.id");
for(EmployeeDB emp:employeeList)
emp.setAddress((AddressDB)emp.createChildObject(AddressDB.class));

Run this example

The EasyORM library will populate a child object automatically as long as it is marked with the @AttributeInfo annotation and the setChildObjects(String[] childNames) method is invoked on that object (containing the names of the child objects you want to populate) Your persistent class should contain a setter method for the child object like in the following code snippet:

AttributeInfo annotation example:

public class EmployeeDB extends DBObject{
...
private AddressDB empAddress;
@AttributeInfo
(attributeType="AddressDB")
public void setAddress(AddressDB address){
empAddress = address;
} }
//A call to populate empAddress might look like this
dbSelect.setChildObject(new String[] { "AddressDB" } );
List<EmployeeDB> employeeList = dbSelect.getRecordsForCustomQuery(query, EmployeeDB.class, 0, 0,"employee.id");