算法需求描述
condition:string类型,JSON Array格式,可为空,当做查询条件,与接口1中的index配合使用。Array中各元素间为“或”关系,元素内的各属性间为“且”关系。元素内的属性定义:key为接口1中index设置的属性,value为JSON Object,该JSON Object的key为比较符,value为某数值(包括字符串)。举例说明:
假设某2条记录appendData时,index的值分别为:
{“gender”:”男”,”age”:26,”income”:10001}
{“gender”:”女”,”age”:30,”income”:9999}
现在需要查询收入大于10000的男性或年龄大于等于30岁的女性,则condition为:
[{“income”:{“gt”:10000},”gender”:{“eq”:”男”}},{“age”:{“ge”:30},”gender”:{“eq”:”女”}}]
若对应于SQL的条件则为 :
(income >10000 and gender=’男’) or (age>=30 and gender=’女’)
比较符 含义
eq 等于
ne 不等于
gt 大于
ge 大于等于
lt 小于
le 小于等于
算法分析
本文是将常用的sql转换成json的方式,sql中可以任意的and or,以及小括号嵌套,比如
(
(income >= 10000 OR income < 100)
AND (
gender = '女'
OR monent = 12
OR NAME = 'l.isi'
)
)
OR (NAME = '张三' AND ID = 1)
为了方便拆串,将上述sql转换为一行,可以有任意的and or 逻辑运算操作,不包含 order page pagecount 字段。小括号以及运算符需要有前后空格 比如( name = ‘张三’ or id = 1 )and ( monent = 12 or name = ‘l.isi’ )
( ( income >= 10000 or income < 100 ) and ( gender = '女' or monent = 12 or name = 'l.isi' ) ) or ( name = '张三' and id = 1 )
截取括号里边的内容
大体分析一下,第一个算法是截取括号里边的内容,比如上述内容,截取最外层的第一个括号,上述sql可表示为
左边:( income >= 10000 or income < 100 ) and ( gender = '女' or monent = 12 or name = 'l.isi' )
右边:or ( name = '张三' and id = 1 )
算法代码描述
截取括号最重要的是使用栈,遇到”(”入栈,第一个需要记录位置,遇到”)”出栈,出栈以后判断栈中元素个数,如果为0,表示第一个括号截取成功,把第一个括号中的内容截取出来,剩下的放到另一个地方。本事例用二叉树作为数据结构,左节点是第一个括号里边的,右节点为截取第一个括号后剩下的内容
/**
* 对第一层括号进行拆分
* 左边节点放置去掉第一层括号的数据,右边节点放置sql - 左节点sql
* @param sql
* @return
*/
private static TreeNode subBrackets(String sql) {
TreeNode node = new TreeNode();
Queue<String> queue = new ArrayDeque<String>();
int left = 0;
for(int i=0;i<sql.length();i++){
char temchar = sql.charAt(i);
if( temchar =='('){
if(queue.size() ==0){
left = i;
}
queue.add("(");
}
if( temchar ==')'){
queue.poll();
if(queue.size() == 0){
node.value = sql;
node.left = new TreeNode(sql.substring(1,i-1));
node.right = new TreeNode(sql.substring(i+1,sql.length()).trim());
return node;
}
}
}
return node;
}
TreeNode,是普通的二叉树,数据结构如下边的代码
static class TreeNode {
TreeNode left;
TreeNode right;
Object value;
public TreeNode() {
}
public TreeNode(String value) {
this.value = value;
}
public TreeNode getLeft() {
return left;
}
public void setLeft(TreeNode left) {
this.left = left;
}
public TreeNode getRight() {
return right;
}
public void setRight(TreeNode right) {
this.right = right;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
@Override
public String toString() {
return value.toString();
}
}
以and or ,比较运算(>,< ,=,……),将sql转换为树
为什么要将sql转换成树,计算机不好识别 name = ‘张三’,此时要转换成
"left": {
"left": {
"value": "gender"
},
" right": {
"value": "’男’"
},
"value": "="
}
算法表示
/**
* 构建二叉树,叶子节点为数据值,父节点为运算符,如果最后构建的数据为非满二叉树,则sql数据格式错误
* "left": {
* "left": {
* "value": "gender"
* },
* " right": {
* "value": "’男’"
* },
* "value": "="
* },
* 左添加,右递归
* @param sql
* @return
*/
private 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());
TreeNode tempRightNode = paraseSqlTree(braketsNode.right.value.toString());
if(tempRightNode.left != null && tempRightNode.left.value != null){
tempNode.right = tempRightNode;
}else {
tempNode.right = tempRightNode.right;
tempNode.value = tempRightNode.value;
}
}else {
tempNode = paraseSqlTree(braketsNode.left.value.toString());
}
return tempNode;
}
}else {
return paraseLogicSql(sql);
}
return null;
}
/**
* 括号以外的逻辑处理数据
* 第一个逻辑符号 (and|or)左边部分添加新的节点,右边部分递归调用
* sql 逻辑处理 比如 and or > =
* @param sql
* 要处理的sql语句
*/
private static TreeNode paraseLogicSql(String sql) {
String[] stre =sql.split(" ");
TreeNode tempNode = new TreeNode();
for(int i = 0;i<stre.length;i++){
String temstr = stre[i];
if(temstr.matches("(and)|(or)")){
TreeNode parentNode = new TreeNode();
parentNode.value = temstr;
if(tempNode != null){
parentNode.left = tempNode;
parentNode.right = paraseSqlTree(subleaftsql(stre,i).trim());
}
return parentNode;
}else {
if(Compare.matchMean(temstr) != null){
tempNode.left = new TreeNode(stre[i-1].trim());
TreeNode rightNode = new TreeNode();
String trimValue = stre[i+1].trim();
if(trimValue.startsWith("\'")) {
rightNode.value = trimValue.substring(1,trimValue.length()-1);
}else {
try{
rightNode.value = Double.valueOf(trimValue);
}catch (Exception e){
rightNode.value = trimValue;
}
}
tempNode.right = rightNode;
tempNode.value = temstr;
if(i+2 == stre.length){
return tempNode;
}
}
}
}
return tempNode;
}
把树中的内容转换成JsonArray,此时想到了离散数学的合取范式
由于化为合取范式时需要排除重复的元素,用Set标识
为了递归方便,如果是or连接的,Map中”or”为key,value为or拼接完以后的Set数据
代码标识
static Map<String,Object> paraseLimitSqlCondition(TreeNode node){
Map<String,Object> nodeMap = new HashedMap();
if(node.value.equals("and")){
Map<String,Object> left = paraseLimitSqlCondition(node.left);
Map<String,Object> right = paraseLimitSqlCondition(node.right);
//都是 and连接
if(left.get("or")==null && right.get("or")==null ) {
left.putAll(right);
return left;
}
//左边and,右边or
if(left.get("or")==null && right.get("or")!=null ){
Set<Map<String,Object>> rightOr = (Set<Map<String,Object>>)right.get("or");
Set<Map<String,Object>> resultSet = new HashSet<>();
for(Map<String,Object> rightEach:rightOr){
rightEach.putAll(left);
resultSet.add(rightEach);
}
nodeMap.put("or",resultSet);
return nodeMap;
}
//右边and,左边or
if(right.get("or")==null && left.get("or")!=null ){
Set<Map<String,Object>> leftOr = (Set<Map<String,Object>>)left.get("or");
Set<Map<String,Object>> resultSet = new HashSet<>();
for(Map<String,Object> leftEach:leftOr){
leftEach.putAll(right);
resultSet.add(leftEach);
}
nodeMap.put("or",resultSet);
return nodeMap;
}
//两边都是or
if(left.get("or")!=null && right.get("or")!=null ){
Set<Map<String,Object>> leftOr = (Set<Map<String,Object>>)left.get("or");
Set<Map<String,Object>> rightOr = (Set<Map<String,Object>>)right.get("or");
Set<Map<String,Object>> resultSet = new HashSet<>();
for(Map<String,Object> leftEach:leftOr){
for(Map<String,Object> rightEach:rightOr){
Map<String,Object> ecahMap= new HashMap<>();
ecahMap.putAll(leftEach);
ecahMap.putAll(rightEach);
resultSet.add(ecahMap);
}
}
nodeMap.put("or",resultSet);
return nodeMap;
}
}
if(node.value.equals("or")){
Set< Map<String,Object>> resultSet = new HashSet<>();
Map<String,Object> left = paraseLimitSqlCondition(node.left);
Map<String,Object> right = paraseLimitSqlCondition(node.right);
if(left.get("or") == null && right.get("or") == null){
resultSet.add(left);
resultSet.add(right);
nodeMap.put("or",resultSet);
return nodeMap;
}
if(left.get("or") != null && right.get("or") == null){
Set< Map<String,Object>> set = (Set< Map<String,Object>>)left.get("or");
set.add(right);
nodeMap.put("or",set);
return nodeMap;
}
if(left.get("or") == null && right.get("or") != null){
Set< Map<String,Object>> set = (Set< Map<String,Object>>)right.get("or");
set.add(left);
nodeMap.put("or",set);
return nodeMap;
}
if(left.get("or") == null && right.get("or") == null){
Set< Map<String,Object>> leftset = (Set< Map<String,Object>>)left.get("or");
Set< Map<String,Object>> rightset = (Set< Map<String,Object>>)left.get("or");
leftset.addAll(rightset);
nodeMap.put("or",leftset);
return nodeMap;
}
}
if(Compare.matchMean(node.value.toString())!=null){
return ( createBaseCondition(node));
}
return null;
}