I'm late to this party, but a friend of mine is working on a neat project:https://code.google.com/p/fado/
I takes SQL you hand write and it generates Java classes. You then use the Java classes in your code, so you have a nice, type safe API for your queries. It parses the SQL and knows about the database schema, so it can be smart. I haven't seen this approach done before. Here's a little example:https://code.google.com/p/fado/source/browse/trunk/source/play/PlayAllergies2.java
I've prodded him to put some examples on the front page.
Going the other way, trying to emit SQL with a Java API, doesn't seem to work too well. Hiding the SQL entirely is fine for simple cases but eventually you tend to need to go under the hood, and thar be dragons. Simple cases are easy to write the SQL for, and you write the best SQL for complex cases anyway. So write your SQL and use Fado to keep your Java source from getting nasty. That is the idea, I believe.