How do you do conditional filters in pure SQL from a backend Java / Python app, without doing string concatenation?

Not a fan of all the proxy object circus ORMs do but I'd leave row-> domain object mapping and filter building to some library. Sweet spot is probably something akin to Android Room / Micronaut Data JDBC.

▲

minitech 3 days ago | parent | next [-]

Query builders that operate at the SQL level. (A popular example of that in Python is SQLAlchemy Core, but there are better ways to do it, especially in better-typed languages.)

	▲	yencabulator 2 days ago \| parent [-]
		I was just pondering, for a little Rust project of mine, whether to suffer some weird ORM-smelling query builder, or to just build the AST with https://github.com/apache/datafusion-sqlparser-rs/ and convert to string..

▲

foobazgt 3 days ago | parent | prev | next [-]

JOOQ (http://jooq.org) is pretty fantastic for this, and it's my go-to for working with RDBMs' on the JVM. It provides a DSL-like API that lets you write pretty much any SQL you need in a type-safe way (without string concatenation).

▲

crazygringo 3 days ago | parent | prev | next [-]

What's wrong with string concatenation?

▲

whatevaa 3 days ago | parent | next [-]

Guaranteed source of bugs in complex cases.

	▲	crazygringo 3 days ago \| parent [-]
		More complex cases are more likely to have bugs period, just in their logic. String concatenation isn't really a major source of that. Just make sure your parentheses match, as you need to do no matter what, and include a space at the start and end of each string to make sure you don't accidentally smush terms together likethis.

▲

t-writescode 3 days ago | parent | prev [-]

Simpler SQL injection risk and more testing to make sure all potential branching paths don’t result in invalid SQL.

▲

webstrand 3 days ago | parent [-]

There's zero danger of sql injection so long as everything is being passed by parameters. You just concatenate placeholders when you need string concatenation to build the query.

	▲	crazygringo 3 days ago \| parent [-]
		Exactly this. And if you're testing, you've got to test every query combination anyways. It's not just syntax that can be wrong, but logic and performance.

▲

sgarland 3 days ago | parent | prev | next [-]

SQL has CASE statements, if you’d really like to have all branching logic in pure SQL.

▲

paulddraper 3 days ago | parent | prev [-]

String concatenation

	▲	Xss3 3 days ago \| parent [-]
		No, we must build 16 more layers of pointless abstraction in a new DSL.