For me there are two primary reasons for having an ORM or ORM-like framework
1. Compile time checking. I hate when I'm writing a straight SQL query in a string literal and misspell one of the column names, because everything will compile and unit tests will pass, and the mistake isn't caught until I run integration tests. I want a short edit-compile-debug cycle. jOOQ is a good example of something that helps here.
2. Arbitrary filters. Often I have to expose a REST API where the user can add an arbitrary number of filters to their query. You can't write SQL literals for these because there's a combinatorial explosion of possibilities. So you wind up dynamically generating SQL yourself by concatenating strings in the WHERE clause, hoping and praying you don't accidentally allow SQL injection. I want an ORM to deal with that shit for me.
3. Modularity and abstraction. Views only get you so much, because they don't let abstract away the table or columns that you are targeting. Also, I typically don't want my views to clutter the main namespace if I'm just trying to refactor some queries. I can use the "WITH" syntax in some cases, but bizarrely it comes with an unavoidable optimiser fence on most RDBMSs (i.e. your subquery will always be materialised), which severely limits its utility.
jOOQ helps with both 1 and 2. With jOOQ you can write something like (totally off-the-cuff code incoming, written outside of an IDE with only basic syntax highlighting, be warned, etc)
List<Condition> conditions = new ArrayList<>();
if (notNull(lessThan)) {
conditions.add(MY_TABLE.MY_COLUMN.lt(lessThan));
}
if (notNull(greaterThan)) {
conditions.add(MY_TABLE.MY_COLUMN.gt(greaterThan));
}
if (notEmpty(name)) {
conditions.add(MY_TABLE.NAME.eq(name));
}
dslContext.selectFrom(MY_TABLE).where(conditions).fetch();
(This is obviously really contrived but I hope it illustrates the ideas)
We use constructions like this with jOOQ relatively frequently, especially for cases where we offer all sorts of arbitrary filter knobs (useful for internal tools especially).
In general, you can do some pretty fancy manipulation with `Condition` in jOOQ, like you can build a list of conditions and then call `DSL.or(conditionList)` to OR them all together. Stuff like that.
Source: use jOOQ in production code -- been very pleased so far, other than sometimes poor documentation
var query = from c in db.MyTable
where lessThan == null ? true : c.MyColumn < lessThan
where greaterThan == null ? true : c.MyColumn > greaterThan
where name == null ? true : c.Name == name
select c;
Although IQueryable supports your approach also:
var query = db.MyTable;
if (lessThan != null)
query = query.Where(c => c.MyColumn < lessThan);
if (greaterThan != null)
query = query.Where(c => c.MyColumn > greaterThan);
if (!String.IsNullOrEmpty(name))
query = query.Where(c => c.Name == name);
var results = query.ToList();
Thanks for your feedback. We're currently collecting API that is not documented well enough yet. Your feedback would be very welcome: https://github.com/jOOQ/jOOQ/issues/5816
1. Compile time checking. I hate when I'm writing a straight SQL query in a string literal and misspell one of the column names, because everything will compile and unit tests will pass, and the mistake isn't caught until I run integration tests. I want a short edit-compile-debug cycle. jOOQ is a good example of something that helps here.
2. Arbitrary filters. Often I have to expose a REST API where the user can add an arbitrary number of filters to their query. You can't write SQL literals for these because there's a combinatorial explosion of possibilities. So you wind up dynamically generating SQL yourself by concatenating strings in the WHERE clause, hoping and praying you don't accidentally allow SQL injection. I want an ORM to deal with that shit for me.