> Kind of like a DOM API, but for SQL.

In Java land, the big persistence API is JPA. It's an API for ORMs, but it has features which go a long way towards this - just from the ORM side rather than the SQL side.

Firstly, it has its own query language, JPQL, which is basically a large subset of SQL (on the order of MySQL's SQL rather than PostgreSQL's, eg no window functions) with some added niceties, such as for joining (eg "select u from User u where u.manager.location.country.code = 'UK'").

Secondly, it has something called the 'criteria API' [1] which is basically a DOM for JPQL. So not SQL, but close. I worked on a large, byzantine financial application where we did a lot of building up of queries using this API. It worked pretty well as a way of separating the logic for different aspects of queries: we had one bit of code that set up the core of a query to limit results according to the user's permissions, another bit that would apply temporal filters, another bit that would apply subject filters, an optional bit that would take a query for trades and turn it into a query to find stocks involved in those trades, etc.

The API itself is not massively typesafe (the programmer specifies an attribute with a string name and type, so they can get both the name and the type wrong if they like), and it is rather verbose (of course). There is an extension called the 'metamodel', where you can automatically generate classes which have ready-made bits and bobs which describe your schema, and saves both typing and errors. Using that, a query to find all trades on a particular stock could look like:

@PersistenceUnit EntityManager em; // someone will dependency inject this

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Trade> q = cb.createQuery(Trade.class);
Root<Trade> trade = q.from(Trade.class);
q.select(trade).where(cb.equal(trade.get(Trade_.stockTicker), "NXJ"));
TypedQuery<Trade> = em.createQuery(q);

Which is is still verbose and stilted in the grand traditions of High Java, of course.

There are some third-party libraries for building JPQL strings which can be used for much the same purposes. Liquidform is quite good:

Trade t = alias(Trade.class, "t");
SubQuery<Trade> q = select(t).from(Trade.class).as(t).where(eq(t.getStockTicker(), "NXJ"));
TypedQuery<Trade> = em.createQuery(q.toString(), Trade.class);

A key thing here is that the alias function takes a class and, via bytecode sorcery, produces an object which conforms to the type of that class, but which isn't really an instance of it, but instead has methods which act as query primitives. It works a lot like things like Mockito and so on.

The new hotness, though, is Jinq, which uses Java 8 lambdas to imitate the great .net LINQ. I haven't used it, but i believe the query would look something like:

List<Trade> = streams.streamAll(em, Trade.class).where(t -> t.getStockTicker().equals("NXJ")).toList();

Again, this uses bytecode sorcery under the hood, but this time, it does it by pulling apart the lambda to determine the condition it expresses, and then constructing a corresponding query string. A different knid of sorcery - in AD&D terms, divination rather than conjuration.

[1] http://docs.oracle.com/javaee/6/tutorial/doc/gjitv.html