Skip to content

Input values

Jump to bottom Edit New page
Vladimír Hudec edited this page Apr 8, 2017 · 57 revisions

Table of Contents

Input values (attributes)
JDBC META types
Hibernate META types
Input modifiers
Custom types
Collections
Null values handling

Input values (attributes)

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 a 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

The example of a standard modifier is a META type (type=int)

QUERY(QRY)=
  select t.t_int n_int
  from TYPES t
  {= where
   {& t_int = :t_int(type=int)}
  }
;

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(type=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)}
  }
;

Input 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

  • any – to detect the empty value and true value
  • null – to detect the empty value and true value
  • anyset - to detect the empty value and true value for containers
  • notnull – to detect the empty value and true value
  • type is the type of the attribute in a search form.
  • any other String or number can be taken as a value modifier (literal)

Any modifier can have the prefix !. It's meaning is the negation of the original modifier.

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.

There's also a modifier, which can be used only with negation prefix - !empty. The typical usage is the next one

DELETE_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  delete from %%CONTACT
  {= where
    {& %ID = :id(!empty) }
    {& %VERSION = :version(!empty) }
  }
;

This prevents the deletion of all records in database in the case the input attribute id is empty (null, or the String with length = 0).

Another set of input modifiers are

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.

Collections

The new modifier anyset (in the new SQLP also any) is used with the collections input values. Let's say we have the next META SQL statement for database rows deletion

DELETE_CONTACT(CRUD)=
  delete from CONTACT
  {= where
    {& %ID in :ids }
  }
;

The standard SQLP behaviour is

  • in the case the attribute ids (of any collection type) is null, the condition won't become a part of the final SQL statement. As a consequence, all rows are in this case deleted.
  • in the case the attribute ids (of any collection type) is empty, the condition won't become a part of the final SQL statement. As a consequence, all rows are in this case deleted.

To prevent this accidental deletion, the next modifiers can be used

DELETE_CONTACT(CRUD)=
  delete from CONTACT
  {= where
    {& %ID in :ids(anyset,notnull) }
  }
;

The standard SQLP behaviour in this case is

  • in the case the attribute ids (of any collection type) is null, the Exception is raised due to notnull modifier usage
  • in the case the attribute ids (of any collection type) is empty, the condition ID in (null) will become a part of the final SQL statement. As a consequence, no rows are in this case deleted.

The only exception is DB2, for which (null) isn't a valid SQL condition.

Extended null values handling in the update statements

The standard behaviour of the next META SQL statement

UPDATE_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  update %%PERSON
  {= set
    { ,%FIRST_NAME = :firstName }
  }
  {= where
    {& %ID = :id(notnull) }
  }
;

is to update the column FIRST_NAME regardless of the firstName attribute value. So also in the case the value of the attribute is null, the DB column is updated with NULL value.

Much more sophisticated way is to control, when the null value can be updated. There are several ways, how to reach this functionality. The most advanced way is described in the Tutorial SQLMOP POJO generation II. The META SQL statement is in this case

UPDATE_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  update %%PERSON
  {= set
    { ,%FIRST_NAME = :firstName(call=isDef) }
  }
  {= where
    {& %ID = :id(notnull) }
  }
;

and the detection of the empty value of the attribute firstName is delegated to the method idDef of the class Person.

The next way is to specify the optional feature EMPTY_USE_METHOD_IS_NULL for the META SQL statement

UPDATE_PERSON(CRUD,BOPT=EMPTY_USE_METHOD_IS_NULL=true,final=,inx=Person,outx=Person,tab=person)=
  update %%PERSON
  {= set
    { ,%FIRST_NAME = :firstName }
  }
  {= where
    {& %ID = :id(notnull) }
  }
;

which has the same effect, as the supplement value isDef in the statement.

The last possibility is to specify, that the all null values are treated as empty, for example in the global scope

EMPTY_FOR_NULL(BOPT)=true;

The tutorial is updated for SQLP 2.2

Add a custom footer

Table of Contents

Clone this wiki locally