@BindSqlSelect

Allow 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.
• value: properties to include into SELECT command.
• excludedFields: properties to exclude from SELECT statement.
• where: WHERE statement. It is no necessary include WHERE words in statements, they are added automatically.
• having: HAVING statement. It is no necessary include HAVING words in statements, they are added automatically.
• groupBy: GROUP BY statement. It is no necessary include GROUP BY words in statements, they are added automatically.
• orderBy: ORDER BY statement. It is no necessary include ORDER BY words in statements, they are 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.

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