最近有个Es查询的需求，用户在前端输入sql语句直接拼条件，然后后台去查询。
因为es本身带有类sql查询，刚开始打算用sql查的，但是分页的limit只有一个查询条数，没有from和size，比如es可以通过类sql 的 limit 1000 一次查出来1000条数据，但是没法通过limit 900,100查出来第900到1000的数据，想实现就得先limit 1000再去截取后100条，没办法了，只能解析sql再去拼接条件了。这块的解析实现需要感谢以下两篇贴子，
灵感来源
灵感来源
https://elasticsearch.cn/article/114
用二叉树简直是神设计，当时为了最终结构困扰了我半天
https://blog.csdn.net/qzshiyongjie123/article/details/54341472
这篇关于具体代码实现讲的挺详细的，唯一的缺点是代码不全，我也是在这篇的基础上修改来的
代码实现
实现方法
import java.util.ArrayDeque;
import java.util.Queue;
/**
* @author fjl
* @date 2022/5/10
*/
public class ParseSqlUtil {
public static void main(String[] args) {
//String sql = "( income >= 10000 or income < 100 and gender = '女' ) or monent = 10 or name = '张三' and id = 1 ";
//String sql = "( ( income >= 10000 or income < 100 ) and ( gender = '女' or monent = 10 ) ) or ( name = '张三' ) and id = 1 ";
String sql = "( income < 100 and gender = '女' ) or monent = 10 ";
try {
TreeNode brake = paraseSqlTree(sql);
}catch (Exception e){
System.out.println("Sql语法有误，请检查后重试");
}
}
public static TreeNode paraseSqlTree(String sql) {
sql = sql.trim();
TreeNode tempNode = new TreeNode();
if (sql.startsWith("(")) {
//构建括号里的数据
TreeNode braketsNode = subBrackets(sql);
if (braketsNode != null) {
if (braketsNode.right != null && !(braketsNode.right.value.equals(""))) {
// 左边树
tempNode.left = paraseSqlTree(braketsNode.left.value.toString());
// 关系符
tempNode.value = braketsNode.value;
// 右边树
tempNode.right = paraseSqlTree(braketsNode.right.value.toString());
} else {
// 右边的是空的，就只处理左边的
tempNode = paraseSqlTree(braketsNode.left.value.toString());
}
return tempNode;
}
} else {
return dealGoOn(sql.split(" "), 0);
}
return null;
}
private static TreeNode subBrackets(String sql) {
TreeNode node = new TreeNode();
Queue<String> queue = new ArrayDeque<String>();
for (int i = 0; i < sql.length(); i++) {
char temchar = sql.charAt(i);
if (temchar == '(') {
queue.add("(");
}
if (temchar == ')') {
queue.poll();
if (queue.size() == 0) {
// 拆解数据
node.left = new TreeNode(sql.substring(1, i - 1));
node.right = new TreeNode(sql.substring(i + 1, sql.length()).trim());
if (null != node.right && !(node.right.value.equals(""))) {
String rightStr = node.right.value.toString();
boolean isAnd = rightStr.substring(0, 3).equals("and") ? true : false;
String value = "";
String right = "";
if (isAnd) {
value = "and";
right = rightStr.substring(4, rightStr.length()).trim();
} else {
value = "or";
right = rightStr.substring(3, rightStr.length()).trim();
}
node.right = new TreeNode(right);
node.value = new TreeNode(value);
}
return node;
}
}
}
// 括号分解有问题，sql语法错误
return node;
}
private static TreeNode dealGoOn(String[] stre, int j) {
TreeNode tempNode = new TreeNode();
for (int i = j; i < stre.length; i++) {
String temstr = stre[i];
if (temstr.matches("(and)|(or)")) {
// 先组装前面的条件树
TreeNode curNode = getNode(stre, i - 3, i - 1, i - 2);
TreeNode parentNode = new TreeNode();
parentNode.value = temstr;
if (tempNode != null) {
parentNode.left = curNode;
// 继续遍历组装
parentNode.right = dealGoOn(stre, i + 1);
}
return parentNode;
} else {
if (i + 2 == stre.length) {
// 循环完了，组装后面的树
TreeNode curNode = getNode(stre, i - 1, i + 1, i);
return curNode;
}
}
}
return null;
}
private static TreeNode getNode(String[] stre, int leftIndex, int rightIndex, int valueIndex) {
// 去除 包中文的 '' in 的 ()
String rightV= (stre[rightIndex].startsWith("'") || stre[rightIndex].startsWith("("))?stre[rightIndex].substring(1,stre[rightIndex].length()-1):stre[rightIndex];
TreeNode leftNode = new TreeNode(stre[leftIndex]);
TreeNode rightNode = new TreeNode(rightV);
TreeNode curNode = new TreeNode();
curNode.setLeft(leftNode);
curNode.setRight(rightNode);
curNode.setValue(new TreeNode(stre[valueIndex]));
return curNode;
}
}
TreeNode实体类
import lombok.Data;
/**
* @author fjl
* @date 2022/5/9
*/
@Data
public class TreeNode {
TreeNode left;
TreeNode right;
Object value;
public TreeNode() {
}
public TreeNode(String value) {
this.value = value;
}
@Override
public String toString() {
return "TreeNode{" +
"left=" + left +
", right=" + right +
", value=" + value +
'}';
}
}
最终结构
{
"left": {
"left": {
"left": {
"left": null,
"right": null,
"value": "income"
},
"right": {
"left": null,
"right": null,
"value": "100"
},
"value": {
"left": null,
"right": null,
"value": "<"
}
},
"right": {
"left": {
"left": null,
"right": null,
"value": "gender"
},
"right": {
"left": null,
"right": null,
"value": "女"
},
"value": {
"left": null,
"right": null,
"value": "="
}
},
"value": "and"
},
"right": {
"left": {
"left": null,
"right": null,
"value": "monent"
},
"right": {
"left": null,
"right": null,
"value": "10"
},
"value": {
"left": null,
"right": null,
"value": "="
}
},
"value": {
"left": null,
"right": null,
"value": "or"
}
}
需要注意的是关系符合字段之间只能有一个空格，多了的话会出问题，这个现阶段得让前端控制一下。
转成Json后，在后台就可以用QueryBuilders去遍历组装查询条件了
