3

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?

Matthew Farwell
  • 60,889
  • 18
  • 128
  • 171
tapasvi
  • 322
  • 1
  • 7
  • 15

3 Answers3

5

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).

Community
  • 1
  • 1
Dave Newton
  • 158,873
  • 26
  • 254
  • 302
  • 1
    So you needed a query to get a query to get a query… Eeek! I don't think I want to contemplate that any more. Definitely not asking! – Donal Fellows Sep 29 '11 at 12:35
3

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=?";
}
Bitmap
  • 12,402
  • 16
  • 64
  • 91
  • The problem is that is forces class reloading if the queries ever change. This isn't intrinsically bad if all of your code depends on the specifics of the query anyway, but when it doesn't, and if you're in an environment where that's prohibitive or impossible, it's a non-starter. – Dave Newton Sep 29 '11 at 12:24
  • Which ever approach is used will cause reloading, unless a change listener is placed on the placeholder. – Bitmap Sep 29 '11 at 12:29
  • (Or you retrieve the query every time.) But there's a *huge* difference between "reloading a text file if a timestamp changes" and "performing a classload", which may require a full app restart. – Dave Newton Sep 29 '11 at 12:33
  • A query change calls for change in the business rule, which in effect require life cycle control hence version deploy. An app will have if not any, atomic change in queries. – Bitmap Sep 29 '11 at 12:38
  • That's not necessarily true at all. – Dave Newton Sep 29 '11 at 12:45
  • What would cause one to want to change the query of tested and deployed application running in production? – Bitmap Sep 29 '11 at 12:52
  • (Who says the queries aren't tested outside of prod?) Reporting, additional/different needs, etc. -- the exact same things that would make you change an app. – Dave Newton Sep 29 '11 at 12:54
  • Exactly! - "Reporting, additional/different needs, etc." does this call for life cycle management or you do it on the fly? – Bitmap Sep 29 '11 at 12:56
  • You seem to believe "life-cycle management" means an application restart, but that's by no means *necessary*. You also seem to believe that it's impossible to test something outside of production. – Dave Newton Sep 29 '11 at 12:58
  • Anything tested outside production should be happening in UAT. "You seem to believe "life-cycle management" means an application restart" - yes. This bring about new requirement, code change, version control and version deploy. – Bitmap Sep 29 '11 at 13:04
  • And *I'm* saying that not all production-ready changes are at the application class level that would require a restart. I don't see how this is difficult to understand, but okay. – Dave Newton Sep 29 '11 at 13:06
  • It depends on what app structure and design you've in place. – Bitmap Sep 29 '11 at 13:09
  • Of course it does--that's why I said it's not *necessarily* true. – Dave Newton Sep 29 '11 at 13:09
  • Ok Handshake! AGREED and you buy me a pint for been a friend lol. – Bitmap Sep 29 '11 at 13:12
  • Lol, as long as you buy me a pint too :) – Dave Newton Sep 29 '11 at 13:13
  • This method still has the problem with the fact the SQL is buried in Java code. In the scenario where there are many Java classes and an SQL statement needs to be change ever so slightly, it could be a pain trying to find the method where the SQL statement is created. Keeping all SQL statements in one external file removes that problem and hence why I prefer that method. – Jamie Burke Apr 11 '14 at 08:15
0

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.

Vishal
  • 127
  • 1
  • 2
  • 16