SqlProvider.java
3.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package com.huaheng.api.erp.service;
import org.apache.ibatis.jdbc.SQL;
import java.time.LocalDate;
import java.util.Calendar;
public class SqlProvider {
public String selectCustomQuery(final String dynamicSql) {
// 传入的 SQL 必须做安全校验，防止 SQL 注入
if (!validateSql(dynamicSql)) {
throw new IllegalArgumentException("非法的 SQL 语句");
}
// 使用传入的 SQL 语句作为基础查询
return new SQL() {{
// 使用动态 SQL 拼接
SELECT("*"); // 这里可以根据需求进行调整
FROM("(" + dynamicSql + ") a"); // 将传入的 SQL 语句当作子查询
}}.toString();
}
/**
* 简单的 SQL 校验函数，确保 SQL 语句不会带来注入风险
*/
private boolean validateSql(String sql) {
// 可以添加更严格的 SQL 校验规则
String lowerSql = sql.toLowerCase();
if (lowerSql.contains("drop") || lowerSql.contains("delete") || lowerSql.contains("insert")) {
return false;
}
// 只允许以 "select" 开头的查询语句
return lowerSql.trim().startsWith("select");
}
// public String selectCustomQuery(final String boxCode, final String chipCode) {
// //获取当前年 取尾数决定去那个service做处理
// LocalDate currentDate = LocalDate.now(); // 获取当前日期
// int currentYear = currentDate.getYear(); // 获取当前年份
// int lastDigit = currentYear % 10; // 取尾数
//
// String table1 = "receipt_container_detail_" + (lastDigit-1);
// String table2 = "receipt_container_detail_" + lastDigit;
//
// return new SQL() {{
// SELECT("e.customerCode AS customerCode",
// "e.customerName AS customerName",
// "e.referCode AS invoiceNumber",
// "e.shipmentNote AS shipmentNote",
// "e.driver_leave_time AS confirmedArrivalTime",
// "e.driverName AS driverInformation");
// FROM("(SELECT * FROM " + table1 + " WHERE containerType='J' AND (chipCode1=#{chipCode} OR boxCode=#{boxCode}) " +
// "UNION ALL " +
// "SELECT * FROM " + table2 + " WHERE containerType='J' AND (chipCode1=#{chipCode} OR boxCode=#{boxCode})) a");
// LEFT_OUTER_JOIN("(SELECT a.boxCode, a.chipCode1, a.materialCode, a.materialName, a.containerCode, " +
// "CASE WHEN b.num=1 THEN '否' ELSE '是' END AS mix, '箱' AS Package, d.customerCode, d.customerName, " +
// "d.referCode, d.shipmentNote, " +
// "CONCAT(d.driverName, '/', IFNULL(e.car_number, ''), '/', IFNULL(e.driver_name, ''), '/', IFNULL(e.phone_number, '')) AS driverName, " +
// "e.driver_leave_time " +
// "FROM inventory_history_detail a " +
// "LEFT JOIN (SELECT containerCode, count(1) AS num FROM (SELECT DISTINCT containerCode, materialCode, `level`, color " +
// "FROM inventory_history_detail " +
// "WHERE containerCode IN (SELECT DISTINCT containerCode " +
// "FROM inventory_history_detail " +
// "WHERE left(containerCode, 1) <> 'J' AND (chipCode1=#{chipCode} OR boxCode=#{boxCode}))) a GROUP BY containerCode) b " +
// "ON b.containerCode = a.containerCode " +
// "LEFT JOIN receipt_header c ON c.code = a.receiptCode " +
// "LEFT JOIN shipment_header d ON d.code = c.shipmentCode " +
// "LEFT JOIN reservation_submission e ON e.order_number = d.referCode " +
// "WHERE left(a.containerCode, 1) <> 'J' AND (a.chipCode1=#{chipCode} OR a.boxCode=#{boxCode})) e " +
// "ON e.boxCode = a.boxCode AND e.chipCode1 = a.chipCode1");
// }}.toString();
// }
}