@BindSqlDelete

This annotation allows deleting a bean from a database data model. You can use the bean as the input parameter or method parameters like bean property, but you can not use mixed case. This annotation can be used only on DAO's interface methods.

For example, suppose we persist bean Person defined as follow:

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

The associated DAO interface is

@BindDao(Person.class)
public interface PersonDAO { }

Case 1 - Method using a bean type parameter

It's possible to define a DELETE query with annotation BindSqlDelete. It is possible to define query parameter simply using method parameter with the same name of the bean property.

@BindDao(Person.class)
public interface PersonDAO {
 
  @BindDelete(where="name=${name} and surname=${surname}")
  void deleteOne(String name, @BindSqlParam("surname") temp);
 
  @BindDelete(where="name=${name} and surname=${surname}")
  long deleteTwo(String name, @BindSqlParam("surname") temp);
}

Each method parameter will be used like input parameter for the query. The name of parameters will be used to map field bean and then the column name of the associated table. If you specify a return type for methods (like method insertTwo), it has to be of type int, long, Integer, Long. In this case, the return value will be the id value of just inserted row.

Case 2 - method use its parameters like bean properties

The other way to define a DELETE SQL is using a bean as input parameter:

@BindDao(Person.class)
public interface PersonDAO { 
  @BindDelete(where = " id = ${bean.id} ")
  void deleteThree(Person bean);
}

If you specify a return type for methods, it has to be of type int, long, Integer, Long and it will contains the number of deleted rows.

Attributes

• where: where condition. It is possible to specify a parameter bound to method's parameter with the syntax ${<parameter name>}.
• jql: JQL value. With this attribute, it is possible to specify directly the JQL code. JQL means that you can write SQL using field's names and class name indeed of column and table names. Moreover, it is possible to specify where to use the dynamic parts of the query through dynamic statements like DYNAMIC_WHERE, DYNAMIC_ORDER_BY, DYNAMIC_PAGE_SIZE, DYNAMIC_PAGE_OFFSET, encapsulated in #{dynamic-part-name}

For example, for a select statement, you can write:

@BindSqlDelete(jsql="SELECT * FROM media WHERE mediaId IN (SELECT mediaId FROM fav WHERE #{DYNAMIC_WHERE}) ORDER BY indx DESC LIMIT 0, 100")
void delete(@BindDynamicWhere where);

Usage

Suppose we persist bean Person defined as follow:

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

The associated DAO interface is

@BindDao(Person.class)
public interface PersonDAO {
 
  // raw delete
  @BindDelete(where="name=${name} and surname=${surname}")
  void deleteOne(String name, @BindSqlParam("surname") temp);
 
  // raw delete
  @BindDelete(where="name=${name} and surname=${surname}")
  long deleteTwo(String name, @BindSqlParam("surname") temp);

  // managed bean delete
  @BindDelete(where="id=${bean.id}")
  void deleteThree(Person bean);
}

There is two kind of delete:

• Raw delete: method parameters are used parameters of where condition: every method's parameter must be used as the query parameter. If you specify a return type for methods (like method deleteTwo), it has to be of type int, long, Integer, Long. In this case, the return value will be the deleted rows count. @BindSqlParam annotation can be used to specify a different column name associated to a specific method's parameter.

• Managed bean delete: method has only one managed bean as parameter: method accepts only one managed bean class type parameter. If you specify a return type for methods, it has to be of type int, long, Integer, Long and it will contain delete rows count. If the method returns a boolean value, it will be set to true in case any row was deleted.

For interface PersonDAO, Kripton annotation processor will generate the following Person DAO implementation:

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

  /**
   * <p>SQL delete:</p>
   * <pre>DELETE person WHERE name=${name} and surname=${surname}</pre>
   *
   * <p><strong>Where parameters:</strong></p>
   * <dl>
   * 	<dt>${name}</dt><dd>is mapped to method's parameter <strong>name</strong></dd>
   * 	<dt>${surname}</dt><dd>is mapped to method's parameter <strong>temp</strong></dd>
   * </dl>
   *
   * @param name
   * 	is used as where parameter <strong>${name}</strong>
   * @param temp
   * 	is used as where parameter <strong>${surname}</strong>
   */
  @Override
  public void deleteOne(String name, String temp) {
    String[] whereConditions={(name==null?null:name), (temp==null?null:temp)};

    Logger.info(StringUtils.formatSQL("DELETE person WHERE name=%s and surname=%s"), (Object[])whereConditions);
    int result = database().delete("person", "name=? and surname=?", whereConditions);
  }

  /**
   * <p>SQL delete:</p>
   * <pre>DELETE person WHERE name=${name} and surname=${surname}</pre>
   *
   * <p><strong>Where parameters:</strong></p>
   * <dl>
   * 	<dt>${name}</dt><dd>is mapped to method's parameter <strong>name</strong></dd>
   * 	<dt>${surname}</dt><dd>is mapped to method's parameter <strong>temp</strong></dd>
   * </dl>
   *
   * @param name
   * 	is used as where parameter <strong>${name}</strong>
   * @param temp
   * 	is used as where parameter <strong>${surname}</strong>
   *
   * @return number of deleted records
   */
  @Override
  public long deleteTwo(String name, String temp) {
    String[] whereConditions={(name==null?null:name), (temp==null?null:temp)};

    Logger.info(StringUtils.formatSQL("DELETE person WHERE name=%s and surname=%s"), (Object[])whereConditions);
    int result = database().delete("person", "name=? and surname=?", whereConditions);
    return result;
  }

  /**
   * <p>SQL delete:</p>
   * <pre>DELETE person WHERE id = ${bean.id}</pre>
   *
   * <p><strong>Parameters used in where conditions:</strong></p>
   * <dl>
   * 	<dt>${bean.id}</dt><dd>is mapped to method's parameter <strong>bean.id</strong></dd>
   * </dl>
   *
   * @param bean
   * 	is used as ${bean}
   */
  @Override
  public void deleteThree(Person bean) {
    String[] whereConditions={String.valueOf(bean.id)};

    Logger.info(StringUtils.formatSQL("id = %s"), (Object[])whereConditions);
    int result = database().delete("person", "id = ?", whereConditions);
  }
}

An example of usage:

BindPersonDataSource instance=BindPersonDataSource.instance();
instance.openDatabase();
instance.getPersonDAO().deleteOne("hellp", "aa");
instance.close();