Select max rows dynamically?

[Björn Raupach]

Hi,

I am trying to limit the result set with a 'select top <value>' statement. We would like to inject the value for maximum rows dynamically in the mapper interface instead of using a hardcoded value direct in the sql statement.

public List<SomeObject> selectSomeObject(int somecriteria, int maxRows);

<select id="selectSomeObjects" resultMap="someObjectResultMap">
SELECT TOP ${1} ColumnA, ColumnB FROM Table WHERE ColumnB = #{0}
</select>

...doesn't work. The value '1' is set in the statement not the value of the parameter maxRows.

Has anyone figured out how to do that?

Thanks in advance!

Björn

[Jerzy Czopek]

W dniu 2010-07-23 13:17, Bj�rn Raupach pisze:

> Bj�rn

I assume that lack of parameterType attribute in above select tag is
just a shortcut, and in Your real mapper You didn't forget about it.
Try #{1} instead of ${1}

You could also modify Your mapper interface to look like this:

public List<SomeObject> selectSomeObject(@Param("someCriteria")int
somecriteria, @Param("maxRows")int maxRows);

and xml:

<select id="selectSomeObjects" resultMap="someObjectResultMap"

parameterType="map" >
SELECT TOP #{maxRows} ColumnA, ColumnB FROM Table WHERE ColumnB =
#{maxRows}
</select>

This way mapper are more readable.

Regards,
Jerzy

[Björn Raupach]

Hi Jerzy,

#{1} does not work because it is not a parameter for the prepared statement: Throws an sql exception. Thats why I tried string substitution.

Björn

On Jul 23, 2010, at 1:45 PM, Jerzy Czopek wrote:

> W dniu 2010-07-23 13:17, Björn Raupach pisze:

>> Hi,
>>
>> I am trying to limit the result set with a 'select top<value>' statement. We would like to inject the value for maximum rows dynamically in the mapper interface instead of using a hardcoded value direct in the sql statement.
>>
>> public List<SomeObject> selectSomeObject(int somecriteria, int maxRows);
>>
>> <select id="selectSomeObjects" resultMap="someObjectResultMap">
>> SELECT TOP ${1} ColumnA, ColumnB FROM Table WHERE ColumnB = #{0}
>> </select>
>>
>> ...doesn't work. The value '1' is set in the statement not the value of the parameter maxRows.
>>
>> Has anyone figured out how to do that?
>>
>> Thanks in advance!
>>

>> Björn

[raupach]

Found the solution. Actually google did.....

Starting with SQL Server 2005 you can do a SELECT TOP (<value>) . The
parentheses do the trick.

SELECT TOP (#{1}) ColumnA, ColumnB FROM Table WHERE ColumnB = #{0}

Björn

On Jul 23, 2:07 pm, "Björn Raupach" <raupach.bjo...@googlemail.com>
wrote: