Java JPA - FindByExample

JPA - FindByExample

提问by HDave

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

有没有人有一个很好的例子来说明如何在 JPA 中做一个 findByExample ，它可以通过任何实体类型的反射在通用 DAO 中工作？我知道我可以通过我的提供者（休眠）来做到这一点，但我不想打破中立......

Seems like the criteria API might be the way to go....but I am not sure how to handle the reflection part of it.

似乎标准 API 可能是要走的路......但我不确定如何处理它的反射部分。

回答by wallenborn

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:

Criteria API 是您最好的选择。不过，您需要一个 JPA-2.0 提供程序。因此，如果您有这样的实体：

@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):

以下单元测试应该会成功（我使用 EclipseLink 对其进行了测试，但它应该适用于任何 JPA-2.0 提供程序）：

@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.

此外，您可能希望点击此处引用的教程链接。

回答by Pascal Thivent

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:

实际上，Query By Example (QBE) 已被考虑包含在 JPA 2.0 规范中，但并未包含在内，即使主要供应商支持它。引用 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.

很抱歉，我们实际上并没有在 JPA 2.0 中进行 QBE。Criteria API 没有任何特殊的运算符，因此实体相等性就像在 JP QL 中一样，基于 PK 值。抱歉，但希望我们在下一轮比赛中能在这方面取得更大的成功。目前，它是每个供应商都支持的供应商功能之一，但尚未在规范中。

Just in case, I've added (non generic) sample code for the major vendors below for documentation purposes.

以防万一，为了文档目的，我为下面的主要供应商添加了（非通用）示例代码。

EclipseLink

Eclipse链接

Here is a sample of using QBE in the EclipseLink JPA 2.0 reference implementation:

以下是在 EclipseLink JPA 2.0 参考实现中使用 QBE 的示例：

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

开放式JPA

OpenJPA supports this style of query through its extended OpenJPAQueryBuilderinterface:

OpenJPA 通过其扩展OpenJPAQueryBuilder接口支持这种查询方式：

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:

使用 Hibernate 的 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.

现在，虽然应该可以使用 JPA 2.0 Criteria API 和反射以供应商中立的方式实现一些接近的东西，但我真的想知道这是否值得付出努力。我的意思是，如果您使上述任何片段通用并将代码放在 DAO 方法中，那么在需要时很容易从一个供应商切换到另一个供应商。我同意这并不理想，但仍然如此。

References

参考

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

• JPA 书中的 findByExample 怎么样？
• JPA 2.0 中的动态类型安全查询

回答by wallenborn

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.

这很粗糙，我不相信它首先是一个好主意。但无论如何，让我们尝试使用 JPA-2.0 标准 API 来实现 QBE。

Start with defining an interface Persistable:

首先定义一个 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:

该getPersistableClass()方法在那里，因为 DAO 将需要该类，我找不到更好的方法来说明T.getClass()。您的模型类将实现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):

然后你的 DAO 可以有一个findByExample(Persistable example)方法（已编辑）：

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:

这是相当丑陋的。它假设 getter 方法可以从字段名称派生（这可能是安全的，例如应该是 Java Bean），在循环中执行字符串操作，并且可能会抛出一堆异常。这种方法的大部分笨拙都围绕着我们正在重新发明轮子这一事实。也许有更好的方法来重新发明轮子，但也许这就是我们应该承认失败并求助于上面 Pascal 列出的方法之一的地方。对于 Hibernate，这会将接口简化为：

public interface Persistable {}

and the DAO method loses almost all of its weight and clunkiness:

而 DAO 方法几乎失去了所有的重量和笨拙性：

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

回答by Nicolas Romanetti

You should check the solution proposed by Springfuse using Spring Data & JPA 2.

您应该使用 Spring Data & JPA 2 检查 Springfuse 提出的解决方案。

http://www.springfuse.com/2012/01/31/query-by-example-spring-data-jpa.html

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

这里的一些示例源代码（在存储库子包下）：https: //github.com/jaxio/generated-projects

Found this project: https://github.com/jaxio/jpa-query-by-example

找到这个项目：https: //github.com/jaxio/jpa-query-by-example

回答by xiaod0510

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

你可以使用这个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

该示例实现了接口“规范”，有关该 github 的更多信息

回答by superbiger

https://github.com/superbiger/sbiger-jpa-qbe

https://github.com/superbiger/sbiger-jpa-qbe

I'think query by example with single table like mybatis is easy to use

我认为像mybatis这样的单表示例查询很容易使用

base on jpa we can also support Join/GroupBy like this:

基于 jpa，我们还可以像这样支持 Join/GroupBy：

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

• 支持和/或逻辑操作
• 支持是（空/布尔/空）
• 支持 Equal/NotEqual/In/NotIn/Like/NotLike
• 支持gt/ge/lt/le/之间
• 支持join查询
• 支持小组
• 支持自定义规格。
• 支持分页
  更多功能即将推出……

回答by Moody Ibrahim Moody

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

也许答案为时已晚。但是检查这个。它可能有帮助。

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

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:

首先，将 jar 包含到类路径中。您将拥有一个名为com.afifi.simpleJPAQuery.entities.utility.JPAUtil. 该类使用反射从 bean 中扣除查询。假设您有一个实体 bean，如下所示：

    @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".

结果将获得人名中包含单词“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

getResultCount: 得到结果的计数

createSqlStatement: to get the sql statement that is being used

createSqlStatement: 获取正在使用的sql语句

getSqlWhereString: to get just the where string used

getSqlWhereString: 只获取使用的字符串

It has the native forms of these functions:

它具有这些函数的原生形式：

findByExampleNative, getResultCountNative, createSqlStatementNativeand getSqlWhereStringNative

findByExampleNative, getResultCountNative,createSqlStatementNative和getSqlWhereStringNative

The library also has QueryAnnotationsclass 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.

该库还有QueryAnnotations包含注释的类，这些注释可以添加到实体 bean 属性中，以便更好地控制如何使用 bean 进行查询。