package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

/**
 * - ResultSet
 * represents tabular view of data mostly generated by SELECT query
 * maintains cursor which initially points to before first row
 * provides many get*** methods which convert data to java types
 *
 * - ResultSet type
 * ResultSet.TYPE_FORWARD_ONLY: default mode, go through result data once and forward
 * ResultSet.TYPE_SCROLL_INSENSITIVE: can go through data in back/forth. Data is static
 * ResultSet.TYPE_SCROLL_SENSITIVE: can go through data in back/forth. Data is dynamic, refresh when change in db
 *
 * - ResultSet concurrency
 * ResultSet.CONCUR_READ_ONLY: default. can't update result set
 * ResultSet.CONCUR_UPDATABLE: can update result set. but not supported in all driver
 *
 */
public class GettingResultSet {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:oracle:thin:@home:1521:orcl";
        try (Connection conn = DriverManager.getConnection(url, "user", "pass"))
        {
            // Creating statement with Scrollable type
            Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            String sqlSelect = "select name, age from people";
            ResultSet resultSet = statement.executeQuery(sqlSelect);

            // next() returns true and moves cursor to first row, returns false if there is no next record.
            // initial cursor points not to data. So it is must call to get data from ResultSet
            while(resultSet.next()) {
                // Access columns via its name
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");

                // Access columns via its ID. ID starts from 1!
                name = resultSet.getString(1);
                age = resultSet.getInt(2);

                // Getting as Object
                Object nameObj = resultSet.getObject("name");
                if (nameObj instanceof String)
                    name = (String)nameObj;
            }

            // It will throw Exception as cursor is not pointed to any data yet. So call next() and check its result
            ResultSet resultSet1 = statement.executeQuery(sqlSelect);
            resultSet1.getInt(2);

            // It is bad and not safe. next()'s return result must be used before calling get methods()
            resultSet1.next();
            resultSet1.getInt(2);

            // Getting Date, Time
            String selectDates = "select birth_date, birth_time from people where name='Bold'";
            ResultSet resultSet2 = statement.executeQuery(selectDates);
            if (resultSet2.next()){
                java.sql.Date birthDate = resultSet2.getDate(1);
                java.sql.Time birthTime = resultSet2.getTime(2);
                java.sql.Timestamp ts = resultSet2.getTimestamp(1);

                // Convert sql Date, Time to Local Date, Time
                java.time.LocalDate localBirthDate = birthDate.toLocalDate();
                java.time.LocalTime localBirthTime = birthTime.toLocalTime();
                java.time.LocalDateTime localBirthDateTime = ts.toLocalDateTime();
            }

            // Scrolling through result sets requires SCROLLABLE result set type
            // So, make sure that the type is scrollable whenever methods other than next() is used
            // all those scrolling methods return boolean except for beforeFirst(), afterLast()
            // they return false when they make cursor points to one before first data row

            // previous() - moves cursor to previous record. Returns true if exists, otherwise false
            resultSet.previous();

            // first(), last() - moves cursor to first and last record in set, respectively
            resultSet.first();

            // void beforeFirst() - brings cursor to its initial position
            // void afterLast() - moves cursor to after last record
            resultSet.afterLast();
            resultSet.beforeFirst();
            System.out.println(resultSet.beforeFirst());    // DOES NOT COMPILE, beforeFirst() returns nothing

            // absolute(int) - moves cursor to certain position. Data row starts from 1.
            // Position 0 is before first data row. Returns true if data exists, false otherwise
            resultSet.absolute(1);
            resultSet.absolute(-1); // negative value point starting from last position

            // relative(int) - moves cursor by given number of rows backward or forward.
            resultSet.relative(1);  // move next
            resultSet.relative(-1); // moves previous
        }
    }
}

@@ -2207,16 +2207,19 @@
 l throw 
+SQL
 Exceptio
@@ -2285,17 +2285,16 @@
 and 
-check its
+then get
  res
@@ -2392,32 +2392,81 @@
 tSet1.getInt(2);
+   // without a doubt, it will throw SQLException
 %0A%0A            //
@@ -2476,16 +2476,8 @@
  is 
-bad and 
 not 
@@ -2522,36 +2522,46 @@
 sed 
-before calling get methods()
+to check if there is any result to get
 %0A   
@@ -2613,32 +2613,90 @@
 tSet1.getInt(2);
+   // trying to get second row which is uncertain to exist
 %0A%0A            //

Call next() before getting row from ResultSet