jqGrid分页技术学习（八）spring jdbc 单条件查询

TablePagingController对单条件查询的判断：

...... String searchField = request.getParameter("searchField"); // searchField:id logger.info("Res-Host request searchField=[" + searchField + "]"); String searchOper = request.getParameter("searchOper"); // searchOper:eg logger.info("Res-Host request searchOper=[" + searchOper + "]"); String searchString = request.getParameter("searchString"); // searchString:7 logger.info("Res-Host request searchString=[" + searchString + "]"); final JQGridPTO<InvHeader> pto = this.getDao() .queryBySingleCondition(page, rows, sidx, sord, searchField, searchOper, searchString); model.put("JSON_OBJECT", pto); ......

 

TablePagingDao对单条件查询的处理：

...... /** * 单条件表单查询 * * @param pageNo * @param pageSize * @param sidx * @param sord * @return */ public JQGridPTO<InvHeader> queryBySingleCondition(final int pageNo, final int pageSize, final String sidx, final String sord, final String searchField, final String searchOper, final String searchString) { List<Criterion> criteria = Collections.emptyList(); criteria = new ArrayList<Criterion>(); // 通过searchField、searchString、searchOper生成通用的查询条件 CriterionUtil util = new CriterionUtil(); Criterion criterion = util.generateSearchCriterion(searchField, searchString, searchOper); if (criterion != null) { criteria.add(criterion); } String conditionSql = Criterion.convertToSql(criteria); logger.info("Condition SQL: " + conditionSql); JdbcTemplate jdbcTemplate = this.getJdbcTemplate(); PaginationHelper<InvHeader> ph = new PaginationHelper<InvHeader>(); final String countSql = "SELECT count(*) FROM invheader" + " WHERE " + conditionSql; final String seaechSql = "SELECT id, invdate, client_id, amount, tax, total, closed, ship_via, note" + " FROM invheader" + " WHERE " + conditionSql + " ORDER BY " + sidx + " " + sord; return ph.fetchPage(jdbcTemplate, countSql, seaechSql, new Object[] { searchString }, pageNo, pageSize, new ParameterizedRowMapper<InvHeader>() { public InvHeader mapRow(ResultSet rs, int i) throws SQLException { InvHeader data = new InvHeader(); data.setId(rs.getInt(1)); data.setInvdate(rs.getString(2)); data.setClient_id(rs.getInt(3)); data.setAmount(rs.getFloat(4)); data.setTax(rs.getFloat(5)); data.setTotal(rs.getFloat(6)); data.setClosed(rs.getString(7)); data.setShip_via(rs.getString(8)); data.setNote(rs.getString(9)); return data; } }); } ......

 

增加一个Criterion类：

/** * 通过searchField、searchString、searchOper三个参数生成Criterion的方法； * 以前在学习Hibernate的时候，里面就介绍过使用Criterion的方式； * 有的朋友对Hibernate奉若神明，但我一直对Hibernate的灵活度不太满意； 不过这种将查询条件抽象出来的思路还是值得借鉴的。 * 因此，我虽然后台使用的是Spring的JdbcTemplate，但为了抽象化Dao内的操作， 我自己写了一个 Criterion类（具体代码在后面）。 * 使用这个Criterion列表来简化查询条件在各个层的传递。 * * 我把Criterion分成4类：EqualCriterion、CompareCriterion、LikeCriterion、 * NotLikeCriterion。（具体划分方式全为使用方便）。 * 另外还有两个静态方法：convertToSql和getCriteriaValues， * 用来将Criterion列表转化为JdbcTemplate需要的SQL子句和参数列表。 * * @author thomas */ public class Criterion { 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 + "."; } }

 

还有一个工具类CriterionUtil：

public class CriterionUtil { public List<Criterion> generateSearchCriteriaFromFilters(String filters) { List<Criterion> criteria = new ArrayList<Criterion>(); JSONObject jsonObject = JSONObject.fromObject(filters); JSONArray rules = jsonObject.getJSONArray("rules"); for (Object obj : rules) { JSONObject rule = (JSONObject) obj; String field = rule.getString("field"); String op = rule.getString("op"); String data = rule.getString("data"); Criterion criterion = this.generateSearchCriterion(field, data, op); if (criterion != null) { criteria.add(criterion); } } return criteria; } // 通过searchField、searchString、searchOper三个参数生成Criterion的方法 public Criterion generateSearchCriterion(String searchField, String searchString, String searchOper) { Criterion criterion = null; // 如果searchField、searchString、searchOper均不为null，且searchString不为空字符串时，则创建Criterion if (searchField != null && searchString != null && searchString.length() > 0 && searchOper != null) { if ("eq".equals(searchOper)) { criterion = Criterion.getEqualCriterion(searchField, searchString, null); } else if ("ne".equals(searchOper)) { criterion = Criterion.getCompareCriterion(CompareType.NE, searchField, searchString, null); } else if ("lt".equals(searchOper)) { criterion = Criterion.getCompareCriterion(CompareType.LT, searchField, searchString, null); } else if ("le".equals(searchOper)) { criterion = Criterion.getCompareCriterion(CompareType.LTE, searchField, searchString, null); } else if ("gt".equals(searchOper)) { criterion = Criterion.getCompareCriterion(CompareType.GT, searchField, searchString, null); } else if ("ge".equals(searchOper)) { criterion = Criterion.getCompareCriterion(CompareType.GTE, searchField, searchString, null); } else if ("bw".equals(searchOper)) { criterion = Criterion.getLikeCriterion(searchField, searchString + "%", null); } else if ("bn".equals(searchOper)) { criterion = Criterion.getNotLikeCriterion(searchField, searchString + "%", null); } else if ("ew".equals(searchOper)) { criterion = Criterion.getLikeCriterion(searchField, "%" + searchString, null); } else if ("en".equals(searchOper)) { criterion = Criterion.getNotLikeCriterion(searchField, "%" + searchString, null); } else if ("cn".equals(searchOper)) { criterion = Criterion.getLikeCriterion(searchField, "%" + searchString + "%", null); } else if ("nc".equals(searchOper)) { criterion = Criterion.getNotLikeCriterion(searchField, "%" + searchString + "%", null); } } return criterion; } }