Responding here in spite of the thread Jacques started on the dev list (keep it centralized). Still, there are some things related to this that should be discussed and the mailing lists is definitely the place for discussion. I'll keep my comments here to general feedback.
This is an interesting concept and various discussions related to it have gone on.
Just to set expectations this is the sort of thing that will likely require on the order of months to review and discuss and come up with good solutions and combining work done by others with this and so on.
I know for sure that Adam Heath has been working on something related to this, though different in that it uses generic SQL which is translated into the Entity Engine objects.
In general running SQL directly against the database is certainly allowed, but even if we did make it easier with the OFBiz framework we would not allow that to be used for code in the project.
This document complained about optimization. It is true that some optimization beyond doing a good job of limiting what you ask the database to do is possible, but that usually requires database-specific SQL that is not very portable to other databases. That would require specifying different SQL for each database we want to support. That is why we are very careful of the actual SQL that makes it into OFBiz.
For general optimization you can do a lot with what exists in OFBiz, and has always been adequate for projects I have worked on, even where performance was very sensitive. The most significant performance gains do come from understanding indexes and temporary tables (the ones the database creates when doing joins, etc) and so on, and the nice thing about the Entity Engine is that this knowledge of the database is easy to map into code changes, unlike more traditional Object-Relational mapping packages where the way operations map to the database is not so transparent.
Whatever the case, those are my initial thoughts. I look forward to seeing more discussion and work going on in this area to improve the framework and make it easier for people to do the things they need to, even the non-best-practice things.
After all the OFBiz Framework is NOT a 4GL tool, and while we have best practices it's not about forbidding things on a framework level. In the applications it's a bit of a different story as some control is required there to make it easy (or even feasible) to deploy OFBiz on different databases and other infrastructure pieces.