CODEWITHSUNDEEP
javamysql
nano_nano
nano_nano

Reputation: 12523

sql "between statement" not working with spring

I have this sql which works fine(the result is given) when I execute the statement via the sql manager console.

SELECT * FROM (
                SELECT s.*, @rank
                := @rank + 1 rank
                FROM
                quiz.score s, (SELECT @rank := 0) init
                ORDER BY points DESC
                ) s
                WHERE rank BETWEEN (select count(id) from score)-(10) AND (select count(id) from score)
                ORDER BY rank;

If I try to execute the script via spring the result is always empty:

public List<Score> loadAllScoreLast(String pFrom, String pTo) {
        return createJdbcTemplate().query(mLAUSFT, new Object[] { pFrom, pTo },
                mScoreMapper);
    }

pFrom is in this case (select count(id) from score)-(10) and pTo is (select count(id) from score)

I think I include pFrom and pTo the wrong way.

That is the original sql:

SELECT * FROM (
                SELECT s.*, @rank
                := @rank + 1 rank
                FROM
                quiz.score s, (SELECT @rank := 0) init
                ORDER BY points DESC
                ) s
                WHERE rank BETWEEN ? AND ?
                ORDER BY rank;

Could someone help me with that issue?

Thx in advance

Stefan

Upvotes: 0

Views: 97

Answers (1)

peter.petrov
peter.petrov

Reputation: 39457

Why are pFrom and pTo strings? I guess it is some problem with the types and/or with the placeholders because the rest looks valid.

http://sqlfiddle.com/#!2/45e1b/1

You should check what plain SQL is generated.

I guess it might look like this (note the quotes).

SELECT * FROM (
            SELECT s.*, @rank
            := @rank + 1 rank
            FROM
            quiz.score s, (SELECT @rank := 0) init
            ORDER BY points DESC
            ) s
            WHERE rank BETWEEN '(select count(id) from score)-(10)' AND '(select count(id) from score)'
            ORDER BY rank;

Upvotes: 3

Related Questions