Input values

###Notice - The tutorial is updated for SQLP 2 and SQLEP 1.3 / Back to Tutorials

A dynamic or static input value can be accompanied with a list of modifiers enclosed in parenthesis. The schema of a dynamic input value (=identifier) with a list of modifiers is shown here:

The schema of a static input value (=constant) with a list of modifiers is shown here:

• EQUALS is a character =, LESS_THAN is a character <and MORE_THAN is a character > - all of them are used in stored procedures handling. For more information please see Stored Procedures Tutorial.
• PLUS is a character +, MINUS is a character - - all of them are used for uppercase/lowercase conversion of the input value
• IDENT or IDENT_DOT is Java identifier. There can be also a NUMBER, which can be used in stored procedures handling.
• LPAREN is a character ( - it is used to separate an input value definition from modifiers definition
• IDENT after the LPAREN is a modifier (for example a META type)
• NUMBER after the LPAREN is a value modifier
• COMMA is a character , - it is used to separate multiple modifiers in parenthesis
• RPAREN is a character ) - it is used to separate an input value definition from modifiers definition

Modifiers can be attached also to a partial input value. In this case it's a partial modifier. The example of standard modifier is a META type (type=date)

ANSI_BASIC_3(QRY)=
  select %p.ID @id, %p.NAME_FIRST @name.first, %p.BIRTHDATE @birthDate(type=date)
  from %%PERSON p
;

You can see, that the META type has to have a prefix type. The example of a partial modifier is a generic type (gtype=movie) used for example in inheritance

BASIC_2_LEFT_JOIN_2(QRY)=
  select l.ID @id(type=long,id), pm.ID @media.id(type=long,id), 
         m.TITLE @media.media(gtype=movie)title, m.ID @media.media.id(type=long,id)
  from LIBRARY l left join PHYSICALMEDIA pm on l.ID = pm.LIBRARY
  left join MEDIA_PHYSICALMEDIA mpm on mpm.PHYSICALMEDIA = pm.ID
  left join MEDIA m on m.ID = mpm.MEDIA
;

So as it can be seen, one type of the modifier is an information about the type. The type defines a mapping from a Java type to a JDBC datatype and is known as a META type. The transformation to a JDBC datatype is done in two steps

• in the first step a possible conversion of input value is done inside the SQL Processor engine
• in the second step there's invoked a standard mapping mechanism based on the stack devoted type and/or a Java type. The stack devoted type can be for example a JDBC type or a Hibernate type.

In most cases it's not necessary to define a META type. A Java type of an input value attribute (of a search form) is enough for this transformation. It's required only in special cases, like binary data. ###JDBC META types The SQL Processor has defined a list of already provided JDBC META types. These types are used in the case the SQL Processor works on top of the JDBC stack or the Spring DAO stack.

Every META type has the name(s) (=IDENT in the previous schema), the related Java type(s) and the JDBC type. These types are inside the package org.sqlproc.engine.jdbc.type and they have to implement the contracts SqlProviderType and JdbcSqlType. The repository of all JDBC META types is implemented in the class https://github.com/hudec/sql-processor/blob/master/sql-processor/src/main/java/org/sqlproc/engine/jdbc/type/JdbcTypeFactory.java, where you can see the actual list of these types.

These types can be explicitly declared in the META SQL statements. In the case the META types aren't defined explicitly in the META SQL statement, they are implicitly derived from the Java type of input values.

Here is a simple example with the explicitly defined types of input values:

QUERY(QRY)=
  select t.t_int n_int, t.t_int, t.t_long n_long, t.t_long, t.t_byte n_byte, t.t_byte,
  t.t_short n_short, t.t_short, t.t_float n_float, t.t_float, t.t_double n_double, t.t_double,
  t.t_char n_char, t.t_char, t.t_string,
  t.t_time, t.t_date, t.t_datetime, t.t_timestamp,
  t.t_timestamp t_timestamp_as_date, t.t_timestamp t_timestamp_as_time, t.t_timestamp t_timestamp_as_datetime,
  t.t_boolean n_boolean, t.t_boolean,
  t.t_big_integer, t.t_big_decimal,
  t.a_byte an_byte, t.a_byte at_byte, t.a_text,
  t.a_blob, t.a_clob
  from TYPES t
  {= where
   {& t_int = :t_int(type=int)}
   {& t_long = :t_long(type=long)}
   {& t_byte = :t_byte(type=byte)}
   {& t_short = :t_short(type=short)}
   {& t_float = :t_float(type=float)}
   {& t_double = :t_double(type=double)}
   {& t_char = :t_char(type=char)}
   {& t_string = :t_string(type=string)}
   {& t_boolean = :t_boolean(type=boolean)}
   {& t_date = :t_date(type=date)}
   {& t_time = :t_time(type=time)}
   {& t_datetime = :t_datetime(type=datetime)}
   {& t_timestamp = :t_timestamp(type=timestamp)}
   {& t_date = :t_timestamp_as_date(type=date)}
   {& t_time = :t_timestamp_as_time(type=time)}
   {& t_datetime = :t_timestamp_as_datetime(type=datetime)}
  }
;

###Hibernate META types The SQL Processor has defined a list of already provided Hibernate META types. These types are used in the case the SQL Processor works on top of the Hibernate stack.

Every META type has the name(s) (=IDENT in the previous schema), the related Java type(s) and the Hibernate type. These types are inside the package org.sqlproc.engine.hibernate.type and they have to implement the contract SqlProviderType. The repository of all Hibernate META types is implemented in the class https://github.com/hudec/sql-processor/blob/master/sql-processor-hibernate/src/main/java/org/sqlproc/engine/hibernate/type/HibernateTypeFactory.java, where you can see the actual list of these types.

These types can be explicitly declared in the META SQL statements. In the case the META types aren't defined explicitly in the META SQL statement, they are implicitly derived from the Java type of input values.

Moreover, there's a special Hibernate type with the prefix h_ in its IDENT (name). This type is implemented in the class HibernateType and is in fact the direct extension of the Hibernate type (in package org.hibernate.type). The full name consists of the prefix h_ and the upper case name of the required Hibernate type.

Here is another simple example with the explicitly defined Hibernate types of input values:

QUERY(QRY)=
 select t.t_int n_int, t.t_int, t.t_long n_long, t.t_long, t.t_byte n_byte, t.t_byte,
  t.t_short n_short, t.t_short, t.t_float n_float, t.t_float, t.t_double n_double, t.t_double,
  t.t_char n_char, t.t_char, t.t_string,
  t.t_time, t.t_date, t.t_datetime, t.t_timestamp,
  t.t_timestamp t_timestamp_as_date, t.t_timestamp t_timestamp_as_time, t.t_timestamp t_timestamp_as_datetime,
  t.t_boolean n_boolean, t.t_boolean,
  t.t_big_integer, t.t_big_decimal,
  t.a_byte an_byte, t.a_byte at_byte, t.a_text,
  t.a_blob, t.a_clob
  from TYPES t
  {= where
   {& t_int = :t_int^h_INTEGER)}
   {& t_long = :t_long(type=h_LONG)}
   {& t_byte = :t_byte(type=h_BYTE)}
   {& a_byte = :an_byte(type=h_BINARY)}
   {& t_short = :t_short(type=h_SHORT)}
   {& t_float = :t_float(type=h_FLOAT)}
   {& t_double = :t_double(type=h_DOUBLE)}
   {& t_char = :t_char(type=h_CHARACTER)}
   {& t_string = :t_string(type=h_STRING)}
   {& t_boolean = :t_boolean(type=h_BOOLEAN)}
   {& t_date = :t_date(type=h_DATE)}
   {& t_time = :t_time(type=h_TIME)}
   {& t_datetime = :t_datetime(type=datetime)}
   {& t_timestamp = :t_timestamp(type=h_TIMESTAMP)}
   {& t_date = :t_timestamp_as_date(type=h_DATE)}
   {& t_time = :t_timestamp_as_time(type=h_TIME)}
   {& t_datetime = :t_timestamp_as_datetime(type=datetime)}
  }
;

###More modifiers More modifiers can be specified for the input values. The treatment of these modifiers depends on the context in which they are used.

In this version of the SQL Processor mainly the next modifiers are supported (for the complete list please see [[ SQLP 2.0 and SQLEP 1.3 Tutorial]])

• any – to detect the empty value and true value
• null – to detect the empty value and true value
• notnull – to detect the empty value and true value
• any other String or number can be taken as a value modifier (literal)

The standard usage of the these modifiers is in isNotEmpty() method to detect the “emptiness” of an input value:

• :ident(any) or $constant(any) is non-empty for any input value. It's used to bind an input value to the final SQL query prepared statement without any restriction.
• :ident(null) or $constant(null) is non-empty for null input value
• :ident(notnull) or $constant(notnull) is used to control the input values. In the case the input values is null, the IllegalArgumentExcpetion is thrown.

Let's have a query

QUERY(QRY)=
  select p.ID @id, p.NAME_FIRST @name.first, p.NAME_LAST @name.last
  from PERSON p
  {= where p.id = :id(any)
  {& p.NAME_FIRST = :name.first(null)}}
;

• in the case :name.first input value isn't null, the final SQL query will become select p.ID id, p.NAME_FIRST first, p.NAME_LAST last from PERSON p where p.id = ?
• in the case :name.first input value is null, the final SQL query will become select p.ID id, p.NAME_FIRST first, p.NAME_LAST last from PERSON p where p.id = ? AND p.NAME_FIRST = ? and null value is bounded to the condition p.NAME_FIRST = ?

Another standard usage of the these modifiers is in isTrue() method to detect a boolean value in conditional query of type IF:

• :ident(null) or $constant(null) is true for null input value
• :ident(someValue) or $constant(someValue) is true for the case the input value is someValue

Let's have a query

QUERY(QRY)=
  select p.ID @id, p.NAME_FIRST @name.first, p.NAME_LAST @name.last
  from PERSON p
  {= where
   {? :id(null) | AND p.ID is null | AND p.ID = :id}
   {? :version(1) | AND (p.VERSION = 1 OR p.VERSION is null)}
  }
;

• in the case the input value :id is null, the condition AND p.ID is null will become a part of the final WHERE
• in the case the input value :id isn't null, the condition AND p.ID = ? will become a part of the final WHERE
• in the case the input value :version is 1, the condition AND (p.VERSION = 1 OR p.VERSION is null) will become a part of the final WHERE

Mainly the modifier null can help with databases, for which null value can't be bounded to the final SQL query prepared statement.

###Custom types Sometimes the internal META types can't handle all types of possible transformations between the input/output values and database column types. For this case it's possible to register custom META types.

For the explanation and the usage example please see the tutorial Custom Types Tutorial.