Passing multiple arguments into a SELECT without using a complex object

Question

I am trying to pass in startSequenceId, stopSequenceId, orderNumber into the SQL map, however, i don't wish to use a typed object, i.e. parameterType="com.abc.Order", can i do so?

<select id="getSequenceIdByOrderNumber" parameterType="?" resultType="int">
    select *
    from log
    where seq_id
    between #{startSequenceId} and #{stopSequenceId}
    and order_no = #{orderNumber}
    and rownum = 1
</select>

Solution 1

@Chin I'll post what I had typed anyway with a simple example though you found what your looking for. My example using iBatis 2.3.4

<select id="retrieveTestXXX" parameterClass="java.util.Map" resultClass="java.lang.Integer">
    SELECT
    example_table.id
    FROM example_table
    WHERE example_table.xx_id = #testId# AND example_table.xx_id = #test2Id#
</select>

Hope this helps.

Solution 2

You can use the built in parameterType 'map' eg

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("name", "abc");
parms.put("phone", "123");
parms.put("email", "[email protected]");

List<Contact> list = myBatis.selectList("getContacts",parms);


<!-- in xml mapper -->
<select id="getContacts" parameterType="map" resultMap="Contact">
  SELECT * FROM CONTACT 
  WHERE CONTACT_NAME = ${name}
  AND CONTACT_PHONE = ${phone}
  AND CONTACT_MAIl = ${email}
</select>

Solution 3

In MyBatis 3, you can use @Param annotation in your mapper class(interface) method:

public getSequenceIdByOrderNumber(@Param("seqId") int sequenceId,@Param("orderId") int orderNo);

Then you can use #{seqId}, #{orderId} in the SQL without using parameterType attribute.

Comments

[Praditha]

hi dude, the link you're share was broken, can U share your Answer,.? thanks a lot -

[OCB]

Hi Praditha, unfortunately after the page really doesn't exist anymore after a year. Please see MalsR's answer, it is relevant.

[otterslide]

If you use @Param annotations, you also must add parameterType="map" to your SQL element in the mapper, or you will get a class cast exception.

[jonfornari]

avoid answers with just a link