Multi value wildcard search in ibatis

Question

I have a search field that can take values separated by blank spaces for multi-value search. But now I want to make it a multi-value search using wildcards. For example, if I add 'away%' and 'ds%' separated by a blank space in the search field it should search for both wild card values in a given column. The query should be as :

SELECT * FROM [table]
WHERE [column] LIKE ('away%') OR
[column] LIKE ('ds%');

How can I achieve this repetitive column reference based on the number of search values? P.S: search value can be of any number.

Answer 1

Here is an example of how to do that:

Mapper Class:

List<MyEntity> search(String terms);

Mapper XML:

<select id="search" resultMap="myEntityResultMap">
    SELECT * FROM `my_table`
    <if test="_parameter != null">
        <where>
            <foreach collection="_parameter.split('\\s+')" item="item" open="(" close=")" separator=" OR ">
                name LIKE CONCAT(CONCAT('%', #{item}), '%')
            </foreach>
        </where>
    </if>
</select>

Notes:

• <if> Checks for a null parameter. In that case, no WHERE clause will be added.
• <foreach> Splits the parameter using the \s+ regex and then iterates over the results adding each term in a separate name LIKE clause and joining them by OR.
• CONCAT(CONCAT('%', #{item}), '%') Is written in this way to be used with Oracle database (takes two parameters in Oracle). It can be different in other database systems. For example, CONCAT('%', #{item}, '%') can be used in MySQL.