How to handle HUGE SQL strings in the source code

Question

I am working on a project currently where there are SQL strings in the code that are around 3000 lines.

The project is a java project, but this question could probably apply for any language.

Anyway, this is the first time I have ever seen something this bad. The code base is legacy, so we can suddenly migrate to Hibernate or something like that.

How do you handle very large SQL strings like that?

I know its bad, but I don't know exactly what is the best thing to suggest for a solution.

Answer 1

It seems to me that making those hard-coded values into stored procedures and referencing the sprocs from code instead might be high yield and low effort.

Answer 2

Does the SQL has a lot of string concatenations for the variables?

If it doesn't you can extract them a put them in resources files. But you'll have to remove the string conatentation in the line breaks.

The stored procedure approach you used is very good, but sometimes when there's need to understand what the SQL is doing, you have to switch from workspace to your favorite SQL IDE. That's the only bad thing.

For my suggestion it would be like this:

String query = "select ......."+
3000 lines.

To

ResourceBundle bundle = ResourceBundle.getBundle("queries");
String query = bundle.getString( "customerQuery" );

Well that's the idea.