com.tmsps.ne4spring.orm.param.NeParamTools Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of ne4Spring Show documentation
NE For Spring is good :)
There is a newer version: 999.0.0.0
package com.tmsps.ne4spring.orm.param;

import java.util.List;

public class NeParamTools {

	/**
	 * 处理sql语言
	 */
	public static String handleSql(String sql, NeParamList params) {
		// sql = sql.replaceAll("\\(\\s*[?]\\s*\\)", "[?]");
		//fix toLowerCase 导致的参数全部小写，致使大小写敏感时导致查询问题
		//sql = sql.replace("[", "@#").replace("]", "#@").toLowerCase();
		sql = sql.replace("[", "@#").replace("]", "#@");
		StringBuilder sb = new StringBuilder(sql);
		
		List list = params.getParamList();

		int indexOf = 0;
		for (int index = 0; index < list.size(); index++) {
			NeParam p = list.get(index);
			indexOf = sb.indexOf("?", indexOf);
			if (!p.getIsNull()) {
				indexOf += 1;
				continue;
			}
			int indexOfL = sb.lastIndexOf("and", indexOf);
			if (indexOfL == -1) {
				indexOfL = sb.lastIndexOf("where", indexOf) + 5 + 1;
			} else {
				indexOfL += 3 + 1;
			}

			String conditionL = sb.substring(indexOfL, indexOf);
			// System.err.println(conditionL);
			// System.err.println(containStr(conditionL, '(') + ":" +
			// containStr(conditionL, ')'));
			int l = containStr(conditionL, '(');
			int r = containStr(conditionL, ')');

			int indexOfR = indexOf + 1;
			for (; r < l; r++) {
				indexOfR = sb.indexOf(")", indexOfR) + 1;
			}

			// String condition = sb.substring(indexOfL, indexOfR);
			// System.err.println(condition);

			sb.insert(indexOfL, "[");
			sb.insert(indexOfR + 1, "]");

			indexOf += 3;
		}

		// System.err.println("-标记后的sql-->" + sb);

		// 去除大括号
		String dkhReg = "\\[[^\\]]*\\]";
		String made = sb.toString().replaceAll(dkhReg + "\\s+(and)", "").replaceAll("(and)\\s+" + dkhReg, "").replaceAll("(where)\\s+" + dkhReg, "");
		made = made.replace("@#","[").replace("#@","]");
		return made;
	}

	public static int containStr(String str, char ch) {
		int cnt = 0;
		for (int i = 0; i < str.length(); i++) {
			if (str.charAt(i) == ch) {
				cnt++;
			}
		}
		return cnt;
	}

	public static void main2(String[] args) {
		String sql5 = "select * from t_fk_user t where t.status=0 and (to_days(t.created)=to_days(?)) and (t.name = ?) and ({fn TIMESTAMPDIFF(SQL_TSI_DAY,t.created,?)}=0) order by t.created";
		NeParamList params5 = new NeParamList();
		params5.add("");
		params5.add("x");
		params5.add("x");
		System.err.println("sql5-->" + NeParamTools.handleSql(sql5, params5));

	}

	// 测试函数
	public static void main(String[] args) {
		String sql1 = "select * from t_fk_user t where t.status=0 and (t.uname like ?) and (t.name like ?) and t.user_flag='已认证'";
		NeParamList params1 = new NeParamList();
		params1.add("");
		params1.add("");
		System.err.println(NeParamTools.handleSql(sql1, params1));

		String sql2 = "select * from t_fk_user t where (t.uname like ?) and (t.name = ?) and t.user_flag='已认证'";
		NeParamList params2 = new NeParamList();
		params2.add("");
		params2.add("");
		System.err.println(NeParamTools.handleSql(sql2, params2));

		String sql3 = "select * from t_fk_user t where (t.uname like ?) and (t.name = ?) order by t.created";
		NeParamList params3 = new NeParamList();
		params3.add("");
		params3.add("");
		System.err.println(NeParamTools.handleSql(sql3, params3));

		String sql4 = "select * from t_fk_user t where (t.uname like ?) and t.status=0 and (t.name = ?) order by t.created";
		NeParamList params4 = new NeParamList();
		params4.add("");
		params4.add("");
		System.err.println(NeParamTools.handleSql(sql4, params4));

		String sql5 = "select * from t_fk_user t where t.status=0 and (to_days(t.created)=to_days(?)) and (t.name = ?) order by t.created";
		NeParamList params5 = new NeParamList();
		params5.add("");
		params5.add("");
		System.err.println("sql5-->" + NeParamTools.handleSql(sql5, params5));

		String sql6 = "select * from t_fk_user t where t.status=0 and (t.id in (?)) and (t.name = ?)";
		NeParamList params6 = new NeParamList();
		params6.add("'1','2','3'");
		params6.add("1");
		System.err.println(NeParamTools.handleSql(sql6, params6));

		String sql = "select * from t_fk_user t where t.status=0 and (t.uname like ?) and (t.name = ?)";
		NeParamList params = new NeParamList();
		params.add("");
		params.add("1");
		System.err.println(NeParamTools.handleSql(sql, params));
	}
}