场景
这个工具是将查询sql where条件，拆分成数组对象，具体效果如下
例如
select * from dual where id=1 and name between 2 and 4 or (value =3 and stash in('we'))
解析为：
[
{
"values":[
"1"
],
"column":"id",
"operator":"="
},
{
"link":"AND"
},
{
"values":[
"2",
"4"
],
"column":"name",
"operator":"BETWEEN"
},
{
"link":"OR"
},
[
{
"values":[
"3"
],
"column":"value",
"operator":"="
},
{
"link":"AND"
},
{
"values":[
"'we'"
],
"column":"stash",
"operator":"IN"
}
]
]
代码
com.github.jsqlparser:jsqlparser:0.9.4
import com.google.common.base.Joiner;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;
import com.madai.rong.common.Assert;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.Andexpression;
import net.sf.jsqlparser.expression.operators.conditional.Orexpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import org.apache.commons.lang.StringUtils;
import java.util.Iterator;
import java.util.List;
public class expressionVisitorCondition extends expressionVisitorAdapter {
private static final String HADES_F_EXISTS = "HADES_F_EXISTS";
private static final String HAVE_OPERATOR = "HAVE_ANY";
private static final String NOT_HAVE_OPERATOR = "NOT_HAVE";
private WhereItem whereItem;
private Character sign;
private List
@Slf4j
public class SqlUtil {
public static List parseSQLBody(String sql){
//解析sql
Select select;
try {
select = (Select) CCJSqlParserUtil.parse(sql);
} catch (JSQLParserException e) {
log.error(Logger.formatMessage("解析sql失败:{}", sql), e);
throw new RuntimeException(e);
}
//SELECT
PlainSelect plain = (PlainSelect) select.getSelectBody();
expression where_expression = plain.getWhere();
if(where_expression == null){
return Lists.newArrayList();
}
List list = Lists.newArrayList();
//自定义解析器
expressionVisitorCondition expressionDeParser = new expressionVisitorCondition(list);
where_expression.accept(expressionDeParser);
log.info("sql:{},解析为:{}", sql, JSONArray.toJSON(list));
return list;
}
}
