[toc]

1. JAVA中的SQL處理有什么問題嗎？

看看JAVA中怎么查詢數(shù)據(jù)庫

public List<String> getSelect(int id, String name) { 
　　　　　// sql語句
        String sql = "select * from people p where p.id = ? and p.name = ?"; 
　　　　　// 獲取到連接
        Connection conn = getCon();
        PreparedStatement pst = null;
        // 定義一個(gè)list用于接受數(shù)據(jù)庫查詢到的內(nèi)容
        List<String> list = new ArrayList<String>();
        try {
            pst = (PreparedStatement) conn.prepareStatement(sql);
            ps.setint(1,id);
            ps.setstring(2,name);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                // 將查詢出的內(nèi)容添加到list中，其中userName為數(shù)據(jù)庫中的字段名稱
                list.add(rs.getString("userName"));
            }
        } catch (Exception e) {
        }
        return list;
    }

看看LISP中怎么做的

(select ((:+ 1 1)))
;=> #<SXQL-STATEMENT: SELECT (1 + 1)>

(select :name
  (from :person)
  (where (:> :age 20)))
;=> #<SXQL-STATEMENT: SELECT name FROM person WHERE (age > 20)>

(select (:id :name)
  (from (:as :person :p))
  (left-join :person_config :on (:= :person.config_id :person_config.id))
  (where (:and (:> :age 20)
               (:<= :age 65)))
  (order-by :age)
  (limit 5))
;=> #<SXQL-STATEMENT: SELECT id, name FROM (person AS p) LEFT JOIN person_config ON (person.config_id = person_config.id) WHERE ((age > 20) AND (age <= 65)) ORDER BY age LIMIT 5>

(select (:sex (:count :*)) (from :person) (group-by :sex))
;=> #<SXQL-STATEMENT: SELECT sex, COUNT(*) FROM person GROUP BY sex>

(select (:sex (:as (:count :*) :num)) 
  (from :person)
  (group-by :sex)
  (order-by (:desc :num)))
;=> #<SXQL-STATEMENT: SELECT sex, COUNT(*) AS num FROM person GROUP BY sex ORDER BY num DESC>

2. 問題的本質(zhì)是什么

JAVA處理sql的方式，與sql自己的語法格格不入， 用java寫sql需要學(xué)習(xí)n個(gè)orm庫的語法，以及這些庫背后的設(shè)計(jì)思想。 其實(shí)這些都是多余的， 會(huì)寫sql還不夠嗎？

在LISP中，會(huì)sql就足夠了。

3. 什么是DSL

領(lǐng)域特定語言（英語：domain-specific language、DSL）指的是專注于某個(gè)應(yīng)用程序領(lǐng)域的計(jì)算機(jī)語言。又譯作領(lǐng)域?qū)Ｓ谜Z言。

定義2： 針對(duì)某一領(lǐng)域，具有受限表達(dá)性的一種計(jì)算機(jī)程序設(shè)計(jì)語言。

DSL最大的作用是屏蔽無關(guān)的噪音，專注于業(yè)務(wù)領(lǐng)域內(nèi)的本質(zhì)問題。 即只解決本質(zhì)復(fù)雜度的問題，降低甚至消除偶然復(fù)雜性。

有人將DSL編程稱之為聲明式編程。

聲明式編程：定義做什么，而不是用一堆命令語句來描述怎么做。這一點(diǎn)很重要。DSL就是用聲明式編程。

防止DSL逐漸演變?yōu)橐环N通用語言，要受限表達(dá)。目的防止DSL過于復(fù)雜，可維護(hù)性降低，學(xué)習(xí)成本提升，偏離方向。

內(nèi)部DSL：用通用語言的語法表示DSL，需要按照某種風(fēng)格使用這種語言。

外部DSL：在主程序設(shè)計(jì)語言之外，用一種單獨(dú)的語言表示領(lǐng)域?qū)Ｓ姓Z言?？梢允嵌ㄖ普Z法，或者遵循另外一種語法，如XML，c make。

DSL并不是一個(gè)新的概念，實(shí)際上，從六七十年代就已經(jīng)有DSL的概念了（從某種程度上來說，COBOL和FORTRAN也算是DSL，一個(gè)面向商業(yè)，一個(gè)面向科學(xué)計(jì)算），然后有RegExp，再到現(xiàn)在的RoR，無一不采用了DSL的思想。

4. java中的一種實(shí)現(xiàn)方法

private List<Well> find(Integer jobid, String state, List<String> wellNames) throws Exception {
        Page<WellJob> page = new Page<WellJob>();//初始化一個(gè)分頁查詢結(jié)果對(duì)象，用于接收查詢結(jié)果
        page.setPageSize(rows.size());//設(shè)置一頁大小
        PageQuery<WellJob> query = new PageQuery<WellJob>(page);//用page對(duì)象構(gòu)造查詢對(duì)象
        query.setCount(false);//設(shè)定是否自動(dòng)獲取總數(shù)，如果獲取的話，只會(huì)統(tǒng)計(jì)一次
        query.addTable(WellJob.class, null);//addtable可以多次調(diào)用，用于聯(lián)合查詢，第二個(gè)參數(shù)是表別名，null的話表示用默認(rèn)值
        query.addConditon("jobType.id", OP.eq, jobid).and("state", OP.eq, state).and("well.wellNum", OP.in, wellNames);
        this.pageQueryService.getNextPage(query);//獲取下一頁，如果page參數(shù)從頁面上傳回來，這個(gè)方法就是翻頁了
        //this.pageQueryService 對(duì)象是全局對(duì)象，基本上就是個(gè)殼，不用關(guān)心，主要功能都是在query對(duì)象里邊實(shí)現(xiàn)的
        return page.getRows();
    }

看下具體怎么實(shí)現(xiàn)的

5. 建立這個(gè)工具的初衷

多條件查詢最惡心的問題是什么？

這個(gè)問題上邊的代碼已經(jīng)解決了，加上注釋看下

private List<Well> find(Integer jobid, String state, List<String> wellNames) throws Exception {
        Page<WellJob> page = new Page<WellJob>();
        page.setPageSize(rows.size());
        PageQuery<WellJob> query = new PageQuery<WellJob>(page);
        query.setCount(false);
    
        query.select(xxx).from(WellJob.class, null）.leftjoin(User.class, null).where("jobType.id", OP.eq, jobid).and("state", OP.eq, state).and("well.wellNum", OP.in, wellNames).leftquort().xxx().rightquort();
        //addCondition 這個(gè)方法表示設(shè)置條件的開始，只能調(diào)用一次，如果第三個(gè)參數(shù)是null，則忽略這次方法調(diào)用
        //add  與條件，如果第三個(gè)參數(shù)是null，忽略這次方法調(diào)用
        //query 對(duì)象上基本每個(gè)api的返回值都是自身，api可以連續(xù)調(diào)用
    
        this.pageQueryService.getNextPage(query);
        return page.getRows();
    }

6. 其他語言中怎么做

ruby 中一個(gè)庫的用法

require 'where_builder' 
f = WhereBuilder::WhereBuilder.new() 
where = f.build( 
  f.add('a.name = ?', 'zd'), 
  f.OR('a.id=?', 1), 
  f.AND, 
  f.bracket( 
    f.add('key like ?', 'zd'), 
    f.AND('value=?', '2') 
  ) 
)

puts "where=#{where}" 

output: where=["WHERE a.name = ? OR a.id=? AND ( key like ? AND value=?)", ["zd", 1, "zd", "2"]] 
=> nil 

==============================
where = f.build( 
  f.add('a.name = b.name'), 
  f.OR('a.id=?', 1), 
  f.AND, 
  f.bracket( 
    f.add('key like ?', 'zd'), 
    f.AND('value=?', '2') 
  ) 
) 
=> ["WHERE a.name = b.name OR a.id=? AND ( key like ? AND value=?)", [1, "zd", "2"]] 

庫代碼

=begin
  use this tool can build a where sentence for sql, it's can ignore a condition when it's para is nil or black string.
  my purpose is not check nil for every condition, don't repeat so much if else.
=end
  class WhereBuilder

=begin
  add first condition on 'where ..' or '(..)', you can use AND(..) or OR(..), it's ok, just ugly
  cond_str:  like 'a.name=?', 'a.id=b.user_id'
  para:   like 'leo', 123, nil
  
  return : fn
=end
    def add(cond_str, para=nil)
      if (para == nil or para.to_s.strip.size == 0) and cond_str.include?('?') 
        return
      end
      #TODO in and not in
      return lambda{return cond_str, para}
    end

=begin
  add a 'and' condition on 'where ..' or '(..)', 
  cond_str:  like 'a.name=?', 'a.id=b.user_id' or nil, if it's nil, just append a ' AND ' str, 
  para:   like 'leo', 123, nil
  return : fn
=end
    def AND(cond_str=nil, para=nil)
      if cond_str == nil 
        return lambda{return " AND", nil}
      end
      if (para == nil or para.to_s.strip.size == 0) and cond_str.include?('?') 
        return
      end
      return lambda{return " AND #{cond_str}", para}
    end

=begin
  add a 'or' condition on 'where ..' or '(..)', 
  cond_str:  like 'a.name=?', 'a.id=b.user_id' or nil, if it's nil, just append a ' OR ' str, 
  para:   like 'leo', 123, nil
  
  return : fn
=end
    def OR(cond_str=nil, para=nil)
      if cond_str == nil 
        return lambda{return " OR", nil}
      end
      if (para == nil or para.to_s.strip.size == 0) and cond_str.include?('?') 
        return
      end
      return lambda{return " OR #{cond_str}", para}
    end

=begin
  if you want add some condition with '()', use this method.
  use like this : 
  f = WhereBuilder.new()
  f.bracket(f.add(...), f.AND(...), f.AND(...))
  
  return : fn
=end
    def bracket(*args)
      fn = _build_fn(*args)
      return if fn == nil
      para = fn.call
      if para == nil || para.size == 0
        return
      end
      return lambda{return " (#{para[0]})", para[1]}
    end

=begin
  do not use this method if you do't want to fix bug or upgrade this tool.
=end
    def _build_fn(*args)
      cond_str = []
      para_list = []
      size = args.size
      index = 0
      while index < size
        fn = args[index]
        if fn == nil
          index += 1
          next
        end
        typle = fn.call()
        if typle == nil
          index += 1
          next
        end
        cond_str << typle[0]
        
        if typle[1].class == Array
          _index = 0
          while _index < typle[1].size
            para_list << typle[1][_index]
            _index += 1
          end
        elsif typle[1] != nil
          para_list << typle[1]
        end

        index += 1
      end #while index < size
      if cond_str.size == 0 
        return 
      end
      if cond_str[0].strip.start_with? 'AND ' or cond_str[0].strip.start_with? 'OR '
        cond1 = cond_str[0].strip
        cond1 = " #{cond1[3..-1]}"
        cond_str[0] = cond1
      end
      return lambda{return " #{cond_str.join('')}", para_list}
    end

=begin
  this is the enter for this tool, 
  use it like this : 
  f = WhereBuilder.new()
  string, para_list = f.build(
                          f.add(...), 
                          f.AND(...), 
                          f.AND(...), 
                          f.bracket(
                            f.add(...), 
                            f.OR(...))
                          f.OR(...))
  
  return : [string, para_list]
=end
    def build(*args)
      fn = _build_fn(*args)
      if fn == nil
        return ''
      end
      v = fn.call()
      return ["WHERE#{v[0]}", v[1]]
    end

  end # class WhereBuilder
end

7. 一種低成本解決辦法

    public List<App> findByCondition(AppQuery q) {
        String sql = "SELECT x FROM App x ";
        List params = new ArrayList<>();
        List<String> conds = new ArrayList<String>();

        SeviceUtils.fillNumCond("Integer", conds, params, "x.id", q.getIdOp(), q.getIdValue(), q.getIdStart(), q.getIdEnd());
        SeviceUtils.fillStringCond(conds, params, "x.name", q.getNameOp(), q.getNameValue(), q.getNameStart(), q.getNameEnd());
        SeviceUtils.fillStringCond(conds, params, "x.domain", q.getDomainOp(), q.getDomainValue(), q.getDomainStart(), q.getDomainEnd());
        SeviceUtils.fillNumCond("Integer", conds, params, "x.parentId.id", "eq", q.getParentIdValue(), null, null);
        if (conds.size() > 0) {
            sql += " WHERE " + String.join(" AND ", conds);
        }
        sql += " ORDER BY x.id DESC";

        Query query = this.entityManager.createQuery(sql);
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i + 1, params.get(i));
        }
        List<App> infos = query.setFirstResult(q.getPageSize() * q.getPageIndex()).setMaxResults(q.getPageSize()).getResultList();
        return infos;
    }

serviceUtils類實(shí)現(xiàn)

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.criteria.Predicate;

import org.springframework.util.StringUtils;

public class SeviceUtils {

    public static void fillNumCond(String typeName, List<String> conds, List params, String fname, String op, Number value,
            Number start, Number end) {
        try {
            if (StringUtils.isEmpty(op)) {
                return;
            }
            Object v = null;
            Object s = null; 
            Object e = null;
            if (typeName.equals("int") || typeName.equals("Integer")) {
                try {
                    v = new Integer(value.intValue());
                } catch (Exception exc) {
                }
                try {
                    s = new Integer(start.intValue());
                } catch (Exception exc) {
                }
                try {
                    e = new Integer(end.intValue());
                } catch (Exception exc) {
                }
            } else if (typeName.equals("byte") || typeName.equals("Byte")) {

                try {
                    v = new Byte(value.byteValue());
                } catch (Exception exc) {
                }
                try {
                    s = new Byte(start.byteValue());
                } catch (Exception exc) {
                }
                try {
                    e = new Byte(end.byteValue());
                } catch (Exception exc) {
                }
            
            } else if (typeName.equals("short") || typeName.equals("Short")) {
                try {
                    v = new Short(value.shortValue());
                } catch (Exception exc) {
                }
                try {
                    s = new Short(start.shortValue());
                } catch (Exception exc) {
                }
                try {
                    e = new Short(end.shortValue());
                } catch (Exception exc) {
                }
            } else if (typeName.equals("long") || typeName.equals("Long")) {
                try {
                    v = new Long(value.longValue());
                } catch (Exception exc) {
                }
                try {
                    s = new Long(start.longValue());
                } catch (Exception exc) {
                }
                try {
                    e = new Long(end.longValue());
                } catch (Exception exc) {
                }
            } else if (typeName.equals("float") || typeName.equals("Float")) {

                try {
                    v = new Float(value.floatValue());
                } catch (Exception exc) {
                }
                try {
                    s = new Float(start.floatValue());
                } catch (Exception exc) {
                }
                try {
                    e = new Float(end.floatValue());
                } catch (Exception exc) {
                }
            
            } else if (typeName.equals("double") || typeName.equals("Double")) {
                try {
                    v = new Double(value.doubleValue());
                } catch (Exception exc) {
                }
                try {
                    s = new Double(start.doubleValue());
                } catch (Exception exc) {
                }
                try {
                    e = new Double(end.doubleValue());
                } catch (Exception exc) {
                }
            }
            
            
            switch (QueryOP.valueOf(op)) {
            case eq:
                if (v == null) {
                    return ;
                }
                conds.add(String.format(" %s=? ", fname));
                params.add(v);
                break;
            case gt:
                if (v == null) {
                    return ;
                }
                conds.add(String.format(" %s>? ", fname));
                params.add(v);
                break;
            case lt:
                if (v == null) {
                    return ;
                }
                conds.add(String.format(" %s<? ", fname));
                params.add(v);
                break;
            case between:
                if (s == null && e == null) {
                    return ;
                }
                if (s != null) {
                    conds.add(String.format(" %s>=? ", fname));
                    params.add(s);
                }
                if (e != null) {
                    conds.add(String.format(" %s<=? ", fname));
                    params.add(e);
                }
                break;
            default:
                return ;
            }

            return ;
        } catch (Exception e) {
            e.printStackTrace();
            return ;
        }
    }
    
    public static void fillStringCond(List<String> conds, List params, String fname, String op, String idValue,
            String idStart, String idEnd) {
        try {
            if (StringUtils.isEmpty(op)) {
                return;
            }
            Predicate p2 = null;
            switch (QueryOP.valueOf(op)) {
            case eq:
                if (idValue == null) {
                    return;
                }
                conds.add(String.format(" %s=? ", fname));
                params.add(idValue);
                break;
            case like:
                if (idValue == null) {
                    return;
                }
                conds.add(String.format(" %s LIKE ? ", fname));
                params.add(idValue);
                break;
            case gt:
                if (idValue == null) {
                    return ;
                }
                conds.add(String.format(" %s>? ", fname));
                params.add(idValue);
                break;
            case lt:
                if (idValue == null) {
                    return ;
                }
                conds.add(String.format(" %s<? ", fname));
                params.add(idValue);
                break;
            case between:
                if (idStart == null && idEnd == null) {
                    return ;
                }
                if (idStart != null) {
                    conds.add(String.format(" %s>=? ", fname));
                    params.add(idStart);
                }
                if (idEnd != null) {
                    conds.add(String.format(" %s<=? ", fname));
                    params.add(idEnd);
                }
                break;
            default:
                return;
            }

            return;
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
    }

    public static void fillEnumCond(List<String> conds, List params, String fname, String op,
            String values) {
        try {
            if (StringUtils.isEmpty(op)) {
                return;
            }
            switch (QueryOP.valueOf(op)) {
            case eq:
                if (values == null) {
                    return ;
                }
                String[] vs = values.split(",");
                List<String> list = new ArrayList<String>();
                for (String v : vs) {
                    list.add(v.trim());
                }
                conds.add(String.format(" %s in (?) ", fname));
                params.add(list);
                break;
            
            default:
                return ;
            }
            return ;
        } catch (Exception e) {
            e.printStackTrace();
            return ;
        }
    }

    public static void fillBooleanCond(List<String> conds, List params, String fname, String op,
            Boolean value) {
        try {
            if (StringUtils.isEmpty(op)) {
                return;
            }
            switch (QueryOP.valueOf(op)) {
            case eq:
                if (value == null) {
                    return ;
                }
                conds.add(String.format(" %s=? ", fname));
                params.add(value);
                break;
            
            default:
                return ;
            }
            return ;
        } catch (Exception e) {
            e.printStackTrace();
            return ;
        }
    }

    public static void fillDateCond(List<String> conds, List params, String fname, String op,
            Date value, Date startValue, Date endValue) {
        try {
            if (StringUtils.isEmpty(op)) {
                return;
            }
            switch (QueryOP.valueOf(op)) {
            case eq:
                if (value == null) {
                    return ;
                }
                conds.add(String.format(" %s=? ", fname));
                params.add(value);
                break;
            case gt:
                if (value == null) {
                    return ;
                }
                conds.add(String.format(" %s>? ", fname));
                params.add(value);
                break;
            case lt:
                if (value == null) {
                    return ;
                }
                conds.add(String.format(" %s<? ", fname));
                params.add(value);
                break;
            case between:
                if (startValue == null && endValue == null) {
                    return ;
                }
                if (startValue != null) {
                    conds.add(String.format(" %s>=? ", fname));
                    params.add(startValue);
                }
                if (endValue != null) {
                    conds.add(String.format(" %s<=? ", fname));
                    params.add(endValue);
                }
                break;
            default:
                return ;
            }

            return ;
        } catch (Exception e) {
            e.printStackTrace();
            return ;
        }
    }
}

8. 邏輯的嚴(yán)密性問題

? 省掉空值會(huì)不會(huì)造成構(gòu)造出來的sql發(fā)生語法錯(cuò)誤， 沒有仔細(xì)思考和證明過