Project scenario:

The background of the project is the monitoring and management platform and the alarm configuration section. It is necessary to query and return multiple data information. Among them, it is necessary to optimize the query and return function of subscribers. The original one can only enter one subscriber name for query and optimization is to enter multiple subscriber names, separated by English commas, and return the results containing them, with fuzzy search function

Problem Description:

The source code has only a single condition, that is, you only need to enter a person's name to search and return the result. Therefore, you only need dynamic Sql to use the like keyword at the same time and concat function to splice% with dynamic data to realize the fuzzy search function.
Carefully observe that the matching here is in two ways, that is, whether and the column depends on login_id or name match. If one of them meets the condition (using or keyword), the data will be displayed finally, which also increases the difficulty of our subsequent implementation.

  <!-- Original query   -->
            <if test="subscribeName.length != 0 and subscribeName!=null and subscribeName != 'null'">
                AND (IFNULL(s.login_id,'') LIKE
                    CONCAT('%',#{subscribeName},'%')
                OR IFNULL(l.name,'') LIKE
                    CONCAT('%',#{subscribeName},'%'))  
            </if>

Cause analysis:

First, let's start with data input. By observing the interface, we can see that the original subscribiname of String type becomes a long String separated by English commas when entered by multiple subscribers. To ensure readability, we are still modifying the sql statement in the xml file.

To realize the sequential matching of all names, use foreach to perform a traversal. The traversal data source is the array separated by subscribiname in the foreach header with split.

In name segmentation, it should be noted that if the user mistakenly enters some empty fields, it requires us to be robust and still be able to return the desired results. The general idea is replace method or trim method, but in fact trim is better, which will be discussed later

Solution:

Scheme 1: foreach+in

Using in in foreach to accurately match is the first method thought of. Although the function of fuzzy search is lost, this method is the most intuitive and easy to understand, and most of the online materials use the in keyword. In this method, we use two foreach services to log in respectively_ ID and name.

<!--  use in Query of   -->
            <if test="subscribeName!='' and subscribeName!=null and subscribeName != 'null'">
            and IFNULL(s.login_id,'')  in
            <foreach collection="subscribeName.split(',')" item="subscribeName" separator="," open="(" close=")">
                #{subscribeName}
            </foreach>
            or IFNULL(l.name,'')  in
            <foreach collection="subscribeName.split(',')" item="subscribeName" separator="," open="(" close=")">
                #{subscribeName}
            </foreach>
            </if>       

Scheme 2: foreach+like+replaceAll

In each round of traversal, concat splicing is still used for fuzzy search, and two foreach services and two matching methods are used, but the difference is that the separator of the foreach body is or at this time.
At the same time, we use replaceAll to remove all spaces in the string before split split split

<!-- foreach Internal fuzzy query like，Simultaneous use replace Remove all empty fields   -->
            <if test="subscribeName!='' and subscribeName!=null and subscribeName != 'null'">
                AND (IFNULL(s.login_id,'') LIKE
                <foreach collection="subscribeName.replaceAll('[\\[\\] ]','').split(',')" open="(" separator="or" close=")" item="subscribeName">
                    CONCAT('%',#{subscribeName},'%')
                </foreach>
                OR (IFNULL(l.name,'')) LIKE
                <foreach collection="subscribeName.replaceAll('[\\[\\] ]','').split(',')" open="(" separator="or" close=")" item="subscribeName">
                    CONCAT('%',#{subscribeName},'%'))
                </foreach>
            </if>     

Scheme 3: foreach+like+trim

Think about it carefully. In fact, the replaceAll in scheme 2 has some shortcomings, that is, we often only use random names in the test. In fact, if there is a foreign name input, if the spaces are removed together, it will be difficult to read.
The improvement method is to use trim to remove the space at the beginning and end of the field in each round of foreach.

<!-- To be in foreach Used in the statement trim    -->
            <if test="subscribeName!='' and subscribeName!=null and subscribeName != 'null'">
                AND (IFNULL(s.login_id,'') LIKE
                <foreach collection="subscribeName.split(',')" open="(" separator="or" close=")" item="subscribeName">
                    CONCAT('%',trim(#{subscribeName}),'%')
                </foreach>
                OR (IFNULL(l.name,'')) LIKE
                <foreach collection="subscribeName.split(',')" open="(" separator="or" close=")" item="subscribeName">
                    CONCAT('%',trim(#{subscribeName}),'%'))
                </foreach>
            </if>

The above is the code implemented by this topic.