Hibernate+Spring+JDBC+Freemarker

今天看到《银行主数据项目(MDM)的数据持久层,你选择hibernate还是ibatis(MyBatis)》跑到首页来了， 把我最近使用方式分享一下。Hiberante+(Spring JDBC + freemarker)两次结合，hibernate对简单的数据操作很方便，可以大量减少SQL语句的维护。对于复杂的sql和性能考虑使用Spring JDBC + freemarker，动态生成SQL。不使用hiberante的二级缓存，在业务层面进行数据缓存。

SQL标签tempateType值;simple和freeMarker, simple直接按照spring jdbc template方式书写。

使用freemarker 可以很好的解决in参数的问题。

StarFlow之前使用了mybatis，最近替换成了spring jdbc.月底会发布0.7版本。

Java代码

1. public class CustomSQL {
2. private static Logger logger = LoggerFactory.getLogger(CustomSQL.class);
4. private Map<String, SQLBean> _sqlPool;
6. private static final String GROUP_BY_CLAUSE = " GROUP BY ";
7. private static final String ORDER_BY_CLAUSE = " ORDER BY ";
8. private static final String STRING_SPACE = " ";
10. private final SAXReader saxReader = new SAXReader();
11. private Configuration configuration = null;
12. private StringTemplateLoader stringTemplateLoader = null;
14. public CustomSQL() throws SQLException {
16. _sqlPool = new HashMap<String, SQLBean>();
18. try {
19. ClassLoader classLoader = getClass().getClassLoader();
20. configuration = new Configuration();
21. stringTemplateLoader = new StringTemplateLoader();
22. configuration.setDefaultEncoding("UTF-8");
24. String[] configs = getConfigs();
25. for (String _config : configs) {
26. read(classLoader, _config);
27. }
29. configuration.setTemplateLoader(stringTemplateLoader);
30. }
31. catch (Exception e) {
32. logger.error("", e);
33. }
35. }
37. protected String[] getConfigs() {
38. return new String[] {"custom-sql/default.xml"};
39. }
41. public String appendCriteria(String sql, String criteria) {
42. if (StringUtils.isBlank(criteria)) {
43. return sql;
44. }
46. if (!criteria.startsWith(STRING_SPACE)) {
47. criteria = STRING_SPACE.concat(criteria);
48. }
50. if (!criteria.endsWith(STRING_SPACE)) {
51. criteria = criteria.concat(STRING_SPACE);
52. }
54. int pos = sql.indexOf(GROUP_BY_CLAUSE);
56. if (pos != -1) {
57. return sql.substring(0, pos + 1).concat(criteria).concat(
58. sql.substring(pos + 1));
59. }
61. pos = sql.indexOf(ORDER_BY_CLAUSE);
63. if (pos != -1) {
64. return sql.substring(0, pos + 1).concat(criteria).concat(
65. sql.substring(pos + 1));
66. }
68. return sql.concat(criteria);
69. }
71. public String get(String id) {
72. SQLBean bean = _sqlPool.get(id);
73. if("simple".equals(bean.getTempateType())) {
74. return _sqlPool.get(id).getContent();
75. } else
76. throw new RuntimeException("SQL 模板类型不正确，只可以是simple类型");
77. }
79. public String get(String id, Map<String, Object> model) {
80. try {
81. Template template = configuration.getTemplate(id);
82. StringWriter writer = new StringWriter();
83. template.process(model, writer);
84. return writer.toString();
85. } catch (TemplateException e) {
86. throw new RuntimeException("Parse sql failed", e);
87. } catch (IOException e) {
88. throw new RuntimeException("Parse sql failed", e);
89. }
90. }
92. protected void read(ClassLoader classLoader, String source)
93. throws Exception {
95. InputStream is = classLoader.getResourceAsStream(source);
97. if (is == null) {
98. return;
99. }
101. logger.info("Loading " + source);
103. Document document = saxReader.read(is);
104. Element rootElement = document.getRootElement();
106. for (Object sqlObj : rootElement.elements("sql")) {
107. Element sqlElement = (Element)sqlObj;
108. String file = sqlElement.attributeValue("file");
110. if (StringUtils.isNotBlank(file)) {
111. read(classLoader, file);
112. } else {
113. String id = sqlElement.attributeValue("id");
114. String sqlType = sqlElement.attributeValue("sqlType");
115. String tempateType = sqlElement.attributeValue("tempateType");
117. if("simple".equals(tempateType) || "freeMarker".equals(tempateType)) {
118. String content = transform(sqlElement.getText());
120. SQLBean bean = new SQLBean();
121. bean.setTempateType(tempateType);
122. bean.setSqlType(sqlType);
123. bean.setContent(content);
125. if("freeMarker".equals(tempateType))
126. stringTemplateLoader.putTemplate(id, content);
128. _sqlPool.put(id, bean);
129. } else {
130. logger.warn("{} 对应 tempateType 值 {} 不正确，可选值为：simple和freeMarker", id, sqlType);
131. }
132. }
133. }
134. }
136. protected String transform(String sql) {
137. StringBuilder sb = new StringBuilder();
139. try {
140. BufferedReader bufferedReader =
141. new BufferedReader(new StringReader(sql));
143. String line = null;
144. while ((line = bufferedReader.readLine()) != null) {
145. sb.append(line.trim());
146. sb.append(STRING_SPACE);
147. }
149. bufferedReader.close();
150. }
151. catch (IOException ioe) {
152. return sql;
153. }
155. return sb.toString();
156. }
158. public static class SQLBean {
159. /**
160. * 两种可选类型：simple和freeMarker
161. */
162. private String tempateType = "simple";
163. /**
164. * 两种可选类型：SQL和HQL
165. */
166. private String sqlType = "SQL";
167. private String content = "";
169. public String getTempateType() {
170. return tempateType;
171. }
172. public void setTempateType(String tempateType) {
173. this.tempateType = tempateType;
174. }
175. public String getSqlType() {
176. return sqlType;
177. }
178. public void setSqlType(String sqlType) {
179. this.sqlType = sqlType;
180. }
181. public String getContent() {
182. return content;
183. }
184. public void setContent(String content) {
185. this.content = content;
186. }
188. }
190. }

public class CustomSQL {
	private static Logger logger = LoggerFactory.getLogger(CustomSQL.class);

	private Map<String, SQLBean> _sqlPool;

	private static final String GROUP_BY_CLAUSE = " GROUP BY ";
	private static final String ORDER_BY_CLAUSE = " ORDER BY ";
	private static final String STRING_SPACE = " ";
	
	private final SAXReader saxReader = new SAXReader();
	private Configuration configuration = null;
	private StringTemplateLoader stringTemplateLoader = null;

	public CustomSQL() throws SQLException {

		_sqlPool = new HashMap<String, SQLBean>();

		try {
			ClassLoader classLoader = getClass().getClassLoader();
			configuration = new Configuration();  
			stringTemplateLoader = new StringTemplateLoader();
	        configuration.setDefaultEncoding("UTF-8");  

			String[] configs = getConfigs();
			for (String _config : configs) {
				read(classLoader, _config);
			}
			
	        configuration.setTemplateLoader(stringTemplateLoader);  
		}
		catch (Exception e) {
			logger.error("", e);
		}
		
	}
	
	protected String[] getConfigs() {
		return new String[] {"custom-sql/default.xml"};
	}

	public String appendCriteria(String sql, String criteria) {
		if (StringUtils.isBlank(criteria)) {
			return sql;
		}

		if (!criteria.startsWith(STRING_SPACE)) {
			criteria = STRING_SPACE.concat(criteria);
		}

		if (!criteria.endsWith(STRING_SPACE)) {
			criteria = criteria.concat(STRING_SPACE);
		}

		int pos = sql.indexOf(GROUP_BY_CLAUSE);

		if (pos != -1) {
			return sql.substring(0, pos + 1).concat(criteria).concat(
				sql.substring(pos + 1));
		}

		pos = sql.indexOf(ORDER_BY_CLAUSE);

		if (pos != -1) {
			return sql.substring(0, pos + 1).concat(criteria).concat(
				sql.substring(pos + 1));
		}

		return sql.concat(criteria);
	}

	public String get(String id) {
		SQLBean bean = _sqlPool.get(id);
		if("simple".equals(bean.getTempateType())) {
			return _sqlPool.get(id).getContent();
		} else
			throw new RuntimeException("SQL 模板类型不正确，只可以是simple类型");
	}
	
	public String get(String id, Map<String, Object> model) {
		try {
			Template template = configuration.getTemplate(id);      
			StringWriter writer = new StringWriter();      
			template.process(model, writer); 
			return writer.toString();
        } catch (TemplateException e) {  
            throw new RuntimeException("Parse sql failed", e);  
        } catch (IOException e) {  
            throw new RuntimeException("Parse sql failed", e);  
        }  
	}

	protected void read(ClassLoader classLoader, String source)
		throws Exception {

		InputStream is = classLoader.getResourceAsStream(source);

		if (is == null) {
			return;
		}

		logger.info("Loading " + source);

		Document document = saxReader.read(is);
		Element rootElement = document.getRootElement();

		for (Object sqlObj : rootElement.elements("sql")) {
			Element sqlElement = (Element)sqlObj;
			String file = sqlElement.attributeValue("file");

			if (StringUtils.isNotBlank(file)) {
				read(classLoader, file);
			} else {
				String id = sqlElement.attributeValue("id");
				String sqlType = sqlElement.attributeValue("sqlType");
				String tempateType = sqlElement.attributeValue("tempateType");
				
				if("simple".equals(tempateType) || "freeMarker".equals(tempateType)) {
					String content = transform(sqlElement.getText());
					
					SQLBean bean = new SQLBean();
					bean.setTempateType(tempateType);
					bean.setSqlType(sqlType);
					bean.setContent(content);
					
					if("freeMarker".equals(tempateType))
						stringTemplateLoader.putTemplate(id, content);
					
					_sqlPool.put(id, bean);
				} else {
					logger.warn("{} 对应 tempateType 值 {} 不正确，可选值为：simple和freeMarker", id, sqlType);
				}
			}
		}
	}

	protected String transform(String sql) {
		StringBuilder sb = new StringBuilder();

		try {
			BufferedReader bufferedReader =
				new BufferedReader(new StringReader(sql));

			String line = null;
			while ((line = bufferedReader.readLine()) != null) {
				sb.append(line.trim());
				sb.append(STRING_SPACE);
			}

			bufferedReader.close();
		}
		catch (IOException ioe) {
			return sql;
		}

		return sb.toString();
	}
	
	public static class SQLBean {
		/**
		 * 两种可选类型：simple和freeMarker
		 */
		private String tempateType = "simple";
		/**
		 * 两种可选类型：SQL和HQL
		 */
		private String sqlType = "SQL";
		private String content = "";
		
		public String getTempateType() {
			return tempateType;
		}
		public void setTempateType(String tempateType) {
			this.tempateType = tempateType;
		}
		public String getSqlType() {
			return sqlType;
		}
		public void setSqlType(String sqlType) {
			this.sqlType = sqlType;
		}
		public String getContent() {
			return content;
		}
		public void setContent(String content) {
			this.content = content;
		}
		
	}

}

Java代码

1. public class CustomSQLUtil {
2. private static Logger logger = LoggerFactory.getLogger(CustomSQLUtil.class);
4. private static CustomSQLUtil _instance = new CustomSQLUtil();
6. private CustomSQL _customSQL;
8. private CustomSQLUtil() {
9. try {
10. _customSQL = new CustomSQL();
11. }
12. catch (Exception e) {
13. logger.error("", e);
14. }
15. }
17. public static String appendCriteria(String sql, String criteria) {
18. return _instance._customSQL.appendCriteria(sql, criteria);
19. }
21. public static String get(String id) {
22. return _instance._customSQL.get(id);
23. }
25. public static String get(String id, Map<String, Object> model) {
26. return _instance._customSQL.get(id, model);
27. }
28. }

public class CustomSQLUtil {
	private static Logger logger = LoggerFactory.getLogger(CustomSQLUtil.class);

	private static CustomSQLUtil _instance = new CustomSQLUtil();

	private CustomSQL _customSQL;
	
	private CustomSQLUtil() {
		try {
			_customSQL = new CustomSQL();
		}
		catch (Exception e) {
			logger.error("", e);
		}
	}

	public static String appendCriteria(String sql, String criteria) {
		return _instance._customSQL.appendCriteria(sql, criteria);
	}

	public static String get(String id) {
		return _instance._customSQL.get(id);
	}
	
	public static String get(String id, Map<String, Object> model) {
		return _instance._customSQL.get(id, model);
	}
}

实例：

Java代码

1. <sql id="com.*.service.home.CvToolMenuService.queryToolMenusByUser" sqlType="SQL" tempateType="freeMarker">
2. <![CDATA[
3. select
4. a.*, (select count(*) from CV_TOOL_MENU d where d.PARENT_MENU_ID = a.RES_ID) COUNT
5. from
6. CV_TOOL_MENU a
7. where
8. exists (
9. select b.TOOL_MENU_ID from ST_ROLE_TOOL_MENU b
10. where a.RES_ID = b.TOOL_MENU_ID and
11. <#if (paretMenuId??)>
12. a.PARENT_MENU_ID = ?
13. <#else>
14. a.PARENT_MENU_ID is null
15. </#if>
16. and b.ROLE_ID in (
17. <#list roleIds as id>
18. ?<#if id_has_next>, </#if>
19. </#list>
20. ))
21. ]]>
22. </sql>

<sql id="com.*.service.home.CvToolMenuService.queryToolMenusByUser" sqlType="SQL" tempateType="freeMarker">
		<![CDATA[
			select 
					a.*, (select count(*) from CV_TOOL_MENU d where d.PARENT_MENU_ID = a.RES_ID) COUNT
			from
					CV_TOOL_MENU a
			where
					exists (
						select b.TOOL_MENU_ID from ST_ROLE_TOOL_MENU b 
						where a.RES_ID = b.TOOL_MENU_ID and 
							<#if (paretMenuId??)> 
								a.PARENT_MENU_ID = ? 
							<#else> 
								a.PARENT_MENU_ID is null 
							</#if>
							and b.ROLE_ID in (
							<#list roleIds as id>
							  	?<#if id_has_next>, </#if>
							</#list>  
						))
		]]>
	</sql>

Java代码

1. private static String QUERY_TOOL_MENUS_BY_USER = CvToolMenuService.class.getName()+".queryToolMenusByUser";
3. String sql = CustomSQLUtil.get(QUERY_TOOL_MENUS_BY_USER, model);
5. List<Map<String, Object>> maps = null;
6. if(parentMenuId == null)
7. maps = toolMenuDao.getJdbcTemplate().queryForList(sql, roleIds.toArray());
8. else {
9. roleIds.add(0, parentMenuId);
10. maps = toolMenuDao.getJdbcTemplate().queryForList (sql, roleIds.toArray());
11. }

 

 

引用：