java - 从java应用程序动态构建oracle sql查询

Question

如何从 java 应用程序动态构建 oracle pl/sql 查询？用户将看到数据库中不同表中存在的一堆列。用户可以选择任何列集，并且应用程序应仅使用包含所选列的表构建完整的选择查询。例如，假设数据库中有 3 个表。用户选择col11、col22。在这种情况下，应用程序应仅使用 Tabl1 和 Tabl2 构建查询。我如何实现这一目标？

Tabl1
 - col11
 - col12
 - col13

Tabl2
 - fkTbl1
 - col21
 - col22
 - col23

Tabl3
 - col31
 - col32
 - col33
 - fkTbl1

Answer 1

临时报告一直是人们的最爱。它经常出现在“报告要求”部分末尾的一行字中:“用户必须能够定义和运行自己的报告”。唯一的障碍是临时报告本身就是一个应用程序。

你说

"The user will be presented with a bunch of columns that are present in different tables in the database."

如果预先选择并严格控制“列束”(以及表的分布)，您可以避免我在下面讨论的一些复杂性。遗憾的是，根据临时报告的本质，用户几乎需要所有表中的所有列。

让我们从您的示例开始。用户已选择 col11 和 col22，因此您需要生成以下查询:

SELECT tabl1.col11
       , tabl2.col22
FROM tabl1 JOIN tabl2 
     ON  (TABL1.ID = TABL2.FKTABL1)
/

这并不太难。您只需导航数据字典 View USER_CONSTRAINTS 和 USER_CONS_COLUMNS 即可在连接条件中建立列 - 前提是您已经定义了外键(请有外键!)。

如果我们添加第四个表，事情会变得更加复杂:

Tabl4
 - col41
 - col42
 - col43
 - fkTbl2

现在，当用户选择 col11 和 col42 时，您需要导航数据字典以确定 Tabl2 充当要连接的中间表Tabl4 和 Tabl1 (假设您没有使用复合主键，正如大多数人不使用的那样)。但假设用户选择 col31 和 col41。这是一个合法的组合吗？就说是吧。现在您必须将 Tabl4 加入到 Tabl2 到 Tabl1 到 Tabl3。嗯...

如果用户从两个完全不相关的表 - Tabl1 和 Tabl23 中选择列会怎样？您是否盲目地生成CROSS JOIN还是抛出异常？选择权在你。

回到第一个查询，它将返回两个表中的所有行。几乎可以肯定，您的用户会想要限制结果集的选项。因此，您需要为他们提供将过滤器添加到 WHERE 子句的能力。这里的陷阱包括:

• 确保提供的值具有适当的数据类型(数字没有字符串，日期没有数字)
• 提供引用数据的查找值(value)观
• 处理多个值(IN 列表而不是等于)
• 确保日期范围合理(先开界再闭界)
• 处理自由文本搜索(您是会允许吗？你需要吗使用 TEXT 索引还是运行用户执行LIKE的风险'%whatever%' 针对某些 CLOB专栏？)

最后一点强调了临时报告固有的一个风险:如果用户可以使用任何过滤器从任何表中组合查询，那么他们组合的查询可能会耗尽系统中的所有资源。所以申请profiles是个好主意。以防止这种情况发生。另外，正如我已经提到的，用户可能会构建无意义的查询。请记住，您的架构中不需要太多表来生成太多排列来测试。

最后还有一个棘手的安全策略提议。如果用户只能根据其部门或工作角色查看数据子集，那么您将需要复制这些规则。在这种情况下，通过 Row Level Security 自动应用策略是一个真正的福音

所有这些可能会让您得出这样的结论:最好的解决方案是说服用户购买现成的产品。尽管这种方法并非没有问题。