How to return a java.util.Map from a SQL Object query method?

[Steve Kingsland]

My SQL query takes a list of email addresses, and returns the account number associated with each one. I'm trying to write a SQL Object method which returns a Map<String, String> with the email -> account number mappings, like so:

@UseStringTemplate3StatementLocator

@SqlQuery("select email, account_number from customer where email in (<emails>)")

public Map<String, String> getAccountNumbers(@BindIn("emails") Collection<String> emails);

However, I can't figure out how to use @RegisterContainerMapper (and possibly @RegisterMapper) to make this work. The error I'm getting is:

org.skife.jdbi.v2.MappingRegistry$1: No mapper registered for java.util.Map

at org.skife.jdbi.v2.MappingRegistry.mapperFor(MappingRegistry.java:83)

at org.skife.jdbi.v2.RegisteredMapper.map(RegisteredMapper.java:35)

at org.skife.jdbi.v2.Query$4.munge(Query.java:183)

at org.skife.jdbi.v2.QueryResultSetMunger.munge(QueryResultSetMunger.java:41)

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1343)

at org.skife.jdbi.v2.Query.fold(Query.java:173)

at org.skife.jdbi.v2.Query.first(Query.java:273)

at org.skife.jdbi.v2.Query.first(Query.java:264)

at org.skife.jdbi.v2.sqlobject.ResultReturnThing$SingleValueResultReturnThing.result(ResultReturnThing.java:110)

at org.skife.jdbi.v2.sqlobject.ResultReturnThing.map(ResultReturnThing.java:46)

at org.skife.jdbi.v2.sqlobject.QueryHandler.invoke(QueryHandler.java:43)

at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:212)

at org.skife.jdbi.v2.sqlobject.SqlObject$2.intercept(SqlObject.java:109)

at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$8a8bd544.getCustomerUtilityIds(<generated>)

Is there a way to do this?

The closest things I found were:

1) Denis's WithoutNullsListContainerFactory, which is the only example I could find of a SQL Object query using both annotations (@RegisterContainerMapper and @RegisterMapper):

https://groups.google.com/d/msg/jdbi/--tU9s2ujmg/LRnRS79kk1EJ

2) TestContainerFactory using a non-iterable container type (called "Maybe"), albeit still with a single value type, instead of two or more like a Map:

https://github.com/jdbi/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestContainerFactory.java#L233

Thanks in advance! Folks at my company use JDBI a ton. :)

-Steve

steve.k...@opower.com

[Michael Joyner]

Extend a Map class:

public class MyCustomMap extends HashMap<String, String> {}

Then use this class with a Mapper and as the return value.

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Steve Kingsland]

Thanks Michael! I have a couple of questions about what you wrote:

1) Should I use a regular Mapper or a ContainerMapper (or both) for MyCustomMap?

2) How is creating my own MyCustomMap class any different from having the SQL Object method return a Map or HashMap? What's special about Map/HashMap that JDBI won't work with it?

The part that I'm hung up on is that I think I need the ResultSetMapper to get the value for each column from the ResultSet, but I also need the ContainerBuilder to put the entries in my Map, because ContainerBuilder#add takes an Object which has already been extracted from the ResultSet.

Steve Kingsland

Senior Software Engineer

Opower

We’re hiring! See jobs here

--
You received this message because you are subscribed to a topic in the Google Groups "jDBI" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jdbi/dzYOL0iT8lo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jdbi+uns...@googlegroups.com.

[Michael Joyner]

Hrmmm I see an issue with my code, it won't work, ignore it. (Not enough coffee yet this morning!).

My brain was thinking "Map#putAll" but the only way I see doing this with the regular resultsetmapper and that custom class is something like:

Map<String,String> finalMap = ...;
dao.getListOfMaps().forEach(map->finalMap.putAll(map));

I've never used CotainerBuilder, perhaps you can get it to do something similar if you map each row as a Map that is then "putAll" into your container?

The biggest thing about extending an xxxMap as a new top level class is that any Mappings/Bindings are now explicit to it and removes ambiguity for compile time/runtime mapping.

[Steve Kingsland]

My workaround is to just return a list of (email, accountNumber) pairs, and have the calling method create a map and put each entry into it. One benefit of that approach is that I can handle the situation where a single email address relates to more than one account number, which is at least theoretically possible in my data model.

My use case doesn't seem that unusual though, so I'd be interested to know if there is a simple, clean solution to extract value pairs from a resultset and return them in a map.

Steve Kingsland

Senior Software Engineer

Opower

We’re hiring! See jobs here

[Matthew Hall]

Try using the @Mapper(YourMapperClass.class) annotation on the method.

[Steve Kingsland]

What benefit does @Mapper bring? It takes the same ResultSetMapper class that the @RegisterMapper annotation does. And what type of Object should the ResultSetMapper class return, Map.Entry?

The problem appears to be that java.util.Map isn't a supported type of collection for SQL Object methods, because it requires a (key, value) tuple for each entry, and I don't see a way for a ResultSetMapper to return that tuple.

Has anyone done this before?

Steve Kingsland

Senior Software Engineer

Opower

We’re hiring! See jobs here

[Steven Schlansker]

Yeah, if you use a Mapper<Map<...>>, it will expect every row to return a Map<...>
I believe you are the first person to explore this feature -- it's certainly something
we'd like to support. I hacked around for twenty minutes and got something very close
to working:

https://github.com/stevenschlansker/jdbi/commit/9d4e1a4a39e824bff1fc90b125c73168e4fb6006

It needs a bit of cleanup to be a proper patch, and the implementation of @Collector
or some other strategy is a TODO, but hopefully this inspires you?

If you like this approach, maybe spend some time polishing it up into a PR ;)

[Steve Kingsland]

Thanks Steven, I'm pretty surprised no one has tried to return a java.util.Map of values from a SQL query before, since it seems like a reasonably normal use case! :-)

Because I couldn't find an acceptable solution to this problem, I went with a workaround using a tuple class and a protected method in an abstract class to execute the query: https://gist.github.com/skingsland/da895e638119ece7a3e0. I'd love to hear thoughts on that approach from you or anyone else on the list!

I don't have enough context about how JDBI's Query class is designed, to take your gist and turn it into a better solution right now. I'd potentially be willing to pick this up in the near future, though, but first I want to make sure that this feature provides enough benefit so that others will use it. I am loathe to add a new feature to a commonly-used framework unless it's broadly useful; the Guava maintainers at Google are super strict about that, I have read.

Steve Kingsland

Senior Software Engineer

Opower

We’re hiring! See jobs here

[Serge M]

I was able to get this working by having custom Collection class that uses HashMap internally. It seems that RegisterContainerMapper will not work with any class that does not extend Collection.

public class MyMappedCollection implements Collection<Entry<String, Object>> {

    private HashMap<String, Object> map = new HashMap<String, Object>();

  // Collection implementation goes here. All methods call map field for data.

[Matthew Hall]

Hi Serge,

This is addressed on the v3 branch, but I'm not sure we'll have the bandwidth on the team to address it in v2.

However if you can spare the time to put together a PR to fix it, I'd be happy to walk you through the current design problem and how we might fix it.

-Matthew

--

You received this message because you are subscribed to the Google Groups "jDBI" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.