How to pass parameter on jpa query?

java, jpa-2.0, sql-server

Solution

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

Problem

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

Original source