SQLP 1.9.x Changes Tutorial - hudec/sql-processor GitHub Wiki

###The tutorial is updated for SQLP 2 and SQLEP 1.3

###Introduction The new features of the SQL Processor (SQLP) version 1.9.0 are

optional features within the statement scope
extended search based on text fragments
compound parameters in SqlControl
new SQL session factory SqlSessionFactory
extended engine factory SqlEngineFactory
extended plugins IsEmptyPlugin and IsTruePlugin
extended null values handling in the update statements
new DDL loader DDLLoader

The new features of the SQL Processor (SQLP) version 1.9.1 is

simple batches support

###Statement optional features Optional features are described in the Tutorial Optional features. Up to the previous release of the SQLP, there was only possibility to specify these values within the global scope. Now it's possible to specify them in the statement (instance of the SqlEngine) scope. There are two ways, how to utilize this feature

#####using the API, for example

SqlQueryEngine sqlEnginePerson = sqlEngineFactory.getCheckedQueryEngine("SELECT_PERSON");
sqlEnginePerson.setFeature("SURROUND_QUERY_LIKE_PARTIAL", Boolean.TRUE);

#####in the header of the META SQL statement, for example

SELECT_PERSON(QRY,BOPT=SURROUND_QUERY_LIKE_PARTIAL=true,final=,inx=Person,outx=Person,...
  select %p.ID @id(id), %p.FIRST_NAME @firstName, %p.LAST_NAME @lastName, ...

###Extended search based on text fragments A search based on text fragments is described in the Tutorial Optional features. Up to the previous release of the SQLP, it requires the following optional features defined in statements.qry

SURROUND_QUERY_LIKE(BOPT)=true;

Now it's possible to distinguish between search based on the index (SELECT ... WHERE :name LIKE 'PER%')

SURROUND_QUERY_LIKE_PARTIAL(BOPT)=true;

and full scan of the DB table (SELECT ... WHERE :name LIKE '%PER%')

SURROUND_QUERY_LIKE_FULL(BOPT)=true;

Remember, the optional features can be defined in the statement scope.

###Compound parameters The basic API is described in Using the API. All principal methods have an alternative with compound parameters in SqlControl (https://github.com/hudec/sql-processor/blob/master/sql-processor/src/main/java/org/sqlproc/engine/SqlControl.java). This interface includes the following getters

staticInputValues
maxTimeout
maxResults
firstResult
order
moreResultClasses

This is used primarily in generated DAO classes (SQLEP 1.2.x DAO modelling). The implementation class for the interface SqlControl is SqlStandardControl.

###Session factory Alongside the SQL Engine factory (described in Stacks Tutorial), there's a new SQL Session factory SqlSessionFactory (https://github.com/hudec/sql-processor/blob/master/sql-processor/src/main/java/org/sqlproc/engine/SqlSessionFactory.java). For every stack there's a special implementation

JDBC - JdbcSessionFactory
Spring - SpringSessionFactory
Hibernate - HibernateSessionFactory

The primary goal is to simplify the API, main for generated DAO layer. Every DAO instance in fact requires two principal attributes

SQL Engine Factory
SQL Session Factory

The usage can be seen in the sample https://github.com/hudec/sql-processor/blob/master/sql-samples/simple-jdbc-crud/src/main/java/org/sqlproc/sample/simple/Main.java.

###Extended Engine factory The Engine factory is described in the Tutorial Stacks Tutorial. The basic methods to create an instance of the META SQL statement are

SqlQueryEngine getQueryEngine(String name);
SqlCrudEngine getCrudEngine(String name);
SqlProcedureEngine getProcedureEngine(String name);

These methods return null value in the case there's no META SQL statement with the required name. The new methods

SqlQueryEngine getCheckedQueryEngine(String name);
SqlCrudEngine getCheckedCrudEngine(String name);
SqlProcedureEngine getCheckedProcedureEngine(String name);

throw an SqlEngineException in the case there's no META SQL statement with the required name. The primary goal is to make the generated DAO layer more robust.

###Extended plugins The business contract of the plugins IsEmptyPlugin and IsTruePlugin has been changed

boolean isNotEmpty(String attributeName, Object obj, Object parentObj, SqlMetaType sqlMetaType,
        String sqlMetaTypeExt, boolean inSqlSetOrInsert, Map<String, String> values, Map<String, Object> features) throws IllegalArgumentException;

public boolean isTrue(String attributeName, Object obj, Object parentObj, SqlMetaType sqlMetaType,
        String sqlMetaTypeExt, Map<String, String> values, Map<String, Object> features);

The primary goal is to handle the new methods toInit and isDef (in fact they are META SQL supplement values) described in SQLEP 1.2.x Changes Tutorial.

###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 SQLEP 1.2.x Changes Tutorial. 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;

###How to load DDL statements There's a new helper class DDLLoader, which can help to load DDL statements (the supported grammar for the DDL file is simplified). The usage is simple

List<String> ddls = DDLLoader.getDDLs(this.getClass(), "hsqldb.ddl");

In the next version of the SQLP the will be new feature - batches support. So it will be easy to execute the loaded DDL statements in the runtime.

###Simple batches The usage is straightforward. Let's say we have a DDL HSQLDB script hsqldb.ddl. To load these DDL statements and execute them, we can use the next code (for the JDBC stack)

List<String> ddls = DDLLoader.getDDLs(this.getClass(), "hsqldb.ddl");
Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:sqlproc", "sa", "");
SqlSessionFactory sessionFactory = new JdbcSessionFactory(connection);

SqlSession sqlSession = sessionFactory.getSqlSession();
sqlSession.executeBatch(ddls.toArray(new String[0]));

SQLP 1.9.x Changes Tutorial - hudec/sql-processor GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️