Access数据库中查询包含指定全部物料的配方ID问题

问题分析 #

原代码的核心问题：

• 冗余子查询：第一个方法中SELECT itemId FROM Item WHERE itemId IN (...)完全多余，ItemInRecipe的itemId本身关联Item表，直接过滤ItemInRecipe即可
• 字符串拼接风险：直接拼接SQL会导致itemName含单引号时出现语法错误，同时存在SQL注入漏洞
• 统计逻辑漏洞：未考虑同一配方重复添加同一物料的情况，COUNT(itemId)会误判物料数量

修正后的实现 #

1. 按ItemId查询 #

使用参数化查询优化逻辑，避免注入和语法错误：

public static List<int> RecipesWithAllItems(List<int> itemIdList)
{
    List<int> recipeIdList = new List<int>();
    if (itemIdList.Count == 0) return recipeIdList;

    // 构建参数占位符
    string paramPlaceholders = string.Join(", ", itemIdList.Select((_, idx) => $"@ItemId{idx}"));
    string query = $@"
        SELECT recipeId 
        FROM ItemInRecipe 
        WHERE itemId IN ({paramPlaceholders})
        GROUP BY recipeId 
        HAVING COUNT(DISTINCT itemId) = {itemIdList.Count}";

    using (OleDbConnection con = Dbf.GenerateConnection())
    using (OleDbCommand cmd = new OleDbCommand(query, con))
    {
        // 绑定参数
        for (int i = 0; i < itemIdList.Count; i++)
        {
            cmd.Parameters.AddWithValue($"@ItemId{i}", itemIdList[i]);
        }

        con.Open();
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                recipeIdList.Add(reader.GetInt32(0));
            }
        }
    }

    return recipeIdList;
}

2. 按ItemName查询 #

通过关联表查询，同样使用参数化避免风险：

public static List<int> GetTheRecipesIdsWithAllItems(List<string> itemNameList)
{
    List<int> recipeIdList = new List<int>();
    if (itemNameList.Count == 0) return recipeIdList;

    // 构建名称参数占位符
    string nameParamPlaceholders = string.Join(", ", itemNameList.Select((_, idx) => $"@ItemName{idx}"));
    string query = $@"
        SELECT ir.recipeId 
        FROM ItemInRecipe ir
        INNER JOIN Item i ON ir.itemId = i.itemId
        WHERE i.itemName IN ({nameParamPlaceholders})
        GROUP BY ir.recipeId 
        HAVING COUNT(DISTINCT ir.itemId) = {itemNameList.Count}";

    using (OleDbConnection con = Dbf.GenerateConnection())
    using (OleDbCommand cmd = new OleDbCommand(query, con))
    {
        // 绑定参数
        for (int i = 0; i < itemNameList.Count; i++)
        {
            cmd.Parameters.AddWithValue($"@ItemName{i}", itemNameList[i]);
        }

        con.Open();
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                recipeIdList.Add(reader.GetInt32(0));
            }
        }
    }

    return recipeIdList;
}

关键优化点 #

• 参数化查询：彻底避免SQL注入，同时解决特殊字符导致的语法错误
• COUNT(DISTINCT itemId)：确保同一配方重复添加同一物料时，仍能正确统计唯一物料数量
• 资源自动释放：使用using语句自动管理数据库连接、命令等资源，避免内存泄漏
• 简化逻辑：去掉冗余子查询，提升查询效率

测试验证 #

针对示例数据，当传入itemIdList = new List<int>{6}时：

• SQL筛选出ItemInRecipe中itemId=6的行（recipeId1和2）
• GROUP BY后每个recipeId的COUNT(DISTINCT itemId)均为1，与列表长度一致，返回[1,2]，符合预期。

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