Output values - hudec/sql-processor GitHub Wiki

Output values (attributes) JDBC META types Hibernate META types Output modifiers Custom types

Output values (attributes)

An output value can be accompanied with a list of modifiers enclosed in parenthesis. The schema of an output value in implicit mapping rule (=column) with a list of modifiers is shown here:

column3.png column4.png

The schema of an output value in expliicit mapping rule (=mappingItem) with a list of modifiers is shown here:

mappingItem3.png mappingItem4.png

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 output 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 output value definition from modifiers definition

Modifiers can be attached also to a partial output 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 JDBC datatype to a Java type (known as a META type) and is described in a composed type conversion prescription. The transformation from an JDBC datatype is done in two steps

in the first 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 the optional second step a possible conversion of an output value is done inside the SQL Processor engine

In most cases it's not necessary to define a META type. A Java type of an output value attribute (of a result class) is enough for this transformation. It's required only in special cases, like binary data, enumeration based output values and so on.

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 output values.

The example with a mapping rule based on the explicitly defined META types

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
  from TYPES t
  where id=1
;
QUERY(OUT)=
  n_int$n_int(type=int) t_int$t_int(type=int)
  n_long$n_long(type=long) t_long$t_long(type=long)
  n_byte$n_byte(type=byte) t_byte$t_byte(type=byte)
  n_short$n_short(type=short) t_short$t_short(type=short)
  n_float$n_float(type=float) t_float$t_float(type=float)
  n_double$n_double(type=double) t_double$t_double(type=double)
  n_char$n_char(type=char) t_char$t_char(type=char)
  t_string$t_string(type=string)
  t_time$t_time(type=time) t_date$t_date(type=date) t_datetime$t_datetime(type=datetime) t_timestamp$t_timestamp(type=timestamp)
  n_boolean$n_boolean(type=boolean) t_boolean$t_boolean(type=boolean)
  t_big_integer$t_big_integer(type=bigint) t_big_decimal$t_big_decimal(type=bigdec)
  an_byte$an_byte(type=bytes) at_byte$at_byte(type=bytes)
;

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.

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.

The example with a mapping rule based mainly on explicitly defined Hibernate types

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 id=1
;
QUERY(OUT)=
  n_int$n_int(type=h_INTEGER) t_int$t_int(type=h_INTEGER)
  n_long$n_long(type=h_LONG) t_long$t_long(type=h_LONG)
  n_byte$n_byte(type=h_BYTE) t_byte$t_byte(type=h_BYTE)
  n_short$n_short(type=h_SHORT) t_short$t_short(type=h_SHORT)
  n_float$n_float(type=h_FLOAT) t_float$t_float(type=h_FLOAT)
  n_double$n_double(type=h_DOUBLE) t_double$t_double(type=h_DOUBLE)
  n_char$n_char(type=h_CHARACTER) t_char$t_char(type=h_CHARACTER)
  t_string$t_string(type=h_STRING)
  t_time$t_time(type=h_TIME) t_date$t_date(type=h_DATE) t_datetime$t_datetime(type=h_TIMESTAMP) t_timestamp$t_timestamp(type=h_TIMESTAMP)
  n_boolean$n_boolean(type=h_BOOLEAN) t_boolean$t_boolean(type=h_BOOLEAN)
  t_big_integer$t_big_integer(type=h_BIG_INTEGER) t_big_decimal$t_big_decimal(type=h_BIG_DECIMAL)
  an_byte$an_byte(type=h_BINARY) at_byte$at_byte(type=h_WRAPPER_BINARY) a_text$a_text(type=h_TEXT)
  a_blob$a_blob(type=h_BLOB) a_clob$a_clob(type=h_CLOB)
;

For more detailed definitions of the related db table structure and the related result class definition for this example please see Junit tests for the SQL Processor.

Output modifiers

More modifiers can be specified for the output 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

id – to identify the primary key column. For the usage please see the tutorial Associations Tutorial or Inheritance Tutorial.
type is the type of the attribute in a result class.
gtype is the generic type of the embedded attribute in a result class. It's used mainly in the case the embedded attribute is a collection of abstract type. For the usage please see the tutorial Inheritance Tutorial.
dtype is the dynamic type of the embedded attribute in a result class. It's used mainly in the case the embedded attribute is of collection type. For the usage please see the tutorial Associations Tutorial.
discr is a discriminator column/attribute. It's used mainly in the inheritance handling. For the usage please see the tutorial More inheritance tutorial.

Another set of input-output modifiers are

seq - to identify the database sequence used in the INSERT statement. For the usage please see the tutorial CRUD Tutorial.
idsel - to identify the database auto-generated identity used in the INSERT statement. For the usage please see the tutorial CRUD Tutorial.

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.