In many running systems have these kind of requirement, query SQL with different filter conditions, we all know that compose SQL statement by joining string is not a smart way, not only generate duplicated object but also has potential security problem such as SQL injection, a common solution is prepared statement, the same as in Java, node.js also support this mechanism.
Here is the code and example dataset:
We can see it is little more complicated than the previous examples, and make use of nested callback function. Firstly we “prepare” a statement template, a query SQL with a variable filter, depends on the user input. Then do query on the statement with ID parameter, the first parameter “exec” is an array of values which fill the “?” in the prepared statement. The snippet result is:
Please note that the result set is an array, means that may contains multiple rows, let’s change the statement and filter:
We found that the result set is an array of JavaScript objects:
There is an optional parameter of exec function, you can determine the formation of result set, for example if you set the value as:
The above query will return in this format:
Another possible options is used when query SQL contains JOIN operations, here is new table:
And let’s change the code to
The result should be
It’s fine but how can we know the source table of each column? We can do as following:
And the result is:
With the flexible of JSON object we can configure the format of result set.
