Search code examples
javajdbi

Cannot get all rows when using ResultSetMapper to parse results

Im using Java 8 and Jdbi version 2.78 to query a database.

I have a table of key-value configurations for merchants.

enter image description here

I have a class which queries the database:

import com.clevergift.gson.MerchantConfigsBeanMapper;
import org.skife.jdbi.v2.sqlobject.Bind;
import org.skife.jdbi.v2.sqlobject.SqlQuery;
import org.skife.jdbi.v2.sqlobject.customizers.Mapper;
import java.util.Map;

public abstract class MerchantConfigDAO {

    @Mapper(MerchantConfigsBeanMapper.class)
    @SqlQuery("SELECT configKey, configValue FROM merchant_configs where merchant = :merchant")
    public abstract Map<String, String> getMerchantConfigs(@Bind("merchant") String merchant);

}

And the MerchantConfigsBeanMapper class should loop through the results:

import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class MerchantConfigsBeanMapper implements ResultSetMapper<Map<String, String>> {

    @Override
    public Map<String, String> map(int index, ResultSet r, StatementContext ctx) throws SQLException {

        Map<String, String> configs = new HashMap<String, String>();

        int rowcount = 0;
        if (r.last()) {
            rowcount = r.getRow();
            r.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
        }
        while (r.next()) {
            configs.put(r.getString("configKey"), r.getString("configValue"));
        }

        return configs;
    }
}

When I run this passing in "adidas", 3 results should obviously be returned. However, r always only contains 1 result and r.getString("configValue") is cbadmin. r.last() is always true as only 1 result is being returned.

How is this possible? Why doesn't r contain all the results?

EDIT

I called the MerchantConfigDAO from the service:

import com.clevergift.services.MerchantConfigService;
import com.google.inject.Inject;
import java.util.Map;

public class MerchantConfigServiceImpl implements MerchantConfigService {
    private MerchantConfigDAO merchantConfigDAO;

    @Inject
    public MerchantConfigServiceImpl(MerchantConfigDAO merchantConfigDAO) {
        this.merchantConfigDAO = merchantConfigDAO;
    }

    public Map<String, String> getMerchantConfigs(String merchant) {
        Map<String, String> merchantConfigs = this.merchantConfigDAO.getMerchantConfigs(merchant);
        return merchantConfigs;
    }
}

Even after implementing @ivans answer here, merchantConfigs is still of length 1...

Solution

  • Mapper should map one row at a time with the following code. From documentation:

    Row mappers are invoked once for each row in the result set.

    @Override
public Map<String, String> map(int index, ResultSet r, StatementContext ctx) throws SQLException {
    Map<String, String> configs = new HashMap<String, String>();
    configs.put(r.getString("configKey"), r.getString("configValue"));
    return configs;
}

    If you still want to process whole ResultSet in one call you nned to return List<Map<String, String>>. One map for each row in ResultSet.

    According to documentation

    Query methods may return a single- or multi-row result, depending on whether the method return type looks like a collection.

    Change your DAO method to return List<Map<String, String>>