當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

模糊搜索的实现

發布時間：2024/3/12 编程问答 26 豆豆

生活随笔收集整理的這篇文章主要介紹了模糊搜索的实现小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

要實現模糊搜索的功能，及在模糊搜索框內輸入要查詢的信息，會在用戶基礎信息欄顯示模糊搜索的結果

以用戶名模糊搜索為例

頁面上實現點擊回車鍵即可進行搜索的功能

<%@ page language="java" contentType="text/html; charset=UTF-8"%><script type="text/javascript"></script> ················································ <tr>
　 <td><input type="text" id="txtNebulosity" name="txtNebulosity" size="25" style="height:60px;" /><br/><td></tr>
<tr><td><input type="button" id="nebulosity" name="nebulosity" value="模糊搜索" οnclick="nebulosityQuery();" /><td></tr> ·····

在sf_leftQuery_js里編寫模糊搜索的方法

/*模糊搜索@parm:queryId 點擊查詢DOM id */ function nebulosityQuery(queryId) {var cdbh = window.parent.$('#cdbh').val();var nebulositySearch = function () {var cardNo = $("#cardNo").val();var codeNebulosity = $("#codeNebulosity").val();var nameNebulosity = $("#nameNebulosity").val();var addrNebulosity = $("#addrNebulosity").val();var cardNoNote = $("#cardNo").attr("noteval");var codeNote = $("#codeNebulosity").attr("noteval");var nameNote = $("#nameNebulosity").attr("noteval");var addrNote = $("#addrNebulosity").attr("noteval");$("#" + queryId).datagrid({queryParams : {"queryVo.firstRequest" : false,//"queryVo.nebulosity":$("#txtNebulosity").val(),"queryVo.codeNebulosity" : codeNebulosity == codeNote ? "" : codeNebulosity,"queryVo.nameNebulosity" : nameNebulosity == nameNote ? "" : nameNebulosity,"queryVo.addrNebulosity" : addrNebulosity == addrNote ? "" : addrNebulosity,"queryVo.yhkh" : cardNo == cardNoNote ? "" : cardNo,"treeUtil.fgs" : "","queryVo.cdbh":cdbh,}});}$("#nebulosity").click(nebulositySearch);$('#txtNebulosity').keypress(function (event) {onPressEnter(event, nebulositySearch);}); }

搜索的實現是依靠準確的sql查詢語句來實現，以下為dao層創建查詢sql語句的方法

private Object[] getSql(BDXCPageContext context, QueryVo queryVo) {String condition = "yh.xq||yh.yyhbm||yh.yhbm||yh.yhmc||yh.yhbh||yh.fgs||yh.qy||yh.fwz||yh.dz||yh.rlz||yh.yhkh||yh.gnzt||yh.lxdh||yh.jtdh||yh.qtdh||yh.sfzh||yh.kzfs||yh.cnfs||yh.yhlb";List list = new ArrayList();// 基礎hqlStringBuilder baseHql = new StringBuilder();// 基礎sqlStringBuilder baseSql = new StringBuilder();baseHql.append("from SfJmyhT yh where zf=0 ");baseSql.append(" select distinct yh.rowid rd,yh.yhbm,yh.dz from sf_jmyh_t yh left join sf_jmmj_t mj on yh.yhbh = mj.yhbh inner join (").append("select xqqx.mc xq, rlzqx.mc rlz,fwzqx.mc fwz, qyqx.mc qy,fgsqx.mc fgs, xqqx.yhlx, xqqx.sjqx from sf_sjqx_t xqqx ").append("inner join sf_sjqx_t rlzqx on xqqx.sjbh = rlzqx.bh and xqqx.sjlx = 5 ").append("inner join sf_sjqx_t fwzqx on rlzqx.sjbh = fwzqx.bh and rlzqx.sjlx = 4 ").append("inner join sf_sjqx_t qyqx on fwzqx.sjbh = qyqx.bh and fwzqx.sjlx = 3 ").append("inner join sf_sjqx_t fgsqx on qyqx.sjbh = fgsqx.bh and qyqx.sjlx = 2) qx ").append("on yh.fgs = qx.fgs and yh.qy = qx.qy and yh.fwz = qx.fwz and yh.rlz = qx.rlz and yh.xq = qx.xq where yh.zf = 0");// 條件StringBuilder whereSql = new StringBuilder();//jsfs 為0代表全部查詢if(!BDXCUtil.objectIsEmpety(queryVo.getJsfs())&&queryVo.getJsfs().equals("0")){//只查詢二部制}else if (!BDXCUtil.objectIsEmpety(queryVo.getJsfs())&&queryVo.getJsfs().equals("二部制結算")) {whereSql.append(" and mj.jsfs = ?");list.add(queryVo.getJsfs());//查詢計量結算之外的兩種情況}else{whereSql.append(" and mj.jsfs != ?");list.add("計量結算");}// 分公司if (!BDXCUtil.objectIsEmpety(queryVo.getFgs())) {whereSql.append(" and yh.fgs= ?");list.add(queryVo.getFgs());}// 區域if (!BDXCUtil.objectIsEmpety(queryVo.getQy())) {whereSql.append(" and yh.qy= ?");list.add(queryVo.getQy());}// 服務站if (!BDXCUtil.objectIsEmpety(queryVo.getFwz())) {whereSql.append(" and yh.fwz= ?");list.add(queryVo.getFwz());}// 熱源if (!BDXCUtil.objectIsEmpety(queryVo.getRy())) {whereSql.append(" and yh.ry= ?'");list.add(queryVo.getRy());}// 換熱站if (!BDXCUtil.objectIsEmpety(queryVo.getRlz())) {whereSql.append(" and yh.rlz= ?");list.add(queryVo.getRlz());}// 小區if (!BDXCUtil.objectIsEmpety(queryVo.getXq())) {whereSql.append(" and yh.xq= ?");list.add(queryVo.getXq());}// 用戶卡號if (!BDXCUtil.objectIsEmpety(queryVo.getYhkh())) {whereSql.append(" and yh.yhkh= ?");list.add(queryVo.getYhkh());}// 用戶編碼if (!BDXCUtil.objectIsEmpety(queryVo.getYhbm())) {whereSql.append(" and yh.yhbm= ?");list.add(queryVo.getYhbm());}// 用戶編號if (!BDXCUtil.objectIsEmpety(queryVo.getYhbh())) {whereSql.append(" and yh.yhbh= ?");list.add(queryVo.getYhbh());} // if (!BDXCUtil.objectIsEmpety(queryVo.getYhmc())) { // whereSql.append(" and yh.yhmc like ?"); // list.add("%" + queryVo.getYhmc() + "%"); // }// 用戶名稱調整為精確查詢　if (!BDXCUtil.objectIsEmpety(queryVo.getYhmc())) {whereSql.append(" and (yh.yhmc=? or yh.cap_yhmc= ?)");list.add(queryVo.getYhmc().toUpperCase());list.add(queryVo.getYhmc().toUpperCase());}// 合同編號if (!BDXCUtil.objectIsEmpety(queryVo.getHtbh())) {whereSql.append(" and yh.htbh= ?");list.add(queryVo.getHtbh());}// 樓號if (!BDXCUtil.objectIsEmpety(queryVo.getLh())) {whereSql.append(" and yh.lh= ?");list.add(queryVo.getLh());}// 單元號if (!BDXCUtil.objectIsEmpety(queryVo.getDyh())) {whereSql.append(" and yh.dyh= ?");list.add(queryVo.getDyh());}//樓層if (!BDXCUtil.objectIsEmpety(queryVo.getCs())) {whereSql.append(" and yh.cs= ?");list.add(queryVo.getCs());}//室號if (!BDXCUtil.objectIsEmpety(queryVo.getSh())) {whereSql.append(" and yh.sh= ?");list.add(queryVo.getSh());}// 層數 // if (!BDXCUtil.objectIsEmpety(queryVo.getCs())) { // whereSql.append(" and yh.cs= ?"); // list.add(new Integer(queryVo.getCs())); // }// 地址if (!BDXCUtil.objectIsEmpety(queryVo.getDz())) {whereSql.append(" and yh.dz like ?");list.add("%" + queryVo.getDz() + "%");}//單位名稱if (!BDXCUtil.objectIsEmpety(queryVo.getDwmc())) {whereSql.append(" and yh.dwmc like ?");list.add("%" + queryVo.getDwmc() + "%");}// 供暖狀態if (!BDXCUtil.objectIsEmpety(queryVo.getGnzt())) {//散戶入網模塊只顯示未裝用戶由于鏈接中傳參不可用中文，所以用字符代替if(!queryVo.getGnzt().equalsIgnoreCase("0")){whereSql.append(" and yh.gnzt= ?");list.add(queryVo.getGnzt());}else{whereSql.append(" and (yh.gnzt= ? or yh.gnzt = ?)");list.add("未裝");list.add("未供");}}// 收費員if (!BDXCUtil.objectIsEmpety(queryVo.getSfy())) {whereSql.append(" and yh.sfy= ?");list.add(queryVo.getSfy());}// 用戶類別if (!BDXCUtil.objectIsEmpety(queryVo.getYhlb())) {whereSql.append(" and yh.yhlb= ?");list.add(queryVo.getYhlb());}// 收費類別if (!BDXCUtil.objectIsEmpety(queryVo.getSflb())) {whereSql.append(" and yh.sflb= ?");list.add(queryVo.getSflb());}// 最小收費面積if (queryVo.getMinNum() != null) {whereSql.append(" and yh.sfmj >= ? ");list.add(queryVo.getMinNum());}// 最大收費面積if (queryVo.getMaxNum() != null && queryVo.getMaxNum() != 0) {whereSql.append(" and yh.sfmj <= ?");list.add(queryVo.getMaxNum());}// 拆網用戶if (!BDXCUtil.objectIsEmpety(queryVo.getCwyh())) {whereSql.append(" and yh.gnzt not in (?)");list.add(queryVo.getCwyh());}// 鎖閉閥if ("lockValveType".equals(queryVo.getBusinessType())) {whereSql.append(" and yh.sfazsbf = ?");list.add("是");}//根據系統菜單判斷是單位還是居民用戶if(!BDXCUtil.objectIsEmpety(queryVo.getCdbh())){if("137".equals(queryVo.getCdbh())){whereSql.append(" and yh.yhlb<>'單位' ");}if("503".equals(queryVo.getCdbh())){whereSql.append(" and yh.yhlb='單位' ");}}// 角色編號whereSql.append(" and qx.sjqx like ? "); list.add("%'" + queryVo.getJsbh() + "'%");String nebulosity = queryVo.getNebulosity();if (!BDXCUtil.objectIsEmpety(queryVo.getNebulosity())) {String[] nebs = nebulosity.replaceAll("　 ", " ").split(" ");for (int i = 0; i < nebs.length; i++) {whereSql.append(" and instr(").append(condition).append(",?,1,1) > 0");list.add(nebs[i].trim());}}String codeNebulosity = queryVo.getCodeNebulosity();if (!BDXCUtil.objectIsEmpety(codeNebulosity)) {String[] nebs = codeNebulosity.replaceAll("　 ", " ").split(" ");whereSql.append(" and ( ");for (int i = 0; i < nebs.length; i++) {if(i!=0) whereSql.append(" and ");whereSql.append(" instr(").append(condition).append(",?,1,1) > 0");list.add(nebs[i].trim());}whereSql.append(" ) ");}String nameNebulosity = queryVo.getNameNebulosity();if (!BDXCUtil.objectIsEmpety(nameNebulosity)) {whereSql.append(" and instr(").append("YHMC").append(",?,1,1) > 0");list.add(nameNebulosity.trim());}String addrNebulosity = queryVo.getAddrNebulosity();if (!BDXCUtil.objectIsEmpety(addrNebulosity)) {whereSql.append(" and instr(").append("DZ").append(",?,1,1) > 0");list.add(addrNebulosity.trim());}String cardNo = queryVo.getYhkh();if (!BDXCUtil.objectIsEmpety(cardNo)) {whereSql.append(" and instr(yhkh,?,1,1) > 0");list.add(cardNo.trim());}// 排序sqlStringBuilder orderSql = new StringBuilder();orderSql.append(" order by yh.yhbm, yh.yhbh");StringBuilder order = new StringBuilder();order.append(" order by yh.yhbm,yh.dz");// hql( =baseSql +whereSql +orderSql)StringBuilder hql = new StringBuilder();hql.append(baseHql.toString()).append(whereSql.toString()).append(orderSql.toString());// sqlStringBuilder sql = new StringBuilder();sql.append("select yhbh,yhbm,yhmc,dz,jzmj,symj,cgmj,sfmj,gnzt,yhlb,yhzt,fgs,qy,fwz,rlz,xq, ry,lh,dyh,cs,sh,mp,yhkh,").append("sfzh,jtdh,lxdh,qtdh,sfazsbf,rwbm,to_char(rwrq,'yyyy-MM-dd') rwrq,to_char(ksgnrq,'yyyy-MM-dd') ksgnrq,").append("czy,kzfs,sfy,sfyxyhjf,sfyxyhcx,sfdbh,sfyxsf,byxsfyy,sfyxtg,byxtgyy,sfebz,fwwz,fwcx,hth,zdyx1,bz,grxz,ryxz,yyhbm,sfswhf,gnfs,sflntg ").append("from sf_jmyh_t yh where rowid in(select distinct rd from (select distinct rd,rownum rn from (" + baseSql.toString() + whereSql.toString() + order.toString() + " ) where rownum <= ?) where rn > ? ) ").append(orderSql.toString());// 查詢記錄數sqlStringBuilder countSql = new StringBuilder();countSql.append("select count(distinct rd) num from (").append(baseSql.toString()).append(whereSql.toString()).append(")");// 查詢合計sqlStringBuilder sumSql = new StringBuilder();sumSql.append("select sum(jzmj) JZMJ,sum(symj) SYMJ,sum(cgmj) CGMJ,sum(sfmj) SFMJ,count(1) NUM from sf_jmyh_t where rowid in (").append(baseSql.toString()).append(whereSql.toString()).append(")");Object[] sqls = new Object[6];sqls[0] = list;sqls[1] = hql.toString();sqls[2] = sql.toString();sqls[3] = countSql.toString();sqls[4] = sumSql.toString();sqls[5] = baseSql.toString();return sqls;}

當執行模糊搜索控制臺輸出sql語句（這里為方便閱讀做了一下分割）

select yhbh,yhbm,yhmc,dz,jzmj,symj,cgmj,sfmj,gnzt,yhlb,yhzt,fgs,qy,fwz,rlz,xq, ry,lh,dyh,cs,sh,mp,yhkh,sfzh,jtdh,lxdh,qtdh,sfazsbf,rwbm,to_char(rwrq,'yyyy-MM-dd') rwrq,to_char(ksgnrq,'yyyy-MM-dd') ksgnrq,czy,kzfs,sfy,sfyxyhjf,sfyxyhcx,sfdbh,sfyxsf,byxsfyy,sfyxtg,byxtgyy,sfebz,fwwz,fwcx,hth,zdyx1,bz,grxz,ryxz,yyhbm,sfswhf,gnfs,sflntg from sf_jmyh_t yh where rowid in(select distinct rd from ( select distinct rd,rownum rn from ( select distinct yh.rowid rd,yh.yhbm,yh.dz from sf_jmyh_t yh left join sf_jmmj_t mj on yh.yhbh = mj.yhbh inner join (select xqqx.mc xq, rlzqx.mc rlz,fwzqx.mc fwz, qyqx.mc qy,fgsqx.mc fgs, xqqx.yhlx, xqqx.sjqx from sf_sjqx_t xqqx inner join sf_sjqx_t rlzqx on xqqx.sjbh = rlzqx.bh and xqqx.sjlx = 5 inner join sf_sjqx_t fwzqx on rlzqx.sjbh = fwzqx.bh and rlzqx.sjlx = 4 inner join sf_sjqx_t qyqx on fwzqx.sjbh = qyqx.bh and fwzqx.sjlx = 3 inner join sf_sjqx_t fgsqx on qyqx.sjbh = fgsqx.bh and qyqx.sjlx = 2) qx on yh.fgs = qx.fgs and yh.qy = qx.qy and yh.fwz = qx.fwz and yh.rlz = qx.rlz and yh.xq = qx.xq where yh.zf = 0 and yh.yhlb<>'單位' and qx.sjqx like ? order by yh.yhbm,yh.dz ) where rownum <= ?) where rn > ? ) order by yh.yhbm, yh.yhbh

總結

以上是生活随笔為你收集整理的模糊搜索的实现的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

模糊

上一篇：面对百亿数据，HBase为什么查询速度依
下一篇：【已解决】关于IDEA中 Driver