欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-08 19:09:16
前言 我们在使用 jdbc 时, 如果把所有的 sql 语句全写在 java 文件中, 由于 java 不支持 here document, 多行字符串要么用加号, 要么...

前言

我们在使用 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 语句了.

总结

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