Externalize sql statements in Java

One of the problems of Java is not having support for multiline strings. It will be added in JDK12 JEP 326: Raw String Literals, but I’ll probably be stuck with Java 8 for a long time.
And I doubt that I’m the only one…
Annoying thing about single line strings is handling large sql statements, so you have to write something like:

String query = "SELECT `EMP_ID`, `LAST_NAME` FROM `EMPLOYEE_TB`\n" +
               "WHERE `CITY` = ‘INDIANAPOLIS'\n" +
               "ORDER BY `EMP_ID`, `LAST_NAME`;\n";

There can be a lot more complicated sql queries, and a lot more sql queries, which makes the code and queries hard to follow and unreadable.

My hack is externalizing queries to a yaml file with the help of Jackson.

YAML is a human friendly data serialization
standard for all programming languages.
Yet Another Markup Language.

Dependencies

We’ll use Jackson for deserializing Yaml file. Check latest versions at mvnrepository.

<dependencies>
    <dependency>
        <groupId>com.fasterxml.jackson.dataformat</groupId>
        <artifactId>jackson-dataformat-yaml</artifactId>
        <version>2.8.0</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.8.0</version>
    </dependency>
</dependencies>

Code

Step 1 – create your yaml file

Below is a simple sql query, written in yaml file. In actual usage, queries might be much more complex than this example.
sql.yaml

select1: >
  SELECT `EMP_ID`, `LAST_NAME` FROM `EMPLOYEE_TB`
  WHERE `CITY` = ‘INDIANAPOLIS'
  ORDER BY `EMP_ID`, `LAST_NAME`;

Step 2 – create yaml loader

Create an abstract class, so we could use it to load more yaml files if needed.
YamlDocument.java

package yaml;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.dataformat.yaml.YAMLFactory;

public abstract class YamlDocument {

    public static<T> T load(Class<T> clazz, String resource){
        return YamlDocument.load(clazz, clazz.getResource(resource));
    }

    public static<T> T load(Class<T> clazz, java.net.URL resource){
        ObjectMapper mapper = new ObjectMapper(new YAMLFactory());
        try {
            return mapper.readValue(resource, clazz);
        } catch (Exception e) {
            throw new RuntimeException(String.format("Could not getInstance SqlResource from %s", resource), e);
        }
    }
}

Step 3 – implement loader

We’ll implement yaml loader in the SqlResource singleton – resource needs to be loaded only once.

SqlResource.java

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.dataformat.yaml.YAMLFactory;

public class SqlResource {
    public String select1;

    private static volatile SqlResource instance;
    public static SqlResource getInstance() {
        SqlResource result = instance;
        if (result == null) {
            synchronized (SqlResource.class) {
                result = instance;
                if (result == null) {
                    instance = result = YamlDocument.load(SqlResource.class, "sql.yaml");
                }
            }
        }
        return result;
    }
}

Use in code like SqlResource.getInstance().select1.