Package org.sqlproc.engine

Class SqlProcedureEngine

  • public class SqlProcedureEngine
extends SqlEngine
    The primary SQL Processor class for the META SQL stored procedures execution.

    Instance of this class holds one META SQL statement for the stored procedure invocation and one optional Mapping rule.

    For example there's a stored function, which subtracts one hour from the time passed on to this function as a parameter. For HSQLDB it can be 

     CREATE FUNCTION an_hour_before(t TIMESTAMP)
   RETURNS TIMESTAMP
   RETURN t - 1 HOUR
    HSQLDB function returns the result set with one column with generated name. To invoke this function the next META SQL statement and mapping rule should be used 
     SIMPLE_FUNCTION(CALL)=
   call an_hour_before(:time)
 ;
 SIMPLE_FUNCTION(OUT)=
   1$stamp
 ;
    You can see that the name of META SQL statement should has the type (inside the parenthesis) CALL. There's used an output mapping with one mapping item. The database column name is 1, so this name is used as an index to retrieve the output value from the result set. At the same time the META type stamp is used, as there's no result class with the output attribute, which can hold the type of the output value.

    For ORACLE it can be 

     CREATE OR REPLACE FUNCTION an_hour_before (t IN DATE)
 RETURN DATE
 AS 
 BEGIN
    RETURN t - INTERVAL '1' HOUR;
 END an_hour_before;

    and for MySQL it can be 

     CREATE FUNCTION an_hour_before(t TIMESTAMP) RETURNS TIMESTAMP
 BEGIN
       RETURN SUBTIME(t, '1:00:00.000000');
 END
    To invoke them the next META SQL statement without any mapping rule should be used, as there's no output result set 
     SIMPLE_FUNCTION(CALL)=
   :<1^stamp = call an_hour_before(:time)
 ;
    You can see there's a special input value :<1^stamp with the name 1, which is used as an index to register OUT parameter to the CallableStatement. The special character < denotes that this input parameter is in fact of type OUT.

    In the case of the SQL Processor initialization 

     JdbcEngineFactory sqlFactory = new JdbcEngineFactory();
 sqlFactory.setMetaFilesNames("statements.qry"); // the meta statements file
 SqlProcedureEngine sqlEngine = sqlFactory.getProcedureEngine("SIMPLE_FUNCTION");
 Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:sqlproc", "sa", "");
 SqlSession session = new JdbcSimpleSession(connection);
    there's created an instance of SqlProcessorEngine with the name SIMPLE_FUNCTION.

    Let's have an input form 

     public class FormSimpleFunction {
     private java.sql.Timestamp time;
     private java.sql.Timestamp time2;
     // getters and setters
 }
    Next the simple stored function can be executed in the following way 
     FormSimpleFunction f = new FormSimpleFunction();
 f.setTime(new java.sql.Timestamp(new Date().getTime()));
 java.sql.Timestamp result = (java.sql.Timestamp) sqlEngine.callFunction(session, f);

    The result from the stored function execution can be also settled back into a search form. Let's have a META SQl statement 

     SIMPLE_FUNCTION(CALL)=
   :<time2 = call an_hour_before(:time)
 ;
    and run the function in the following way 
     FormSimpleFunction f = new FormSimpleFunction();
 f.setTime(new java.sql.Timestamp(new Date().getTime()));
 sqlEngine.callFunction(session, f);
    The result will be stored in the attribute time2 in the search form FormSimpleFunction.

    For more info please see the Tutorials.

    Author:
    Vladimir Hudec

    • Constructor Detail

      • SqlProcedureEngine

        public SqlProcedureEngine​(String name,
                          String statement,
                          String mapping,
                          SqlTypeFactory typeFactory,
                          SqlPluginFactory pluginFactory)
                   throws SqlEngineException
        Creates a new instance of the SqlProcedureEngine from one stored procedure execution META SQL statement and one SQL mapping rule string. Constructor will call the internal ANTLR parsers for the statement and the mapping rule instances construction. This constructor is devoted to manual META SQL statement and mapping rules construction. More obvious is to put these definitions into the meta statements file and engage the SqlProcessorLoader for the SqlProcedureEngine instances construction.
        Parameters:
        name - the name of this SQL Engine instance
        statement - the stored procedure execution META SQL statement
        mapping - the SQL mapping rule
        typeFactory - the factory for the META types construction
        pluginFactory - the factory for the SQL Processor plugins
        Throws:
        SqlEngineException - in the case the provided statements are not compliant with the ANTLR grammar

      • SqlProcedureEngine

        public SqlProcedureEngine​(String name,
                          String statement,
                          String mapping,
                          SqlMonitor monitor,
                          Map<String,​Object> features,
                          SqlTypeFactory typeFactory,
                          SqlPluginFactory pluginFactory)
                   throws SqlEngineException
        Creates a new instance of the SqlProcedureEngine from one stored procedure execution META SQL statement string and one SQL Mapping rule string. Constructor will call the internal ANTLR parsers for the statement and the mapping rule instances construction. Compared to the previous constructor, an external SQL Monitor for the runtime statistics gathering is engaged and the optional features can be involved. This constructor is devoted to manual META SQL statement and mapping rules construction. More obvious is to put these definitions into the meta statements file and engage the SqlProcessorLoader for the SqlProcedureEngine instances construction.
        Parameters:
        name - the name of this SQL Engine instance
        statement - the stored procedure execution META SQL statement
        mapping - the SQL mapping rule
        monitor - the SQL Monitor for the runtime statistics gathering
        features - the optional SQL Processor features
        typeFactory - the factory for the META types construction
        pluginFactory - the factory for the SQL Processor plugins
        Throws:
        SqlEngineException - mainly in the case the provided statements are not compliant with the ANTLR grammar

      • SqlProcedureEngine

        public SqlProcedureEngine​(String name,
                          SqlMetaStatement statement,
                          SqlMappingRule mapping,
                          SqlTypeFactory typeFactory,
                          SqlPluginFactory pluginFactory)
        Creates a new instance of the SqlProcedureEngine from one stored procedure execution META SQL statement and one SQL mapping rule instances. Both parameters are already pre-compiled instances using the ANTLR parsers. This is the recommended usage for the runtime performance optimization. This constructor is devoted to be used from the custom loader, which is able to read all statements and mapping rules definitions from an external meta statements file and create the named SqlProcedureEngine instances.
        Parameters:
        name - the name of this SQL Engine instance
        statement - the pre-compiled stored procedure execution META SQL statement
        mapping - the pre-compiled SQL mapping rule
        typeFactory - the factory for the META types construction
        pluginFactory - the factory for the SQL Processor plugins

      • SqlProcedureEngine

        public SqlProcedureEngine​(String name,
                          SqlMetaStatement statement,
                          SqlMappingRule mapping,
                          SqlMonitor monitor,
                          Map<String,​Object> features,
                          SqlTypeFactory typeFactory,
                          SqlPluginFactory pluginFactory)
        Creates a new instance of the SqlProcedureEngine from one stored procedure execution META SQL statement and one SQL mapping rule instances. Both parameters are already pre-compiled instances using the ANTLR parsers. This is the recommended usage for the runtime performance optimization. This constructor is devoted to be used from the custom loader, which is able to read all statements and mapping rules definitions from an external meta statements file and create the named instances. Compared to the previous constructor, an external SQL Monitor for the runtime statistics gathering is engaged and the optional features can be involved.
        Parameters:
        name - the name of this SQL Engine instance
        statement - the pre-compiled stored procedure execution META SQL statement
        mapping - the pre-compiled SQL mapping rule
        monitor - the SQL Monitor for the runtime statistics gathering
        features - the optional SQL Processor features
        typeFactory - the factory for the META types construction
        pluginFactory - the factory for the SQL Processor plugins

      • SqlProcedureEngine

        public SqlProcedureEngine​(String name,
                          SqlMetaStatement statement,
                          SqlMappingRule mapping,
                          SqlMonitor monitor,
                          Map<String,​Object> features,
                          SqlTypeFactory typeFactory,
                          SqlPluginFactory pluginFactory,
                          SqlEngineConfiguration configuration)
        Creates a new instance of the SqlProcedureEngine from one stored procedure execution META SQL statement and one SQL mapping rule instances. Both parameters are already pre-compiled instances using the ANTLR parsers. This is the recommended usage for the runtime performance optimization. This constructor is devoted to be used from the custom loader, which is able to read all statements and mapping rules definitions from an external meta statements file and create the named instances. Compared to the previous constructor, an external SQL Monitor for the runtime statistics gathering is engaged and the optional features can be involved.
        Parameters:
        name - the name of this SQL Engine instance
        statement - the pre-compiled stored procedure execution META SQL statement
        mapping - the pre-compiled SQL mapping rule
        monitor - the SQL Monitor for the runtime statistics gathering
        features - the optional SQL Processor features
        typeFactory - the factory for the META types construction
        pluginFactory - the factory for the SQL Processor plugins
        configuration - the overall configuration, which can be persisted

    • Method Detail

      • callQuery

        public <E> List<E> callQuery​(SqlSession session,
                             Class<E> resultClass,
                             Object dynamicInputValues,
                             Object staticInputValues,
                             int maxTimeout)
                      throws SqlProcessorException,
                             SqlRuntimeException
        Runs the stored procedure based on the META SQL statement to obtain a list of database rows. This is the primary and the most complex SQL Processor execution method to obtain a list of result class instances. The parameters for the stored procedure execution are taken from the input values.
        Parameters:
        session - The SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
        resultClass - The class used for the return values, the stored procedure execution output. This class is also named as the output class or the transport class, In fact it's a standard POJO class, which must include all the attributes described in the mapping rule statement. This class itself and all its subclasses must have public constructors without any parameters. All the attributes used in the mapping rule statement must be accessible using public getters and setters. The instances of this class are created on the fly in the process of the stored procedure execution using the reflection API.
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        staticInputValues - The object used for the stored procedure static input values. The class of this object is also named as the input class or the static parameters class. The exact class type isn't important, all the parameters injected into the callable statement are picked up using the reflection API. Compared to dynamicInputValues input parameters, parameters in this class should't be produced by an end user to prevent SQL injection threat!
        maxTimeout - The max SQL execution time. This parameter can help to protect production system against ineffective SQL statements. The value is in milliseconds.
        Returns:
        The list of the resultClass instances.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • callQuery

        public <E> List<E> callQuery​(SqlSession session,
                             Class<E> resultClass,
                             Object dynamicInputValues,
                             SqlControl sqlControl)
                      throws SqlProcessorException,
                             SqlRuntimeException
        Runs the stored procedure based on the META SQL statement to obtain a list of database rows. This is the primary and the most complex SQL Processor execution method to obtain a list of result class instances. The parameters for the stored procedure execution are taken from the input values.
        Parameters:
        session - The SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
        resultClass - The class used for the return values, the stored procedure execution output. This class is also named as the output class or the transport class, In fact it's a standard POJO class, which must include all the attributes described in the mapping rule statement. This class itself and all its subclasses must have public constructors without any parameters. All the attributes used in the mapping rule statement must be accessible using public getters and setters. The instances of this class are created on the fly in the process of the stored procedure execution using the reflection API.
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        sqlControl - The compound parameters controlling the META SQL execution
        Returns:
        The list of the resultClass instances.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • callQuery

        private <E> List<E> callQuery​(SqlQuery query,
                              SqlMappingResult mappingResult,
                              Class<E> resultClass)
        Internal callQuery implementation
        Parameters:
        query - query
        mappingResult - mappingResult
        resultClass - resultClass
        Returns:
        the result

      • callUpdate

        public int callUpdate​(SqlSession session,
                      Object dynamicInputValues,
                      Object staticInputValues,
                      int maxTimeout)
               throws SqlProcessorException,
                      SqlRuntimeException
        Runs the stored procedure based on the META SQL statement. This is the primary and the most complex SQL Processor execution method devoted to CRUD commands execution from inside the stored procedure.
        Parameters:
        session - The SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        staticInputValues - The object used for the stored procedure static input values. The class of this object is also named as the input class or the static parameters class. The exact class type isn't important, all the parameters injected into the callable statement are picked up using the reflection API. Compared to dynamicInputValues input parameters, parameters in this class should't be produced by an end user to prevent SQL injection threat!
        maxTimeout - The max SQL execution time. This parameter can help to protect production system against ineffective SQL statements. The value is in milliseconds.
        Returns:
        The number of persisted, updated, deleted or otherwise affected database rows. It's value strongly depends on the type of database.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • callUpdate

        public int callUpdate​(SqlSession session,
                      Object dynamicInputValues,
                      SqlControl sqlControl)
               throws SqlProcessorException,
                      SqlRuntimeException
        Runs the stored procedure based on the META SQL statement. This is the primary and the most complex SQL Processor execution method devoted to CRUD commands execution from inside the stored procedure.
        Parameters:
        session - The SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        sqlControl - The compound parameters controlling the META SQL execution
        Returns:
        The number of persisted, updated, deleted or otherwise affected database rows. It's value strongly depends on the type of database.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • callUpdate

        private Integer callUpdate​(SqlQuery query,
                           SqlProcessResult processResult)
        Internal callUpdate implementation
        Parameters:
        query - query
        processResult - processResult
        Returns:
        the result

      • callFunction

        public Object callFunction​(SqlSession session,
                           Object dynamicInputValues,
                           Object staticInputValues,
                           int maxTimeout)
                    throws SqlProcessorException,
                           SqlRuntimeException
        Runs the stored function based on the META SQL statement. This is the primary and the most complex SQL Processor execution method devoted to obtain the value from the stored function execution.
        Parameters:
        session - The SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        staticInputValues - The object used for the stored procedure static input values. The class of this object is also named as the input class or the static parameters class. The exact class type isn't important, all the parameters injected into the callable statement are picked up using the reflection API. Compared to dynamicInputValues input parameters, parameters in this class should't be produced by an end user to prevent SQL injection threat!
        maxTimeout - The max SQL execution time. This parameter can help to protect production system against ineffective SQL statements. The value is in milliseconds.
        Returns:
        The result from the stored function execution.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • callFunction

        public Object callFunction​(SqlSession session,
                           Object dynamicInputValues,
                           SqlControl sqlControl)
                    throws SqlProcessorException,
                           SqlRuntimeException
        Runs the stored function based on the META SQL statement. This is the primary and the most complex SQL Processor execution method devoted to obtain the value from the stored function execution.
        Parameters:
        session - The SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        sqlControl - The compound parameters controlling the META SQL execution
        Returns:
        The result from the stored function execution.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • callFunction

        private Object callFunction​(SqlQuery query,
                            SqlProcessResult processResult,
                            SqlMappingResult mappingResult)
        Internal callFunction implementation
        Parameters:
        query - query
        processResult - processResult
        Returns:
        the result

      • getSql

        public String getSql​(Object dynamicInputValues,
                     Object staticInputValues,
                     SqlMetaStatement.Type statementType)
              throws SqlProcessorException,
                     SqlRuntimeException
        Because SQL Processor is Data Driven Query engine, every input parameters can produce in fact different SQL statement command. This method can help to identify the exact SQL statement command, which is produced in the background of the SQL Processor execution. The statement is derived from the META SQL statement.
        Parameters:
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        staticInputValues - The object used for the stored procedure static input values. The class of this object is also named as the input class or the static parameters class. The exact class type isn't important, all the parameters injected into the callable statement are picked up using the reflection API. Compared to dynamicInputValues input parameters, parameters in this class should't be produced by an end user to prevent SQL injection threat!
        statementType - The type of the statement under consideration. For the stored procedures it is CALL.
        Returns:
        The SQL statement command derived from the META SQL statement based on the input parameters.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • getSql

        public String getSql​(Object dynamicInputValues,
                     SqlControl sqlControl,
                     SqlMetaStatement.Type statementType)
              throws SqlProcessorException,
                     SqlRuntimeException
        Because SQL Processor is Data Driven Query engine, every input parameters can produce in fact different SQL statement command. This method can help to identify the exact SQL statement command, which is produced in the background of the SQL Processor execution. The statement is derived from the META SQL statement.
        Parameters:
        dynamicInputValues - The object used for the stored procedure dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL callable statement are picked up using the reflection API. At the same time this object can collect the output values from all OUT and INOUT stored procedure parameters.
        sqlControl - The compound parameters controlling the META SQL execution
        statementType - The type of the statement under consideration. For the stored procedures it is CALL.
        Returns:
        The SQL statement command derived from the META SQL statement based on the input parameters.
        Throws:
        SqlProcessorException - in the case of any problem with ORM or JDBC stack
        SqlRuntimeException - in the case of any problem with the input/output values handling

      • getName

        public String getName()
        Returns the name of this META SQL, which uniquely identifies the instance. In the case the META SQL statement is located in the meta statements file, this name is the unique part of the keys in this file. For example for the name ALL in the meta statements file there's the META SQL statement with the name ALL(CALL).
        Returns:
        The name of the SQL engine instance.

      • getMonitor

        public SqlMonitor getMonitor()
        Returns the SQL Monitor instance for the runtime statistics gathering. By default no runtime statistics gathering is active. So this SQL Monitor is implied in SQL engine constructor in the case the statistics gathering should be engaged.
        Returns:
        The SQL Monitor instance, which is active for this SQL engine instance.