cachedRowSet = new CachedRowSetImpl();
cachedRowSet.setCommand("SELECT * FROM COFFEES");
cachedRowSet.execute(connection);

cachedRowSet = new CachedRowSetImpl();
cachedRowSet.setDataSouceName("java:comp/env/jdbc/continuousops");
cachedRowSet.setCommand("SELECT * FROM COFFEES");
cachedRowSet.execute();

cachedRowSet.addRowSetListener(new ExampleRowSetListener());

//getting first cachedRowSet
cachedRowSet = new CachedRowSetImpl();
cachedRowSet.setCommand("SELECT * FROM COFFEES");
cachedRowSet.execute(connection);

//getting second cachedRowSet
cachedRowSet2 = new CachedRowSetImpl();
cachedRowSet2.setCommand("SELECT * FROM COFFEES");
cachedRowSet2.execute(connection);

cachedRowSet.next();
cachedRowSet.updateInt("SUP_ID", 101);
cachedRowSet.updateRow();
cachedRowSet.acceptChanges();

cachedRowSet2.next();
cachedRowSet2.updateInt("SUP_ID", 150);
cachedRowSet2.updateRow();
cachedRowSet2.acceptChanges();

} catch (SyncProviderException spe){
    System.out.println("conflict detected.");

    SyncResolver resolver = spe.getSyncResolver();

    Object crsValue;
    Object resolverValue;
    Object resolvedValue;

    try {
        while (resolver.nextConflict()) {

            if (resolver.getStatus() == SyncResolver.UPDATE_ROW_CONFLICT) {
                int row = resolver.getRow();
                cachedRowSet2.absolute(row);

                int colCount = cachedRowSet2.getMetaData().getColumnCount();
                for (int j = 1; j <= colCount; j++) {
                    System.out.println("conflict value: " + resolver.getConflictValue(j));
                    if (resolver.getConflictValue(j) != null) {
                        crsValue = cachedRowSet2.getObject(j);
                        resolverValue = resolver.getConflictValue(j);

                        resolvedValue = crsValue;

                        resolver.setResolvedValue(j, resolvedValue);
                        System.out.println("conflict resolved.");
                    }
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

conflict detected.
conflict value: null
conflict value: null
conflict value: null
conflict value: null
conflict value: null

mysql> SELECT * FROM COFFEES;
+--------------------+--------+-------+-------+-------+
| COF_NAME           | SUP_ID | PRICE | SALES | TOTAL |
+--------------------+--------+-------+-------+-------+
| Amaretto           |    101 |  5.99 |     0 |     0 |
| Amaretto_decaf     |     49 | 10.99 |     0 |     0 |
| Colombian          |    101 |  0.07 |     0 |     0 |
| Colombian_Decaf    |    101 |  8.99 |     0 |     0 |
| Espresso           |    150 |  9.99 |     0 |     0 |
| French_Roast       |     49 |  8.99 |     0 |     0 |
| French_Roast_Decaf |     49 |  9.99 |     0 |     0 |
| Hazelnut           |     49 |  9.99 |     0 |     0 |
| Hazelnut_decaf     |     49 | 10.99 |     0 |     0 |
+--------------------+--------+-------+-------+-------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM SUPPLIERS;
+--------+---------------------------+---------------------+--------------+-------+-------+
| SUP_ID | SUP_NAME                  | STREET              | CITY         | STATE | ZIP   |
+--------+---------------------------+---------------------+--------------+-------+-------+
|     49 | Superior Coffee           | 1 Party Place       | Mendocino    | CA    | 95460 |
|    101 | Acme, Inc.                | 99 Market Street    | Groundsville | CA    | 95199 |
|    150 | The High Ground           | 100 Coffee Lane     | Meadows      | CA    | 93966 |
|    456 | Restaurant Supplies, Inc. | 200 Magnolia Street | Meadows      | CA    | 93966 |
|    927 | Professional Kitchen      | 300 Daisy Avenue    | Groundsville | CA    | 95199 |
+--------+---------------------------+---------------------+--------------+-------+-------+
5 rows in set (0.00 sec)

SELECT c.COF_NAME
FROM COFFEES AS c, SUPPLIERS AS s
WHERE c.SUP_ID = s.SUP_ID
AND SUP_NAME = "Acme, Inc."

coffees = new CachedRowSetImpl();
coffees.setCommand("SELECT * FROM COFFEES");
coffees.execute(connection);

suppliers = new CachedRowSetImpl();
suppliers.setCommand("SELECT * FROM SUPPLIERS");
suppliers.execute(connection);

jrs = new JoinRowSetImpl();
jrs.addRowSet(coffees, "SUP_ID");
jrs.addRowSet(suppliers, "SUP_ID");

System.out.println("Coffees bought from " + supplierName + ": ");
while (jrs.next()) {
    if (jrs.getString("SUP_NAME").equals(supplierName)) {
        String coffeeName = jrs.getString(1);
        System.out.println("     " + coffeeName);
    }
}

mysql> SELECT * FROM COFFEES WHERE COF_NAME IN ("Amaretto", "Espresso");
+----------+--------+-------+-------+-------+
| COF_NAME | SUP_ID | PRICE | SALES | TOTAL |
+----------+--------+-------+-------+-------+
| Amaretto |     49 |  9.99 |     0 |     0 |
| Espresso |    150 |  9.99 |     0 |     0 |
+----------+--------+-------+-------+-------+
2 rows in set (0.00 sec)

try {
  context = new InitialContext();
  DataSource dataSource = (DataSource)context.lookup("java:comp/env/jdbc/continuousops");
  connection = dataSource.getConnection();

  connection.setAutoCommit(false);

  preparedStatement1 = connection.prepareStatement("UPDATE COFFEES SET PRICE=? WHERE COF_NAME = 'Amaretto' LIMIT 1");
  preparedStatement2 = connection.prepareStatement("UPDATE COFFEES SET PRICE=? WHERE COF_NAME = 'Espresso' LIMIT 1");

  preparedStatement1.setFloat(1, 7.99f);
  preparedStatement1.executeUpdate();

  preparedStatement2.setFloat(1, 6.99f);
  preparedStatement2.executeUpdate();

  connection.commit();

} catch (NamingException | SQLException e) {
  connection.rollback();
  e.printStackTrace();
}finally{
  connection.setAutoCommit(true);
}

mysql> SELECT * FROM COFFEES WHERE COF_NAME IN ("Amaretto", "Espresso");
+----------+--------+-------+-------+-------+
| COF_NAME | SUP_ID | PRICE | SALES | TOTAL |
+----------+--------+-------+-------+-------+
| Amaretto |     49 |  7.99 |     0 |     0 |
| Espresso |    150 |  6.99 |     0 |     0 |
+----------+--------+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> show variables like '%isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

int isolationLevel = connection.getTransactionIsolation();

    Savepoint savepoint1 = null;
    Savepoint savepoint2 = null;
    try {
        context = new InitialContext();
        DataSource dataSource = (DataSource)context.lookup("java:comp/env/jdbc/continuousops");
        connection = dataSource.getConnection();

        connection.setAutoCommit(false);

        savepoint1 = connection.setSavepoint("point1");

        preparedStatement1 = connection.prepareStatement("UPDATE COFFEES SET PRICE=? WHERE COF_NAME = 'Amaretto' LIMIT 1");
        preparedStatement2 = connection.prepareStatement("UPDATE COFFEES SET PRICE=? WHERE COF_NAME = 'Espresso' LIMIT 1");

        preparedStatement1.setFloat(1, 5.99f);
        preparedStatement1.executeUpdate();

        savepoint2 = connection.setSavepoint("point2");

        preparedStatement2.setFloat(1, 7.99f);
        preparedStatement2.executeUpdate();

        connection.rollback(savepoint2);

        connection.commit();

    } catch (NamingException | SQLException e) {
        try {
            connection.rollback(savepoint1);

mysql> SELECT * FROM COFFEES WHERE COF_NAME IN ("Amaretto", "Espresso");
+----------+--------+-------+-------+-------+
| COF_NAME | SUP_ID | PRICE | SALES | TOTAL |
+----------+--------+-------+-------+-------+
| Amaretto |     49 |  9.99 |     0 |     0 |
| Espresso |    150 |  9.99 |     0 |     0 |
+----------+--------+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM COFFEES WHERE COF_NAME IN ("Amaretto", "Espresso");
+----------+--------+-------+-------+-------+
| COF_NAME | SUP_ID | PRICE | SALES | TOTAL |
+----------+--------+-------+-------+-------+
| Amaretto |     49 |  5.99 |     0 |     0 |
| Espresso |    150 |  9.99 |     0 |     0 |
+----------+--------+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from COFFEES;
+--------------------+--------+-------+-------+-------+
| COF_NAME           | SUP_ID | PRICE | SALES | TOTAL |
+--------------------+--------+-------+-------+-------+
| Colombian          |    101 |  7.99 |     0 |     0 |
| Colombian_Decaf    |    101 |  8.99 |     0 |     0 |
| Espresso           |    150 |  9.99 |     0 |     0 |
| French_Roast       |     49 |  8.99 |     0 |     0 |
| French_Roast_Decaf |     49 |  9.99 |     0 |     0 |
+--------------------+--------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> show create table COFFEES\G
*************************** 1. row ***************************
       Table: COFFEES
Create Table: CREATE TABLE `COFFEES` (
  `COF_NAME` varchar(32) NOT NULL,
  `SUP_ID` int(11) NOT NULL,
  `PRICE` decimal(10,2) NOT NULL,
  `SALES` int(11) NOT NULL,
  `TOTAL` int(11) NOT NULL,
  PRIMARY KEY (`COF_NAME`),
  KEY `SUP_ID` (`SUP_ID`),
  CONSTRAINT `coffees_ibfk_1` FOREIGN KEY (`SUP_ID`) REFERENCES `SUPPLIERS` (`SUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

private List<HashMap> viewTable(Connection connection, String dbName) throws SQLException {
        String query = "SELECT COF_NAME, SUP_ID, PRICE, SALES, TOTAL FROM " + dbName + ".coffees" ;
        preparedStatement = connection.prepareStatement(query);
        resultSet = preparedStatement.executeQuery();
        resultSet.next();
        System.out.println("|" + resultSet.getString("COF_NAME")
                         + "|" + resultSet.getInt("SUP_ID")
                         + "|" + resultSet.getBigDecimal("PRICE") + "|");

|Colombian|101|7.99|

resultSet.next();
resultSet.next();
System.out.println("|" + resultSet.getString("COF_NAME")
               + "|" + resultSet.getInt("SUP_ID")
               + "|" + resultSet.getBigDecimal("PRICE") + "|");

|Espresso|150|9.99|

resultSet.previous();
System.out.println("|" + resultSet.getString("COF_NAME")
      + "|" + resultSet.getInt("SUP_ID")
      + "|" + resultSet.getBigDecimal("PRICE") + "|");

|Colombian_Decaf|101|8.99|

resultSet.last();
System.out.println("|" + resultSet.getString("COF_NAME")
      + "|" + resultSet.getInt("SUP_ID")
      + "|" + resultSet.getBigDecimal("PRICE") + "|");

|French_Roast_Decaf|49|9.99|

resultSet.absolute(4);
System.out.println("|" + resultSet.getString("COF_NAME")
      + "|" + resultSet.getInt("SUP_ID")
      + "|" + resultSet.getBigDecimal("PRICE") + "|");

|French_Roast|49|8.99|

resultSet.afterLast();
System.out.println(resultSet.next());

false

private void updateTable(Connection connection, String dbName) throws SQLException {
        float percentage = 0.3f;
        String query = "SELECT cof_name, sup_id, price, sales, total FROM " + dbName + ".coffees" ;
        preparedStatement = connection.prepareStatement(query);
        resultSet = preparedStatement.executeQuery();
        resultSet.next();
        System.out.println("|" + resultSet.getString("COF_NAME")
                + "|" + resultSet.getInt("SUP_ID")
                + "|" + resultSet.getBigDecimal("PRICE") + "|");
        float f = resultSet.getFloat("PRICE");
        resultSet.updateFloat("PRICE", f * percentage);
        resultSet.updateRow();
        System.out.println("|" + resultSet.getString("COF_NAME")
                + "|" + resultSet.getInt("SUP_ID")
                + "|" + resultSet.getBigDecimal("PRICE") + "|");
    }

com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
  at com.mysql.jdbc.ResultSetImpl.updateFloat(ResultSetImpl.java:8319)

private void updateTableRevised(Connection connection, String dbName) throws SQLException {
  float percentage = 0.3f;
  String query = "SELECT cof_name, sup_id, price, sales, total FROM " + dbName + ".coffees" ;
  Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  resultSet = statement.executeQuery(query);
  resultSet.next();
  System.out.println("|" + resultSet.getString("COF_NAME")
          + "|" + resultSet.getInt("SUP_ID")
          + "|" + resultSet.getBigDecimal("PRICE") + "|");
  float f = resultSet.getFloat("PRICE");
  resultSet.updateFloat("PRICE", f * percentage);
  resultSet.updateRow();
  System.out.println("|" + resultSet.getString("COF_NAME")
          + "|" + resultSet.getInt("SUP_ID")
          + "|" + resultSet.getBigDecimal("PRICE") + "|");
}

|Colombian|101|2.40|
|Colombian|101|0.72|

public void batchUpdate(Connection connection) throws SQLException {
  Statement stmt = null;
  try {
      connection.setAutoCommit(false);
      stmt = connection.createStatement();

      stmt.addBatch(
              "INSERT INTO COFFEES " +
                      "VALUES('Amaretto', 49, 9.99, 0, 0)");

      stmt.addBatch(
              "INSERT INTO COFFEES " +
                      "VALUES('Hazelnut', 49, 9.99, 0, 0)");

      stmt.addBatch(
              "INSERT INTO COFFEES " +
                      "VALUES('Amaretto_decaf', 49, " +
                      "10.99, 0, 0)");

      stmt.addBatch(
              "INSERT INTO COFFEES " +
                      "VALUES('Hazelnut_decaf', 49, " +
                      "10.99, 0, 0)");

      int [] updateCounts = stmt.executeBatch();
      System.out.println(updateCounts);
      connection.commit();

  } catch(BatchUpdateException b) {
      b.printStackTrace();
  } catch(SQLException ex) {
      ex.printStackTrace();
  } finally {
      if (stmt != null) { stmt.close(); }
      connection.setAutoCommit(true);
  }
}

mysql> select * from COFFEES;
+--------------------+--------+-------+-------+-------+
| COF_NAME           | SUP_ID | PRICE | SALES | TOTAL |
+--------------------+--------+-------+-------+-------+
| Amaretto           |     49 |  9.99 |     0 |     0 |
| Amaretto_decaf     |     49 | 10.99 |     0 |     0 |
| Colombian          |    101 |  0.07 |     0 |     0 |
| Colombian_Decaf    |    101 |  8.99 |     0 |     0 |
| Espresso           |    150 |  9.99 |     0 |     0 |
| French_Roast       |     49 |  8.99 |     0 |     0 |
| French_Roast_Decaf |     49 |  9.99 |     0 |     0 |
| Hazelnut           |     49 |  9.99 |     0 |     0 |
| Hazelnut_decaf     |     49 | 10.99 |     0 |     0 |
+--------------------+--------+-------+-------+-------+

DatabaseMetaData databaseMetaData = connection.getMetaData();
System.out.printf("[TYPE_FORWARD_ONLY]%s%n", databaseMetaData.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
System.out.printf("[TYPE_SCROLL_INSENSITIVE]%s%n", databaseMetaData.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
System.out.printf("[TYPE_SCROLL_SENSITIVE]%s%n", databaseMetaData.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));

[TYPE_FORWARD_ONLY]false
[TYPE_SCROLL_INSENSITIVE]true
[TYPE_SCROLL_SENSITIVE]false

System.out.printf("[CONCUR_READ_ONLY]%s%n", databaseMetaData.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
System.out.printf("[CONCUR_UPDATABLE]%s%n", databaseMetaData.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));

[CONCUR_READ_ONLY]true
[CONCUR_UPDATABLE]true

DatabaseMetaData dbMetaData = connection.getMetaData();
System.out.println("ResultSet.HOLD_CURSORS_OVER_COMMIT = " +
          ResultSet.HOLD_CURSORS_OVER_COMMIT);

System.out.println("ResultSet.CLOSE_CURSORS_AT_COMMIT = " +
          ResultSet.CLOSE_CURSORS_AT_COMMIT);

System.out.println("Default cursor holdability: " +
          dbMetaData.getResultSetHoldability());

System.out.println("Supports HOLD_CURSORS_OVER_COMMIT? " +
          dbMetaData.supportsResultSetHoldability(
                    ResultSet.HOLD_CURSORS_OVER_COMMIT));

System.out.println("Supports CLOSE_CURSORS_AT_COMMIT? " +
          dbMetaData.supportsResultSetHoldability(
                    ResultSet.CLOSE_CURSORS_AT_COMMIT));

ResultSet.HOLD_CURSORS_OVER_COMMIT = 1
ResultSet.CLOSE_CURSORS_AT_COMMIT = 2
Default cursor holdability: 1
Supports HOLD_CURSORS_OVER_COMMIT? true
Supports CLOSE_CURSORS_AT_COMMIT? false

private void insertRecord(Connection connection) throws SQLException {
  PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO address (name, address, tel, email) VALUES(?, ?, ?, ?)");
  preparedStatement.setString(1, getParameter("name"));
  preparedStatement.setString(2, getParameter("address"));
  preparedStatement.setString(3, getParameter("tel"));
  preparedStatement.setString(4, getParameter("email"));
  preparedStatement.executeUpdate();
  preparedStatement.close();
}

private HashMap selectRecord(Connection connection) throws SQLException {
  PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM address LIMIT 1");
  ResultSet resultSet = preparedStatement.executeQuery();
  HashMap<String, String> map = new HashMap<>();
  if(resultSet.next()){
      map.put("name-1", resultSet.getString("name"));
      map.put("address-1", resultSet.getString("address"));
      map.put("tel-1", resultSet.getString("tel"));
      map.put("email-1", resultSet.getString("email"));
  }
  preparedStatement.close();
  return map;
}

Statement statement = null;
String query = "SELECT * FROM address LIMIT 1";

try {
  statement = connection.createStatement();
  ResultSet resultSet = statement.executeQuery(query);
(以下略)

├── lib
├── pom.xml
├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── kirishikistudios
│       │           └── continuousops
│       │               └── digintojdbc
│       │                   └── Chapter2.java
│       ├── resources
│       └── webapp
│           ├── META-INF
│           │   └── context.xml
│           ├── WEB-INF
│           │   └── web.xml
│           └── chapter2
│               └── index.jsp

package com.kirishikistudios.continuousops.digintojdbc;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

public class Chapter2 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        request.setAttribute("subject", "JDBC");
        Context context;
        Connection connection = null;
        try {
            context = new InitialContext();
            DataSource dataSource = (DataSource)context.lookup("java:comp/env/jdbc/continuousops");
            connection = dataSource.getConnection();
            request.setAttribute("message1", "Successfully connected to database.");
            connection.close();
        } catch (NamingException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null) {
                try {
                    connection.close();
                    request.setAttribute("message2", "Successfully disconnected from database.");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        this.getServletContext().getRequestDispatcher("/chapter2/index.jsp").forward(request, response);
    }
}

<%@ page contentType="text/html; charsert=UTF-8" %>
<!DOCTYPE html>
<html>
<body>
<h2>Hello ${requestScope['subject']}!</h2>
<p>${requestScope['message1']}</p>
<p>${requestScope['message2']}</p>
</body>
</html>