英文:
How to build a Dynamic SELECT Query in Mule 4 to get data from Snowflake
问题
我有一个需求，需要构建一个动态的SELECT查询，它必须接受任意数量的选择属性、任意表名和任意数量的条件，就像下面的示例查询一样。{} 中的值将是动态的。
如果没有指定 first_name 和 last_name，则默认选择所有字段（select * from）。条件部分也可以为空。
我考虑可以将输入处理成以下方式：
- {first_name, last_name} - 放在 payload 中
- {tableName} - 放在 URI 参数中
- {student_id=12345&status=active} - 放在查询参数中
REST 端点将如下所示（POST 方法），输入 payload 如下：
http://**************/{tableName}?student_id=12345&status=active
输入 payload：
{
param1: first_name,
param2: last_name
}
此外，计划将表名存储在属性文件中，并根据传入的路径或参数将其映射到变量 tableName，以避免安全漏洞。
请问您是否认为这是正确的方法？想要避免任何安全漏洞，并且希望将其打造成更通用的可重用 API。
英文:
I have a requirement to build a dynamic SELECT query which has to take any select number of attributes, any table name and any number of where conditions like a sample query hown below. The values in {} will be dynamic.
select {first_name,last_name} from {tableName} where {student_id=12345 and status='active'}
if first_name,last_name are not present, then it will be all (select * from). Where condition also may be empty.
I am thinking maybe I can handle the inputs to my API as below
{first_name,last_name} - payload
{tableName} - uri param
{student_id=12345&status=active}- query params
rest endpoint will look like this – (POST) with input payload as shown below
http://**************/{tableName}?student_id=12345&status=active
input payload
{
param1:first_name,
param2 : last_name
}
Also planning to add table names in property file and map them to variable tableName based on the incoming path or a parameter to avoid security breaches.
Could you please suggest if this is the right approach? Want to avoid any security breaches and also want to make it more generic API for reusability.
答案1
得分: 1
以下是我对您的要求的建议。理想情况下，您应该遵循RESTful API设计的指南。
- 由于这是一个查询操作，方法应该是GET，并且要检索的字段（列）可以在查询参数中定义。您可以使用**"fields"**参数来限制返回的属性集，只返回您明确指定的属性，应该指定为逗号分隔的列表。例如，要仅返回数据库中所有记录的firstName和lastName字段，请使用：
GET /students?fields=firstName,lastName
- 如果API的URL（端点）复制了表名，就不需要在属性文件中管理表名。但是，请注意遵循端点的命名约定。使用名词而不是动词，并使用复数名词。例如：
GET /students或/customers
- 如果数据库中有主键，它应该被定义为URI参数，其余的过滤条件可以在查询参数**"q"**中定义。此参数可用于指定限制返回的项目集的过滤表达式，基于诸如数值比较或字符串匹配之类的条件。例如：
GET /students/{studentId}?q=status = 'active' and name = 'xyz'
- 最终，数据库中的记录数量可能会增加，响应将变得过于庞大。因此，建议实现分页机制。
英文:
Here are my suggestions for your requirement. Ideally, you should follow the guidelines of RESTful API design
- Since it is a select query, the method should be GET, and the fields (columns) to be retrieved can be defined in the query parameter. You can use the "fields" parameter to restrict the set of properties returned to only those that you explicitly specify, and it should be specified as a comma-separated list. For example, to return only the firstName and lastName fields for all records in the database, use
> GET /students?fields=firstName,lastName
- If the URLs (endpoints) of the API replicate the table names, there is no need to manage the table names in the property files. However, be mindful to follow the naming conventions for the endpoints. Use nouns instead of verbs and use plural nouns. For example
> GET /students or /customers
- If there is any primary key from the database, it should be defined as a URI parameter, and the rest of the filtering conditions can be defined in the query parameter "q". This parameter can be used for specifying a filter expression that restricts the set of items returned, based on criteria such as numeric comparisons or string matching. For example
> GET /students/{studentId}?q=status = 'active' and name = 'xyz'
- Eventually, the number of records in the database may increase, and the response will become too large. Therefore, it is advised to implement the pagination mechanism as well.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论