@BindSqlSelect

Allows to query a database table. When you define the query through interface's method you can define query parameter by a DAO's associated bean instance, or directly with fields.

Attributes

• distinct: if true insert distinct clause in SQL statement.
• excludedFields: properties to exclude from the SELECT statement.
• fields: properties to include into the SELECT statement.
• groupBy: GROUP BY statement. It is not necessary to include GROUP BY words in statements, they are added automatically.
• having: HAVING statement. It is not necessary to include HAVING words in statements, they are added automatically.
• jql: allows specifying the entire query with JQL.
• orderBy: ORDER BY statement. It is not necessary to include ORDER BY words in statements, they are added automatically.
• pageSize: If the method returns a paginated result, this attribute allows to specify the size of the page.
• where: WHERE statement. It is not necessary to include WHERE words in statements, they have added automatically.

Query parameters

Almost all parameters used in method can be used as query parameter.

@BindSqlSelect(where = "name=${name} and surname=${surname}")
Person selectOne(String name, @BindSqlParam("surname") String temp);

Parameters of where condition are linked to method parameters with the syntax ${<name of parameter>}

Return query result

There are many return type allowed for method which define a query:

• a DAO's associated bean instance
• list of associated bean
• set of associated bean
• Cursor: it is possible to wrap cursor with the cursor wrapper generated for bean associated to DAO. For example, given a Person and PersonDAO, will be generated BindPersonCursor.
• It is possible to set return type as Void and define a OnReadBeanListener which a method void onRead(E bean, int row, int rowCount) allow to manage each row of result with only one bean (reused) instance.
• It is possible to set return type as Void and define a OnReadCursorListener which a method void onRead(Cursor cursor) allows to manage resultset iteration with a cursor.
• LiveData
• [Paginated result](Paginated result)

Example

Given a Java class definition:

@BindType
public class Person {
  public long id;
  public String name;
  public String surname;
  public String birthCity;
  public Date birthDay;
}

And a associated DAO definition:

@BindDao(Person.class)
public interface PersonDAO {
  @BindSqlSelect(orderBy="name")
  List<Person> selectAll();
	
  @BindSqlSelect(where="name like ${name} || '%%' ", orderBy="name")
  Set<Person> selectAll(String name);
	
  @BindSqlSelect(orderBy="name")
  void selectBeanListener(OnReadBeanListener<Person> beanListener);
	
  @BindSqlSelect(orderBy="name")
  void selectCursorListener(OnReadCursorListener cursorListener);
}

When Kripton annotation processor examine @BindDao annotation, it generates the following DAO implementations:

/**
 * <p>
 * DAO implementation for entity <code>Person</code>, based on interface <code>PersonDAO</code>
 * </p>
 *
 *  @see Person
 *  @see PersonDAO
 *  @see PersonTable
 */
public class PersonDAOImpl extends AbstractDao implements PersonDAO {
  public PersonDAOImpl(BindPersonDataSource dataSet) {
    super(dataSet);
  }

  /**
   * <h2>Select SQL:</h2>
   * <p>
   * <pre>SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name</pre>
   *
   * <h2>Projected columns:</h2>
   * <p>
   * <dl>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * 	<dt>name</dt><dd>is associated to bean's property <strong>name</strong></dd>
   * 	<dt>surname</dt><dd>is associated to bean's property <strong>surname</strong></dd>
   * 	<dt>birth_city</dt><dd>is associated to bean's property <strong>birthCity</strong></dd>
   * 	<dt>birth_day</dt><dd>is associated to bean's property <strong>birthDay</strong></dd>
   * </dl>
   *
   *
   * @return collection of bean or empty collection.
   */
  @Override
  public List<Person> selectAll() {
    // build where condition
    String[] args={};

    Logger.info(StringUtils.formatSQL("SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name"),(Object[])args);
    Cursor cursor = database().rawQuery("SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name", args);
    Logger.info("Rows found: %s",cursor.getCount());

    LinkedList<Person> resultList=new LinkedList<Person>();
    Person resultBean=null;

    if (cursor.moveToFirst()) {

      int index0=cursor.getColumnIndex("id");
      int index1=cursor.getColumnIndex("name");
      int index2=cursor.getColumnIndex("surname");
      int index3=cursor.getColumnIndex("birth_city");
      int index4=cursor.getColumnIndex("birth_day");

      do
       {
        resultBean=new Person();

        if (!cursor.isNull(index0)) { resultBean.id=cursor.getLong(index0); }
        if (!cursor.isNull(index1)) { resultBean.name=cursor.getString(index1); }
        if (!cursor.isNull(index2)) { resultBean.surname=cursor.getString(index2); }
        if (!cursor.isNull(index3)) { resultBean.birthCity=cursor.getString(index3); }
        if (!cursor.isNull(index4)) { resultBean.birthDay=DateUtils.read(cursor.getString(index4)); }

        resultList.add(resultBean);
      } while (cursor.moveToNext());
    }
    cursor.close();

    return resultList;
  }

  /**
   * <h2>Select SQL:</h2>
   * <p>
   * <pre>SELECT id, name, surname, birth_city, birth_day FROM person WHERE name like ${name} || \'%%\' ORDER BY name</pre>
   *
   * <h2>Projected columns:</h2>
   * <p>
   * <dl>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * 	<dt>name</dt><dd>is associated to bean's property <strong>name</strong></dd>
   * 	<dt>surname</dt><dd>is associated to bean's property <strong>surname</strong></dd>
   * 	<dt>birth_city</dt><dd>is associated to bean's property <strong>birthCity</strong></dd>
   * 	<dt>birth_day</dt><dd>is associated to bean's property <strong>birthDay</strong></dd>
   * </dl>
   *
   * <h2>Query's parameters:</h2>
   * <p>
   * <dl>
   * 	<dt>${name}</dt><dd>is binded to method's parameter <strong>name</strong></dd>
   * </dl>
   *
   * @param name
   * 	is binded to ${name}
   *
   * @return collection of bean or empty collection.
   */
  @Override
  public Set<Person> selectAll(String name) {
    // build where condition
    String[] args={(name==null?null:name)};

    Logger.info(StringUtils.formatSQL("SELECT id, name, surname, birth_city, birth_day FROM person WHERE name like '%s' || \'%%\' ORDER BY name"),(Object[])args);
    Cursor cursor = database().rawQuery("SELECT id, name, surname, birth_city, birth_day FROM person WHERE name like ? || \'%%\' ORDER BY name", args);
    Logger.info("Rows found: %s",cursor.getCount());

    HashSet<Person> resultList=new HashSet<Person>();
    Person resultBean=null;

    if (cursor.moveToFirst()) {

      int index0=cursor.getColumnIndex("id");
      int index1=cursor.getColumnIndex("name");
      int index2=cursor.getColumnIndex("surname");
      int index3=cursor.getColumnIndex("birth_city");
      int index4=cursor.getColumnIndex("birth_day");

      do
       {
        resultBean=new Person();

        if (!cursor.isNull(index0)) { resultBean.id=cursor.getLong(index0); }
        if (!cursor.isNull(index1)) { resultBean.name=cursor.getString(index1); }
        if (!cursor.isNull(index2)) { resultBean.surname=cursor.getString(index2); }
        if (!cursor.isNull(index3)) { resultBean.birthCity=cursor.getString(index3); }
        if (!cursor.isNull(index4)) { resultBean.birthDay=DateUtils.read(cursor.getString(index4)); }

        resultList.add(resultBean);
      } while (cursor.moveToNext());
    }
    cursor.close();

    return resultList;
  }

  /**
   * <h2>Select SQL:</h2>
   * <p>
   * <pre>SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name</pre>
   *
   * <h2>Projected columns:</h2>
   * <p>
   * <dl>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * 	<dt>name</dt><dd>is associated to bean's property <strong>name</strong></dd>
   * 	<dt>surname</dt><dd>is associated to bean's property <strong>surname</strong></dd>
   * 	<dt>birth_city</dt><dd>is associated to bean's property <strong>birthCity</strong></dd>
   * 	<dt>birth_day</dt><dd>is associated to bean's property <strong>birthDay</strong></dd>
   * </dl>
   *
   * @param beanListener
   * 	is the Person listener
   */
  @Override
  public void selectBeanListener(OnReadBeanListener<Person> beanListener) {
    // build where condition
    String[] args={};

    Logger.info(StringUtils.formatSQL("SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name"),(Object[])args);
    Cursor cursor = database().rawQuery("SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name", args);
    Logger.info("Rows found: %s",cursor.getCount());
    Person resultBean=new Person();
    try {
      if (cursor.moveToFirst()) {

        int index0=cursor.getColumnIndex("id");
        int index1=cursor.getColumnIndex("name");
        int index2=cursor.getColumnIndex("surname");
        int index3=cursor.getColumnIndex("birth_city");
        int index4=cursor.getColumnIndex("birth_day");

        int rowCount=cursor.getCount();
        do
         {
          // reset mapping
          resultBean.id=0L;
          resultBean.name=null;
          resultBean.surname=null;
          resultBean.birthCity=null;
          resultBean.birthDay=null;

          // generate mapping
          if (!cursor.isNull(index0)) { resultBean.id=cursor.getLong(index0); }
          if (!cursor.isNull(index1)) { resultBean.name=cursor.getString(index1); }
          if (!cursor.isNull(index2)) { resultBean.surname=cursor.getString(index2); }
          if (!cursor.isNull(index3)) { resultBean.birthCity=cursor.getString(index3); }
          if (!cursor.isNull(index4)) { resultBean.birthDay=DateUtils.read(cursor.getString(index4)); }

          beanListener.onRead(resultBean, cursor.getPosition(), rowCount);
        } while (cursor.moveToNext());
      }
    } finally {
      if (!cursor.isClosed()) {
        cursor.close();
      }
    }
  }

  /**
   * <h2>Select SQL:</h2>
   * <p>
   * <pre>SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name</pre>
   *
   * <h2>Projected columns:</h2>
   * <p>
   * <dl>
   * 	<dt>id</dt><dd>no bean's property is associated</dd>
   * 	<dt>name</dt><dd>no bean's property is associated</dd>
   * 	<dt>surname</dt><dd>no bean's property is associated</dd>
   * 	<dt>birth_city</dt><dd>no bean's property is associated</dd>
   * 	<dt>birth_day</dt><dd>no bean's property is associated</dd>
   * </dl>
   *
   * @param cursorListener
   * 	is the cursor listener
   */
  @Override
  public void selectCursorListener(OnReadCursorListener cursorListener) {
    // build where condition
    String[] args={};

    Logger.info(StringUtils.formatSQL("SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name"),(Object[])args);
    Cursor cursor = database().rawQuery("SELECT id, name, surname, birth_city, birth_day FROM person WHERE 1=1 ORDER BY name", args);
    Logger.info("Rows found: %s",cursor.getCount());

    try {
      if (cursor.moveToFirst()) {

        do
         {
          cursorListener.onRead(cursor);
        } while (cursor.moveToNext());
      }
    } finally {
      if (!cursor.isClosed()) {
        cursor.close();
      }
    }
  }
}

So, the code to execute query selection:

// open database
instance.openReadOnlyDatabase();

// select 1
Set<Person> list=instance.getPersonDAO().selectAll("name");

// select 2
instance.getPersonDAO().selectBeanListener(new OnReadBeanListener<Person>() {
  
  @Override
  public void onRead(Person bean, int row, int rowCount) {
    // work with 
    
  }
});

// select 3
instance.getPersonDAO().selectCursorListener(new OnReadCursorListener() {
  
  @Override
  public void onRead(Cursor cursor) {
    // work directly with cursor
    
  }
});

// close database
instance.close();