/*** 根據QueryRule自動構建sql語句*/ public class QueryRuleSqlBuilder {private int CURR_INDEX = 0; //記錄參數所在的位置private List<String> properties; //保存列名列表private List<Object> values; //保存參數值列表private List<Order> orders; //保存排序規則列表private String whereSql = ""; private String orderSql = "";private Object [] valueArr = new Object[]{};private Map<Object,Object> valueMap = new HashMap<Object,Object>();/*** 或得查詢條件* @return*/public String getWhereSql(){return this.whereSql;}/*** 獲得排序條件* @return*/public String getOrderSql(){return this.orderSql;}/*** 獲得參數值列表* @return*/public Object [] getValues(){return this.valueArr;}/*** 獲取參數列表* @return*/public Map<Object,Object> getValueMap(){return this.valueMap;}/*** 創建SQL構造器* @param queryRule*/public QueryRuleSqlBuilder(QueryRule queryRule) {CURR_INDEX = 0;properties = new ArrayList<String>();values = new ArrayList<Object>();orders = new ArrayList<Order>();for (Rule rule : queryRule.getRuleList()) {switch (rule.getType()) {case QueryRule.BETWEEN:processBetween(rule);break;case QueryRule.EQ:processEqual(rule);break;case QueryRule.LIKE:processLike(rule);break;case QueryRule.NOTEQ:processNotEqual(rule);break;case QueryRule.GT:processGreaterThen(rule);break;case QueryRule.GE:processGreaterEqual(rule);break;case QueryRule.LT:processLessThen(rule);break;case QueryRule.LE:processLessEqual(rule);break;case QueryRule.IN:processIN(rule);break;case QueryRule.NOTIN:processNotIN(rule);break;case QueryRule.ISNULL:processIsNull(rule);break;case QueryRule.ISNOTNULL:processIsNotNull(rule);break;case QueryRule.ISEMPTY:processIsEmpty(rule);break;case QueryRule.ISNOTEMPTY:processIsNotEmpty(rule);break;case QueryRule.ASC_ORDER:processOrder(rule);break;case QueryRule.DESC_ORDER:processOrder(rule);break;default:throw new IllegalArgumentException("type " + rule.getType() + " not supported.");}}//拼裝where語句appendWhereSql();//拼裝排序語句appendOrderSql();//拼裝參數值appendValues();}/*** 去掉order* * @param sql* @return*/protected String removeOrders(String sql) {Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);Matcher m = p.matcher(sql);StringBuffer sb = new StringBuffer();while (m.find()) {m.appendReplacement(sb, "");}m.appendTail(sb);return sb.toString();}/*** 去掉select* * @param sql* @return*/protected String removeSelect(String sql) {if(sql.toLowerCase().matches("from\\s+")){int beginPos = sql.toLowerCase().indexOf("from");return sql.substring(beginPos);}else{return sql;}}/*** 處理like* @param rule*/private void processLike(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}Object obj = rule.getValues()[0];if (obj != null) {String value = obj.toString();if (!StringUtils.isEmpty(value)) {value = value.replace('*', '%');obj = value;}}add(rule.getAndOr(),rule.getPropertyName(),"like","%"+rule.getValues()[0]+"%");}/*** 處理between* @param rule*/private void processBetween(Rule rule) {if ((ArrayUtils.isEmpty(rule.getValues()))|| (rule.getValues().length < 2)) {return;}add(rule.getAndOr(),rule.getPropertyName(),"","between",rule.getValues()[0],"and");add(0,"","","",rule.getValues()[1],"");}/*** 處理 =* @param rule*/private void processEqual(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}add(rule.getAndOr(),rule.getPropertyName(),"=",rule.getValues()[0]);}/*** 處理 <>* @param rule*/private void processNotEqual(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}add(rule.getAndOr(),rule.getPropertyName(),"<>",rule.getValues()[0]);}/*** 處理 >* @param rule*/private void processGreaterThen(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}add(rule.getAndOr(),rule.getPropertyName(),">",rule.getValues()[0]);}/*** 處理>=* @param rule*/private void processGreaterEqual(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}add(rule.getAndOr(),rule.getPropertyName(),">=",rule.getValues()[0]);}/*** 處理<* @param rule*/private void processLessThen(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}add(rule.getAndOr(),rule.getPropertyName(),"<",rule.getValues()[0]);}/*** 處理<=* @param rule*/private void processLessEqual(Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}add(rule.getAndOr(),rule.getPropertyName(),"<=",rule.getValues()[0]);}/*** 處理 is null* @param rule*/private void processIsNull(Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"is null",null);}/*** 處理 is not null* @param rule*/private void processIsNotNull(Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"is not null",null);}/*** 處理 <>''* @param rule*/private void processIsNotEmpty(Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"<>","''");}/*** 處理 =''* @param rule*/private void processIsEmpty(Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"=","''");}/*** 處理in和not in* @param rule* @param name*/private void inAndNotIn(Rule rule,String name){if (ArrayUtils.isEmpty(rule.getValues())) {return;}if ((rule.getValues().length == 1) && (rule.getValues()[0] != null)&& (rule.getValues()[0] instanceof List)) {List<Object> list = (List) rule.getValues()[0];if ((list != null) && (list.size() > 0)){for (int i = 0; i < list.size(); i++) {if(i == 0 && i == list.size() - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name + " (",list.get(i),")");}else if(i == 0 && i < list.size() - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name + " (",list.get(i),"");}if(i > 0 && i < list.size() - 1){add(0,"",",","",list.get(i),"");}if(i == list.size() - 1 && i != 0){add(0,"",",","",list.get(i),")");}}}} else {Object[] list = rule.getValues();for (int i = 0; i < list.length; i++) {if(i == 0 && i == list.length - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name + " (",list[i],")");}else if(i == 0 && i < list.length - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name + " (",list[i],"");}if(i > 0 && i < list.length - 1){add(0,"",",","",list[i],"");}if(i == list.length - 1 && i != 0){add(0,"",",","",list[i],")");}}}}/*** 處理 not in* @param rule*/private void processNotIN(Rule rule){inAndNotIn(rule,"not in");}/*** 處理 in* @param rule*/private void processIN(Rule rule) {inAndNotIn(rule,"in");}/*** 處理 order by* @param rule 查詢規則*/private void processOrder(Rule rule) {switch (rule.getType()) {case QueryRule.ASC_ORDER:// propertyName非空if (!StringUtils.isEmpty(rule.getPropertyName())) {orders.add(Order.asc(rule.getPropertyName()));}break;case QueryRule.DESC_ORDER:// propertyName非空if (!StringUtils.isEmpty(rule.getPropertyName())) {orders.add(Order.desc(rule.getPropertyName()));}break;default:break;}}/*** 加入到sql查詢規則隊列* @param andOr and 或者 or* @param key 列名* @param split 列名與值之間的間隔* @param value 值*/private void add(int andOr,String key,String split ,Object value){add(andOr,key,split,"",value,"");}/*** 加入到sql查詢規則隊列* @param andOr and 或則 or* @param key 列名* @param split 列名與值之間的間隔* @param prefix 值前綴* @param value 值* @param suffix 值后綴*/private void add(int andOr,String key,String split ,String prefix,Object value,String suffix){String andOrStr = (0 == andOr ? "" :(QueryRule.AND == andOr ? " and " : " or ")); properties.add(CURR_INDEX, andOrStr + key + " " + split + prefix + (null != value ? " ? " : " ") + suffix);if(null != value){values.add(CURR_INDEX,value);CURR_INDEX ++;}}/*** 拼裝 where 語句*/private void appendWhereSql(){StringBuffer whereSql = new StringBuffer();for (String p : properties) {whereSql.append(p);}this.whereSql = removeSelect(removeOrders(whereSql.toString()));}/*** 拼裝排序語句*/private void appendOrderSql(){StringBuffer orderSql = new StringBuffer();for (int i = 0 ; i < orders.size(); i ++) {if(i > 0 && i < orders.size()){orderSql.append(",");}orderSql.append(orders.get(i).toString());}this.orderSql = removeSelect(removeOrders(orderSql.toString()));}/*** 拼裝參數值*/private void appendValues(){Object [] val = new Object[values.size()];for (int i = 0; i < values.size(); i ++) {val[i] = values.get(i);valueMap.put(i, values.get(i));}this.valueArr = val;}}

?

總結

以上是生活随笔為你收集整理的基于SpringJDBC 实现关键功能-QueryRuleSqlBulider的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。