使用Node.js和Oracle数据库创建REST API。 第5部分

第5部分：创建REST API：分页，手动排序和过滤

在上一篇文章中，您已经完成了CRUD API的核心功能的构建。

现在，当在雇员路由上发出HTTP GET请求时，将返回表的所有行。 对于HR.EMPLOYEES表中只有107行，这可能没有多大关系，但可以想象一下，如果该表包含数千或数百万行，将会发生什么。 诸如移动和Web应用程序之类的客户端通常仅显示数据库中可用行的一小部分，然后在必要时选择更多行-可能是当用户向下滚动或单击某些中断控件上的“下一步”按钮时到用户界面中的页面。

为此，REST API必须支持分页工具以返回结果。 一旦支持分页，就需要排序功能，因为通常应在应用分页之前对数据进行排序。 另外，数据过滤工具对于性能非常重要。 如果没有必要，为什么要通过中间层从数据库将数据完全发送到客户端？

我将使用URL查询字符串参数，以便客户端可以指定应如何对结果进行分页，排序和过滤。 与编程一样，实现可能会因您的要求，性能目标等而异。在本文中，我将向您介绍将这些功能添加到API的手动方法。

分页

我将用于分页的查询字符串参数：跳过和限制。 skip参数将用于跳过指定的行数，而limit将限制返回的行数。 如果客户端不提供该值，我将使用默认值30作为限制。

首先更新控制器逻辑以从查询字符串中提取值，然后将其传递给数据库API。 打开controllers / employee.js文件，并将以下代码行添加到解析req.params.id参数的行之后的get函数中。

// *** line that parses req.params.id is here *** context.skip = parseInt(req.query.skip, 10); context.limit = parseInt(req.query.limit, 10); 

现在，您需要更新数据库逻辑以考虑这些值，并相应地更新SQL查询。 在SQL中，offset子句用于跳过行，而fetch子句用于限制查询返回的行数。 与往常一样，值不会直接添加到查询中-而是出于性能和安全性的考虑，它们将作为绑定变量添加。 打开db_apis / employee.js，并在find函数的if块之后添加以下代码，该代码将where子句添加到请求中。

 // *** if block that appends where clause ends here *** if (context.skip) { binds.row_offset = context.skip; query += '\noffset :row_offset rows'; } const limit = (context.limit > 0) ? context.limit : 30; binds.row_limit = limit; query += '\nfetch next :row_limit rows only'; 

这就是您要做的全部分页！ 启动API，然后在另一个终端上运行一些URL命令以对其进行测试。 以下是一些您可以使用的示例：

 # use default limit (30) curl "http://localhost:3000/api/employees" # set limit to 5 curl "http://localhost:3000/api/employees?limit=5" # use default limit and set skip to 5 curl "http://localhost:3000/api/employees?skip=5" # set both skip and limit to 5 curl "http://localhost:3000/api/employees?skip=5&limit=5" 

排序

至少，客户应该能够指定要排序和排序的列（升序或降序）。 最简单的方法是定义一个查询参数（我将使用sort），它允许您传递一个字符串，例如'last_name：asc'或'salary：desc'。 确保从SQL查询返回的结果集的顺序的唯一方法是包括order by子句。 因此，最好定义一个默认的订单定义，以确保当客户未指定订单定义时保持一致。

返回controllers / employee.js并将以下代码行添加到解析req.query.limit参数的行之后的get函数中。

 // *** line that parses req.query.limit is here *** context.sort = req.query.sort; 

然后打开db_apis / employee.js，并在声明和初始化baseQuery的行下方添加以下行。

 // *** lines that initalize baseQuery end here *** const sortableColumns = ['id', 'last_name', 'email', 'hire_date', 'salary']; 

sortableColumns是客户可用于排序的列白名单。 然后，在find函数内部，添加以下if块，从而添加order by子句。 必须在添加where子句之后但在offset和fetch子句之前完成。

 // *** if block that appends where clause ends here *** if (context.sort === undefined) { query += '\norder by last_name asc'; } else { let [column, order] = context.sort.split(':'); if (!sortableColumns.includes(column)) { throw new Error('Invalid "sort" column'); } if (order === undefined) { order = 'asc'; } if (order !== 'asc' && order !== 'desc') { throw new Error('Invalid "sort" order'); } query += `\norder by "${column}" ${order}`; } 

if块的第一部分检查客户端是否通过了排序值。 如果不是，则将默认的order by子句添加到SQL查询中，该查询按last_name升序排序。 如果指定了排序值，则首先将其分为列值和订单值，并在将order by添加到查询之前检查每个值。

现在，您可以运行几个URL命令进行验证。 以下是一些尝试的示例：

 # use default sort (last_name asc) curl "http://localhost:3000/api/employees" # sort by id and use default direction (asc) curl "http://localhost:3000/api/employees?sort=id" # sort by hire_date desc curl "http://localhost:3000/api/employees?sort=hire_date:desc" # use sort with limit and skip together curl "http://localhost:3000/api/employees?limit=5&skip=5&sort=salary:desc" # should throw an error because first_name is not whitelisted curl "http://localhost:3000/api/employees?sort=first_name:desc" # should throw an error because 'other' is not a valid order curl "http://localhost:3000/api/employees?sort=last_name:other" 

最后两个示例应该引发异常，因为它们包含的不是白名单中的值。 它使用标准的Express错误处理程序，因此错误将作为HTML网页返回。

筛选

过滤数据的能力是所有REST API必须提供的重要功能。 与排序一样，实现可能是简单的也可能是复杂的，具体取决于您要支持的内容。 最简单的方法是添加对完全匹配过滤器的支持（例如，last_name = Doe）。 更复杂的实现可以增加对基本运算符（例如，<，>，instr等）和复杂逻辑运算符（例如和/或）的支持，这些逻辑运算符可以将多个过滤器组合在一起。

在本文中，我将尝试简化情况并仅对两列添加过滤器支持：department_id和manager_id。 对于每一列，我将在查询字符串中启用相应的参数。 需要更新将GET请求发送给具有单个员工的端点时添加where子句的数据库逻辑，以适应这些新过滤器。

打开controllers / employee.js，并在解析get函数中req.query.sort值的行下方添加以下行。

 // *** line that parses req.query.sort is here *** context.department_id = parseInt(req.query.department_id, 10); context.manager_id = parseInt(req.query.manager_id, 10); 

然后编辑db_apis / employee.js ，将句子1 = 1添加到基本查询中，如下所示。

 const baseQuery = `select employee_id "id", first_name "first_name", last_name "last_name", email "email", phone_number "phone_number", hire_date "hire_date", job_id "job_id", salary "salary", commission_pct "commission_pct", manager_id "manager_id", department_id "department_id" from employees where 1 = 1`; 

当然，1 = 1将始终为true，因此优化器将简单地忽略它。 但是，此方法将在将来简化附加谓词的添加。

在find函数中，用以下几行替换if块，该if块在传递context.id时添加where子句。

 // *** line that declares 'binds' is here *** if (context.id) { binds.employee_id = context.id; query += '\nand employee_id = :employee_id'; } if (context.department_id) { binds.department_id = context.department_id; query += '\nand department_id = :department_id'; } if (context.manager_id) { binds.manager_id = context.manager_id; query += '\nand manager_id = :manager_id'; } 

如您所见，每个if块仅将传递给binds对象的值添加，然后将相应的谓词添加到where子句。 保存更改并重新启动API。 然后使用以下URL命令进行验证：

 # filter where department_id = 90 (returns 3 employees) curl "http://localhost:3000/api/employees?department_id=90" # filter where manager_id = 100 (returns 14 employees) curl "http://localhost:3000/api/employees?manager_id=100" # filter where department_id = 90 and manager_id = 100 (returns 2 employees) curl "http://localhost:3000/api/employees?department_id=90&manager_id=100" 

就是这样-该API现在支持分页，排序和过滤！ 手动方法提供了很多控制权，但是需要大量代码。 搜索功能现在有58行，仅支持有限的排序和过滤功能。 您可能会考虑使用模块（例如Knex.js查询构建器 ）来简化这些操作。