Where do you store long/complete queries used in code?

Question

Here's my situation. I've noticed that code gets harder to maintain when you keep embedding queries in every function that will use them. Some queries tend to grow very fast and tend to lose readability after concatenating every line. Another issue that comes with all of the concatenation is when you must test a specific query and paste it. You are forced to remove all of the " that held your query string together.

So my question is, what methods are being used to separate queries from the code? I have tried searching but it doesn't look like it's the right thing because i'm not finding anything relevant.

I'd like to note that views and stored procedure are not possible since my queries fetch data from a production database.

Thank you.

Answer 1

I avoid this problem by wrapping queries in classes that represent the entities stored in the table. So the accounts table has an Account object. It'll have an insert/update/delete query.

I've seen places where the query is stored in a file and templates are used to replace parts of the query.

Java had something called SQLJ - don't know if it ever took off.

LINQ might provide some way around this as an issue too.

Answer 2

Risking being accused of not answering the question, my suggestion to you would be simply don't. Use an O/RM of your choice and you'll see this problem disappear in no time.

Answer 3

If you follow an MVC pattern, then your queries should be all in the model - i.e. the objects representing actual data.

If not, then you could just put all your repetitive queries in script files, including only those needed in each request.

However, concatenating and that kind of stuff is hard to get rid of; that's why programmers exist :)

Answer 4

These two words will be your best friend: Stored Procedure

Answer 5

I usually create a data class that represents the data requirements for objects that are represented in the database. So if I have a customer class, then I create a customerData class as well that houses all the data access logic in them. This keeps data logic out of your entity classes. You would add all you CRUD methods here as well as custom data methods.

Youc an also use Stored Proceedures or an ORM tool depending on your language.

The main key is to get your data logic away from your business and entity logic.

Answer 6

I use stored procedures in my production environment, and we process the rows too...