[toc]
1. JAVA中的SQL处理有什么问题吗？
看看JAVA中怎么查询数据库
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;
// 定义一个list用于接受数据库查询到的内容
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()) {
// 将查询出的内容添加到list中，其中userName为数据库中的字段名称
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. 问题的本质是什么
JAVA处理sql的方式，与sql自己的语法格格不入， 用java写sql需要学习n个orm库的语法，以及这些库背后的设计思想。 其实这些都是多余的， 会写sql还不够吗？
在LISP中，会sql就足够了。
3. 什么是DSL
领域特定语言（英语：domain-specific language、DSL）指的是专注于某个应用程序领域的计算机语言。又译作领域专用语言。
定义2： 针对某一领域，具有受限表达性的一种计算机程序设计语言。
DSL最大的作用是屏蔽无关的噪音，专注于业务领域内的本质问题。 即只解决本质复杂度的问题，降低甚至消除偶然复杂性。
有人将DSL编程称之为声明式编程。
声明式编程：定义做什么，而不是用一堆命令语句来描述怎么做。这一点很重要。DSL就是用声明式编程。
防止DSL逐渐演变为一种通用语言，要受限表达。目的防止DSL过于复杂，可维护性降低，学习成本提升，偏离方向。
内部DSL：用通用语言的语法表示DSL，需要按照某种风格使用这种语言。
外部DSL：在主程序设计语言之外，用一种单独的语言表示领域专有语言。可以是定制语法，或者遵循另外一种语法，如XML，c make。
DSL并不是一个新的概念，实际上，从六七十年代就已经有DSL的概念了（从某种程度上来说，COBOL和FORTRAN也算是DSL，一个面向商业，一个面向科学计算），然后有RegExp，再到现在的RoR，无一不采用了DSL的思想。
4. java中的一种实现方法
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);//用page对象构造查询对象
query.setCount(false);//设定是否自动获取总数，如果获取的话，只会统计一次
query.addTable(WellJob.class, null);//addtable可以多次调用，用于联合查询，第二个参数是表别名，null的话表示用默认值
query.addConditon("jobType.id", OP.eq, jobid).and("state", OP.eq, state).and("well.wellNum", OP.in, wellNames);
this.pageQueryService.getNextPage(query);//获取下一页，如果page参数从页面上传回来，这个方法就是翻页了
//this.pageQueryService 对象是全局对象，基本上就是个壳，不用关心，主要功能都是在query对象里边实现的
return page.getRows();
}
看下具体怎么实现的
5. 建立这个工具的初衷
多条件查询最恶心的问题是什么？
这个问题上边的代码已经解决了，加上注释看下
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 这个方法表示设置条件的开始，只能调用一次，如果第三个参数是null，则忽略这次方法调用
//add 与条件，如果第三个参数是null，忽略这次方法调用
//query 对象上基本每个api的返回值都是自身，api可以连续调用
this.pageQueryService.getNextPage(query);
return page.getRows();
}
6. 其他语言中怎么做
ruby 中一个库的用法
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类实现
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. 逻辑的严密性问题
省掉空值会不会造成构造出来的sql发生语法错误， 没有仔细思考和证明过
