Design of a dynamic SQL query order data

Order data is generally changed more data, query conditions are more complex, if there is no rule to explain, the change of the interface will be more frequent; because the individual does not like to use stitching SQL in the business side of the way, in addition to doing a safe origin, the business side of SQL splicing easy SQL injection, so do not consider using In order to satisfy this characteristic, the condition of the order is divided into one: state, user, time, company, pick-up place and drop-off place;

The first step: the state query is processed first, the status query is defined in the T_om_group table, the background staff can manually add the state group, the front-end service only needs the state group corresponding to which state;

group_id 1 corresponds to Order_staus (1001,1002,1003)

group_id Correspondence Order_status (1002,1003)

The second step: processing the user, the user has two driver and passenger, here as a parameter input, 1 means no authentication

BEGIN DECLARE Sqlpattern VARCHAR (255); DECLARE sqltemp VARCHAR (1024); DECLARE sqlExec VARCHAR (1024); DECLARE Sqlcondi VARCHAR (1024); Select pattern into Sqlpattern from T_om_group where group_id=groupid; IF driveruid >= 0 and Passengeruid>=0 Then SET Sqlcondi = CONCAT ("driver_uid=", Driveruid, "and passenger_uid=", passengeruid); ELSEIF driveruid<0 and Passengeruid>=0 Then SET Sqlcondi = CONCAT ("passenger_uid=", Passengeruid); ELSEIF driveruid>=0 and Passengeruid<0 Then SET Sqlcondi = CONCAT ("driver_uid=", Driveruid); ELSE SET Sqlcondi = ""; END IF; IF sqlpattern= ' OR sqlpattern is NULL then IF Sqlcodi = "Then Set sqltemp = "SELECT * from V_order"; ELSE SET sqltemp = CONCAT ("select * from V_order where", Sqlcondi); END IF; ELSE IF Sqlcondi = "Then SET sqltemp = CONCAT ("select * from V_order where", Sqlpattern); ELSE SET sqltemp = CONCAT ("select * from V_order where", Sqlcondi, "and", Sqlpattern); END IF; END IF; SET @sqlExec =sqltemp; Prepare stmt from @sqlExec; EXECUTE stmt; deallocate prepare stmt; END

Step three: Assemble SQL and execute, i.e. dynamic SQL

Fourth step: Call the stored procedure;

@Select ("Call Sp_getgrouporders (#{groupid}, #{driveruid}, #{passengeruid})") @Options (StatementType = statementtype.callable) List<map<string, Object>> getgrouporders (@Param ("GroupId") Integer groupId, @Param ("Driveruid") Integer Driveruid, @Param ("Passengeruid") Integer Passengeruid);

The above completes the first phase of the design, dynamic query data;

Of course, this is not what we ultimately need, because there will be more conditional settings in the actual use of the process, we need to query for a definition;

1, which fields can be queried for conditions

2, whether the business passes the value of the field

3, which expressions are supported by the field

Here we need to refer to the design of mybaits example, but in order to support query security and dynamic SQL, we still need to match the SQL query with the conditions we configured in T_om_group, and then query processing according to the input parameters;

For example: The group validation rule is: Order_status in (${statuslist}) and Start_time>${starttime} and End_time<${endtime}

Then when the SQL is interpreted, only the three parameter definition values are processed, then the business side must pass the three parameters, otherwise the query will not be executed;

If you follow the rules above, then we can also define a table outside of T_om_order while we are working on the query, and we will design this query rule table below:

GROUP_ID: Ref. GROUP_NAME: Name table_name: Table Name fields: Field List where_expr: conditional expression order_expr: Sort expression group_expr: grouping expression

SQL Template: Select ${fields} from ${table_name} where ${where_expr} ORDER by ${order_expr} GROUP by ${group_expr}

Validation parameters: If the where_expr is configured, the query fails by intercepting the dynamic parameters in the where_expr, and the matching succeeds;

The second stage completes, the basic dynamic SQL inquiry is possible;

Third stage: multi-table and nested query, not to be continued

Design of a dynamic SQL query order data