Indices

To gain the best performance during access to a data table, it is usually to create some index to speed up the queries. An index is a collection of columns. With Kripton it is possible to define a single column index (unique or not) on a single field directly with @BindColumn annotation. Composed indices must be defined at class level, using @BindSqlType and @BindIndex annotations. The indices definition must be done using field names. Kripton will convert field name in the associated column name.

Consider the following bean definition:

@BindType
@BindSqlType(indexes = { 
  @BindIndex({"birthCity", "birthDay desc"}), 
  @BindIndex({"surname"}), 
  @BindIndex(value={"name","surname", "date desc"}, unique=true )})
public class Person {
  public long id;
  
  @BindColumn(columnType=ColumnType.UNIQUE)
  public String aliasName;
 
  public String date;
     
  @BindColumn(columnType=ColumnType.INDEXED)
  public String name;
  
  @BindColumn(columnType=ColumnType.INDEXED)
  public String surname;
  
  public String birthCity;
  
  public Date birthDay;
}

In Person definition were used all mode to define an index with Kripton:

• on property level with @BindColumn#columnType attribute set to ColumnType.INDEXED for simple index or columnType=ColumnType.UNIQUE for an alternative key.
• on the class level with @BindSqlType#indexes that allows defining both simple or unique indices. This is the way to define indices that work on multiple fields. You can also use this mode to define single field indices. Each column name or expression can be followed by one of the "ASC" or "DESC" keywords to indicate sort order.

The Person definition will generate the following table definition:

CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, alias_name TEXT UNIQUE, date TEXT, name TEXT, surname TEXT, birth_city TEXT, birth_day TEXT);
CREATE INDEX idx_person_name ON person(name);
CREATE INDEX idx_person_surname ON person(surname);
CREATE UNIQUE INDEX idx_person_0 on person (name, surname, date desc); 
CREATE INDEX idx_person_0 on person (birth_city, birth_day desc); 
CREATE INDEX idx_person_1 on person (surname);

Note that for the id field, the primary key of the table no definition was needed. The primary key will be generated automatically if id field is present is table definition.