[JAVA] 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.