Resultset of stored procedure contains list of LinkedCaseInsensitiveMap

Question

I have this stored procedure call in Java:

@Autowired
public ScoreDao(DataSource dataSource) {
    setDataSource(dataSource);
    mScoreStoredProcedure = new ScoreStoredProcedure(dataSource);
}

public List<Score> loadAllScore(String pUsername, String pUUID, int pLimit) {
    return mScoreStoredProcedure.execute(pUsername, pUUID, pLimit);
}

private class ScoreStoredProcedure extends StoredProcedure {
    private static final String SPROC_NAME = "loadUserScore";

    public ScoreStoredProcedure(DataSource datasource) {
        super(datasource, SPROC_NAME);
        declareParameter(new SqlReturnResultSet("#result-set-2", mScoreMapper));
        declareParameter(new SqlParameter("vusername", Types.VARCHAR));
        declareParameter(new SqlParameter("vuuid", Types.VARCHAR));
        declareParameter(new SqlParameter("vlimit", Types.INTEGER));
        compile();
    }

    @SuppressWarnings("unchecked")
    public List<Score> execute(String pUsername, String pUUID, int pLimit){ 
        Map<String,Object> lAllScoreResult = super.execute(pUsername, pUUID, pLimit);
        List<Score> lAllScore = ((List<Score>)lAllScoreResult.get("#result-set-2"));
        return lAllScore; 
    }

}

and this mapper class:

public class ScoreMapper implements RowMapper<Score> {

private String suffix = "";

@Autowired
private ScoreCreator scoreCreator;

@Autowired
private QuestionMapper questionMapper;

public ScoreMapper(String pSuffix) {
    suffix = pSuffix;
}

public Score mapRow(ResultSet rs, int rowNum) throws SQLException {
    Score lScore = scoreCreator.createScore(rs.getLong(suffix+"id"),
            rs.getTimestamp(suffix+"stempel"), rs.getString(suffix+"username"),
            rs.getInt(suffix+"points"), rs.getInt(suffix+"level"),
            rs.getString(suffix+"comment"),
            questionMapper.mapRow(rs, rowNum), rs.getString(suffix+"uuid"));
    return lScore;
}
  }

The problem I have is that the result of my StoredProcedure is never cast to List<Score>.

Instead it contains a List of LinkedCaseInsensitiveMap<V>. Each entry covers a value from the database.

The mapper is correctly wired via Spring.

Briefly: I expect as result a List<Score>. I thought I handle this with the methods shown in the code. How can I cast the result directly to my List<Score>?

I followed this tutorial http://www.jtmelton.com/2007/10/30/using-springs-storedprocedure-and-rowmapper-mechanisms/

Could you help me find the problem?

It looks like you are never calling mapRow, you are just casting the map to a list. — aglassman, Oct 14 '13 at 20:19
Yes Iam wondering about that too but I followed this tutorial http://www.jtmelton.com/2007/10/30/using-springs-storedprocedure-and-rowmapper-mechanisms/ — nano_nano, Oct 14 '13 at 20:20
Ah ok. Well scratch what I said about calling mapRow, this should only be used by the spring api. — aglassman, Oct 14 '13 at 20:26
If you post the code where you call the execute, that may be helpful. The posted code seems to be correct according to the tutorial. — aglassman, Oct 14 '13 at 20:32
which execute do you mean? I posted the StoredProcedure execute already. — nano_nano, Oct 14 '13 at 20:35
The code you are creating an instance of ScoreStoredProcedure from, and then calling execute on it. — aglassman, Oct 14 '13 at 20:36
no. I just expect that spring does the work for me and casts the result to List — nano_nano, Oct 14 '13 at 20:44
How are you able to tell that the code is not working correctly? If it is able to cast to List I'd say it is working. — aglassman, Oct 14 '13 at 20:48
I would expect a list of score as result. But I get a list of LinkedCaseSensitiveMap. Thats a fact. If I try to cast one of these maps I got a ClassCastException. — nano_nano, Oct 14 '13 at 21:14
Btw. I never told you the code is Not working correctly. I just told it works not as I expect — nano_nano, Oct 14 '13 at 21:16
What is the content of the returned `LinkedCaseInsensitiveMap`s? — Timofey Gorshkov, Oct 21 '13 at 17:53
Btw. setting the resultset name and mapper is without any effect. Thats the reason why I use the defaultname of the resultset — nano_nano, Oct 24 '13 at 08:38

score 3 · Accepted Answer · edited May 23 '17 at 12:14

Can you rename the result set parameter to, say, "ScoreResultSet"?

So

declareParameter(new SqlReturnResultSet("ScoreResultSet", mScoreMapper));

and

List<Score> lAllScore = ((List<Score>)lAllScoreResult.get("ScoreResultSet"));

The name you are currently using also gets used by spring when it auto generates names for result sets.

I think you may therefore now be returning a different, unprocessed, result set than the mapped one from the SqlReturnResultSet parameter you declared.

======

EDIT:

I just found this other question of yours: Java StoredProcedure with SqlReturnResultSet not working and assuming that we are talking about the same stored procedure:

I suspect you have two selects in the stored procedure and it returns two result sets. You should declare two (properly named) SqlReturnResultSet parameters instead of one. The order of the declaration matters, so you cannot declare just one and give it the name usually assigned to the second one.

You was right. I have 3 selects inside my Sp. Setting the properties in the right ordering leads to the expected result. Thx a lot! — nano_nano, Oct 24 '13 at 08:49
Please help me with almost same problem http://stackoverflow.com/q/30378061/2323234 — MRK187, May 21 '15 at 16:41

score 0 · Answer 2 · answered Oct 14 '13 at 21:22

0

Try casting to LinkedList rather than List<Score>.

The tutorial shows as follows, and is the only thing I can think of that you have done differently from the tutorial:

List resultList = (LinkedList)results.get(Constants.RESULTSET);

    //iterate of results list and print
    for (Iterator it=resultList.iterator(); it.hasNext(); ) {
        User user1 = (User)it.next();
        System.out.println(user1);
    }

    return resultList;

answered Oct 14 '13 at 21:22

aglassman

2,643
1
17
30

3

casting to LinkedList can't help, as LinkedList has no additional methods the List interface doesn't have – Sean Patrick Floyd Oct 17 '13 at 10:55

Resultset of stored procedure contains list of LinkedCaseInsensitiveMap

2 Answers2