[Feat]: The dynamic SQL statement support

[hellomyheart]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like A clear and concise description of what you want to happen.

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Additional context Add any other context or screenshots about the feature request here.

The dynamic SQL statement support. The current SQL only supports inserting variables, but many queries are very complex. If the data volume is small, the SELECT method can be used, along with the filtering function of the table component. However, when dealing with a large amount of data, SELECT is obviously not feasible. In this case, manual SQL writing is required for data filtering. In the current SQL, if a variable {{a}} is injected, this line of code cannot be reduced. If there are three optional query parameters, in order to ensure that the SQL does not throw an error, it is necessary to manually write 8 SQL statements, which is obviously too complex and not maintainable. If the SQL supports dynamic conditional judgment like MyBatis, this problem can be solved.

Using something like:

  SELECT * FROM a
WHERE
      <if test="corpName != null">
        corp_name = {{corp_name}}
      </if>

This method of generating SQL can meet this requirement. Of course, it would be even better if it supports advanced syntax such as looping, choosing, and setting as in MyBatis.

This is the first solution, using SQL templates similar to MyBatis. Another solution is to enhance the capability of JavaScript and provide a JavaScript API to query the database, so that the dynamic nature of JavaScript code can solve this problem.

These two methods are not mutually exclusive and can coexist. Of course, if there is a third method that can solve the problem of dynamic SQL, that would also be acceptable.

[FalkWolsky]

We agree. That would be a truly helpful feature! We think to via XML, but via JavaScript / JSON Notation. As it goes deep into the mechanics of the Backend, this is not an easy task. But an important one. We try to get this done for v2.5. But it will take us time.