| +
"AND p_name LIKE CAST(? AS VARCHAR(55)) " +
"ORDER BY p_partkey " +
"FETCH FIRST 20 ROWS ONLY";
查询 2 看上去也和原始的查询几乎完全相同。我们只是为添加到我们的 UNION ALL 视 图的服务器属性列增加了连接谓词。正如在前面我们已经提到，我们在 Excel、Oracle 和 DB2 数据的昵称上创建 UNION ALL 视图，并且添加一个服务器属性列来指示数据的原始来 源。我们这样做是因为我们的数据分布方式模拟了由三个独立（并在以后合并）的公司维 护的订单记录。从而，我们知道可能有一些客户对不止一个原来的公司下订单，导致重复 的 CUSTOMER 数据，但是我们也知道在相同的日期中，一个特定的客户不会对两个不同的 公司下同样的订单。通过用视图定义维护一个服务器属性列，我们可以确保取回正确的信 息 － 即，我们可以消除对我们的业务情况没有意义的跨地点连接。
以下是查询 2 的实现
query[2] ="SELECT ps_partkey, s_name, s_suppkey, " +
"MIN(ps_supplycost) AS ps_supplycost " +
"FROM fed_partsupp, fed_supplier, fed_nation " +
"WHERE ps_partkey = ? " +
"AND ps_suppkey = s_suppkey " +
"AND s_nationkey = n_nationkey " +
"AND n_name = ? " +
"AND ps_server = s_server " +
"AND s_server = n_server " +
"GROUP BY ps_partkey, s_name, s_suppkey";
跨多个数据源的J2EE开发: 细节探讨(6)
时间:2011-04-11 IBM C. M. Saracco
查询 3、4 和 5 事实上和它们的原始版本没有什么不同。我们只是用参数标记替换了 搜索谓词值，而且对于查询 3，同样也是在视图中的服务器属性列上增加连接谓词：
query[3] = "SELECT c_custkey, c_name, SUM(o_totalprice) AS total_ordered, " +
"COUNT(*) AS num_orders " +
"FROM fed_customer, fed_orders " +
"WHERE o_custkey = c_custkey " +
"AND o_orderdate >= ? " +
"AND o_orderdate < ? " +
"AND c_server = o_server " +
"GROUP BY c_custkey, c_name " +
"ORDER BY total_ordered DESC " +
"FETCH FIRST 10 ROWS ONLY";
query[4] = "SELECT c_custkey, c_name, o_totalprice, n_name " +
"FROM db2_customer, ora_orders, db2_nation " +
"WHERE c_nationkey = n_nationkey " +
"AND c_custkey = o_custkey " +
"AND o_totalprice > ? " +
"AND n_name in (''JAPAN'', ''CHINA'', ''VIETNAM'', ''INDIA'')";
query[5] = "SELECT AVG(o_totalprice) AS avg_order, c_name, c_custkey, " +
"c_acctbal " +
"FROM db2_customer, fed_orders, db2_nation " +
"WHERE c_custkey = o_custkey " +
"AND c_nationkey = n_nationkey " +
"AND n_name = ? " +
"AND c_mktsegment = ? " +
"AND c_acctbal >>= ? " +
"AND c_acctbal <= ? " +
"GROUP BY c_custkey, c_name, c_acctbal " +
"ORDER BY avg_order DESC";
开发使用直接数据访问的查询
当我们直接使用每个数据源时，查询开发任务 |
