Many to many

This annotation is used to simplify management of many 2 many relationships. This annotation is not mandatory, you can always manage many 2 many relationships by yourself. @BindDaoMany2Many allows avoiding the manual creation of bean associated to the table that implements relation many 2 many between two table. It's very useful when you have many M2M relationships and they are without any custom attributes.

The link between a Person and a City can be expressed with a table named City2Person. Usually to manage this kind of relationship you need:

• to create a relation table (in the example City2Person) throw an entity.
• to create an associated DAO interface
• to define all methods needed to manage table

Quite boring...

With Kripton, to define a many to many relationships between two entities (in our example City and Person entities) you need only to define an empty DAO interface marked with @BindDaoMany2Many.

A data model representing the above example can be expressed by the following Java classes and interfaces:

// data model
@BindTable
public class City {
  public long id;
  public String name;
}

// data model
@BindTable
public class Person {
  public long id;
  public String name;
}

// dao
@BindDao(City.class)
public interface CityDao {
  @BindSqlInsert
  public void insert(City bean);
	
  @BindSqlSelect
  public List<City> listCities();
}

// dao
@BindDao(Person.class)
public interface PersonDao {
  @BindSqlInsert
  public void insert(Person bean);
	
  @BindSqlSelect
  public List<Person> listPersons();
}

// many 2 many dao interface
@BindDaoMany2Many(entity1 = City.class, entity2 = Person.class)
public interface City2PersonDao {

}

// datasource
@BindDataSource(daoSet = { CityDao.class, PersonDao.class, City2PersonDao.class }, fileName = "app.db")
public interface AppDataSource {

}

The above example is the simplest case to view how Kripton manages many to many relationships. As you can notice, for City2PersonDao DAO no methods are defined: Kripton, when detects that a DAO has @BindDaoMany2Many will generate for us:

• an entity for the relation
• table definition associated to entity
• DAO implementation
• method's to manage INSERT, SELECT, UPDATE and SELECT operation by foreign keys presents in entity

So, for the above example, it will generate many object.

First of all, a DAO interface and entity definitions are created:

// dao interface
@BindDao(CityPerson.class)
@BindGeneratedDao(dao = City2PersonDao.class)
@BindDaoMany2Many(
    entity1 = City.class,
    entity2 = Person.class
)
public interface GeneratedCity2PersonDao extends City2PersonDao {
  @BindSqlSelect(where = "id=${id}")
  CityPerson selectById(@BindSqlParam("id") long id);

  @BindSqlSelect(where = "cityId=${cityId}")
  List<CityPerson> selectByCityId(@BindSqlParam("cityId") long cityId);

  @BindSqlSelect(where = "personId=${personId}")
  List<CityPerson> selectByPersonId(@BindSqlParam("personId") long personId);

  @BindSqlDelete(where = "id=${id}")
  int deleteById(@BindSqlParam("id") long id);

  @BindSqlDelete(where = "cityId=${cityId}")
  int deleteByCityId(@BindSqlParam("cityId") long cityId);

  @BindSqlDelete(where = "personId=${personId}")
  int deleteByPersonId(@BindSqlParam("personId") long personId);

  @BindSqlInsert
  int insert(@BindSqlParam("bean") CityPerson bean);
}

The table definition:

/**
 * <p>
 * Generated entity implementation for <code>CityPerson</code>
 * </p>
 */
@BindTable(name = "city_person")
public class CityPerson {
  /**
   * Primary key
   */
  @BindColumn(columnType = ColumnType.PRIMARY_KEY)
  public long id;

  /**
   * Foreign key to City model class
   */
  @BindColumn(
      parentEntity = City.class,
      onDelete = ForeignKeyAction.CASCADE
  )
  public long cityId;

  /**
   * Foreign key to Person model class
   */
  @BindColumn(
      parentEntity = Person.class,
      onDelete = ForeignKeyAction.CASCADE
  )
  public long personId;
}

Once this intermediate operation is done, Kripton will proceed to crate the DAO and the table implementation.

The DAO implementation:

/**
 * <p>
 * DAO implementation for entity <code>CityPerson</code>, based on interface <code>GeneratedCity2PersonDao</code>
 * </p>
 *
 *  @see CityPerson
 *  @see GeneratedCity2PersonDao
 *  @see CityPersonTable
 */
public class City2PersonDaoImpl extends Dao implements GeneratedCity2PersonDao {
  private static final String SELECT_BY_ID_SQL3 = "SELECT id, city_id, person_id FROM city_person WHERE id=?";

  private static final String SELECT_BY_CITY_ID_SQL4 = "SELECT id, city_id, person_id FROM city_person WHERE city_id=?";

  private static final String SELECT_BY_PERSON_ID_SQL5 = "SELECT id, city_id, person_id FROM city_person WHERE person_id=?";

  private static SQLiteStatement deleteByIdPreparedStatement0;

  private static SQLiteStatement deleteByCityIdPreparedStatement1;

  private static SQLiteStatement deleteByPersonIdPreparedStatement2;

  private static SQLiteStatement insertPreparedStatement3;

  public City2PersonDaoImpl(BindAppDaoFactory daoFactory) {
    super(daoFactory.context());
  }

  /**
   * <h2>Select SQL:</h2>
   *
   * <pre>SELECT id, city_id, person_id FROM city_person WHERE id=${id}</pre>
   *
   * <h2>Projected columns:</h2>
   * <dl>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * 	<dt>city_id</dt><dd>is associated to bean's property <strong>cityId</strong></dd>
   * 	<dt>person_id</dt><dd>is associated to bean's property <strong>personId</strong></dd>
   * </dl>
   *
   * <h2>Query's parameters:</h2>
   * <dl>
   * 	<dt>${id}</dt><dd>is binded to method's parameter <strong>id</strong></dd>
   * </dl>
   *
   * @param id
   * 	is binded to <code>${id}</code>
   * @return selected bean or <code>null</code>.
   */
  @Override
  public CityPerson selectById(long id) {
    KriptonContentValues _contentValues=contentValues();
    // query SQL is statically defined
    String _sql=SELECT_BY_ID_SQL3;
    // add where arguments
    _contentValues.addWhereArgs(String.valueOf(id));
    String[] _sqlArgs=_contentValues.whereArgsAsArray();
    // log section BEGIN
    if (_context.isLogEnabled()) {
      // manage log
      Logger.info(_sql);

      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    try (Cursor _cursor = database().rawQuery(_sql, _sqlArgs)) {
      // log section BEGIN
      if (_context.isLogEnabled()) {
        Logger.info("Rows found: %s",_cursor.getCount());
      }
      // log section END

      CityPerson resultBean=null;

      if (_cursor.moveToFirst()) {

        int index0=_cursor.getColumnIndex("id");
        int index1=_cursor.getColumnIndex("city_id");
        int index2=_cursor.getColumnIndex("person_id");

        resultBean=new CityPerson();

        resultBean.id=_cursor.getLong(index0);
        if (!_cursor.isNull(index1)) { resultBean.cityId=_cursor.getLong(index1); }
        if (!_cursor.isNull(index2)) { resultBean.personId=_cursor.getLong(index2); }

      }
      return resultBean;
    }
  }

  /**
   * <h2>Select SQL:</h2>
   *
   * <pre>SELECT id, city_id, person_id FROM city_person WHERE city_id=${cityId}</pre>
   *
   * <h2>Projected columns:</h2>
   * <dl>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * 	<dt>city_id</dt><dd>is associated to bean's property <strong>cityId</strong></dd>
   * 	<dt>person_id</dt><dd>is associated to bean's property <strong>personId</strong></dd>
   * </dl>
   *
   * <h2>Query's parameters:</h2>
   * <dl>
   * 	<dt>${cityId}</dt><dd>is binded to method's parameter <strong>cityId</strong></dd>
   * </dl>
   *
   * @param cityId
   * 	is binded to <code>${cityId}</code>
   * @return collection of bean or empty collection.
   */
  @Override
  public List<CityPerson> selectByCityId(long cityId) {
    KriptonContentValues _contentValues=contentValues();
    // query SQL is statically defined
    String _sql=SELECT_BY_CITY_ID_SQL4;
    // add where arguments
    _contentValues.addWhereArgs(String.valueOf(cityId));
    String[] _sqlArgs=_contentValues.whereArgsAsArray();
    // log section BEGIN
    if (_context.isLogEnabled()) {
      // manage log
      Logger.info(_sql);

      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    try (Cursor _cursor = database().rawQuery(_sql, _sqlArgs)) {
      // log section BEGIN
      if (_context.isLogEnabled()) {
        Logger.info("Rows found: %s",_cursor.getCount());
      }
      // log section END

      ArrayList<CityPerson> resultList=new ArrayList<CityPerson>(_cursor.getCount());
      CityPerson resultBean=null;

      if (_cursor.moveToFirst()) {

        int index0=_cursor.getColumnIndex("id");
        int index1=_cursor.getColumnIndex("city_id");
        int index2=_cursor.getColumnIndex("person_id");

        do
         {
          resultBean=new CityPerson();

          resultBean.id=_cursor.getLong(index0);
          if (!_cursor.isNull(index1)) { resultBean.cityId=_cursor.getLong(index1); }
          if (!_cursor.isNull(index2)) { resultBean.personId=_cursor.getLong(index2); }

          resultList.add(resultBean);
        } while (_cursor.moveToNext());
      }

      return resultList;
    }
  }

  /**
   * <h2>Select SQL:</h2>
   *
   * <pre>SELECT id, city_id, person_id FROM city_person WHERE person_id=${personId}</pre>
   *
   * <h2>Projected columns:</h2>
   * <dl>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * 	<dt>city_id</dt><dd>is associated to bean's property <strong>cityId</strong></dd>
   * 	<dt>person_id</dt><dd>is associated to bean's property <strong>personId</strong></dd>
   * </dl>
   *
   * <h2>Query's parameters:</h2>
   * <dl>
   * 	<dt>${personId}</dt><dd>is binded to method's parameter <strong>personId</strong></dd>
   * </dl>
   *
   * @param personId
   * 	is binded to <code>${personId}</code>
   * @return collection of bean or empty collection.
   */
  @Override
  public List<CityPerson> selectByPersonId(long personId) {
    KriptonContentValues _contentValues=contentValues();
    // query SQL is statically defined
    String _sql=SELECT_BY_PERSON_ID_SQL5;
    // add where arguments
    _contentValues.addWhereArgs(String.valueOf(personId));
    String[] _sqlArgs=_contentValues.whereArgsAsArray();
    // log section BEGIN
    if (_context.isLogEnabled()) {
      // manage log
      Logger.info(_sql);

      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    try (Cursor _cursor = database().rawQuery(_sql, _sqlArgs)) {
      // log section BEGIN
      if (_context.isLogEnabled()) {
        Logger.info("Rows found: %s",_cursor.getCount());
      }
      // log section END

      ArrayList<CityPerson> resultList=new ArrayList<CityPerson>(_cursor.getCount());
      CityPerson resultBean=null;

      if (_cursor.moveToFirst()) {

        int index0=_cursor.getColumnIndex("id");
        int index1=_cursor.getColumnIndex("city_id");
        int index2=_cursor.getColumnIndex("person_id");

        do
         {
          resultBean=new CityPerson();

          resultBean.id=_cursor.getLong(index0);
          if (!_cursor.isNull(index1)) { resultBean.cityId=_cursor.getLong(index1); }
          if (!_cursor.isNull(index2)) { resultBean.personId=_cursor.getLong(index2); }

          resultList.add(resultBean);
        } while (_cursor.moveToNext());
      }

      return resultList;
    }
  }

  /**
   * <h2>SQL delete</h2>
   * <pre>DELETE FROM city_person WHERE id=${id}</pre>
   *
   *
   * <h2>Where parameters:</h2>
   * <dl>
   * 	<dt>${id}</dt><dd>is mapped to method's parameter <strong>id</strong></dd>
   * </dl>
   *
   * @param id
   * 	is used as where parameter <strong>${id}</strong>
   *
   * @return number of deleted records
   */
  @Override
  public int deleteById(long id) {
    if (deleteByIdPreparedStatement0==null) {
      // generate static SQL for statement
      String _sql="DELETE FROM city_person WHERE id=?";
      deleteByIdPreparedStatement0 = KriptonDatabaseWrapper.compile(_context, _sql);
    }
    KriptonContentValues _contentValues=contentValuesForUpdate(deleteByIdPreparedStatement0);
    _contentValues.addWhereArgs(String.valueOf(id));

    // generation CODE_001 -- BEGIN
    // generation CODE_001 -- END
    // log section BEGIN
    if (_context.isLogEnabled()) {

      // display log
      Logger.info("DELETE FROM city_person WHERE id=?");

      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    int result = KriptonDatabaseWrapper.updateDelete(deleteByIdPreparedStatement0, _contentValues);
    return result;
  }

  /**
   * <h2>SQL delete</h2>
   * <pre>DELETE FROM city_person WHERE city_id=${cityId}</pre>
   *
   *
   * <h2>Where parameters:</h2>
   * <dl>
   * 	<dt>${cityId}</dt><dd>is mapped to method's parameter <strong>cityId</strong></dd>
   * </dl>
   *
   * @param cityId
   * 	is used as where parameter <strong>${cityId}</strong>
   *
   * @return number of deleted records
   */
  @Override
  public int deleteByCityId(long cityId) {
    if (deleteByCityIdPreparedStatement1==null) {
      // generate static SQL for statement
      String _sql="DELETE FROM city_person WHERE city_id=?";
      deleteByCityIdPreparedStatement1 = KriptonDatabaseWrapper.compile(_context, _sql);
    }
    KriptonContentValues _contentValues=contentValuesForUpdate(deleteByCityIdPreparedStatement1);
    _contentValues.addWhereArgs(String.valueOf(cityId));

    // generation CODE_001 -- BEGIN
    // generation CODE_001 -- END
    // log section BEGIN
    if (_context.isLogEnabled()) {

      // display log
      Logger.info("DELETE FROM city_person WHERE city_id=?");

      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    int result = KriptonDatabaseWrapper.updateDelete(deleteByCityIdPreparedStatement1, _contentValues);
    return result;
  }

  /**
   * <h2>SQL delete</h2>
   * <pre>DELETE FROM city_person WHERE person_id=${personId}</pre>
   *
   *
   * <h2>Where parameters:</h2>
   * <dl>
   * 	<dt>${personId}</dt><dd>is mapped to method's parameter <strong>personId</strong></dd>
   * </dl>
   *
   * @param personId
   * 	is used as where parameter <strong>${personId}</strong>
   *
   * @return number of deleted records
   */
  @Override
  public int deleteByPersonId(long personId) {
    if (deleteByPersonIdPreparedStatement2==null) {
      // generate static SQL for statement
      String _sql="DELETE FROM city_person WHERE person_id=?";
      deleteByPersonIdPreparedStatement2 = KriptonDatabaseWrapper.compile(_context, _sql);
    }
    KriptonContentValues _contentValues=contentValuesForUpdate(deleteByPersonIdPreparedStatement2);
    _contentValues.addWhereArgs(String.valueOf(personId));

    // generation CODE_001 -- BEGIN
    // generation CODE_001 -- END
    // log section BEGIN
    if (_context.isLogEnabled()) {

      // display log
      Logger.info("DELETE FROM city_person WHERE person_id=?");

      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    int result = KriptonDatabaseWrapper.updateDelete(deleteByPersonIdPreparedStatement2, _contentValues);
    return result;
  }

  /**
   * <p>SQL insert:</p>
   * <pre>INSERT INTO city_person (city_id, person_id) VALUES (${bean.cityId}, ${bean.personId})</pre>
   *
   * <p><code>bean.id</code> is automatically updated because it is the primary key</p>
   *
   * <p><strong>Inserted columns:</strong></p>
   * <dl>
   * 	<dt>city_id</dt><dd>is mapped to <strong>${bean.cityId}</strong></dd>
   * 	<dt>person_id</dt><dd>is mapped to <strong>${bean.personId}</strong></dd>
   * </dl>
   *
   * @param bean
   * 	is mapped to parameter <strong>bean</strong>
   *
   * @return <strong>id</strong> of inserted record
   */
  @Override
  public int insert(CityPerson bean) {
    if (insertPreparedStatement3==null) {
      // generate static SQL for statement
      String _sql="INSERT INTO city_person (city_id, person_id) VALUES (?, ?)";
      insertPreparedStatement3 = KriptonDatabaseWrapper.compile(_context, _sql);
    }
    KriptonContentValues _contentValues=contentValuesForUpdate(insertPreparedStatement3);
    _contentValues.put("city_id", bean.cityId);
    _contentValues.put("person_id", bean.personId);

    // log section BEGIN
    if (_context.isLogEnabled()) {
      // log for insert -- BEGIN 
      StringBuffer _columnNameBuffer=new StringBuffer();
      StringBuffer _columnValueBuffer=new StringBuffer();
      String _columnSeparator="";
      for (String columnName:_contentValues.keys()) {
        _columnNameBuffer.append(_columnSeparator+columnName);
        _columnValueBuffer.append(_columnSeparator+":"+columnName);
        _columnSeparator=", ";
      }
      Logger.info("INSERT INTO city_person (%s) VALUES (%s)", _columnNameBuffer.toString(), _columnValueBuffer.toString());

      // log for content values -- BEGIN
      Triple<String, Object, KriptonContentValues.ParamType> _contentValue;
      for (int i = 0; i < _contentValues.size(); i++) {
        _contentValue = _contentValues.get(i);
        if (_contentValue.value1==null) {
          Logger.info("==> :%s = <null>", _contentValue.value0);
        } else {
          Logger.info("==> :%s = '%s' (%s)", _contentValue.value0, StringUtils.checkSize(_contentValue.value1), _contentValue.value1.getClass().getCanonicalName());
        }
      }
      // log for content values -- END
      // log for insert -- END 


      // log for where parameters -- BEGIN
      int _whereParamCounter=0;
      for (String _whereParamItem: _contentValues.whereArgs()) {
        Logger.info("==> param%s: '%s'",(_whereParamCounter++), StringUtils.checkSize(_whereParamItem));
      }
      // log for where parameters -- END
    }
    // log section END
    // insert operation
    long result = KriptonDatabaseWrapper.insert(insertPreparedStatement3, _contentValues);
    bean.id=result;

    return (int)result;
  }

  public static void clearCompiledStatements() {
    if (deleteByIdPreparedStatement0!=null) {
      deleteByIdPreparedStatement0.close();
      deleteByIdPreparedStatement0=null;
    }
    if (deleteByCityIdPreparedStatement1!=null) {
      deleteByCityIdPreparedStatement1.close();
      deleteByCityIdPreparedStatement1=null;
    }
    if (deleteByPersonIdPreparedStatement2!=null) {
      deleteByPersonIdPreparedStatement2.close();
      deleteByPersonIdPreparedStatement2=null;
    }
    if (insertPreparedStatement3!=null) {
      insertPreparedStatement3.close();
      insertPreparedStatement3=null;
    }
  }
}

The entity definition:

/**
 * <p>
 * Entity <code>CityPerson</code> is associated to table <code>city_person</code>
 * This class represents table associated to entity.
 * </p>
 *  @see CityPerson
 */
public class CityPersonTable implements SQLiteTable {
  /**
   * Costant represents typeName of table city_person
   */
  public static final String TABLE_NAME = "city_person";

  /**
   * <p>
   * DDL to create table city_person
   * </p>
   *
   * <pre>CREATE TABLE city_person (id INTEGER PRIMARY KEY AUTOINCREMENT, city_id INTEGER NOT NULL, person_id INTEGER NOT NULL, FOREIGN KEY(city_id) REFERENCES city(id) ON DELETE CASCADE, FOREIGN KEY(person_id) REFERENCES person(id) ON DELETE CASCADE); CREATE INDEX idx_city_person_city_id ON city_person(city_id); CREATE INDEX idx_city_person_person_id ON city_person(person_id); CREATE UNIQUE INDEX idx_city_person_0 on city_person (city_id, person_id);</pre>
   */
  public static final String CREATE_TABLE_SQL = "CREATE TABLE city_person (id INTEGER PRIMARY KEY AUTOINCREMENT, city_id INTEGER NOT NULL, person_id INTEGER NOT NULL, FOREIGN KEY(city_id) REFERENCES city(id) ON DELETE CASCADE, FOREIGN KEY(person_id) REFERENCES person(id) ON DELETE CASCADE); CREATE INDEX idx_city_person_city_id ON city_person(city_id); CREATE INDEX idx_city_person_person_id ON city_person(person_id); CREATE UNIQUE INDEX idx_city_person_0 on city_person (city_id, person_id);";

  /**
   * <p>
   * DDL to drop table city_person
   * </p>
   *
   * <pre> DROP INDEX IF EXISTS idx_city_person_city_id; DROP INDEX IF EXISTS idx_city_person_person_id; DROP INDEX IF EXISTS idx_city_person_1;DROP TABLE IF EXISTS city_person;</pre>
   */
  public static final String DROP_TABLE_SQL = " DROP INDEX IF EXISTS idx_city_person_city_id; DROP INDEX IF EXISTS idx_city_person_person_id; DROP INDEX IF EXISTS idx_city_person_1;DROP TABLE IF EXISTS city_person;";

  /**
   * Entity's property <code>id</code> is associated to table column <code>id</code>. This costant represents column name.
   *
   *  @see CityPerson#id
   */
  public static final String COLUMN_ID = "id";

  /**
   * Entity's property <code>cityId</code> is associated to table column <code>city_id</code>. This costant represents column name.
   *
   *  @see CityPerson#cityId
   */
  public static final String COLUMN_CITY_ID = "city_id";

  /**
   * Entity's property <code>personId</code> is associated to table column <code>person_id</code>. This costant represents column name.
   *
   *  @see CityPerson#personId
   */
  public static final String COLUMN_PERSON_ID = "person_id";

  /**
   * Columns array
   */
  private static final String[] COLUMNS = {COLUMN_ID, COLUMN_CITY_ID, COLUMN_PERSON_ID};

  /**
   * Columns array
   */
  @Override
  public String[] columns() {
    return COLUMNS;
  }

  /**
   * table name
   */
  @Override
  public String name() {
    return TABLE_NAME;
  }
}

As you can see, all tipical foreign key, primary key and constraints are created for us. Quite easy isn't it?