MyBatis Mapping multiple columns to an array

Question

I'm working with a (legacy) java app and DB and I need to map with MyBatis multiple columns to a single Array property

My query is something like

select price1, price2, price3, qty1, qty2,qty3 ... from table where ....

and the corresponding pojo is

public class Price{
    double[] prices = new double[3]
    int[] quantity = new int[3]
    public double[] getPrices(){}
    public void setPrices(double[] prices){...}
    public int[] getQty(){...}
    public void setQty(int[] quantities){...}
    ....
}

Unfortunatly I can't modify neither the query nor the java object

Any tip?

Solution 1

also you can implement the BaseTypeHandler interface and in getNullableResult method the ResultSet variable is available for you. So you can easily get all columns you need and put them to the Array.

Solution 2

You say you can't modify the query - if that means you can't change the SQL but you can change your MyBatis mapping, then I recommend using the MyBatis ObjectFactory. You define a subclass of DefaultObjectFactory and override the create method. The ObjectFactory receives arguments that you have specified as "constructor args" in your MyBatis ResultMap.

Your MyBatis mapping would now specify that the price fields are constructor arguments, even though they are not really. It's just a convenient way to pass the raw data to your own handler/factory.

<resultMap id="priceResultMap" type="Price">
  <constructor>
    <arg column="price1" javaType="double"/>
    <arg column="price2" javaType="double"/>
    <arg column="price3" javaType="double"/>
    <arg column="qty1" javaType="int"/>
    <arg column="qty2" javaType="int"/>
    <arg column="qty3" javaType="int"/>
  </constructor>
</resultMap>

<select id="getPrice" resultMap="priceResultMap">
  SELECT price1, price2, price3, qty1, qt2, qty3 ...
  FROM table 
  WHERE ...
</select>

You override MyBatis' default ObjectFactory, by putting this in your mybatis config.xml:

<objectFactory type="net.foo.bar.PriceObjectFactory"/>

And then PriceObjectFactory will look something like this:

import org.apache.ibatis.reflection.factory.DefaultObjectFactory;

public class PriceObjectFactory extends DefaultObjectFactory {

  private static final long serialVersionUID = 3627013739044L;

  @Override
  public <T> T create(final Class<T> type, final List<Class<?>> ctorArgTypes,
                      final List<Object> ctorArgs) {
    if (type.equals(Price.class)) {
      return this.<T>createPrice(ctorArgs);
    } else {
      // let MyBatis handle the other object types as it normally does
      return super.<T>create(type, ctorArgTypes, ctorArgs);
    }
  }

  private <T> T createPrice(final List<Object> ctorArgs) {
    final int expSize = 6;
    if (ctorArgs.size() != expSize) {
      throw new IllegalArgumentException("Expected " + expSize +
                                         " ctor args for Price class");
    }

    // construct with no arg ctor
    final Price p = new Price();
    double[] prices = new double[]{ ((Number)ctorArgs.get(0)).doubleValue(), 
                                    ((Number)ctorArgs.get(1)).doubleValue(),
                                    ((Number)ctorArgs.get(2)).doubleValue()};
    int[] qty = new int[]{ ((Number)ctorArgs.get(3)).intValue(),
                           ((Number)ctorArgs.get(4)).intValue(),
                           ((Number)ctorArgs.get(5)).intValue() };
    p.setPrices(prices);
    p.setQty(qty);

    @SuppressWarnings("unchecked")
    final T t = (T) p;
    return t;
  }
}

If you also want/need to have additional arguments to the Price constructor (like an id), then specify those in the <constructor> section of the mapping and it will also be passed into your PriceObjectFactory.

Solution 3

Another option is to use create a ResultHandler, which is a MyBatis interface that you can pass to the SqlSession#select method to handle the data coming back from your query.

Here's how you could use it to solve your issue.

First define a PriceResultHandler, something like this:

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;

public class PriceResultHandler implements ResultHandler {

  // keep a list, assuming you have multiple Price objects to handle
  List<Price> lp = new ArrayList<Price>();

  @Override
  public void handleResult(ResultContext rc) {
    // the result object is map with column names mapped to values like so:
    // {price1=11.32, qty1=15500, price2=2.62, qty2=234, etc.}

    HashMap<?,?> m = (HashMap<?,?>) rc.getResultObject();
    Price p = new Price();
    double[] prices = new double[]{ ((Number)m.get("price1")).doubleValue(),
                                    ((Number)m.get("price2")).doubleValue(),
                                    ((Number)m.get("price3")).doubleValue() };
    int[] qty = new int[]{ ((Number)m.get("qty1")).intValue(),
                           ((Number)m.get("qty2")).intValue(),
                           ((Number)m.get("qty3")).intValue() };
    p.setPrices(prices);
    p.setQty(qty);
    lp.add(p);
  }


  public List<Price> getPrices() {
    return lp;
  }

The reason the ResultHandler receives a HashMap is because you would set your mapping like this:

<select id="getPrices" resultType="hashmap">
  SELECT price1, price2, price3, qty1, qty2, qty3 
  FROM table 
  WHERE ...
</select>

Then you call it in your Java code like so:

PriceResultHandler rh = new PriceResultHandler();
session.select("getPrices", rh);
// or
session.select("getPrices", arg, rh);
// where arg is a value to pass to the getPrices query mapping

Price p = rh.getPrices().get(0);

This model is roughly the same amount of work as the other option I gave in the other answer. MyBatis is simply mapping the JDBC ResultSet into a Map for you and then you make domain objects with it as you see fit.

One caveat to this method is you have to deal with the case-sensitivity issue of the column names in the maps. You cannot control this via MyBatis. It depends on the behavior of the underlying JDBC driver: https://stackoverflow.com/a/11732674/871012

Comments

[pbanfi]

thenk you for the answer. I think that I simplyfied my example too much. I have several "groups" of colums/arrays. In this case I think that mapping all those fields as constructor args could be a little too verbose.

[pbanfi]

I've clarified my question :-)

[quux00]

I worked on other options and couldn't find an easy way. This is not a problem in MyBatis' sweet spot, as far I can know how to use it. I provide another option below, but overall I prefer the answer here, since you can use a Mapper class, not just xml. And you just keep adding whatever you need to the <constructor> section of your XML. It is "verbose" as you have to handle it all directly, pretty much as if you were doing pure JDBC, but you can encapsulate it all in one place (the factory or ResultHandler below) and the rest of the code doesn't have to deal with it.