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

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.

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

Following is my database diagram:

Edited

Following is my code :

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

and exception is :

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

解决方案

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.

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

TypedQuery q = em.createQuery("SELECT so FROM SmsOut so WHERE so.smsIn.mobileNumber = :number ORDER BY id DESC");

q.setParameter("number", "12345678");

List results = q.getResultList();

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