How do you store SQL queries in code?

Question

SQL

Good afternoon!
I write in Java for Android, but I think the question can be relevant for any language.
There were difficulties with storing the text of sql queries in the code. Tried several approaches, but each of them has its own drawbacks:
1) Store requests as text as they are. But at the same time, it is difficult to find a mention of any column in the code; you can make a typo, which is then difficult to find. All the disadvantages of hardcode are obvious.
2) Store column and table names as constants. But then queries generally become completely unreadable due to multiple concatenations.
3) Use generating templates like builders and factories to create queries. But then the code turns out to be darling, and there is too much of it. And again, how to store column names inside builders?
4) It is impossible to use any ORM due to some features of the system.
I would like to hear how you store sql queries in code.

Answer 1

I store in XML.
For the hostname, the request identifier.
Attributes can store a list of types (for mysqli).
<getAssoc params="is">
SELECT content_id FROM assoc WHERE news_id=? AND type=? ORDER BY sort ASC
</getAssoc>

Answer 2

Just transferring work with the database from java to php. If the queries are not in the form of sql, even with inserts, then in order to find the right one, you have to cut into the code, which makes it extremely difficult to work.
Or ORM normal to fasten, or lines.

Answer 3

1 + tests for each
column query by code search are perfectly searchable if you make it a rule to enclose them in double quotes.

Answer 4

Query builder is very helpful.

Answer 5

I store columns directly in classes with the db_ prefix

 public class User 
{
  private String db_Name
  private DateTime db_DOB

}

To compose a query, I use QueryBuilder through Reflection, I pull out the fields

Answer 6

I usually use points 1 or 2, the second more often

Answer 7

What is unreadable here? Any typo will result in an error describing the wrong column.

$sql = "SELECT shop_groups.shop_group_id, shop_groups.short_name
            FROM `shop_groups`
            WHERE shop_groups.parent_id = 0
            AND country_id = {$this->_cid}";

Answer 8

From the point of view of code consistency, everything that is more difficult than sampling from one source and filters, if possible, can be transferred to views.

Answer 9

try Scala and some type-safe query builder %)

Answer 10

You will have to make some kind of ORM or query builder (the latter is easier). You can make a query builder from an XML definition (still more structural than SQL), write the names of tables and fields as constants through the XSD mechanism ...

Answer 11

2+3
Names as constants + query factory.