SQL动态封装工具类

在项目中直接使用jdbc操作的时候，sql的动态条件拼装一直是个非常头疼的事情，前一阵子研究ibatis，发现他的代码生成工具iBATOR生成的example类实际是个很不错的sql拼装类，于是拿过来修改了下，

使用：

            BaseSelector select = new BaseSelector();
            select.setCommand("select  from  user ");
            select.createCriteria().andEqualTo("org_code", "YZ1201")
            .andGreaterThanOrEqualTo("day_code", "2009-01-01")
            .andLessThanOrEqualTo("day_code", "2009-03-31");

调用 select.toSql() 输出:

select  from  user where org_code = 'YZ1201' and day_code >= '2009-01-01' and day_code <= '2009-03-31' 

抛砖引玉，有哪位兄弟有更高的拿来看看啊

        List days = new LinkedList();
        days.add("2008-01-01");
        days.add("2008-01-02");
        BaseSelector cri = new BaseSelector();
        cri.setCommand("select * from table where 1=1");
        cri.createCriteria().andEqualTo("org_code", "vvv")
        .andIn("day_code", days);
        cri.or( cri.createCriteria().andEqualTo("status", "1"));
        
        
        System.out.println(cri.toSql());

select * from table where 1=1 AND (ORG_CODE ='vvv' and DAY_CODE in('2008-01-01','2008-01-02')) or (STATUS ='1')

BaseSelector.java

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<Map> 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;
    /**
     * 排序字段
     */
    protected String orderByClause;
    

    /**
     * sql条件
     */
    protected List<Criteria> oredCriteria;



    public String getCommand()
    {
        return command;
    }


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


    public BaseSelector() {
        oredCriteria = new ArrayList<Criteria>();
    }


    protected BaseSelector(BaseSelector example) {
        this.orderByClause = example.orderByClause;
        this.oredCriteria = example.oredCriteria;
    }
    
    /**
     * 生成最终sql语句
     */
    public String toSql()
    {
        if(oredCriteria==null||oredCriteria.size()<=0)
            return command;
        
        StringBuffer sqlAll = new StringBuffer();
        sqlAll.append(command);
        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 ");
        }
        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<n;i++)
        {
            sql.append(list.get(i));
            if(i<n-1)
                sql.append(" and ");
        }
        
        return sql.toString();
    }
    
    @SuppressWarnings("unchecked")
    private String criteriaWithSingleValueSql(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<n;i++)
        {
            Map map = (Map) list.get(i);
            sql.append(map.get("condition")).append(map.get("value"));
            if(i<n-1)
                sql.append(" and ");
        }
        return sql.toString();
    }

    @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<n;i++)
        {
            Map map = (Map) list.get(i);
            sql.append(map.get("condition")).append("("+join((Collection) map.get("values"),",")+")");
            if(i<n-1)
                sql.append(" and ");
        }
        return sql.toString();
    }
    
    @SuppressWarnings("unchecked")
    private String criteriaWithBetweenValueSql(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<n;i++)
        {
            Map map = (Map) list.get(i);
            sql.append(map.get("condition")).append(join((Collection) map.get("values")," and "));
            if(i<n-1)
                sql.append(" and ");
        }
        return sql.toString();
    }
    
    @SuppressWarnings("unchecked")
    private String  join(Collection list,String spe)
    {
        if(list==null)
            return "";
        Object array[] = list.toArray();
        StringBuffer buff = new StringBuffer();
        for (int i = 0; i < array.length; i++)
        {
            buff.append(array[i]);
            if(i<array.length-1)
                buff.append(spe);
        }
        return buff.toString();
    }

    /**
     * 顺序排序
     * @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<Criteria> getOredCriteria() {
        return oredCriteria;
    }

    /**
     * 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<String> criteriaWithoutValue;

        protected List<Map<String, Object>> criteriaWithSingleValue;

        protected List<Map<String, Object>> criteriaWithListValue;

        protected List<Map<String, Object>> criteriaWithBetweenValue;

        protected Criteria() {
            super();
            criteriaWithoutValue = new ArrayList<String>();
            criteriaWithSingleValue = new ArrayList<Map<String, Object>>();
            criteriaWithListValue = new ArrayList<Map<String, Object>>();
            criteriaWithBetweenValue = new ArrayList<Map<String, Object>>();
        }

        public boolean isValid() {
            return criteriaWithoutValue.size() > 0
                || criteriaWithSingleValue.size() > 0
                || criteriaWithListValue.size() > 0
                || criteriaWithBetweenValue.size() > 0;
        }

        public List<String> getCriteriaWithoutValue() {
            return criteriaWithoutValue;
        }

        public List<Map<String, Object>> getCriteriaWithSingleValue() {
            return criteriaWithSingleValue;
        }

        public List<Map<String, Object>> getCriteriaWithListValue() {
            return criteriaWithListValue;
        }

        public List<Map<String, Object>> 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<String, Object> map = new HashMap<String, Object>();
            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<String, Object> map = new HashMap<String, Object>();
            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<Object> list = new ArrayList<Object>();
            list.add(value1);
            list.add(value2);
            Map<String, Object> map = new HashMap<String, Object>();
            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;
        }

        public Criteria andEqualTo(String field,String value) {
            addCriterion(getFieldName(field)+" =", quoteStr(value), field);
            return this;
        }

        public Criteria andNotEqualTo(String field,String value) {
            addCriterion(getFieldName(field)+" <>", quoteStr(value), field);
            return this;
        }

        public Criteria andGreaterThan(String field,String value) {
            addCriterion(getFieldName(field)+" >", quoteStr(value), field);
            return this;
        }

        public Criteria andGreaterThanOrEqualTo(String field,String value) {
            addCriterion(getFieldName(field)+" >=", quoteStr(value), field);
            return this;
        }

        public Criteria andLessThan(String field,String value) {
            addCriterion(getFieldName(field)+" <", quoteStr(value), field);
            return this;
        }

        public Criteria andLessThanOrEqualTo(String field,String value) {
            addCriterion(getFieldName(field)+" <=", quoteStr(value), field);
            return this;
        }

        public Criteria andLike(String field,String value) {
            addCriterion(getFieldName(field)+" like", quoteStr(value), field);
            return this;
        }

        public Criteria andNotLike(String field,String value) {
            addCriterion(getFieldName(field)+" not like", quoteStr(value), field);
            return this;
        }

        @SuppressWarnings("unchecked")
        public Criteria andIn(String field,List<String> 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<String> 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)
    {
        List days = new LinkedList();
        days.add("2008-01-01");
        days.add("2008-01-02");
        BaseSelector cri = new BaseSelector();
        cri.setCommand("select * from table where 1=1");
        cri.createCriteria().andEqualTo("org_code", "vvv")
        .andIn("day_code", days);
        cri.or( cri.createCriteria().andEqualTo("status", "1"));
        
        
        System.out.println(cri.toSql());
    }
    

研究一下了，感觉不错，还没有依赖其他的类，收藏。

用我的“仓库猫”吧。动态处理查询比这个好多了。

貌似LZ的这个封装里，order by 没有封装进来吧。

用这种形式的话是不是如果参数是带有单引号或者双引号或者其他特殊字符串的时候会有转义错误的！
是不是应该用preparestatement来进行参数的设置呢？

看着累死，我们现在就有这样的工具

妈的，看老代码真的想杀人！

也在用String 拼sql的中。

楼主封装的不错。