Using JoinRowSet Objects使用JoinRowSet对象

A JoinRowSet implementation lets you create a SQL JOIN between RowSet objects when they are not connected to a data source. JoinRowSet实现允许您在未连接到数据源的RowSet对象之间创建SQLJOIN。This is important because it saves the overhead of having to create one or more connections.这很重要，因为它节省了创建一个或多个连接的开销。

The following topics are covered:涵盖以下主题：

• Creating JoinRowSet Objects创建JoinRowSet对象
• Adding RowSet Objects添加行集对象
• Managing Match Columns管理匹配列

The JoinRowSet interface is a subinterface of the CachedRowSet interface and thereby inherits the capabilities of a CachedRowSet object. JoinRowSet接口是CachedRowSet接口的子接口，因此继承CachedRowSet对象的功能。This means that a JoinRowSet object is a disconnected RowSet object and can operate without always being connected to a data source.这意味着JoinRowSet对象是断开连接的RowSet对象，可以在不始终连接到数据源的情况下运行。

Creating JoinRowSet Objects创建JoinRowSet对象

A JoinRowSet object serves as the holder of a SQL JOIN. JoinRowSet对象充当SQL联接的持有者。The following example from JoinSample shows to create a JoinRowSet object:JoinSample中的以下示例显示如何创建JoinRowSet对象：

RowSetFactory factory = RowSetProvider.newFactory();  
    try (CachedRowSet coffees = factory.createCachedRowSet();
         CachedRowSet suppliers = factory.createCachedRowSet();
         JoinRowSet jrs = factory.createJoinRowSet()) {
      coffees.setCommand("SELECT * FROM COFFEES");
      // Set connection parameters for the CachedRowSet coffees
      coffees.execute();
      
      suppliers.setCommand("SELECT * FROM SUPPLIERS");
      // Set connection parameters for the CachedRowSet suppliers      suppliers.execute();      

      jrs.addRowSet(coffees, "SUP_ID");
      jrs.addRowSet(suppliers, "SUP_ID");

      // ...

The variable jrs holds nothing until RowSet objects are added to it.变量jrs在添加RowSet对象之前不保存任何内容。

Adding RowSet Objects添加行集对象

Any RowSet object can be added to a JoinRowSet object as long as it can be part of a SQL JOIN. A JdbcRowSet object, which is always connected to its data source, can be added, but typically it forms part of a JOIN by operating with the data source directly instead of becoming part of a JOIN by being added to a JoinRowSet object. The point of providing a JoinRowSet implementation is to make it possible for disconnected RowSet objects to become part of a JOIN relationship.

The owner of The Coffee Break chain of coffee houses wants to get a list of the coffees he buys from Acme, Inc. 咖啡屋咖啡休息连锁店的老板想得到一份他从Acme，Inc.购买的咖啡的清单。In order to do this, the owner will have to get information from two tables, COFFEES and SUPPLIERS. 为了做到这一点，店主必须从COFFEES和SUPPLIERS这两张表上获取信息。In the database world before RowSet technology, programmers would send the following query to the database:在RowSet技术之前的数据库世界中，程序员会向数据库发送以下查询：

String query =
    "SELECT COFFEES.COF_NAME " +
    "FROM COFFEES, SUPPLIERS " +
    "WHERE SUPPLIERS.SUP_NAME = Acme.Inc. " +
    "and " +
    "SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

In the world of RowSet technology, you can accomplish the same result without having to send a query to the data source. You can add RowSet objects containing the data in the two tables to a JoinRowSet object. Then, because all the pertinent data is in the JoinRowSet object, you can perform a query on it to get the desired data.

The following code fragment from JoinSample.testJoinRowSet creates two CachedRowSet objects, coffees populated with the data from the table COFFEES, and suppliers populated with the data from the table SUPPLIERS. The coffees and suppliers objects have to make a connection to the database to execute their commands and get populated with data, but after that is done, they do not have to reconnect again in order to form a JOIN.

try (CachedRowSet coffees = factory.createCachedRowSet();
         CachedRowSet suppliers = factory.createCachedRowSet();
         JoinRowSet jrs = factory.createJoinRowSet()) {
      coffees.setCommand("SELECT * FROM COFFEES");
      coffees.setUsername(settings.userName);
      coffees.setPassword(settings.password);
      coffees.setUrl(settings.urlString);
      coffees.execute();
      
      suppliers.setCommand("SELECT * FROM SUPPLIERS");
      suppliers.setUsername(settings.userName);
      suppliers.setPassword(settings.password);
      suppliers.setUrl(settings.urlString);
      suppliers.execute();  
	  // ...

Managing Match Columns管理匹配列

Looking at the SUPPLIERS table, you can see that Acme, Inc. has an identification number of 101. 查看SUPPLIERS表，您可以看到Acme，Inc.的标识号为101。The coffees in the table COFFEES with the supplier identification number of 101 are Colombian and Colombian_Decaf. The joining of information from both tables is possible because the two tables have the column SUP_ID in common. In JDBC RowSet technology, SUP_ID, the column on which the JOIN is based, is called the match column.

Each RowSet object added to a JoinRowSet object must have a match column, the column on which the JOIN is based. There are two ways to set the match column for a RowSet object. The first way is to pass the match column to the JoinRowSet method addRowSet, as shown in the following line of code:

jrs.addRowSet(coffees, "SUP_ID");

This line of code adds the coffees CachedRowSet to the jrs object and sets the SUP_ID column of coffees as the match column.

At this point, jrs has only coffees in it. The next RowSet object added to jrs will have to be able to form a JOIN with coffees, which is true of suppliers because both tables have the column SUP_ID. The following line of code adds suppliers to jrs and sets the column SUP_ID as the match column.

jrs.addRowSet(suppliers, "SUP_ID");

Now jrs contains a JOIN between coffees and suppliers from which the owner can get the names of the coffees supplied by Acme, Inc. Because the code did not set the type of JOIN, jrs holds an inner JOIN, which is the default. In other words, a row in jrs combines a row in coffees and a row in suppliers. It holds the columns in coffees plus the columns in suppliers for rows in which the value in the COFFEES.SUP_ID column matches the value in SUPPLIERS.SUP_ID. The following code prints out the names of coffees supplied by Acme, Inc., where the String supplierName is equal to Acme, Inc. Note that this is possible because the column SUP_NAME, which is from suppliers, and COF_NAME, which is from coffees, are now both included in the JoinRowSet object jrs.

System.out.println("Coffees bought from " + supplierName + ": ");
      while (jrs.next()) {
        if (jrs.getString("SUP_NAME").equals(supplierName)) { 
          String coffeeName = jrs.getString(1);
          System.out.println("     " + coffeeName);
        }
      }

This will produce output similar to the following:这将产生与以下类似的输出：

Coffees bought from Acme, Inc.:
     Colombian
     Colombian_Decaf

The JoinRowSet interface provides constants for setting the type of JOIN that will be formed, but currently the only type that is implemented is JoinRowSet.INNER_JOIN.JoinRowSet接口提供常量，用于设置将形成的JOIN类型，但当前唯一实现的类型是JoinRowSet.INNER_JOIN。