前言
我們在使用 JDBC 時, 如果把所有的 SQL 語句全寫在 Java 文件中, 由于 Java 不支持 Here Document, 多行字符串要么用加號, 要么用 Java 8 的 String.join() 方法來連接, 同時不能對 SQL 語句進行語法加亮, 所以這樣的 SQL 字符串閱讀性很差. 別說為何不用 Hibernate 之類的而不直接寫原始的 SQL 語句, 在操作復雜的系統時還是會用到 JdbcTemplate 吧.
所以我們希望能把 SQL 語句寫在單獨的 *.sql 文件里, 這樣很多編輯器就能語法高亮顯示, 或在輸入時還能得到智能提示.
有種辦法是把 *.sql 用作為屬性文件, 那么在其中定義多行的 SQL 語句時就得這樣
|
1
2
3
|
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 中指定執行環境的用了特殊的注釋方式 #!, 如
|
1
2
|
#!/bin/bash#!/usr/bin/env python |
依葫蘆畫瓢, SQL 的標準單注釋是 --, 因而我們也創建一個特別的注釋 --!, , 其后的字符串就是接下來 SQL 語句的 Key.
舉例如下
|
1
2
3
4
5
6
7
|
--!select.userselect id, firstname, lastname, address from users where id=?--!update.userupdate ........ |
--! 之后是 key select.user, 往下在未到文件結束, 或是遇到下一個 --! 之前就是這個 key 對應的完整 SQL 語句的內容.
本文以 Spring 項目為例來演示如何應這個 SQL 文件, 其實在其他類型的 Java 項目中同樣可以借鑒.
因為這是一個真正的 SQL 文件, 所以在 Spring 中我們無法直接作為屬性文件來加載. 假設我們把該文件存儲為 src/resources/sql/queries.sql, 因此我們不能直接用
|
1
2
|
@PropertySource(value = "classpath:sql/queries.sql")public class AppConfig { ...... } |
加載該文件.
幸好 PropertySource 注解還有一個屬性 factory, 類型為 PropertySourceFactory, 這就是我們作文章的地方, 馬上著手自定義一個 SqlPropertySourceFactory, 在其中總有辦法把一個 *.sql 的內容轉換為 Properties. 因此將來我們要加載 sql/queries.sql 文件所用的注解形式就會是
|
1
2
|
@PropertySource(value = "classpath:sql/queries.sql", factory = SqlPropertySourceFactory.class)public class AppConfig { ......} |
接下來就是本文的關鍵, 看看 SqlPropertySourceFactory 的實現
SqlPropertySourceFactory.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
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 文件內容如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
--external queries in this file --!select_users_by_idselect id, firstname, lastname, address from users where id=? --!add_userinsert users(id, firstname, lastname, address) values(DEFAULT, ?, ?, ?)-- --!no_statement--- --!updateupdate users set firstname=? where id=? |
最后是如何應用它, 我們以 SpringBoot 的方式來啟動一個 Spring 項目
DemoApplication.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
package cc.unmi; import org.springframework.beans.factory.annotation.Value;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.context.EnvironmentAware;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.PropertySource;import org.springframework.core.env.Environment; @SpringBootApplication@PropertySource(value = "classpath:sql/queries.sql", factory = SqlPropertySourceFactory.class)public class DemoApplication implements EnvironmentAware { private Environment env; @Value("${add_user}") private String sqlAddUser; @Bean public String testBean() { System.out.println("SQL_1:" + env.getProperty("select_users_by_id")); System.out.println("SQL_2:" + sqlAddUser); return "testBean"; } public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } @Override public void setEnvironment(Environment environment) { env = environment; }} |
既然已轉換為普通的屬性了, 所以可以通過表達式 ${key} 或 env.getProperty("key") 來引用它們.
執行上面的代碼, 輸出如下:
|
1
2
3
4
5
6
7
8
9
10
|
Configured SQL statements:select_users_by_id=select id, firstname, lastname, address from users where id=?add_user=insert users(id, firstname, lastname, address) values(DEFAULT, ?, ?, ?)update=update users set firstname=? where id=?SQL_1:select id, firstname, lastname, address from users where id=?SQL_2:insert users(id, firstname, lastname, address) values(DEFAULT, ?, ?, ?) |
就這么簡單. 當然那個 *.sql 文件最好是寫得嚴謹一些, 我們可以將來對 SqlPropertySourceFactory 進行逐步完善以應對更多的可能. 不管怎么說它是一個真正的 SQL 文件, 在代碼中也能像任何別的屬性那么方便的引用其中定義的 SQL 語句了.
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。
原文鏈接：http://unmi.cc/spring-external-sql-statements/
