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