What's the rationale for using static final Strings for SQL statement strings?

Question

It seems many places incorporate as best practice the use of class constants for SQL strings.

So instead of:

String sql = "select * from users";
PreparedStatement stmt = conn.prepareStatement(sql);

It's preferred to do:

private static final String SQL_SELECT_USERS = "select * from users";

void someMethod() {
  ...
  PreparedStatement stmt = conn.prepareStatement(SQL_SELECT_USERS);
  ...
}

What are the advantages to the latter? It seems to me it's less readable.

Thanks.

Answer 1

If it is a short text and it is used only in one place then there is no need to make it a field. In your case it could be this

PreparedStatement stmt = conn.prepareStatement("select * from users");

You can find a lot of this kind of coding style in JDK source, like this one

    if (is == null) {
        throw new IllegalArgumentException("InputStream cannot be null");
    }

Answer 2

A final variable cannot be changed, by mistake or otherwise. Declaring the statement string as final makes sure to avoid this bug:

String s = "select * from users";

// many lines of code
s = "temp";
// do something with s
// many lines of code
PreparedStatement stmt = conn.prepareStatement(sql);

No matter how many lines of code you add after declaring s, if s is final the compiler will tell you when some code attempts to clobber it with a new value.

If you always do the prepared statement immediately after the variable, then simple proximity will help you avoid this error. Also, your actual example used a better name (sql rather than just s). But still, you never know who will edit your code after you write it.

Any time you can use a feature of the language to get the compiler to help you, you should do it. In this case, the final declaration lets the compiler protect you from someone clobbering your pre-defined string. Admittedly in this specific example the benefit seems pretty small, but in general constant things should be declared final and I don't see any reason to break that rule for this case.

As for declaring it private, that is just typical data hiding. If this string isn't part of the "interface" to this class, it should be private; by default, make everything private, and only make the interface stuff public.

EDIT: One more point worth considering. If you have a literal string that contains SQL, and you make some mistake when writing the SQL, the compiler cannot help you. "selct * from users" is a perfectly valid string; the Java compiler doesn't know it's an SQL error, so you find out at runtime.

You can make constants that are SQL fragments, and put them together with string concatenation. The great part about this is that if you misspell something, now you likely have a compiler error.

private final String SELECT_STAR_FROM = "select * from ";
private final String USERS_TABLE = "users";

// many lines of code
PreparedStatement stmt0 = conn.prepareStatement(SELECT_STAR_FROM + USERS_TABLE);

// this line would fail at run time
PreparedStatement stmt1 = conn.prepareStatement("selct * from users");

// this line fails at compile time and the compiler points you at it
PreparedStatement stmt0 = conn.prepareStatement(SELCT_STAR_FROM + USERS_TABLE);

When you do JNI programming you need to specify function signatures with cryptic codes. I made a bunch of constants and my JNI programs concatenate the constants together to build up the function signatures. This is C code, not Java, but it illustrates the same idea as the above.

#define JSIG_CONSTRUCTOR "<init>"

If I made a typo and wrote "<intt>" for a constructor, the C compiler couldn't help me; it would be a runtime error (JNI would fail to find the constructor). But if I use JSIG_CONSTRUCTOR in my code, if I make a typo, the compiler will tell me.