Spring jdbc templates : What is the best way to keep sql statement out of code

There doesn't seem to be a named query support in Spring JDBC Templates. By named query I mean the facility to reference sql statement by name in java code and keep开发者_如何转开发 the actual statements in some configuration file.

In the absence of out-of-box support, I'm researching the best approach to keep the sql statements outside java code.

Here are the alternatives:

1. properties file
2. xml properties file
3. spring context xml (dependency inject)

comments?

Java - Storing SQL statements in an external file

I did one system where queries were stored in the DB, too, which for what we needed was absolutely perfect, but very entertaining. The query to do query lookups was in the DB as well, which caused great hilarity when it broke (don't ask).

All the approaches you've considered as alternative will work, yet you are adding additional layer to your design if you move the statements out of Java classes which to me is unnecessary. Consider the overhead of keeping the statements outside Java class.

• Properties and XML will have be loaded to fetch a single statement.
• Inject will inject unnecessary statements into classes.

If I were you, I will create a class and have the statement listed in static final. This makes it transparent, avoid unnecessary layers and makes it flexible for whoever is going to maintain your code in the future.

Example:

//This does the job
public class SQLStatements
{
  static public final String GET_NAME="SELECT Name FROM some_table WHERE ID=?";
}

I would prefer to store all the SQL queries in properties or xml file. Then fetch all the queries and store them in Map or Properties object at the program boot up.

This provides the feasibility to change the query even after code deployment. We can change the query in property file and then restart the application.