public class ExprssionHandlerImpl implements ExpressionHandler {
/*
* (no Javadoc) <p>Title: handleExpression</p> <p>Description:
* 过滤表达式中为0的除数,同时将表达式转化为合法的sql</p>
* @param expression
* @return
* @see
* com.reiyen.htz.database.generateview.ExpressionHandler#handleExpression
* (java.lang.String)
*/
@Override
public String handleExpression(String expression) {
String sql = "";
Set<String> set = dividerFilter(expression);
// 如果表达式没有除数
if (set.size() <= 0) {
sql = expression;
}
else {
StringBuffer result = new StringBuffer();
Iterator<String> iterator = set.iterator();
while (iterator.hasNext()) {
result.append("nullif(" + iterator.next() + ",0)*");
}
result.deleteCharAt(result.length() - 1);
// 不能用case result when null then null else derivedIndex end as
sql = " case when " + result.toString()
+ " is null then null else " + expression
+ " end as ";
}
return sql;
}
/**
* <p>
* Title: dividerFilter
* </p>
* <p>
* Description: 过滤表达式中的除号"/",得到所有的除数集合
* </p>
*
* @param expression
* @return
*/
private Set<String> dividerFilter(String expression) {
// 去掉原始字符串一个或多个空格
expression = expression.replaceAll("\\s+", "");
int index = -1;
Comparator<String> comparator = new MyComparator();
// 利用treeSet将长度短的除数排到前面来先判断,如:表达戒a\(b+c\d),则将除数b移到前除数b+c\d前面来,防止出现除数为0的情况出现
Set<String> dividers = new TreeSet<String>(comparator);
// 循环检查除号进行过滤,直到检查到过滤后的表达式中没有除号'/',同时使用index将除号的位置标记
while ((index = expression.indexOf("/", index + 1)) > 0) {
// 如果除号后面紧跟的是左括号'(',即除数是一个有括号包围的表达式,如:/()。'('这是除数的起点
if (expression.charAt(index + 1) == '(') {
// 将位置标记点移动到左括号'('后面的字符上,
int next = index + 2;
// 用一个计数器来记录(的数目
int count = 1;
// 循环检查括号,检查到左括号'('计数器就加1,如果检查到左括号')',则计数器就减1
while (count != 0) {
if (expression.charAt(next) == '(') {
count++;
}
else if (expression.charAt(next) == ')') {
count--;
}
next++;
}
// 得到第一表达式中第一层的除数
String divider = expression.substring(index + 1, next);
// 使用递归逐层得到除数
int index1 = divider.indexOf("/", 0);
if (index1 > 0) {
Set<String> set = dividerFilter(divider);
dividers.addAll(set);
}
dividers.add(divider);
}
else {
// 除数中没有括号'()',但可能有右括号,如:表达戒a\(b+c\d)中的d);所以要判断右括号')'的情况
int next = index + 1;
do {
char c = expression.charAt(next);
if (c == '+' || c == '-' || c == '*' || c == '/'
|| c == ')')
break;
else
next++;
} while (next < expression.length());
dividers.add(expression.substring(index + 1, next));
}
}
return dividers;
}
public static void main(String args[]) {
ExpressionHandler handler = new ExprssionHandlerImpl();
String expression = "a/b + (c/d + f/(d+e))/f + g/p";
String result = handler.handleExpression(expression);
System.out.println(result);
}
}
比较器:
public class MyComparator implements Comparator<String> {
public int compare(String arg0, String arg1) {
int result = 0;
int len1 = arg0.length();
int len2 = arg1.length();
if (len1 < len2) {
result = -1;
}
else if (len1 > len2) {
result = 1;
}
else if (len1 == len2) {
if (arg0.equals(arg1)) {
result = 0;
}
else {
result = arg0.compareTo(arg1);
}
}
return result;
}
}
比如表达式:"a/b + (c/d + f/(d+e))/f + g/p"
使用此程序解析后输出的sql如下所示:
case when nullif(b,0)*nullif(d,0)*nullif(f,0)*nullif(p,0)*nullif((d+e),0) is null then null else a/b + (c/d + f/(d+e))/f + g/p end as