/*
 * Copyright (c) 1995, 2020, 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 java.net.MalformedURLException;
import java.net.URL;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
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.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
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 {

    this.con.setAutoCommit(false);
    RowSetFactory factory = RowSetProvider.newFactory();
    CachedRowSet crs = factory.createCachedRowSet();


    try {
      crs.setUsername(settings.userName);
      crs.setPassword(settings.password);
      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 {
    String query = "select ITEM_ID from MERCH_INVENTORY where ITEM_ID = ?";
    try (PreparedStatement ps = con.prepareStatement(query)){
      ps.setInt(1, id);
      if (ps.execute(query)) {
        return true;
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } 
    return false;

  }

  public static void viewTable(Connection con) throws SQLException {
    String query = "select * from MERCH_INVENTORY";
    try (Statement 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);
    } 
  }

  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);
    }
  }
}