创建自己的sql语句拼接工具

最新推荐文章于 2024-05-22 15:02:34 发布

小鹏头

最新推荐文章于 2024-05-22 15:02:34 发布

阅读量2.8k

点赞数

分类专栏：工具类文章标签：数据库拼接 map拼接sql语句

本文链接：https://blog.csdn.net/q876651731/article/details/90084933

版权

工具类专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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]

运行结果：
在这里插入图片描述

源码我放在GitHub中啦，也可以从CSDN中下载。

小鹏头

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
创建自己的sql语句拼接工具

创建自己的sql语句拼接工具从浏览器接收到参数以后，传统的方法要判断传上来的参数有哪些，然后根据这些参数写service和dao方法，这会导致dao方法又多，方法重用性又不高。例如：当有3个需求：模糊查询某些，通过id精确查找某个值，通过指定范围查找某些值，就需要写3条dao方法：模糊查询一个方法，精确查询一个方法，范围查询一个方法。这样维护起来既不方便，也增加出错的几率，为了解决这个问...
复制链接

扫一扫