JDBI, retrieve data with sql query into customized object(constructor) instead of Map

Question

So when we use JDBI to query from database, it is getting it into a Map<String, Object> type.

I want to get it as my customized object (constructor) instead of Map<String, Object>.

DBI dbi = establishConnection(url, userName, passWord);
Handle handle = dbi.open();
List<Map<String, Object>> rs = handle.select("select * from sometable");

Instead I want to use:

List<customizedObject> rs = handle.select("select * from sometable");

Where customizedObject class is an object that contains all the column properties with it.

Is there any way to do this? I found some relative documentation, but I cannot really understand the implementation.

http://jdbi.org/sql_object_api_queries/

To display code properly either wrap short snippits with backticks (left of your 1 key) `like this` or begin the line with 4 (or more) spaces. — indivisible, Apr 14 '14 at 17:52
I'm not that familiar with JDBI, but the docs mention creating a class based on the [ResultSetMapper](http://jdbi.org/apidocs/org/skife/jdbi/v2/tweak/ResultSetMapper.html) interface and passing it using `handle.create("select * from sometable").map(MyMapper)` command. — Powerlord, Apr 14 '14 at 18:09
Having said that, I'm surprised you're not using something like a [JPA](http://docs.oracle.com/javaee/6/tutorial/doc/bnbpz.html) implementation such as [Hibernate ORM](http://hibernate.org/orm/) if you want to deal with objects. — Powerlord, Apr 14 '14 at 18:12
My reason to go with JDBI is that it accesses the database in a more direct way than JPA and still abstracts over JDBC's API. JPA involves more overhead and thus makes it difficult to reason about the interaction of an application and the database. If you want to access a single DBMS implementation efficiently JBDI is favorable, if targeting multiple DBMS products JPA is favorable. As so often there is no _always better_ but a _more suitable_ for the individual case. — Augustus Kling, Apr 14 '14 at 20:15

score 13 · Accepted Answer · edited Feb 13 '15 at 19:46

Please also see the previous page in the documentation that shows how to link your Handle or DBI with the mappers.

Essentially, you need a mapper to convert the ResultSet to the desired object and an interface to refer to the mapper.

Let's assume a minimal example. First the mapper needs to be provided:

public class CustomizedObjectMapper implements ResultSetMapper<customizedObject> {

    @Override
    public customizedObject map(int index, ResultSet r, StatementContext ctx)
            throws SQLException {
        return new customizedObject(r.getString("uuid"), r.getString("other_column"));
    }

}

Then we need an interface to define which query provides the data that is passed to the mapper class. One result row leads to one invocation of CustomizedObjectMapper.map(...):

@RegisterMapper(CustomizeObjectMapper.class)
public interface CustomizeObjectQuery {

    @SqlQuery("Select uuid, other_column from schema.relation")
    List<customizedObject> get();
}

Finally, the objects can be retrieved: List<customizedObject> test = dbi.open(CustomizeObjectQuery.class).get().

Your can also put the components together on an individual basis like so and omit the interface: dbi.open().createQuery("Select uuid, other_colum from schema.relation").map(new EventMapper()).list()

JDBI, retrieve data with sql query into customized object(constructor) instead of Map

1 Answers1