java多条件分页查询_多条件分页查询,省掉麻烦的空值判断

1 importjava.math.BigDecimal;2 importjava.math.BigInteger;3 importjava.util.ArrayList;4 importjava.util.Date;5 importjava.util.HashMap;6 importjava.util.List;7 importjava.util.Locale;8 importjava.util.Map;9

10 importorg.apache.commons.lang.xwork.StringUtils;11 importorg.hibernate.Query;12 importorg.hibernate.classic.Session;13 importorg.secneo.framework.orm.hibernate.Page;14

15 /**

16 * 分页查询 工具, 原理类似hibernate 的critical工具,但用法上不同, 可以与pageQueryService 配合,在 action中直接使用,17 * 本质上该工具是对hql的封装, 封装后18 * 目的是提供简易的分页查询接口,可以设定为自动计算总条目数。19 * 可以设定条件的替换规则,如 第二个条件不为空时 ,第一个条件则不生效,20 *21 *@authorZDCIN22 *23 */

24 public class PageQuery{25

26 private Pagepage;27 //private List tables = new ArrayList();

28 private String where = "";29 private Map conditionMap = new HashMap();30 private String orderBy = "";31

32 private int conditionIndex = 0;33 private boolean isCount = true;34

35 /**

36 * 查看是否计算总数, 默认true, 计算总数的条件是 isCount = true37 *@return

38 */

39 public booleanisCount() {40 returnisCount;41 }42 /**

43 * 设置是否计算总数, 默认true44 *@return

45 */

46 public void setCount(booleanisCount) {47 this.isCount =isCount;48 }49 /**

50 * 设定page对象,和返回类型T51 *@parampage52 */

53 public PageQuery(Pagepage) {54 this.page =page;55 }56

57 private String selectSql = "";58

59 /**

60 * 当获取字段不是一个标准bean或者不是一个bean的全部字段的时候使用, 默认不用调用该方法61 *@paramselectSql62 *@return

63 */

64 public PageQuerysetSelectSql(String selectSql) {65 this.selectSql =selectSql;66 return this;67 }68 privateString countSql;69

70 /**

71 * 当count语句不能从select简易变化过来的时候使用。 比如有distinct限制的时候72 *@paramcountSql73 *@return

74 */

75 public PageQuerysetCountSql(String countSql) {76 this.countSql =countSql;77 return this;78 }79

80 privateString fromSql;81

82 /**

83 * 添加要查询的表,并设定别名, 该方法可以多次调用,作为多表联合查询。84 *@paramtableClass85 *@paramalias 可以为空,表示没有别名86 *@return

87 */

88 @SuppressWarnings("rawtypes")89 public PageQueryaddTable(Class tableClass, String alias) {90 if (StringUtils.isBlank(this.fromSql)) {91 this.fromSql = " FROM ";92 } else{93 this.fromSql += ", ";94 }95 if(StringUtils.isNotBlank(alias)) {96 this.fromSql += tableClass.getSimpleName() + " AS " +alias;97 } else{98 this.fromSql += tableClass.getSimpleName() + " ";99 }100 return this;101 }102

103 /**

104 * 添加条件, 该方法只能调用一次,且必须在and 或者or之前调用105 *@paramfiledName 字段名, 别名和字段的组合形式,106 *@paramop 字段上的条件操作, 如等于, 大于, in like等,107 *@paramvalue 如果value为空,则该条件不生效, 但当op是 innotnull或者isnull的时候例外,该条件会生效108 *@return

109 */

110 public PageQueryaddConditon(String filedName, OP op, Object value) {111 CondTuple tuple = new CondTuple(null, filedName, op, value, null);112 this.conditionMap.put(this.conditionIndex ++, tuple);113 return this;114 //if (value == null) {115 //return this;116 //}117 // //String filedFullName = this.filedName(tableIndex, filedName);118 //this.condition = " WHERE " + filedName + op.v() + ":" + filedName;119 //this.conditionMap.put(filedName, value);120 //return this;

121 }122

123

124 /**

125 * addConditon(String filedName, OP op, Object value) 的简写形式, op 是eq126 *@paramfiledName127 *@paramvalue128 *@return

129 */

130 public PageQueryaddConditon(String filedName, Object value) {131 return this.addConditon(filedName, OP.eq, value);132 }133

134 private static enumLogicOp {135 and, or;136 }137

138 private PageQueryand_AND_or(LogicOp logicOP, String filedName, OP op, Object value,139 String replaceWhich) {140 CondTuple tuple = newCondTuple(logicOP, filedName, op, value, replaceWhich);141 this.conditionMap.put(this.conditionIndex ++, tuple);142 return this;143 //if (value == null) {144 //return this;145 //}146 //if (StringUtils.isBlank(this.condition)) {147 //this.condition = " WHERE ";148 //} else {149 //this.condition += " " + logicOP.name() + " ";150 //}151 // //String filedFullName = this.filedName(tableIndex, filedName);152 //this.condition += filedName + op.v() + ":" + filedName;153 //this.conditionMap.put(filedName, value);154 //return this;

155 }156

157 /**

158 * 添加and条件, where中的条件都是平级的,如果有括号,则用逻辑运算规则拉平159 *160 *@paramfiledName 与addConditon中意义相同161 *@paramop 与addConditon中意义相同162 *@paramvalue 与addConditon中意义相同163 *@paramreplaceWhich 替换列表, 如果该方法中value不为空,则替换列表中的字段,如“1,2”164 *165 *@return

166 */

167 public PageQueryand(String filedName, OP op, Object value, String replaceWhich) {168 return this.and_AND_or(LogicOp.and, filedName, op, value, replaceWhich);169 }170

171 /**

172 * and(String filedName, OP op, Object value, String replaceWhich) 的简写形式173 *@paramfiledName174 *@paramop175 *@paramvalue176 *@return

177 */

178 public PageQueryand(String filedName, OP op, Object value) {179 return this.and_AND_or(LogicOp.and, filedName, op, value, null);180 }181

182 /**

183 * and(String filedName, OP op, Object value, String replaceWhich) 的简写形式184 *@paramfiledName185 *@paramvalue186 *@return

187 */

188 public PageQueryand(String filedName, Object value) {189 return this.and_AND_or(LogicOp.and, filedName, OP.eq, value, null);190 }191

192 /**

193 * 添加or条件, where中的条件都是平级的,如果有括号,则用逻辑运算规则拉平194 *195 *@paramfiledName 与addConditon中意义相同196 *@paramop 与addConditon中意义相同197 *@paramvalue 与addConditon中意义相同198 *@paramreplaceWhich 替换列表, 如果该方法中value不为空,则替换列表中的字段,如“1,2”199 *200 *@return

201 */

202 public PageQueryor(String filedName, OP op, Object value, String replaceWhich) {203 return this.and_AND_or(LogicOp.or, filedName, op, value, replaceWhich);204 }205

206

207 /**

208 * 同 or(String filedName, OP op, Object value, String replaceWhich)209 *@paramfiledName210 *@paramop211 *@paramvalue212 *@return

213 */

214 public PageQueryor(String filedName, OP op, Object value) {215 return this.and_AND_or(LogicOp.or, filedName, op, value, null);216 }217

218 /**

219 * 同 or(String filedName, OP op, Object value, String replaceWhich)220 *@paramfiledName221 *@paramvalue222 *@return

223 */

224 public PageQueryor(String filedName, Object value) {225 return this.and_AND_or(LogicOp.or, filedName, OP.eq, value, null);226 }227

228 /**

229 * 添加排序, 可调用多次230 *@paramfiledName 与addConditon中意义相同231 *@paramorderType OrderType.ASC, OrderType.DESC232 *@return

233 */

234 public PageQueryaddOrderBy(String filedName, OrderType orderType) {235 if(StringUtils.isBlank(filedName)) {236 return this;237 }238 if (StringUtils.isBlank(this.orderBy)) {239 this.orderBy = " ORDER BY ";240 } else{241 this.orderBy += ",";242 }243 this.orderBy += filedName + " " +orderType.name();244 return this;245 }246

247

248 /**

249 * 执行查询, 在pageQueryService中调用, service可以提供hibernate session对象250 *@paramcurrentSession251 *@return

252 */

253 public PageexcuteQuery(Session currentSession) {254 List replaceList = this.getReplaceList();255 this.buildWhereStatementInSql(replaceList);256

257 if (page.getTotal() == -1) {258 String countHql = "SELECT " + (StringUtils.isBlank(this.countSql) ? " COUNT(*) " : this.countSql)259 + this.fromSql + this.where;260

261 Query hquery =currentSession.createQuery(countHql);262 this.buildHibernateQuery(replaceList, hquery);263 Long totalCol =(Long) hquery.uniqueResult();264

265 page.setTotal(totalCol.intValue());266 //page.setPageNo(1);//肯定是从第一页开始

267 }268 String hql = this.selectSql + this.fromSql + this.where + this.orderBy;269 System.out.println("query sql is:" +hql);270 Query hquery =currentSession.createQuery(hql);271 this.buildHibernateQuery(replaceList, hquery);272 @SuppressWarnings("unchecked")273 List resultSet= hquery.setFirstResult(this.page.getFirst()).setMaxResults(this.page.getPageSize()).list();274 this.page.setRows(resultSet);275 return this.page;276 }277

278 private void buildHibernateQuery(ListreplaceList, Query hquery) {279 for (int i = 0; i < this.conditionIndex; i++) {280 CondTuple tuple = this.conditionMap.get(i);281 if(replaceList.contains(i)) {282 continue;283 }284 if (tuple.value != null && !tuple.op.equals(OP.in)) {285 this.putObjectToHql(hquery, tuple.valueNameInHql +i, tuple.value);286 }287 }288 }289

290 private voidputObjectToHql(Query hquery, String valueNameInHql, Object value) {291 //valueNameInHql = valueNameInHql.replace('.', '_');292 //valueName 是hql中的冒号后边的单词,用于hql执行时用变量替换,里边不能含有下列字符293 //String valueNameInHql = valueNameInHql;

294 valueNameInHql = valueNameInHql.replace('.', '_');295 valueNameInHql = valueNameInHql.replaceAll(" ", "");296 valueNameInHql = valueNameInHql.replaceAll("-", "");297 valueNameInHql = valueNameInHql.replaceAll("\\(", "");298 valueNameInHql = valueNameInHql.replaceAll("\\)", "");299 if (value instanceofLong) {300 hquery.setLong(valueNameInHql, (Long) value);301 } else if (value instanceofString) {302 hquery.setString(valueNameInHql, (String) value);303 } else if (value instanceofDate) {304 hquery.setTimestamp(valueNameInHql, (Date) value);305 } else if (value instanceofBigDecimal) {306 hquery.setBigDecimal(valueNameInHql, (BigDecimal) value);307 } else if (value instanceofInteger) {308 hquery.setInteger(valueNameInHql, (Integer) value);309 } else if (value instanceofDouble) {310 hquery.setDouble(valueNameInHql, (Double) value);311 } else if (value instanceofFloat) {312 hquery.setFloat(valueNameInHql, (Float) value);313 } else if (value instanceofBoolean) {314 hquery.setBoolean(valueNameInHql, (Boolean) value);315 } else if (value instanceof byte[]) {316 hquery.setBinary(valueNameInHql, (byte[]) value);317 } else if (value instanceofLocale) {318 hquery.setLocale(valueNameInHql, (Locale) value);319 } else if (value instanceofBigInteger) {320 hquery.setBigInteger(valueNameInHql, (BigInteger) value);321 } else{322 throw new RuntimeException("不支持的value类型:" +value.getClass());323 }324 //下面这些不常用,且麻烦,不支持325 //setText(String, String)326 //setSerializable(String, Serializable)327 //setDate(String, Date)328 //setTime(String, Date)329 //setCalendar(String, Calendar)330 //setCalendarDate(String, Calendar)331 //setCharacter(String, char)332 //setByte(String, byte)333 //setShort(String, short)

334 }335

336 private void buildWhereStatementInSql(ListreplaceList) {337 for (int i = 0; i < this.conditionIndex; i++) {338 CondTuple tuple = this.conditionMap.get(i);339 if(replaceList.contains(i)) {340 continue;341 }342 if (tuple.value != null) {343 //in 查询特殊处理

344 if(tuple.op.equals(OP.in)) {345 List value =(List)tuple.value;346 String temp = "";347 if (value == null || value.size() == 0) {348 break;349 }350 if (value.get(0) instanceofString) {351 temp = "('";352 for (String subv : (List)value) {353 temp += subv + "','";354 }355 temp += "')";356 temp = temp.replace(",')", "')");357 } else if (value.get(0) instanceofLong){358 temp = "(";359 for (Long subv : (List)value) {360 temp += subv.longValue() + ",";361 }362 temp += ")";363 temp = temp.replace(",)", ")");364 }else{365 temp = "(";366 for (Integer subv : (List)value) {367 temp += subv.intValue() + ",";368 }369 temp += ")";370 temp = temp.replace(",)", ")");371 }372 this.where += (tuple.logicOp == null ? "" : tuple.logicOp.name()) + " " + tuple.valueNameInHql +tuple.op.v()373 + temp + " ";374 } else{375 //valueName 是hql中的冒号后边的单词,用于hql执行时用变量替换,里边不能含有下列字符

376 String valueName = tuple.valueNameInHql +i;377 valueName = valueName.replace('.', '_');378 valueName = valueName.replaceAll(" ", "");379 valueName = valueName.replaceAll("-", "");380 valueName = valueName.replaceAll("\\(", "");381 valueName = valueName.replaceAll("\\)", "");382 this.where += (tuple.logicOp == null ? "" : tuple.logicOp.name()) + " " +tuple.valueNameInHql383 + tuple.op.v() + ":" + valueName + " ";384 }385 } else{386 if (tuple.op.equals(OP.isNull) ||tuple.op.equals(OP.isNotNull)) {387 this.where += (tuple.logicOp == null ? "" : tuple.logicOp.name()) + " " +tuple.valueNameInHql388 + tuple.op.v() + " ";389 }390 }391 }392 if (StringUtils.isNotBlank(this.where)) {393 this.where = " WHERE " + this.where;394 if (this.where.contains("WHERE and")) {395 this.where.replace("WHERE and", "WHERE ");396 } else if (this.where.contains("WHERE or")) {397 this.where.replace("WHERE or", "WHERE ");398 }399 }400 }401 private ListgetReplaceList() {402 List result = new ArrayList();403 String replaceString = "";404 for (int i = 0; i < this.conditionIndex; i++) {405 CondTuple tuple = this.conditionMap.get(i);406 if (tuple.value != null &&StringUtils.isNotBlank(tuple.replaceString)) {407 replaceString +=tuple.replaceString;408 }409 }410 String[] temp = replaceString.split(",");411 if (temp == null || temp.length == 0) {412 returnresult;413 }414 for(String t : temp) {415 if(StringUtils.isBlank(t)) {416 continue;417 }418 if (!result.contains(t)) {419 result.add(Integer.parseInt(t));420 }421 }422 returnresult;423 }424

425

426

427 /**条件组合对象*/

428 private static classCondTuple {429 CondTuple(LogicOp logicOp, String valueNameInHql, OP op, Object value, String replaceString) {430 this.logicOp =logicOp;431 this.valueNameInHql =valueNameInHql;432 this.op =op;433 this.value =value;434 this.replaceString =replaceString;435 }436

437 LogicOp logicOp;438 String valueNameInHql;439 OP op;440 Object value;441 String replaceString;442 }443 public static enumOrderType {444 ASC,DESC;445 }446 public static enumOP {447 eq {448 {449 value = "=";450 }451 },452 neq {453 {454 value = "<>";455 }456 },457 gt {458 {459 value = ">";460 }461 },462 lt {463 {464 value = "=";470 }471 },472 le {473 {474 value = "<=";475 }476 },477 like {478 {479 value = " like ";480 }481 },482 in {483 {484 value = " in ";485 }486 },487 isNotNull {488 {489 value = " is not null ";490 }491 },492 isNull {493 {494 value = " is null ";495 }496 };497 protected String value = null;498

499 publicString v() {500 returnvalue;501 }502 }503

504

505 }

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页
评论

打赏作者

Jason不跪

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值