package lab6;
import com.sun.rowset.CachedRowSetImpl;
import javax.sql.rowset.CachedRowSet;

import java.sql.*;
import com.microsoft.sqlserver.jdbc.SQLServerResultSet;

public class CachedRowSetExample {

   public static void main(String[] args) {



      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      CachedRowSetImpl crs = null;

      try {

          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        String connectionUrl = "jdbc:sqlserver://localhost:1433;"
                + "databaseName=NORTHWND;user=Bartek;password=q1w2e3r4;";
        con = DriverManager.getConnection(connectionUrl);



			try
			{
				crs = new CachedRowSetImpl();
			}
			catch (SQLException e1)
			{
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			stmt = con.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, +
	             SQLServerResultSet.CONCUR_READ_ONLY);
			ResultSet resultSet = stmt.executeQuery("select * from Orders");



			resultSet.first();
	         resultSet.deleteRow();
	         System.out.println("ROW DELETED");



			crs.populate(rs);

			 while (crs.next()) {
			        System.out.println(crs.getString(2));
			    }


         // Create and execute an SQL statement that returns a large
         // set of data and then display it.
         //String SQL = "SELECT * FROM Customers;";


         // Perform a fetch for every row in the result set.
        // rs = stmt.executeQuery(SQL);
        // timerTest(1, rs);
        // rs.beforeFirst();
//         while(rs.next())
//         {
//
//              System.out.println(rs.getString(1));
//         }
        // rs.close();

//         // Perform a fetch for every tenth row in the result set.
//         rs = stmt.executeQuery(SQL);
//         timerTest(10, rs);
//         rs.close();
//
//         // Perform a fetch for every 100th row in the result set.
//         rs = stmt.executeQuery(SQL);
//         timerTest(100, rs);
//         rs.close();
//
//         // Perform a fetch for every 1000th row in the result set.
//         rs = stmt.executeQuery(SQL);
//         timerTest(1000, rs);
//         rs.close();
//
//         // Perform a fetch for every 128th row (the default) in the result set.
//         rs = stmt.executeQuery(SQL);
//         timerTest(0, rs);
//         rs.close();

      }
      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }

      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }

   private static void timerTest(int fetchSize, ResultSet rs) {
      try {

         // Declare the variables for tracking the row count and elapsed time.
         int rowCount = 0;
         long startTime = 0;
         long stopTime = 0;
         long runTime = 0;

         // Set the fetch size then iterate through the result set to
         // cache the data locally.
         rs.setFetchSize(fetchSize);
         startTime = System.currentTimeMillis();
         while (rs.next()) {
            rowCount++;
           System.out.println(rs.getString(2));

         }
         stopTime = System.currentTimeMillis();
         runTime = stopTime - startTime;

         // Display the results of the timer test.
         System.out.println("FETCH SIZE: " + rs.getFetchSize());
         System.out.println("ROWS PROCESSED: " + rowCount);
         System.out.println("TIME TO EXECUTE: " + runTime);
         System.out.println();

      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}