Clean code – store native SQL statements in external file

In certain schools of thought, an emphasis is placed on experientially reaching spiritual enlightenment, or nirvana, via meditation. When it comes to writing code, putting any person that may have to look at your code, including yourself, in a state free from suffering, takes writing clean and maintainable code.

Having attended a Clean Code book inspired talk on Clean Code a few months ago, and in piety worn the LIVESTRONG-like CLEAN CODE bracelet for two days, I had been itching to find something significant to clean. What had been irritating me for months, if not years, was native SQL statements contained in Java class files, as Strings, split over multiple lines and painstakingly indented to make them legible. It makes no sense to keep them in a Java class as Strings, they logically should be kept in plain text format.

Naturally I consulted the Web and I relied heavily on this post. My requirements where slightly different though:

• It should take me no more than half an hour reading any requisite documentation.
• Statements should ideally allow parameters, but not necessarily as the bulk of the native queries I wanted to move to a text file where reporting queries that did not require parameters i.e. I was willing to settle for second prize that involved some cleaning.
• Queries should remain legible and not be split in any way. In other words, I should be able to cut and paste from .sql files or pgAdmin III, the PostgreSQL Administration Tool.

An example of what I wanted to clean is shown below. Apart from the fact that indenting the String so that the query remain legible takes ages, it just looks out of place and if you ever need to work on the query its a nightmare. In my mind it is best removed from the Java source and stored in a format that it is meant be stored in, plaintext.

import javax.persistence.EntityManager;
import java.util.Properties;
import javax.persistence.Query;
import java.io.InputStream;

EntityManager entityManager;

public String extremelyUsefulPercentage() {
 Properties properties = readProperties("native-postgresql-queries.xml");
 String somethingExtremelyUsefulSql
     = "SELECT round(unrounded_percentage,2) AS rounded_percentage " +
       "FROM" +
         "(SELECT (" +
             "(" +
                "(" +
                    "SELECT round(count(*),2)" +
                    "FROM glacier" +
                    "JOIN country ON (countryid = country.id)" +
                    "WHERE ismelting AND country.name LIKE 'n%')" +
                 "/" +
                 "(" +
                    "SELECT round(count(*),2)" +
                    "FROM glacier" +
                    "JOIN country ON (countryid = country.id)" +
                    "WHERE ismelting" +
                 ")" +
               ") * 100" +
          ") AS unrounded_percentage) AS subquery";
 Query extremelyUsefulQuery = entityManager.createNativeQuery(somethingExtremelyUsefulSql);
 return extremelyUsefulQuery.getResultList().get(0).toString();
}

public Properties readProperties(String xmlFileName) throws Exception {
 Properties properties = new Properties();
 InputStream is = this.getClass().getClassLoader().getResourceAsStream(xmlFileName);
 properties.loadFromXML(is);
 return properties;
}

The solution, which cuts the enormous native query Java String down to two neat lines, is shown below. It uses Java’s Properties class and the loadFromXML method. One could also use name value pairs, instead of loading from XML, but doing so will inevitably involve splitting up a lengthy query into multiple name value pairs, since the value part of a particular name must be on a single line. So using XML is the logical and cleanest option.

import javax.persistence.EntityManager;
import java.util.Properties;
import javax.persistence.Query;
import java.io.InputStream;

EntityManager entityManager;

public String extremelyUsefulPercentage() {
 Properties properties = readProperties("native-postgresql-queries.xml");
 String somethingExtremelyUsefulSql = properties.getProperty("something.extremely.useful.sql");
 Query extremelyUsefulQuery = entityManager.createNativeQuery(somethingExtremelyUsefulSql);
 return extremelyUsefulQuery.getResultList().get(0).toString();
}

public Properties readProperties(String xmlFileName) throws Exception {
 Properties properties = new Properties();
 InputStream is = this.getClass().getClassLoader().getResourceAsStream(xmlFileName);
 properties.loadFromXML(is);
 return properties;
}

File native-postgresql-queries.xml that now contains the previously offending native SQL query:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
 <comment>This query returns something extremely useful.
 </comment>
 <entry key="something.extremely.useful.sql">
 SELECT round(unrounded_percentage,2) AS rounded_percentage
 FROM
 (SELECT (
    (
      (
         SELECT round(count(*),2)
         FROM glacier
         JOIN country ON (countryid = country.id)
         WHERE ismelting AND country.name LIKE 'n%')
      /
      (
        SELECT round(count(*),2)
        FROM glacier
        JOIN country ON (countryid = country.id)
        WHERE ismelting
    )
  ) * 100
 ) AS unrounded_percentage) AS subquery
 </entry>
</properties>

I’m not sure if the approach I have used is best, and I may have commited a plethora of Clean Code sins in the examples above, but I feel the code has been made significantly more maintainable and I feel substantially more enlightened.