Examples of javax.sql.rowset.WebRowSet

javax.sql.rowset.WebRowSet
sun.com/xml/ns/jdbc/webrowset.xsd">http://java.sun.com/xml/ns/jdbc/webrowset.xsd

It describes the standard XML document format required when describing a {@code RowSet} object in XML and must be used be all standard implementationsof the {@code WebRowSet} interface to ensure interoperability. In addition,the {@code WebRowSet} schema uses specific SQL/XML Schema annotations,thus ensuring greater cross platform inter-operability. This is an effort currently under way at the ISO organization. The SQL/XML definition is available at the following URI:

http://standards.iso.org/iso/9075/2002/12/sqlxml

The schema definition describes the internal data of a {@code RowSet} objectin three distinct areas:

properties - These properties describe the standard synchronization provider properties in addition to the more general {@code RowSet} properties.
metadata - This describes the metadata associated with the tabular structure governed by a {@code WebRowSet} object. The metadata described is closely aligned with themetadata accessible in the underlying {@code java.sql.ResultSet} interface.
data - This describes the original data (the state of data since the last population or last synchronization of the {@code WebRowSet} object) and the currentdata. By keeping track of the delta between the original data and the current data, a {@code WebRowSet} maintains the ability to synchronize changesin its data back to the originating data source.

2.0 WebRowSet States

The following sections demonstrates how a {@code WebRowSet} implementationshould use the XML Schema to describe update, insert, and delete operations and to describe the state of a {@code WebRowSet} object in XML.

2.1 State 1 - Outputting a {@code WebRowSet} Object to XML

In this example, a {@code WebRowSet} object is created and populated with a simple 2 column,5 row table from a data source. Having the 5 rows in a {@code WebRowSet} objectmakes it possible to describe them in XML. The metadata describing the various standard JavaBeans properties as defined in the RowSet interface plus the standard properties defined in the {@code CachedRowSet}™ interface provide key details that describe WebRowSet properties. Outputting the WebRowSet object to XML using the standard {@code writeXml} methods describes the internal properties as follows:

 {@code  select co1, col2 from test_table 1  true 0 0 1   0 0 0 false TRANSACTION_READ_UNCOMMITED false  jdbc:thin:oracle  .com.rowset.provider.RIOptimisticProvider Oracle Corporation 1.0 LOW NONE  }

The meta-data describing the make up of the WebRowSet is described in XML as detailed below. Note both columns are described between the {@code column-definition} tags.

 {@code  2  1 false true false 1 false true 10 COL1 COL1  10 0   1 CHAR   2 false false false 1 true true 39 COL2 COL2  38 0   3 NUMBER  }

Having detailed how the properties and metadata are described, the following details how the contents of a {@code WebRowSet} object is described in XML. Note, thatthis describes a {@code WebRowSet} object that has not undergone anymodifications since its instantiation. A {@code currentRow} tag is mapped to each row of the table structure that the{@code WebRowSet} object provides. A {@code columnValue} tag may containeither the {@code stringData} or {@code binaryData} tag, according tothe SQL type that the XML value is mapping back to. The {@code binaryData} tag contains data in theBase64 encoding and is typically used for {@code BLOB} and {@code CLOB} type data.

 {@code    firstrow   1     secondrow   2     thirdrow   3     fourthrow   4   }

2.2 State 2 - Deleting a Row

Deleting a row in a {@code WebRowSet} object involves simply moving to the rowto be deleted and then calling the method {@code deleteRow}, as in any other {@code RowSet} object. The followingtwo lines of code, in which wrs is a {@code WebRowSet} object, deletethe third row.

 wrs.absolute(3); wrs.deleteRow();

The XML description shows the third row is marked as a {@code deleteRow}, which eliminates the third row in the {@code WebRowSet} object.

 {@code    firstrow   1     secondrow   2     thirdrow   3     fourthrow   4   }

2.3 State 3 - Inserting a Row

A {@code WebRowSet} object can insert a new row by moving to the insert row,calling the appropriate updater methods for each column in the row, and then calling the method {@code insertRow}.

 {@code wrs.moveToInsertRow(); wrs.updateString(1, "fifththrow"); wrs.updateString(2, "5"); wrs.insertRow();}

The following code fragment changes the second column value in the row just inserted. Note that this code applies when new rows are inserted right after the current row, which is why the method {@code next} moves the cursor to the correct row.Calling the method {@code acceptChanges} writes the change to the data source.

 {@code wrs.moveToCurrentRow();wrs.next(); wrs.updateString(2, "V"); wrs.acceptChanges();}

Describing this in XML demonstrates where the Java code inserts a new row and then performs an update on the newly inserted row on an individual field.

 {@code    firstrow   1     secondrow   2     newthirdrow   III     fifthrow   5   V     fourthrow   4   }

2.4 State 4 - Modifying a Row

Modifying a row produces specific XML that records both the new value and the value that was replaced. The value that was replaced becomes the original value, and the new value becomes the current value. The following code moves the cursor to a specific row, performs some modifications, and updates the row when complete.

 {@code wrs.absolute(5); wrs.updateString(1, "new4thRow"); wrs.updateString(2, "IV"); wrs.updateRow();}

In XML, this is described by the {@code modifyRow} tag. Both the original and newvalues are contained within the tag for original row tracking purposes.

 {@code    firstrow   1     secondrow   2     newthirdrow   III     fifthrow   5     fourthrow   new4thRow   4   IV   }

@see javax.sql.rowset.JdbcRowSet @see javax.sql.rowset.CachedRowSet @see javax.sql.rowset.FilteredRowSet @see javax.sql.rowset.JoinRowSet


    public void testAddJoinableRowSet_WebRowSet_Exception() throws Exception {
        /*
         * Test empty WebRowSet
         */
        WebRowSet webRs = newWebRowSet();
        /*
         * Though WebRowSet implements Joinable, addRowSet(WebRowSet) would
         * throw ClassCastException when run on RI.
         */
        if ("true".equals(System.getProperty("Testing Harmony"))) {
            try {
                jrs.addRowSet(webRs);
                fail("should throw SQLException");
            } catch (SQLException e) {
                // expected
            }


            webRs.setMatchColumn(1);
            try {
                jrs.addRowSet(webRs);
                fail("should throw SQLException");
            } catch (SQLException e) {
                // expected
            }
        } else {
            try {
                jrs.addRowSet(webRs);
                fail("should throw NullPointerException");
            } catch (NullPointerException e) {
                // expected
            } catch (SQLException e) {
                // Expected
            }


            webRs.setMatchColumn(1);
            jrs.addRowSet(webRs);
        }
        jrs.close();


        /*
         * Test WebRowSet filled with data
         */
        jrs = newJoinRowSet();
        webRs = newWebRowSet();
        webRs.populate(st.executeQuery("SELECT * FROM USER_INFO"));
        webRs.setMatchColumn(1);
        jrs.addRowSet(webRs);
        assertTrue(jrs.next());
        assertEquals(1, jrs.getInt(1));
        jrs.close();
    }

    public void testAddJoinableRowSet_WebRowSet_Int_Exception()
            throws Exception {
        /*
         * Test empty WebRowSet. RI would throw ClassCastException.
         */
        WebRowSet webRs = newWebRowSet();
        if ("true".equals(System.getProperty("Testing Harmony"))) {
            try {
                // can't be empty
                jrs.addRowSet(webRs, 1);
                fail("should throw SQLException");
            } catch (SQLException e) {
                // expected
            }
        } else {
            jrs.addRowSet(webRs, 1);
        }
        jrs.close();


        /*
         * Test non-empty WebRowSet.
         */
        jrs = newJoinRowSet();
        webRs = newWebRowSet();
        webRs.populate(st.executeQuery("SELECT * FROM USER_INFO"));
        jrs.addRowSet(webRs, 1);
        assertTrue(jrs.next());
        assertEquals(1, jrs.getInt(1));
        jrs.close();
    }

         * xsi:schemaLocation also can be empty. However, The value of the
         * attribute "prefix="xmlns",localpart="xsi" can't be empty. No matter
         * what the value of these attributes are, the output xml's header of
         * WebRowSet keeps the same.
         */
        WebRowSet webRs = newWebRowSet();
        Reader fileReader = new FileReader(XML_SRC_URL_INVALID_HEADER);
        webRs.readXml(fileReader);


        StringWriter strWriter = new StringWriter();
        webRs.writeXml(strWriter);
        assertFalse(-1 == strWriter.toString().indexOf(
                "http://java.sun.com/xml/ns/jdbc"));
        assertFalse(-1 == strWriter.toString().indexOf(
                "http://www.w3.org/2001/XMLSchema-instance"));
    }

        assertFalse(-1 == strWriter.toString().indexOf(
                "http://www.w3.org/2001/XMLSchema-instance"));
    }


    public void testReaderXml_Reader() throws Exception {
        WebRowSet webRs = newWebRowSet();
        webRs.readXml(new FileReader(currentUrl));


        /*
         * TODO A row is marked as delete in XML. The row isn't marked as delete
         * any more after populate to WebRowSet.
         */
        if (!"true".equals(System.getProperty("Testing Harmony"))) {
            assertTrue(webRs.absolute(3));
            assertEquals(3, webRs.getInt(1));
            assertFalse(webRs.rowDeleted());
            webRs.deleteRow();
        }


        Document srcDoc = DocumentBuilderFactory.newInstance()
                .newDocumentBuilder().parse(currentUrl);
        XmlWriterTest.assertProperties(srcDoc, webRs);

        XmlWriterTest.assertMetadata(srcDoc, webRs);
        XmlWriterTest.assertData(srcDoc, webRs);
    }


    public void testReaderXml_InputStream() throws Exception {
        WebRowSet webRs = newWebRowSet();
        webRs.readXml(new FileInputStream(currentUrl));


        /*
         * TODO A row is marked as delete in XML. The row isn't marked as delete
         * any more after populating to WebRowSet.
         */
        if (!"true".equals(System.getProperty("Testing Harmony"))) {
            assertTrue(webRs.absolute(3));
            assertEquals(3, webRs.getInt(1));
            assertFalse(webRs.rowDeleted());
            webRs.deleteRow();
        }


        Document srcDoc = DocumentBuilderFactory.newInstance()
                .newDocumentBuilder().parse(currentUrl);
        XmlWriterTest.assertProperties(srcDoc, webRs);

        preStmt.setTime(11, new Time(452368512));
        preStmt.setTimestamp(12, new Timestamp(874532105));
        preStmt.executeUpdate();
        preStmt.close();


        WebRowSet webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);
        webRs.writeXml(strWriter);
        assertTrue(webRs.last());
        assertEquals(unicodeChar, webRs.getString(2));
        assertFalse(-1 == strWriter.toString().indexOf(unicodeChar));


        WebRowSet webRs2 = newWebRowSet();
        webRs2.readXml(new StringReader(strWriter.toString()));
        assertTrue(webRs2.last());
        assertEquals(unicodeChar, webRs2.getString(2));
    }

         * writeXml(), write to StringWriter; call readXml() to read the
         * StringWriter's content again. See what happens: The properties and
         * metadata remains the same. Only four new rows which are the same as
         * the original data in WebRowSet are added.
         */
        WebRowSet webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);
        ResultSetMetaData meta = webRs.getMetaData();
        // register listener
        Listener listener = new Listener();
        webRs.addRowSetListener(listener);
        assertNull(listener.getTag());
        // write to StringWriter
        webRs.writeXml(strWriter);
        webRs.beforeFirst();
        // read from StringWriter
        webRs.readXml(new StringReader(strWriter.toString()));
        isMetaDataEquals(meta, webRs.getMetaData());
        webRs.beforeFirst();
        int index = 0;
        while (webRs.next()) {
            index++;
            if (index > 4) {
                assertEquals(index - 4, webRs.getInt(1));
            } else {
                assertEquals(index, webRs.getInt(1));
            }
        }
        // TODO How to solve the difference between RI and Harmony
        // assertEquals(8, index);


        /*
         * Create a new table. Then populate it to WebRowSet. See what happens:
         * The metadata and the row datas come from the new table.
         */
        createNewTable();
        rs = st.executeQuery("SELECT * FROM CUSTOMER_INFO");
        index = 0;
        while (rs.next()) {
            index++;
            if (index == 1) {
                assertEquals(1111, rs.getInt(1));
                assertEquals("customer_one", rs.getString(2));
            } else if (index == 2) {
                assertEquals(5555, rs.getInt(1));
                assertEquals("customer_two", rs.getString(2));
            }
        }
        assertEquals(2, index);
        rs = st.executeQuery("SELECT * FROM CUSTOMER_INFO");
        webRs.beforeFirst();
        listener.clear();
        webRs.populate(rs);
        assertEquals(CachedRowSetListenerTest.EVENT_ROWSET_CHANGED, listener
                .getTag());
        webRs.beforeFirst();
        index = 0;
        /*
         * TODO record the difference between RI and Harmony
         */
        if ("true".equals(System.getProperty("Testing Harmony"))) {
            while (webRs.next()) {
                index++;
                if (index == 1) {
                    assertEquals(1111, webRs.getInt(1));
                    assertEquals("customer_one", webRs.getString(2));
                } else if (index == 2) {
                    assertEquals(5555, webRs.getInt(1));
                    assertEquals("customer_two", webRs.getString(2));
                }
            }
        } else {
            while (webRs.next()) {
                index++;
                if (index == 1) {
                    assertEquals(1, webRs.getInt(1));
                    assertEquals("hermit", webRs.getString(2));
                } else if (index == 2) {
                    assertEquals(2, webRs.getInt(1));
                    assertEquals("test", webRs.getString(2));
                }
            }
        }
        assertEquals(2, index);
    }

        }
        assertEquals(2, index);
    }


    public void testWriteXML() throws Exception {
        WebRowSet webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);


        Map<String, Class<?>> map = new HashMap<String, Class<?>>();
        map.put("VARCHAR", String.class);
        map.put("Array", Array.class);
        webRs.setTypeMap(map);


        webRs.setKeyColumns(new int[] { 2, 1 });


        // update a row
        assertTrue(webRs.absolute(4));
        webRs.updateInt(1, 44);
        webRs.updateString(2, "update44");
        webRs.updateRow();


        // update a row but not call updateRow()
        assertTrue(webRs.absolute(2));
        webRs.updateInt(1, 22);


        // delete a row
        assertTrue(webRs.absolute(3));
        webRs.deleteRow();


        // insert a row
        webRs.moveToInsertRow();
        webRs.updateInt(1, 77);
        webRs.updateString(2, "insert77");
        webRs.insertRow();
        webRs.moveToCurrentRow();


        webRs.writeXml(strWriter);


        Document doc = getDocument(strWriter);
        assertProperties(doc, webRs);
        assertMetadata(doc, webRs);
        assertData(doc, webRs);


        webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);


        strWriter = new StringWriter();
        webRs.writeXml(strWriter);


        doc = getDocument(strWriter);
        assertProperties(doc, webRs);
        assertMetadata(doc, webRs);
        assertData(doc, webRs);

        assertMetadata(doc, webRs);
        assertData(doc, webRs);
    }


    public void testWriteXML_Update() throws Exception {
        WebRowSet webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);


        webRs.absolute(3);
        webRs.updateString(2, "update3");


        webRs.writeXml(strWriter);


        assertTrue(webRs.isAfterLast());


        Document doc = getDocument(strWriter);


        assertProperties(doc, webRs);
        assertMetadata(doc, webRs);
        assertData(doc, webRs);


        webRs.updateRow();


        strWriter = new StringWriter();
        webRs.writeXml(strWriter);


        assertTrue(webRs.isAfterLast());


        doc = getDocument(strWriter);


        assertProperties(doc, webRs);
        assertMetadata(doc, webRs);

        assertMetadata(doc, webRs);
        assertData(doc, webRs);
    }


    public void testWriteXML_Insert() throws Exception {
        WebRowSet webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);


        webRs.moveToInsertRow();
        webRs.updateString(2, "update3");
        webRs.updateInt(4, 3);
        webRs.moveToCurrentRow();


        webRs.writeXml(strWriter);


        assertTrue(webRs.isAfterLast());


        Document doc = getDocument(strWriter);


        assertProperties(doc, webRs);
        assertMetadata(doc, webRs);
        assertData(doc, webRs);


        webRs = newWebRowSet();
        rs = st.executeQuery("SELECT * FROM USER_INFO");
        webRs.populate(rs);


        assertTrue(webRs.absolute(3));
        webRs.moveToInsertRow();
        webRs.updateString(2, "insert5");
        webRs.updateInt(1, 5);
        webRs.insertRow();
        webRs.moveToCurrentRow();
        webRs.next();
        webRs.updateString(2, "update5");
        webRs.updateInt(1, 6);


        strWriter = new StringWriter();
        webRs.writeXml(strWriter);


        assertTrue(webRs.isAfterLast());


        doc = getDocument(strWriter);


        assertProperties(doc, webRs);
        assertMetadata(doc, webRs);