Interface JoinRowSet
- All Superinterfaces:
-
AutoCloseable
,CachedRowSet
,Joinable
,ResultSet
,RowSet
,WebRowSet
,Wrapper
public interface JoinRowSet extends WebRowSet
The JoinRowSet
interface provides a mechanism for combining related data from different RowSet
objects into one JoinRowSet
object, which represents an SQL JOIN
. In other words, a JoinRowSet
object acts as a container for the data from RowSet
objects that form an SQL JOIN
relationship.
The Joinable
interface provides the methods for setting, retrieving, and unsetting a match column, the basis for establishing an SQL JOIN
relationship. The match column may alternatively be set by supplying it to the appropriate version of the JointRowSet
method addRowSet
.
1.0 Overview
DisconnectedRowSet
objects (CachedRowSet
objects and implementations extending the CachedRowSet
interface) do not have a standard way to establish an SQL JOIN
between RowSet
objects without the expensive operation of reconnecting to the data source. The JoinRowSet
interface is specifically designed to address this need. Any RowSet
object can be added to a JoinRowSet
object to become part of an SQL JOIN
relationship. This means that both connected and disconnected RowSet
objects can be part of a JOIN
. RowSet
objects operating in a connected environment (JdbcRowSet
objects) are encouraged to use the database to which they are already connected to establish SQL JOIN
relationships between tables directly. However, it is possible for a JdbcRowSet
object to be added to a JoinRowSet
object if necessary.
Any number of RowSet
objects can be added to an instance of JoinRowSet
provided that they can be related in an SQL JOIN
. By definition, the SQL JOIN
statement is used to combine the data contained in two or more relational database tables based upon a common attribute. The Joinable
interface provides the methods for establishing a common attribute, which is done by setting a match column. The match column commonly coincides with the primary key, but there is no requirement that the match column be the same as the primary key. By establishing and then enforcing column matches, a JoinRowSet
object establishes JOIN
relationships between RowSet
objects without the assistance of an available relational database.
The type of JOIN
to be established is determined by setting one of the JoinRowSet
constants using the method setJoinType
. The following SQL JOIN
types can be set:
-
CROSS_JOIN
-
FULL_JOIN
-
INNER_JOIN
- the default if noJOIN
type has been set -
LEFT_OUTER_JOIN
-
RIGHT_OUTER_JOIN
JOIN
will automatically be an inner join. The comments for the fields in the JoinRowSet
interface explain these JOIN
types, which are standard SQL JOIN
types. 2.0 Using a JoinRowSet
Object for Creating a JOIN
When a JoinRowSet
object is created, it is empty. The first RowSet
object to be added becomes the basis for the JOIN
relationship. Applications must determine which column in each of the RowSet
objects to be added to the JoinRowSet
object should be the match column. All of the RowSet
objects must contain a match column, and the values in each match column must be ones that can be compared to values in the other match columns. The columns do not have to have the same name, though they often do, and they do not have to store the exact same data type as long as the data types can be compared. A match column can be set in two ways:
- By calling the
Joinable
methodsetMatchColumn
This is the only method that can set the match column before aRowSet
object is added to aJoinRowSet
object. TheRowSet
object must have implemented theJoinable
interface in order to use the methodsetMatchColumn
. Once the match column value has been set, this method can be used to reset the match column at any time. - By calling one of the versions of the
JoinRowSet
methodaddRowSet
that takes a column name or number (or an array of column names or numbers)
Four of the fiveaddRowSet
methods take a match column as a parameter. These four methods set or reset the match column at the time aRowSet
object is being added to aJoinRowSet
object.
3.0 Sample Usage
The following code fragment adds two CachedRowSet
objects to a JoinRowSet
object. Note that in this example, no SQL JOIN
type is set, so the default JOIN
type, which is INNER_JOIN, is established.
In the following code fragment, the table EMPLOYEES
, whose match column is set to the first column (EMP_ID
), is added to the JoinRowSet
object jrs. Then the table ESSP_BONUS_PLAN
, whose match column is likewise the EMP_ID
column, is added. When this second table is added to jrs, only the rows in ESSP_BONUS_PLAN
whose EMP_ID
value matches an EMP_ID
value in the EMPLOYEES
table are added. In this case, everyone in the bonus plan is an employee, so all of the rows in the table ESSP_BONUS_PLAN
are added to the JoinRowSet
object. In this example, both CachedRowSet
objects being added have implemented the Joinable
interface and can therefore call the Joinable
method setMatchColumn
.
JoinRowSet jrs = new JoinRowSetImpl(); ResultSet rs1 = stmt.executeQuery("SELECT * FROM EMPLOYEES"); CachedRowSet empl = new CachedRowSetImpl(); empl.populate(rs1); empl.setMatchColumn(1); jrs.addRowSet(empl); ResultSet rs2 = stmt.executeQuery("SELECT * FROM ESSP_BONUS_PLAN"); CachedRowSet bonus = new CachedRowSetImpl(); bonus.populate(rs2); bonus.setMatchColumn(1); // EMP_ID is the first column jrs.addRowSet(bonus);
At this point, jrs is an inside JOIN of the two RowSet
objects based on their EMP_ID
columns. The application can now browse the combined data as if it were browsing one single RowSet
object. Because jrs is itself a RowSet
object, an application can navigate or modify it using RowSet
methods.
jrs.first(); int employeeID = jrs.getInt(1); String employeeName = jrs.getString(2);
Note that because the SQL JOIN
must be enforced when an application adds a second or subsequent RowSet
object, there may be an initial degradation in performance while the JOIN
is being performed.
The following code fragment adds an additional CachedRowSet
object. In this case, the match column (EMP_ID
) is set when the CachedRowSet
object is added to the JoinRowSet
object.
ResultSet rs3 = stmt.executeQuery("SELECT * FROM 401K_CONTRIB"); CachedRowSet fourO1k = new CachedRowSetImpl(); four01k.populate(rs3); jrs.addRowSet(four01k, 1);
The JoinRowSet
object jrs now contains values from all three tables. The data in each row in four01k in which the value for the EMP_ID
column matches a value for the EMP_ID
column in jrs has been added to jrs.
4.0 JoinRowSet
Methods
The JoinRowSet
interface supplies several methods for adding RowSet
objects and for getting information about the JoinRowSet
object. - Methods for adding one or more
RowSet
objects
These methods allow an application to add oneRowSet
object at a time or to add multipleRowSet
objects at one time. In either case, the methods may specify the match column for eachRowSet
object being added. - Methods for getting information
One method retrieves theRowSet
objects in theJoinRowSet
object, and another method retrieves theRowSet
names. A third method retrieves either the SQLWHERE
clause used behind the scenes to form theJOIN
or a text description of what theWHERE
clause does. - Methods related to the type of
JOIN
One method sets theJOIN
type, and five methods find out whether theJoinRowSet
object supports a given type. - A method to make a separate copy of the
JoinRowSet
object
This method creates a copy that can be persisted to the data source.
- Since:
- 1.5
Field Summary
Modifier and Type | Field | Description |
---|---|---|
static int | CROSS_JOIN | An ANSI-style |
static int | FULL_JOIN | An ANSI-style |
static int | INNER_JOIN | An ANSI-style |
static int | LEFT_OUTER_JOIN | An ANSI-style |
static int | RIGHT_OUTER_JOIN | An ANSI-style |
Fields declared in interface javax.sql.rowset.CachedRowSet
COMMIT_ON_ACCEPT_CHANGES
Fields declared in interface java.sql.ResultSet
CLOSE_CURSORS_AT_COMMIT, CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, HOLD_CURSORS_OVER_COMMIT, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE
Fields declared in interface javax.sql.rowset.WebRowSet
PUBLIC_XML_SCHEMA, SCHEMA_SYSTEM_ID
Method Summary
Modifier and Type | Method | Description |
---|---|---|
void | addRowSet(Joinable rowset) | Adds the given |
void | addRowSet(RowSet[] rowset,
int[] columnIdx) | Adds one or more |
void | addRowSet(RowSet[] rowset,
String[] columnName) | Adds one or more |
void | addRowSet(RowSet rowset,
int columnIdx) | Adds the given |
void | addRowSet(RowSet rowset,
String columnName) | Adds rowset to this |
int | getJoinType() | Returns a |
String[] | getRowSetNames() | Returns a |
Collection<?> | getRowSets() | Returns a |
String | getWhereClause() | Return a SQL-like description of the WHERE clause being used in a JoinRowSet object. |
void | setJoinType(int joinType) | Allow the application to adjust the type of |
boolean | supportsCrossJoin() | Indicates if CROSS_JOIN is supported by a JoinRowSet implementation |
boolean | supportsFullJoin() | Indicates if FULL_JOIN is supported by a JoinRowSet implementation |
boolean | supportsInnerJoin() | Indicates if INNER_JOIN is supported by a JoinRowSet implementation |
boolean | supportsLeftOuterJoin() | Indicates if LEFT_OUTER_JOIN is supported by a JoinRowSet implementation |
boolean | supportsRightOuterJoin() | Indicates if RIGHT_OUTER_JOIN is supported by a JoinRowSet implementation |
CachedRowSet | toCachedRowSet() | Creates a new |
Methods declared in interface javax.sql.rowset.CachedRowSet
acceptChanges, acceptChanges, columnUpdated, columnUpdated, commit, createCopy, createCopyNoConstraints, createCopySchema, createShared, execute, getKeyColumns, getOriginal, getOriginalRow, getPageSize, getRowSetWarnings, getShowDeleted, getSyncProvider, getTableName, nextPage, populate, populate, previousPage, release, restoreOriginal, rollback, rollback, rowSetPopulated, setKeyColumns, setMetaData, setOriginalRow, setPageSize, setShowDeleted, setSyncProvider, setTableName, size, toCollection, toCollection, toCollection, undoDelete, undoInsert, undoUpdate
Methods declared in interface javax.sql.rowset.Joinable
getMatchColumnIndexes, getMatchColumnNames, setMatchColumn, setMatchColumn, setMatchColumn, setMatchColumn, unsetMatchColumn, unsetMatchColumn, unsetMatchColumn, unsetMatchColumn
Methods declared in interface java.sql.ResultSet
absolute, afterLast, beforeFirst, cancelRowUpdates, clearWarnings, close, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getBoolean, getByte, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getConcurrency, getCursorName, getDate, getDate, getDate, getDate, getDouble, getDouble, getFetchDirection, getFetchSize, getFloat, getFloat, getHoldability, getInt, getInt, getLong, getLong, getMetaData, getNCharacterStream, getNCharacterStream, getNClob, getNClob, getNString, getNString, getObject, getObject, getObject, getObject, getObject, getObject, getRef, getRef, getRow, getRowId, getRowId, getShort, getShort, getSQLXML, getSQLXML, getStatement, getString, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, insertRow, isAfterLast, isBeforeFirst, isClosed, isFirst, isLast, last, moveToCurrentRow, moveToInsertRow, next, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setFetchDirection, setFetchSize, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateClob, updateClob, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNString, updateNString, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateRowId, updateRowId, updateShort, updateShort, updateSQLXML, updateSQLXML, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp, wasNull
Methods declared in interface javax.sql.RowSet
addRowSetListener, clearParameters, execute, getCommand, getDataSourceName, getEscapeProcessing, getMaxFieldSize, getMaxRows, getPassword, getQueryTimeout, getTransactionIsolation, getTypeMap, getUrl, getUsername, isReadOnly, removeRowSetListener, setArray, setAsciiStream, setAsciiStream, setAsciiStream, setAsciiStream, setBigDecimal, setBigDecimal, setBinaryStream, setBinaryStream, setBinaryStream, setBinaryStream, setBlob, setBlob, setBlob, setBlob, setBlob, setBlob, setBoolean, setBoolean, setByte, setByte, setBytes, setBytes, setCharacterStream, setCharacterStream, setCharacterStream, setCharacterStream, setClob, setClob, setClob, setClob, setClob, setClob, setCommand, setConcurrency, setDataSourceName, setDate, setDate, setDate, setDate, setDouble, setDouble, setEscapeProcessing, setFloat, setFloat, setInt, setInt, setLong, setLong, setMaxFieldSize, setMaxRows, setNCharacterStream, setNCharacterStream, setNCharacterStream, setNCharacterStream, setNClob, setNClob, setNClob, setNClob, setNClob, setNClob, setNString, setNString, setNull, setNull, setNull, setNull, setObject, setObject, setObject, setObject, setObject, setObject, setPassword, setQueryTimeout, setReadOnly, setRef, setRowId, setRowId, setShort, setShort, setSQLXML, setSQLXML, setString, setString, setTime, setTime, setTime, setTime, setTimestamp, setTimestamp, setTimestamp, setTimestamp, setTransactionIsolation, setType, setTypeMap, setUrl, setURL, setUsername
Methods declared in interface javax.sql.rowset.WebRowSet
readXml, readXml, writeXml, writeXml, writeXml, writeXml
Methods declared in interface java.sql.Wrapper
isWrapperFor, unwrap
Field Detail
CROSS_JOIN
static final int CROSS_JOIN
An ANSI-style JOIN
providing a cross product of two tables
- See Also:
- Constant Field Values
INNER_JOIN
static final int INNER_JOIN
An ANSI-style JOIN
providing a inner join between two tables. Any unmatched rows in either table of the join should be discarded.
- See Also:
- Constant Field Values
LEFT_OUTER_JOIN
static final int LEFT_OUTER_JOIN
An ANSI-style JOIN
providing a left outer join between two tables. In SQL, this is described where all records should be returned from the left side of the JOIN statement.
- See Also:
- Constant Field Values
RIGHT_OUTER_JOIN
static final int RIGHT_OUTER_JOIN
An ANSI-style JOIN
providing a right outer join between two tables. In SQL, this is described where all records from the table on the right side of the JOIN statement even if the table on the left has no matching record.
- See Also:
- Constant Field Values
FULL_JOIN
static final int FULL_JOIN
An ANSI-style JOIN
providing a full JOIN. Specifies that all rows from either table be returned regardless of matching records on the other table.
- See Also:
- Constant Field Values
Method Detail
addRowSet
void addRowSet(Joinable rowset) throws SQLException
Adds the given RowSet
object to this JoinRowSet
object. If the RowSet
object is the first to be added to this JoinRowSet
object, it forms the basis of the JOIN
relationship to be established.
This method should be used only when the given RowSet
object already has a match column that was set with the Joinable
method setMatchColumn
.
Note: A Joinable
object is any RowSet
object that has implemented the Joinable
interface.
- Parameters:
-
rowset
- theRowSet
object that is to be added to thisJoinRowSet
object; it must implement theJoinable
interface and have a match column set - Throws:
-
SQLException
- if (1) an empty rowset is added to the to thisJoinRowSet
object, (2) a match column has not been set for rowset, or (3) rowset violates the activeJOIN
- See Also:
Joinable.setMatchColumn(int)
addRowSet
void addRowSet(RowSet rowset, int columnIdx) throws SQLException
Adds the given RowSet
object to this JoinRowSet
object and sets the designated column as the match column for the RowSet
object. If the RowSet
object is the first to be added to this JoinRowSet
object, it forms the basis of the JOIN
relationship to be established.
This method should be used when RowSet does not already have a match column set.
- Parameters:
-
rowset
- theRowSet
object that is to be added to thisJoinRowSet
object; it may implement theJoinable
interface -
columnIdx
- anint
that identifies the column to become the match column - Throws:
-
SQLException
- if (1) rowset is an empty rowset or (2) rowset violates the activeJOIN
- See Also:
Joinable.unsetMatchColumn(int)
addRowSet
void addRowSet(RowSet rowset, String columnName) throws SQLException
Adds rowset to this JoinRowSet
object and sets the designated column as the match column. If rowset is the first to be added to this JoinRowSet
object, it forms the basis for the JOIN
relationship to be established.
This method should be used when the given RowSet
object does not already have a match column.
- Parameters:
-
rowset
- theRowSet
object that is to be added to thisJoinRowSet
object; it may implement theJoinable
interface -
columnName
- theString
object giving the name of the column to be set as the match column - Throws:
-
SQLException
- if (1) rowset is an empty rowset or (2) the match column for rowset does not satisfy the conditions of theJOIN
addRowSet
void addRowSet(RowSet[] rowset, int[] columnIdx) throws SQLException
Adds one or more RowSet
objects contained in the given array of RowSet
objects to this JoinRowSet
object and sets the match column for each of the RowSet
objects to the match columns in the given array of column indexes. The first element in columnIdx is set as the match column for the first RowSet
object in rowset, the second element of columnIdx is set as the match column for the second element in rowset, and so on.
The first RowSet
object added to this JoinRowSet
object forms the basis for the JOIN
relationship.
This method should be used when the given RowSet
object does not already have a match column.
- Parameters:
-
rowset
- an array of one or moreRowSet
objects to be added to theJOIN
; it may implement theJoinable
interface -
columnIdx
- an array ofint
values indicating the index(es) of the columns to be set as the match columns for theRowSet
objects in rowset - Throws:
-
SQLException
- if (1) an empty rowset is added to thisJoinRowSet
object, (2) a match column is not set for aRowSet
object in rowset, or (3) aRowSet
object being added violates the activeJOIN
addRowSet
void addRowSet(RowSet[] rowset, String[] columnName) throws SQLException
Adds one or more RowSet
objects contained in the given array of RowSet
objects to this JoinRowSet
object and sets the match column for each of the RowSet
objects to the match columns in the given array of column names. The first element in columnName is set as the match column for the first RowSet
object in rowset, the second element of columnName is set as the match column for the second element in rowset, and so on.
The first RowSet
object added to this JoinRowSet
object forms the basis for the JOIN
relationship.
This method should be used when the given RowSet
object(s) does not already have a match column.
- Parameters:
-
rowset
- an array of one or moreRowSet
objects to be added to theJOIN
; it may implement theJoinable
interface -
columnName
- an array ofString
values indicating the names of the columns to be set as the match columns for theRowSet
objects in rowset - Throws:
-
SQLException
- if (1) an empty rowset is added to thisJoinRowSet
object, (2) a match column is not set for aRowSet
object in rowset, or (3) aRowSet
object being added violates the activeJOIN
getRowSets
Collection<?> getRowSets() throws SQLException
Returns a Collection
object containing the RowSet
objects that have been added to this JoinRowSet
object. This should return the 'n' number of RowSet contained within the JOIN
and maintain any updates that have occurred while in this union.
- Returns:
- a
Collection
object consisting of theRowSet
objects added to thisJoinRowSet
object - Throws:
-
SQLException
- if an error occurs generating theCollection
object to be returned
getRowSetNames
String[] getRowSetNames() throws SQLException
Returns a String
array containing the names of the RowSet
objects added to this JoinRowSet
object.
- Returns:
- a
String
array of the names of theRowSet
objects in thisJoinRowSet
object - Throws:
-
SQLException
- if an error occurs retrieving the names of theRowSet
objects - See Also:
CachedRowSet.setTableName(java.lang.String)
toCachedRowSet
CachedRowSet toCachedRowSet() throws SQLException
Creates a new CachedRowSet
object containing the data in this JoinRowSet
object, which can be saved to a data source using the SyncProvider
object for the CachedRowSet
object.
If any updates or modifications have been applied to the JoinRowSet the CachedRowSet returned by the method will not be able to persist it's changes back to the originating rows and tables in the in the datasource. The CachedRowSet instance returned should not contain modification data and it should clear all properties of it's originating SQL statement. An application should reset the SQL statement using the RowSet.setCommand
method.
In order to allow changes to be persisted back to the datasource to the originating tables, the acceptChanges
method should be used and called on a JoinRowSet object instance. Implementations can leverage the internal data and update tracking in their implementations to interact with the SyncProvider to persist any changes.
- Returns:
- a CachedRowSet containing the contents of the JoinRowSet
- Throws:
-
SQLException
- if an error occurs assembling the CachedRowSet object - See Also:
-
RowSet
,CachedRowSet
,SyncProvider
supportsCrossJoin
boolean supportsCrossJoin()
Indicates if CROSS_JOIN is supported by a JoinRowSet implementation
- Returns:
- true if the CROSS_JOIN is supported; false otherwise
supportsInnerJoin
boolean supportsInnerJoin()
Indicates if INNER_JOIN is supported by a JoinRowSet implementation
- Returns:
- true is the INNER_JOIN is supported; false otherwise
supportsLeftOuterJoin
boolean supportsLeftOuterJoin()
Indicates if LEFT_OUTER_JOIN is supported by a JoinRowSet implementation
- Returns:
- true is the LEFT_OUTER_JOIN is supported; false otherwise
supportsRightOuterJoin
boolean supportsRightOuterJoin()
Indicates if RIGHT_OUTER_JOIN is supported by a JoinRowSet implementation
- Returns:
- true is the RIGHT_OUTER_JOIN is supported; false otherwise
supportsFullJoin
boolean supportsFullJoin()
Indicates if FULL_JOIN is supported by a JoinRowSet implementation
- Returns:
- true is the FULL_JOIN is supported; false otherwise
setJoinType
void setJoinType(int joinType) throws SQLException
Allow the application to adjust the type of JOIN
imposed on tables contained within the JoinRowSet object instance. Implementations should throw a SQLException if they do not support a given JOIN
type.
- Parameters:
-
joinType
- the standard JoinRowSet.XXX static field definition of a SQLJOIN
to re-configure a JoinRowSet instance on the fly. - Throws:
-
SQLException
- if an unsupportedJOIN
type is set - See Also:
getJoinType()
getWhereClause
String getWhereClause() throws SQLException
Return a SQL-like description of the WHERE clause being used in a JoinRowSet object. An implementation can describe the WHERE clause of the SQL JOIN
by supplying a SQL strings description of JOIN
or provide a textual description to assist applications using a JoinRowSet
- Returns:
- whereClause a textual or SQL description of the logical WHERE clause used in the JoinRowSet instance
- Throws:
-
SQLException
- if an error occurs in generating a representation of the WHERE clause.
getJoinType
int getJoinType() throws SQLException
Returns a int
describing the set SQL JOIN
type governing this JoinRowSet instance. The returned type will be one of standard JoinRowSet types: CROSS_JOIN
, INNER_JOIN
, LEFT_OUTER_JOIN
, RIGHT_OUTER_JOIN
or FULL_JOIN
.
- Returns:
- joinType one of the standard JoinRowSet static field definitions of a SQL
JOIN
.JoinRowSet.INNER_JOIN
is returned as the defaultJOIN
type is no type has been explicitly set. - Throws:
-
SQLException
- if an error occurs determining the SQLJOIN
type supported by the JoinRowSet instance. - See Also:
setJoinType(int)