JSON驱动的动态SQL查询：实现灵活条件筛选的查询

当我们构建动态 SQL 查询功能时，需要考虑到安全性和灵活性的平衡。本文将讨论如何通过 JSON 数据和 FreeMarker 模板构造动态 SQL 查询，以及如何减少 SQL 注入的风险。

JSON 数据与动态 SQL

JSON 是一种常用的数据交换格式，它的灵活性和易读性使得它在前后端数据传输中得到广泛应用。我们可以利用 JSON 数据来构建动态 SQL 查询，根据用户的输入条件动态生成 SQL 语句，实现灵活的查询功能。

FreeMarker 模板引擎

FreeMarker 是一个强大的模板引擎，它可以将数据模型和模板文件结合起来生成文本输出。我们可以利用 FreeMarker 的语法和功能，将 JSON 数据和 SQL 查询模板结合起来，动态生成最终的 SQL 语句。

动态 SQL 查询示例

让我们以一个示例来说明如何通过 JSON 数据和 FreeMarker 构造动态 SQL 查询功能。假设我们有一个需求：根据用户输入的查询条件动态生成 SQL 查询语句，并执行查询操作。

首先，我们可以定义一个 JSON 数据格式，包含用户的查询条件，如下所示

{"model": "pageForPurchaseOrder","page": 1,"size": 10,"filters": {"acceptance": "0","account": "","beginDate": "2024-01-03","endDate": "2024-05-09","warehouseCodeList": "000,SD002"}
}

接下来，我们可以编写一个 FreeMarker 模板文件，定义 SQL 查询语句的模板，例如：

SELECT * FROM users
WHERE 1=1
</#if>
<#if age??>
AND age = ${age}
</#if>
<#if city??>
AND city = '${city}'
</#if>

在这个模板中，我们使用了 FreeMarker 的条件判断语句 <#if> 和 ${} 表达式，根据传入的查询条件动态生成 SQL 查询语句。

后台配置具体内容

结合后台的 FreeMarker 模板引擎和前台传递的 JSON 数据是一种常见的做法。下面将通过引入代码示例来说明如何整合后台的 FreeMarker 和前台的 JSON 数据生成 SQL 查询语句，并进一步探讨其实现原理和优势。

首先，让我们来看一下代码示例：

public ResponseObject<SqlObject> getSqlObject(HttpServletRequest request) {ResponseObject<SqlObject> result = new ResponseObject<>();try {SqlObject obj = new SqlObject();JSONObject search = JSON.parseObject(request.getInputStream());obj.setPage(search.getLongValue("page", 1));obj.setSize(search.getLongValue("size", 10));String modelKey = search.getString("model");QueryTemplateRequest req = new QueryTemplateRequest();req.setId(modelKey);ResponseObject<QueryTemplateResponse> model = queryTemplateApi.view(req);if (model.getCode() != 200) {result.setCode(501);result.setMsg("model不存在");return result;}Map<String, Object> param = new HashMap<>();param.put("phone", UserUtils.user().getPhone());if (search.containsKey("filters")){JSONObject filters = search.getJSONObject("filters");QueryTemplateExt ext = model.getData().getExtData();if (ext != null && ext.getFilters() != null) {for (QueryFilter filter : ext.getFilters()) {if (filter.getJavaName() != null) {if (filters.containsKey(filter.getJavaName())){try {Object temp = FieldTypeMatch.matchType(filter.getJavaName(), filter.getFieldType(), filters);if (temp!=null){param.put(filter.getJavaName(), temp);}}catch (Exception e){e.printStackTrace();}}}}}}obj.setSql(process(model.getData().getContent(), param));result.setData(obj);return result;} catch (Exception e) {e.printStackTrace();result.setCode(501);result.setMsg("解析json出错");return result;}
}

在这段代码中，我们接收了一个 HTTP 请求，通过 HttpServletRequest 获取了前台传递的 JSON 数据。然后，利用 FreeMarker 模板引擎和后台的业务逻辑，动态生成了 SQL 查询语句。下面我们来逐步解析这段代码的关键步骤：

解析 JSON 数据： 首先通过 JSON.parseObject(request.getInputStream()) 解析前台传递的 JSON 数据，获取了查询所需的各种参数，例如页码、每页大小、模型关键字和筛选条件等。
查询模型信息： 根据获取的模型关键字，调用 queryTemplateApi 获取模型信息。如果模型不存在或获取失败，则返回相应的错误信息。
构建参数： 根据前台传递的筛选条件，利用后台的业务逻辑构建查询所需的参数。这里通过 FieldTypeMatch 类中的 matchType 方法，根据字段类型匹配，减少 SQL 注入的风险，并构建最终的查询参数。
生成 SQL 语句： 最后通过 process 方法，结合模型内容和参数，生成最终的 SQL 查询语句，并将其设置到 SqlObject 对象中，作为返回结果。

通过这段代码示例，我们展示了如何整合后台的 FreeMarker 模板引擎和前台传递的 JSON 数据，实现动态生成 SQL 查询语句的功能。这种方法使得系统可以根据用户的不同需求动态生成不同的 SQL 查询，具有很高的灵活性和可扩展性。

这种整合方式的优势在于：

灵活性： 可以根据前端传递的 JSON 数据动态生成不同的 SQL 查询，适应不同的业务需求。
安全性： 通过后台的业务逻辑和参数处理，可以有效减少 SQL 注入的风险。
可维护性： 使用 FreeMarker 模板可以将 SQL 查询逻辑与 Java 代码分离，提高代码的可读性和可维护性。

在动态生成 SQL 查询语句的过程中，FieldTypeMatch 类发挥了重要作用。这个类中的 matchType 方法根据字段类型和前台传递的 JSON 数据，将不同类型的值转换为适合 SQL 查询的格式。让我们来看一下这个方法的具体实现：

 public static Object matchType(String key, Integer fieldType, JSONObject filter) {Object value;if (fieldType == 1) {value = filter.getString(key);} else if (fieldType == 2) {value = filter.getInteger(key);} else if (fieldType == 3) {value = filter.getLong(key);} else if (fieldType == 4) {value = filter.getDouble(key);} else if (fieldType == 5) {value = filter.getBigDecimal(key);} else if (fieldType == 6) {Object value = filter.getDate(key);SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");value = format.format(value);} else if (fieldType == 7) {value = filter.getBoolean(key);} else {String[] stars;String temp;if (fieldType == 10) {temp = filter.getString(key);if (temp == null) {return null;}stars = temp.split(",");value = Arrays.stream(stars).map(String::trim).filter(StringUtils::hasText).map((s) -> {return "'" + s + "'";}).collect(Collectors.joining(","));value = "(" + value + ")";} else if (fieldType == 11) {temp = filter.getString(key);if (temp == null) {return null;}stars = temp.split(",");value = Arrays.stream(stars).map(String::trim).filter(StringUtils::hasText).map(Long::valueOf).map(String::valueOf).collect(Collectors.joining(","));value = "(" + value + ")";} else if (fieldType == 12) {temp = filter.getString(key);if (temp == null) {return null;}stars = temp.split(",");value = Arrays.stream(stars).map(String::trim).filter(StringUtils::hasText).map(Double::valueOf).map(String::valueOf).collect(Collectors.joining(","));value = "(" + value + ")";} else {value = null;}}return value;}

在这个方法中，根据传入的 fieldType 参数，对应处理了不同类型的值转换：

对于基本类型（如字符串、整数、长整数、双精度浮点数、布尔值等），直接通过 filter 对象的对应方法获取值。
对于日期类型（fieldType 为 6），采用 SimpleDateFormat 进行日期格式化处理。
对于其他复杂类型（例如逗号分隔的字符串列表，整数列表，双精度浮点数列表等），进行了特殊处理，将其转换为适合 SQL 查询的格式。

这样的设计使得在动态生成 SQL 查询语句时，可以根据字段类型准确地处理对应的值，避免了因数据类型不匹配而导致的错误或异常，同时也增强了代码的健壮性和可维护性。

总结

通过结合 JSON 数据和 FreeMarker 模板引擎，我们可以实现动态生成 SQL 查询语句的功能。这种方法使得查询条件可以灵活地根据用户需求动态生成，提高了系统的灵活性和可维护性。在实际应用中，可以根据具体的业务需求和数据模型，结合 FreeMarker 的强大功能，实现更加智能和高效的动态 SQL 查询功能。