CRUD 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.

Introduction

In the version 1.2 the SQL Processor engine can also handle the ANSI SQL CRUD statements. For the basic Simple Tutorial, please see the Simple Tutorial. Next only the CRUD statements are described.

INSERT statement

The CREATE part of CRUD is in fact an SQL INSERT statement. The related META SQL statement is again defined in the meta statements file statements.meta, like the next one:

INSERT_PERSON(CRUD)=
  insert into PERSON (ID, NAME)
  {= values (:id, :name) }
;

This META SQL statement definition begins with it's name (INSERT_PERSON) 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 (CRUD) of the SQL Processor artifact. In this case it's a META SQL CRUD statement. At the end of the definition there's a semicolon.

Of course we have to take into account there's an auto-generated identity column ID in the table PERSON. We have to indicate this feature, like in the next example:

IDSEL=SIMPLE_IDENTITY(OPT)=call identity();

INSERT_PERSON(CRUD)=
  insert into PERSON (ID, NAME)
  {= values (:id(idgen=SIMPLE_IDENTITY,id=ID), :name) }
;

In this case the SQL SELECT query call identity() is used to obtain the value of the auto-generated identity. This query is executed immediately after the INSERT statement. This is done automatically, behind the surface.

Another possibility is to use the sequence, like in the next example:

SEQ=SIMPLE_SEQUENCE(OPT)=call next value for SIMPLE_SEQUENCE;

INSERT_PERSON(CRUD)=
  insert into PERSON (ID, NAME)
  {= values (:id(idgen=SIMPLE_SEQUENCE,id=ID), :name) }
;

The sequence PERSON_SEQUENCE can be created in the following way (for HSQLDB):

CREATE SEQUENCE SIMPLE_SEQUENCE AS BIGINT START WITH 100 INCREMENT BY 1;

In the previously used META SQL statement INSERT_PERSON(CRUD):

insert into PERSON (ID, NAME) is a standard SQL fragment for the insert statement.
{= values is a marker for VALUES 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 VALUES will become a prefix of this fragment.
(:id(idgen=SIMPLE_SEQUENCE,id=ID), :name) is a list of the input values. The input values can be accompanied with modifiers in parenthesis, like idgen=SIMPLE_SEQUENCE or id=ID for the input value id. This modifier indicates, that for ID column the sequence SIMPLE_SEQUENCE will be used.
(:id(idgen=SIMPLE_IDENTITY,id=ID), :name) is a list of the input values. The input values can be accompanied with modifiers in parenthesis, like idgen=SIMPLE_IDENTITY or id=ID for the input value id. This modifier indicates, that for ID column the select SIMPLE_IDENTITY will be used immediately after the insert statement.

There's also an optional feature

SEQ=SIMPLE_SEQUENCE(OPT)=call next value for SIMPLE_SEQUENCE;, which defines the correct sequence code for the HSQLDB database.

or

IDSEL=SIMPLE_IDENTITY(OPT)=call identity(), which defines the query select code for the HSQLDB database to obtain a value of the auto-generated identity.

To persist a new Person data into the database (in the case the SQL Processor 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
SqlCrudEngine sqlEngine = sqlFactory.getCrudEngine("INSERT_PERSON");

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

person = new Person();
person.setName("Jan");
int count = sqlEngine.insert(session, person);

The SqlCrudEngine is the SQL Processor engine itself. It contains a pre-compiled META SQL statement and the optional features.

The first parameter into the insert() method is an SQL Processor session. The second parameter is a POJO class, which carries all dynamic input values. The return value is the number of inserted rows, which can be used to verify, that the insert statement was successful.

In the case the ID column is generated by sequence SEQ1 or it's auto-generated (SEL1), the attribute id of the input form Person holds this value.

GET statement

The RETRIEVE part of CRUD is in fact an SQL QUERY statement, which produces a unique row in the ResultSet. The related META SQL statement is again defined in the meta statements file statements.meta, like the next one:

GET_PERSON(CRUD)=
  select @id, @name
  from PERSON
  {= where 
    {& id=:id }
  }
;

In the previously used META SQL query GET_PERSON(CRUD):

select @id, @name from PERSON is a standard SQL fragment. From this fragment the character @ is removed. It it used to correlate the aliases of the 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. The input value here is :id, and it should not be null, so the final SQL query will contain the next fragment id=:id and value of id attribute will be seeded into a prepared statement.

To get a Person data from the database for a person with some id value (in the case the SQL Processor 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
SqlCrudEngine sqlEngine = sqlFactory.getCrudEngine("GET_PERSON");

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

Person person = new Person();
person.setId(1);
Person p = sqlEngine.get(session, Person.class, person);

The first parameter into the get() method is an SQL Processor session. The second parameter is a result class, so the SQL Processor knows, how to construct the instance of the output values. The third parameter is a search form, which carries all dynamic input values (in this case only the id).

UPDATE statement

The UPDATE part of CRUD is in fact an SQL UPDATE statement. The related META SQL statement is again defined in the meta statements file statements.meta, like the next one:

UPDATE_PERSON(CRUD)=
  update PERSON
  {= set 
    { ,name = :name }
  }
  {= where 
    {& id = :id(notnull) }
  }
;

In the previously used META SQL statement UPDATE_PERSON(CRUD):

update PERSON is a standard SQL fragment for an update statement.
{= set is a marker for the SET 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 SET will become a prefix of this fragment.
{ ,name = :name } next is a list of the input values, which have to be updated.
{= 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(notnull) } is a conditional SQL fragment of type AND. Everything between {& and } will become a part of the final SQL query only in the case all input values in this conditional fragment are non-empty. At the same time there's a modifier notnull defined. This notnull means the attribute id is checked for the null value. In the positive case an IllegalArgumentExcpetion is thrown. This prevents updating all rows in database at the same time.

To update a Person data to the database for a person with some id value (in the case the SQL Processor 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
SqlCrudEngine sqlEngine = sqlFactory.getCrudEngine("UPDATE_PERSON");

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

Person person = new Person();
person.setId(1);
person.setName("Bozena");
int count = sqlEngine.update(session, person);

The first parameter into the update() method is an SQL Processor session. The second parameter is a POJO class, which carries all dynamic input values. The return value is the number of updated rows, which can be used to verify, that the update statement was successful.

DELETE statement

The DELETE part of CRUD is in fact an SQL DELETE statement. The related META SQL statement is again defined in the meta statements file statements.meta, like the next one:

DELETE_PERSON(CRUD)=
  delete from PERSON
  {= where 
    {& id = :id(notnull) }
  }
;

In the previously used META SQL statement DELETE_PERSON(CRUD):

delete from PERSON is a standard SQL fragment for a delete statement.
{= 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(notnull)} is a conditional SQL fragment of type AND. Everything between {& and } will become a part of the final SQL query only in the case all input values in this conditional fragment are non-empty. At the same time there's a modifier notnull defined. This notnull means the attribute id is checked for null value. In the positive case an IllegalArgumentExcpetion is thrown. This prevents all rows in the database from being deleted at the same time.

To delete a Person data from the database for a person with some id value (in the case the SQL Processor 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
SqlCrudEngine sqlEngine = sqlFactory.getCrudEngine("DELETE_PERSON");

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

Person person = new Person();
person.setId(1);
int count = sqlEngine.delete(session, person);

The first parameter into the delete() method is an SQL Processor session. The second parameter is a search form, which carries all dynamic input values. The return value is the number of deleted rows, which can be used to verify, that the delete statement was successful.

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly