parsesqlpara.java

采用web2.0技术,采用动态标签,sql语句全部存储在数据库里面.开发速度快.

/**
 * <p>Title: 简单SQL解析</p>
   @author 李光明
   @version 1.1
*/
package com.sztheater.framework.util;
import java.util.*;
import java.sql.*;

public class ParseSQLPara {

	private String  m_id    = null;//服务ID编号
	private String  m_desc  = null;//服务描述
	private HashMap m_paras = null;//服务参数信息
	private HashMap m_sqls  = null;//服务SQL语句

	public void setID(String s_id){
		if(s_id==null) s_id = "";
		this.m_id =s_id.trim();
	}
	public void setSQL(String s_seq,String s_sql,String return_set,String func_desc){
		if(m_sqls==null) m_sqls = new HashMap();
		if(m_sqls==null) return ;
		if(s_seq==null) s_seq = "";
		if(func_desc==null) func_desc = "";
		if(return_set==null) return_set = "";
		func_desc  = func_desc.trim();
		return_set = return_set.trim();
		return_set = return_set.trim();
		
		if(return_set.equals("")) return_set = "data_list";
		s_seq = s_seq.trim().toLowerCase();
		HashMap hashSQL = (HashMap)m_sqls.get(s_seq);
		
		if(hashSQL==null) {
			hashSQL = new HashMap();
			hashSQL.put( "seq" , s_seq);
			m_sqls.put(s_seq,hashSQL);
		}
		hashSQL.put( "sql"        , s_sql       );
		hashSQL.put( "return_set" , return_set  );
		hashSQL.put( "func_desc"  , func_desc);
	}

	/*压入参数信息*/
	public void setParam(String p_name,String p_type,String p_desc){
		HashMap aHash = null;
		if(p_name ==null) return ;
		p_name = p_name.trim();

		if(p_desc==null || p_desc.trim().equals("")) p_desc = p_name;
		if(p_type==null || p_type.trim().equals("")) p_type = "string";

		p_type = SqlFunc.getDataType(p_type);

		if(this.m_paras==null) m_paras = new HashMap();

		aHash = (HashMap)m_paras.get(p_name);
		if(aHash==null) {
			aHash = new HashMap();
			m_paras.put(p_name,aHash);
		}
		aHash.put("name" , p_name);
		aHash.put("type" , p_type);
		aHash.put("desc" , p_desc);
	}

	public String getID(){
		if(this.m_id==null) this.m_id = "";
		return this.m_id;
	}

	public HashMap getArgs(){
		return this.m_paras;
	}
	public HashMap getSQLS(){
		return this.m_sqls;
	}
	public static String getParaLogic(String f_name)throws Exception{
		String f_logic = "";
		if(f_name.startsWith("%")) {
			f_logic = "%like";
		}
		if(f_name.endsWith  ("%")) {
			if(f_logic.equals("")){
				f_logic = "like%";
			}else{
				f_logic = "%like%";
			}
		}
		if(f_logic.equals("")) f_logic = "=";
		return f_logic;
	}
	public static String getParaName(String f_name)throws Exception{
		if(f_name==null) f_name = "";
		f_name = f_name.trim();
		if(f_name.startsWith("%")) {
			f_name  = f_name.substring(1);
		}
		if(f_name.endsWith  ("%")) {
			f_name = f_name.substring(0,f_name.length()-1);
		}
		return f_name.trim();
	}
	public static String getExecSql(String strSQL,HashMap hashArgs,HashMap hashParam,HashMap hashUser)throws Exception{
		if(strSQL==null || strSQL.trim().equals("")) return "";
		
		StringBuffer sbSQL = new StringBuffer();

		String f_name  = null;
		String rs_name  = null;
		String f_value = null;
		String f_logic = null;
		String f_desc  = null;
		String f_type  = null;
		HashMap fHash  = null;
		int    iPosPrev = 0;
		int    iPosLast = 0;
		int    iPos     = 0;
		strSQL = getCondSql(strSQL,hashArgs,hashParam, hashUser);

		if(strSQL==null) return "";
		while  (!strSQL.equals("")){
			iPosPrev = strSQL.indexOf("{");
			iPosLast = strSQL.indexOf("}");

			if(iPosPrev<0 && iPosLast<0) break;
			if(iPosPrev<0) throw new Exception("-9030301 {}不匹配：{的个数大于}的个数");
			if(iPosLast<0) throw new Exception("-9030302 {}不匹配：}的个数大于{的个数");
			sbSQL.append(strSQL.substring(0,iPosPrev));

			f_name  = strSQL.substring(iPosPrev+1,iPosLast);
			strSQL  = strSQL.substring(iPosLast+1);
			f_name  = f_name.trim();
			f_logic = getParaLogic(f_name);
			f_name  = getParaName (f_name);
			if(f_name.equals("")) {
				throw new Exception("-9030301 {}中间配置的参数名为空");
			}
			fHash = null;
			if(hashArgs!=null)fHash = (HashMap)hashArgs.get(f_name);
			if(fHash==null){
				throw new Exception("-9030302 动态SQL服务没有配置["+f_name+"]的参数信息");
			}

			f_type = (String)fHash.get("type");
			f_desc = (String)fHash.get("desc");
			if(hashParam!=null) f_value = (String)hashParam.get(f_name);
			
			iPos   = f_name.indexOf(".");
			rs_name= "";
			if(iPos>-1){
				rs_name = f_name.substring(0,iPos);
				f_name  = f_name.substring(iPos+1);
			}
			if(rs_name.equals("user")) {
				if(hashUser!=null) f_value = (String)hashUser.get(f_name);
			}
			if(f_value==null ) f_value = "";
			//数据类型校验
			f_value = SqlFunc.getSqlValue(f_desc,f_type,f_value,f_logic);
			sbSQL.append(f_value);
		}
		sbSQL.append(strSQL);
		String strRet = sbSQL.toString();
		sbSQL.delete(0,sbSQL.length());
		sbSQL.setLength(0);
		sbSQL = null;
		return strRet;
	}
	public static String getCondSql(String str_sql,HashMap hashArgs,HashMap hashParam,HashMap hashUser)throws Exception{
		//参数校验
		if(str_sql==null || str_sql.trim().equals("")) return "";
		if(str_sql==null) return "";
		StringBuffer sbSQL = new StringBuffer();
		//字符串处理,从前面向后面查找[],并进行处理

		int iPos     = 0;
		int iPosPrev = 0;
		int iPosLast = 0;
		int iStrLen  = 0;
		int iSubLen  = 0;

		String f_expr  = null;
		String s_expr  = null;
		String f_name  = null;
		String rs_name = null;
		String f_value = null;
		String f_logic = null;
		String f_desc  = null;
		String f_type  = null;
		HashMap fHash  = null;


		while  (!str_sql.equals("")){
			iPosPrev = str_sql.indexOf("[");
			iPosLast = str_sql.indexOf("]");
			if(iPosPrev<0 && iPosLast<0) break;
			if(iPosPrev<0) throw new Exception("-9030301 []不匹配：[的个数大于]的个数");
			if(iPosLast<0) throw new Exception("-9030302 []不匹配：]的个数大于[的个数");

			sbSQL.append(str_sql.substring(0,iPosPrev));
			s_expr   = str_sql.substring(iPosPrev+1,iPosLast);
			str_sql  = str_sql.substring(iPosLast+1);
			//分析条件
			f_expr = "";

			while(!s_expr.equals("")){
				iPosPrev = s_expr.indexOf("{");
				iPosLast = s_expr.indexOf("}");
				if(iPosPrev<0 && iPosLast<0) break;
				if(iPosPrev<0) throw new Exception("-9030301 {}不匹配：{的个数大于}的个数");
				if(iPosLast<0) throw new Exception("-9030302 {}不匹配：}的个数大于{的个数");


				f_expr += s_expr.substring(0,iPosPrev);
				f_name  = s_expr.substring(iPosPrev+1,iPosLast);
				s_expr  = s_expr.substring(iPosLast+1);
				f_name  = f_name.trim();
				f_logic = getParaLogic(f_name);
				f_name  = getParaName (f_name);

				fHash = null;
				if(hashArgs!=null) fHash = (HashMap)hashArgs.get(f_name);
				if(fHash==null){
					throw new Exception("-9030302 动态SQL服务没有配置["+f_name+"]的参数信息");
				}

				f_type = (String)fHash.get("type");
				f_desc = (String)fHash.get("desc");
				rs_name= "";
				iPos   = f_name.indexOf(".");
				if(iPos>-1){
					rs_name= f_name.substring(0 ,iPos);
					f_name = f_name.substring(iPos+1);
				}
				rs_name= rs_name.trim().toLowerCase();
				if(hashParam!=null) f_value = (String)hashParam.get(f_name);
				if(rs_name.equals("user")){
					if(hashUser!=null) f_value = (String)hashUser.get(f_name);
				}
				if(f_value==null || f_value.trim().equals("")) {
					f_expr = "";
					break;
				}
				//数据类型校验
				f_value = SqlFunc.getSqlValue(f_desc,f_type,f_value,f_logic);
				f_expr += f_value;
			}
			if(f_expr.equals("")) continue;
			sbSQL.append(f_expr);
			sbSQL.append(s_expr);
		}

		sbSQL.append(str_sql);
		String strSQL = sbSQL.toString();
		sbSQL.delete(0,sbSQL.length());
		sbSQL.setLength(0);
		sbSQL = null;
		return strSQL;
	}
	public static void main(String args[])	{
		String strSQL = "";
		strSQL  = "select ta.warehouse_id,tb.warehouse_name,ta.corp_id,tc.corp_name, \n"
				+ "ta.prod_no,td.prod_name,ta.piece_amount,ta.weight_amount \n"
				+ "from ( \n"
				+ "    select b.warehouse_id,b.corp_id,a.prod_no, \n"
				+ "    sum(a.piece_amount) as piece_amount, \n"
				+ "    sum(a.amount) as weight_amount \n"
				+ "    from tcm_prod_stock_in a,tcm_stock_in b \n"
				+ "    where a.stock_id = b.stock_id \n"
				+ "[    and  b.stock_date>= {start_date} \n]"
				+ "[    and  b.stock_date>= {start_date} and  b.stock_date<= {end_date} \n]"
				+ "[    and  b.stock_date<= {end_date} \n]"
				+ "[    and  b.prod_no like {%prod_no%} \n]"
				+ "    group by b.warehouse_id,b.corp_id,a.prod_no \n"
				+ ")ta,twh_warehouse tb ,tsm_corp tc ,tpm_prod td \n"
				+ "where ta.warehouse_id= tb.warehouse_id \n"
				+ "and   ta.corp_id     = tc.corp_id \n"
				+ "and   ta.prod_no     = td.prod_no \n"
				+ "order by ta.warehouse_id,ta.corp_id,ta.prod_no  \n";

		HashMap hashParam = new HashMap();
		HashMap hashUser  = null;

		ParseSQLPara parseSQLPara = new ParseSQLPara();

		hashParam.put("start_date","2005-01-01");
		
		
		parseSQLPara.setSQL("0",strSQL,"data_list","查询");
		parseSQLPara.setParam("start_date","date","开始日期");
		parseSQLPara.setParam("end_date"  ,"date","开始日期");
		parseSQLPara.setParam("end_date"  ,"date","开始日期");
		parseSQLPara.setParam("prod_no"   ,"string","产品编码");

		try{
			strSQL = parseSQLPara.getExecSql("",null,hashParam,hashUser);
			System.out.println("strSQL=\n"+strSQL);
		}catch(Exception e){
			System.out.println("Exception="+e.getMessage());
		}
	}
}