SprintBoot+Vue+Elemnt-Ui 实现首拼模糊查询

1.首先前端组件使用的是select选择器，要想实现首拼查询，需要调用后端的接口，所以，这里我们使用select选择器的远程搜索属性。启用远程搜索，需要将filterable和remote设置为true，同时传入一个remote-method。remote-method为一个Function，它会在输入值发生变化时调用，参数为当前输入值。在Element官网中，有对该控件利用搜索功能快速查找的详细说明（https://element.eleme.cn/#/zh-CN/component/select）。下图是我的代码实现：

<el-form-item label="井名" prop="wellName">
          <el-select v-model="temp.wellName" filterable remote style="width: 360px" placeholder="请选择" :remote-method="remoteMethod" :loading="loading">
            <el-option
              v-for="item in wellOptions"
              :key="item.id"
              :label="item.wellAreaName"
              :value="item.wellAreaName"
            />
          </el-select>
        </el-form-item>

2.remoteMethod中就调用获取后端数据的接口，并根据后端接口传回来的数据对wellOptions对象赋值。

remoteMethod(query) {
      if (query !== '') {
        this.loading = true
        findWellAreaNameByCondition({ wellAreaName: query }).then(response => {
          this.loading = false
          this.wellOptions = response.data
        }).catch(() => {
          this.loading = false
        })
      } else {
        this.wellOptions = []
      }
    },

 3.关键点来啦！要想实现首拼查询的关键点就是上图中调用的findWellAreaNameByCondition方法的实现，那么来看后端。

        ① 首先是controller，就直接使用@RequesetParm注解接收请求参数

/**
     * 查询 井区块 不分页
     *
     * @param wellAreaName 井区域名
     * @return ListResult<Object>
     */
    @ApiOperation(value = "查询 井区块 不分页", tags = {"井区块"})
    @GetMapping(path = "/findWellAreaNameByCondition")
    public ListResult<Object> findWellAreaNameByCondition(@RequestParam(name = "wellAreaName", required = false) String wellAreaName) {
        List<SysWell> data = sysWellService.findWellAreaNameByCondition(wellAreaName);
        return ResultBuilder.buildListSuccess(data);
    }

         ② 然后是service

 /**
     * 查询井区块不分页数据
     *
     * @param wellAreaName 井通用名称
     * @return List<SysWell>
     */
    List<SysWell> findWellAreaNameByCondition(String wellAreaName);

        ③ 然后是serviceImplement，这里需要添加一个PinyinUtils类实现中文英文判定的功能。

 /**
     * 查询井区块不分页数据
     */
    @Override
    public List<SysWell> findWellAreaNameByCondition(String wellAreaName) {
        Map<String, Object> paramMap = new HashMap<>();
        if (PinyinUtils.isEnglish(wellAreaName)) {
            paramMap.put("pinyin", wellAreaName);
        } else {
            paramMap.put("wellAreaName", wellAreaName);
        }
        return sysWellMapper.findWellAreaNameByCondition(paramMap);
    }

package com.chinatechstar.component.commons.utils;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class PinyinUtils {

    // 检验是不是中文
    public static boolean isChinese(String str) {
        if (StrUtils.isNullOrEmpty(str)) {
            return false;
        }
        String regEx = "[\u4e00-\u9fa5]";
        Pattern pat = Pattern.compile(regEx);
        Matcher matcher = pat.matcher(str);
        boolean flg = false;
        if (matcher.find()) {
            flg = true;
        }

        return flg;
    }

    // 检验是不是英文
    public static boolean isEnglish(String str) {
        if (StrUtils.isNullOrEmpty(str)) {
            return false;
        }
        String regEx = "[A-Za-z]";
        Pattern pat = Pattern.compile(regEx);
        Matcher matcher = pat.matcher(str);
        boolean flg = false;
        if (matcher.find()) {
            flg = true;
        }

        return flg;
    }

    public static void main(String[] args) {
        boolean english = isChinese("");
        System.out.println(english);
    }
}

         ④ 最后是mybatis，需要注意的是，在xml中调用的GET_FIRST_PINYIN函数需要导入到数据库中，GET_FIRST_PINYIN是MySQL获取汉字拼音首字母的函数。

mapper:

/**
     * 查询井区块不分页数据
     *
     * @param paramMap 参数Map
     * @return List<SysWell>
     */
    List<SysWell> findWellAreaNameByCondition(Map<String, Object> paramMap);

mapper.xml 

<!-- 查询井区块不分页数据 -->
    <select id="findWellAreaNameByCondition" resultMap="sysWellMap">
        SELECT
        <include refid="allColumns"/>
        FROM sys_well sw WHERE sw.soft_delete = 1
        <if test="pinyin != null and pinyin != ''">
            AND GET_FIRST_PINYIN(sw.well_area_name) LIKE CONCAT('%',#{pinyin},'%')
        </if>
        <if test="wellAreaName != null and wellAreaName != ''">
            AND sw.well_area_name LIKE CONCAT('%', #{wellAreaName}, '%')
        </if>
    </select>

GET_FIRST_PINYIN

CREATE DEFINER=`root`@`localhost` FUNCTION `GET_FIRST_PINYIN`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;
    SET I = 1;
    SET V_RETURN = '';
    while I < LENGTH(P_NAME) do
        SET V_COMPARE = SUBSTR(P_NAME, I, 1);
        IF (V_COMPARE != '') THEN
            SET V_RETURN = CONCAT(V_RETURN, GET_FIRST_PINYIN_CHAR(V_COMPARE));
        END IF;
        SET I = I + 1;
    end while;

    IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;

    RETURN V_RETURN;
END

GET_FIRST_PINYIN_CHAR

CREATE DEFINER=`root`@`localhost` FUNCTION `GET_FIRST_PINYIN_CHAR`(PARAM VARCHAR(255)) RETURNS varchar(2) CHARSET utf8
BEGIN  
    DECLARE V_RETURN VARCHAR(255);  
    DECLARE V_FIRST_CHAR VARCHAR(2);  
    SET V_FIRST_CHAR = UPPER(LEFT(PARAM,1));  
    SET V_RETURN = V_FIRST_CHAR;  
    IF LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR ) THEN  
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(PARAM USING gbk),1)),16,10),  
        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,  
        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,  
        0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),  
    'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');  
    END IF;  
    RETURN V_RETURN;  
END

最后大功告成，来看一下效果吧

 大家可以在我的码云上拉取我的代码，附上地址：https://gitee.com/l749951543