如何在jpa查询上传递参数？

问题描述

我有两个实体，如 SmsOut 和 SmsIn 。两个实体之间的关系包含 OneToMany ，其中 smsIn.id 是主键， smsOut.sms_in_id 是外键。

I have two entities Like SmsOut and SmsIn. The relation between two entities contains OneToMany where smsIn.id is primary key and smsOut.sms_in_id is foreign key.

现在我想传递像 smsIn.mobileNumber ， smsIn.smsText 这样的参数依此类推，在查询上

Now I want to pass parameter like smsIn.mobileNumber, smsIn.smsText and so on, on the query

SELECT so FROM SmsOut so order by id desc

以下是我的数据库图表：

已编辑

以下是我的代码：

String sql = "SELECT so FROM SmsOut so WHERE so.smsInId.mobileNumber =:mobileNumber AND so.smsInId.smsText =:smsText AND so.smsInId.shortCode =:shortCode between so.smsOutDate =:startDate and so.smsOutDate =:endDate order by id desc";
    Query query = em.createQuery(sql);
    query.setParameter("mobileNumber", mobileNumber);
    query.setParameter("smsText", smsText);
    query.setParameter("shortCode", shortCode);
    query.setParameter("smsOutDate", startDate);
    query.setParameter("smsOutDate", endDate);
    smsOutList = query.getResultList();

且例外是：

SEVERE: line 1:188: expecting "and", found '='
java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: expecting "and", found '=' near line 1, column 188 [SELECT so FROM     com.f1soft.SMSC.entities.SmsOut so WHERE so.smsInId.mobileNumber =:mobileNumber AND so.smsInId.smsText =:smsText AND so.smsInId.shortCode =:shortCode between so.smsOutDate =:startDate and so.smsOutDate =:endDate order by id desc]
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:624)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:96)

请帮助我。
谢谢

Please Help me.Thanks

推荐答案

您还没有解释 SmsIn 和 SmsOut ，所以我假设 SmsOut 有一个 getSmsIn（）与 id 字段有关系。

You haven't explained the JPA relationship between SmsIn and SmsOut, so I'll assume SmsOut has a getSmsIn() with a relation on the id field.

当你有一个 EntityManager em ，你可以调用 em.createQuery ，这就像SQL prepare ，然后 setParameter ：

When you have an EntityManager em, you can call em.createQuery, which is like SQL prepare, and then setParameter:

TypedQuery<SmsOut> q = em.createQuery("SELECT so FROM SmsOut so WHERE so.smsIn.mobileNumber = :number ORDER BY id DESC");
q.setParameter("number", "12345678");
List<SmsOut> results = q.getResultList();

参见，了解指定参数的不同方法。

See the Javadoc for Query for the different ways you can specify the parameters.

这篇关于如何在jpa查询上传递参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！