基于Node、Express、MySQL的REST API如何通过URL限制返回记录数

Hey there! Let's get that maxRecords functionality up and running for your Node/Express/MySQL API. Here's a straightforward breakdown of the changes you need to make:

Step 1: Update the Route Handler (routes/Nodes.js) #

First, we'll modify the route to read the maxRecords query parameter from the request. If it's missing or invalid, we'll default to 25. Then we'll pass this value to your model's getAllNodes function.

Here's the revised route code:

var express = require('express');
var router = express.Router();
var Node=require('../models/Node');

router.get('/:id?',function(req,res,next){
 if(req.params.id){
 Node.getNodeById(req.params.id,function(err,rows){
 if(err) {
 res.json(err);
 } else{
 res.json(rows);
 }
 });
 } else{
 // Get maxRecords from query, default to 25 if missing/invalid
 const maxRecords = parseInt(req.query.maxRecords) || 25;
 Node.getAllNodes(maxRecords,function(err,rows){
 if(err) {
 res.json(err);
 } else {
 res.json(rows);
 }
 });
 }
});

module.exports=router;

Step 2: Update the Model (models/Node.js) #

Next, we'll adjust the getAllNodes method to accept the maxRecords parameter and use it in a LIMIT clause in your SQL query. Using a parameterized query here keeps your code safe from SQL injection.

Revised model code:

var db=require('../dbconnection');

var Node={
 getAllNodes:function(maxRecords, callback){
 // Use parameterized LIMIT to avoid SQL injection risks
 return db.query("Select * from node_link LIMIT ?", [maxRecords], callback);
 },
 getNodeById:function(id,callback){
 return db.query("select * from node_link where id=?",[id],callback);
 }
}

module.exports=Node;

How It Works #

• When you call http://localhost:3000/Node without any query parameters, it will automatically return the first 25 records.
• If you pass ?maxRecords=50 (or any valid number), it will return that many records instead.
• The parseInt() ensures we're working with a numeric value, and the || 25 falls back to the default if the parameter is missing, non-numeric, or zero.

Quick Extra Tip #

For added robustness, you might want to add a check to cap maxRecords at a reasonable number (like 1000) to prevent performance issues with overly large queries. For example:

const maxRecords = Math.min(parseInt(req.query.maxRecords) || 25, 1000);

内容的提问来源于stack exchange，提问作者Silvio Custodio