Thursday, July 8, 2010

Serializing JP-QL Queries with QuerySerialize

Like the library EntityCopy, QuerySerialize facilitates the integration between PHP and Java, permitting the creation of JP-QL queries in PHP and be to executed in a JEE server. This library is based on the new Criteria API of JPA2.

This library have APIs for Java and PHP, the Java APIs can be used on the client or the server side and the PHP APIs is used on the client side.

The PHP bundle can be downloaded here.

The Java library can be configured with maven by adding the following dependencies from Maven Central:


    net.cafeto.queryserialize
    cafeto-queryserialize
    0.5.0


    javax.ejb
    ejb-api
    3.0
    provided


    javax.persistence
    persistence-api
    1.0
    provided

    
    javax.jws
    jsr181-api
    1.0-MR1
    provided


    javax.xml.ws
    jaxws-api
    2.0
    provided


To use the library only has to add the following line in a PHP file and instantiate the class Cafeto_QuerySerialize.

require_once "Cafeto/QuerySerialize.php";
$qs = new Cafeto_QuerySerialize();

The Cafeto_QuerySerialize_CriteriaBuilder class allows us to create different types of objects that we could use to construct the search criteria, its operation is similar to JPA2 CriteriaBuilder class. You can use the "getCriteriaBuilder" method of QuerySerialize to get a instance of Cafeto_QuerySerialize_CriteriaBuilder.

$cb = $qs->getCriteriaBuilder();

List of methods of Cafeto_QuerySerialize_CriteriaBuilder class.

function andOp($expr0, $expr1)
function orOp($expr0, $expr1)
function disjunction()
function conjunction()
function not($expr)

function sum($expr0, $expr1 = null)
function diff($expr0, $expr1)
function prod($expr0, $expr1)
function qout($expr0, $expr1)

function eq($expr0, $expr1)
function ne($expr0, $expr1)
function gt($expr0, $expr1)
function lt($expr0, $expr1)
function ge($expr0, $expr1)
function le($expr0, $expr1)
function like($expr0, $expr1)
function notLike($expr0, $expr1)
function between($expr0, $expr1, $expr2)
function notBetween($expr0, $expr1, $expr2)
function isNull($expr)
function isNotNull($expr)
function isEmpty($expr)
function isNotEmpty($expr)
function isMember($expr0, $expr1)
function isNotMember($expr0, $expr1)
function isTrue($expr)
function isNotTrue($expr)
function isFalse($expr)
function isNotFalse($expr)
function in($expr)
function notIn($expr)

function exists($subquery)
function notExists($subquery)
function all($subquery)
function any($subquery)
function some($subquery)

function count($expr)
function countDistinct($expr)
function max($expr)
function min($expr)
function avg($expr)
function concat($expr0, $expr1)
function substring($expr0, $expr1, $expr2)
function trim($expr, $type = "BOTH", $char = " ")
function lower($expr)
function upper($expr)
function length($expr)
function locate($expr0, $expr1, $expr2 = null)
function abs($expr)
function sqrt($expr)
function mod($expr)
function size($expr)

function currentDate()
function currentTime()
function currentTimestamp()

function stringLit($object)
function integerLit($object)
function longLit($object)
function booleanLit($object)
function floatLit($object)
function doubleLit($object)
function dateLit($object)
function dateTimeLit($object)
function timeLit($object)

function path($path)
function parameter($parameter)
function domain($name, $alias, $collection = false)

function inner($path, $alias)
function left($path, $alias)
function fetch($path)
function leftFetch($path)

function asc($expr)
function desc($expr)

function stringVar($object)
function integerVar($object)
function longVar($object)
function booleanVar($object)
function floatVar($object)
function doubleVar($object)
function dateVar($object)
function dateTimeVar($object)
function timeVar($object)

To create a search criteria, you can use the "createQuery" method of CriteriaBuild. This object is instance of Cafeto_QuerySerialize_CriteriaQuery class, its operation is similar to JPA2 CriteriaQuery class. The netx line allows us to create a search criteria on the entity "Person" aliased "obj."

$criteria = $cb->createQuery($cb->domain("Person", "obj"));
$qs->criteria = $criteria;

The main feature of CriteriaQuery is be able define a search filter.

$where = $cb->andOp(
    $cb->eq($cb->path("obj.age"), $cb->parameter("age")), 
    $cb->like($cb->lower($cb->path("obj.lastName")), $cb->parameter("lastName"))
);
$criteria->where = $where;

As you can see the parameter of function "path()" is the path of de property with the entity alias, and in the function "parameter()" is the parameter name.

You can also define the order of the search results:

$criteria->orders[] = $cb->desc($cb->path("obj.firstName"));
$criteria->orders[] = $cb->asc($cb->path("obj.lastName"));

If parameters have been used in the criteria they should be initialized as follows:

$qs->addParameter("id", $cb->integerVar(7));
$qs->addParameter("lastName", $cb->stringVar("s%"));

QuerySerialize execute the following query on the server:

SELECT obj 
FROM Person AS obj 
WHERE obj.age = :age AND LOWER(obj.lastName) LIKE :lastName 
ORDER BY obj.firstName DESC, obj.lastName ASC

This JP-QL query is executed in java as follows:

QuerySerialize qs = ...
List<person> persons = (List<person>) qs.getResultList(entityManager);

You can also get the number of query results.

Long numRows = qs.getCountResult(entityManager);

This change the JP-QL as follows

SELECT COUNT(obj) 
FROM Person AS obj 
WHERE obj.age = :age AND LOWER(obj.lastName) LIKE :lastName 

Example


The following example is tested on a Linux Fedora Core 13, deployed in a JBoss server 5.1 and a PostgreSQL 8.4 database, the client side requires PHP 5.2 with the SOAP module active, the source code could be downloaded from here.

We will create a EJB3 entity called "Person" with basic data and in PHP send a JP-QL query for get the entity data.

Server side:

Database

CREATE TABLE person (  
id  TEXT,  
firstname TEXT,  
lastname TEXT,  
age  INTEGER,  
enabled  BOOLEAN NOT NULL DEFAULT 'false',  
createdate TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,  CONSTRAINT person_pk PRIMARY KEY (id) 
); 

INSERT INTO person VALUES ('be1be6b5-2587-415a-858e-8638cd79f87d', 'Peter', 'Griffin', 40, 't', '2010-01-01'); 
INSERT INTO person VALUES ('31a97c27-9f1a-45e3-ab9c-e1eebb0aa8ad', 'Louis', 'Griffin', 35, 't', '2010-01-02'); 
INSERT INTO person VALUES ('bfddd7ae-1ca0-46c2-8cc5-c183caacd0d4', 'Stewie', 'Griffin', 2, 'f', '2010-01-03'); 
INSERT INTO person VALUES ('f43c57c8-3713-4a27-8c71-030749da3a31', 'Homer', 'Simpson', 38, 't', '2010-01-04'); 
INSERT INTO person VALUES ('e41aa0f4-0fdd-44ac-8e37-bf80dfda8a2e', 'Marge', 'Simpson', 36, 'f', '2010-01-05'); 
INSERT INTO person VALUES ('ef2feb21-edf5-4ace-8ffa-d603609a082d', 'Bart', 'Simpson', 7, 't', '2010-01-06');

Create the file pesistence.xml


 
  java:/CafetoTestDS
  
   
  
 



We create the entity "Person"

@Entity
public class Person implements Serializable {

 private static final long serialVersionUID = -2036658445596637963L;

 @Id
 private String id;
 private String firstName;
 private String lastName;
 private Integer age;
 private Boolean enabled = false;
 private Date createDate = new Date();

 /**
  * 
  */
 public Person() {
 }

 /** (non-Javadoc)
  * @see java.lang.Object#hashCode()
  */
 @Override
 public int hashCode() {
  final int prime = 31;
  int result = 1;
  result = prime * result + ((firstName == null) ? 0 : firstName.hashCode());
  result = prime * result + ((id == null) ? 0 : id.hashCode());
  result = prime * result + ((lastName == null) ? 0 : lastName.hashCode());
  result = prime * result + ((age == null) ? 0 : age.hashCode());
  result = prime * result + ((enabled == null) ? 0 : enabled.hashCode());
  result = prime * result + ((createDate == null) ? 0 : createDate.hashCode());
  return result;
 }

 /** (non-Javadoc)
  * @see java.lang.Object#equals(java.lang.Object)
  */
 @Override
 public boolean equals(Object obj) {
  if (this == obj)
   return true;
  if (obj == null)
   return false;
  if (getClass() != obj.getClass())
   return false;
  Person other = (Person) obj;
  if (firstName == null) {
   if (other.firstName != null)
    return false;
  } else if (!firstName.equals(other.firstName))
   return false;
  if (id == null) {
   if (other.id != null)
    return false;
  } else if (!id.equals(other.id))
   return false;
  if (lastName == null) {
   if (other.lastName != null)
    return false;
  } else if (!lastName.equals(other.lastName))
   return false;
  if (age == null) {
   if (other.age != null)
    return false;
  } else if (!age.equals(other.age))
   return false;
  if (enabled == null) {
   if (other.enabled != null)
    return false;
  } else if (!enabled.equals(other.enabled))
   return false;
  if (createDate == null) {
   if (other.createDate != null)
    return false;
  } else if (!createDate.equals(other.createDate))
   return false;
  return true;
 }

 /**
  * @return the id
  */
 public String getId() {
  return id;
 }

 /**
  * @param id the id to set
  */
 public void setId(String id) {
  this.id = id;
 }

 /**
  * @return the firstName
  */
 public String getFirstName() {
  return firstName;
 }

 /**
  * @param firstName the firstName to set
  */
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 /**
  * @return the lastName
  */
 public String getLastName() {
  return lastName;
 }

 /**
  * @param lastName the lastName to set
  */
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 /**
  * @return the age
  */
 public Integer getAge() {
  return age;
 }

 /**
  * @param age the age to set
  */
 public void setAge(Integer age) {
  this.age = age;
 }

 /**
  * @return the enabled
  */
 public Boolean getEnabled() {
  return enabled;
 }

 /**
  * @param enabled the enabled to set
  */
 public void setEnabled(Boolean enabled) {
  this.enabled = enabled;
 }

 /**
  * @return the createDate
  */
 public Date getCreateDate() {
  return createDate;
 }

 /**
  * @param createDate the createDate to set
  */
 public void setCreateDate(Date createDate) {
  this.createDate = createDate;
 }
}

Creamos la siguiente interface para el DAO y el WebService

@javax.jws.WebService(name="PersonDaoSEI", targetNamespace="http://person.queryserialize.test.cafeto.net/PersonDaoSEI")
@javax.jws.soap.SOAPBinding(style = javax.jws.soap.SOAPBinding.Style.DOCUMENT)
public interface PersonDao {

 public static final String NAMESPACE = "http://person.queryserialize.test.cafeto.net/PersonDaoSEI";
 public static final String SERVICE_NAME = "PersonDaoService";

 /**
  * @param querySerialize
  * @return
  */
 @javax.jws.WebMethod
 @javax.xml.ws.RequestWrapper(className="net.cafeto.test.queryserializer.person.FindByQueryRequest")
 @javax.xml.ws.ResponseWrapper(className="net.cafeto.test.queryserializer.person.FindByQueryResponse")
 public List<person> findByQuery(@javax.jws.WebParam(name = "qs") QuerySerialize qs);

 /**
  * @param querySerialize
  * @return
  */
 @javax.jws.WebMethod
 @javax.xml.ws.RequestWrapper(className="net.cafeto.test.queryserializer.person.CountByQueryRequest")
 @javax.xml.ws.ResponseWrapper(className="net.cafeto.test.queryserializer.person.CountByQueryResponse")
 public Long countByQuery(@javax.jws.WebParam(name = "qs") QuerySerialize qs);
}

We implement the WebService:

@javax.ejb.Stateless(name="PersonDao")
@javax.ejb.Local(net.cafeto.test.queryserializer.person.PersonDao.class)
@javax.jws.WebService(serviceName = "PersonDaoService", portName="PersonDaoServicePort", endpointInterface="net.cafeto.test.queryserializer.person.PersonDao", targetNamespace="http://person.queryserialize.test.cafeto.net/PersonDaoSEI")
@javax.ejb.TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRED)
public class PersonDaoImpl implements PersonDao {

 @javax.persistence.PersistenceContext(unitName = "CafetoTestPU")
 protected javax.persistence.EntityManager entityManager;

 @javax.annotation.Resource
 protected javax.xml.ws.WebServiceContext wsCtx;

 /**
  * 
  */
 public PersonDaoImpl() {
 }

 /** (non-Javadoc)
  * @see net.cafeto.test.queryserializer.person.PersonDao#findByQuery(net.cafeto.queryserialize.QuerySerialize)
  */
 @SuppressWarnings("unchecked")
 @Override
 public List<person> findByQuery(QuerySerialize qs) {
  //Override the root of the search criteria to limit it only to entities "Person"
  qs.getCriteria().getRoot().entity(Person.class);
  // Execute the JP-QL query
  List<person> persons = (List<person>) qs.getResultList(entityManager);
  // If the method is called from a Web Servie, it will make copies of entities.
  if (wsCtx != null) {
   List<person> list = new ArrayList<person>();
   for (Person person : persons) {
    try {
     list.add(EntityCopy.copy(person, qs.getDeepCopy()));
    } catch (InstantiationException e) {
     e.printStackTrace();
    } catch (IllegalAccessException e) {
     e.printStackTrace();
    }
   }
   return list;
  }
  return persons;
 }

 /** (non-Javadoc)
  * @see net.cafeto.test.queryserializer.person.PersonDao#countByQuery(net.cafeto.queryserialize.QuerySerialize)
  */
 @Override
 public Long countByQuery(QuerySerialize qs) {
  //Override the root of the search criteria to limit it only to entities "Person"
  qs.getCriteria().getRoot().entity(Person.class);
  // Execute the JP-QL query return the number of results
  return qs.getCountResult(entityManager);
 }

Client side

File PersonDao.php

<?php
class PersonDao {

  protected $soapClient;

  public function __construct($wsdl) {
    soapClient = new SoapClient($wsdl);
  }

 public function findByQuery($qs) {
  $ret = $this->soapClient->findByQuery(array('qs' => $qs));
  if (isset($ret->return))
   if (is_array($ret->return))
    return $ret->return;
   else
    return array($ret->return);
  else
   return array();
 }

 public function countByQuery($qs) {
  $ret = $this->soapClient->countByQuery(array('qs' => $qs));
  if (isset($ret->return))
   return $ret->return;
  else
   return 0;
 }
}

File Test.php

<?php

require_once "Cafeto/QuerySerialize.php";
require_once "PersonDao.php";

$qs = new Cafeto_QuerySerialize();
$cb = $qs->getCriteriaBuilder();

$criteria = $cb->createQuery($cb->domain("Person", "obj"));

$where = $cb->andOp(
    $cb->eq($cb->path("obj.age"), $cb->parameter("age")), 
    $cb->like($cb->lower($cb->path("obj.lastName")), $cb->parameter("lastName"))
);

$criteria->where = $where;
$criteria->orders[] = $cb->desc($cb->path("obj.firstName"));
$criteria->orders[] = $cb->asc($cb->path("obj.lastName"));

$qs->criteria = $criteria;
$qs->addParameter("age", $cb->integerVar(7));
$qs->addParameter("lastName", $cb->stringVar("s%"));

$service = new PersonDao("http://127.0.0.1:8080/cafeto-test-queryserialize-0.0.1-SNAPSHOT/PersonDao?wsdl");

$ret = $service->findByQuery($qs);
print_r("Data: " . $ret);

$ret = $service->countByQuery($qs);
print_r(“Count: ” . $ret);

The above script will display the following

Data: Array
(
    [0] => stdClass Object
        (
            [age] => 7
            [createDate] => 2010-01-06T00:00:00+01:00
            [enabled] => 1
            [firstName] => Bart
            [id] => ef2feb21-edf5-4ace-8ffa-d603609a082d
            [lastName] => Simpson
       )
)
Count: 1

No comments:

Post a Comment