com.oracle.tutorial.jdbc.CachedRowSetSample.java Source code

Java tutorial

Introduction

Here is the source code for com.oracle.tutorial.jdbc.CachedRowSetSample.java

Source

/*
 * Copyright (c) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 *   - Redistributions of source code must retain the above copyright
 *     notice, this list of conditions and the following disclaimer.
 *
 *   - Redistributions in binary form must reproduce the above copyright
 *     notice, this list of conditions and the following disclaimer in the
 *     documentation and/or other materials provided with the distribution.
 *
 *   - Neither the name of Oracle or the names of its
 *     contributors may be used to endorse or promote products derived
 *     from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
 * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE COPYRIGHT OWNER OR
 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
 * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

package com.oracle.tutorial.jdbc;

import com.sun.rowset.CachedRowSetImpl;

import java.net.MalformedURLException;
import java.net.URL;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Timestamp;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.spi.SyncProviderException;
import javax.sql.rowset.spi.SyncResolver;

public class CachedRowSetSample {

    private String dbName;
    private Connection con;
    private String dbms;
    private JDBCTutorialUtilities settings;

    public CachedRowSetSample(Connection connArg, JDBCTutorialUtilities settingsArg) {
        super();
        this.con = connArg;
        this.dbName = settingsArg.dbName;
        this.dbms = settingsArg.dbms;
        this.settings = settingsArg;

    }

    public void testPaging() throws SQLException, MalformedURLException {

        CachedRowSet crs = null;
        this.con.setAutoCommit(false);

        try {

            crs = new CachedRowSetImpl();
            crs.setUsername(settings.userName);
            crs.setPassword(settings.password);

            if (this.dbms.equals("mysql")) {
                crs.setUrl(settings.urlString + "?relaxAutoCommit=true");
            } else {
                crs.setUrl(settings.urlString);
            }
            crs.setCommand("select * from MERCH_INVENTORY");

            // Setting the page size to 4, such that we
            // get the data in chunks of 4 rows @ a time.
            crs.setPageSize(100);

            // Now get the first set of data
            crs.execute();

            crs.addRowSetListener(new ExampleRowSetListener());

            // Keep on getting data in chunks until done.

            int i = 1;
            do {
                System.out.println("Page number: " + i);
                while (crs.next()) {
                    System.out.println("Found item " + crs.getInt("ITEM_ID") + ": " + crs.getString("ITEM_NAME"));
                    if (crs.getInt("ITEM_ID") == 1235) {
                        int currentQuantity = crs.getInt("QUAN") + 1;
                        System.out.println("Updating quantity to " + currentQuantity);
                        crs.updateInt("QUAN", currentQuantity + 1);
                        crs.updateRow();
                        // Syncing the row back to the DB
                        crs.acceptChanges(con);
                    }

                } // End of inner while
                i++;
            } while (crs.nextPage());
            // End of outer while

            // Inserting a new row
            // Doing a previous page to come back to the last page
            // as we ll be after the last page.

            int newItemId = 123456;

            if (this.doesItemIdExist(newItemId)) {
                System.out.println("Item ID " + newItemId + " already exists");
            } else {
                crs.previousPage();
                crs.moveToInsertRow();
                crs.updateInt("ITEM_ID", newItemId);
                crs.updateString("ITEM_NAME", "TableCloth");
                crs.updateInt("SUP_ID", 927);
                crs.updateInt("QUAN", 14);
                Calendar timeStamp;
                timeStamp = new GregorianCalendar();
                timeStamp.set(2006, 4, 1);
                crs.updateTimestamp("DATE_VAL", new Timestamp(timeStamp.getTimeInMillis()));
                crs.insertRow();
                crs.moveToCurrentRow();

                // Syncing the new row back to the database.
                System.out.println("About to add a new row...");
                crs.acceptChanges(con);
                System.out.println("Added a row...");
                this.viewTable(con);
            }
        } catch (SyncProviderException spe) {

            SyncResolver resolver = spe.getSyncResolver();

            Object crsValue; // value in the RowSet object
            Object resolverValue; // value in the SyncResolver object
            Object resolvedValue; // value to be persisted

            while (resolver.nextConflict()) {

                if (resolver.getStatus() == SyncResolver.INSERT_ROW_CONFLICT) {
                    int row = resolver.getRow();
                    crs.absolute(row);

                    int colCount = crs.getMetaData().getColumnCount();
                    for (int j = 1; j <= colCount; j++) {
                        if (resolver.getConflictValue(j) != null) {
                            crsValue = crs.getObject(j);
                            resolverValue = resolver.getConflictValue(j);

                            // Compare crsValue and resolverValue to determine
                            // which should be the resolved value (the value to persist)
                            //
                            // This example choses the value in the RowSet object,
                            // crsValue, to persist.,

                            resolvedValue = crsValue;

                            resolver.setResolvedValue(j, resolvedValue);
                        }
                    }
                }
            }
        } catch (SQLException sqle) {
            JDBCTutorialUtilities.printSQLException(sqle);
        } finally {
            if (crs != null)
                crs.close();
            this.con.setAutoCommit(true);
        }

    }

    private boolean doesItemIdExist(int id) throws SQLException {

        Statement stmt = null;
        String query = "select ITEM_ID from MERCH_INVENTORY where ITEM_ID = " + id;
        try {
            stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(query);

            if (rs.next()) {
                return true;
            }

        } catch (SQLException e) {
            JDBCTutorialUtilities.printSQLException(e);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        return false;

    }

    public static void viewTable(Connection con) throws SQLException {
        Statement stmt = null;
        String query = "select * from MERCH_INVENTORY";
        try {
            stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                System.out.println("Found item " + rs.getInt("ITEM_ID") + ": " + rs.getString("ITEM_NAME") + " ("
                        + rs.getInt("QUAN") + ")");
            }

        } catch (SQLException e) {
            JDBCTutorialUtilities.printSQLException(e);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    public static void main(String[] args) {
        JDBCTutorialUtilities myJDBCTutorialUtilities;
        Connection myConnection = null;

        if (args[0] == null) {
            System.err.println("Properties file not specified at command line");
            return;
        } else {
            try {
                myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]);
            } catch (Exception e) {
                System.err.println("Problem reading properties file " + args[0]);
                e.printStackTrace();
                return;
            }
        }

        try {
            myConnection = myJDBCTutorialUtilities.getConnection();

            if (myJDBCTutorialUtilities == null) {
                System.out.println("myJDBCTU is null");
            }

            if (myConnection == null) {
                System.out.println("myConnection is null");
            }

            CachedRowSetSample myCachedRowSetSample = new CachedRowSetSample(myConnection, myJDBCTutorialUtilities);
            myCachedRowSetSample.viewTable(myConnection);
            myCachedRowSetSample.testPaging();

        } catch (SQLException e) {
            JDBCTutorialUtilities.printSQLException(e);
        } catch (Exception ex) {
            System.out.println("Unexpected exception");
            ex.printStackTrace();
        }

        finally {
            JDBCTutorialUtilities.closeConnection(myConnection);
        }
    }
}