Stacks Tutorial - hudec/sql-processor GitHub Wiki

Table of Contents

Maven dependencies
Engine factories
Extended engine factories
SQLP lazy initilization
Session factories

The SQL Processor can cooperate with several persistence related technology:

  • the JDBC stack
  • the Hibernate stack
  • the Spring DAO stack

Maven dependencies

JDBC stack

In the SQL Processor core package the JDBC API is used to provide all database related operations. To run the SQL Processor on top of the JDBC stack, the following configuration snippet should be used in the Maven pom.xml

    <dependency>
        <groupId>org.sqlproc</groupId>
        <artifactId>sql-processor</artifactId>
        <version>2.2.1</version>
    </dependency>

Hibernate stack

Another possibility is to use the SQL Processor alongside the Hibernate ORM. There are several ways, how to split the job between them:

  • all CRUD operations can be done using the Hibernate JPA and all the queries can be done using the SQL Processor
  • the SQL Processor can handle all database related operations, the usage of the Hibernate is restricted to the first or second level cache, the advanced data transformation and so on

To run the SQL Processor on top of the Hibernate stack, the following configuration snippet should be used in the Maven pom.xml

    <dependency>
        <groupId>org.sqlproc</groupId>
        <artifactId>sql-processor</artifactId>
        <version>2.2.1</version>
    </dependency>
    <dependency>
        <groupId>org.sqlproc</groupId>
        <artifactId>sql-processor-hibernate</artifactId>
        <version>2.2.1</version>
    </dependency>

Spring DAO stack

The last possibility is to use the SQL Processor alongside the Spring DAO. This offers a couple of benefits, mainly in the case of the web applications. To run the SQL Processor on top of the Spring DAO stack, the following configuration snippet should be used in the Maven pom.xml

    <dependency>
        <groupId>org.sqlproc</groupId>
        <artifactId>sql-processor</artifactId>
        <version>2.2.1</version>
    </dependency>
    <dependency>
        <groupId>org.sqlproc</groupId>
        <artifactId>sql-processor-spring</artifactId>
        <version>2.2.1</version>
    </dependency>

Engine factories

JDBC stack

The basic factory is the JdbcEngineFactory. In the process of the factory initialization the next job is done:

  • the META type factory JdbcTypeFactory is instantiated
  • the optional feature SET_JDBC is set to true
  • the meta statements file is read and all META SQL statements are pre-compiled

Using the setters a lot of factory attributes can be set before the initialization is done. The typical usage is very simple:

JdbcEngineFactory sqlFactory = new JdbcEngineFactory();
sqlFactory.setMetaFilesNames("statements.qry"); // meta statements file
sqlFactory.setFilter(SqlFeature.HSQLDB); // the optional database type to improve the SQLP behaviour
SqlQueryEngine sqlEngine = sqlFactory.getCrudEngine("QUERY_NAME");

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

Hibernate stack

The basic factory is the HibernateEngineFactory. In the process of the factory initialization the next is done:

  • the META type factory HibernateTypeFactory is instantiated
  • the optional feature SET_JDBC is set to false
  • the meta statements file is read and all META SQL statements are pre-compiled

Using the setters a lot of factory attributes can be set before the initialization is done. The typical usage is very simple:

HibernateEngineFactory sqlFactory = new HibernateEngineFactory();
sqlFactory.setMetaFilesNames("statements.qry"); // meta statements file
sqlFactory.setFilter(SqlFeature.HSQLDB); // the optional database type to improve the SQLP behaviour
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("QUERY_NAME");

For this case the sample Simple is available in GIT https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-hibernate/.

Spring DAO stack

The basic factory is the SpringEngineFactory. In the process of the factory initialization the next is done:

  • the META type factory SpringTypeFactory is instantiated
  • the optional feature SET_JDBC is set to true
  • the meta statements file is read and all META SQL statements are pre-compiled

Using the setters a lot of factory attributes can be set before the initialization is done. The typical usage is very simple:

SpringEngineFactory sqlFactory = new SpringEngineFactory();
sqlFactory.setMetaFilesNames("statements.qry"); // meta statements file
sqlFactory.setFilter(SqlFeature.HSQLDB); // the optional database type to improve the SQLP behaviour
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("QUERY_NAME");

Another possibility is to use the Spring DI:

ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
SqlEngineFactory sqlFactory = context.getBean("sqlFactory", SqlEngineFactory.class);

with the following snippet in the applicationContext.xml:

<bean id="sqlFactory" class="org.sqlproc.engine.spring.SpringEngineFactory" init-method="init">
    <property name="metaFilesNames">
        <list>
            <value>statements.qry</value>
        </list>
    </property>
    <property name="filter" value="HSQLDB" />
    <property name="customTypes">
        <list>
            <bean class="org.sqlproc.sample.simple.type.PhoneNumberType"></bean>
        </list>
    </property>
</bean>

In the previous configuration also the custom META type PhoneNumberType was injected into the engine factory. For this case the sample Simple is available in GIT https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-spring/.

##Extended Engine factories 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.

##SQLP lazy initialization A standard behaviour of the Engine Factories is to pre-compile all instances of META SQL statements, when the first instance is established. It can take some time, mainly in the case META SQL statements are generated using SQLEP (so there are lot of statements, regardless the are used or not in the runtime).

To speed up the SQLP start-up, a lazy mode of the Engine Factories can be configured. For example for the Spring stack the configuration can be the next one

<bean id="sqlFactory" class="org.sqlproc.engine.spring.SpringEngineFactory" init-method="init">
    <property name="metaFilesNames">
        <list>
            <value>statements.qry</value>
        </list>
    </property>
    <property name="filter" value="HSQLDB" />
    <property name="lazyInit" value="true" />
</bean>

It can be used mainly in the development phase or for the JUnit based tests.

Session factories

JDBC stack

To call any method of the SQL Processor engine the instance of the SqlSession has to be establish. There are several ways, like the next one:

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

SqlSession can be implemented by JdbcSession or JdbcSimpleSession. Both of them are built on top of JDBC Connection.

Hibernate stack

To call any method of the SQL Processor engine the instance of the SqlSession has to be establish. There are several ways, like the next one:

Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
SqlSessionFactory sessionFactory = new HibernateSessionFactory(configuration.buildSessionFactory());
SqlSession session = sessionFactory.getSqlSession();

with the following snippet in the hibernate.cfg.xml:

<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
    <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
    <property name="hibernate.connection.url">jdbc:hsqldb:mem:sqlproc</property>
    <property name="hibernate.connection.username">sa</property>
    <property name="hibernate.connection.password"></property>
    <!-- Use the Hibernate built-in pool for tests. -->
    <property name="hibernate.connection.pool_size">1</property>
    <property name="hibernate.connection.autocommit">true</property>
</hibernate-configuration>

SqlSession can be implemented by HibernateSession or HibernateSimpleSession. Both of them are built on top of Hibernate Session.

Spring DAO stack

To call any method of the SQL Processor engine the instance of the SqlSession has to be establish. There are several ways, like the next one:

SqlSessionFactory = context.getBean("sessionFactory", SqlSessionFactory.class);
SqlSession session = sessionFactory.getSqlSession();

with the following snippet in the applicationContext.xml:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:sqlproc" />
    <property name="username" value="sa" />
    <property name="password" value="" />
    <property name="defaultAutoCommit" value="true" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="sessionFactory" class="org.sqlproc.engine.spring.SpringSessionFactory">
    <constructor-arg ref="jdbcTemplate" />
</bean>

SqlSession can be implemented by SpringSimpleSession. The SpringSimpleSession is a simple wrapper around the Spring JdbcTemplate.

Summary

To summarize there are several implementations of the SQL Session factory SqlSessionFactory

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.

##Next steps The next SQLP usage can be seen at Using the API.

The tutorial is updated for SQLP 2.2

Add a custom footer
⚠️ **GitHub.com Fallback** ⚠️