Enumerations - hudec/sql-processor GitHub Wiki

Table of Contents

Enumeration based input values Conditional query IF Enumeration based output values

Enumeration based input values

In the case an input value is enumeration based, the relation between a database value and an input value needn't be straightforward. For the usage of such input values two special modifiers (META enumeration types) have been introduced

• estring for the case that a database column is of type VARCHAR (or a similar one)
• eint for the case that a database column is of type NUMERIC (or a similar one)

Let's look at a query

QUERY(QRY)=
  select p.ID, p.SEX
  from PERSON p
  {= where
   {& p.SEX = :sex(type=estring)}
  }
  {#1 order by id}
;

The attribute :sex in a search form is of enumeration type Gender.

In the case that there are values FEMALE and MALE in the database column SEX, the following definition of the enumeration class Gender is required

public enum Gender {
    FEMALE,
    MALE;
}

In the case where there are values F and M in the database column SEX, the SQL Processor Engine must know, how to relate an instance of enumeration Gender to these values. For these purposes the following methods of an enumeration class are being probed (in this order)

• getCode
• getValue
• getName
• name

So in this example the following definition of the enumeration class Gender is required

public enum Gender implements Serializable {
    FEMALE("F"),
    MALE("M");
    private static Map<String,Gender> identifierMap =
        new HashMap<String,Gender>();

    static {
        for (Gender value : Gender.values()) {
            identifierMap.put(value.getValue(), value);
        }
    }

    private String value;

    private Gender(String value) {
        this.value = value;
    }

    public static Gender fromValue(String value) {
        Gender result = identifierMap.get(value);
        if (result == null) {
            throw new IllegalArgumentException("No Gender for value: " + value);
        }
        return result;
    }

    public String getValue() {
        return value;
    }
}

In this class the method getValue is used to bind an input value to the final SQL query prepared statement.

Input values can also be collections of enumerations. In this case the usage is

QUERY(QRY)=
  select p.ID, p.SSN_NUMBER ssn, p.SSN_COUNTRY country, p.SEX
  from PERSON p
  {= where
   {& p.SEX in $sexs(type=estring)}
   {& p.COUNTRY in $ssn.countries(type=estring)}
   {& p.CLOTHES_SIZE in (:clothesSizes(type=eint))}
  }
  {#1 order by id}
;

The explicit declaration of estring and eint isn't required. The SQL Processor can derive these META enumerations types (modifiers) based on the type of input value related attribute.

Conditional query IF

An enumeration based input value in a boolean expression can be evaluated in the following way

• if there's a value modifier alongside the META enumeration type, the value of an input value is compared to this value modifier

Let's look at a query

QUERY(QRY)=
  select p.ID, p.SSN_NUMBER ssn, p.SSN_COUNTRY country, p.SEX, p.CLOTHES_SIZE clothesSize
  from PERSON p
  {= where
   {? :sex(type=estring,MALE) | AND p.SEX = 'M'}
   {? $ssn.country(type=estring,US) | AND p.SSN_COUNTRY = 'US'}
   {? :clothesSize(type=eint,MIDDLE) | AND p.CLOTHES_SIZE = 1}
  }
  {#1 order by id}
;

• in the case the input value :sex is an enumeration instance MALE , the condition AND p.SEX = ”M” will become a part of the final WHERE
• in the case the input value $ssn.country is an enumeration instance US , the condition AND p.SSN_COUNTRY = ”US” will become a part of the final WHERE
• in the case the input value $clothesSize is an enumeration instance MIDDLE , the condition AND p.CLOTHES_SIZE = 1 will become a part of the final WHERE

Enumeration based output values

In the case an output value is enumeration based, the relation between a database value and an output value needn't be straightforward. For the usage of such an output value two special modifiers (META enumeration types) have been introduced

• ENUM_STRING for the case a database column is of type VARCHAR (or a similar one)
• ENUM_INT for the case a database column is of type NUMERIC (or a similar one)

Let's look at a query

QUERY(QRY)=
  select p.ID, p.SEX
  from PERSON p
  {#1 order by id}
;
QUERY(OUT)=
  id$Long$id sex$estring$sex
;

The attribute :sex in the result class is of enumeration type Gender:

In the case there are values FEMALE and MALE in the database column SEX, the following definition of the enumeration class Gender is required

public enum Gender {
    FEMALE,
    MALE;
}

In the case there are values F and M in the database column SEX, the SQL Processor Engine must know, how to relate an instance of enumeration Gender to these values. For these purposes the following static methods of an enumeration class are being probed (in this order)

• fromCode
• fromValue
• valueOf

So in this example the following definition of the enumeration class Gender is required

public enum Gender implements Serializable {
    FEMALE("F"),
    MALE("M");
    private static Map<String,Gender> identifierMap =
        new HashMap<String,Gender>();

    static {
        for (Gender value : Gender.values()) {
            identifierMap.put(value.getValue(), value);
        }
    }

    private String value;

    private Gender(String value) {
        this.value = value;
    }

    public static Gender fromValue(String value) {
        Gender result = identifierMap.get(value);
        if (result == null) {
            throw new IllegalArgumentException("No Gender for value: " + value);
        }
        return result;
    }

    public String getValue() {
        return value;
    }
}

In this class the method fromValue is used to relate database column values to output values.

The tutorial is updated for SQLP 2.2