SqlTemplate - MyBatis
SqlTemplate - MyBatis 是sql模板引擎，主要解决动态拼接sql字符串 。原理是比较简单，把模板内容构建成完成的xml，这样可以解析成相关的数据结构，再结合Ognl强大表达式计算条件。设计上参考了MyBatis动态sql部分，为了减少学习成本，兼容Mybatis大部分用法。目前能支持以下标签：
-
if
-
choose (when, otherwise)
-
trim ， where, set
-
foreach
一、快速开始
@Test
public void testIf() {
Configuration configuration = new Configuration();
SqlTemplate template = configuration.getTemplate("select *, ${col} from user where <if test='id != null ' > id = #{id} </if>");
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("col", "name");
map.put("id", "11");
map.put("b", "b");
SqlMeta meta = template.process(map);
System.out.println(meta);
// 如果你集成了 spring jdbc 你可以这样使用
// List list = jdbcTemplate.queryForList(meta.getSql(), meta.getParameter());
}
二、示例
2.1 where 条件
@Test
public void testWhere() {
Configuration configuration = new Configuration();
SqlTemplate template = configuration.getTemplate("select * from user <where> <if test='id != null ' > and id = #{id} </if> <if test=' name != null' >name =#{name}</if> </where>");
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("name", "1fffdsfdf1");
SqlMeta process = template.process(map);
System.out.println(process);
}
2.2 for 循环
@Test
public void testForEach() {
Configuration configuration = new Configuration();
SqlTemplate template = configuration.getTemplate("select * from user <where> id in <foreach item=\"item\" index=\"index\" collection=\"list\" open=\"(\" separator=\",\" close=\")\"> ${item} ${index} </foreach></where>");
HashMap<String, Object> map = new HashMap<String, Object>();
HashMap<String, Object> map2 = new HashMap<String, Object>();
map2.put("11", "11-11");
map2.put("22", "22-22");
map.put("list", map2);
SqlMeta process = template.process(map);
System.out.println(process);
}
2.3 update
@Test
public void testUpdate() {
Configuration configuration = new Configuration(false, Charset.defaultCharset());
SqlTemplate template = configuration
.getTemplate("update sys_task\n" +
" <set >\n" +
" <if test=\"updatedAt != null\" >\n" +
" updated_at = #{updatedAt},\n" +
" </if>\n" +
" <if test=\"taskStatus != null\" >\n" +
" task_status = #{taskStatus},\n" +
" </if>\n" +
" <if test=\"taskMsg != null\" >\n" +
" task_msg = #{taskMsg},\n" +
" </if>\n" +
" </set>\n" +
" where id = #{id}");
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("id", 123);
map.put("taskStatus", "1");
SqlMeta process = template.process(map);
System.out.println(process);
}
2.4 insert
@Test
public void testInsert() {
Configuration configuration = new Configuration(false, Charset.defaultCharset());
SqlTemplate template = configuration
.getTemplate("insert into sys_mail\n" +
" <trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\" >\n" +
" <if test=\"mailSenderId != null\" >\n" +
" mail_sender_id,\n" +
" </if>\n" +
" <if test=\"mailSenderName != null\" >\n" +
" mail_sender_username,\n" +
" </if>\n" +
" <if test=\"mailSenderHost != null\" >\n" +
" mail_sender_host,\n" +
" </if>\n" +
" <if test=\"mailSenderUsername != null\" >\n" +
" mail_sender_username,\n" +
" </if>\n" +
" <if test=\"mailSenderPassword != null\" >\n" +
" mail_sender_password,\n" +
" </if>\n" +
" <if test=\"mailSenderFrom != null\" >\n" +
" mail_sender_from,\n" +
" </if>\n" +
" </trim>\n" +
" <trim prefix=\"values (\" suffix=\")\" suffixOverrides=\",\" >\n" +
" <if test=\"mailSenderId != null\" >\n" +
" #{mailSenderId},\n" +
" </if>\n" +
" <if test=\"mailSenderName != null\" >\n" +
" #{mailSenderName},\n" +
" </if>\n" +
" <if test=\"mailSenderHost != null\" >\n" +
" #{mailSenderHost},\n" +
" </if>\n" +
" <if test=\"mailSenderUsername != null\" >\n" +
" #{mailSenderUsername},\n" +
" </if>\n" +
" <if test=\"mailSenderPassword != null\" >\n" +
" #{mailSenderPassword},\n" +
" </if>\n" +
" <if test=\"mailSenderFrom != null\" >\n" +
" #{mailSenderFrom},\n" +
" </if>\n" +
" </trim>");
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("mailSenderId", 123);
map.put("mailSenderFrom", "zhangsan");
SqlMeta process = template.process(map);
System.out.println(process);
}
三、鸣谢
-
部分代码摘自 mybatis
Mybatis 语法 请参考MyBatis
四、源码地址
https://code.aliyun.com/qiaoshiju/sqltemplate-mybatis.git
注意：本文归作者所有，未经作者允许，不得转载
