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

ringord picture ringord · Apr 14, 2014 · Viewed 10.6k times · Source

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/

Answer

Augustus Kling picture Augustus Kling · Apr 14, 2014

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