查询语句的独立工具类FcSql

前言

有一部分查询，需要通过写原生SQL，然后JdbcTemplate来查询；如果需要修改sql就需要改代码，所以这里就把sql读取出来，方便修改使用

使用

1.在Spring的配置XML中注入

<bean class="com.xqx.spfsign.util.FcSql">
  	<property name="sqlconfigpath" value="#{'classpath:externalSql/SpfSignSql.xml'}"/>
</bean>

2.在resources下面创建对应的xml文件：externalSql/SpfSignSql.xml

<?xml version="1.0" encoding="UTF-8"?>
<sqls>
	<!--
		id:唯一标记，必须有，工具类就是通过id获取SQL的
		name：备注：不必须，写这个sql是做什么用的
	-->
    <sql id="findByAttemptsAndStatusList" name="查询待推送列表">
        这里写sql语句
    </sql>
 <sqls>

3.引入FcSql工具类

package com.xqx.spfsign.util;

import java.io.File;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.net.URL;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.Timer;
import java.util.TimerTask;
import java.util.concurrent.ConcurrentHashMap;

import javax.annotation.PostConstruct;
import javax.xml.parsers.DocumentBuilderFactory;

import org.springframework.util.ResourceUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * sql配置读取
 */
public class FcSql {
	
	public static class PrintSql {
		
		public static void printSql(String sql, Object[] params) {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mmss.SSS");
			for (int i = 0; i < params.length; i++) {
				if (null != params[i]) {
					if (params[i].getClass().equals(String.class)) {
						sql = sql.replaceFirst("\\?", "'" + params[i].toString() + "'");
					}else if (params[i].getClass().equals(Date.class)) {
						sql = sql.replaceFirst("\\?", "'" + format.format(params[i]) + "'");
					}else {
						sql = sql.replaceFirst("\\?", format.format(params[i]));
					}
				}else {
					sql = sql.replaceFirst("\\?", "'" + "'");
				}
			}
			System.out.println(sql);
		}
	}
	
	/**
	 * 直接注解到方法上
	 */
	@Documented
	@Retention(RetentionPolicy.RUNTIME)
	@Target({ElementType.METHOD})
	public static @interface FcSqls {
		
		public enum Sqls{
			FIRST, SECOND;
		}
		
		public Sqls sqls() default Sqls.FIRST;
	}
	
	public static class FcSqlInfo {

		private String id;
		private String name;
		private String sql;
		
		public String getId() {
			return id;
		}
		public void setId(String id) {
			this.id = id;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getSql() {
			return sql;
		}
		public void setSql(String sql) {
			this.sql = sql;
		}
		
	}
	
	private String sqlconfigpath = null;
	private Boolean timer = false;
	private Long sleep = 300000L;
	private static Double version;
	private static Boolean sync = false;
	private static Map<String, FcSqlInfo> infos = new ConcurrentHashMap<String, FcSqlInfo>();
	
	@PostConstruct
	public void init() {
		if (null != sqlconfigpath && !sqlconfigpath.equals("")) {
			if (timer) {
				readsql(sqlconfigpath);
				timer();
			}else {
				readsql(sqlconfigpath);
			}
		}
	}
	
	public String getSqlconfigpath() {
		return sqlconfigpath;
	}
	
	public void setSqlconfigpath(String sqlconfigpath) {
		this.sqlconfigpath = sqlconfigpath;
	}
	
	public Boolean getTimer() {
		return timer;
	}
	
	public void setTimer(Boolean timer) {
		this.timer = timer;
	}
	
	public Long getSleep() {
		return sleep;
	}
	
	public void setSleep(Long sleep) {
		this.sleep = sleep;
	}
	
	public static String getSql(String id) {
		return null == infos.get(id) ? null : infos.get(id).getSql();
	}
	
	public static String getName(String id) {
		return null == infos.get(id) ? null : infos.get(id).getName();
	}
	
	private static void readsql(String xmlpath) {
		try {
			DocumentBuilderFactory doucumentfactory = DocumentBuilderFactory.newInstance();
			Document document = null;
			if (xmlpath.startsWith("classpath:")) {
				document = doucumentfactory.newDocumentBuilder().parse(ResourceUtils.getFile(xmlpath));
			}else {
				URL url = FcSql.class.getResource("/");
				String projectpath = new File(new File(URLDecoder.decode(url.getPath(), "UTF-8")).getParent()).getParent();
				if (xmlpath.startsWith("/")) {
					projectpath += xmlpath;
				}else {
					projectpath += "/" + xmlpath;
				}
				document = doucumentfactory.newDocumentBuilder().parse(ResourceUtils.getFile(new File(projectpath).toURI().toURL()));
			}
			Element root = document.getDocumentElement(); 
			NodeList sqls = root.getChildNodes();
			for (int i = 0; i < sqls.getLength(); i++) {
				if (sqls.item(i) instanceof Element) {
					FcSqlInfo info = new FcSqlInfo();
					Node sql = sqls.item(i);
					NamedNodeMap attributes = sql.getAttributes();
					info.setId(getAttribute(attributes, "id"));
					info.setName(getAttribute(attributes, "name"));
					String context = sql.getTextContent();
					context = context.replaceAll("\t", " ").replaceAll("\n", " ");
					info.setSql(context);
					infos.put(info.getId(), info);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Double getVersion() {
		return version;
	}
	
	public void setVersion(Double version) {
		FcSql.version = version;
	}
	
	public static Double getQueryVersion() {
		return FcSql.version;
	}
	
	private static String getAttribute(NamedNodeMap nodemap, String key) {
		for (int i = 0; i < nodemap.getLength(); i++) {
			if (nodemap.item(i).getNodeName().equals(key)) {
				return nodemap.item(i).getNodeValue();
			}
		}
		return null;
	}
	
	private void timer() {
		Timer timer = new Timer();
		timer.schedule(new TimerTask() {
			
			@Override
			public void run() {
				readsql(sqlconfigpath);
			}
			
		}, 0, sleep);
	}

}

4.使用方法

//这里就能获取到xml中写的sql了
String sql = FcSql.getSql("findByAttemptsAndStatusList");