Can't use Map<String, Object> inside a 'SQL Object Query'

[matty]

When returning type Map<String, Object> or List<Map<String, Object>> causes a failure saying there is no result set mapper for type 'java.util.Map'. I know 'SQL Object Query' (http://jdbi.org/sql_object_api_queries/) is for returning objects, but it would be nice to be able to return this type for when you don't want to specify and object and a result set mapper which is useful in some scenarios. (e.g when doing a complex join query).

I got around the issue by providing a mapper for the type 'java.util.Map' using the code below.

@Override
    public Map<String, Object> map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        Map<String, Object> rs = new HashMap<>();

        ResultSetMetaData metaData = r.getMetaData();

        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            rs.put(metaData.getColumnName(i), r.getObject(i));
        }

        return rs;
    }

But it would be nice if this was included as standard.

ps. If anyone knows of a proper way of doing SQL joins easily in JDBI without having to specify multiple complex objects for each join (or an object for each data set you want) then let me know.. AFAIK transforming the ResultSet into a Map<String, Object> is the easiest way.

[qualidafial]

So if you're using the fluent API (i.e. using the Handle to create queries
and bind arguments imperatively), the default mapper maps to
Map<String,Object> as in your example.

We seem to have overlooked this case for SQL objects. I'll submit an issue
for this.

An easy workaround is to implement your SQL method as a concrete method:

public List<Map<String,Object>> doQuery(String arg0, int arg1) {
return getHandle().createQuery("select * from foo where arg0 = :arg0
and arg1 = :arg1")
.bind("arg0", arg0)
.bind("arg1", arg1)
.list();
}

On Wed, Aug 31, 2016 at 8:23 AM, Matty Southall notifications@github.com
wrote:

When returning type Map or List> causes a failure saying there is no
result set mapper for type 'java.util.Map'. I know 'SQL Object Query' (
http://jdbi.org/sql_object_api_queries/) is for returning objects, but it
would be nice to be able to return this type for when you don't want to
specify and object and a result set mapper which is useful in some
scenarios. (e.g when doing a complex join query).

I got around the issue by providing a mapper for the type 'java.util.Map'
using the code below.

`@Override https://github.com/Override
public Map map(int index, ResultSet r, StatementContext ctx) throws
SQLException {
Map rs = new HashMap<>();

ResultSetMetaData metaData = r.getMetaData();

for (int i = 1; i <= metaData.getColumnCount(); i++) {
    rs.put(metaData.getColumnName(i), r.getObject(i));
}

return rs;

}`

But it would be nice if this was included as standard.

ps. If anyone knows of a proper way of doing SQL joins easily in JDBI
without having to specify multiple complex objects for each join (or an
object for each data set you want) then let me know.. AFAIK transforming
the ResultSet into a Map is the easiest way.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#466, or mute the thread
https://github.com/notifications/unsubscribe-auth/AACW5SBrje8A9H9bGFeZJxV4P3lyqK-qks5qlY5zgaJpZM4Jxq5o
.

[stevenschlansker]

We should also add some code examples of various methods for joining.

[matty]

@stevenschlansker Some samples would be great for joining. How to handle one to many or one to one effectively?

[qualidafial]

My go-to tool for one-to-n joins is to use Query.fold:

getHandle().createQuery(
        "select foo_id, foo_name, bar_id, bar_name " +
        "from foo left join bar on foo.foo_id = bar.foo_id")
    .fold(new LinkedHashMap<String,Foo>(), (acc, rs, ctx) -> {
        String fooId = rs.getString("foo_id");
        Foo foo = acc.get(fooId);
        if (foo == null) {
            foo = new Foo(fooId, rs.getString("foo_name"));
            acc.put(fooId, foo);
        }

        String barId = rs.getString("bar_id");
        if (barId != null) {
            foo.addBar(new Bar(barId, rs.getString("bar_name"));
        }

        return acc;
    })
    .values();

[matty]

@qualidafial It would be interesting to make use of java.util.Optional (https://docs.oracle.com/javase/8/docs/api/java/util/Optional.html) for one-to-n joins. If my entity is setup as:

class Foo {
    private int foo;

    private List<Bar> bar;

    ...
}

'bar' may always not be required, wrapping it into an Optional<List<Bar>> may be better? Also how about one-to-one & optional?

[qualidafial]

That's certainly up to you. java.util.Optional is only well-supported in v3 (alpha) however.

[qualidafial]

So looking at the v2 code, supporting Map<String,Object> (or List<Map<String,Object>>) will take quite a bit of refactoring on the ResultReturnThing (yes, that's the real class name--it's internal). Considering there is a workaround by making a concrete method and dropping down to the fluent API, I'm not sure whether I want to spend time on this for v2.

Another workaround in v2: you could also call dbi.registerMapper(new DefaultMapper()), however because v2 has limited awareness of generics, this will cause DefaultMapper to be used for all Map.class return types, not just Map<String,Object>. If you don't have other Map mappers, this probably won't be a problem for you.

In v3, you can simply call dbi.registerRowMapper(new DefaultMapper()) which should correctly match the full Map<String,Object> type signature and ignore other signatures. We could also add it to the list of mappers registered by default, so you don't have to do anything.

[matty]

@qualidafial Wouldn't it be a better idea to have a custom ResultSet class instead of handling Map<String, Object>? The annoying part of using Map<String, Object> is having to cast to the type. Something where you could do ".getInt(col)" similar to how ResultSet works (but a ResultSet has to be closed)?

[qualidafial]

@matty What you're describing is pretty much the point of ResultSetMapper.

[stevenschlansker]

It looks like we support this via registering DefaultMapper in jdbi3, and also now provide easy joining via JoinRowMapper and RowView which make retrieving results much nicer. I think there's nothing left in this issue then -- we're not going to backport to v2, sorry.

Let us know if this doesn't solve your problem!