[Spring Framework] Extract static SQL statement to sql file

If we had some static SQL statement, it won't change based on specific condition, then we can consider to extract them into SQL file.

It will reduce line of code in your custom repository implementation class, it will be easier to maintain as well.

Assume SQL files is located in /resource/sql/common

Therefore, we can create a utility class to read the content of SQL file:

package tw.com.abc.analysis.util;

import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.util.stream.Stream;

@Component
public class SqlFileLoader {

    public String getSqlFile(String fileName) throws IOException {
        StringBuilder sql = new StringBuilder();
        try {
            File file = ResourceUtils.getFile("classpath:sql/common/" + fileName);
            Stream<String> lines = Files.lines(file.toPath());
            lines.forEach(l -> sql.append(l).append(" \n "));
        } catch (IOException e) {
            throw new IOException("cannot find sql file", e);
        }
        return sql.toString();
    }

}