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:

/wp-content/uploads/2015/11/24_850659.png

/wp-content/uploads/2015/11/25_850660.png

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:

/wp-content/uploads/2015/11/26_850661.png

Please note that the result set is an array, means that may contains multiple rows, let’s change the statement and filter:

/wp-content/uploads/2015/11/27_850662.png

We found that the result set is an array of JavaScript objects:

/wp-content/uploads/2015/11/28_850663.png




There is an optional parameter of exec function, you can determine the formation of result set, for example if you set the value as:

/wp-content/uploads/2015/11/29_850664.png

The above query will return in this format:

/wp-content/uploads/2015/11/30_850665.png

Another possible options is used when query SQL contains JOIN operations, here is new table:

/wp-content/uploads/2015/11/31_850666.png

And let’s change the code to

/wp-content/uploads/2015/11/32_850667.png

The result should be

/wp-content/uploads/2015/11/33_850668.png

It’s fine but how can we know the source table of each column? We can do as following:

/wp-content/uploads/2015/11/34_850669.png

And the result is:

/wp-content/uploads/2015/11/35_850670.png

With the flexible of JSON object we can configure the format of result set.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply