Catalog (JPA) Sample - hudec/sql-processor GitHub Wiki
The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.
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+Hibernate+Maven+Jetty. In this version there's used Oracle or HSQLDB as a database. It's a simple web application, which enable you do all CRUD operations on the database table ITEM using the JPA Hibernate. 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 coexistence of the Hibernate ORM and the SQL Processor.
-
checkout the code
git clone https://github.com/hudec/sql-processor.git -
compile the SQL Processor samples
cd sql-processor/sql-samples
mvn clean install -
run web application
cd catalog-jpa/web
mvn jetty:run-war -
access the web application on the URL http://localhost:8080/catalog
-
checkout the code
git clone https://github.com/hudec/sql-processor.gitcd sql-processor/sql-samples -
install Oracle XE, a good tutorial for Ubuntu you can find at http://mediakey.dk/~cc/ubuntu-howto-install-oracle/
-
create the user catalog/catalog123 with the appropriate privileges using Oracle XE admin web pages
-
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.jaris used. To install this driver into the Maven repository you can usemvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=ojdbc6.jar -
create and seed the table
ITEMcd catalog-jpa
you can use the SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. Load the next SQL script database/catalog.sql
-
use the Groovy script
database/img2db.groovyto import the images into db. The images are located in directorybusiness/src/main/resources/images -
compile the Catalog Sample with the profile Oracle
mvn clean install -P oracle -
run web application
cd web
mvn jetty:run-war -
access web application on the URL http://localhost:8080/catalog
The Meta SQL query is defined in the meta statements file statements.meta:
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}
;
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(Session sqlSession, Serializable id) {
Item entity = (Item) sqlSession.get(Item.class, id);
return entity;
}
public Item store(Session sqlSession, Item entity) {
entity = (Item) sqlSession.merge(entity);
sqlSession.flush();
return entity;
}
public void remove(Session sqlSession, Item entity) {
sqlSession.delete(entity);
sqlSession.flush();
}
public List<ItemTO> find(SqlSession sqlSession, ItemForm criteria) {
return sqlFactory.getCheckedQueryEngine("ITEMS").query(sqlSession, ItemTO.class, criteria, null,
OrderIds.getOrder(criteria.getOrder()), 0, criteria.getCount(), criteria.getFirst());
}
public int findCount(SqlSession sqlSession, ItemForm criteria) {
return sqlFactory.getCheckedQueryEngine("ITEMS").queryCount(sqlSession, criteria);
}
@Required
public void setSqlFactory(SqlEngineFactory sqlFactory) {
this.sqlFactory = sqlFactory;
}
}
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.hibernate.HibernateEngineFactory">
<property name="metaFilesNames">
<list>
<value>statements.meta</value>
</list>
</property>
<property name="filter" value="${db.type}" />
</bean>
...
That's it. No plumbing code to build 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.