ResultMapper for returning multiple rows

[Anurag Laddha]

Greetings,

I'm using jdbi for interfacing with mysql using Scala.

What is working: converting mysql results with single row in it to corresponding object using ResultMapper

What is not working: converting mysql results with multiple rows in it to corresponding object using ResultMapper

Issues encountered:

• (PersonMapper) Mapper used to convert queries with single row in result to Scala object cannot be used with the queries that return multiple rows (because of return type of functions)
• (PersonMapperList) Mapper to convert queries with multiple rows in result always gets only one row in the result set. Hence only one model is returned by mapper

model:

class Person(name:String,  age: Int,  color:String) {

    override def toString = name + "," + age.toString + ", " + color

  }

Result mapper

class PersonMapper extends ResultSetMapper[Person]

  {

     def map(index : Int, r:ResultSet, ctx:StatementContext) : Person = {      

          new Person(r.getString("Name"), r.getInt("Age"), r.getString("Favorite Color"))     

    }

 }

  trait QuerySample{

    @SqlQuery("select * from anuragTest.trivia where name = :name")

    @Mapper(classOf[PersonMapper])

    def findByName(@Bind("name") name:String) : Person

    @SqlQuery("select * from anuragTest.trivia")

    @Mapper(classOf[PersonMapper])

    def getAllRecords() : List[Person]

  }

def main(args : Array[String]){

    val dbi : DBI = new DBI("jdbc:mysql://localhost:3306", "root","somepassword")

    val h = dbi.open()

   val qs:QuerySample = h.attach(classOf[QuerySample])

   println(qs.findByName("Bruce Callow"))  //this query runs fine

   val allPeople = qs.getAllRecords()

}

I get build error if i use the same PersonMapper to map one row result and multiple row result to Person object

com.experiments.TestMysqlConnection$Person cannot be cast to scala.collection.immutable.List

If i add another mapper to multiple results to list of person object and use @Mapper(classOf[PersonMapperList]) for getAllRecords

Result mapper for processing multiple result rows in query (ideally this shouldnt have been required)

class PersonMapperList extends ResultSetMapper[List[Person]]

  {

     def map(index : Int, r:ResultSet,ctx:StatementContext) : List[Person] = {

       val buf = scala.collection.mutable.ListBuffer.empty[Person]

       while(r.next()){

          buf += new Person(r.getString("Name"), r.getInt("Age"), r.getString("Favorite Color"))

       }

       buf.toList

    }

  }

By using PersonMapperList, the ResultSet of map of PersonMapperList contains always only one object (its resultSet.next() is always false) - the query returns more than one record when run on terminal or through select query : val rs = h.select("select * from anuragTest.trivia"). So there is no issue with the query 

There is no way I'm being to convert a query with multiple result rows to corresponding People object.

What wrong am i doing here? How do i proceed

Thanks!

Anurag

[Brian McCallister]

I haven't used JDBI from Scala myself, but the first form looks
correct on the surface. Chris (CC'ed) do you see any obvious problems
or have advice?

-Brian

> --
> 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.

[Christopher Currie]

I suspect that in the short term you're going need to use `java.util.List` as your return type. You can make this a private function and have a public forwarding function if you don't want to force your users to use the Java API.

Brian, I don't know if there is any way to use alternate collection types for list responses. I looked at https://github.com/gilt/jdbi-scala, but it doesn't appear to have support for this. Is this a v3 feature?

[Denis Kudriashov]

To return custom container object you should implement CustomContainerFactory and register it for your method for some annotation (I can be wrong about actual class names).

I use it to get list of entities which skipped some redundant rows from results of stored procedure (for which I don't have modify access )

[Anurag Laddha]

Greetings,

Christopher's suggestion worked that I may have to return java List as return type for things to work

@SqlQuery("select * from anuragTest.trivia")

 @Mapper(classOf[PersonMapper])

 def getAllRecords() : java.util.List[Person]   // instead of  def getAllRecords() : scala.collection.immutable.List[Person] 

This atleast got me started but i dont like the fact that i need to convert this explicitly to scala collection

Denis, could you please point me to an example of CustomContainerFactory where I can specify a non java collection as return type?

Thanks!

Anurag

[Denis Kudriashov]

In my case I implement mapper for results of stored procedure which returns nulls for rows which I not needed. And to get resulted list without nulls I add custom container like that:

@RegisterContainerMapper(WithoutNullsListContainerFactory.class)

@RegisterMapper(YourObjectMapper.class)

List<YoutObject> yourSqlQueryMethod();

And its implementation pretty simple:

public class WithoutNullsListContainerFactory implements ContainerFactory<List> {

@Override

public boolean accepts(Class<?> type) {

return List.class.isAssignableFrom(type);

}

@Override

public ContainerBuilder<List> newContainerBuilderFor(Class<?> type) {

return new ContainerBuilder<List>() {

private final List result = new ArrayList();

@Override

public ContainerBuilder<List> add(Object it) {

if (it == null)

return this;

result.add(it);

return this;

}

@Override

public List build() {

return result;

}

};

}

}

[Christopher Currie]

I would also look at jdbi-scala and its implementation of OptionContainerFactory, which looks like it does similar things. Also note that you can register a container factory on the DBI instance, so you don't need an annotation on your SQLObject classes.