parsesqlbusi.java~3~

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

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

public class ParseSQLBusi {

    private static HashMap m_ParseSQLBusi = null;
    private static boolean m_product = false;

    public  int execute(Connection conn,ValueObject recvPack,ValueObject sendPack) throws Exception{
        HashMap hashPara  = null;
        HashMap hashUser  = null;
        ArrayList fileList= null;
        String s_func_id  = null;
		String action_id  = null;

		hashPara  = recvPack.getParam();
		hashUser  = recvPack.getUser ();

		s_func_id = recvPack.getFuncID();
		action_id = recvPack.getActionID();

        return  execute(conn, s_func_id, hashPara, hashUser, sendPack);
    }

    public int execute(Connection conn, String s_function_id, HashMap hashPara,
                       HashMap hashUser, ValueObject sendPack) throws
            Exception {
        int iSuccFlag = 0;
        String strMsg = null;

        if (sendPack == null) {
            return 0;
        }
        if (s_function_id == null) {
            s_function_id = "";
        }

        s_function_id = s_function_id.trim();
        if (conn == null) {
            strMsg = "数据库连接为空";
            throw new Exception(strMsg);
        }
        if (s_function_id.equals("")) {
            strMsg = "没有指定动态服务号";
            throw new Exception(strMsg);
        }
        //基本变量定义
        ParseSQLPara parseSQLPara = null;
        parseSQLPara = getParseSQLPara(conn, s_function_id);

        if (parseSQLPara == null) {
            iSuccFlag = -900012;
            strMsg = "动态服务[" + s_function_id + "]不存在";
            return sendPack.setErrMsg(iSuccFlag,strMsg);
        }

        HashMap  hashSQLs = parseSQLPara.getSQLS();
		HashMap  hashSQL  = null;
		HashMap  hashArgs = null;
		Iterator it       = null;

		String s_seq      = null;
		String s_sql      = null;
		String return_set = null;
		String func_desc  = null;
		String strSQL     = null;
		String sPageNo    = null;
		String sPageSize  = null;
		String sRowSize   = null;
		ArrayList aList   = null;

		int iRecNum       = 0;
		int iTotalNum     = 0;
		int iPageNo       = 0;
		int iPageSize     = 0;
		int iStartRow    = 0;
		int iEndRow      = 0;
		int iRowSize     = 0;

		sPageNo   = CommFunc.getValue(hashPara, "PAGE_NO"   , "", true);
		sPageSize = CommFunc.getValue(hashPara, "PAGE_SIZE" , "", true);
		sRowSize  = CommFunc.getValue(hashPara, "ROW_SIZE" , "", true);
		iPageNo   = CommFunc.strToInt(sPageNo  ,0);
		iPageSize = CommFunc.strToInt(sPageSize,0);
		iRowSize  = CommFunc.strToInt(sRowSize,0);
		if(iPageSize<0) iPageSize= 0;
		if (iPageSize>0) {
			if(iPageNo  <1) iPageNo  = 1;
			iStartRow= (iPageNo-1)*iPageSize +1;
			iEndRow  = (iPageNo)*iPageSize;
		}

		hashArgs  = parseSQLPara.getArgs();


		if(hashSQLs!=null) it = hashSQLs.keySet().iterator();
		while(it!=null && it.hasNext()){
			s_seq  = (String)it.next();
			hashSQL= (HashMap)hashSQLs.get(s_seq);
			if(hashSQL==null) continue;
			s_sql      = CommFunc.getValue(hashSQL, "sql"       , "", true);
			return_set = CommFunc.getValue(hashSQL, "return_set", "", true);
			func_desc  = CommFunc.getValue(hashSQL, "func_desc" , "", true);
			if(return_set.equals("")) return_set = "data_list";
			strSQL = parseSQLPara.getExecSql(s_sql,hashArgs,hashPara, hashUser);

			System.out.println("["+s_function_id+"]执行动态SQL\n" +strSQL);

			if (strSQL == null || strSQL.trim().equals("")) continue;
			aList      = new ArrayList();
			iRecNum    = DbFunc.getRecordSet(-900012,func_desc,conn, strSQL, iStartRow,iEndRow, aList,sendPack);

			sendPack.putPage(return_set,"pagesize"   ,iPageSize);
			sendPack.putPage(return_set,"pageno"     ,iPageNo  );
			sendPack.putPage(return_set,"recordcount",iRecNum  );
			sendPack.putPage(return_set,"rowsize"    ,iRowSize  );
			sendPack.putParam(return_set,strSQL  );

			iTotalNum += iRecNum;
			if (aList.size() < 1) {
	            CommFunc.freeObj(aList);
	            if (aList != null) {
	                aList.clear();
	            }
	            aList = null;
	            continue;
	        }
	        if (sendPack != null) {
                sendPack.putResultSet(return_set, aList);
            }
		}
        return iTotalNum;
    }

    public static ParseSQLPara getParseSQLPara(Connection conn, String s_function_id) {
        ParseSQLPara parseSQLPara = null;
        if (s_function_id == null || s_function_id.trim().equals("")) {
            System.out.println("请求执行动态SQL服务为空");
            return null;
        }
        s_function_id = s_function_id.trim();
        if (m_ParseSQLBusi != null) {
            if (m_product) {
                parseSQLPara = (ParseSQLPara) m_ParseSQLBusi.get(s_function_id);
                if (parseSQLPara != null) {
                    return parseSQLPara;
                }
            }
        }
        String strSQL = "";
        String function_id = null;
        String service_sql = null;
        String param_name = null;
        String data_type = null;
        String param_desc = null;

		String s_seq     = null;
		String s_sql     = null;
		String return_set= null;
		String func_desc = null;

        ArrayList aList = new ArrayList();
        HashMap aHash = null;
        int iRecNum = 0;

        int iLoop = 0;
        int iLoopNum = 0;

        try {
            strSQL =
                    "select function_id,seq,func_desc,func_sql,param_set,return_set \n"
                    + "from tsm_sql_service \n";
            if (!s_function_id.equals("")) {
                strSQL += " where function_id='" + s_function_id + "' \n";
            }
            iRecNum = DbFunc.getRecordSet(-900012,"查询"+s_function_id+"SQL服务信息",conn, strSQL, 0, 0, aList);
            iLoopNum = aList.size();

            for (iLoop = 0; iLoop < iLoopNum; iLoop++) {
                aHash = (HashMap) aList.get(iLoop);
                if (aHash == null || aHash.size() < 1) {
                    continue;
                }
                function_id = CommFunc.getValue(aHash, "function_id", "", true);
				s_seq       = CommFunc.getValue(aHash, "seq"        , "", true);
				s_sql       = CommFunc.getValue(aHash, "func_sql"   , "", true);
				return_set  = CommFunc.getValue(aHash, "return_set" , "", true);
				func_desc   = CommFunc.getValue(aHash, "func_desc"  , "", true);

                if (function_id.equals("") || s_sql.equals("")) {
                    continue;
                }

                if (m_ParseSQLBusi == null) {
                    m_ParseSQLBusi = new HashMap();
                }
                parseSQLPara = (ParseSQLPara) m_ParseSQLBusi.get(function_id);
                if (parseSQLPara == null) {
                    parseSQLPara = new ParseSQLPara();
                    m_ParseSQLBusi.put(function_id, parseSQLPara);
                }
                parseSQLPara.setID(function_id);
                parseSQLPara.setSQL(s_seq,s_sql,return_set,func_desc);
            }
            CommFunc.freeObj(aList);
            aList.clear();
            strSQL = "select function_id,param_name,data_type,param_desc \n"
                     + "from tsm_sql_args \n";
            if (!s_function_id.equals("")) {
                strSQL += " where function_id='" + s_function_id + "' \n";
            }
            iRecNum = DbFunc.getRecordSet(-900012,"查询"+s_function_id+"参数信息",conn, strSQL, 0, 0, aList);
            iLoopNum = aList.size();

            for (iLoop = 0; iLoop < iLoopNum; iLoop++) {
                aHash = (HashMap) aList.get(iLoop);
                if (aHash == null || aHash.size() < 1) {
                    continue;
                }
                function_id = CommFunc.getValue(aHash, "function_id", "", true);
                param_name  = CommFunc.getValue(aHash, "param_name", "", true);
                data_type   = CommFunc.getValue(aHash, "data_type", "", true);
                param_desc  = CommFunc.getValue(aHash, "param_desc", "", true);

                if (function_id.equals("") || param_name.equals("")) {
                    continue;
                }


                parseSQLPara = null;
                if (m_ParseSQLBusi != null) {
                    parseSQLPara = (ParseSQLPara) m_ParseSQLBusi.get(function_id);
                }
                if (parseSQLPara == null) {
                    continue;
                }
                parseSQLPara.setParam(param_name, data_type, param_desc);
            }
            if(m_ParseSQLBusi==null) return null;
            return (ParseSQLPara) m_ParseSQLBusi.get(s_function_id);
        } catch (Exception e) {
            System.out.println("取得["+s_function_id+"]动态SQL配置异常:"+e.getMessage());
        } finally {
            CommFunc.freeObj(aList);
            if (aList != null) {
                aList.clear();
            }
            aList = null;
        }
        return null;
    }
    public static void main(String args[])	{
    	try{
	    	Connection conn     = DbFunc.getConnection(true);
	    	String s_function_id= "TEST_001";
	    	HashMap hashPara    = new HashMap();
	    	HashMap hashUser    = new HashMap();
	    	ValueObject resultObj= new ValueObject();

	    	ParseSQLBusi  parseSQL  =new ParseSQLBusi();
	    	System.out.println("main ************ 1");
	    	parseSQL.execute(conn, s_function_id, hashPara,hashUser, resultObj);

	    	System.out.println("getResultSet="+resultObj.getResultSet());
	    	System.out.println("getErrorMessage="+resultObj.getMessage());
	 	}catch(Exception e){
	 		System.out.println("Exception*****="+e.getMessage());
	 	}
    }

}