Strategy to map SQL results to java objects

I am using JDBC (and spring-jdbc's jdbcTemplate) to access my database in a java web application. I have many different varying queries, some where I join to the table over there, one with the subquery that, one using a group by here etc. etc.

Oftentimes I need the result just to display a particular table that is generated by JSP, so I can just use the convenient queryForList method that returns a List<Map<String, Object>>, a List with each row represented by a map that maps column names to values. In JSP this is just fine, there is no compile time type check anyway, no code completion for properties by eclipse etc.

But sometimes I have java code to process the query result and I think it would be helpfu开发者_JAVA百科l to not work with maps, but with real objects, mainly for having a compile time check whether to properties really exist, have the correct type and of course to have code completion.

But if I want that I need to write an Object for every single query which may be many objects (pages of code with nothing but setters and getters).

What would be the best way to deal with that situation? Just write those darn objects? Or is there a better way?

---

...it would be helpful to not work with maps, but with real objects, mainly for having a compile time check whether to properties really exist, have the correct type and of course to have code completion.

You want the compiler to understand your object model, so (in Java, at least) you will need to create it, I'm afraid.

A decent IDE will provide options to generate get/set methods for you from members variables, which can be a timesaver. If the object model exists purely to apply some semantics over the JDBC queries and not business logic, perhaps public members are appropriate, avoiding the need for the getters and setters in the style of the J2EE Transfer Object pattern. Just watch out for business logic creeping in there, and don't forget about equals() and hashCode().

There is some help available for doing the mapping to and from your objects though, in Spring JDBC, like RowMapper and MappingSqlQuery. You may also want to check out Object Relational Mapping frameworks like Spring ORM to save yourself some effort. I think these approaches save time in writing translation code to and from SQL, managing transactions and the database schema - we still need to create the object model.

---

As Brabster mentioned, RowMapper and MappingSQLQuery are there to help you in converting your sql resultsets into objects. But I understand that your concern is that you don't always extract whole objects from queries and sometimes you extract a combination of objects (by doing sql joins).

So if I understand correctly, you are basically wondering whether you'll have to create classes for each of these combination of object too?

Simple answer is no, you don't need to create custom classes for each type of join query, but in absence of a proper ORM, you'll have to provide RowMappers for each type of join (note that M in ORM stands for mapping). As the spring's JDBC framework is not an ORM, it asks you to provide the mapping logic for each query. If this sound too much work, welcome to the ORM world :)