Extract SQL to property file with jdbcTemplate of spring boot

1.First of all

In the previous article "Access the built-in h2db of spring boot with jdbcTemplate", I wrote that it is possible to go out with SQL "@Value`". I did. Then I was asked to teach me how to do it, so I wrote this article.

By default, spring boot can read the src / main / application.properties (.yml) file and reference it with @ Value. However, it is not desirable to write SQL that is likely to increase the number of definitions in this main property file. So, this time, I would like to explain how to define a property file dedicated to SQL of jdbcTemplate.

2. Implementation

Only the contents related to cutting out SQL to an external file are described. Please refer to Previous article for the explanation of jdbcTemplate. The point is just to specify the file with the @ org.springframework.context.annotation.PropertySource annotation. The field name looks like a constant because the previous source code works as it is.

FileInfoRepository.java


// ... omitted

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;

// ... omitted

//★ Point
@PropertySource(value = "classpath:sql/FileInfoRepository.properties")
@Repository
public class FileInfoRepository {

    private static final Logger LOGGER = LoggerFactory.getLogger(FileInfoRepository.class);
    
    //★ Point
    @Value("${FileInfoReppsitory.INSERT_SQL}")
    String INSERT_SQL;
    
    @Value("${FileInfoReppsitory.DELETE_BY_KEY_SQL}")
    String DELETE_BY_KEY_SQL;
    
    @Value("${FileInfoReppsitory.UPDATE_BY_KEY_SQL}")
    String UPDATE_BY_KEY_SQL;
    
    @Value("${FileInfoReppsitory.FIND_ONE_SQL}")
    String FIND_ONE_SQL;
    
    @Value("${FileInfoReppsitory.FIND_ALL_SQL}")
    String FIND_ALL_SQL;

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    
    // omitted
}

src/main/resources/sql/FileInfoRepository.properties


FileInfoReppsitory.INSERT_SQL=INSERT INTO file_info (file_id, file_type, file_name, file_path, content_type, content_length, registered_date) values (:fileId, :fileType, :fileName, :filePath, :contentType, :contentLength, :registeredDate)
FileInfoReppsitory.DELETE_BY_KEY_SQL=DELETE FROM file_info WHERE file_id = :fileId
FileInfoReppsitory.UPDATE_BY_KEY_SQL=UPDATE file_info SET file_type = :fileType, file_name = :fileName, file_path = :filePath, content_type = :contentType, content_length = :contentLength, registered_date = :registeredDate WHERE file_id = :fileId
FileInfoReppsitory.FIND_ONE_SQL=SELECT file_id, file_type, file_name, file_path, content_type, content_length, registered_date FROM file_info WHERE file_id = :fileId
FileInfoReppsitory.FIND_ALL_SQL=SELECT file_id, file_type, file_name, file_path, content_type, content_length, registered_date FROM file_info ORDER BY file_type, registered_date

**(Caution) Since it is a property file, it must be described in one line. Please note that even long SQL cannot break a line in the middle. By the way, it's hard to see because you can't start a new line at the value where SQL is described in yml. ** **

FileInfoReppsitory:
   INSERT_SQL:
      INSERT INTO file_info (file_id, file_type, file_name, file_path, content_type, content_length, registered_date) values (:fileId, :fileType, :fileName, :filePath, :contentType, :contentLength, :registeredDate)

3. Finally

This time, I explained how to define a property file dedicated to SQL of jdbcTemplate. As you can see from the results, the readability is not good. I think the merit of jdbcTemplate is the simplicity that is quick and easy to use, but this merit is lost. Instead, the annoyance of writing SQL in a Java file is eliminated. When converting SQL to an external file, it is better to consider the advantages and disadvantages of each PJ.

Recommended Posts

Extract SQL to property file with jdbcTemplate of spring boot
How to bind to property file in Spring Boot
[Spring Boot] How to refer to the property file
Procedure to make the value of the property file visible in Spring Boot
File upload with Spring Boot
How to boot by environment with Spring Boot of Maven
How to realize huge file upload with Rest Template of Spring
How to split Spring Boot message file
[Spring Boot] I want to add my own property file and get the value with env.getProperty ().
How to read Body of Request multiple times with Spring Boot + Spring Security
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to use built-in h2db with spring boot
The story of raising Spring Boot 1.5 series to 2.1 series
Try to implement login function with Spring Boot
Try to automate migration with Spring Boot Flyway
[Java] Article to add validation with Spring Boot 2.3.1.
I wanted to gradle spring boot with multi-project
[Introduction to Spring Boot] Authentication function with Spring Security
I tried to clone a web application full of bugs with Spring Boot
How to set environment variables in the properties file of Spring boot application
Download with Spring Boot
Settings for connecting to MySQL with Spring Boot + Spring JDBC
I tried to implement file upload with Spring MVC
[Note] Configuration file when using Logback with Spring Boot
Automatically map DTOs to entities with Spring Boot API
A memorandum of addiction to Spring Boot2 x Doma2
05. I tried to stub the source of Spring Boot
I tried to reduce the capacity of Spring Boot
How to use CommandLineRunner in Spring Batch of Spring Boot
Create Restapi with Spring Boot ((1) Until Run of App)
Attempt to SSR Vue.js with Spring Boot and GraalJS
File upload with Spring Boot (do not use Multipart File)
I want to read the property file with a file name other than application.yml or application- [profile name] .yml with Spring Boot.
The story of raising Spring Boot from 1.5 series to 2.1 series part2
Output embedded Tomcat access log to standard output with Spring Boot
Try Spring Boot from 0 to 100.
Generate barcode with Spring Boot
About the function of Spring Boot due to different versions
Implement GraphQL with Spring Boot
Get started with Spring boot
Hello World with Spring Boot!
A story packed with the basics of Spring Boot (solved)
Run LIFF with Spring Boot
SNS login with Spring Boot
Introduction to Spring Boot ① ~ DI ~
Spring Boot starting with copy
Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Introduction to Spring Boot ② ~ AOP ~
Connect to database with spring boot + spring jpa and CRUD operation
Spring Boot starting with Docker
Flow until output table data to view with Spring Boot
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
Add module with Spring Boot
Getting Started with Spring Boot
Introduction to Spring Boot Part 1
Create microservices with Spring Boot
Send email with spring boot
I tried to get started with Swagger using Spring Boot
Introduction of library ff4j that realizes FeatureToggle with Spring Boot