SpringBoot+mybatis 制作系统查询工具

左手的ㄟ右手 2022-03-25 01:52 102阅读 0赞

# StringBoot+mybatis 制作系统查询工具 #

笔者在做一个项目时，有许多个功能需要的查询条件太多，而且有写很复杂，一开始使用mybatis 中的Example编写各种查询条件，但是因为页面太多，查询条件太多，如果一个一个的去编写会耗费大量的时间，所以制作了一个查询工具，统一配置值查询条件。  
因为项目集成了layer，所以本人的查询框就直接用的是弹出框。废话不多说，直接上代码  
[这里是笔者的查询系统的本分代码，提供参考][Link 1]

## 部分代码代码 ##

1.  **实体类**

public class SearchCondition { 
       private Long conId;//主键
       private String menutid;//菜单ID
       private String menutname;//菜单名称
       private String menuturl;//菜单URL
       private String condition;// 查询条件
       private String conditionCh;// 显示名称
       private String conditiontype;// 数据类型
       private Integer status;// 是否启用
       private Long ordernum;// 显示顺序
       private String replace;// 拼接字符
       //get set 省略

2、**字典类**  
字典类可以在项目启动的时候加载到环翠中，也可以在用的时候加载到缓存中，看 个人意愿，笔者是在使用的时候才加载到缓存中的

public class SearchConditionCache { 
    
       public static HashMap<String, LinkedHashMap<String, SearchCondition>> searchConditionMap = new HashMap<String, LinkedHashMap<String, SearchCondition>>();
    
       /** * 加载字典数据到缓存中 * @param searchConditions */
       public static void loadCacheData(Iterable<SearchCondition> searchConditions) { 
           for (Iterator<SearchCondition> iter = searchConditions.iterator(); iter.hasNext(); ) { 
               SearchCondition searchCondition = iter.next();
               addOrUpdateCache(searchCondition);
           }
       }
    
       /** * 根据menuturl查询 * @param menuturl * @return */
       public static LinkedHashMap<String, SearchCondition> findSCByMenuturl(String menuturl) { 
           if (searchConditionMap.containsKey(menuturl)) { 
               return searchConditionMap.get(menuturl);
           }
           return null;
       }
    
       /** * 根据typeId与codeId查询数据 * @param menuturl * @param conId * @return */
       public static SearchCondition findSCByMenuturlAndCondition(String menuturl, String conId) { 
           if (searchConditionMap.containsKey(menuturl)) { 
               LinkedHashMap<String, SearchCondition> tmp = searchConditionMap.get(menuturl);
               if (tmp.containsKey(menuturl)) { 
                   return tmp.get(menuturl);
               } else { 
                   return null;
               }
           } else { 
               return null;
           }
       }
    
       /** * 添加/更新一条字典数据 * @param searchCondition */
       public static void addOrUpdateCache(SearchCondition searchCondition) { 
           String menuturl = searchCondition.getMenuturl();
           if (searchConditionMap.containsKey(menuturl)) { 
               searchConditionMap.get(menuturl).put(searchCondition.getConId().toString(), searchCondition);
           } else { 
               LinkedHashMap<String, SearchCondition> temp = new LinkedHashMap<String, SearchCondition>();
               temp.put(searchCondition.getConId().toString(), searchCondition);
               searchConditionMap.put(menuturl, temp);
           }
       }
    
       /** * 从字典缓存中移除数据 * @param searchCondition */
       public static void removeFormCache(SearchCondition searchCondition) { 
           String menuturl = searchCondition.getMenuturl();
           if (searchConditionMap.containsKey(menuturl)) { 
               searchConditionMap.get(menuturl).remove(searchCondition.getConId().toString());
               if (searchConditionMap.get(menuturl).isEmpty()) { 
                   searchConditionMap.remove(menuturl);
               }
           }
       }
    }

3、**dao、service、mapper**

public interface SearchConditionDao{ 
    //省略
    }
    public interface SearchConditionMapper{ 
    //省略
    }
    @Service
    public class SearchConditionService{ 
        @Autowired
        SearchConditionMapper searchConditionMapper;
         /** * 按照菜单URL查询条件模型 * * @param menuturl * @return */
        public List<SearchCondition> findOneMenutSearchByMenuturl(String menuturl) { 
            if (ObjectUtils.isEmpty(SearchConditionCache.searchConditionMap)) { 
                List<SearchCondition> all = baseDao.findAll();
                SearchConditionCache.loadCacheData(all);
            }
            List<SearchCondition> list = new ArrayList<>();
            if (SearchConditionCache.findSCByMenuturl(menuturl) != null) { 
                LinkedHashMap<String, SearchCondition> scByMenuturl = SearchConditionCache.findSCByMenuturl(menuturl);
                Iterator<Map.Entry<String, SearchCondition>> iterator = scByMenuturl.entrySet().iterator();
                while (iterator.hasNext()) { 
                    Map.Entry entry = iterator.next();
                    list.add((SearchCondition) entry.getValue())；
                }
            }
            return list;
        }
    
        /** * 按照查询模板查询 * * @param url 菜单URL * @param con 模型ID * @param falg 符号 * @param values 值 * @param page 页数 * @param size 页面大小 * @return */
        public com.github.pagehelper.Page<SearchCondition> findAllBySql(String url, String[] con, String[] falg, String[] values, Integer page, Integer size) { 
            Example example = new Example();
            if (!StringUtils.isEmpty(con)) { 
                try { 
                    example = ExampleUtils.getExample(con, falg, values, url);
                } catch (Exception e) { 
                    e.printStackTrace();
                }
            }
            PageHelper.startPage(page, size);
            return (com.github.pagehelper.Page<SearchCondition>) searchConditionMapper.findAllBySql(example);
        }
    }

4、**controller**

@Controller
    @RequestMapping("cms/searchmodel")
    public class SearchModelController extends BaseController { 
    
       /** * 日志 */
       private static final Logger log = LoggerFactory.getLogger(ClwqSellContractController.class);
    
       /** * 查询条件service */
       @Autowired
       private SearchConditionService searchConditionService;
    
    
       /** * 列表页面 * * @param model * @param con * @param falg * @param values * @param page * @param size * @return */
       @RequestMapping("searchConditionList")
       public String searchConditionList(Model model, String[] con, String []falg, String[] values,
                                         @RequestParam(value = "page", defaultValue = "0") Integer page,
                                         @RequestParam(value = "size", defaultValue = "10") Integer size) { 
           Page<SearchCondition> pageData = searchConditionService.findAllBySql("cms/searchmodel/searchConditionList", con, falg, values, page, size);
           model.addAttribute("pageData", pageData.getResult());
           model.addAttribute("totalPages", pageData.getPages());
           model.addAttribute("number", pageData.getPageNum());
           return "cms/clf/searchConditionList";
       }
    
       /** * 修改 * * @param conId * @param model * @return */
       @RequestMapping("toEdit")
       public String toEdit(String conid, Model model) { 
           SearchCondition condition = new SearchCondition();
           if (!ObjectUtils.isEmpty(conid)) { 
               condition = searchConditionService.findOne(Long.valueOf(conid));
           }
           model.addAttribute("condition", condition);
           return "cms/clf/searchConditionEdit";
       }
    
       /** * 保存 * * @param condition * @param bs * @param model * @return */
       @RequestMapping("toSave")
       public String saveCondition(@Valid SearchCondition condition, BindingResult bs, Model model) { 
           if (bs.hasErrors()) { 
               model.addAttribute("condition", condition);
               return "cms/clf/searchConditionEdit";
           }
           SearchCondition save = searchConditionService.save(condition);
           SearchConditionCache.addOrUpdateCache(save);
           return "redirect:/cms/searchmodel/searchConditionList";
       }
    
       /** * 删除一行 * * @param conId * @return */
       @RequestMapping("toDelete")
       @ResponseBody
       public Map<String, Object> delete(String conId) { 
           Map<String, Object> map = new HashMap<>();
           try { 
               SearchCondition one = searchConditionService.findOne(Long.valueOf(conId));
               searchConditionService.delete(Long.valueOf(conId));
               SearchConditionCache.removeFormCache(one);
           } catch (Exception e) { 
               e.printStackTrace();
               map.put("msg", "系统异常：" + e);
               map.put("stu", 1);
               return map;
           }
           map.put("stu", 2);
           return map;
       }
    
       /** * 获取一个页面的查询条件 * * @param menuturl * @return */
       @ResponseBody
       @RequestMapping("OneMenutSearchModel")
       public Map<String, Object> getOneMenutSearchModel(String menuturl) { 
           Map<String, Object> map = new HashMap<>();
           List<SearchCondition> list = searchConditionService.findOneMenutSearchByMenuturl(menuturl);
           map.put("model", list);
           map.put("status", "ok");
           return map;
       }
    }

5、**js，css**

/** * 请求地址，全局参数 * @type {string} */
    var actions = '';
    
    /** * 弹出框 * @param act */
    function searchview(act) { 
        actions = act;
        layer.open({ 
            type: 1,
            title: "查询",
            area: ['580px', '460px'],
            fixed: false, //不固定
            maxmin: false,
            // content: parent.urlBaseName + 'cms/searchmodel/searchPage',
            content: createHTML(),
            btn: ['查询', '取消'],
            btnAlign: 'c',
            yes: function (index, layero) { 
                return serchParam(act);
            },
            btn2: function (index) { 
            }
        });
    }
    
    /** * 创建弹出框页面 * @returns {string} */
    function createHTML() { 
        var html = '<div class="searchPage" >\n' +
            '<div class="search_model">\n' +
            ' <div class="search_model_head">\n' +
            ' <table border="1" cellspacing="0" ><tr><th nowrap >备选</th></tr></table>\n' +
            ' </div>' +
            ' <div class="search_model_content">' +
            ' <table id="model" border="" cellspacing="1" >' +
            ' <tbody id="condition_model"></tbody></table>' +
            ' </div>' +
            '</div>' +
            '<div class="search_select" >' +
            ' <div class="search_model_head"> ' +
            ' <table border="1" cellspacing="0">' +
            ' <tr>' +
            ' <th width="38%" nowrap>字段</th>' +
            ' <th width="29%" nowrap>表达式</th>' +
            ' <th >值</th></tr>\n' +
            ' </table>\n' +
            ' </div>' +
            ' <div class="search_model_content">' +
            ' <table border="1" cellspacing="1" style="width: 100%">' +
            ' <tbody id="condition_select"></tbody>' +
            ' </table>' +
            '</div>' +
            '</div></div>'
        getSearchModel();
        return html;
    }
    
    /** * 获取查询模板数据 */
    function getSearchModel() { 
        var action = parent.urlBaseName + 'cms/searchmodel/OneMenutSearchModel';
        actions = actions.substring(actions.indexOf("/", 2) + 1, actions.length);
        $.ajax({ 
            url: action,
            // url:'OneMenutSearchModel',
            data: { menuturl: actions},
            type: 'POST', //GET
            async: true,    //或false,是否异步
            //timeout: 5000, //超时时间
            dataType: 'json',  //返回的数据格式：json/xml/html/script/jsonp/text
            success: function (data) { 
                if (data.status == 'ok') { 
                    var model = '';
                    $.each(data.model, function (index, one) { 
                        model += '<tr ondblclick="selectModel(this)"><td data = "' + one.conId + '" data-type="' + one.conditiontype + '"> <button>' + one.conditionCh + '</button></td></tr>';
                    })
                    $('#condition_model').append(model);
                }
            }
        })
    }
    
    /** * 选中模板 * @param tr */
    function selectModel(tr) { 
        var html = '<tr ondblclick = "deleteCondition(this)">';
        html += '<td width="40%" data="' + $(tr).children().attr('data') + '">' + $(tr).children().html() + '</td>';
        var tp = $(tr).children().attr('data-type');
        html += '<td width="30%">' + '<select>' + getOption(tp) + '</select>' + '</td>';
        html += '<td width="30%">';
        switch (tp) { 
            case "2":
                html += '<input type="number" placeholder="请输入" />';
                break;
            case "3":
                html += '<input type="text" placeholder="请输入" />';
                break;
            default:
                html += '<input type="text" placeholder="请输入" />';
                break;
        }
        html += '</td></tr>';
        $('#condition_select').append(html);
        //$(tr).remove();
    }
    
    /** * 获取符号 * @param tp * @returns {string} */
    function getOption(tp) { 
        var op = '<option value="eq">等于</option>' +
            '<option value="noteq">不等于</option>';
        switch (tp) { 
            case "1":
                op += '<option value="like">包含</option>' +
                    '<option value="notlike">不包含</option>';
                break;
            case "2":
                op += '<option value="gt">大于</option>' +
                    '<option value="gteq">大于等于</option>' +
                    '<option value="lt">小于</option>' +
                    '<option value="lteq">小于等于</option>';
                break;
            case "3":
                break;
            default:
                break;
        }
        return op;
    }
    
    /** * 删除选中项 * @param tr */
    function deleteCondition(tr) { 
        $(tr).remove();
    }
    
    /** * 获取数据，执行查询 * @param act * @returns {boolean} */
    function serchParam(act) { 
        var state = true;
        var con = new Array();
        var falg = new Array();
        var values = new Array();
        var k = 0;
        $.each($('#condition_select').find('tr'), function (i, o) { 
            $.each($(o).find('td'), function (j, v) { 
    
                if (j == 0) { 
                    con[k] = $(v).attr('data');
                } else if (j == 1) { 
                    falg[k] = $(v).find('select option:selected').val();
                } else if (j == 2) { 
                    if ($(v).find('input').val() == null || $(v).find('input').val() == "") { 
                        layer.msg("第" + (i + 1) + "个查询条件值不能为空");
                        state = false;
                    } else { 
                        values[k] = $(v).find('input').val();
                    }
                }
                if (!state) { 
                    return false;
                }
            })
            k++;
        })
        if (!state) { 
            return false;
        } else { 
            layer.close();
            showLoading('数据查询中...');
            location.href = act + "?con=" + con + "&falg=" + falg + "&values=" + values;
        }
    }

.searchPage { 
        width: 580px;
        height: 360px;
        overflow: hidden;
    }
    
    .search_model { 
        width: 200px;
        height: 350px;
        float: left;
        margin-left: 10px;
    }
    
    .search_model_head { 
        line-height: 28px;
    }
    
    .search_model_content { 
        height: 90%;
        overflow-y: scroll;
        border: 1px solid #999;
    }
    
    .search_select { 
        margin-left: 10px;
        float: left;
        width: 350px;
        height: 350px;
    }
    
    .search_select_head { 
        line-height: 28px;
    }
    
    .search_select_content { 
        height: 90%;
        overflow-y: scroll;
    }
    
    .searchPage table { 
        width: 100%;
    }
    
    .searchPage table th { 
        background-color: #1C25D5;
        text-align: center;
    }
    
    .searchPage table tr:nth-child(even) { 
        background-color: #ccc;
    }
    
    .searchPage table tr th { 
        height: 28px;
        line-height: 28px;
        background: #999
    }
    
    .searchPage table tr td { 
        height: 28px;
        line-height: 28px;
        text-align: center;
        vertical-align: middle;
    
    }
    
    /* css 注释：默认隔行背景颜色 */
    .searchPage table tr.over td { 
        background: #d4d4d4;
    }
    
    .searchPage table tr input { 
        width: 99%;
        height: 99%;
        background-color: transparent;
    }
    
    .searchPage table tr button { 
        width: 100%;
        height: 100%;
        background-color: transparent;
    }
    
    .searchPage table tr select { 
        height: 99%;
        width: 99%;
        background-color: transparent;
    }

6、**测试**  
查询弹出框  
![查询弹出框][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDQwNTUy_size_16_color_FFFFFF_t_70]  
新增查询模板  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDQwNTUy_size_16_color_FFFFFF_t_70 1]

## 结语 ##

这是笔者从自己的项目中复制出来的，仅供参考，提供思路  
[笔者的查询系统][Link 1];

[Link 1]: https://download.csdn.net/download/qq_43040552/10925699
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDQwNTUy_size_16_color_FFFFFF_t_70]: /images/20220325/82d4244f471548b6b89287f792866a84.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDQwNTUy_size_16_color_FFFFFF_t_70 1]: /images/20220325/17ad2b9d2b16429880c46b54cf750f8e.png