Stručný popis

SQL Procesor slouží k vykonávaní ANSI SQL dotazů bez nutnosti psát obslužný kód na úrovni JDBC nebo ORM. Důsledkem je úspora psaní (zejména obslužného) kódu a zkrácení vývojové fáze, čímž vzrůstá produktivita realizace. Současně je genericky ošetřeno množství konverzních a obslužních procesů, čímž se snižuje chybovost v kódu a vzrůstá spolehlivost aplikace.

Tyto možnosti nabízí v současné době množství ORM technologií. SQL Procesor se odlišuje v tom, že do centra vrací ANSI SQL. Dobře napsaný SQL dotaz nebo CRUD příkaz je lepší a jednodušší, než předimenzovaná ORM technologie, zejména v případě databází s letitou historií, které nejsou zcela normalizované nebo normalizovatelné. Tyto databáze jsou taky většinou nasazené v rozsáhlejších implementacích a zásadnější změna jejich struktury v podstatě není možná. Na druhou stranu optimalizace SQL dotazů je velice žádoucí. SQL Procesor tohle nabízí, většinou bez nutnosti měnit Java kód, a to dokonce i v produkci.

V neposlední řadě SQL Procesor nabízí přidanou hodnotu v podpoře stránkování, třídění, kontroly času vykonávání SQL dotazů (tedy zatížení produkční databáze), statistiky atd.

SQL Procesor může být provozován nad několika technologiemi:

  • nativně nad JDBC knihovnou

  • nad Hibernate ORM

  • nad Spring DAO

Cílem je co nejvíce vytěžit z uvedených technologií a přizpůsobit SQL Procesor cílové platformě, která je v provozu. Jelikož těžiště SQL Procesor-u je ve vykonávaní SQL dotazů, jeho vlastní architektura je databázově nezávislá.

Na vstupu do SQL Procesoru jsou

  • vyhledávací formuláře (nebo také dotazovací formuláře nebo vstupní třídy), což jsou POJO třídy. Typicky vstupní formulář je vyplněn uživatelem webové aplikace.

  • META SQL dotazy nebo příkazy, což jsou rozšířené ANSI SQL příkazy. Toto rozšíření je definováno pomocí ANTLR gramatiky. Všechny META SQL příkazy jsou umístěné v externím souboru a nejsou součástí Java kódu.

  • výstupní pravidla, což jsou předpisy pro mapování výsledků SQL dotazů na atributy Java tříd. Tyto předpisy jsou opět definovány pomocí ANTLR gramatiky. Můžou být součástí META SQL příkazů, nebo jsou samostatně umístěné v externím souboru a nejsou součástí Java kódu.

Na základě vstupních parametrů je vygenerován finální SQL příkaz (může být vždy jiný), který je následně vykonán. Tento princip je nazván “dotaz řízený daty” (Data Driven Queries). Dosazení vstupních parametrů je realizováno pomocí reflexe, bez nutnosti psát obslužný kód.

Na výstupu je

  • seznam výstupních tříd (nebo také transportních tříd, což jsou opět POJO třídy). Tyto třídy jsou konstruovány opět pomocí reflexe, bez nutnosti psát obslužný kód.

 

V následujícím si uvedeme velice jednoduchý příklad na přibližení použití této technologie. Více informací je pak možno obdržet na

kde jsou k dispozici návody, referenční příručka, příklady a Javadoc. SQL Procesor je OSS projekt, který je hostován na GitHub infrastruktuře.


Jednoduchý příklad

Chceme pořídit seznam dat o osobách, přičemž na vstupu může a nemusí být podmínka na jméno osoby. Máme databázovou tabulku PERSON s následující strukturou (HSQLDB)

CREATE TABLE PERSON

(

   ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL

, NAME VARCHAR(100) NOT NULL

);

Objektový model, která odpovídá této tabulce, je Java třída Person, která může sloužit taky jako vyhledávací formulář:

package org.sqlproc.sample.simple.model;

import java.util.List;

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;

  }

}

Chceme tedy obdržet seznam všech lidí v této tabulce. Výstupní třída je opět Java třída Person. META SQL dotaz se jménem ALL_PEOPLE a výstupní pravidlo se stejným jménem jsou uvedené v souboru dotazů queries.properties:

QRY_ALL_PEOPLE= \

  select p.ID id, p.NAME name \

  from PERSON p \

  {= where \

    {& id=:id} \

   {& UPPER(name)=:+name} \

  } \

  {#1 order by ID} \

  {#2 order by NAME}

OUT_ALL_PEOPLE=id name

 

Každý META SQL dotaz/příkaz je uveden jako QRY_xxx ,kde xxx je jméno. Každé explicitně definované výstupní pravidlo je uvedeno jako OUT_xxx, kde xxx je jméno. Kromě META SQL dotazů/příkazů a výstupních pravidel můžou být v tomto souboru uvedené volitelné konfigurační parametry, které ovlivňují fukcionalitu SQL Procesor-u. Kažý takový parametr je uveden jako SET_xxx.

Alternativou k explicitně uvedeným výstupním pravidlům jsou implicitní výstupní pravidla, které jsou vnořené do META SQL dotazu. Pro předchozí příklad to může být:

QRY_ALL_PEOPLE= \

  select p.ID @id, p.NAME @name \

  from PERSON p \

  {= where \

    {& id=:id} \

    {& UPPER(name)=:+name} \

  } \

  {#1 order by ID} \

  {#2 order by NAME}

Abychom vytvořili instanci SQL Procesor-u nad JDBC knihovnou k uvedenému dotazu, můžeme použít: 

JdbcEngineFactory factory = new JdbcEngineFactory();
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("ALL_PEOPLE");

V procesu vytvoření instance JdbcEngineFactory je načten soubor dotazů queries.properties. Všechny artefakty (META SQL dotazy/příkazy a výstupní pravidla) jsou před-kompilována pomocí odpovídajících ANTLR gramatik. Třída SqlQueryEngine představuje výkonnou třídu (stroj) SQL Procesor-u, jež obsahuje jednu dvojici uvedených artefaktů.

Dále potřebujeme databázovou relaci, která je realizována pomocí třídy SqlSession:

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

Obdržení seznamu osob je možno: 

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

První parametr metody query() je databázová relace. Druhý parametr je signatura třídy, jež říká SQL Procesor-u, jak konstruovat instance výstupní třídy. V produkci je vygenerován a proveden následující SQL dotaz: 

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

 

Třídu Person je možno současně použít jako vyhledávací formulář. Pro obdržení seznamu osob, jež mají jméno "Jan" a pro setřídění tohoto seznamu sestupně podle jména: 

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

První parametr metody query() je databázová relace. Druhý parametr je signatura třídy, jež říká SQL Procesor-u, jak konstruovat výstupní třídy. Třetí parametr je vyhledávací formulář. Čtvrtý parametr je předpis pro třídění. V produkci je vygenerován a proveden následující SQL dotaz: 

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

 

Pro obdržení seznamu osob, jež mají ve jménu textový fragment "an" a pro setřídění tohoto seznamu sestupně podle jména, použijeme v souboru dotazů následující META SQL dotaz:

SET_LIKE_STRING=like

SET_WILDCARD_CHARACTER=%

SET_SURROUND_QUERY_LIKE=true

SET_SURROUND_QUERY_MIN_LEN=2

 

QRY_LIKE_PEOPLE= \

  select p.ID @id, p.NAME @name \

  from PERSON p \

  {= where \

    {& id=:id} \

    {& UPPER(name) like :+name} \

  } \

  {#1 order by ID} \

  {#2 order by NAME}

 

Dále pak pro obdržení tohoto seznamu:

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

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

V produkci je vygenerován a proveden následující SQL dotaz: 

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


META SQL

META SQL je rozšířením ANSI SQL. Rozšířující elementy začínají levou složenou závorkou { a končí pravou složenou závorkou }. Vstupní hodnoty z vyhledávacích formulářů jsou označeny prefixem : nebo $. Implicitní výstupní pravidla jsou přiřazeny výstupním databázovým sloupcům nebo alias-ům a jsou označeny prefixem @. V předchozím META SQL dotazu QRY_ALL_PEOPLE:

  • select p.ID @id, p.NAME @name from PERSON p je standardní SQL fragment dotazu. Z tohoto fragmentu jsou odstraněny znaky @ . Ty slouží pro korelaci aliasů databázových sloupců a atributů výstupních tříd.

  • {= where je speciální označení pro WHERE fragment SQL dotazu. Je užit pro označení této části SQL dotazu. Současně aktivuje speciální zpracovnání vstupních atributů v tomto fragmentu. Před tento fragment je přidáno klíčové slovo WHERE a všechny potenciální prefixy AND/OR z následujícih podmínek jsou ostraněny.

  • {& id=:id} je podmíněný fragment dotazu typu AND. Text mezi {& a } se stane součástí vygenerovaného SQL dotazu pouze v případě, že všechny vstupní hodnoty uvedené mezi závorkami jsou neprázdné. V tomto případě je zde vstupní hodnota :id, takže pokud ve vyhledávacím formuláři je atribut id neprázdný, vygenerovaný SQL dotaz bude obsahovat fragment AND id=:id a hodnota id atributu bude použita v tomto dotazu. Pokud ve vyhledávacím formuláři je atribut id prázdný, vygenerovaný SQL dotaz nebude obsahovat tento fragment.

  • {& UPPER(name)=:+name} je podmíněný fragment dotazu typu AND. Text mezi {& a } se stane součástí vygenerovaného SQL dotazu pouze v případě, že všechny vstupní hodnoty uvedené mezi závorkami jsou neprázdné. V tomto případě je zde vstupní hodnota :+name, takže pokud ve vyhledávacím formuláři je atribut id neprázdný, vygenerovaný SQL dotaz bude obsahovat fragment AND UPPER(name)=:name a hodnota id atributu převedena na velká písmena bude použita v tomto dotazu. Znak + v :+name značí konverzi na velká písmena. Pokud ve vyhledávacím formuláři je atribut name prázdný, vygenerovaný SQL dotaz nebude obsahovat tento fragment.

  • {#1 order by ID} je předpis pro třídění s identifikátorem id = 1. V případě, že metoda query() má jako parametr SqlOrder.getAscOrder(1), vygenerovaný SQL dotaz bude obsahovat fragment order by ID ASC. V případě, že metoda query() má jako parametr SqlOrder.getDesOrder(1), vygenerovaný SQL dotaz bude obsahovat fragment order by ID DESC.

  • {#1 order by ID NAME} je předpis pro třídění s identifikátorem id = 2. V případě, že metoda query() má jako parametr SqlOrder.getAscOrder(2), vygenerovaný SQL dotaz bude obsahovat fragment order by NAME ASC. V případě, že metoda query() má jako parametr SqlOrder.getDesOrder(2), vygenerovaný SQL dotaz bude obsahovat fragment order by NAME DESC. Předpisy pro třídění je možno kombinovat, jako např. SqlOrder.getDescOrder(1).addAscOrder(2).

Výstupní pravidla

Výstupní pravidla jsou předpisy pro mapování výsledků SQL dotazů na atributy Java tříd, tedy předpisy, jak naplnit instance výstupních tříd. V podstatě to jsou seznamy databázových sloupců a/nebo aliasů, jež jsou použity jako výstupní hodnoty. Jmémo databázového sloupce nebo aliasu nemusí odpovídat názvu příslušného atributu výstupní třídy. Výstupní pravidlo v tomto případě koreluje tyto názvy. Dále pak ani typ databázového sloupce nebo aliasu nemusí odpovídat typu příslušného atributu výstupní třídy. Ve výstupním pravidlu může být v tomto případě uvedeno konverzní pravidlo (nebo je užito standardní konverzní pravidlo). V předchozím (explicitním) výstupním pravidlu OUT_ALL_PEOPLE:

  • id je název databázového sloupce a současně je to název atributu ve výstupní třídě

  • name je název databázového sloupce a současně je to název atributu ve výstupní třídě

V případě implicitního výstupního pravidla zde není uveden/použit OUT_ALL_PEOPLE, ale v META SQL dotazu QRY_ALL_PEOPLE všechny aliasy s prefixem @ mají stejnou funkci.

Konfigurační parametry

Volitelné konfigurační parametry ovlivňují fukcionalitu SQL Procesor-u. Příkladem jsou parametry, které slouží pro aktivaci speciálního vyhledávání podle textových fragmentů. Tato funkcionalita byla prezentována v předchozím META SQL dotazu QRY_LIKE_PEOPLE:

  • SET_SURROUND_QUERY_LIKE=true – tento parametr aktivuje uvedenou funkcionalitu

  • SET_LIKE_STRING=like – kažý SQL dotaz s klíčovým slovem like může sloužit pro uvedený typ vyhledávání

  • SET_SURROUND_QUERY_MIN_LEN=2 – minimální delká textového řetězce, který může být použit pro tento typ vyhledávání

  • SET_WILDCARD_CHARACTER=% - speciální znak, který může sloužit jako prefix/postfix pro textové fragmenty

Uvedené příklad je k dispozici v GIT repository https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc.