SQLUtil:简单易用的sql语句拼接工具
从浏览器接收到参数以后，传统的方法要判断传上来的参数有哪些，然后根据这些参数写service和dao方法，这会导致dao方法又多，方法重用性又不高。
例如：当有3个需求：模糊查询某些值，通过id精确查找某个值，通过指定范围查找某些值，就需要写3条dao方法：模糊查询一个方法，精确查询一个方法，范围查询一个方法。
这样维护起来既不方便，也增加出错的几率，为了解决这个问题。我们可以自己创建一个拼接sql语句的工具。
SQLUtil的使用
SQLUtil需要用户遵循以下规则，所有不符合规则的entry都会被忽略掉：
- 所有查找条件都被装到一个
ConditionMap对象中，ConditionMap对象的键都需要包含冒号":",冒号前面的字符串被称为“标签”。 ConditionMap的value值不能为null或为空字符串，否则会被忽略掉。- 当标签为空字符串（注意并非指键为空），表示该条件是精确查找，如：
entry(":id",2) 表示 id=2 - 当标签为
ge,表示大于等于，如entry("ge:price",400) 表示 price>=400 - 当标签为
le，表示小于等于，如entry("le:price",500) 表示 price <= 500 - 当标签为
gt，表示大于，如entry("gt:date","2018-01-01") 表示date > '2018-01-01' - 当标签为
lt，表示小于，如entry("lt:date","2018-01-01") 表示date < '2019-01-01' - 当标签为
like，表示like，如entry("like:name","小雪") 表示 name like '%小雪%' - 当标签为
llike，表示百分号加在value的左边，如entry("llike:name","小雪")表示 name like '%小雪' - 当标签为
rlike，表示百分号加在value的右边，如entry("rlike:name","小雪")表示"name like '小雪%' - 当标签为
in，表示IN，如entry("IN:rid","2,5,6") 表示 rid IN (2,5,6)注意，in的条件之间一定要用逗号分隔，不能用其他字符分隔。 - 当标签为
order，表示orderby，键如果以desc结尾，表示是降序。如：entry("order:desc",“count”) 表示 order by count desc - 当标签为
limit，表示限制搜索多少条数据，如:entry("limit:",20) 表示 limit 20 - 当标签为
offset， 表示限制开始的位置，如entry("offset:",3) 表示offset 3 - 当标签为
join*(星为任意字母)，则需要一个on*(星为任意字母)标签与之对应，表示查询的另一个表以及所绑定的字段，如：
entry("joina:tf","table_favourite");
entry("ona:uid","table_user");
-- 两个entry要同时存在，才能表示如下表达式
JOIN table_favourite tf ON table_user.uid = tf.uid外键查询的规则会比其他查询更复杂，需要仔细核对。
- 当标签为
group，表示分组查询，
如：
entry("group:","uid");
表示 GROUP BY uid- 当标签为
having，值要求是另一个ConditionMap，表示分组查询的筛选条件，如：
// 用于主要语句的ConditionMap
ConditionMap conditionMap = SQLUtil.createConditionMap();
// 用于having的ConditionMap
ConditionMap havingMap = SQLUtil.createConditionMap();
// 指定having的ConditionMap
havingMap.put("ge:date", "1990-09-20");
// 把ConditionMap放入having键中
conditionMap.put("having:", havingMap);
// 加入group 标签
conditionMap.put("group:", "cid");
表示：
GROUP BY cid HAVING date>=‘1990-09-20’;好了！现在你已经掌握了这个工具的使用方式了！现在看看怎样使用吧：
import org.junit.Test;
import utils.SQLUtil;
import java.util.Arrays;
import java.util.TreeMap;
public class TestDemo {
@Test
public void testSQL() {
// 先获取一个专用的键值对Map
ConditionMap conditionMap = SQLUtil.createConditionMap();
conditionMap.put(":id", 2); // 按照规则向map放入键值对
conditionMap.put("gt:price", 3);
conditionMap.put("like:product", "烟草");
String conditionSQL = "select * from tab_product" + SQLUtil.createConditionSQL(conditionMap); // 工具只拼接条件语句，前面的可以自己定义
Object[] conditionValues = SQLUtil.createConditionValues(conditionMap);
System.out.println("conditionSQL = " + conditionSQL);
System.out.println("Arrays.toString(conditionValues) = " + Arrays.toString(conditionValues));
}
}
输出结果：
conditionSQL = select * from tab_product where 1=1 and id=? and price>? and product like ?
Arrays.toString(conditionValues) = [2, 3, %烟草%]这样，就可以直接把conditionSQL，和conditionValues作为参数给jdbcTemplate的query()方法使用啦。
例子使用的是select ，你还可以进行任意操作，比如update、delete等等。sql拼接工具，是专门用于拼接条件，也就是拼接 From some_table 后面的语句。
这样，你就掌握了sql语句拼接工具的基本使用了
如何安装
- 到github选择downloadZIP
- 解压后，用cmd进入解压得到的文件夹
- 运行 mvn install 命令
- 在项目的pom文件添加SQLUtil依赖
添加如下依赖：
<dependency>
<groupId>com.peng</groupId>
<artifactId>SQLUtil</artifactId>
<version>2.0.1</version>
</dependency>就可以使用啦
使用中要注意的地方
SQLUtil的生成语句createConditionSQL()接受一个ConditionMap对象作为参数，我们可以通过以下方法把普通的map，转化成ConditionMap：
public static ConditionMap makeConditionMap(Map<String, ?> map)
这个方法会去掉不符合规范的语句，并且适当地对Entry进行排序，这都是成功拼接的必要条件。
当需要分页查询时
当我们需要分页查询时，除了数据本身，还要查询符合数据的总记录数，为了方便用同一个条件map完成多个查询，SQLUtil提供了方法：public static ConditionMap filterConditionForCount(ConditionMap orgCondition) 他接受一个条件ConditionMap作为参数，返回过滤掉不适合执行计总数的entry，比如order:、limit、offset，这样就能用同一个Map执行分页查询和计算总数的语句了
import org.junit.Test;
import utils.SQLUtil;
import java.util.Arrays;
import java.util.Map;
import java.util.TreeMap;
public class TestDemo {
@Test
public void testSQL() {
ConditionMap conditionMap = SQLUtil.createConditionMap();
conditionMap.put(":id", 2);
conditionMap.put("gt:price", 3);
conditionMap.put("like:product", "烟草");
conditionMap.put("limit:", 5);
conditionMap.put("offset:", 10);
String conditionSQL = "select * from tab_product" + SQLUtil.createConditionSQL(conditionMap);
Object[] conditionValues = SQLUtil.createConditionValues(conditionMap);
System.out.println("conditionSQL = " + conditionSQL);
System.out.println("conditionValues = " + Arrays.toString(conditionValues));
ConditionMap countMap = SQLUtil.filterConditionForCount(conditionMap);
String countSQL = "select count(*) from tab_product" + SQLUtil.createConditionSQL(countMap);
System.out.println("countSQL = " + countSQL);
Object[] countValues = SQLUtil.createConditionValues(countMap);
System.out.println("countValues = " + Arrays.toString(countValues));
}
}
运行结果：
conditionSQL = select * from tab_product where 1=1 and id=? and price>? and product like ? limit ? offset ?
conditionValues = [2, 3, %烟草%, 5, 10]
countSQL = select count(*) from tab_product where 1=1 and id=? and price>? and product like ?
countValues = [2, 3, %烟草%]下面演示一个实际应用的例子
有3个表，tab_user记录了用户信息，tab_route记录了路线信息，tab_favourite记录了用户收藏路线的信息，我需要从tab_user、tab_route、tab_favourite三个表中，按收藏时间倒序来获取路线，这就要用到多表查询
// 先通过session获得用户的uid，就能传入这个方法中
@Override
public PageBean<Route> getUserFavRoute(Map<String, String> map, int uid) {
// 要多表查询
// join tab_favorite tf on tab_route.rid=tf.rid
map.put("joina:tf", "tab_favorite");
map.put("ona:rid", "tab_route");
// join tab_suer tu on tu.uid=tf.uid
map.put("joinb:tu", "tab_user");
map.put("onb:uid", "tf");
// 要增加按用户id，按收藏时间倒序这两个条件
map.put(":tu.uid", uid + "");
map.put("order:desc", "tf.date");
return getPageBean(map);
}就是这么简单，就能拼接好一个条件语句，而在getPageBean(Map) 方法中，将进一步按照这些逻辑定义sql语句的条件：
@Override
public PageBean<Route> getPageBean(Map<String, String> map) {
// map只需要提供当前页码，和每一页显示多少条数据，
// 这些数据都应该是浏览器提供的
int current = Integer.parseInt(map.get("current"));
int size = Integer.parseInt(map.get("size"));
// 生成pageBean后就用数据进行填充
PageBean<Route> routePageBean = new PageBean<>();
routePageBean.setCurrent(current);
routePageBean.setSize(size);
// 过滤掉不带条件的其他内容，也就是过滤掉没有冒号的其他内容，并且用所需的sortedMap包装好
ConditionMap conditionMap = SQLUtil.makeConditionMap(map);
// 把current和size转换成limit 和offset条件
conditionMap.put("limit:", size);
conditionMap.put("offset:", size * (current - 1));
// 条件已经齐全，调用dao方法获取路线对象
List<Route> data = routeDAO.getRoutesByCondition(conditionMap);
// 调用dao方法获取路线的总记录数
int count = routeDAO.getRouteCountOf(conditionMap);
routePageBean.setCount(count);
routePageBean.setData(data);
return routePageBean;
}再来看看DAO层：
public List<Route> getRoutesByCondition(ConditionMap condition) {
String sql = "select tab_route.rid,tab_route.sid,rimage," +
"tab_route.cid,count,rname,rdate,price,routeIntroduce from tab_route " +
// 调用SQLUtil方法拼接条件就行
SQLUtil.createConditionSQL(condition);
System.out.println("sql = " + sql);
// 调用SQLUtil方法创建条件参数
Object[] conditionValues = SQLUtil.createConditionValues(condition);
System.out.println("values=" + Arrays.toString(conditionValues));
// 直接交给jdbc就能使用，不用担心顺序之类的问题
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Route.class),
conditionValues);
}执行项目后打印出来的结果:
-- jdbc的sql语句
sql =
select tab_route.rid,tab_route.sid,rimage,tab_route.cid,count,rname,rdate,price,routeIntroduce
from tab_route
JOIN tab_favorite tf ON tab_route.rid=tf.rid
JOIN tab_user tu ON tf.uid=tu.uid
where 1=1 and tu.uid=?
order by tf.date desc
limit ? offset ?
-- jdbc的参数
values=[1, 12, 0]运行结果：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
