Spring项目里将SQL语句写在.sql文件中的方法
前言
我们在使用JDBC时,如果把所有的SQL语句全写在Java文件中,由于Java不支持HereDocument,多行字符串要么用加号,要么用Java8的String.join()方法来连接,同时不能对SQL语句进行语法加亮,所以这样的SQL字符串阅读性很差.别说为何不用Hibernate之类的而不直接写原始的SQL语句,在操作复杂的系统时还是会用到JdbcTemplate吧.
所以我们希望能把SQL语句写在单独的*.sql文件里,这样很多编辑器就能语法高亮显示,或在输入时还能得到智能提示.
有种办法是把*.sql用作为属性文件,那么在其中定义多行的SQL语句时就得这样
select.user=selectid,firstname,lastname,address\ fromusers\ whereid=?
加载后就能用getProperty("select.user")来引用相应的语句了.属性文件的换行与Bash 一样,也是用 \,但如此,则*.sql并非一个纯粹的SQL文件,不能正确的进行语法加亮,一旦写上SQL的注释--就更是在添乱了.
所以我们的第二个方案是:首先*.sql就该是一个真正的 SQL文件,而不是伪装的属性文件,为了能在程序中引用每一条SQL语句,我们该如何表示各自的Key呢?这里的灵感仍然是来自于LinuxShell,在LinuxShell中指定执行环境的用了特殊的注释方式#!,如
#!/bin/bash #!/usr/bin/envpython
依葫芦画瓢,SQL的标准单注释是--,因而我们也创建一个特别的注释--!,,其后的字符串就是接下来SQL语句的Key.
举例如下
--!select.user selectid,firstname,lastname,address fromusers whereid=? --!update.user update........
--!之后是keyselect.user,往下在未到文件结束,或是遇到下一个--!之前就是这个key对应的完整SQL语句的内容.
本文以Spring项目为例来演示如何应这个SQL文件,其实在其他类型的Java项目中同样可以借鉴.
因为这是一个真正的SQL文件,所以在Spring中我们无法直接作为属性文件来加载.假设我们把该文件存储为src/resources/sql/queries.sql,因此我们不能直接用
@PropertySource(value="classpath:sql/queries.sql")
publicclassAppConfig{......}
加载该文件.
幸好PropertySource注解还有一个属性factory,类型为PropertySourceFactory,这就是我们作文章的地方,马上着手自定义一个SqlPropertySourceFactory,在其中总有办法把一个*.sql的内容转换为Properties.因此将来我们要加载 sql/queries.sql文件所用的注解形式就会是
@PropertySource(value="classpath:sql/queries.sql",factory=SqlPropertySourceFactory.class)
publicclassAppConfig{......}
接下来就是本文的关键,看看SqlPropertySourceFactory的实现
SqlPropertySourceFactory.java
packagecc.unmi;
importorg.springframework.core.env.MapPropertySource;
importorg.springframework.core.env.PropertySource;
importorg.springframework.core.io.support.EncodedResource;
importorg.springframework.core.io.support.PropertySourceFactory;
importjava.io.BufferedReader;
importjava.io.IOException;
importjava.util.*;
importjava.util.stream.Collectors;
publicclassSqlPropertySourceFactoryimplementsPropertySourceFactory{
privatestaticfinalStringKEY_LEADING="--!";
@Override
publicPropertySource<?>createPropertySource(Stringname,EncodedResourceresource)throwsIOException{
Deque<Pair>queries=newLinkedList<>();
newBufferedReader(resource.getReader()).lines().forEach(line->{
if(line.startsWith(KEY_LEADING)){
queries.addLast(newPair(line.replaceFirst(KEY_LEADING,"")));
}elseif(line.startsWith("--")){
//skipcommentline
}elseif(!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("ConfiguredSQLstatements:");
sqlMap.forEach((s,o)->System.out.println(s+"="+o));
returnnewMapPropertySource(resource.toString(),sqlMap);
}
privatestaticclassPair{
privateStringkey;
privateList<String>lines=newLinkedList<>();
Pair(Stringkey){
this.key=key;
}
}
}
我们定义的src/resources/sql/queries.sql文件内容如下:
--externalqueriesinthisfile --!select_users_by_id selectid,firstname,lastname,address fromuserswhereid=? --!add_user insertusers(id,firstname,lastname,address) values(DEFAULT,?,?,?) -- --!no_statement --- --!update updateuserssetfirstname=?whereid=?
最后是如何应用它,我们以SpringBoot的方式来启动一个Spring项目
DemoApplication.java
packagecc.unmi;
importorg.springframework.beans.factory.annotation.Value;
importorg.springframework.boot.SpringApplication;
importorg.springframework.boot.autoconfigure.SpringBootApplication;
importorg.springframework.context.EnvironmentAware;
importorg.springframework.context.annotation.Bean;
importorg.springframework.context.annotation.PropertySource;
importorg.springframework.core.env.Environment;
@SpringBootApplication
@PropertySource(value="classpath:sql/queries.sql",factory=SqlPropertySourceFactory.class)
publicclassDemoApplicationimplementsEnvironmentAware{
privateEnvironmentenv;
@Value("${add_user}")
privateStringsqlAddUser;
@Bean
publicStringtestBean(){
System.out.println("SQL_1:"+env.getProperty("select_users_by_id"));
System.out.println("SQL_2:"+sqlAddUser);
return"testBean";
}
publicstaticvoidmain(String[]args){
SpringApplication.run(DemoApplication.class,args);
}
@Override
publicvoidsetEnvironment(Environmentenvironment){
env=environment;
}
}
既然已转换为普通的属性了,所以可以通过表达式${key}或env.getProperty("key")来引用它们.
执行上面的代码,输出如下:
ConfiguredSQLstatements: select_users_by_id=selectid,firstname,lastname,address fromuserswhereid=? add_user=insertusers(id,firstname,lastname,address) values(DEFAULT,?,?,?) update=updateuserssetfirstname=?whereid=? SQL_1:selectid,firstname,lastname,address fromuserswhereid=? SQL_2:insertusers(id,firstname,lastname,address) values(DEFAULT,?,?,?)
就这么简单.当然那个*.sql文件最好是写得严谨一些,我们可以将来对SqlPropertySourceFactory进行逐步完善以应对更多的可能.不管怎么说它是一个真正的SQL文件,在代码中也能像任何别的属性那么方便的引用其中定义的 SQL语句了.
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。