When working directly with SQL instead of an ORM, how do you elegantly handle things like parameterizing table names or having flags that turns on or off filters?
In non-ORM codebasese I am seeing patterns where sql queries are "copy-pasted" together and I am not sure I like it.
Just imagine the following example had hundreds of lines of SQL and several optional filters, some of which could themselves be several lines long:
def get_data(table1, table1, use_subset=True):
if use_subset:
filter_query = "AND column2="subset_value"
else:
filter_query = ""
query = f"""
SELECT
*
FROM
{table1}
JOIN
{table2}
WHERE
column1="value"
{filter_query}
"""
You could use a query builder for this, rather than a full ORM. Programmatically generating SQL is a subset of what an ORM does, and it's one of the less contentious parts.
EDIT: Removed the reference to SimpleJdbcTemplate.
A good alternative (if you are using Java) is to use something like Spring's JDBCTemplate. This provides the following benefits over using Hibernate/JPA:
1. You craft the queries yourself and so there is no intermediate ORM language which you need to use.
2. It parameterises the queries so that you don't have to append strings to get to the final query.
String query = "INSERT INTO FORUMS (FORUM_ID, FORUM_NAME, FORUM_DESC) VALUES (?,?,?)";
jdbcTemplate.update(query, forum.getForumId(), forum
.getForumName(), forum.getForumDesc());
}
@Override
public Forum selectForum(int forumId) {
String query = "SELECT * FROM FORUMS WHERE FORUM_ID=?";
return jdbcTemplate.queryForObject(query, new ParameterizedRowMapper<Forum>() {
@Override
public Forum mapRow(ResultSet resultSet, int rowNum) throws SQLException {
return new Forum(resultSet.getInt("FORUM_ID"), resultSet.getString("FORUM_NAME"),
resultSet.getString("FORUM_DESC"));
}
}, forumId);
}
</code>
3. It fetches only the columns that you need it to fetch and across multiple tables (if need be) without fetching the "relationships" between tables unnecessarily.
The example given below shows how to use RowMappers and to fetch only the columns you need:
<code>
String query = "SELECT FORUM_ID, FORUM_NAME, FORUM_DESC FROM FORUMS WHERE FORUM_ID=?";
return (Forum) jdbcTemplate.queryForObject(query, new Object[] { Integer.valueOf(forumId) },
new RowMapper() {
public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
return new Forum(resultSet.getInt("FORUM_ID"), resultSet.getString("FORUM_NAME"),
resultSet.getString("FORUM_DESC"));
}
});
This is basically how I use ORMs. Only as a query builder. Writing plain SQLs in code can be very messy and hard to debug, and this works similar to an API the author mentioned in his article. IMO ORMs can spare some duplication, but relying too much on that is quite painful.
In non-ORM codebasese I am seeing patterns where sql queries are "copy-pasted" together and I am not sure I like it.
Just imagine the following example had hundreds of lines of SQL and several optional filters, some of which could themselves be several lines long: