Dynamic SQL statement: definition (i)

Article series

1. Dynamic SQL statement: definition (i)

static SQL vs. dynamic SQL

• Static SQL: SQL that has a fixed form and structure before the program runs.
• Dynamic SQL: SQL that can dynamically change form or structure while the program is running.

Some thoughts and thoughts

In a real project, it is often difficult to implement complex business logic with a static SQL statement, and SQL statements are generated dynamically from the programming language. However, there are a number of drawbacks to creating SQL statements through conditional-judging statements in code, which are briefly listed as follows:

1. Code is messy and repetitive, coding is a lot of work, and it's easy to generate errors.

2. SQL injection can easily be caused by stitching SQL statements in the process.

3. The code is mixed with SQL statements, the code structure is not clear, and the SQL statement is not readable.

In view of the above problems, we hope to achieve the following goals:

1. Provides a unified set of markup languages and writes dynamic SQL statements.

2. Provides a code parser to parse dynamic SQL statements to generate real-world SQL.

3. Avoid splicing problems in the SQL process, such as SQL injection, redundant "and" or "or" or "," and so on.

This article first defines the dynamic SQL syntax to be implemented and the necessary conditional judgment functions, in the following article, will gradually give a concrete implementation of the program, the final code will be published to Github:https://github.com/lotusun/robin.git. Through this practice, I hope to improve our understanding of compiling principle, lexical analyzer, parser, finite state machine and scriptengine.

Dynamic SQL Syntax

2. Conditional statement (IF)

   usage : if ( condition ) { body }, or if ( condition ) { body } else { body }

   description : Condition supports JavaScript syntax.

4. SELECT statement (choose)

   usage : choose{when (condition) {body} else { body }}

   description : Condition supports JavaScript syntax; When clauses can exist multiple;

6. struct statement (where)

   usage : where{ Body }

   description : Used in the where statement to remove the excess "and" or "or" before and after the body.

8. Structure Statement (SET)

   usage : set{ Body }

   Description : For the UPDATE statement, can remove the body before and after the superfluous ",".

10. struct statement (value)

    usage : value{ Body }

    description : For INSERT statements, the ability to generate INSERT statements.

12. Argument Statement (#)

    usage : #{name}

    Description: Ability to replace parameters with "?" to avoid SQL injection.

14. Argument statement ($)

    usage : ${name}

    Description: The ability to replace parameters with strings, there is SQL injection.

16. Parameter Statement (@)

    usage : @{name}

    Description : Marks the return value of a stored procedure.

18. Parameter statement (in)

    usage : in{name}

    description : Used in the in statement to replace the list parameter with more than one "?".

20. Paging Statement (page)

    Usage : page (A, b) { Body } order { column }

    Description : The ability to animate a page statement, where a is the number of rows per page and B is the current page;

Conditional Judgment Function

2. IsNull ()

   Description : The value of the test object, or True if NULL, otherwise false.

4. Isnotnull ()

   Description : The value of the test object, functionally opposite to IsNull ().

6. Isallnull ()

   Description : Tests the values of multiple objects, False if an object exists so that Isnotnull () is true, otherwise true.

8. Isanynull ()

   Description : Tests the value of multiple objects, or False if there is an object that causes IsNull () to be true.

10. IsEmpty ()

    Description : The value of the test object, true if null or an empty string or array length of 0, otherwise false.

12. Isnotempty ()

    Description : The value of the test object, functionally opposite to IsEmpty ().

14. Isallempty ()

    Description : Tests the values of multiple objects, False if an object exists so that Isnotempty () is true, otherwise true.

16. Isanyempty ()

    Description : Tests the value of multiple objects, or False if there is an object that causes IsEmpty () to be true.

18. IsBlank ()

    Description : The value of the test object, if null or an empty string or white space character (\ t, \ n, \ r, and so on) is true, otherwise false.

20. Isnotblank ()

    Description : The value of the test object, functionally opposite to isblanl ().

22. Isallblank ()

    Description : Tests the values of multiple objects, False if an object exists so that Isnotblank () is true, otherwise true.

24. Isanyblank ()

    Description : Tests the value of multiple objects, or False if there is an object that causes Isblank () to be true.

Example

• SELECT statement

  1 Select  from User where {3     if (Isnotempty (name)) {5         = #{name} 7     }9 }

• UPDATE statement

    1  update  user  set  {  3  if   (Isnotempty (name)) {  5  name =   #{name},   7  Span style= "COLOR: #000000" > "  9  if  Span style= "COLOR: #000000" > (isnotempty (sex)) { 11  sex =   #{sex},  13   " 15 } 

• Insert statement

  1 Insert User Values {2     = = = #{name} 3     if (Isnotempty (Sex)) {4         = #{sex} 5     }6 }

• Delete statement

  1 Delete  from User where {2     if (Isnotempty (id)) {3          = #{id} 4     } Else  5         1=26    }7 }

Dynamic SQL statement: definition (i)