使用freemarker创建动态SQL

使用freemarker创建动态SQL

 

    使用Spring的JdbcTempl时为了让SQL文便于管理，一般采用的将SQL文写在静态的字符串常量中。形式如下：

public class UserDao {

	private static final String QUERY_USER_BY_ID = "select id, email, login_name from LOGIN_USER where id=?";
	private static final String QUERY_USER_BY_IDS = "select id, email, login_name from LOGIN_USER where id in(:ids)";
	private static final String QUERY_USER = "select id, email, login_name from LOGIN_USER order by id";
	private static final String QUERY_USER_BY_LOGINNAME = "select id,email,login_name from LOGIN_USER where login_name=:login_name";
	private static final String INSERT_USER = "insert into LOGIN_USER(id, login_name, email) values(:id, :loginName, :email)";

	private SimpleJdbcTemplate jdbcTemplate;

	private UserMapper userMapper = new UserMapper();

	private class UserMapper implements RowMapper<User> {
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User user = new User();
			user.setId(rs.getString("id"));
			user.setEmail(rs.getString("email"));
			user.setLoginName(rs.getString("login_name"));
			return user;
		}
	}

	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * 查询单个对象.
	 */
	public User queryObject(String id) {
		return jdbcTemplate.queryForObject(QUERY_USER_BY_ID, userMapper, id);
	}

	/**
	 * 查询对象列表.
	 */
	public List<User> queryObjectList() {
		return jdbcTemplate.query(QUERY_USER, userMapper);
	}

	/**
	 * 查询结果Map列表.
	 */
	public List<Map<String, Object>> queryMapList() {
		return jdbcTemplate.queryForList(QUERY_USER);
	}

	/**
	 * 使用Map形式的命名参数.
	 */
	public User queryByNamedParameter(String loginName) {
		Map<String, Object> map = Maps.newHashMap();
		map.put("login_name", loginName);

		return jdbcTemplate.queryForObject(QUERY_USER_BY_LOGINNAME, userMapper, map);
	}

	/**
	 * 使用Map形式的命名参数.
	 */
	public List<User> queryByNamedParameterWithInClause(Long... ids) {
		Map<String, Object> map = Maps.newHashMap();
		map.put("ids", Arrays.asList(ids));

		return jdbcTemplate.query(QUERY_USER_BY_IDS, userMapper, map);
	}

	/**
	 * 使用Bean形式的命名参数, Bean的属性名称应与命名参数一致. 
	 */
	public void createObject(User user) {
		//使用BeanPropertySqlParameterSource将User的属性映射为命名参数.
		BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(user);
		jdbcTemplate.update(INSERT_USER, source);
	}

	/**
	 * 批量插入/更新对象,使用Bean形式的命名参数.
	 */
	public void batchCreateObject(List<User> userList) {
		int i = 0;
		BeanPropertySqlParameterSource[] sourceArray = new BeanPropertySqlParameterSource[userList.size()];

		for (User user : userList) {
			sourceArray[i++] = new BeanPropertySqlParameterSource(user);
		}

		jdbcTemplate.batchUpdate(INSERT_USER, sourceArray);
	}

}

 

但是实际的项目中的SQL文不可能是这样固定的，他是有条件的动态SQL文，这时候可以使用freemarker来动态生成你需要的SQL文。可以在一个文件里面写SQL文：

<?xml version="1.0" encoding="utf-8" ?>
<commands>
  <command id="sel_login_user_001">
    <desription>
      SQL文的用途的描述。
      例：
      搜索用户时的动态条件SQL文。
    </desription>
    <sql><![CDATA[
		SELECT id, email, login_name 
		FROM login_user
		WHERE 1=1

		<#if loginName??>
		AND login_name=:loginName
		</#if>
		<#if email??>
		AND email=:email
		</#if>

		ORDER BY id
    ]]>
    </sql>
  </command>
...
</commands>

 

 /**
 * 使用freemarker生成sql的工具类.
 * 
 * @author tivan
 */
public class SQLParser {

	public static String getSql(String commandId, Map<String, ?> model) {
		try {
			Configuration cfg = new Configuration();
			StringTemplateLoader sTmpLoader = new StringTemplateLoader();
			sTmpLoader.putTemplate(commandId, getSqlTemplate(commandId));
			cfg.setTemplateLoader(sTmpLoader);
			cfg.setDefaultEncoding("UTF-8");
			Template template = cfg.getTemplate(commandId);    
	        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);
		}
	}
	
	public static String getSqlTemplate(String commandId) {
		// 取得配置文件中的SQL文模板
		return Commands.GetDbCommand(commandId);
	}
}

 

 使用freemarker生成sql的工具类,没有进行具体的性能测试，实际应用中应该考虑这些因素。

本人曾经也有这个想法，使用Freemarker+ spring jdbc完全替换iBatis,没想到LZ快人一步，呵呵。不错！！！

以前有个代替ibatis的开源项目叫o/r broker，它的xml配置文件里面就是用freemarker或者velcity来写脚本的，可惜这个项目在05年以后就没再更新了。

不知楼主Commands类中的GetDbCommand方法是怎么写的？如果是解析xml文件的话是否考虑到线程安全性的问题？

启动时把XML文件加载到java的Properties 里面。

代码大概如下

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.util.List;
import java.util.Properties;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.xml.sax.EntityResolver;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

public class SQLBuildHelp {
...................
	Properties properties = new Properties();

	private static class Resolver implements EntityResolver {

		public InputSource resolveEntity(String pid, String sid)
				throws SAXException {
			if (sid.equals("http://java.sun.com/dtd/properties.dtd")) {
				InputSource is = new InputSource(
						new StringReader(
								"<?xml version=\"1.0\" encoding=\"UTF-8\"?><!-- DTD for properties --><!ELEMENT properties ( comment?, entry* ) ><!ATTLIST properties version CDATA #FIXED \"1.0\"><!ELEMENT comment (#PCDATA) ><!ELEMENT entry (#PCDATA) ><!ATTLIST entry  key CDATA #REQUIRED>"));
				is.setSystemId("http://java.sun.com/dtd/properties.dtd");
				return is;
			} else {
				throw new SAXException((new StringBuilder()).append(
						"Invalid system identifier: ").append(sid).toString());
			}
		}

		private Resolver() {
		}

	}

	@SuppressWarnings("unchecked")
	public void loadFromXml(InputStream is) throws JDOMException, IOException {

		SAXBuilder builder = new SAXBuilder();
		builder.setEntityResolver(new Resolver());
		Document doc = builder.build(is);
		Element sqls = doc.getRootElement();
		List list = sqls.getChildren("command");
		if (list == null || list.isEmpty())
			return;
		for (int i = 0; i < list.size(); i++) {
			Element element = (Element) list.get(i);
			String key = element.getAttributeValue("id");
			String value = element.getChildText("sql");
			properties.setProperty(key, value);
		}
	}
...........................
}

对于配置文件的读取建议使用 Commons Configuration

楼主有没有测试过性能？