自製xml實現SQL動態參數配置

此文章是基於 搭建SpringMVC+Spring+Hibernate平臺

一. 準備工作

　　1. 點擊此找到並下載 commons-digester3-3.2.jar

　　2. 點擊此找到並下載 commons-beanutils-1.9.3.jar

　　　　目前最高版本

　　3. 將得到的jar包放到工程的 lib 目錄下

二. 相關代碼

　　1. xml 解析器：SqlXmlParser.java

package com.ims.persistence.base;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.digester3.Digester;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

public class SqlXmlParser {
    private String sqlXml;
    private static Digester digester;
    
    private static String webrootPath = System.getProperty("webapp.root")+"sql/";
    private static Logger logger = Logger.getLogger(SqlXmlParser.class);
    
    static{
        digester = new Digester();
        digester.setValidating(false);
        
        digester.addObjectCreate("set", SqlSet.class);
        
        digester.addObjectCreate("set/sql", Sql.class);
        digester.addSetProperties("set/sql");
        digester.addBeanPropertySetter("set/sql/pattern");  
        digester.addSetNext("set/sql", "addSql");
        
        digester.addObjectCreate("set/sql/param", SqlParam.class);
        digester.addSetProperties("set/sql/param");
        digester.addSetNext("set/sql/param", "addParam"); 
    }
    
    public SqlXmlParser(String sqlXml){
        this.sqlXml = sqlXml; 
    }
    
    public String parse(String sqlName, Map<String, String> params){    
        InputStream is = null;
        String result = null;
        try{
            is = new FileInputStream(new File(webrootPath+sqlXml));
            SqlSet sqlSet = (SqlSet)digester.parse(is);
            Sql sql = sqlSet.getSqls().get(sqlName);
            List<String> paramList = new ArrayList<String>(); 
            for(SqlParam param : sql.getCondition()){
                String value = params.get(param.getName());
                paramList.add(param.getPosition(), StringUtils.isBlank(value)?"":value);
            }
            result = MessageFormat.format(sql.getPattern(), paramList.toArray());
        }catch(Exception e1){
            logger.error("sql文件解析異常："+e1);
        }finally {
            if(is != null) {
                try {
                    is.close();
                }catch (Exception e2) {
                    logger.error("sql文件流關閉異常："+e2);
                }    
                
                is = null;
                digester.clear();
            }
        }
        
        return result;
    }
}

　　2. SqlSet.java

package com.ims.persistence.base;

import java.util.HashMap;
import java.util.Map;

public class SqlSet {
    private Map<String, Sql> sqls = new HashMap<String, Sql>();

    public void addSql(Sql sql){
        sqls.put(sql.getName(), sql);
    }
    
    public Map<String, Sql> getSqls() {
        return sqls;
    }
    public void setSqls(Map<String, Sql> sqls) {
        this.sqls = sqls;
    }
}

　　3. Sql.java

package com.ims.persistence.base;

import java.util.ArrayList;
import java.util.List;

public class Sql{
    private String name;
    private String pattern;
    private List<SqlParam> condition = new ArrayList<SqlParam>();
    
    public void addParam(SqlParam param){
        condition.add(param);
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getPattern() {
        return pattern;
    }
    public void setPattern(String pattern) {
        this.pattern = pattern;
    }

    public List<SqlParam> getCondition() {
        return condition;
    }
    public void setCondition(List<SqlParam> condition) {
        this.condition = condition;
    }
}

　　4. SqlParam.java

package com.ims.persistence.base;

public class SqlParam {
    private String name;
    private Integer position;
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    public Integer getPosition() {
        return position;
    }
    public void setPosition(Integer position) {
        this.position = position;
    }
}

　　5. 放在 WebContent/sql 目錄下的包含sql語句的xml文件，如：

<?xml version="1.0" encoding="utf-8" ?>
<set>
    <sql name="codeType">
        <pattern>
            <![CDATA[
                select A.id, A.value, A.text 
                from sys.code A 
                left join sys.codeType B on A.codeTypeId = B.id 
                where (case when "{0}"="" then 1=1 else B.codeType = "{0}" end)
                    and (case when "{1}"="" then 1=1 else A.isEnable = "{1}" end)
                    and (case when "{2}"="" then 1=1 else A.isDefault = "{2}" end)
            ]]>
        </pattern>
    
        <param name="codeType" position="0" />
        <param name="isEnable" position="1" />
        <param name="isDefault" position="2" />
        
    </sql>
</set>

　　6. 使用方法例如：

package com.ims.service.xxx.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.ims.persistence.base.SqlXmlParser;
import com.ims.persistence.dao.xxx.CodeDao;
import com.ims.service.xxx.CodeBS;

@Service("codeBS")
public class CodeBSImpl implements CodeBS{
    private static final String sqlXml = "xxx/code.xml";
    @Autowired
    private CodeDao codeDao;
    
    @Override
    public List<Map<String, Object>> getValueTextListByType(String codeType) {
        Map<String, String> paramMap = new HashMap<String, String>();
        paramMap.put("codeType", codeType);
        List<Map<String, Object>> list = codeDao.findBySql(new SqlXmlParser(sqlXml).parse("codeType", paramMap));
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        for(Map<String, Object> map:list){
            Map<String, Object> temp = new HashMap<String, Object>();
            temp.put("value", map.get("value"));
            temp.put("text", map.get("text"));
            result.add(temp);
        }
        return result;
    }

}