本文整理自网络,侵删。
前言
我们在使用 JDBC 时, 如果把所有的 SQL 语句全写在 Java 文件中, 由于 Java 不支持 Here Document, 多行字符串要么用加号, 要么用 Java 8 的 String.join()
方法来连接, 同时不能对 SQL 语句进行语法加亮, 所以这样的 SQL 字符串阅读性很差. 别说为何不用 Hibernate 之类的而不直接写原始的 SQL 语句, 在操作复杂的系统时还是会用到 JdbcTemplate 吧.
所以我们希望能把 SQL 语句写在单独的 *.sql 文件里, 这样很多编辑器就能语法高亮显示, 或在输入时还能得到智能提示.
有种办法是把 *.sql 用作为属性文件, 那么在其中定义多行的 SQL 语句时就得这样
select.user=select id, firstname, lastname, address \ from users \ where id=?
加载后就能用 getProperty("select.user")
来引用相应的语句了. 属性文件的换行与 Bash 一样, 也是用 \, 但如此, 则 *.sql 并非一个纯粹的 SQL 文件, 不能正确的进行语法加亮, 一旦写上 SQL 的注释 -- 就更是在添乱了.
所以我们的第二个方案是: 首先 *.sql 就该是一个真正的 SQL 文件, 而不是伪装的属性文件, 为了能在程序中引用每一条 SQL 语句, 我们该如何表示各自的 Key 呢? 这里的灵感仍然是来自于 Linux Shell, 在 Linux Shell 中指定执行环境的用了特殊的注释方式 #!, 如
#!/bin/bash #!/usr/bin/env python
依葫芦画瓢, SQL 的标准单注释是 --, 因而我们也创建一个特别的注释 --!, , 其后的字符串就是接下来 SQL 语句的 Key.
举例如下
--!select.user select id, firstname, lastname, address from users where id=? --!update.user update ........
--! 之后是 key select.user
, 往下在未到文件结束, 或是遇到下一个 --! 之前就是这个 key 对应的完整 SQL 语句的内容.
本文以 Spring 项目为例来演示如何应这个 SQL 文件, 其实在其他类型的 Java 项目中同样可以借鉴.
因为这是一个真正的 SQL 文件, 所以在 Spring 中我们无法直接作为属性文件来加载. 假设我们把该文件存储为 src/resources/sql/queries.sql, 因此我们不能直接用
@PropertySource(value = "classpath:sql/queries.sql") public class AppConfig { ...... }
加载该文件.
幸好 PropertySource 注解还有一个属性 factory, 类型为 PropertySourceFactory, 这就是我们作文章的地方, 马上着手自定义一个 SqlPropertySourceFactory, 在其中总有办法把一个 *.sql 的内容转换为 Properties. 因此将来我们要加载 sql/queries.sql 文件所用的注解形式就会是
@PropertySource(value = "classpath:sql/queries.sql", factory = SqlPropertySourceFactory.class) public class AppConfig { ......}
接下来就是本文的关键, 看看 SqlPropertySourceFactory 的实现
SqlPropertySourceFactory.java
package cc.unmi; import org.springframework.core.env.MapPropertySource; import org.springframework.core.env.PropertySource; import org.springframework.core.io.support.EncodedResource; import org.springframework.core.io.support.PropertySourceFactory; import java.io.BufferedReader; import java.io.IOException; import java.util.*; import java.util.stream.Collectors; public class SqlPropertySourceFactory implements PropertySourceFactory { private static final String KEY_LEADING = "--!"; @Override public PropertySource<?> createPropertySource(String name, EncodedResource resource) throws IOException { Deque<Pair> queries = new LinkedList<>(); new BufferedReader(resource.getReader()).lines().forEach(line -> { if (line.startsWith(KEY_LEADING)) { queries.addLast(new Pair(line.replaceFirst(KEY_LEADING, ""))); } else if (line.startsWith("--")) { //skip comment line } else if (!line.trim().isEmpty()) { Optional.ofNullable(queries.getLast()).ifPresent(pair -> pair.lines.add(line)); } }); Map<String, Object> sqlMap = queries.stream() .filter(pair -> !pair.lines.isEmpty()) .collect(Collectors.toMap(pair -> pair.key, pair -> String.join(System.lineSeparator(), pair.lines), (r, pair) -> r, LinkedHashMap::new)); System.out.println("Configured SQL statements:"); sqlMap.forEach((s, o) -> System.out.println(s + "=" + o)); return new MapPropertySource(resource.toString(), sqlMap); } private static class Pair { private String key; private List<String> lines = new LinkedList<>(); Pair(String key) { this.key = key; } } }
我们定义的 src/resources/sql/queries.sql 文件内容如下:
--external queries in this file --!select_users_by_id select id, firstname, lastname, address from users where id=? --!add_user insert users(id, firstname, lastname, address) values(DEFAULT, ?, ?, ?) -- --!no_statement --- --!update update users set firstname=? where id=?
最后是如何应用它, 我们以 SpringBoot 的方式来启动一个 Spring 项目
相关阅读 >>
ms-sql 2005拒绝了对对象 'xxx' (数据库 'xxx',架构 'dbo')的 select
sqlserver新特性sequencenumber用法介绍
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。