Map SQL (not JPQL) to a collection of simple Java objects?

I can't believe I'm asking this, but...

Is there any way, in Java, to execute a SQL statement (not JPQL) and map the results to a List of Plain Old Java Objects?

I want to be able to create small lightweight POJO objects and then have them populated by raw SQL queries. I'm expressly NOT looking to create complex objects: just primitives, with no relationships.

Everything seems to be centered around JPA/J开发者_如何学JAVAPQL, but the problem with that is that I do not want to bind my objects to a specific table.

I feel like I'm either:

• (a) on crazy pills, or
• (b) missing something fundamental

---

A lightweight mapper is not available as part of the JDK itself. You could either roll-your-own simple mapper using Java's standard JDBC API (in fact JPA implementations build on top of that) or you could have a look at external libraries that provide simple SQL-to-Object mappers. I know MyBatis (formerly known as iBatis).

A) No, I think you're not on crazy pills and B) is it possible that you just missed JDBC?

---

Sormula may be able to do what you want. You would need to extend Table and override getTableName() and/or getQualifiedTableName() to supply the desired table name since sormula normally associates one POJO to one table. See example 2a and example 3a.

---

jOOQ has a couple of Record -> POJO mapping capabilities that will probably do the job for you (although jOOQ can do much more). Here's an example:

// A "mutable" POJO class
public class MyBook1 {
  public int id;
  public String title;
}

// The various "into()" methods allow for fetching records into your POJOs:
List<MyBook1> myBooks = create.select().from(BOOK).fetchInto(MyBook1.class);

Taken from the manual here: http://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos/

The mapping algorithm is described in the Javadoc: http://www.jooq.org/javadoc/latest/org/jooq/impl/DefaultRecordMapper.html

While the above example makes use of jOOQ's DSL API, you can do with plain SQL as well:

List<MyBook1> myBooks = create.resultQuery("SELECT * FROM BOOK")
                              .fetchInto(MyBook1.class);

You can even operate on a JDBC ResultSet, using jOOQ only for mapping:

ResultSet rs = stmt.executeQuery();
List<MyBook1> myBooks = create.fetch(rs).into(MyBook1.class);