JPA - FindByExample

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开发者_JAVA技巧 go....but I am not sure how to handle the reflection part of it.

---

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

• What about findByExample in JPA book?
• Dynamic, typesafe queries in JPA 2.0

---

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());      
}

---

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

---

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……

---

Criteria API is your best bet. You'll need a JPA-2.0 provider for that, though. So if you have an entity like this:

@Entity
public class Foo {
    @Size(max = 20)
    private String name;
}

The following unit test should succeed (i tested it with EclipseLink, but it should work with any of the JPA-2.0 providers):

@PersistenceContext
private EntityManager em;

@Test
@Transactional
public void testFoo(){
    Foo foo = new Foo();
    foo.setName("one");
    em.persist(foo);
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Foo> c = cb.createQuery(Foo.class);
    Root<Foo> f = c.from(Foo.class);
    c.select(f).where(cb.equal(f.get("name"), "one"));
    TypedQuery<Foo> query = em.createQuery(c);
    Foo bar = query.getSingleResult();
    Assert.assertEquals("one", bar.getName());
}

Also, you might want to follow the link to the tutorial referenced here.

---

you can use this https://github.com/xiaod0510/jpa-findbyexample

if your entity is Contact:

@Entity
public class Contact {
    @Id
    @GeneratedValue
    private Long id;
    @Column
    private String name;
    @Column
    private Date birthday;
    //Getter and Setter
}
public interface ContactRepository
        extends
        JpaSpecificationExecutor<Contact> {
}

just create your own Example like this:

public class ContactExample extends BaseExample<ContactExample, Contact> {
    public final Attr<Long> id = new Attr<Long>("id");
    public final Attr<String> name = new Attr<String>("name");
    public final Attr<Date> birthday = new Attr<Date>("birthday");
    //default builder  
    public static ContactExample where() {
        ContactExample example = new ContactExample();
        example.operatorType = OperatorType.and;
        return example;
    }
}

and now you can query by example :

 ContactRepository.findOne(ContactExample
                    .where()//default is and
                    .id.eq(1l)
);

the example implements the interface "Specification",more information on that github

---

Maybe the answer is too late. But check this. It might be of help.

https://sourceforge.net/projects/simplejpaquery/

First, include the jar into the classpath. You will have a class called com.afifi.simpleJPAQuery.entities.utility.JPAUtil. This class uses reflection to deduct the query from the bean. Suppose you have an entity bean as follows:

    @Entity
    public class Person {
        @Id
        private Integer personNo;

        private String personName;

        public Integer getPersonNo() {
            return personNo;
        }

        public void setPersonNo(Integer personNo) {
            this.personNo = personNo;
        }

        public String getPersonName() {
            return personName;
        }

        public void setPersonName(String personName) {
            this.personName = personName;
        }
    }

Then if you want to query by person name for instance, you need to do as follows:

    //initiate entity manager (em)
    Person p=new Person();
    p.setPersonName("John");
    String sortString="";
    List<Person> result= JPAUtil.findByExample(em,p,sortString);

The result will get all the records where the person name contained the word "John".

if you want to limit the results, you can do something like:

    List<Person> result= JPAUtil.findByExample(em, p, sortString, start, size);

This library has other methods like:

getResultCount: to get the count of the result

createSqlStatement: to get the sql statement that is being used

getSqlWhereString: to get just the where string used

It has the native forms of these functions:

findByExampleNative, getResultCountNative, createSqlStatementNative and getSqlWhereStringNative

The library also has QueryAnnotations class that contains annotations that can be added to the Entity bean properties to give more control on how you want to query using the bean.