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
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);
}

Every DAO is bound to a specific entity of data model, so in its definition, every method can return a collection or a paginated result of associated bean, a cursor or a simple type (long, string etc).

When you work on DAO definition, the queries are defined using the Java Query Language (JQL). It's nothing else that SQL language in which class and field name is used instead of using table and column names. In a DAO definition, regardless of associated bean, it is possible to define a query in two modes:

• In compact mode: in which only specific part of the query is defined. An example:

  @BindSqlSelect(orderBy="name")
  List<CollegeStudent> getAllStudents();

In this case, we take all the students ordered by his name. The other parts of the query are already known: we know which field project (for default all), the where conditions (for default none) and which table (the one associated to CollegeStudent class).

• In explicit mode: that allows defining the entire SQL statement, using class and field names. An example:

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

In the above query, all JQL select are written. In this case we want to valorize only the name properties of the beans. There are only a where condition on id field. This kind of query is useful when it need to write complex queries.