快捷導(dǎo)航

Spring項(xiàng)目里將SQL語(yǔ)句寫(xiě)在.sql文件中的方法

更新時(shí)間：2017年01月19日 08:44:22 作者：隔葉黃鶯

這篇文章主要介紹了Spring項(xiàng)目里如何將SQL語(yǔ)句寫(xiě)在.sql文件中的方法，文中給出了詳細(xì)的介紹和示例代碼，相信對(duì)大家的學(xué)習(xí)或者工作具有一定的參考借鑒價(jià)值，有需要的朋友們下面來(lái)一起看看吧。

前言

我們?cè)谑褂?JDBC 時(shí), 如果把所有的 SQL 語(yǔ)句全寫(xiě)在 Java 文件中, 由于 Java 不支持 Here Document, 多行字符串要么用加號(hào), 要么用 Java 8 的 String.join() 方法來(lái)連接, 同時(shí)不能對(duì) SQL 語(yǔ)句進(jìn)行語(yǔ)法加亮, 所以這樣的 SQL 字符串閱讀性很差. 別說(shuō)為何不用 Hibernate 之類的而不直接寫(xiě)原始的 SQL 語(yǔ)句, 在操作復(fù)雜的系統(tǒng)時(shí)還是會(huì)用到 JdbcTemplate 吧.

所以我們希望能把 SQL 語(yǔ)句寫(xiě)在單獨(dú)的 *.sql 文件里, 這樣很多編輯器就能語(yǔ)法高亮顯示, 或在輸入時(shí)還能得到智能提示.

有種辦法是把 *.sql 用作為屬性文件, 那么在其中定義多行的 SQL 語(yǔ)句時(shí)就得這樣

select.user=select id, firstname, lastname, address \
 from users \
 where id=?

加載后就能用 getProperty("select.user") 來(lái)引用相應(yīng)的語(yǔ)句了. 屬性文件的換行與 Bash 一樣, 也是用 \, 但如此, 則 *.sql 并非一個(gè)純粹的 SQL 文件, 不能正確的進(jìn)行語(yǔ)法加亮, 一旦寫(xiě)上 SQL 的注釋 -- 就更是在添亂了.

所以我們的第二個(gè)方案是: 首先 *.sql 就該是一個(gè)真正的 SQL 文件, 而不是偽裝的屬性文件, 為了能在程序中引用每一條 SQL 語(yǔ)句, 我們?cè)撊绾伪硎靖髯缘?Key 呢? 這里的靈感仍然是來(lái)自于 Linux Shell, 在 Linux Shell 中指定執(zhí)行環(huán)境的用了特殊的注釋方式 #!, 如

#!/bin/bash
#!/usr/bin/env python

依葫蘆畫(huà)瓢, SQL 的標(biāo)準(zhǔn)單注釋是 --, 因而我們也創(chuàng)建一個(gè)特別的注釋 --!, , 其后的字符串就是接下來(lái) SQL 語(yǔ)句的 Key.

舉例如下

--!select.user
select id, firstname, lastname, address
 from users
 where id=?

--!update.user
update ........

--! 之后是 key select.user, 往下在未到文件結(jié)束, 或是遇到下一個(gè) --! 之前就是這個(gè) key 對(duì)應(yīng)的完整 SQL 語(yǔ)句的內(nèi)容.

本文以 Spring 項(xiàng)目為例來(lái)演示如何應(yīng)這個(gè) SQL 文件, 其實(shí)在其他類型的 Java 項(xiàng)目中同樣可以借鑒.

因?yàn)檫@是一個(gè)真正的 SQL 文件, 所以在 Spring 中我們無(wú)法直接作為屬性文件來(lái)加載. 假設(shè)我們把該文件存儲(chǔ)為 src/resources/sql/queries.sql, 因此我們不能直接用

@PropertySource(value = "classpath:sql/queries.sql")
public class AppConfig { ...... }

加載該文件.

幸好 PropertySource 注解還有一個(gè)屬性 factory, 類型為 PropertySourceFactory, 這就是我們作文章的地方, 馬上著手自定義一個(gè) SqlPropertySourceFactory, 在其中總有辦法把一個(gè) *.sql 的內(nèi)容轉(zhuǎn)換為 Properties. 因此將來(lái)我們要加載 sql/queries.sql 文件所用的注解形式就會(huì)是

@PropertySource(value = "classpath:sql/queries.sql", factory = SqlPropertySourceFactory.class)
public class AppConfig { ......}

接下來(lái)就是本文的關(guān)鍵, 看看 SqlPropertySourceFactory 的實(shí)現(xiàn)

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 文件內(nèi)容如下:

--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=?

最后是如何應(yīng)用它, 我們以 SpringBoot 的方式來(lái)啟動(dòng)一個(gè) Spring 項(xiàng)目

DemoApplication.java

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;
 }
}

既然已轉(zhuǎn)換為普通的屬性了, 所以可以通過(guò)表達(dá)式 ${key} 或 env.getProperty("key") 來(lái)引用它們.

執(zhí)行上面的代碼, 輸出如下:

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, ?, ?, ?)

就這么簡(jiǎn)單. 當(dāng)然那個(gè) *.sql 文件最好是寫(xiě)得嚴(yán)謹(jǐn)一些, 我們可以將來(lái)對(duì) SqlPropertySourceFactory 進(jìn)行逐步完善以應(yīng)對(duì)更多的可能. 不管怎么說(shuō)它是一個(gè)真正的 SQL 文件, 在代碼中也能像任何別的屬性那么方便的引用其中定義的 SQL 語(yǔ)句了.

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流。

您可能感興趣的文章: