JEECG Advanced Finder

I. Background

for users, the query function is divided into three levels by ease of use:

1. The simplest query operation is an input box, full-text search, such as Baidu, background technology use search engine, need to design and build indexes, technology is more complex, suitable for documents and information database retrieval, but the results are difficult to control accurately.

2. Next is the definition of the field query, many enterprise information systems are mostly used this query, specific fields for the module query targeted, using a low threshold, applicable to enterprise internal information Management system module customization.

3. The last one is a flexible query editor specifically for the data model, the most difficult to use, but the query results can be flexible and precise control, suitable for a certain it knowledge and the data is quite familiar to users, while avoiding the direct database exposure to the user of the security risks.

It is not difficult to find a good system software query basically covers the above three types of query function

Second, the JEECG realization principle

JEECG System module mainly uses the second way query function, uses Hibernate's QBC to encapsulate the front-end query condition, the custom filter condition for the field, finally transforms the SQL to execute the database query.

Advantages of existing solutions:

1) No need for complex development to implement field retrieval by default, support range, fuzzy, exact matching query

2) built-in simple expression support:! * To implement non-, fuzzy, array, etc.

Disadvantages of existing scenarios:

1) not supported or conditions

2) Do not support inter-field operations Field1=field2

3) Non-Hibernate associated table Federated query is not supported

4) Single-field conditions can only occur once

5) SQL nesting not supported

6) to support the above features requires additional development of custom work

In some specific scenarios, the user wants to obtain relevant information through the module with the help of the third-party report module function or to the developer, the development of the report module has a certain pressure. For example, the user to query from the module special part of the data to operate, the existing query function can not be done, the report module can not operate the editor, this time is the advanced query custom query function comes in handy.

Iii. Examples of similar applications

Let's look at the Outlook mail query design:

1. Full-Text Search type

2. Field Custom Type

3. Advanced Search

Microsoft Team Foundation Server Query Editor:

design and implementation of JEECG query device

UI Design: first to implement advanced queries, you must encapsulate the data table meta-information metadata to generalize, we use the columnlist from the DataGrid tag to generate the field drop-down list. As an advanced query, the UI scale from ease-of-use and usage-frequency perspective is not too much, with pop-up windows implemented,

Here's the table I used Treegrid, because more suitable for the expression of SQL syntax tree, but did not implement the tree structure, the condition only added more than, less than, including the common operators, waiting for the subsequent expansion of SQL nesting.

There is a query history on the right, and the JSON condition of each query is saved in an array for fast re-checking, this historical query record is saved in the client cache using HTML5 's localstorage, and the next login is still valid. For browsers that do not support localstorage, cookie storage is used.

front-to-back interaction: from to forward compatibility and the least factor for the framework upgrade, encapsulate all the contents of the query assembly into a JSON string, as a parameter _sqlbuidler passed to the background, assembled by the background control conditions, more secure, prevent SQL injection and other vulnerabilities

JSON format Example:

[{"id": 101, "field": "User_name", "condition": "Like", "value": "% Wang", "Relation": "and"},{"ID": 101, "field": "User_name "," condition ":" Like "," value ":"% Wang "," Relation ":" and "}]

Background Wrapper handles Java objects querycondition

Package Org.jeecgframework.web.demo.entity.test;import Java.util.list;public class Querycondition {String field; String type; String condition; String value; String relation; List<querycondition> children;public list<querycondition> GetChildren () {return children;} public void Setchildren (list<querycondition> children) {This.children = children;} Public String GetField () {return field;} public void SetField (String field) {This.field = field;} Public String GetType () {return type;} public void SetType (String type) {this.type = type;} Public String getcondition () {return condition;} public void Setcondition (String condition) {this.condition = condition;} Public String GetValue () {return value;} public void SetValue (String value) {this.value = value;} Public String getrelation () {return relation;} public void Setrelation (String relation) {this.relation = relation;} Public String toString () {stringbuffer sb =new stringbuffer (); Sb.append (this.relation). Append (""); Sb.append ( This.field). AppEnd (""). Append (This.condition). Append (""), if ("Java.util.Date". Equals (This.type)) {Sb.append ("to_date (')"). Append ( This.value). Append ("', ' Yyyy-mm-dd ')"); else if ("Java.lang.Number". Equals (This.type) | | This.condition.indexOf ("in") >0) {//todo needs to be processed by type sb.append (This.value);} Else{sb.append ("'"). Append (This.value). Append ("'");//todo need to handle special characters}return sb.tostring ();}} Background parsing processing code, modifying org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil:public static void Installhql (Criteriaquery CQ , Object searchobj,map<string, string[]> parametermap) {Installhqljoinalias (CQ, Searchobj, GetRuleMap (), Parametermap, "");//--adds a special SQL parameter processing----------------------------------try{if (Stringutil.isnotempty ( Parametermap.get ("_sqlbuilder"))) {list<querycondition> List = Jsonhelper.tolist (Parametermap.get ("_ Sqlbuilder ") [0], Querycondition.class); String sql=getsql (List, ""); System.out.println ("DEBUG sqlbuilder:" +sql); Cq.add (restrictions.sqlrestriction (SQL));}} catch (Exception e) {e.printstacktrace ();} -IncreaseAdd a special SQL parameter to handle----------------------------------Cq.add ();} 

V. IMPLEMENTATION of constraints

1) Only the standard named table name is supported, because it is the name of the database table with the underlined names of the Java hump, if the table name is not the rule field name will be converted error;
2) The SQL front-end interface does not have too much constraint and control in the input, so the non-professional users will appear illegal statements and query no results, it is recommended that the person who understands the use of SQL not directly to the user.

Vi. use of labels

the datagrid tag has already encapsulated the Advanced Finder, and the JS variable has been named for each module's ID to prevent collisions.

Simply add a property querybuilder="true"to the DataGrid tag when using it, for example

    <t:datagrid name= "Jeecgdemolist" title= "Demo sample List"

    Autoloaddata= "true" Actionurl= "Jeecgdemocontroller.do?datagrid"

    Sortname= "UserName" fitcolumns= "true" idfield= "id" fit= "true" querymode= "group" checkbox= "true"

        Querybuilder= "true" >

The module UI effect is as follows, resetting one more button after the Reset:

Seven, TODO

UI aspect: Ease of use can also be improved, such as when a field and condition = is selected, the value automatically enumerates a subset of candidates based on the actual value of the column in the database table; When the field is of type date, the value edit box becomes a date control; The value edit box has a check or can only enter an integer spinbox to prevent

Features: List<querycondition> object conversion to the SQL GetSQL () function only implements the assembly of native SQL, this block also has a lot of room for improvement, can increase the field type (int,string,date) recognition and processing, operator (regular match) , built-in expressions, and functions (like TFS).

Security: The module button has not been bound with the permissions, but only through the tag properties to switch, should add a dynamic permission by the system configuration to control

JEECG Advanced Finder