The Spring project writes the SQL statement in the.sql file

preface

When we use JDBC, if we write all SQL statements in Java files, since Java does not support Here Document, multi-line strings are either plus or Java 8 String.join() The SQL string is poorly read because it does not syntactically highlight the SQL statement. JdbcTemplate is used in complex systems, even if you don't write the original SQL statement instead of Hibernate or something like that.

So we want to be able to write SQL statements in a separate *.sql file, so that many editors can highlight the syntax or get smart hints when typing.

One way to do this is to use *.sql as a properties file, which is what you need to do when you define multi-line SQL statements

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

It loads and it works getProperty("select.user") *.sql is not a pure SQL file, so it cannot be syntaxically highlighted correctly.

So our second solution is: first, *.sql should be a real SQL file, not a disguised properties file. In order to refer to every SQL statement in the program, how should we represent each Key? The inspiration here is still from Linux Shell, which specifies the execution environment in Linux Shell with a special annotation #! , such as

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

To follow suit, SQL's standard single comment is --, so we also created a special comment --! The string that follows is Key of the next SQL statement.

For example, the following

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

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

- -! After that key select.user , down at the end of the file, or encountered the next 1 -! So that was the full SQL statement for this key.

This article takes the Spring project as an example to demonstrate how to use this SQL file, which can also be used in other types of Java projects.

Because this is a real SQL file, so we cannot directly as in Spring properties file to load. Suppose that we store this file for src/resources/sql/queries sql, so we can't use directly

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

Load the file.

Fortunately, the PropertySource annotation also has a property factory of type PropertySourceFactory, so that's where we'll do this. We'll start customizing an SqlPropertySourceFactory and there will always be a way to convert a *.sql content to Properties. So in the future we'll load the sql/ queries.sql file in the form of an sql/ queries.sql

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

The key to this article is to look at the implementation of 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;
  }
 }
}

We define src/resources/sql/queries sql file content is 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=?

And finally, how do we apply it, and we start an Spring project in an SpringBoot way

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

Now that you've converted to normal properties, you can use expressions ${key} or env.getProperty("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, ?, ?, ?)

Of course, the *.sql file had better be more precise, and we can gradually improve SqlPropertySourceFactory to deal with more possibilities in the future. Anyway, it is a real SQL file, which can refer to the SQL statements defined in it as easily as any other property in the code.

conclusion

The above is the whole content of this article, I hope the content of this article to your study or work can bring 1 definite help, if you have questions you can leave a message to communicate.