Tuesday, September 8, 2015

How to pass multiple parameters to a mybatis xml mapper element

Using mybatis mapper XML files with only one parameter to pass into SQL statements is straightforward.  For example, if you had a select statement that retrieved a record by an id, then you need to:

  • Define an element in the xml mapper file.  i.e. the select element can receive a parameter type of "Long" with name of "logsId" with an id of "getRecordsByLogsId"
  • Define a function in the mapper java file.  i.e. the function name is "getRecordsByLogsId" and takes an Integer parameter named "logsId"
  • You'll notice that the id in the mapper xml file and the function name in the mapper java file match; as well as the parameter name defined in both.  
  • The parameter notaton of #{} tells mybatis to make a prepared statement
Single Parameter XML Mapper:
  <select id="getRecordsByLogsId" parameterType="Long"resultMap="baseResultMap">
    SELECT
          <include refid="base_column_list" />
    FROM vw_cherryshoe
    WHERE logs_id = #{logsId}
  </select>

Mapper Java Class - single parameter - VwCherryShoeMapper.java:
VwCherryShoe getRecordByLogsId(Integer logsId);

I had to create a SQL statement to find all records between a date range for a timestamp with no timestamp column.  I needed  a way to pass multiple parameters to the xml mapper.  To do this you have to do in addition:

  • In the xml mapper file, use a parameter type of "map".  This allows you to pass in multiple parameter names into the map
  • In the mapper java file, use the @Param annotation for the function's parameters

Two parameters XML Mapper:
<select id="getRecordsBySentDateRange" parameterType="map"resultMap="baseResultMap">
    SELECT
          <include refid="base_column_list" />
    FROM vw_cherryshoe
    WHERE sent_date BETWEEN TO_TIMESTAMP(#{fromTimestamp}, 'DD/MM/YYYY HH24:MI:SS')
    AND TO_TIMESTAMP(#{toTimestamp}, 'DD/MM/YYYY HH24:MI:SS')
  </select>

Mapper Java Class - multiple parameter  - VwCherryShoeMapper.java
List<VwCherryShoe> getRecordsBySentDateRange(
@Param("fromTimestamp") String fromTimestamp@Param("toTimestamp") String toTimestamp);

This worked with Postgresql 9.0.4 and Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.


2 comments:

  1. and how pass a string and long value in same map to update ???

    ReplyDelete
  2. You should be able to use a Long and String in the mapper class, then reference the @Param defined name in the xml mapper.

    ReplyDelete

I appreciate your time in leaving a comment!