SQLMEP SQLMOP Functions and Procedures

The tutorial is updated for SQLP 3.1 and 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.

Procedures and functions

The next new features of the SQLMOP is the ability to generate POJOs and DAOs for procedures and functions. First please see the tutorial Stored procedures tutorial.

We can use the advanced template procedures, which loads the definitions of all database procedures in the current schema. Let's have the database structure, as is used in the simple sample based for HSQLDB database https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-hsqldb/src/main/resources/hsqldb.ddl. We should use the next control directives in the definitions.model, as is described in SQLMEP META SQL generation, to establish connection to the target database:

database-is-online;
database-jdbc-driver org.hsqldb.jdbcDriver;
database-has-url jdbc:hsqldb:mem:sqlproc;
database-login-username sa;
database-login-password "";
database-ddl-create hsqldb.ddl; // should be located in the same directory as definitions.qry

The complete definitions.meta can be found at https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-hsqldb/src/main/resources/definitions.model.

Next the template procedures should be used to generate the following snippet in definitions.model. Just put the cursor anywhere in the definitions.model, press CTRL-Space and pick up the procedures:

is-procedure anHourBefore AN_HOUR_BEFORE;
is-procedure newPerson NEW_PERSON;
is-procedure newPersonRetRs NEW_PERSON_RET_RS;

Some of the procedures are in fact functions, like AN_HOUR_BEFORE in this sample. Unfortunately the JDBC class DatabaseMetaData for every database returns another set of meta data, and mostly these data are not complete or correct. So we can used another advanced template functions, which is used to correct the definitions of some database procedures in the current schema. Just put the cursor anywhere in the definitions.model, press CTRL-Space and pick up the functions:

is-function anHourBefore AN_HOUR_BEFORE;

Both templates can return also some system database procedures or functions. Just delete the lines not required in your application.

Another lack of the JDBC drivers is that they don't return the meta data regarding functions' result. For example the Oracle JDBC driver is fine, but the HSQLDB is missing this feature. We can use the next control directive to fix up the missing info, like in the next code snippet

metagen-function-result AN_HOUR_BEFORE stamp;

As obvious, everywhere the content assist can be utilized, like to pick up the correct function name AN_HOUR_BEFORE or the required META type stamp.

In the case the procedure or function returns result set, we have to use the control directive metagen-procedure-result-set or metagen-function-result-set to supply the info regarding this result set. For example in the following snippet the procedure NEW_PERSON_RET_RS returns the rows of table PERSON

metagen-procedure-result-set NEW_PERSON_RET_RS PERSON;

Or we can construct new synthetic table (several databases are able to return the meta info required)

pojogen-create-tables NEW_PERSON_RET_RS_RESULT;
pojogen-create-columns NEW_PERSON_RET_RS_RESULT ID->java.lang.Long FIRST_NAME->java.lang.String 
LAST_NAME->java.lang.String DATE_OF_BIRTH->java.util.Date GENDER->::org.sample.model.PersonGender SSN->java.lang.String;

and assign this table to the procedure

metagen-procedure-result-set NEW_PERSON_RET_RS NEW_PERSON_RET_RS_RESULT;

Next delete in pojo.model everything between package org.sample.model { and the final }, put the cursor before the final }, press CRTL-SPACE and use the template pojogen to recreate POJOs. All related Java classes in src-gen are recreated. We can see that for every procedure or function a new POJO is created. This POJO holds all input/output parameters used to invoke the procedure or function. The re-created pojo.model can be seen at https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-hsqldb/src/main/resources/pojo.model.

Similarly delete in dao.model everything between package org.sample.dao { and the final }, put the cursor before the final }, press CRTL-SPACE and use the template daogen to recreate POJOs. All related Java classes in src-gen are recreated. The re-created dao.model can be seen at https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-hsqldb/src/main/resources/dao.model.

Finally delete in statements.meta all the content, press CRTL-SPACE and use the template metagen to recreate META SQL statements. The re-created statements.qry can be seen at https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-hsqldb/src/main/resources/statements.meta.

The final step is a pleasure - just used the generated code

    // function
    AnHourBeforeDao anHourBeforeDao = new AnHourBeforeDao(sqlFactory, sessionFactory);
    AnHourBefore anHourBefore = new AnHourBefore();
    anHourBefore.setT(new java.sql.Timestamp(new Date().getTime()));
    java.sql.Timestamp result = anHourBeforeDao.anHourBefore(anHourBefore);

    // procedures
    NewPersonDao newPersonDao = new NewPersonDao(sqlFactory, sessionFactory);
    NewPerson newPerson = new NewPerson();
    newPerson.setFirstName("Maruska");
    newPerson.setLastName("Maruskova");
    newPerson.setSsn("999888777");
    newPerson.setDateOfBirth(getAge(1969, 11, 1));
    newPersonDao.newPerson(newPerson);

    NewPersonRetRsDao newPersonRetRsDao = new NewPersonRetRsDao(sqlFactory, sessionFactory);
    NewPersonRetRs newPersonRetRs = new NewPersonRetRs();
    newPersonRetRs.setFirstName("Beruska");
    newPersonRetRs.setLastName("Beruskova");
    newPersonRetRs.setSsn("888777666");
    newPersonRetRs.setDateOfBirth(getAge(1969, 1, 21));
    List<NewPersonRetRsResult> list = newPersonRetRsDao.newPersonRetRs(newPersonRetRs);

The complete code can be seen at https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-hsqldb/src/main/java/org/sample/Main.java.

Extended DAO modelling

The basic DAO modelling can be found at SQLMOP DAO modelling. To support the procedures and functions, this grammar has been enhanced, as it can be seen in the next model dao.model

package org.sample.dao {
    ...
    #ProcedureUpdate(int,org.sample.model.NewPerson)
    #Dao NewPersonDao {
    }

    #ProcedureCallQuery(java.util.List<org.sample.model.NewPersonRetRsResult>,org.sample.model.NewPersonRetRs)
    #Dao NewPersonRetRsDao {
    }

    #FunctionCall(java.sql.Timestamp,org.sample.model.AnHourBefore)
    #Dao AnHourBeforeDao {
    }
}

ProcedureUpdate

The DAO NewPersonDao has the modifier (a META annotation) #ProcedureUpdate. This is the indicator, which method is used in the SQLP runtime (for more info please see the SqlProcedureEngine) - in this case it's

int callUpdate(final SqlSession session, final Object dynamicInputValues, final SqlControl sqlControl)

There are two parameters for #ProcedureUpdate

• int - the procedure invocation returns the number of affected database rows
• NewPerson - this POJO holds all real input parameters or output values

ProcedureCallQuery

The DAO NewPersonRetRsDao has the modifier (a META annotation) #ProcedureCallQuery. This is the indicator, which method is used in the SQLP runtime (for more info please see the SqlProcedureEngine) - in this case it's

public <E> List<E> callQuery(final SqlSession session, final Class<E> resultClass, final Object dynamicInputValues, final SqlControl sqlControl)

There are two parameters for #ProcedureCallQuery

• java.util.List<org.sample.model.NewPersonRetRsResult> - the POJO list holds all real output values
• org.sample.model.NewPersonRetRs - this POJO holds all real input parameters

FunctionCall

The DAO AnHourBeforeDao has the modifier (a META annotation) #FunctionCall. This is the indicator, which method is used in the SQLP runtime (for more info please see the SqlProcedureEngine) - in this case it's

public Object callFunction(final SqlSession session, final Object dynamicInputValues, final SqlControl sqlControl)

There are two parameters for #ProcedureCallQuery

• java.sql.Timestamp - the POJO holds all real output values
• org.sample.model.AnHourBefore - this POJO holds all real input parameters

FunctionCallQuery

In Oracle devoted sample at https://github.com/hudec/sql-processor/blob/master/simple-samples/simple-oracle/src/main/resources/dao.model we can see yet another template

#FunctionCallQuery(java.util.List<org.sample.model.Person>,org.sample.model.NewPersonRetRs)
#Dao NewPersonRetRsDao {
}

This is the same, as #ProcedureCallQuery - just it's used for database function instead of database procedure.