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

Cafeto at Maven Central

Thanks to people of Sonatype all the utilities of cafeto.net will be in Maven Central http://repo2.maven.org/maven2/net/cafeto/.

The people that use the cafeto-entitycopy utility can be use the follow maven dependency.

<dependecy>
  <groupId>net.cafeto.entitycopy</groupId>
  <artifactId>cafeto-entitycopy</artifactId>
  <version>5.0.3</version>
<dependency>

EntityCopy a solution to LazyInitializationException

Although there are different solutions for LazyInitializationException exception of Hibernate, as DTO, Dozer or Gilead, but for me these solutions have some disadvantages such as adding new classes, you can not set the depth level of the copy in real time or require special configuration.

In several projects I needed a utility easy to use and that can be used by other programming languages like PHP, AS3, ..etc.

Scenario:

• A EJB3 container-managed transaction and persistence and the entities configured with JPA annotations (JBoss5.1 and Hibernate).
• Different client applications in Java and PHP, connected to the container by WebServices.

EntityCopy lets you copy an entity with JPA annotations by reflection and depth level set in real time. EntityCopy has the following levels of copy of an entity:

• NONE: Copy only the properties that are not associations.
• DEFAULT: Copy only the associations with FetchType.EAGER.
• FULL: Copy all the properties of the entity.

For example if we have the following entities:

@Entity
public class Address {
    @Id
    String id;
    String street;
    ...
}

@Entity
public class Person {
    @Id
    String id;
    String firstName;
    String lastName;
    @ManyToOne   // By default FetchType.EAGER 
    Person parent;
    @OneToMany   // By default FetchType.LAZY
    Set<Address> addresses = new HashTable<Address>();
    ..
}

To make a copy of Person with associaiones configured with FetchType.EAGER (only the "parent" association):

Person entity = personDao.findByPk(pk);
...
Person copy = EntityCopy.copy(Person entity, DeepCopy.DEFAULT);

If the second parameter is null, DeepCopyType.DEFAULT always be taken.

You can change the level of copy of a property defining it directly, for example, if you want a copy of Person but not the addresses list:

DeepCopy deepCopy = DeepCopy.createFull().add("addresses", DeepCopy.createNone());
Person copy = EntityCopy.copy(entity, deepCopy);

When DeepCopyType.NONE is assigned to a property, this property will not be copied.

EntityCopy avoids recursion between entities, to prevent infinite loops.

The following is a example of how to use EntityCopy with a web service.

TestServiceSEI.java

@WebService
@SOAPBinding(style = SOAPBinding.Style.DOCUMENT)
public interface TestServiceSEI {
    @WebMethod
    public List<Person> findAllPersons(@javax.jws.WebParam(name="deepCopy")  DeepCopy deepCopy);
}

TestServiceBean.java

@Stateless
@WebService(endpointInterface="TestServiceSEI")
@Remote(TestServiceSEI.class)
public class TestServiceBean  implements TestServiceSEI {
    @javax.persistence.PersistenceContext(unitName = "TestPU")
    protected javax.persistence.EntityManager entityManager;

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

    public List<Person> findAllPersons(DeepCopy deepCopy) {
            Query query = this.getEntityManager().createQuery("select obj from Person obj");
            List<Person> entities = query.getResultList();
            if (wsCtx != null) {
                List<Person> results = new ArrayList<Person>();
                for (Person entity : entities) {
                    results.add(EntityCopy.copy(entity, deepCopy));
                }
                return results;
            }
            return entities;
    }
}

From a PHP client application:

test.php

<?php
require_once "Cafeto/DeepCopy.php";

class TestService {

    protected $soapClient;

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

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

$service = new TestService("http://127.0.0.1:8080/test-service/TestService?wsdl");

$deepCopy = Cafeto_DeepCopy::createFull()->add("addresses", Cafeto_DeepCopy::createNone());

$persons = $service->findAllPersons($deepCopy);

You can download the jar file cafeto-entitycopy-0.5.0.jar and the PHP library cafeto-deepcopy.zip.

The source code repository is here.

You can also clone the project with Git by running:

$ git clone git://github.com/cafeto/cafeto-entitycopy