java sql 工具类_Java中SQL动态封装工具类--Java自学网

package com.javalearns.util;

import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; /**  * SQL动态封装工具  *   * simple：：  *   *           BaseSelector select = new BaseSelector();  *           select.setCommand("select * from  stat_user ");  *           select.createCriteria().andEqualTo("org_code", "YZ1201")  *           .andGreaterThanOrEqualTo("day_code", "2009-01-01")  *           .andLessThanOrEqualTo("day_code", "2009-03-31");  *           List rowset = SqlTemplate.executeQuery(select.toSql(),new Object[] {});  *  *           output:select * from  stat_user where org_code = 'YZ1201' and day_code >= '2009-01-01' and day_code <= '2009-03-31'   *   */ public class BaseSelector {

/**      * sql语句      */     private String command;          /**      * 表名称      */     private String tableName;          /**      * 表字段      */     private String tableField;          /**      * 排序字段      */     protected String orderByClause;          /**      * 分组字段      */     protected String groupByClause;          /**      * 统计字段      */     protected String countClause;          /**      * sql条件      */     protected List oredCriteria;

public String getCommand()     {         return command;     }

public void setCommand(String command)     {         this.command = command;     }

public BaseSelector() {         oredCriteria = new ArrayList();     }

protected BaseSelector(BaseSelector example) {      this.tableName = example.tableName;         this.orderByClause = example.orderByClause;         this.oredCriteria = example.oredCriteria;         this.groupByClause = example.groupByClause;         this.countClause = example.countClause;     }          /**      * 生成最终sql语句      */     public String toSql()     {         if(oredCriteria==null||oredCriteria.size()<=0)             return command;                  StringBuffer sqlAll = new StringBuffer();         sqlAll.append(command);                  sqlAll.append(" ");                  if(tableField != null){          sqlAll.append(tableField);         }                  sqlAll.append(" ");                   if(countClause != null){          sqlAll.append(countClause);         }                  sqlAll.append(" ");                  command = sqlAll.toString();         if (command != null && command.toUpperCase().indexOf(" WHERE ") == -1)         //if (command != null && command.toUpperCase().indexOf(" WHERE ") == -1)             sqlAll.append(" WHERE ");         else             sqlAll.append(" AND ");         for (Criteria cri : oredCriteria)         {             if(!cri.isValid())               continue;             sqlAll.append("(");             StringBuffer sql = new StringBuffer();             criteriaWithoutValueSql(sql,cri.criteriaWithoutValue);             criteriaWithSingleValueSql(sql,cri.criteriaWithSingleValue);             criteriaWithListValueSql(sql,cri.criteriaWithListValue);             criteriaWithBetweenValueSql(sql,cri.criteriaWithBetweenValue);             sqlAll.append(sql.toString());             sqlAll.append(")");             sqlAll.append(" or ");         }                  String sql = sqlAll.substring(0, sqlAll.length()-4);                  if(groupByClause != null && groupByClause.toUpperCase().indexOf(" GROUP BY ") == -1){          sql += " GROUP BY ";          sql += groupByClause;         }                          if(orderByClause != null && orderByClause.toUpperCase().indexOf(" ORDER BY ") == -1){          sql += " ORDER BY ";          sql += orderByClause;         }                  return sql;         //return sqlAll.substring(0, sqlAll.length()-4);              }          @SuppressWarnings("unchecked")     private String criteriaWithoutValueSql(StringBuffer sql,List list)     {         if(list==null)             return "";                int n = list.size();         for (int i = 0;i 0&&list.size()>0)             sql.append(" and ");         int n = list.size();         for (int i = 0;i

@SuppressWarnings("unchecked")     private String criteriaWithListValueSql(StringBuffer sql,List list)     {         if(list==null)             return "";         if (sql.length() > 0&&list.size()>0)             sql.append(" and ");         int n = list.size();         for (int i = 0;i 0&&list.size()>0)             sql.append(" and ");         int n = list.size();         for (int i = 0;i

/**      * 顺序排序      * @param field      */     public void setOrderByClauseAsc(String field) {         this.orderByClause = getFieldName(field) + " ASC";     }          /**      * 倒序排序      * @param field      */     public void setOrderByClauseDesc(String field) {         this.orderByClause = getFieldName(field) + " DESC";     }

public String getOrderByClause() {         return orderByClause;     }

public List getOredCriteria() {         return oredCriteria;     }               /**      * 统计总数      * @return      */  public String getCountClause() {   return countClause;  }

public void setCountClause(String countClause) {   String[] s = countClause.split(",");   if(s.length > 1){    this.countClause = ", count(" + s[s.length-1] + ") ";   }else{    this.countClause = countClause;   }  }

/**   * 获得分组   * @return   */  public String getGroupByClause() {   return groupByClause;  }

public void setGroupByClause(String groupByClause) {   this.groupByClause = groupByClause;  }            /**   * 表名称   * @return   */  public String getTableName() {   return tableName;  }

public void setTableName(String tableName) {   this.tableName = tableName;  }

/**   * 表字段   * @return   */  public String getTableField() {   return tableField;  }

public void setTableField(String tableField) {   this.tableField = tableField;  }

/**      * or 条件      */     public void or(Criteria criteria) {         oredCriteria.add(criteria);     }

/**      * 创建条件对象      */     public Criteria createCriteria() {         Criteria criteria = createCriteriaInternal();         if (oredCriteria.size() == 0) {             oredCriteria.add(criteria);         }         return criteria;     }

protected Criteria createCriteriaInternal() {         Criteria criteria = new Criteria();         return criteria;     }

/**      * 清除条件      */     public void clear() {         oredCriteria.clear();     }           static String getFieldName(String field)     {                   if (field == null ) {             throw new RuntimeException( field + " cannot be null");         }         return field.toUpperCase(); // // //        Pattern pattern = Pattern.compile("[A-Z]{1}"); //        Matcher m =  pattern.matcher(field); //        StringBuffer sbr = new StringBuffer(); //        while(m.find()) //            m.appendReplacement(sbr, "_"+m.group()); //        m.appendTail(sbr); //        return sbr.toString().toUpperCase();     }

/**      * 查询条件      */     public static class Criteria {                  //没有值         protected List criteriaWithoutValue;         //String值         protected List> criteriaWithSingleValue;         //集合值         protected List> criteriaWithListValue;         //between区间值         protected List> criteriaWithBetweenValue;

//构造函数         protected Criteria() {             super();             criteriaWithoutValue = new ArrayList();             criteriaWithSingleValue = new ArrayList>();             criteriaWithListValue = new ArrayList>();             criteriaWithBetweenValue = new ArrayList>();         }

//是否有效         public boolean isValid() {             return criteriaWithoutValue.size() > 0                 || criteriaWithSingleValue.size() > 0                 || criteriaWithListValue.size() > 0                 || criteriaWithBetweenValue.size() > 0;         }

public List getCriteriaWithoutValue() {             return criteriaWithoutValue;         }

public List> getCriteriaWithSingleValue() {             return criteriaWithSingleValue;         }

public List> getCriteriaWithListValue() {             return criteriaWithListValue;         }

public List> getCriteriaWithBetweenValue() {             return criteriaWithBetweenValue;         }

protected void addCriterion(String condition) {             if (condition == null||"".equals(condition)) {                 return;             }             criteriaWithoutValue.add(condition);         }

protected void addCriterion(String condition, Object value, String property) {             if (value == null||"".equals(value)) {                 return;             }             Map map = new HashMap();             map.put("condition", condition);             map.put("value", value);             criteriaWithSingleValue.add(map);         }

protected void addCriterion(String condition, List extends Object> values, String property) {             if (values == null || values.size() == 0) {                 return;             }             Map map = new HashMap();             map.put("condition", condition);             map.put("values", values);             criteriaWithListValue.add(map);         }

protected void addCriterion(String condition, Object value1, Object value2, String property) {             if (value1 == null || value2 == null) {                 return;             }             List list = new ArrayList();             list.add(value1);             list.add(value2);             Map map = new HashMap();             map.put("condition", condition);             map.put("values", list);             criteriaWithBetweenValue.add(map);         }

public Criteria andIsNull(String field) {             addCriterion(getFieldName(field)+" is null");             return this;         }

public Criteria andIsNotNull(String field) {             addCriterion(getFieldName(field)+" is not null");             return this;         }

/**          * 字符相等          * @param field          * @param value          * @return          */         public Criteria andEqualTo(String field,String value) {             addCriterion(getFieldName(field)+" =", quoteStr(value), field);             return this;         }

/**          * 字符不相等          * @param field          * @param value          * @return          */         public Criteria andNotEqualTo(String field,String value) {             addCriterion(getFieldName(field)+" <>", quoteStr(value), field);             return this;         }

/**          * 字符大于          * @param field          * @param value          * @return          */         public Criteria andGreaterThan(String field,String value) {             addCriterion(getFieldName(field)+" >", quoteStr(value), field);             return this;         }

/**          * 字符大于等于          * @param field          * @param value          * @return   Java免费学习  Java自学网 http://www.javalearns.com          */         public Criteria andGreaterThanOrEqualTo(String field,String value) {             addCriterion(getFieldName(field)+" >=", quoteStr(value), field);             return this;         }                  /**          * 字符小于          * @param field          * @param value          * @return          */         public Criteria andLessThan(String field,String value) {             addCriterion(getFieldName(field)+"

/**          * 字符小于等于          * @param field          * @param value          * @return          */         public Criteria andLessThanOrEqualTo(String field,String value) {             addCriterion(getFieldName(field)+" <=", quoteStr(value), field);             return this;         }

/**          * 字符相似          * @param field          * @param value          * @return   Java免费学习  Java自学网 http://www.javalearns.com          */         public Criteria andLike(String field,String value) {             addCriterion(getFieldName(field)+" like", quoteStr(value), field);             return this;         }

/**          * 字符不相似          * @param field          * @param value          * @return          */         public Criteria andNotLike(String field,String value) {             addCriterion(getFieldName(field)+" not like", quoteStr(value), field);             return this;         }

/**          * 字符包含在某个范围内IN          * @param field          * @param value          * @return Java免费学习  Java自学网 http://www.javalearns.com          */         @SuppressWarnings("unchecked")         public Criteria andIn(String field,List values) {             List vs = new ArrayList();             for (String string : values)             {                 vs.add(quoteStr(string));             }             addCriterion(getFieldName(field)+" in", vs, field);

return this;         }

@SuppressWarnings("unchecked")         public Criteria andNotIn(String field,List values) {             List vs = new ArrayList();             for (String string : values)             {                 vs.add(quoteStr(string));             }             addCriterion(getFieldName(field)+" not in", vs, field);             return this;         }

public Criteria andBetween(String field,String value1, String value2) {             addCriterion(getFieldName(field)+" between", quoteStr(value1), quoteStr(value2), field);             return this;         }

public Criteria andNotBetween(String field,String value1, String value2) {             addCriterion(getFieldName(field)+" not between", quoteStr(value1), quoteStr(value2), field);             return this;         }                  private String quoteStr(String str)         {             if(str==null)                 return null;             return "'"+str+"'";         }              }     @SuppressWarnings("unchecked")     public static void main(String[] args)     {

BaseSelector select = new BaseSelector();            //select.setCommand("select * from  stat_user ");        select.setCommand("select ");       select.setTableField("uid,uname,pwd");       select.setCountClause("uid,uname,pwd");             select.createCriteria().andEqualTo("org_code", "YZ1201")          .andGreaterThanOrEqualTo("day_code", "2009-01-01")          .andLessThanOrEqualTo("day_code", "2009-03-31");       select.setGroupByClause("uid,uname,pwd");       //select.setOrderByClauseAsc("uid");       System.out.println(select.toSql());          }

}