Catalog (Spring) Sample

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.

Table of Contents

To run this sample on HSQLDB
To run this sample on Oracle
The SQL Processor usage

This simple web application is used to present the very basic features of the SQL Processor.

Catalog itself is based on Wicket+Spring+Maven+Jetty. In this version there's used Oracle or HSQLDB as a database. It's a simple web application, which enable you to do all CRUD operations on the database table ITEM using the SQL Processor. At he same time the SQL Processor is used for all queries execution. It produces a list of pets based on a search criteria.

So this version of the Catalog sample presents the ability of the SQL Processor to provide all database related operations on top of Spring DAO. The Hibernate ORM isn't used at all.

To run this sample on HSQLDB

1. checkout the code

   git clone https://github.com/hudec/sql-processor.git

   cd sql-processor
mvn clean install
   

2. compile the SQL Processor samples

   cd sql-samples mvn clean install

3. run web application

   cd catalog-spring/web
mvn jetty:run-war

4. access the web application on the URL http://localhost:8080/catalog

To run this sample on Oracle

1. checkout the code

   git clone https://github.com/hudec/sql-processor.git

   cd sql-processor
mvn clean install
cd sql-samples

2. install Oracle XE, a good tutorial for Ubuntu you can find at http://mediakey.dk/~cc/ubuntu-howto-install-oracle/

3. create the user catalog/catalog123 with the appropriate privileges using Oracle XE admin web pages

4. install into the Maven repository Oracle JDBC driver. You can find this driver at http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html. In this case the driver ojdbc6.jar is used. To install this driver into the Maven repository you can use

   mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=ojdbc6.jar

5. create and seed the table ITEM

   cd catalog-spring

you can use the SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. Load the SQL script database/catalog.sql

6. use the Groovy script database/img2db.groovy to import the images into db. The images are located in directory business/src/main/resources/images

7. compile the Catalog Sample with the profile Oracle

   mvn clean install -P oracle

8. run web application

   cd web
mvn jetty:run-war

9. access web application on the URL http://localhost:8080/catalog

The SQL Processor usage

The Meta SQL statements are defined in the meta statements file statements.qry:

SEQ1(OPT,HSQLDB)=call next value for HIBERNATE_SEQUENCE;
SEQ1(OPT,ORACLE)=select HIBERNATE_SEQUENCE.nextval from dual;

ITEMS(QRY)=
  select @itemid, @productid, @name, @description, @imageurl, @imagethumburl,
         @image, @imagethumb, @price
  from item it
  {= where
   {& it.itemid=:itemid}
   {& UPPER(it.name) like :+name}
   {& UPPER(it.description) like :+description}
   {& it.price >= :priceFrom}
   {& it.price <= :priceTo}
  }
  {#NAME order by name}
  {#PRICE order by price}
;

GET_ITEM(CRUD)=
  select @itemid, @productid, @name, @description, @imageurl, @imagethumburl,
         @image, @imagethumb, @price
  from item it
  {= where it.itemid=:itemid(notnull)}
;

DELETE_ITEM(CRUD)=
  delete from item it
  {= where it.itemid=:itemid(notnull)}
;

INSERT_ITEM(CRUD)=
  insert into item (itemid, productid, name, description, imageurl, imagethumburl, image, imagethumb, price)
  {= values (:itemid(seq=SEQ1), :productid, :name, :description, :imageurl, :imagethumburl, :image, :imagethumb, :price) }
;

UPDATE_ITEM(CRUD)=
  update item it
  {= set it.productid = :productid, it.name = :name, it.description = :description,
      it.imageurl = :imageurl, it.imagethumburl = :imagethumburl, it.image = :image,
      it.imagethumb = :imagethumb, it.price = :price}
  {= where it.itemid = :itemid(notnull)}
;

The search form ItemForm.java is used to transport the search criteria into the SQL Processor. It's a simple Java bean with the next attributes and setters/getters:

public class ItemForm implements Serializable {
    private Long itemid;
    private String name;
    private String description;
    private BigDecimal priceFrom;
    private BigDecimal priceTo;

    // the next attributes are used to support the pagination and sorting
    int first;
    int count;
    int order;

    public ItemForm() {
    }
...
}

The data transport object (DTO) ItemTO.java is used to transport the query result data from the SQL Processor. It's a simple Java bean with the next attributes and setters/getters:

public class ItemTO implements Serializable {

    private Long itemid;
    private String productid;
    private String name;
    private String description;
    private String imageurl;
    private String imagethumburl;
    private byte[] image;
    private byte[] imagethumb;
    private BigDecimal price;

    public ItemTO() {
    }
...
}

The persistence layer contains DAO class ItemDao.java with the next code:

public class ItemDao {

  protected SqlEngineFactory sqlFactory;

  public Item findById(SqlSession session, Long id) {
    Item item = sqlFactory.getCheckedCrudEngine("GET_ITEM").get(session, Item.class, new Item(id));
    return item;
  }

  public Item create(SqlSession session, Item item) {
    sqlFactory.getCheckedCrudEngine("INSERT_ITEM").insert(session, item);
    return item;
  }

  public Item update(SqlSession session, Item item) {
    sqlFactory.getCheckedCrudEngine("UPDATE_ITEM").update(session, item);
    return item;
  }

  public void delete(SqlSession session, Long id) {
    sqlFactory.getCheckedCrudEngine("DELETE_ITEM").delete(session, new Item(id));
  }

  public List<ItemTO> find(SqlSession session, ItemForm criteria) {
    return sqlFactory.getCheckedQueryEngine("ITEMS").query(session, ItemTO.class, criteria, null,
            OrderIds.getOrder(criteria.getOrder()), 0, criteria.getCount(), criteria.getFirst());
  }

  public int findCount(SqlSession session, ItemForm criteria) {
    return sqlFactory.getCheckedQueryEngine("ITEMS").queryCount(session, criteria);
  }

  @Required
  public void setSqlFactory(SqlEngineFactory sqlFactory) {
    this.sqlFactory = sqlFactory;
  }
}

Instead of Hibernate SessionFactory the Spring JdbcTemplate is used. The sqlFactory is injected into this DAO using the Spring DI in applicationContext-business.xml:

...
<bean id="dao" autowire="byType" abstract="true">
    <property name="sqlFactory" ref="sqlFactory" />
</bean>

<bean id="itemDao" class="org.sqlproc.sample.catalog.dao.ItemDao" parent="dao" />

<bean id="sqlFactory" class="org.sqlproc.engine.jdbc.JdbcEngineFactory">
    <property name="metaFilesNames">
        <list>
            <value>statements.qry</value>
        </list>
    </property>
    <property name="filter" value="${db.type}" />
    <property name="monitorFactory" ref="sqlMonitorFactory"/>
</bean>
...

That's it. No plumbing code to build the SQL query, to put data into the SQL prepared statement or to get the data from the query result set. Based on data in a search form the executed query can be

select
        it.itemid,
        it.productid,
        it.name,
        it.description,
        it.imageurl,
        it.imagethumburl,
        it.image,
        it.imagethumb,
        it.price 
    from
        item it 
    where
        UPPER(it.name) like ?     
    order by
        name ASC

in the case you put cat into the attribute name. Or it can be

select
        it.itemid,
        it.productid,
        it.name,
        it.description,
        it.imageurl,
        it.imagethumburl,
        it.image,
        it.imagethumb,
        it.price 
    from
        item it 
    where
        UPPER(it.description) like ? 
        AND  it.price >= ?   
    order by
        name ASC

in the case you put cat into the attribute description and 100 into the attribute priceFrom. For the creation of a new item the next statement is generated

insert 
    into
        item
        (itemid, productid, name, description, imageurl, imagethumburl, image, imagethumb, price) 
    VALUES
        (?, ?, ?, ?, ?, ?, ?, ?, ?)