sql 分析 依赖beanutils

你还在为sql语句的拼接而烦恼吗？

sql语句支持表达式了！

package com.newland.bi.webservice.common.manage;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.commons.beanutils.BeanUtils;import com.newland.bi.util.common.Function;import com.newland.bi.webservice.common.model.SQLBean;/** * 解析sql工具类 *  将 { condition ?[condition_ture]:[condition_false]} 或者 { condition ?[condition_ture]} 根据参数进行解析 *  <br/>condition 可以是bean中的一个属性名eg:name，如果属性值不为null或者不等于""就返回condition_true语句，否则返回condition_false *  <br/>也可以是字符串比较的表达式 eg:name ==|!=|>|<|>=|<= wch *  <br/>支持条件的嵌套 *  <br/>select * from user where 1=1 {name?[ and name=:name]} {name==wch?[name=:name]:[id=:name]} *  <br/>其中表达式中的name要是bean中属性 * @author wch * @version 1.0 * @since 2014/05/19 */@SuppressWarnings("unchecked")public class SQLUtils {/** * 将普通javabean转换为map,属性的名值对 * @param bean * @return map bean的属性的键值对 */public static Map<String, String> bean2Map(Object bean){Map<String, String> map = new HashMap<String, String>();try {map = BeanUtils.describe(bean);} catch (Exception e) {e.printStackTrace();} return map;}/** * 进行解析sql语句 * <br/>{ condition ?[condition_ture]:[condition_false]} 或者 { condition ?[condition_ture]} * <br/> * @return 根据条件表达式替换后的语句 */public static String analyzeSql(Map<String, String> paramMap, String sql) {if (sql == null) {return sql;}int start = 0, end = 1;/*循环查找sql中的表达式进行解析*/while (true) { start = sql.lastIndexOf("{"); // 条件起始位置end = sql.indexOf("}",start); // 条件结束位置if (end <= start) { // 没有条件return sql;}String braces = sql.substring(start, end + 1);// 取得大括号的内容{...}String braces_trim = braces;// 处理分界符之间的空格braces_trim = braces_trim.replaceAll("\\?\\s*\\[", "?[");braces_trim = braces_trim.replaceAll("\\]\\s*:\\s*\\[", "]:[");braces_trim = braces_trim.replaceAll("\\]\\s*\\}", "]}");int question_mark_index = braces_trim.indexOf("?["); // 获取问号的位置int colon_index = braces_trim.indexOf("]:["); // 获取冒号的位置int end_mark_index = braces_trim.length() - 2; // ]} 的位置if (question_mark_index == -1) {// 如果不存在问号就不进行解析return sql;}String condition = braces_trim.substring(1, question_mark_index); // 获取条件从{到?String sql_true = "", sql_false = "";if (colon_index == -1) { // 不存在冒号sql_true = braces_trim.substring(question_mark_index + 2,end_mark_index);// 条件为真时的语句} else {sql_true = braces_trim.substring(question_mark_index + 2,colon_index);// 条件为真时的语句sql_false = braces_trim.substring(colon_index + 3,end_mark_index);// 添加为假时的语句}if (isConditionTrue(paramMap, condition)) { // 判断条件是否为空sql = sql.replace(braces, addBothSpace(sql_true));} else {sql = sql.replace(braces, addBothSpace(sql_false));}}}/** * 进行解析sql语句 * <br/>{ condition ?[condition_ture]:[condition_false]} 或者 { condition ?[condition_ture]} * @return 根据条件表达式替换后的语句 */public static String analyzeSql(Object bean,String sql){return analyzeSql(bean2Map(bean), sql);}/** * 如果str不为空，在str的两端添加空格，如果不存在的话 * @param str * @return str  */public static String addBothSpace(String str){if (str == null) {return str;}/*添加前空格*/if (!str.startsWith(" ")) {str = " "+str;}/*添加后空格*/if (!str.endsWith(" ")) {str = str + " ";}return str;}/** * 根据map中的值判断condition的真假 * @param map * @param condition * @return */private static boolean isConditionTrue(Map<String, String> map,String condition){/*为空直接返回false*/if (condition == null || condition.length() == 0) {return false;}//去除空格condition = condition.trim();//匹配一个表达式Pattern pattern = Pattern.compile("(\\w+)(?:\\s*([!><=]{1,2})\\s*(\\w+))?");//进行匹配Matcher matcher = pattern.matcher(condition);if (!matcher.matches()) {return false; //匹配不成功，直接返回false}else { //匹配成功String oper_left = matcher.group(1); //捕获组1，左边云算数String oper_symbol = matcher.group(2);String oper_right = matcher.group(3);//属性对应的值String value = map.get(oper_left); if (value == null) { return false;}/*存在操作符*/if (oper_symbol != null && oper_right != null) {return doOperate(value, oper_symbol, oper_right);} else {return true;}}}/** * 进行 left symbol right的运算 * @param left * @param symbol * @param right * @return */private static boolean doOperate(String left,String symbol,String right){/*不等于*/if ("!=".equals(symbol) || "!".equals(symbol)) {return left.compareToIgnoreCase(right) != 0;}/*等于*/if ("==".equals(symbol) || "=".equals(symbol)) {return left.compareToIgnoreCase(right) == 0;}/*大于等于*/if (">=".equals(symbol)) {return left.compareToIgnoreCase(right) >= 0;}/*小于等于*/if ("<=".equals(symbol)) {return left.compareToIgnoreCase(right) <= 0;}/*大于*/if (">".equals(symbol) ) {return left.compareToIgnoreCase(right) > 0;}/*小于*/if ("<".equals(symbol)) {return left.compareToIgnoreCase(right) < 0;}return false;}/** * 将查询sql语句中的查询的列名,不支持* * @param sql * @return metaDataList */public static List<String> getMetaData(String sql){List<String> metaData = new ArrayList<String>();if (sql == null) {return metaData;}Pattern col_pattern = Pattern.compile("(\\w+)\\s*,"); // 获取列名的patternPattern row_pattern = Pattern.compile("select(.*?)from"); // 获取所有列名的字符串Matcher rowMatcher = row_pattern.matcher(sql);if (rowMatcher.find()) {String row = rowMatcher.group(1) + ",";row = removeBrackets(row);Matcher colMatcher = col_pattern.matcher(row);// 添加列名while (colMatcher.find()) {metaData.add(colMatcher.group(1));}}return metaData;}/** * 将str 中括号里的内容去掉 * @param str * @return 去掉括号里的内容 */private static String removeBrackets(String str){if (str == null) {return str;}StringBuilder sb = new StringBuilder(str);int start = -1,end = -1;/*从后向前查找，支持嵌套*/start = sb.lastIndexOf("(");while (start > -1) {end = sb.indexOf(")", start);if (end > -1) {//删除掉括号的内容sb.delete(start, end+1); }start = sb.lastIndexOf("(");}return sb.toString();}public static void main(String[] args) {String sql = "{id?[sql_id is not null]:[{sql==wch?[sql_id is wch]:[not wch]}]}";SQLBean bean = new SQLBean();bean.setSql("wc");System.out.println("#"+analyzeSql(bean, sql)+"#");}}