SQL code inside Java classes

Question

Our current project does not use Hibernate (for various reasons) and we are using Spring's SimpleJdbc support to perform all our DB operations. We have a utility class that abstracts all CRUD operations but complex operations are performed using custom SQL queries.

Currently our queries are stored as String constants inside the service classes themselves and are fed to a utility to be execute by the SimpleJdbcTemplate. We are at an impasse where readability has to be balanced with maintainability. SQL code inside the class itself is more maintainable since it resides with the code that uses it. On the other hand if we store these queries in an external file (flat or XML) the SQL itself would be more readable as compared to escaped java string syntax.

Has anyone encountered a similar problem? What is a good balance? Where do you keep your custom SQL in your project?

A sample query is as follows:

private static final String FIND_ALL_BY_CHEAPEST_AND_PRODUCT_IDS = 
"    FROM PRODUCT_SKU T \n" +
"    JOIN \n" +
"    ( \n" +
"        SELECT S.PRODUCT_ID, \n" +
"               MIN(S.ID) as minimum_id_for_price \n" +
"          FROM PRODUCT_SKU S \n" +
"         WHERE S.PRODUCT_ID IN (:productIds) \n" +
"      GROUP BY S.PRODUCT_ID, S.SALE_PRICE \n" +
"    ) FI ON (FI.PRODUCT_ID = T.PRODUCT_ID AND FI.minimum_id_for_price = T.ID) \n" +
"    JOIN \n" +
"    ( \n" +
"        SELECT S.PRODUCT_ID, \n" +
"               MIN(S.SALE_PRICE) as minimum_price_for_product \n" +
"          FROM PRODUCT_SKU S \n" +
"         WHERE S.PRODUCT_ID IN (:productIds) \n" +
"      GROUP BY S.PRODUCT_ID \n" +
"    ) FP ON (FP.PRODUCT_ID = T.PRODUCT_ID AND FP.minimum_price_for_product = T.sale_price) \n" +
"WHERE T.PRODUCT_ID IN (:productIds)";

This is how it would look like in a flat SQL file:

--namedQuery: FIND_ALL_BY_CHEAPEST_AND_PRODUCT_IDS
FROM PRODUCT_SKU T 
JOIN 
( 
    SELECT S.PRODUCT_ID, 
           MIN(S.ID) as minimum_id_for_price 
      FROM PRODUCT_SKU S 
     WHERE S.PRODUCT_ID IN (:productIds) 
  GROUP BY S.PRODUCT_ID, S.SALE_PRICE 
) FI ON (FI.PRODUCT_ID = T.PRODUCT_ID AND FI.minimum_id_for_price = T.ID) 
JOIN 
( 
    SELECT S.PRODUCT_ID, 
           MIN(S.SALE_PRICE) as minimum_price_for_product 
      FROM PRODUCT_SKU S 
     WHERE S.PRODUCT_ID IN (:productIds) 
  GROUP BY S.PRODUCT_ID 
) FP ON (FP.PRODUCT_ID = T.PRODUCT_ID AND FP.minimum_price_for_product = T.sale_price) 
WHERE T.PRODUCT_ID IN (:productIds)

Answer 1

I've stored SQL as both Strings inside a Java class and as separate files that were loaded at run time. I greatly preferred the latter for two reasons. First, the code is more readable by a wide margin. Second, it's easier to test the SQL in isolation if you store it in a separate file. In addition to that, it was easier to get someone better than me at SQL to help me with my queries when they were in separate files.