Panasonic Youth rob sanheim writes about software, business, ruby, music, stuff and things

Keep your sql and your java code separate

(assumption: using Spring JDBC or some other thin JDBC layer, obviously this does not apply to ORM)

At what point in the process do you decided to take all your sql out of your data access layer and put it into plain property files or sql files? This gives you the PowerOfPlainText but adds an additional layer of indirection, and could also violate DRY where you could generate SQL programmatically if you kept more of it in the code.

This would also jive more with separation of concerns. If you add three fields to the where clause to better hit an index, you shouldn’t have to touch your java code at all. If you want to map things into new model objects, you shouldn’t have to worry about the sql beyond “I have these five fields, put them somewhere”.

Right now I’m leaning towards having sql out in plain .sql files for everything but the smallest apps. This gives you the power to run sql directly with whatever tool you want, and also lets you recreate schemas or test data very easily. This is the route iBatis takes, and I haven’t had a chance to use it in anger yet, but everything I’ve heard about it has been pretty good.

(kudos to Jim for letting me rant about this to him over IM)

Rob:
I like the idea of separating SQL from the rest of your code in plain text format — I mention it in a post about using properties files to clarify code.
Some people will take issue with this, however, and tell you that you should just use an ORM. They also may not like the idea that you could potentially end up having a connection open over too large a scope with this approach.
This will eventually lead down the road to something called “Table-Oriented-Programming” (I think…), and is apparently a very “LISPy” concept.
Thanks for the interesting post. :-)

I recently had an idea to separate SQL and Java code. I’m developing an application and I want the SQL to be optimized for multiple database servers, but be all in one application build. So, it’s a little different but take a look.