The Joinable interface provides methods for getting and setting a match column, which is the basis for forming a SQL JOIN formed by adding RowSet objects to a JoinRowSet object. The Joinable is defined in the javax.sql.rowset package.
An instance of a class that implements Joinable can be added to a JoinRowSet object to allow an SQL JOIN relationship to be established. JoinRowSet defines the following type of joins: CROSS_JOIN, FULL_JOIN, INNER_JOIN, LEFT_OUTER_JOIN and RIGHT_OUTER_JOIN. INNER_JOIN is the default type.
A JoinRowSet is a disconnected rowset. The JoinRowSet interface makes it possible to get data from one or more rowset objects consolidated into one table without having to incur the expense of creating a connection to a database. It is therefore ideally suited for use by disconnected rowset objects.
Both connected and disconnected rowset objects can be part of a join. JdbcRowSet a connected rowset, implements Joinable, and it can be part of a join.
All the rowsets, CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet and WebRowSet extend this interface.
From the API’s javadoc: An application may add a RowSet object that has not implemented the Joinable interface to a JoinRowSet object, but to do so it must use one of the JoinRowSet.addRowSet methods that takes both a RowSet object and a match column or an array of RowSet objects and an array of match columns.
1. An Example
The example uses a JoinRowSet to combine two CachedRowSet objects.
The cached rowsets have rows from two database tables emp_table and emp_addr_table respectively. The two rowsets are combined (joined) on their empid match (join) column.
1.1. First, create an empty join rowset.
RowSetFactory rsFactory = RowSetProvider.newFactory(); JoinRowSet jrs = rsFactory.createJoinRowSet();
The first rowset object to be added to the join rowset becomes the basis for the join relationship. A match column is set in two ways: by calling the (1) Joinable‘s setMatchColumn() method, or (2) JoinRowSet‘s addRowSet() method.
1.2. Create the first rowset and add it to the join rowset.
Joinable‘s setMatchColumn() method sets the designated column (either using column index or name) as the match column for the rowset object. A JoinRowSet object now adds this rowset object based on the match column.
crs1 is the first CachedRowSet.
crs1.setMatchColumn(ID); // Note: ID is the empid column in the database table emp_table jrs.addRowSet(crs1);
1.3. Create the second rowset and add it to the join rowset.
The JoinRowSet‘s addRowSet(RowSet rowset, int columnIdx or String columnName) method adds the given rowset object to this join rowset object and sets the designated column as the match column for the rowset object.
crs2 is the second CachedRowSet.
jrs.addRowSet(crs2, ID); // Note: ID is the empid column in the database table emp_addr_table
1.4. Query the join rowset.
At this point, the join rowset is an INNER_JOIN (the default join type) of the two cached rowset objects (crs1 and crs2) based on their specified column (empid). Now the application can navigate or modify the join rowset object, using RowSet methods.
In the example the join rowset rows are queried and verified with the database tables’SQL join query.
2. Database Tables and Data
The example uses MySQL version 5.5.20 server database. The details to create the database table data are shown below.
The following SQL commands can be used to create tables (in an example_db database; emp_table and emp_addr_table) and insert some data into the tables. The mysql command-line tool can be used.
2.1. Create Database
CREATE DATABASE example_db; USE example_db;
2.2. Create Tables
Create emp_table and emp_addr_table.
CREATE TABLE emp_table (empid VARCHAR(3), name VARCHAR(30), PRIMARY KEY (empid)); CREATE TABLE emp_addr_table (empid VARCHAR(3), addrid VARCHAR(1), addr VARCHAR(30), city VARCHAR(20), state VARCHAR(20), PRIMARY KEY (empid, addrid ));
2.3. Create Table Data
Insert data into emp_table and emp_addr_table.
INSERT INTO emp_table VALUES ('1', 'William');
INSERT INTO emp_table VALUES ('2', 'Valentina');
INSERT INTO emp_addr_table VALUES ('1', '1', '41A Van Buren Street', 'Phoenix', 'Arizona');
INSERT INTO emp_addr_table VALUES ('1', '2', '100 University Drive', 'Sacramento', 'California');
INSERT INTO emp_addr_table VALUES ('2', '1', '12-E Nassau Street', 'New York', 'New York');2.4. Query Tables
2.4.1. Query emp_table and emp_addr_table.
mysql> SELECT * FROM emp_table; +-------+-----------+ | empid | name | +-------+-----------+ | 1 | William | | 2 | Valentina | +-------+-----------+ 2 rows in set (0.00 sec)
mysql> SELECT * FROM emp_addr_table; +-------+--------+----------------------+------------+------------+ | empid | addrid | addr | city | state | +-------+--------+----------------------+------------+------------+ | 1 | 1 | 41A Van Buren Street | Phoenix | Arizona | | 1 | 2 | 100 University Drive | Sacramento | California | | 2 | 1 | 12-E Nassau Street | New York | New York | +-------+--------+----------------------+------------+------------+ 3 rows in set (0.00 sec)
2.4.2. Join both tables and query on empid.
mysql> SELECT a.empid, name, city FROM emp_table a, emp_addr_table b WHERE a.empid = b.empid; +-------+-----------+------------+ | empid | name | city | +-------+-----------+------------+ | 1 | William | Phoenix | | 1 | William | Sacramento | | 2 | Valentina | New York | +-------+-----------+------------+ 3 rows in set (0.00 sec)
3. The Example Program Code
JoinableExample.java
import javax.sql.rowset.RowSetProvider;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JoinRowSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JoinableExample {
private final static String DB_URL = "jdbc:mysql://localhost:3306/example_db";
private final static String USR = "root";
private final static String PWD = "root";
private final static String EMPLOYEE_TABLE = "emp_table";
private final static String ADDRESS_TABLE = "emp_addr_table";
private final static String ID = "empid";
private final static String NAME = "name";
private final static String CITY = "city";
public static void main(String [] args)
throws Exception {
Connection con = getDbConnection();
CachedRowSet crs1 = getCachedRowset();
populateRowset(crs1, con, EMPLOYEE_TABLE, ID, NAME);
CachedRowSet crs2 = getCachedRowset();
populateRowset(crs2, con, ADDRESS_TABLE, ID, CITY);
JoinRowSet jrs = getJoinRowset(crs1, crs2);
queryJoinRowset(jrs);
crs1.close();
crs2.close();
jrs.close();
System.out.println("- Close rowsets.");
}
private static Connection getDbConnection()
throws SQLException {
System.out.println("- Get connection to database: " + DB_URL);
Connection con = DriverManager.getConnection(DB_URL, USR, PWD);
return con;
}
private static CachedRowSet getCachedRowset()
throws SQLException {
System.out.println("- Create cached rowset");
RowSetFactory rsFactory = RowSetProvider.newFactory();
CachedRowSet rowset = rsFactory.createCachedRowSet();
return rowset;
}
private static void populateRowset(CachedRowSet crs, Connection con, String tableName, String col1, String col2)
throws SQLException {
System.out.println("- Populate rowset with database table rows: " + tableName);
String sql = "SELECT * FROM " + tableName;
crs.setCommand(sql);
crs.execute(con);
crs.last();
System.out.println("Total rows: " + crs.getRow());
queryCrs(crs, col1, col2);
}
private static void queryCrs(CachedRowSet crs, String col1, String col2)
throws SQLException {
crs.beforeFirst();
while (crs.next()) {
String s1 = crs.getString(col1);
String s2 = crs.getString(col2);
System.out.println("[" + col1 + ", " + col2 + "]: " + s1 + ", " + s2);
}
}
private static JoinRowSet getJoinRowset(CachedRowSet crs1, CachedRowSet crs2)
throws SQLException {
System.out.println("- Create join rowset");
RowSetFactory rsFactory = RowSetProvider.newFactory();
JoinRowSet jrs = rsFactory.createJoinRowSet();
System.out.println("- Add two cached rowsets, joined on: " + ID);
crs1.setMatchColumn(ID);
jrs.addRowSet(crs1);
jrs.addRowSet(crs2, ID);
return jrs;
}
private static void queryJoinRowset(JoinRowSet jrs)
throws SQLException {
System.out.println("- Query join rowset:");
jrs.last();
System.out.println("Total rows: " + jrs.getRow());
jrs.beforeFirst();
while (jrs.next()) {
String s1 = jrs.getString(ID);
String s2 = jrs.getString(NAME);
String s3 = jrs.getString(CITY);
System.out.println("[" + ID + ", " + NAME + ", " + CITY + "]: " + s1 + ", " + s2 + ", " + s3);
}
}
}4. The Output
- Get connection to database: jdbc:mysql://localhost:3306/example_db - Create cached rowset - Populate rowset with database table rows: emp_table Total rows: 2 [empid, name]: 1, William [empid, name]: 2, Valentina - Create cached rowset - Populate rowset with database table rows: emp_addr_table Total rows: 3 [empid, city]: 1, Phoenix [empid, city]: 1, Sacramento [empid, city]: 2, New York - Create join rowset - Add two cached rowsets, joined on: empid - Query join rowset: Total rows: 3 [empid, name, city]: 2, Valentina, New York [empid, name, city]: 1, William, Sacramento [empid, name, city]: 1, William, Phoenix - Close rowsets.
From the output, note that the SQL join query (in the section 2.4.2. Join both tables and query on empid.) and the join rowset’s query outputs have the same data.
5. Download Java Source Code
This was an example of javax.sql.rowset.Joinable
You can download the full source code of this example here: JoinableExample.zip
