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 havent explained the JPA relationship between SmsIn and SmsOut, so Ill 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.

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 havent explained the JPA relationship between SmsIn and SmsOut, so Ill 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.