Storing SQL Queries within a Configuration file?

Question

Hello y'all,

A general question on what everyone's opinion is on storing SQL queries within a configuration file?

Is this just another bike shed?

Cheers, Ben

Answer 1

That's what you'd do if you were using iBatis. I don't see the harm in it.

It sure beats building them up dynamically when it's unnecessary.

Answer 2

No. never. Seriously ;) Cleaning up that here at the moment.

Try looking at BLToolkit - stores them in attributes right on the abstract class they dynamically generate the whole accessing code under. Same like using it in the config file, but without writing or seeing the stupid DAL code.

Answer 3

Doesn't sound so bad to me, provided it meets the requirements.

Are you trying to allow the customer to see and edit the queries?

Are you trying to make it easier for development/testing?

I certainly prefer this to embedding the queries in code.

But there is a risk that this file will bloat with a myriad of slightly different queries (SORT ASCENDING, SORT DESCENDING, SUM(col1) + SUM(col2), SUM(col1) + SUM(col3), etc.)

Answer 4

In that case , I'd store them in the db :

1. Would make it harder for regular joe to fiddle around with my queries and jeopardize my app.
2. They cannot deduce how I store my data.
3. The changes would be reflected automatically without a need to "push" the config file.

But really, I'm extrapolating without knowing your actual scenario. But off the cuff , I'd not want others to see how I interact with my schema or how it is laid out.

Answer 5

What is your reasoning for placing them anywhere but in the code? I've found that more often than not the SQL changes at the same rate as your code, meaning that if you change UserDao.java you'll likely have to change sql-statements.properties at the same time. With that being said, code is read many more times than it is written, so writing readable code is critical in a clean codebase. With the SQL statements in a separate file, a developer has to look elsewhere to figure out what query your UserDao uses, making understanding your code more difficult.

The short answer? I'd avoid it if possible.