CODEWITHSUNDEEP
jpa
Tom
Tom

Reputation: 2847

How to build jpql query which the search condition is multiple attributes of an object?

I have a problem to query database by JPA. Got exception:

org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1
    at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:80)
    at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:86)
    at org.hibernate.internal.AbstractQueryImpl.determineType(AbstractQueryImpl.java:444)
    at org.hibernate.internal.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:416)
    at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:440)
    at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:67)
    at com.lawa.service.impl.SubjectServiceImpl.getTotal(SubjectServiceImpl.java:746)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)

My code is :

@Transactional(propagation=Propagation.REQUIRED)
    public long getTotal(PSubject search) {
        StringBuilder jsql = new StringBuilder("select count(*) from PSubject p where p.id=p.id ");
        ArrayList paramValues  = new ArrayList();
        if(search.getName()!=null && search.getName().trim().length()>0) {
            jsql.append(" and p.name=:name");
            paramValues.add(search.getName().trim());
        }
        if(search.getType()!=null && search.getType().trim().length()>0) {
            jsql.append(" and p.type=:type");
            paramValues.add(search.getType().trim());
        }
        if(search.getMacaddress()!=null && search.getMacaddress().trim().length()>0) {
            jsql.append(" and p.macaddress=:macaddress");
            paramValues.add(search.getMacaddress().trim());
        }
        if(search.getUri()!=null && search.getUri().trim().length()>0) {
            jsql.append(" and p.uri=:uri");
            paramValues.add(search.getUri().trim());
        }
        if(search.getDescription()!=null && search.getDescription().trim().length()>0) {
            jsql.append(" and p.description=:description");
            paramValues.add(search.getDescription().trim());
        }
        if(search.getCreateTime()!=null) {
            jsql.append(" and p.createTime=:createTime");
        }
        Query query = persistService.getEntityManager().createQuery(jsql.toString());
        for(int i=0;i<paramValues.size();i++) {
            query.setParameter((i+1), paramValues.get(i));
        }
        if(search.getCreateTime()!=null) {
            query.setParameter(":createTime",search.getCreateTime(),TemporalType.DATE);
        }
        Long count = (Long)query.getSingleResult();
        return count;
    }

First I want make the code work, and I don't like the code. I feel it's not clean, I have to validate every attribute of "PSubject" one by one and contact the jpql, and I have to pick "createTime" out because its type is "Date". Even more, I add "p.id=p.id" to the jpql,which makes me feel not good.

Please help me to resolve the exception and give a best practice.

Upvotes: 0

Views: 1020

Answers (2)

Doron Manor
Doron Manor

Reputation: 596

You should use Criteria instead of Query. It's perfect for this use case.

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 691865

If you use named parameters, you should bind them by name:

query.setParameter("macaddress", theMacAddress);

So you should rather use a Map to hold your parameter values, indexed by their name.

But this kind of dynamic query is exactly why the JPA criteria API has been designed. You should use it.

Upvotes: 1

Related Questions