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);