JPA - FindByExample
Solution 1
Actually, Query By Example (QBE) has been considered for inclusion in the JPA 2.0 specification but is not included, even if major vendors support it. Quoting Mike Keith:
I'm sorry to say that we didn't actually get to do QBE in JPA 2.0. Criteria API does not have any special operators for it so entity equality is just like in JP QL, based on PK value. Sorry, but hopefully we'll be more successful on that front in the next go-round. For now it is one of those vendor features that every vendor supports, but is not in the spec yet.
Just in case, I've added (non generic) sample code for the major vendors below for documentation purposes.
EclipseLink
Here is a sample of using QBE in the EclipseLink JPA 2.0 reference implementation:
// Create a native EclipseLink query using QBE policy
QueryByExamplePolicy policy = new QueryByExamplePolicy();
policy.excludeDefaultPrimitiveValues();
ReadObjectQuery q = new ReadObjectQuery(sampleEmployee, policy);
// Wrap the native query in a standard JPA Query and execute it
Query query = JpaHelper.createQuery(q, em);
return query.getSingleResult();
OpenJPA
OpenJPA supports this style of query through its extended OpenJPAQueryBuilder
interface:
CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
Employee example = new Employee();
example.setSalary(10000);
example.setRating(1);
q.where(cb.qbe(q.from(Employee.class), example);
Hibernate
And with Hibernate's Criteria API:
// get the native hibernate session
Session session = (Session) getEntityManager().getDelegate();
// create an example from our customer, exclude all zero valued numeric properties
Example customerExample = Example.create(customer).excludeZeroes();
// create criteria based on the customer example
Criteria criteria = session.createCriteria(Customer.class).add(customerExample);
// perform the query
criteria.list();
Now, while it should be possible to implement something approaching in a vendor neutral way with JPA 2.0 Criteria API and reflection, I really wonder if it's worth the effort. I mean, if you make any of the above snippets generic and put the code in a DAO method, it would be quite easy to switch from one vendor to another if the need should arise. I agree it's not ideal, but still.
References
Solution 2
This is quite crude and i'm not convinced it's a good idea in the first place. But anyway, let's try to implement QBE with the JPA-2.0 criteria API.
Start with defining an interface Persistable:
public interface Persistable {
public <T extends Persistable> Class<T> getPersistableClass();
}
The getPersistableClass()
method is in there because the DAO will need the class, and i couldn't find a better way to say T.getClass()
later on. Your model classes will implement Persistable
:
public class Foo implements Persistable {
private String name;
private Integer payload;
@SuppressWarnings("unchecked")
@Override
public <T extends Persistable> Class<T> getPersistableClass() {
return (Class<T>) getClass();
}
}
Then your DAO can have a findByExample(Persistable example)
method (EDITED):
public class CustomDao {
@PersistenceContext
private EntityManager em;
public <T extends Persistable> List<T> findByExample(T example) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, SecurityException, NoSuchMethodException {
Class<T> clazz = example.getPersistableClass();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(clazz);
Root<T> r = cq.from(clazz);
Predicate p = cb.conjunction();
Metamodel mm = em.getMetamodel();
EntityType<T> et = mm.entity(clazz);
Set<Attribute<? super T, ?>> attrs = et.getAttributes();
for (Attribute<? super T, ?> a: attrs) {
String name = a.getName();
String javaName = a.getJavaMember().getName();
String getter = "get" + javaName.substring(0,1).toUpperCase() + javaName.substring(1);
Method m = cl.getMethod(getter, (Class<?>[]) null);
if (m.invoke(example, (Object[]) null) != null)
p = cb.and(p, cb.equal(r.get(name), m.invoke(example, (Object[]) null)));
}
cq.select(r).where(p);
TypedQuery<T> query = em.createQuery(cq);
return query.getResultList();
}
This is quite ugly. It assumes getter methods can be derived from field names (this is probably safe, as example should be a Java Bean), does string manipulation in the loop, and might throw a bunch of exceptions. Most of the clunkiness in this method revolves around the fact that we're reinventing the wheel. Maybe there's a better way to reinvent the wheel, but maybe that's where we should concede defeat and resort to one of the methods listed by Pascal above. For Hibernate, this would simplify the Interface to:
public interface Persistable {}
and the DAO method loses almost all of its weight and clunkiness:
@SuppressWarnings("unchecked")
public <T extends Persistable> List<T> findByExample(T example) {
Session session = (Session) em.getDelegate();
Example ex = Example.create(example);
Criteria c = session.createCriteria(example.getClass()).add(ex);
return c.list();
}
EDIT: Then the following test should succeed:
@Test
@Transactional
public void testFindFoo() {
em.persist(new Foo("one",1));
em.persist(new Foo("two",2));
Foo foo = new Foo();
foo.setName("one");
List<Foo> l = dao.findByExample(foo);
Assert.assertNotNull(l);
Assert.assertEquals(1, l.size());
Foo bar = l.get(0);
Assert.assertNotNull(bar);
Assert.assertEquals(Integer.valueOf(1), bar.getPayload());
}
Solution 3
You should check the solution proposed by Springfuse using Spring Data & JPA 2.
http://www.springfuse.com/2012/01/31/query-by-example-spring-data-jpa.html
Some sample source code here (under repository sub package): https://github.com/jaxio/generated-projects
Found this project: https://github.com/jaxio/jpa-query-by-example
Solution 4
https://github.com/superbiger/sbiger-jpa-qbe
I'think query by example with single table like mybatis is easy to use
base on jpa we can also support Join/GroupBy like this:
/*
SQL:
select * from
user
where
id=1
or id=2
group by
id,
name
order by
id asc,
name asc
limit ?
*/
public List<User> findAll(){
Example<User> example = ExampleBuilder.create();
example.or()
.andEqual("id", 1)
.orEqual("id", 2);
example.groupBy("id","name");
example.asc("id","name");
return userReponsitory.findAll(example, new PageRequest(0, 1));
}
Features now:
- Support and/or logic operation
- Support is(Empty/Boolean/Null)
- Support Equal/NotEqual/In/NotIn/Like/NotLike
- Support gt/ge/lt/le/between
- Support join query
- Support group by
- Support custom specification.
- Support pagination
more features coming soon……
Comments
-
Dave over 3 years
Does anyone have a good example for how to do a findByExample in JPA that will work within a generic DAO via reflection for any entity type? I know I can do it via my provider (Hibernate), but I don't want to break with neutrality...
Seems like the criteria API might be the way to go....but I am not sure how to handle the reflection part of it.