Spring项目里将SQL语句写在.sql文件中的方法

前言

我们在使用 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 项目

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

既然已转换为普通的属性了, 所以可以通过表达式 ${key} 或 env.getproperty("key") 来引用它们.

执行上面的代码, 输出如下:

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 语句了.

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。