如何用Dapper+Oracle19c在.NET Core Web API处理JSON POST请求并返回薪资

问题描述 #

我正在用Dapper和Oracle 19c构建.NET Core Web API，这个API要接收指定格式的POST请求，从Employees表返回对应员工的Salary字段值。需要遍历请求里的JSON数据，根据姓名、ID及雇佣年份匹配表中的数据，返回对应薪资。

我对Oracle的JSON操作不太熟，试了用JSON_TABLE但报错：column ambiguously defined（第2行第8列），也搞不清最佳实现方式，Oracle官方文档看得头疼。API开发我也刚入门，但用Dapper做简单的GET请求返回员工JSON信息倒是没问题。

请求示例 #

{
    "Employees": [
        {
        "EMPLOYEE_ID": "100",
        "FIRST_NAME":"Steven",
        "LAST_NAME": "King",
        "HIRE_DATE": "17-JUN-03"
        },
        {
        "EMPLOYEE_ID": "101",
        "FIRST_NAME":"Neena",
        "LAST_NAME": "Kochar",
        "HIRE_DATE": "21-SEP-05"
        },
        {
        "EMPLOYEE_ID": "104",
        "FIRST_NAME":"Bruce",
        "LAST_NAME": "Ernst",
        "HIRE_DATE": "21-MAY-07"
        }
    ]
}

响应示例 #

{
    "Employees": [
        {
        "SALARY": "100000",
        "STATUS":"SUCCESS"      
        },
        {
        "SALARY": "100000",
        "STATUS":"SUCCESS"      
        },
        {
        "SALARY": "100000",
        "STATUS":"SUCCESS"      
        }
    ]
}

尝试的SQL语句 #

SELECT *
FROM EMPLOYEES e
JOIN EMPLOYEES e ON e.EMPLOYEE_ID IN(
    SELECT jt.* FROM JSON_TABLE(
    '{
    "Payees": [
        {
        "EMPLOYEE_ID": "100",
        "FIRST_NAME":"Steven",
        "LAST_NAME": "King",
        "HIRE_DATE": "17-JUN-03"
        
        }
    ]
},
'COLUMNS(EMPLOYEE_ID VARCHAR2(20) PATH '$.EMPLOYEE_ID')) AS jt  
);

解决方案 #

1. 修正SQL的错误 #

你这段SQL有两个明显问题：

• 重复给EMPLOYEES表起别名e，导致数据库分不清引用的是哪个表的列，这就是column ambiguously defined报错的核心原因；
• JSON_TABLE用法错误：嵌套了没必要的子查询，还把请求里的Employees数组写成了Payees，完全不匹配请求结构。

正确的SQL应该用绑定变量接收传入的JSON，直接解析数组并关联Employees表：

SELECT 
    e.SALARY,
    'SUCCESS' AS STATUS
FROM 
    EMPLOYEES e
JOIN 
    JSON_TABLE(
        :json_data,
        '$.Employees[*]' -- 遍历请求里的Employees数组每一项
        COLUMNS(
            EMPLOYEE_ID VARCHAR2(20) PATH '$.EMPLOYEE_ID',
            FIRST_NAME VARCHAR2(50) PATH '$.FIRST_NAME',
            LAST_NAME VARCHAR2(50) PATH '$.LAST_NAME',
            HIRE_DATE_STR VARCHAR2(20) PATH '$.HIRE_DATE'
        ) jt
ON 
    e.EMPLOYEE_ID = jt.EMPLOYEE_ID
    AND e.FIRST_NAME = jt.FIRST_NAME
    AND e.LAST_NAME = jt.LAST_NAME
    -- 匹配雇佣年份：把请求的日期字符串转成Oracle日期后提取年份
    AND EXTRACT(YEAR FROM TO_DATE(jt.HIRE_DATE_STR, 'DD-MON-RR')) = EXTRACT(YEAR FROM e.HIRE_DATE)

2. 结合Dapper实现API逻辑 #

第一步：定义对应请求响应的实体类

先创建和JSON结构匹配的C#类：

// 请求中的单个员工信息
public class RequestEmployee
{
    public string EMPLOYEE_ID { get; set; }
    public string FIRST_NAME { get; set; }
    public string LAST_NAME { get; set; }
    public string HIRE_DATE { get; set; }
}

// 完整请求体
public class EmployeeRequest
{
    public List<RequestEmployee> Employees { get; set; }
}

// 响应中的单个员工薪资结果
public class ResponseEmployee
{
    public string SALARY { get; set; }
    public string STATUS { get; set; }
}

// 完整响应体
public class EmployeeResponse
{
    public List<ResponseEmployee> Employees { get; set; }
}

第二步：编写API接口和Dapper代码

在Controller里实现POST接口，用Dapper执行上面的SQL：

[HttpPost("get-salaries")]
public async Task<IActionResult> GetSalaries([FromBody] EmployeeRequest request)
{
    // 将请求对象序列化为JSON字符串，传给Oracle
    var jsonData = JsonSerializer.Serialize(request);

    using (var connection = new OracleConnection("你的Oracle连接字符串"))
    {
        await connection.OpenAsync();
        
        // 执行SQL，传入绑定变量json_data
        var salaryResults = await connection.QueryAsync<ResponseEmployee>(@"
            SELECT 
                e.SALARY,
                'SUCCESS' AS STATUS
            FROM 
                EMPLOYEES e
            JOIN 
                JSON_TABLE(
                    :json_data,
                    '$.Employees[*]'
                    COLUMNS(
                        EMPLOYEE_ID VARCHAR2(20) PATH '$.EMPLOYEE_ID',
                        FIRST_NAME VARCHAR2(50) PATH '$.FIRST_NAME',
                        LAST_NAME VARCHAR2(50) PATH '$.LAST_NAME',
                        HIRE_DATE_STR VARCHAR2(20) PATH '$.HIRE_DATE'
                    ) jt
            ON 
                e.EMPLOYEE_ID = jt.EMPLOYEE_ID
                AND e.FIRST_NAME = jt.FIRST_NAME
                AND e.LAST_NAME = jt.LAST_NAME
                AND EXTRACT(YEAR FROM TO_DATE(jt.HIRE_DATE_STR, 'DD-MON-RR')) = EXTRACT(YEAR FROM e.HIRE_DATE)",
            new { json_data = jsonData });

        // 构造响应返回
        var response = new EmployeeResponse
        {
            Employees = salaryResults.ToList()
        };

        return Ok(response);
    }
}

3. 补充优化点 #

• 如果有员工匹配不到，可改用LEFT JOIN，同时把STATUS设为NOT_FOUND：

  SELECT 
      NVL(e.SALARY, 'N/A') AS SALARY,
      CASE WHEN e.EMPLOYEE_ID IS NOT NULL THEN 'SUCCESS' ELSE 'NOT_FOUND' END AS STATUS
  FROM 
      JSON_TABLE(:json_data, '$.Employees[*]' COLUMNS(...)) jt
  LEFT JOIN 
      EMPLOYEES e
  ON 
      -- 原匹配条件不变
  

• 确保请求的HIRE_DATE格式和TO_DATE的参数一致，比如如果是YYYY-MM-DD，就把'DD-MON-RR'改成'YYYY-MM-DD'；
• 连接字符串建议放在appsettings.json里，通过配置注入获取，不要硬编码。

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