Simple Tutorial

The tutorial is updated for SQLP 3.1, SQLMEP/SQLMOP 2.6

The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.

Table of Contents

Introduction
A simple usage
META SQL
Mapping rules
Optional features

Introduction

The SQL Processor (SQLP) is an engine producing the ANSI SQL statements and providing their execution without the necessity to write any Java plumbing code related to the ORM or JDBC API. The result is less code, reduced time for development and increased productivity.

At the same time a lot of conversion and service related processes are treated generically behind the surface. Again, the result is less error rate and a more reliable application. The similar features are offered by a lot of ORM technologies. The uniqueness of the SQL Processor is in fact, that ANSI SQL is the central focus. A well written SQL query or statement is still better than any over-designed Java code, mainly for the enterprise databases with a high number of transactions. A lot of databases are still not normalized and SQL is certainly an advantage.

Another SQL Processor feature is the pagination support, the output sorting capability and the execution time control. All these features are devoted to decreasing the database load.

Still the SQL Processor can be built on top of Hibernate, one of the best ORM technologies. It can be treated as a supplement to this ORM. It also gives the SQL Processor a couple of advantages, like a second level cache or better type conversion.

In fact there are several stacks, on top of which the SQL Processor can run:

• the native JDBC stack - it's the integral part of the SQL Processor core library
• the Hibernate ORM - the best choice for the JPA based applications
• the Spring DAO - the best choice for the Spring based applications

All the SQL statements are defined outside of the Java code, so they can be optimized without the need to rewrite an application. This can be done also in production, without the necessity to interrupt the application.

The input to the SQL Processor is

• a search form (or a question form or the input values), which is just a POJO. Typically this form is seeded by an end user in a presentation layer of a web application.
• a META SQL query or statement, which is an enhanced ANSI SQL statement. This enhancement is defined in ANTLR based grammar. All the META SQL statements are defined in the meta statements file.
• an output mapping rule, which is a mapping prescription from an SQL query result to the Java output classes values. This prescription syntax is again defined in the ANTLR based grammar. All the mapping rules are also defined in the meta statements file.

Based on the inputs the final SQL query/statement is generated. The parameters are bound and finally this query/statement is executed. This process is known as a Data Driven Query. The binding of the input values is done using the Reflection API, without any Java plumbing code. The output of the SQL Processor is

• a list of result class instances (or data transfer objects or the output values). Each result class instance is created using the Reflection API, again without any Java plumbing code.

A simple usage

Here is a simple example – we have a database table PERSON with the next (HSQLDB) layout

CREATE TABLE PERSON
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, NAME VARCHAR(100) NOT NULL
);
ALTER TABLE PERSON ADD CONSTRAINT PK_PERSON
	PRIMARY KEY (ID)
;

The related model is the Java class Person, which can be also used as a search form:

package org.sqlproc.sample.simple.model;

public class Person {

    private Long id;
    private String name;

    public Person() {
    }

    public Person(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + "]";
    }
}

We'd like to have a list of all people in this table. The result class is again the Java class Person. The META SQL query with the name ALL_PEOPLE and the embedded mapping rules is defined in the meta statements file statements.meta:

ALL_PEOPLE(QRY)=
  select %ID @id, NAME @name
  from PERSON
  {= where
   {& ID = :id}
   {& UPPER(NAME) = :+name}
  }
  {#ORD1 order by ID}
  {#ORD2 order by NAME}
;

Every META SQL query definition begins with it's name (ALL_PEOPLE) followed by the left and right parenthesis. Next is the equals sign and the META statement definition itself. Inside the parenthesis there's a type (QRY) of the SQL Processor artifact. In this case it's a META SQL query. At the end of the definition there's a semicolon.

There's an alternative with the META SQL query without embedded mapping rules (@):

ALL_PEOPLE(QRY)=
  select ID id, NAME name
  from PERSON
  {= where
   {& id=:id}
   {& UPPER(name)=:+name}
  }
  {#ORD1 order by ID}
  {#ORD2 order by NAME}
;

and the explicit mapping rule

ALL_PEOPLE(OUT)=
  id$id name$name
;

Every explicitly defined mapping rule begins with it's name (ALL_PEOPLE) followed by the left and right parenthesis. Next is the equals sign and the mapping rule definition itself. Inside the parenthesis there's a type (OUT) of the SQL Processor artifact. In this case it's an explicitly defined mapping rule. At the end of the definition there's a semicolon.

Except the META SQL queries/statements and explicitly defined mapping rules there are also the optional features, which are used to control the SQL Processor behaviour.

To create an instance of the SQL Processor engine (in the case it runs on top of the JDBC stack):

JdbcEngineFactory sqlFactory = new JdbcEngineFactory();
sqlFactory.setMetaFilesNames("statements.meta");
sqlFactory.setFilter(SqlFeature.HSQLDB); // the optional database type to improve the SQLP behaviour
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("ALL_PEOPLE");

The JdbcEngineFactory instance is settled with the name of the meta statements file (statements.meta). Before the first SqlQueryEngine instantiation the meta statements file is read and all the META SQL statements and mapping rules in this file are pre-compiled. The SqlQueryEngine is the SQL Processor engine itself. It contains the pre-compiled META SQL query, the mapping rule, the optional features and the optional ordering directives.

Next we need an SqlSession instance. For the case of the JDBC stack it's just a wrapper around a database connection:

Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:sqlproc", "sa", "");
SqlSessionFactory sessionFactory = new JdbcSessionFactory(connection);
SqlSession session = sessionFactory.getSqlSession();

To obtain a list of all people:

List<Person> list = sqlEngine.query(session, Person.class);

The first parameter into the query() method is an SQL Processor session. The second parameter is a result class, so the SQL Processor knows, how to construct the instances of the output values. In the runtime the next ANSI SQL query is generated and executed:

select p.ID id, p.NAME name from PERSON p 

The class Person can be used as a search form at the same time. To obtain a list of all people, which have the name Jan and have this list sorted by the name in descending order:

Person person = new Person();
person.setName("Jan");
List<Person> list = sqlEngine.query(session, Person.class, person, SqlOrder.getDescOrder("ORD2"));

The first parameter into the query() method is an SQL Processor session. The second parameter is a result class, so the SQL Processor knows, how to construct the instances of the output values. The third parameter is an instance of a search form, which contains the input values. The last parameter is an ordering directive. In the runtime the next ANSI SQL query is generated and executed:

select p.ID id, p.NAME name from PERSON p where UPPER(name)=? order by NAME DESC

To obtain a list of people, which have in their name a text fragment an and have this list sorted by the name in ascending order, we have the next META SQL query in the meta statements file statements.meta:

SURROUND_QUERY_LIKE_FULL(BOPT)=true;
LIKE_STRING(OPT)=like; // this is not required, it's a default feature
WILDCARD_CHARACTER(OPT)=%; // this is not required, it's a default feature
SURROUND_QUERY_MIN_LEN(IOPT)=2; // this is not required, it's a default feature

LIKE_PEOPLE(QRY)=
  select p.ID @id, p.NAME @name
  from PERSON p
  {= where
   {& id=:id}
   {& UPPER(name) like :+name}
  }
  {#ORD1 order by ID}
  {#ORD2 order by NAME}
;

To obtain this list:

SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("LIKE_PEOPLE");

Person person = new Person();
person.setName("an");
List<Person> list = sqlEngine.query(session, Person.class, person, SqlOrder.getAscOrder("ORD2"));

In the runtime the next ANSI SQL query is generated and executed:

select p.ID id, p.NAME name from PERSON p where UPPER(name) like ? order by NAME ASC

META SQL

META SQL is an extension of ANSI SQL. Extended query elements begin with a curly bracket {. The input values from a search form begin with a character : or $. The mapping rules for the output values (implicitly defined) begin with a character @. In the previously used META SQL query ALL_PEOPLE(QRY):

• select p.ID @id, p.NAME @name from PERSON p is a standard SQL fragment. From this fragment the character @ is removed. It it used to correlate the aliases of query results with the attributes in the result class.
• {= where is a marker for the WHERE fragment of an SQL query. It's used to delimit this fragment and switch a special handling of the input values in it. At the same time the keyword WHERE will become a prefix of this fragment, and potential leading AND/OR keywords will be stripped.
• {& id=:id} is a conditional SQL fragment of type AND. Everything between {& and } will become a part of the final SQL query only in the case that all the input values in this conditional fragment are non-empty. In this case the only input value here is :id, so if a search form attribute id is not null, the final SQL query will contain the next fragment AND id=:id and value of id attribute will be seeded into a prepared statement. In the case a search form attribute id is null, the final SQL query won't contain anything from this conditional SQL query fragment.
• {& UPPER(name)=:+name} is a conditional SQL fragment of type AND. Everything between {& and } will become a part of the final SQL query only in the case that all the input values in this conditional fragment are non-empty. In this case the only input value here is :+name, so if a search form attribute name is not empty, the final SQL query will contain the next fragment AND UPPER(name)=:name and upper-case value of name attribute will be seeded into a prepared statement. The character + in :+name means upper-case conversion. In the case a search form attribute name is null or empty string, the final SQL query won't contain anything from this conditional SQL query fragment.
• {#ORD1 order by ID} is an ordering SQL fragment with the ordering identifier ORD1. In the case the method query() has as a parameter ordering directive SqlOrder.getAscOrder("ORD1"), the final SQL query will contain a fragment order by ID ASC. In the case the method query() has as a parameter ordering directive SqlOrder.getDescOrder("ORD1"), the final SQL query will contain a fragment order by ID DESC.
• {#ORD2 order by NAME} is an ordering SQL fragment with the ordering identifier ORD2. In the case the method query() has as a parameter ordering directive SqlOrder.getDescOrder("ORD2"), the final SQL query will contain a fragment order by NAME DESC. In the case the method query() has as a parameter ordering directive SqlOrder.getDescOrder("ORD2").addAscOrder("ORD1"), the final SQL query will contain a fragment order by NAME DESC, ID ASC.

Mapping rules

The mapping rule is a prescription, how to fill the instances of the result class with the output values from an SQL query execution. In fact it's a list of database columns or aliases, values of which are seeded into the instances of the result class. The names of the result class attributes and the database columns/aliases do not have to be the same. The mapping rule can describe a mapping between them. The types of the result class attributes and the database columns/aliases do not have to be the same. The mapping rule can describe also a translation between them. In the previously used mapping rule ALL_PEOPLE(OUT):

• id$id is the name of a database column and at the same time the name of a result class attribute
• name$name is the name of a database column and at the same time the name of a result class attribute

In the case of embedded mapping rule, there's no ALL_PEOPLE(OUT), but in the META SQL query ALL_PEOPLE(QRY) all aliases with prefix @ have the same role.

Optional features

The optional features can alter the behaviour of the SQL Processor Engine. They can be defined in the meta statements file. For example the SQL Processor supports the special searching feature based on text fragments. This feature is used in the previously presented META SQL query LIKE_PEOPLE(QRY):

• SURROUND_QUERY_LIKE_FULL(BOPT)=true – this activates this feature, so every new instance of SqlQueryEngine supports this behaviour. The SQL Processor artifact type BOPT means this is an optional feature with a boolean value.
• LIKE_STRING(OPT)=like – in every SQL query with the keyword like the input values are treated in a special way. The SQL Processor artifact type OPT means this is an optional feature with a String value.
• SURROUND_QUERY_MIN_LEN(IOPT)=2 – the minimal length of the input values to participate in this special treatment. The SQL Processor artifact type IOPT means this is an optional feature with an integer value.
• WILDCARD_CHARACTER(OPT)=% - the SQL query wild-card character. This character is added to the related input values as a prefix and/or suffix for all queries with the like command. The SQL Processor artifact type OPT means this is an optional feature with a String value.

---

The sample Simple is available in GIT https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc.