Java实现读取xml文件sql配置转换为JDBC SQL

      公司的框架都是用原生的JDBC框架，其中操作数据很大一部分都是在拼凑SQL语句和GET/SET上，这种方式不仅麻烦而且很容易出错，端午节放假几天，突然间想起来把SQL配置XML中去，然后再用JAVA反射机制动态赋值，直接生成可执行的SQL给JDBC，这样可以省下一大堆的时间，而且错误率很少，当然我现在只是实现简单的应用，先上传到博客上，防止丢失说不定格式化了就没了...

     下面是我的实现方法：

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

/**
 * 动态SQL赋值，生成可执行SQL语句
 * 使用方法：
 * 		SQLFactory sql = new SQLFactory(pojo, xmlpath);
 * 		两个参数第一个是POJO对象，第二个是配置sql xml位置
 * 获取生成SQL方法：
 * 		String sql = sql.getSQL(xmlKey);
 * 		xmlKey：sql xml配置文件中每一个sql的id对应值，在xml中id唯一，不可重复
 * 
 * 引入jar：
 * 	本工厂生成sql类，依赖两个jar包，
 * 		1：dom4j-1.6.1.jar
 * 		2: jaxen-1.1.1.jar
 * 环境：
 * 		JDK 1.5以上
 * 
 * @author lizhenbin
 *
 */
public class SQLFactory {
	
	/**
	 * get方法
	 */
	private static final String BOOLEANHEAD = "is";
	private static final String STRHEAN = "get";
	/**
	 * 过滤XML配置文件特殊标志
	 */
	private static final char SPLIT_XMLCHAR = '#';
	private static final char SPLIT_LIKE_XMLCHAR = '%';
	private static final String SPLIT_XMLSTR = "#";
	private static final String SPLIT_LIKE_XMLSTR = "%";
	private static final String SPLIT_SQLSTR = "'";
	
	/**
	 * XML路径
	 */
	private String xmlPath;
	/**
	 * sql语句集合体
	 */
	private List<Map<String, String>> sqlMapList = null;
	
	/**
	 * 初始化，获取SQL
	 * @param xmlPath
	 */
	public SQLFactory(String xmlPath) {
		this.xmlPath = xmlPath;
		XMLLoader load = new XMLLoader(this.xmlPath);
		this.sqlMapList = load.loadSQL();
	}
	
	/**
	 * 直接获取SQL语句，用于不需要传入参数
	 * 
	 * @param sqlId
	 * 			XML配置的SQL主键ID
	 * @return
	 */
	public String getBasicSQL(String sqlId) {
		return this.getXMLSql(sqlId);
	}
	
	/**
	 * 初始化时候是JAVA的基本类型或者其实现类，调用此方法获取SQL语句
	 * 适用于通过单个属性信息查询
	 * eg : java.lang.String, java.lang.Integer...
	 * 
	 * @param sqlId 
	 * 		XML配置的SQL主键ID
	 * @param obj
	 * 		POJO对象 
	 * @return
	 * 		JDBC SQL语句
	 */
	public String getBasicSQL(String sqlId, Object obj) {
		
		if(obj == null)
			return this.getXMLSql(sqlId);		
		StringBuilder sql = new StringBuilder();
		sql.append(this.getXMLSql(sqlId));
		List<String> fieldlist = new ArrayList<String>();
		List<Integer> splitlist = new ArrayList<Integer>();
		for(int i = 0; i < sql.length(); i++) {
			char symbol = sql.charAt(i);
			if(symbol == this.SPLIT_XMLCHAR) {
				splitlist.add(i);
			}
		}		
		/**
		 * 获取POJO的值，放在MAP中
		 */
		List<Map<Object, Object>> valuesList = new ArrayList<Map<Object, Object>>();
		Class clazz = obj.getClass();
		int index = 0;
		while(splitlist != null && splitlist.size() > index 
				&& splitlist.get(index + 1) != null) {
			int begin = splitlist.get(index);
			int end = splitlist.get(index + 1);
			String str = sql.substring(begin, end + 1);
			try {
				Map<Object, Object> map = new HashMap<Object, Object>();
				map.put(str, obj);
				valuesList.add(map);
			} catch (Exception e) {
				e.printStackTrace();
			}
			fieldlist.add(str);
			index = index + 2;
		}
		/**
		 * 把获取的值替换掉字符串中的属性值
		 */
		String sqlStr = this.replaceStr(sql.toString(), valuesList);
		return sqlStr;
	}
	
	/**
	 * 初始化时是POJO对象的，调用此方法获取SQL语句
	 * 获取SQL是，将POJO对象信息封装
	 * 
	 * @param sqlId 
	 * 		XML中配置的ID
	 * @param obj 
	 * 		POJO对象
	 * @return
	 * 		JDBC SQL语句
	 */
	public String getEntirySQL(String sqlId, Object obj) {
		
		if(obj == null)
			return this.getXMLSql(sqlId);	
		StringBuilder sql = new StringBuilder();
		sql.append(this.getXMLSql(sqlId));
		List<String> fieldlist = new ArrayList<String>();
		List<Integer> splitlist = new ArrayList<Integer>();
		for(int i = 0; i < sql.length(); i++) {
			char symbol = sql.charAt(i);
			if(symbol == this.SPLIT_XMLCHAR) {
				splitlist.add(i);
			}
		}
		/**
		 * 获取POJO的值，放在MAP中
		 */
		List<Map<Object, Object>> valuesList = new ArrayList<Map<Object, Object>>();
		Class clazz = obj.getClass();
		int index = 0;
		while(splitlist != null && splitlist.size() > index 
				&& splitlist.get(index + 1) != null) {
			int begin = splitlist.get(index);
			int end = splitlist.get(index + 1);
			String str = sql.substring(begin, end + 1);
			try {
				String methodStr = this.getMethodName(str);
				Field field = clazz.getDeclaredField(methodStr);
				String key = this.getMethod(methodStr, field);
				Method method = clazz.getMethod(key);
				Object value = (Object) method.invoke(obj);
				Map<Object, Object> map = new HashMap<Object, Object>();
				map.put(str, value);
				valuesList.add(map);
			} catch (Exception e) {
				e.printStackTrace();
			}
			fieldlist.add(str);
			index = index + 2;
		}
		
		/**
		 * 把获取的值替换掉字符串中的属性值
		 */
		String sqlStr = this.replaceStr(sql.toString(), valuesList);
		return sqlStr;
	}
	
	/**
	 * 动态生成sql语句
	 * @param xmlSql
	 * @param valuesList
	 * @return
	 */
	private String replaceStr(String xmlSql, List<Map<Object, Object>> valuesList) {
		
		String tempStr = xmlSql;
		for(Map<Object, Object> map : valuesList) {
			StringBuffer sb = new StringBuffer();
			StringBuffer newStr = new StringBuffer();
			Set keySet = map.keySet();
			String key = null;
			for(Iterator itor = keySet.iterator(); itor.hasNext();) {
				key = (String) itor.next();
			}
			sb.append(key);
			if(map.get(key) != null) {
				/**
				 * 一般赋值#property#
				 */
				if(key.contains(this.SPLIT_XMLSTR) && !key.contains(this.SPLIT_LIKE_XMLSTR))
					newStr.append(this.SPLIT_SQLSTR).append(map.get(key)).append(this.SPLIT_SQLSTR);
				/**
				 * 模糊查询赋值#%property%#
				 */
				else if(key.contains(this.SPLIT_LIKE_XMLSTR) && key.contains(this.SPLIT_LIKE_XMLSTR))
					newStr.append(this.SPLIT_SQLSTR).append(this.SPLIT_LIKE_XMLSTR).append(map.get(key))
						.append(this.SPLIT_LIKE_XMLSTR).append(this.SPLIT_SQLSTR);
				/**
				 * 不符合规范赋''空串
				 */
				else
					newStr.append(this.SPLIT_SQLSTR + this.SPLIT_SQLSTR);
			}
			else
				// 空值默认是空串插入
				newStr.append(this.SPLIT_SQLSTR + this.SPLIT_SQLSTR);
			boolean flag = xmlSql.contains(sb.toString());
			if(flag) {
				tempStr = tempStr.replace(sb.toString(), newStr.toString());
			}
		}		
		return tempStr;
	}
	
	/**
	 * 获取配置参数名称，对应POJO属性参数
	 * 
	 * @param splitStr
	 * 			XML #Property#配置属性参数
	 * @return
	 */
	private String getMethodName(String splitStr) {
		
		StringBuilder sb = new StringBuilder();
		if(splitStr != null && !splitStr.equals("")) {
			for(int i = 0; i < splitStr.length(); i++) {
				char symbol = splitStr.charAt(i);
				if(symbol != this.SPLIT_XMLCHAR && symbol != this.SPLIT_LIKE_XMLCHAR)
					sb.append(symbol);
			}
			return sb.toString();
		}
		return null;
	}
	
	/**
	 * 通过SQL ID获取SQL语句
	 * @param sqlId
	 * @return
	 */
	private String getXMLSql(String sqlId) {
		
		String sql = null;
		if(sqlId == null || sqlId.equals(""))
			return null;
		if(this.sqlMapList != null && this.sqlMapList.size() > 0) {
			for(Map<String, String> sqlmap : this.sqlMapList) {
				if(sqlmap.get(sqlId) != null) {
					sql = sqlmap.get(sqlId);
					break;
				}
			}
		}
		return sql;
	}
	
	/**
	 * 拼凑成getXXX()方法，boolean为isXXX()
	 * @param field
	 * @return
	 */
	private String getMethod(String value, Field field) {
		
		StringBuilder sb = new StringBuilder();
		String firstStr = value.substring(0, 1).toUpperCase();
		String endStr = value.substring(1, value.length());
		if(field.getType() == Boolean.class || field.getType() == boolean.class)
			return sb.append(this.BOOLEANHEAD).append(firstStr).append(endStr).toString();
		else
			return sb.append(this.STRHEAN).append(firstStr).append(endStr).toString();
	}
	
	/**
	 * 内部类
	 * 作用：读取解析XML信息，返回sql语句的Lits集合，集合的对象是Map<k,v>
	 * 		k:XML的配置的ID，value为id对应的内容
	 * @author lizhenbin
	 *
	 */
	class XMLLoader {	
		
		/**
		 * sql语句标志
		 */
		private static final String XML_SELECT = "select";
		private static final String XML_INSERT = "insert";
		private static final String XML_UPDATE = "update";
		private static final String XML_DELETE = "delete";
		
		/**
		 * 加载xml文件路径
		 */
		private String filePath;
		
		/**
		 * 初始化
		 * @param filePath
		 */
		public XMLLoader(String filePath) {
			this.filePath = filePath;
		}
		
		/**
		 * 解析XML文件，返回sql集合
		 * 集合元素为Map<k,v> key：sql语句在xml的id标志，value：xml中的sql语句
		 * @return
		 */
		public List<Map<String, String>> loadSQL() {
			
			/**
			 * 返回XML的格式
			 */
			List<Map<String, String>> queryList = new ArrayList<Map<String, String>>();
			try {   
	            SAXReader reader = new SAXReader();   
	            InputStream in = XMLLoader.class.getClassLoader().getResourceAsStream(this.filePath);   
	            Document doc = reader.read(in);   
	            Element root = doc.getRootElement();   
	            List<Map<String, String>> nodelist = readNode(root);
	            if(nodelist != null && nodelist.size() > 0) {
	            	for(Map<String, String> map : nodelist) {
	            		Element node = null;
	            		Map<String, String> querySQL = new HashMap<String, String>();
	            		if(map.get(this.XML_SELECT) != null) {
	            			node = (Element) root.selectSingleNode("//" + this.XML_SELECT + "[@id='" + map.get(this.XML_SELECT) + "']");
	            			querySQL.put(map.get(this.XML_SELECT), node.getText());
	            		}else if(map.get(this.XML_INSERT) != null){
	            			node = (Element) root.selectSingleNode("//" + this.XML_INSERT + "[@id='" + map.get(this.XML_INSERT) + "']");
	            			querySQL.put(map.get(this.XML_INSERT), node.getText());
	            		}else if(map.get(this.XML_UPDATE) != null) {
	            			node = (Element) root.selectSingleNode("//" + this.XML_UPDATE + "[@id='" + map.get(this.XML_UPDATE) + "']");
	            			querySQL.put(map.get(this.XML_UPDATE), node.getText());
	            		}else if(map.get(this.XML_DELETE) != null) {
	            			node = (Element) root.selectSingleNode("//" + this.XML_DELETE + "[@id='" + map.get(this.XML_DELETE) + "']");
	            			querySQL.put(map.get(this.XML_DELETE), node.getText());
	            		}
	            		queryList.add(querySQL);
	            	}
	            }
	            return queryList;
	        } catch (DocumentException e) {   
	            e.printStackTrace();   
	        }   
			return null;
		}
		
		/**
		 * 读取所有的节点信息内容
		 * @param root
		 */
		private List<Map<String, String>> readNode(Element root) {	
			
			List<Map<String, String>> nodelist = new ArrayList<Map<String, String>>();
	        if (root == null) 
	        	return null;  
	        // 获取他的子节点    
	        List<Element> childNodes = root.elements();   
	        for (Element e : childNodes) {
	        	if(e!=null) {
	        		List<Attribute> childs = e.attributes();
	    	        if (childs != null && childs.size() > 0) {
	    	        	for (Attribute attr : childs) {
	    	        		Map<String, String> map = new HashMap<String, String>();
	    	        		map.put(e.getName(), attr.getValue());
	    	        		nodelist.add(map);
	    	            }     
	    	        }   
	        	}
	        }
	        return nodelist;
		} 
	}
}

 

 测试文件：

首先是我的XML模板

<?xml version="1.0" encoding="UTF-8" ?>
<sql name="safedoormgr">
	<!-- 查询sql语句模板 -->
	<select id="querySafeDoorsPage">
		<![CDATA[
			select equno, equip, equname, state, remark 
			from cs_equipregister
			where state != '1';
		]]>
	</select>
	
	<!-- 插入sql语句模板 -->
	<insert id="insertSafeDoor">
		<![CDATA[
			insert into cs_equipregister(equno, equip, equname, state, remark)
			values(#s_equno#, #s_equip#, #s_equname#, #s_state#, #s_remark#)
		]]>
	</insert>
	
	<!-- 更新sql语句模板 -->
	<update id="updateSafeDoorById">
		<![CDATA[
			update cs_equipregister 
			set equip = #s_equip#,
				equname = #s_equname#,
				remark = #s_remark#
			where equno = #s_equno#
		]]>
	</update>
	
	<!-- 更新信息状态，用于软删除 -->
	<update id="updateSafeDoorStateById">
		<![CDATA[
			update cs_equipregister 
			set state = #s_state#
			where equno = #s_equno#
		]]>
	</update>
	
</sql>

 

测试类：

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Vo vo = new Vo();
		vo.setS_equip("00001");
		vo.setS_equname("测试");
		SQLFactory sql = new SQLFactory(vo, "SafedoorForm.xml");
		System.out.println(sql.getSQL("insertSafeDoor"));
		System.out.println(sql.getSQL("updateSafeDoorById"));
	}

}

 

package com.commsoft.sql.tools;

import java.util.Date;

public class Vo {
	
	/**
	 * 设备编号
	 */
	private String s_equno;
	/**
	 * 设备IP
	 */
	private String s_equip;
	/**
	 * 设备名称
	 */
	private String s_equname;
	/**
	 * 设备状态
	 */
	private String s_state;
	/**
	 * 设备备注
	 */
	private String s_remark;
	public String getS_equno() {
		return s_equno;
	}
	public void setS_equno(String s_equno) {
		this.s_equno = s_equno;
	}
	public String getS_equip() {
		return s_equip;
	}
	public void setS_equip(String s_equip) {
		this.s_equip = s_equip;
	}
	public String getS_equname() {
		return s_equname;
	}
	public void setS_equname(String s_equname) {
		this.s_equname = s_equname;
	}
	public String getS_state() {
		return s_state;
	}
	public void setS_state(String s_state) {
		this.s_state = s_state;
	}
	public String getS_remark() {
		return s_remark;
	}
	public void setS_remark(String s_remark) {
		this.s_remark = s_remark;
	}
	
	

}

 

结果

		
			insert into cs_equipregister(equno, equip, equname, state, remark)
			values('', '00001', '测试', '', '')
		
	

		
			update cs_equipregister 
			set equip = '00001',
				equname = '测试',
				remark = ''
			where equno = ''

 

评论

1 楼 yanxunjian 2012-12-11  

Java实现读取xml文件sql配置转换为JDBC SQL
文章有没有完整的实例吗？