Preface
When we use JDBC, if we write all SQL statements in Java files, since Java does not support Here Document, multi-line strings should either use the plus sign or use Java 8's() Methods are used to connect, and SQL statements cannot be syntax highlighted, so such SQL strings are very poorly readable. Not to mention why you don’t use Hibernate and other things without writing original SQL statements directly, you will still use JdbcTemplate when operating complex systems.
So we hope to write SQL statements in a separate *.sql file so that many editors can highlight syntax, or get smart prompts when entering.
There is a way to use *.sql as a property file, so this is what it takes to define multiple lines of SQL statements in it.
=select id, firstname, lastname, address \ from users \ where id=?
It can be used after loadinggetProperty("") To quote the corresponding statement. The line breaks of the property file are the same as Bash, and they also use \. However, *.sql is not a pure SQL file and cannot be syntax-highlighted correctly. Once SQL comments are written, it will be even more messy.
So our second solution is: First of all, *.sql should be a real SQL file, not a disguised attribute file. In order to reference each SQL statement in the program, how should we represent our respective keys? The inspiration here is still from Linux Shell, which specifies the execution environment in Linux Shell in a special comment method #!, such as
#!/bin/bash #!/usr/bin/env python
By copying it, the standard single comment of SQL is --, so we also create a special comment --!, , and the string followed is the key of the next SQL statement.
For example,
--! select id, firstname, lastname, address from users where id=? --! update ........
--! Thenkey , go down until the end of the file, or encounter the next --! The content of the complete SQL statement corresponding to this key is before.
This article uses the Spring project as an example to demonstrate how to apply to this SQL file. In fact, it can also be used in other types of Java projects.
Because this is a real SQL file, we cannot load it directly as a property file in Spring. Suppose we store the file as src/resources/sql/, so we cannot use it directly
@PropertySource(value = "classpath:sql/")
public class AppConfig { ...... }
Load the file.
Fortunately, the PropertySource annotation also has a property factory, type PropertySourceFactory, which is where we write articles. Let's start customizing a SqlPropertySourceFactory immediately, and there is always a way to convert the content of *.sql into Properties. Therefore, in the future, the annotation form used by us to load the sql/ file will be
@PropertySource(value = "classpath:sql/", factory = )
public class AppConfig { ......}
Next is the key to this article, take a look at the implementation of SqlPropertySourceFactory
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import .*;
import ;
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(()).lines().forEach(line -> {
if ((KEY_LEADING)) {
(new Pair((KEY_LEADING, "")));
} else if (("--")) {
//skip comment line
} else if (!().isEmpty()) {
(()).ifPresent(pair -> (line));
}
});
Map<String, Object> sqlMap = ()
.filter(pair -> !())
.collect((pair -> ,
pair -> ((), ),
(r, pair) -> r, LinkedHashMap::new));
("Configured SQL statements:");
((s, o) -> (s + "=" + o));
return new MapPropertySource((), sqlMap);
}
private static class Pair {
private String key;
private List<String> lines = new LinkedList<>();
Pair(String key) {
= key;
}
}
}
The contents of the src/resources/sql/ file we define are as follows:
--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=?
Finally, how to apply it, we start a Spring project in SpringBoot
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@SpringBootApplication
@PropertySource(value = "classpath:sql/", factory = )
public class DemoApplication implements EnvironmentAware {
private Environment env;
@Value("${add_user}")
private String sqlAddUser;
@Bean
public String testBean() {
("SQL_1:" + ("select_users_by_id"));
("SQL_2:" + sqlAddUser);
return "testBean";
}
public static void main(String[] args) {
(, args);
}
@Override
public void setEnvironment(Environment environment) {
env = environment;
}
}
Since it has been converted to a normal property, it can be expressed through the expression${key} or("key") To quote them.
Execute the above code and the output is as follows:
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, ?, ?, ?)
It's that simple. Of course, the *.sql file should be written more rigorously. We can gradually improve SqlPropertySourceFactory in the future to deal with more possibilities. No matter what, it is a real SQL file, and it can also easily refer to the SQL statements defined in it in the code like any other property.
Summarize
The above is the entire content of this article. I hope the content of this article will be of some help to your study or work. If you have any questions, you can leave a message to communicate.