SQL Select

Given a Dao interface definition, it is possible to define a SQL SELECT operation by method's definition annotated by @BindSqlSelect annotation. An example:

// data model definition
@BindTable
public class CollegeStudent {
  public long id;
  public String name;
  public String surname;
}

// dao interface associated to CollegeStudent
@BindDao(CollegeStudent.class)
public interface CollegeStudentDao {
  // retrieve all student
  @BindSqlSelect(orderBy="name")
  List<CollegeStudent> getAllStudents();

  // retrieve all student from table student
  @BindSqlSelect(orderBy="name")
  List<CollegeStudent> getAllStudents();

  // retrieve all student from table student
  @BindSqlSelect
  ArrayList<CollegeStudent> getAllStudents();

  // select student#name by id
  @BindSqlSelect(where="select name from CollegeStudent where id=${uid}")
  Student getStudent(int uid);
}

Select a List or a concrete implementation of a collection of model objects

The method returns a List or a concrete implementation of a collection of model objects. The collection can be an interface (like List or Set) or concrete class (like ArrayList or HashSet):

// returns result as List
// compact mode JQL: select * from students
@BindSqlSelect(where="name like ${name} + '%'")
List<CollegeStudent> getAllStudents(String name);
 
// returns result as HashSet
// explicit JQL: select * from students
@BindSqlSelect(jql="select * from collegeStudent order by name")
HashSet<CollegeStudent> getAllStudentsAsSet();

If no record will be found, an empty collection will be returned.

Select a single data model object

A DAO’s method can be configured to return a single bean

// JQL: select * from students where id=${id}
@BindSqlSelect(where = "id=${id}")
CollegeStudent getStudentById(long id);

If no entity is found, a null result will be returned.

Select a paginated result

A paginated result is an object that encapsulates the capability to return only a piece (also called page) of the select statement result. A query can return a paginated result using an specialized PaginatedResult object as result of a DAO's interface method.

@BindSqlSelect(pageSize = 20)
PaginatedResult<CollegeStudent> getAllStudentsPaginated();

Once obtained a paginated result, it is possible to move around its pages with methods PaginatedResult#nextPage, PaginatedResult#previousPage, PaginatedResult#hasNext. The partial list is gained with PaginatedResult#list.

// to get paginated result (database must be already opened)
PaginatedResult<CollegeStudent> res = daoFactory.getStudentDao().getAllStudentsPaginated();
 
// iterate over pages
while(res.hasNext()) {
  // retrieve partial list of result
  List<CollegeStudent> pageElements = res.list();
  res.nextPage();
}

If the result has no elements, an empty paginated result will be returned. In above example, the page size was fixed and defined by a @BindSqlSelect#pageSize attribute. There is another way to define pageSize that allows defining at runtime the page size dimension: the use of @BindPageSize] annotation. As a usage example, we take the previous example and we adapt it:

@BindSqlSelect
PaginatedResult<CollegeStudent> getAllStudentsPaginated(@PageSize int pageSize);

Select a cursor

In some case, it may necessary to work with cursors. Kripton obviously supports this case.

@BindSqlSelect
Cursor getAllStudentsAsCursor();

Manage result with a bean listener

Instead to create a bean for each row, a bean listener allows to reuse same bean instance for all item list. In this way for each found row, the same data model object instance will be used with the listener to present data.

@BindSqlSelect
void getAllStudentWithBeanListener(OnReadBeanListener<CollegeStudent> listener);

to manage result:

collegeStudentDao.getAllStudentWithBeanListener(new OnReadBeanListener<CollegeStudent>() {
  @Override
  public void onRead(CollegeStudent bean, int row, int rowCount) {
    // invoked for each row
  }
});

Manage result with a cursor listener

To manage query result with a cursor managed iterator, just use an OnReadCursorListener parameter. When this kind of parameter is present, Kripton generates, inside the method, an iterator that launches listener’s method for each row.

@BindSqlSelect
void getAllStudentWithCursorListener(OnReadCursorListener listener);

And the code to iterate over all result rows:

collegeStudentDao.getAllStudentWithCursorListener(new OnReadCursorListener() {
  @Override
  public void onRead(Cursor cursor) {
    // invoked for each row
  }
});