I prefer what I tend to call "micro ORMs," for lack of a more formal classification. From my point of view, a micro ORM does not attempt to fully replicate the entire breadth of a data model in code--that is, it does not automatically traverse relationships; does not enforce the constraints of the data model; does not require that your object model align with your data model.
Often, a micro ORM will allow you to quickly construct a list of objects from the resultset of an arbitrary SQL query and provide some programmatic way to handle relationships.
I don't think the performance penalty of high-performance micro-ORMs is great enough to justify avoiding their use on performance grounds [1] (see ORM column at right of charts). In the linked test, several micro-ORMs fair quite well, and incidentally, Wicket is using Hibernate, which I consider a full ORM. Not my particular cup of tea, but just goes to show ORMs are not necessarily a huge performance penalty.
Edit: Using our test implementation [2] as an example of what I mean, our in-house micro ORM will fetch World objects from a World table as so:
@Entity
public class World
{
private int id;
private int randomNumber;
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public int getRandomNumber() { return randomNumber; }
public void setRandomNumber(int randomNumber) { this.randomNumber = randomNumber; }
}
public class HelloHandler
// ...
{
@PathSegment
public void multipleQueries()
{
// ...
for (int i = 0; i < queries; i++)
{
worlds[i] = store.get(World.class, random.nextInt(DB_ROWS) + 1);
}
// ...
}
}
I've seen similar approaches in other micro ORMs. "Put" and other operations are generally provided, but composing joins using nothing by Java code? That's what a full ORM does.
To answer your questions:
The chief advice I would provide for writing clean database access code without an ORM is to use prepared statements as much as possible. Doing so allows you to avoid the potentially disastrous outcome of not correctly escaping user-provided data.
1. I wouldn't agree with the premise of the question. I would use an ORM for simple CRUD and then use an ORM's "build objects from a resultset" feature for complex SELECTs.
2. I think you do want at least a micro ORM to deal with this scenario. To be clear, a micro ORM would typically automate the work of constructing objects from a resultset using reflection (or similar) but probably doesn't come with much baggage (no external configuration files and no attempt to replicate the full complexity of the data model).
3. In my experience, cursors and read-write resultsets are not as high-performance as one might expect. If you're considering using those features in order to gain performance, I'd encourage you to do some simple tests to confirm they yield the performance benefit you are being promised.
[1] http://www.techempower.com/benchmarks/#section=data-r6&hw=i7...
[2] https://github.com/TechEmpower/FrameworkBenchmarks/blob/mast...