Java Code Examples for java.sql.ResultSet#updateString()

/**
 * Tests calling updateString on all columns of the row.
 * @exception SQLException database access error. Causes test to 
 *                         fail with an error.
 */
public void testUpdateString() 
    throws SQLException
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY, 
            ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery(SELECT_STMT);
    rs.next();

    for (int i = 1; i <= COLUMNS; i++) {
        rs.updateString(i, "2");
        assertEquals("Expected rs.getDouble(" + i + 
                     ") to match updated value", 2, (int) rs.getDouble(i));
    }
    rs.updateRow();
    rs.close();
    checkColumnsAreUpdated();
    
    s.close();
}
 

private static void updateResultSetParam(final GraphReadMethods rg, final ResultSet rs, final String label, final Attribute attr, final int id) throws SQLException {
    switch (attr.getAttributeType()) {
        case "boolean":
            rs.updateBoolean(label, rg.getBooleanValue(attr.getId(), id));
            break;
        case "date":
            final long date = rg.getLongValue(attr.getId(), id);
            if (date != Long.MIN_VALUE) {
                rs.updateDate(label, new Date(date));
            }
            break;
        case "datetime":
            final long timestamp = rg.getLongValue(attr.getId(), id);
            if (timestamp != Long.MIN_VALUE) {
                rs.updateTimestamp(label, new Timestamp(timestamp));
            }
            break;
        case "integer":
            rs.updateInt(label, rg.getIntValue(attr.getId(), id));
            break;
        case "float":
            rs.updateFloat(label, rg.getFloatValue(attr.getId(), id));
            break;
        case "time":
            final long time = rg.getLongValue(attr.getId(), id);
            if (time != Long.MIN_VALUE) {
                rs.updateTime(label, new Time(time));
            }
            break;
        default:
            final String s = rg.getStringValue(attr.getId(), id);
            if (s != null) {
                rs.updateString(label, s);
            }
            break;
    }
}
 

public void update(ResultSet rs) throws SQLException {
    rs.updateString(1, typeName);
    rs.updateInt(2, normalizedType);
    rs.updateInt(3, precision);
    rs.updateString(4, literalPrefix);
    rs.updateString(5, literalSuffix);
    rs.updateString(6, createParams);
    rs.updateShort(7, nullable);
    rs.updateBoolean(8, caseSensitive);
    rs.updateShort(9, searchable);
    rs.updateBoolean(10, unsigned);
    rs.updateBoolean(11, fixedPrecScale);
    rs.updateBoolean(12, autoIncrement);
    rs.updateString(13, localTypeName);
    if (rs.getMetaData().getColumnCount() >= 18) {
        // Some servers provide more information
        rs.updateShort(14, minimumScale);
        rs.updateShort(15, maximumScale);
        rs.updateInt(16, sqlDataType);
        rs.updateInt(17, sqlDatetimeSub);
        rs.updateInt(18, numPrecRadix);
    }
}
 

/**
 * Test that PHB can actually update using {@code ResultSet.insertRow},
 * {@code ResultSet.updateRow} and {@code ResultSet.deleteRow}.
 * <p/>
 * Aside: This test is somewhat artificial here, since the middle manager
 * would not be allowed to do this, presumably; just added here to test
 * this functionality (which was initially broken by the first patch for
 * DERBY-4551).
 * <p/>
 * The problem was that the nested statement contexts used for SQL
 * substatements generated for these ResultSet operations were not
 * correctly set up, so the effective user id would not be the DEFINER
 * (DBO), and authorization would fail. Cf DERBY-4551 and DERBY-3327
 * for more detail.
 */
public static void updateWage()
        throws SQLException
{
    Connection c = null;

    c = DriverManager.getConnection("jdbc:default:connection");
    Statement cStmt = c.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);

    // Try nested statements by inserting, updating and deleting a bogus
    // row
    ResultSet rs = cStmt.executeQuery(
        "select * from s1.wages");
    assertTrue(rs.isBeforeFirst());
    rs.moveToInsertRow();
    rs.updateInt("EMPLOYEEID", 666);
    rs.updateInt("CATEGORY", 667);
    rs.updateDouble("SALARY", 666.0);
    rs.updateString("NAME", "N.N.");
    rs.insertRow();
    rs.close();

    rs = cStmt.executeQuery(
        "select * from s1.wages where name = 'N.N.'");
    rs.next();
    rs.updateDouble("SALARY", 666.1);
    rs.updateRow();
    rs.close();

    rs = cStmt.executeQuery(
        "select * from s1.wages where name = 'N.N.'");
    rs.next();
    rs.deleteRow();
    rs.close();

    cStmt.close();
    c.close();
}
 

@Test
public void testUpdateCustomers() throws SQLException {
	given(resultSet.next()).willReturn(true, true, false);
	given(resultSet.getInt("id")).willReturn(1, 2);
	given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID,
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
		).willReturn(preparedStatement);

	class CustomerUpdateQuery extends UpdatableSqlQuery<Customer> {

		public CustomerUpdateQuery(DataSource ds) {
			super(ds, SELECT_ID_FORENAME_WHERE_ID);
			declareParameter(new SqlParameter(Types.NUMERIC));
			compile();
		}

		@Override
		protected Customer updateRow(ResultSet rs, int rownum, @Nullable Map<? ,?> context)
				throws SQLException {
			rs.updateString(2, "" + context.get(rs.getInt(COLUMN_NAMES[0])));
			return null;
		}
	}

	CustomerUpdateQuery query = new CustomerUpdateQuery(dataSource);
	Map<Integer, String> values = new HashMap<>(2);
	values.put(1, "Rod");
	values.put(2, "Thomas");
	query.execute(2, values);
	verify(resultSet).updateString(2, "Rod");
	verify(resultSet).updateString(2, "Thomas");
	verify(resultSet, times(2)).updateRow();
	verify(preparedStatement).setObject(1, 2, Types.NUMERIC);
	verify(resultSet).close();
	verify(preparedStatement).close();
	verify(connection).close();
}
 

/**
 * Calls updateString on the given ResultSet with the given value
 * for the position of this PlaceHolder.
 */
public void updateString(String value, ResultSet rs)
  throws SQLException
{
  if(value != null) {
    if(isInQuery()) {
      rs.updateString(getIndex(), value);
    }
  } else {
    updateNull(rs);
  }
}
 

/**
 * Updates the current row in the ResultSet using updateRow()
 * @param rs ResultSet to be updated
 * @param meta meta for the ResultSet
 **/
private void updateRow(final ResultSet rs, final ResultSetMetaData meta) 
    throws SQLException
{
    for (int column = 1; column<=meta.getColumnCount(); column++) {
        if (meta.getColumnType(column)==Types.INTEGER) {
            // Set to negative value
            rs.updateInt(column, -rs.getInt(column));
        } else {
            rs.updateString(column, "UPDATED_" + rs.getString(column));
        }
    }
    rs.updateRow();
}
 

/** This method is a wrapper for ResultSet method 
 * updateBigDecimal(int columnIndex, BigDecimal x)
 * @param rs ResultSet
 * @param columnIndex Column Index
 * @param bdString String to be used in updateXXX method
 * @throws SQLException
 */
public static void updateBigDecimalString(ResultSet rs, int columnIndex, String bdString) throws SQLException{
			
	switch(representation){
		case BIGDECIMAL_REPRESENTATION:
			BigDecimal bd = (bdString == null) ? null : new BigDecimal(bdString);
			rs.updateBigDecimal(columnIndex, bd);
			break;
		case STRING_REPRESENTATION:
			rs.updateString(columnIndex, bdString);
			break;
		default:	
			new Exception("Failed: Invalid Big Decimal representation").printStackTrace();
	}
}
 

@Test
public void testUpdateCustomers() throws SQLException {
	given(resultSet.next()).willReturn(true, true, false);
	given(resultSet.getInt("id")).willReturn(1, 2);
	given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID,
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
		).willReturn(preparedStatement);

	class CustomerUpdateQuery extends UpdatableSqlQuery<Customer> {

		public CustomerUpdateQuery(DataSource ds) {
			super(ds, SELECT_ID_FORENAME_WHERE_ID);
			declareParameter(new SqlParameter(Types.NUMERIC));
			compile();
		}

		@Override
		protected Customer updateRow(ResultSet rs, int rownum, Map<? ,?> context)
				throws SQLException {
			rs.updateString(2, "" + context.get(rs.getInt(COLUMN_NAMES[0])));
			return null;
		}
	}

	CustomerUpdateQuery query = new CustomerUpdateQuery(dataSource);
	Map<Integer, String> values = new HashMap<Integer, String>(2);
	values.put(1, "Rod");
	values.put(2, "Thomas");
	query.execute(2, values);
	verify(resultSet).updateString(2, "Rod");
	verify(resultSet).updateString(2, "Thomas");
	verify(resultSet, times(2)).updateRow();
	verify(preparedStatement).setObject(1, 2, Types.NUMERIC);
	verify(resultSet).close();
	verify(preparedStatement).close();
	verify(connection).close();
}
 

/** This method is a wrapper for ResultSet method 
 * updateBigDecimal(String columnName, BigDecimal x)
 * @param rs ResultSet
 * @param columnName Column Name
 * @param bdString String to be used in updateXXX method
 * @throws SQLException
 */
public static void updateBigDecimalString(ResultSet rs, String columnName,String bdString) throws SQLException{
			
	switch(representation){
		case BIGDECIMAL_REPRESENTATION:
			BigDecimal bd = (bdString == null) ? null : new BigDecimal(bdString);
			rs.updateBigDecimal(columnName, bd);
			break;
		case STRING_REPRESENTATION:
			rs.updateString(columnName, bdString);
			break;
		default:	
			new Exception("Failed: Invalid Big Decimal representation").printStackTrace();
	}
}
 

/**
 * Updates the current row in the ResultSet using updateRow()
 * @param rs ResultSet to be updated
 * @param meta meta for the ResultSet
 **/
private void updateRow(final ResultSet rs, final ResultSetMetaData meta) 
    throws SQLException
{
    for (int column = 1; column<=meta.getColumnCount(); column++) {
        if (meta.getColumnType(column)==Types.INTEGER) {
            // Set to negative value
            rs.updateInt(column, -rs.getInt(column));
        } else {
            rs.updateString(column, "UPDATED_" + rs.getString(column));
        }
    }
    rs.updateRow();
}
 

protected int updateTableUsingURS(ResultSet rs, int qty, String status, 
    Timestamp orderTime, int oid, int whichUpdate, boolean[] success) 
    throws SQLException {
  
  int rowCount = 1; 
  
  String txid =  "TXID:" + (Integer)SQLDistTxTest.curTxId.get() + " ";
     
  switch (whichUpdate) {
  case 0: 
    //"update  status = ?        
    Log.getLogWriter().info(txid + "updating tarde.sellorders table using URS with STATUS: " + status  + 
        "where OID:" + oid + " QUERY: " + "update  status = ? where oid = ?");
    //select for update of column (status) has checked already 
    //whether updating on partition column
    rs.updateString("STATUS", status);    
    rs.updateRow();

    break;
  case 1: 
    //"update trade.sellorders set qty = ?,  status = ? where where oid = ?  ",
    Log.getLogWriter().info(txid + "updating trade.sellorders table using URS with QTY:" + qty + ", " +
        "STATUS:" + status + " where OID:" + oid + " QUERY: " + "update trade.sellorders set qty = ?,  status = ? where where oid = ?  ");
    //select for update of column (status and qty) has checked already 
    //whether updating on partition column
    rs.updateInt("QTY", qty);
    rs.updateString("STATUS", status);
    rs.updateRow();
    break;
  case 2: 
    //"update trade.sellorders set order_time = ? where where oid = ? ",
    Log.getLogWriter().info(txid + "updating trade.sellorders table using URS with ORDERTIME:"
        + orderTime + " where OID:" + oid + " QUERY: " + "update trade.sellorders set order_time = ? where where oid = ? ");
    try {
      rs.updateTimestamp("ORDER_TIME", orderTime);
      rs.updateRow();
    } catch (SQLException se) {
      SQLHelper.printSQLException(se);
      if (se.getSQLState().equals("0A000") && 
         partitionKeys.contains("order_time")) {
        rowCount = 0;
        success[0] = false;
        return rowCount;
      } else throw se;
    }      
    break;
  default:
   throw new TestException ("Wrong updatable resultset used here");
  }
  success[0] = true;
  return rowCount;
}
 

protected int updateTableUsingURS(ResultSet rs, int qty, String status, 
    Timestamp orderTime, int oid, int whichUpdate, boolean[] success) 
    throws SQLException {
  
  int rowCount = 1; 
  
  String txid =  "TXID:" + (Integer)SQLDistTxTest.curTxId.get() + " ";
     
  switch (whichUpdate) {
  case 0: 
    //"update  status = ?        
    Log.getLogWriter().info(txid + "updating tarde.sellorders table using URS with STATUS: " + status  + 
        "where OID:" + oid + " QUERY: " + "update  status = ? where oid = ?");
    //select for update of column (status) has checked already 
    //whether updating on partition column
    rs.updateString("STATUS", status);    
    rs.updateRow();

    break;
  case 1: 
    //"update trade.sellorders set qty = ?,  status = ? where where oid = ?  ",
    Log.getLogWriter().info(txid + "updating trade.sellorders table using URS with QTY:" + qty + ", " +
        "STATUS:" + status + " where OID:" + oid + " QUERY: " + "update trade.sellorders set qty = ?,  status = ? where where oid = ?  ");
    //select for update of column (status and qty) has checked already 
    //whether updating on partition column
    rs.updateInt("QTY", qty);
    rs.updateString("STATUS", status);
    rs.updateRow();
    break;
  case 2: 
    //"update trade.sellorders set order_time = ? where where oid = ? ",
    Log.getLogWriter().info(txid + "updating trade.sellorders table using URS with ORDERTIME:"
        + orderTime + " where OID:" + oid + " QUERY: " + "update trade.sellorders set order_time = ? where where oid = ? ");
    try {
      rs.updateTimestamp("ORDER_TIME", orderTime);
      rs.updateRow();
    } catch (SQLException se) {
      SQLHelper.printSQLException(se);
      if (se.getSQLState().equals("0A000") && 
         partitionKeys.contains("order_time")) {
        rowCount = 0;
        success[0] = false;
        return rowCount;
      } else throw se;
    }      
    break;
  default:
   throw new TestException ("Wrong updatable resultset used here");
  }
  success[0] = true;
  return rowCount;
}
 

/**
 * Tests for ResultSet.updateNString()
 * 
 * @throws Exception
 */
public void testUpdateNString() throws Exception {
    createTable("testUpdateNString", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
    Properties props1 = new Properties();
    props1.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
    props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "UTF-8"); // ensure charset is utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
    pstmt1.setString(1, "1");
    pstmt1.setNString(2, "aaa");
    pstmt1.execute();
    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
    rs1.next();
    rs1.updateNString("c2", "bbb");
    rs1.updateRow();
    rs1.moveToInsertRow();
    rs1.updateString("c1", "2");
    rs1.updateNString("c2", "ccc");
    rs1.insertRow();
    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
    rs2.next();
    assertEquals("1", rs2.getString("c1"));
    assertEquals("bbb", rs2.getNString("c2"));
    rs2.next();
    assertEquals("2", rs2.getString("c1"));
    assertEquals("ccc", rs2.getNString("c2"));
    pstmt1.close();
    stmt1.close();
    conn1.close();

    createTable("testUpdateNString", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
    Properties props2 = new Properties();
    props2.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
    props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "SJIS"); // ensure charset isn't utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
    pstmt2.setString(1, "1");
    pstmt2.setString(2, "aaa");
    pstmt2.execute();
    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNString");
    rs3.next();
    try {
        rs3.updateNString("c2", "bbb"); // field's charset isn't utf8
        fail();
    } catch (SQLException ex) {
        assertEquals("Can not call updateNString() when field's character set isn't UTF-8", ex.getMessage());
    }
    rs3.close();
    pstmt2.close();
    stmt2.close();
    conn2.close();
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateStringForColumnLabel() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateString("label", "1");
    }
}
 

/**
 * Test column collations.
 */
public void testColumnCollations() throws Exception {
    Statement stmt = con.createStatement();
    stmt.execute("create table #testColumnCollations (id int primary key, "
            + "cp437val varchar(255) collate SQL_Latin1_General_Cp437_CI_AS, "
            + "cp850val varchar(255) collate SQL_Latin1_General_Cp850_CI_AS, "
            + "ms874val varchar(255) collate Thai_CI_AS, "
            + "ms932val varchar(255) collate Japanese_CI_AS, "
            + "ms936val varchar(255) collate Chinese_PRC_CI_AS, "
            + "ms949val varchar(255) collate Korean_Wansung_CI_AS, "
            + "ms950val varchar(255) collate Chinese_Taiwan_Stroke_CI_AS, "
            + "cp1250val varchar(255) collate SQL_Romanian_Cp1250_CI_AS, "
            + "cp1252val varchar(255) collate SQL_Latin1_General_Cp1_CI_AS)");

    ResultSet rs = stmt.executeQuery("select * from #testColumnCollations");
    assertFalse(rs.next());
    rs.close();

    PreparedStatement pstmt = con.prepareStatement(
            "insert into #testColumnCollations "
            + "(id, cp437val, cp850val, ms874val, ms932val, "
            + "ms936val, ms949val, ms950val, cp1250val, cp1252val) "
            + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    // Test inserting and retrieving pure-ASCII values
    pstmt.setInt(1, 1);
    for (int i = 2; i <= 10; i++) {
        pstmt.setString(i, "test");
    }
    assertEquals(1, pstmt.executeUpdate());

    rs = stmt.executeQuery("select * from #testColumnCollations");
    assertTrue(rs.next());
    for (int i = 2; i <= 10; i++) {
        assertEquals("test", rs.getString(i));
    }
    assertFalse(rs.next());
    rs.close();
    assertEquals(1, stmt.executeUpdate("delete from #testColumnCollations"));

    // Test inserting and retrieving charset-specific values via PreparedStatement
    String[] values = {
        "123abc\u2591\u2592\u2593\u221a\u221e\u03b1",
        "123abc\u00d5\u00f5\u2017\u00a5\u2591\u2592",
        "123abc\u20ac\u2018\u2019\u0e10\u0e1e\u0e3a",
        "123abc\uff67\uff68\uff9e\u60c6\u7210\ufa27",
        "123abc\u6325\u8140\u79a9\u9f1e\u9f32\ufa29",
        "123abc\uac4e\ub009\ubcde\u00de\u24d0\u30e5",
        "123abc\ufe4f\u00d7\uff5e\u515e\u65b0\u7881",
        "123abc\u20ac\u201a\u0103\u015e\u0162\u00f7",
        "123abc\u20ac\u201e\u017d\u00fe\u02dc\u00b8"
    };
    for (int i = 2; i <= 10; i++) {
        pstmt.setString(i, values[i - 2]);
    }
    assertEquals(1, pstmt.executeUpdate());
    pstmt.close();

    rs = stmt.executeQuery("select * from #testColumnCollations");
    assertTrue(rs.next());
    for (int i = 2; i <= 10; i++) {
        assertEquals("Column " + i + " doesn't match", values[i - 2], rs.getString(i));
    }
    assertFalse(rs.next());
    rs.close();
    pstmt.close();
    stmt.close();

    // Test inserting and retrieving charset-specific values via updateable ResultSet
    stmt = con.createStatement(
            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs = stmt.executeQuery("select * from #testColumnCollations");
    assertTrue(rs.next());
    for (int i = 2; i <= 10; i++) {
        rs.updateString(i, rs.getString(i) + "updated");
        values[i - 2] = values[i - 2] + "updated";
    }
    rs.updateRow();
    for (int i = 2; i <= 10; i++) {
        assertEquals("Column " + i + " doesn't match", values[i - 2], rs.getString(i));
    }
    assertFalse(rs.next());
    rs.close();
    stmt.close();
}
 

/**
 * Tests for ResultSet.updateNClob()
 * 
 * @throws Exception
 */
public void testUpdateNClob() throws Exception {
    createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
    Properties props1 = new Properties();
    props1.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
    props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "UTF-8"); // ensure charset isn't utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
    pstmt1.setString(1, "1");
    NClob nClob1 = conn1.createNClob();
    nClob1.setString(1, "aaa");
    pstmt1.setNClob(2, nClob1);
    pstmt1.execute();
    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs1.next();
    NClob nClob2 = conn1.createNClob();
    nClob2.setString(1, "bbb");
    rs1.updateNClob("c2", nClob2);
    rs1.updateRow();
    rs1.moveToInsertRow();
    rs1.updateString("c1", "2");
    NClob nClob3 = conn1.createNClob();
    nClob3.setString(1, "ccc");
    rs1.updateNClob("c2", nClob3);
    rs1.insertRow();
    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs2.next();
    assertEquals("1", rs2.getString("c1"));
    assertEquals("bbb", rs2.getNString("c2"));
    rs2.next();
    assertEquals("2", rs2.getString("c1"));
    assertEquals("ccc", rs2.getNString("c2"));
    pstmt1.close();
    stmt1.close();
    conn1.close();

    createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
    Properties props2 = new Properties();
    props2.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
    props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "SJIS"); // ensure charset isn't utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
    pstmt2.setString(1, "1");
    pstmt2.setString(2, "aaa");
    pstmt2.execute();
    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs3.next();
    NClob nClob4 = conn2.createNClob();
    nClob4.setString(1, "bbb");
    try {
        rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
        fail();
    } catch (SQLException ex) {
        assertEquals("Can not call updateNClob() when field's character set isn't UTF-8", ex.getMessage());
    }
    rs3.close();
    pstmt2.close();
    stmt2.close();
    conn2.close();
}
 

/**
 * Tests for ResultSet.updateNClob()
 * 
 * @throws Exception
 */
public void testUpdateNClob() throws Exception {
    createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
    Properties props1 = new Properties();
    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props1.put("characterEncoding", "UTF-8"); // ensure charset isn't utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
    pstmt1.setString(1, "1");
    NClob nClob1 = conn1.createNClob();
    nClob1.setString(1, "aaa");
    pstmt1.setNClob(2, nClob1);
    pstmt1.execute();
    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs1.next();
    NClob nClob2 = conn1.createNClob();
    nClob2.setString(1, "bbb");
    rs1.updateNClob("c2", nClob2);
    rs1.updateRow();
    rs1.moveToInsertRow();
    rs1.updateString("c1", "2");
    NClob nClob3 = conn1.createNClob();
    nClob3.setString(1, "ccc");
    rs1.updateNClob("c2", nClob3);
    rs1.insertRow();
    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs2.next();
    assertEquals("1", rs2.getString("c1"));
    assertEquals("bbb", rs2.getNString("c2"));
    rs2.next();
    assertEquals("2", rs2.getString("c1"));
    assertEquals("ccc", rs2.getNString("c2"));
    pstmt1.close();
    stmt1.close();
    conn1.close();

    createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
    Properties props2 = new Properties();
    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
    pstmt2.setString(1, "1");
    pstmt2.setString(2, "aaa");
    pstmt2.execute();
    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs3.next();
    NClob nClob4 = conn2.createNClob();
    nClob4.setString(1, "bbb");
    try {
        rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
        fail();
    } catch (SQLException ex) {
        assertEquals("Can not call updateNClob() when field's character set isn't UTF-8", ex.getMessage());
    }
    rs3.close();
    pstmt2.close();
    stmt2.close();
    conn2.close();
}
 

@Test
public void whenInsertUpdateRecord_thenCorrect() throws SQLException {
    Statement stmt = con.createStatement();

    String insertSql = "INSERT INTO employees(name, position, salary) values ('john', 'developer', 2000)";
    stmt.executeUpdate(insertSql);

    String selectSql = "SELECT * FROM employees";
    ResultSet resultSet = stmt.executeQuery(selectSql);

    List<Employee> employees = new ArrayList<>();

    while (resultSet.next()) {
        Employee emp = new Employee();
        emp.setId(resultSet.getInt("emp_id"));
        emp.setName(resultSet.getString("name"));
        emp.setSalary(resultSet.getDouble("salary"));
        emp.setPosition(resultSet.getString("position"));
        employees.add(emp);
    }

    assertEquals("employees list size incorrect", 1, employees.size());
    assertEquals("name incorrect", "john", employees.iterator().next().getName());
    assertEquals("position incorrect", "developer", employees.iterator().next().getPosition());
    assertEquals("salary incorrect", 2000, employees.iterator().next().getSalary(), 0.1);

    Statement updatableStmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql);

    updatableResultSet.moveToInsertRow();
    updatableResultSet.updateString("name", "mark");
    updatableResultSet.updateString("position", "analyst");
    updatableResultSet.updateDouble("salary", 2000);
    updatableResultSet.insertRow();

    String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
    PreparedStatement pstmt = con.prepareStatement(updatePositionSql);
    pstmt.setString(1, "lead developer");
    pstmt.setInt(2, 1);

    String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?";
    PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql);
    pstmt.setDouble(1, 3000);
    pstmt.setInt(2, 1);

    boolean autoCommit = con.getAutoCommit();

    try {
        con.setAutoCommit(false);
        pstmt.executeUpdate();
        pstmt2.executeUpdate();
        con.commit();
    } catch (SQLException exc) {
        con.rollback();
    } finally {
        con.setAutoCommit(autoCommit);
    }
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateStringForColumnLabel() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateString("label", "1");
    }
}