Fuzzy Query + Dynamic (Multiple Conditions) Query (ssm)

Fuzzy query and dynamic query are one of the necessary functions of some management classes. Just as we have done this function today, let's summarize it.

I am in the framework of ssm, the environment configuration is omitted
First of all, dao.xml is sql statement.

    <select id="fine"  resultType="cn.edu.model.Staff">
        select * from  staff
        <where>
--         Fuzzy query
        <if test="keyWords != null and keyWords != ''">
            and concat(id,name,sex,number,age,workAge) like  "%"#{keyWords}"%"
        </if>
--         Dynamic query
        <if test="name != null and name != ''">
            and name like "%"#{name}"%"
        </if>
        <if test="id != null and id != ''">
            and id=#{id}
        </if>
        <if test="number != null and number != ''">
            and number=#{number}
        </if>
        </where>
    </select>

And then use where 1=1 to judge. Here's the < where label.
This tag automatically identifies and or and judges
concat is in the MySQL statement, the function is to connect the statement what, if you want to know it, you can check it by yourself. (brackets are the attributes of columns, which can realize the fuzzy query of multiple attributes. If there is only one attribute, there is no need to use this directly.
Property like% field%
if is the core of dynamic query, which is well understood.

Service is not written, just implement dao directly.
Next controller

if (value != null && !"".equals(value)){
    if ("name".equals(type)){
        staff.setName(value);
    }else if ("id".equals(type)) {
        if ("id".equals(type)) {
            try {
                Integer id = Integer.valueOf(value);
                staff.setId(id);
                staff.setMark(false);
            } catch (Exception e) {
            }
        }
    }else if ("number".equals(type)) {
        staff.setNumber(value);
    } else if ("keyWords".equals(type)) {
        staff.setKeyWords(value);
    }

The above is the core code, which needs to pass in the values of type and value

The following is jsp.

<div class="text-c">
    <form action="/staff/getAll" class="form form-horizontal">
        <select class="select-box inline" style="width:100px" name="type" size="1">
        <option value="keyWords">Keyword</option>
        <option value="id">id</option>
        <option value="name">Full name</option>
        <option value="number">ID number</option>
    </select>&nbsp;
        <input type="text" value= "${value}" class="input-text" style="width:250px" placeholder="Please input"  name="value">&nbsp;
        <input type="submit" class="btn btn-success Hui-iconfont radius" name="" value="&#xe665; Search for Employees
    </form>
</div>

The following effects

Tags: xml SQL MySQL Attribute

Posted on Sun, 06 Oct 2019 06:59:00 -0700 by haagen