jqgrid 多条件查询问题

享受幸福生活 2013-10-25 05:58:22

根据gengv大虾的武功秘籍我终于修炼到了第五层，
jqGrid与Struts2的结合应用（五） —— 结合Action类进行数据查询
不过继续深入学习还是碰到了问题，
多条件查询出问题了。
症状如下：
1、没有比较操作符

2、代码方面也是自己瞎凑的求大侠们来帮忙

Criterion.java ，不知道为什么没有实现官方的Criterion接口。因为不懂，在Action.java里用离线连接的时候必须要转换成官方的类所以我做了实现官方类。

package cn.dao.impl;



import java.util.ArrayList;

import java.util.Collections;

import java.util.List;



import org.hibernate.Criteria;

import org.hibernate.HibernateException;

import org.hibernate.criterion.CriteriaQuery;

import org.hibernate.engine.TypedValue;

public class Criterion implements org.hibernate.criterion.Criterion  {

	/**

	 * 

	 */

	private static final long serialVersionUID = 1L;

	public static enum CriterionType {

		EQUAL, LIKE, COMPARE, NOT_LIKE

	}

	public static enum CompareType {

		GT, GTE, LT, LTE, EQ, NE

	}

	private CriterionType criterionType;

	private String tableName;

	private String field;

	private Object value;

	// 将Criteria转换为SQL条件语句

	public static String convertToSql(List<Criterion> criteria) {

		String criteriaString = "";

		StringBuilder sb = new StringBuilder();

		for (Criterion criterion : criteria) {

			String prefix = criterion.getFieldPrefix();

			switch (criterion.getCriterionType()) {

			case EQUAL:

				sb.append(prefix + criterion.getField() + "=? and ");

				break;

			case LIKE:

				sb.append(prefix + criterion.getField() + " like ? and ");

				break;

				

			case NOT_LIKE:

				sb.append(prefix + criterion.getField() + " not like ? and ");

				break;

			case COMPARE:

				CompareType compareType = ((CompareCriterion) criterion)

						.getCompareType();

				switch (compareType) {

				case EQ:

					sb.append(prefix + criterion.getField() + "=? and ");

					break;

				case NE:

					sb.append(prefix + criterion.getField() + "<>? and ");

					break;

				case GT:

					sb.append(prefix + criterion.getField() + ">? and ");

					break;

				case GTE:

					sb.append(prefix + criterion.getField() + ">=? and ");

					break;

				case LT:

					sb.append(prefix + criterion.getField() + "<? and ");

					break;

				case LTE:

					sb.append(prefix + criterion.getField() + "<=? and ");

					break;

				}

				break;

			}

		}

		int i = -1;

		if ((i = sb.lastIndexOf(" and ")) != -1) {

			criteriaString = sb.substring(0, i);

		}

		return criteriaString;

	}

	// 将Criteria各条件的值转换为List<Object>

	public static List<Object> getCriteriaValues(List<Criterion> criteria) {

		List<Object> criteriaValues = criteria.isEmpty() ? Collections

				.emptyList() : new ArrayList<Object>();

		for (Criterion criterion : criteria) {

			criteriaValues.add(criterion.getValue());

		}

		return criteriaValues;

	}

	public CriterionType getCriterionType() {

		return criterionType;

	}

	public void setCriterionType(CriterionType criterionType) {

		this.criterionType = criterionType;

	}

	public String getField() {

		return field;

	}

	public void setField(String field) {

		this.field = field;

	}

	public Object getValue() {

		return value;

	}

	public void setValue(Object value) {

		this.value = value;

	}

	public static Criterion getCompareCriterion(CompareType compareType,

			String field, Object value, String tableName) {

		CompareCriterion compareCriterion = new CompareCriterion();

		compareCriterion.setCriterionType(CriterionType.COMPARE);

		compareCriterion.setCompareType(compareType);

		compareCriterion.setField(field);

		compareCriterion.setValue(value);

		compareCriterion.setTableName(tableName);

		return compareCriterion;

	}

	public static Criterion getLikeCriterion(String field, Object value,

			String tableName) {

		LikeCriterion likeCriterion = new LikeCriterion();

		likeCriterion.setCriterionType(CriterionType.LIKE);

		likeCriterion.setField(field);

		likeCriterion.setValue(value);

		likeCriterion.setTableName(tableName);

		return likeCriterion;

	}

	

	public static Criterion getNotLikeCriterion(String field, Object value,

			String tableName) {

		NotLikeCriterion notLikeCriterion = new NotLikeCriterion();

		notLikeCriterion.setCriterionType(CriterionType.NOT_LIKE);

		notLikeCriterion.setField(field);

		notLikeCriterion.setValue(value);

		notLikeCriterion.setTableName(tableName);

		return notLikeCriterion;

	}

	public static Criterion getEqualCriterion(String field, Object value,

			String tableName) {

		EqualCriterion equalCriterion = new EqualCriterion();

		equalCriterion.setCriterionType(CriterionType.EQUAL);

		equalCriterion.setField(field);

		equalCriterion.setValue(value);

		equalCriterion.setTableName(tableName);

		return equalCriterion;

	}

	public static class LikeCriterion extends Criterion {

	}

	

	public static class NotLikeCriterion extends Criterion {

	}

	public static class EqualCriterion extends Criterion {

	}

	public static class CompareCriterion extends Criterion {

		private CompareType compareType;

		public CompareType getCompareType() {

			return compareType;

		}

		public void setCompareType(CompareType compareType) {

			this.compareType = compareType;

		}

	}

	public String getTableName() {

		return tableName;

	}

	public void setTableName(String tableName) {

		this.tableName = tableName;

	}

	public String getFieldPrefix() {

		return (tableName == null || tableName.length() == 0) ? "" : tableName

				+ ".";

	}

	@Override

	public TypedValue[] getTypedValues(Criteria arg0, CriteriaQuery arg1)

			throws HibernateException {

		// TODO Auto-generated method stub

		return null;

	}

	@Override

	public String toSqlString(Criteria arg0, CriteriaQuery arg1)

			throws HibernateException {

		// TODO Auto-generated method stub

		return null;

	}

}

daobean.java



	@SuppressWarnings("unchecked")  

	 public List detachedCriteria(final DetachedCriteria detachedCriteria)  

	 {  

	  return this.getHibernateTemplate().execute(new HibernateCallback()  

	    

	  {  

	   public Object doInHibernate(Session session)  

	     throws HibernateException, SQLException {  

	    //构造Criteria，detachedCriteria与session关联下  

	    Criteria criteria = detachedCriteria.getExecutableCriteria(session);  

	    return criteria.list();  

	   }  

	  });  

	 }

action.java

	@Override

	public List<Roomusage> listResults(List<Criterion> criteria, int from,

			int length) {

		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Roomusage.class);

		for(Criterion criteria1:criteria){

		detachedCriteria.add(criteria1);

		}

		List<Roomusage>  results = roomusageService.detachedCriteria(detachedCriteria,from,length);



		return results;

	}

service.java
实在还没明白悟透，这边传入的两个参数也被我直接作废了.....



	@Transactional(propagation=Propagation.SUPPORTS,readOnly=true)  

	 public List detachedCriteria(final DetachedCriteria detachedCriteria,int from,int length)  

	 {  

	  return roomusageDao.detachedCriteria(detachedCriteria);  

	 }

当前状态，多条件查询没用。
网上找了Demo都没配置运行起来......可能是java版本问题找的貌似1.5的
求大侠帮忙。

...全文

541 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

馥郁的村庄 2013-12-17

打赏
举报

直接获取前台给我们建立的sql语句不行吗？是不是client端给的sql不靠谱？

onSearch: function () {
		            	var $filter = $("#" + $.jgrid.jqID("fbox_" + this.id)),
		                sql = $filter.jqFilter('toSQLString');
		            	alert(sql);
		        }

享受幸福生活 2013-10-28

打赏
举报

看样子只好自力更生了

悲催的程序猿 2013-10-28

打赏
举报

引用 10 楼 redgrass2 的回复:

终于有眉目了，要是谁有问题可以看下 1-9篇： http://www.javakfz.com/index.php/05/21/112.html http://blog.csdn.net/gengv/article/details/5725172 9篇 demo ：http://www.javakfz.com/index.php/06/24/393.html?replytocom=51 改下SQL Server 的数据库自己到配置文件改下就好运行了

谢谢分享！

享受幸福生活 2013-10-28

打赏
举报

终于有眉目了，要是谁有问题可以看下 1-9篇： http://www.javakfz.com/index.php/05/21/112.html http://blog.csdn.net/gengv/article/details/5725172 9篇 demo ：http://www.javakfz.com/index.php/06/24/393.html?replytocom=51 改下SQL Server 的数据库自己到配置文件改下就好运行了