多条件查询问题

Question

 public Object findInstVop(final ServiceInstanceVO voll,final int pageno,final int pageNumber) {
        // TODO Auto-generated method stub
        return this.getHibernateTemplate().execute(new HibernateCallback(){
            public Object doInHibernate(Session arg0) throws HibernateException, SQLException {
                // TODO Auto-generated method stub
                int index=0;
                String hsql="from ServiceInstanceVO where(1=1) ";
                StringBuffer buffer = new StringBuffer(hsql);
                if(voll.getServiceId()!=0)buffer.append(" and serviceId=?");                                         //审批事项
                if(voll.getService_orgId()!=0)buffer.append(" and service_orgId=?");                                 //部门
                if(voll.getService_declareTime()!= null)buffer.append(" and service_declareTime >= ?");            //申报开始时间
                if(voll.getService_endTime()!= null)buffer.append(" and service_declareTime<=? ");                //申报结束时间
                if(voll.getService_userId()!=0)buffer.append(" and service_userId=?");                             //创建人----受理人   service_userId
                if(voll.getService_state()!=0)buffer.append(" and service_state=?");                               //实例状态
                if(voll.getService_name()!=null && voll.getService_name().trim().length()!=0)buffer.append(" and service_name like ?"); //事项名称
                if(voll.getService_number()!=null && voll.getService_number().trim().length()!=0)buffer.append(" and service_number like ?");  //事项号
                if(voll.getService_instNumber()!=null && voll.getService_instNumber().trim().length()!=0)buffer.append(" and service_instNumber like ?");  //办件号
                if(voll.getService_declarerName()!=null && voll.getService_declarerName().trim().length()!=0)buffer.append(" and service_declarerName like ?");     //申请人
                buffer.append(" and service_state!=?");                   //办理状态
                buffer.append(" order by service_declareTime desc");   //申报时间
                Query query = arg0.createQuery(buffer.toString());
                if(voll.getServiceId()!=0) query.setInteger(index++, voll.getServiceId());
                if(voll.getService_orgId()!=0) query.setInteger(index++, voll.getService_orgId());
                if(voll.getService_declareTime()!=null) query.setTimestamp(index++,voll.getService_declareTime());//申报开始时间
                if(voll.getService_endTime()!=null) query.setTimestamp(index++,voll.getService_endTime()) ;//申报结束时间
                if(voll.getService_userId()!=0) query.setInteger(index++, voll.getService_userId());
                if(voll.getService_state()!=0) query.setInteger(index++, voll.getService_state());
                if(voll.getService_name()!=null && voll.getService_name().trim().length()!=0) query.setString(index++, "%"+voll.getService_name()+"%");
                if(voll.getService_number()!=null && voll.getService_number().trim().length()!=0) query.setString(index++, "%"+voll.getService_number()+"%");
                if(voll.getService_instNumber()!=null && voll.getService_instNumber().trim().length()!=0) query.setString(index++, "%"+voll.getService_instNumber()+"%");
                if(voll.getService_declarerName()!=null && voll.getService_declarerName().trim().length()!=0) query.setString(index++, "%"+voll.getService_declarerName()+"%");
                query.setInteger(index++, 12);
                query.setFirstResult(pageno * pageNumber);
                query.setMaxResults(pageNumber);
                return query.list();
            }});
    }

Answer 1

最上面那条就是打印出来的sql语句

Answer 2

明显是你的SQL有问题啊， like 后面应该跟的是单引号的字符串，如 like '%test%'

Answer 3

改一下写法
if(studentid!=null && !studentid.equals(""))
            {
                sql.append(" and s.studentid like ?");
                pars.add("%"+studentid.trim()+"%");
            }改成
if(studentid!=null && !studentid.equals(""))
            {
                sql.append(" and s.studentid like '" + studentid.trim() + "'");            
            }
不要用那个倒霉的pars。。