Java Code Examples for java.sql.PreparedStatement#setString()

public void fillPreparedInsertStatement(PreparedStatement pstmt, String stmt, int iid)
    throws SQLException {
  String instrument = Instrument.getInstrument(iid);
  int numPositionsPerInstrument = SectorPrms.getNumPositionsPerInstrument();
  int numBookValues = SectorPrms.getNumBookValues();
  int synthetic = 0;
  
  //for (int i = 0; i < numPositionsPerInstrument; i++) {
    int id = iid; // unique
    int amount = id;
    int bookId = id % numBookValues;
    String owner = Position.getOwner(id);
    synthetic = 1 - synthetic;
    String symbol = Position.getSymbol(id);
    pstmt.setInt(1, id);
    pstmt.setInt(2, bookId);
    pstmt.setString(3, instrument);
    pstmt.setInt(4, amount);
    pstmt.setInt(5, synthetic);
    pstmt.setString(6, owner);
    pstmt.setString(7, symbol);
  //}
    
}
 

public boolean removeGroup(Connection connection, String resourceType, String resourceName,
                           String action, String group) throws AuthServerException {
    PreparedStatement insertMappingsStmt = null;
    try {
        insertMappingsStmt = connection.prepareStatement(RdbmsConstants.PS_DELETE_AUTH_RESOURCE_MAPPING);
        insertMappingsStmt.setString(1, resourceType);
        insertMappingsStmt.setString(2, resourceName);
        insertMappingsStmt.setString(3, action);
        insertMappingsStmt.setString(4, group);

        int updateRows = insertMappingsStmt.executeUpdate();

        return updateRows != 0;
    } catch (SQLException e) {
        throw new AuthServerException("Error occurred while persisting resource.", e);
    } finally {
        close(insertMappingsStmt);
    }
}
 

/**
 * @description:get a new uuid by type
 * @date:2014-5-6 下午6:05:08
 * @version:v1.0
 * @param type
 * @return
 */
public synchronized String add(String type){
	Connection conn = null;
	PreparedStatement ptmt = null;
	ResultSet rs = null;
	try {
		conn = DbPoolConnection.getInstance().getConnection();
		ptmt = conn.prepareStatement("insert into uuid (type) values(?)",Statement.RETURN_GENERATED_KEYS);
		ptmt.setString(1, type);
		ptmt.execute();
		rs = ptmt.getGeneratedKeys();
		if (rs.next()) {
			return String.valueOf(rs.getInt(1));
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		DbPoolConnection.getInstance().closeResultSet(rs);
		DbPoolConnection.getInstance().closeStatment(ptmt);
		DbPoolConnection.getInstance().closeConn(conn);
	}
	return "";
}
 

public Map<String, String> getFilterIdNameMap(String userName){
	Map<String, String> idNameMap = new HashMap<String, String>();

	PreparedStatement pstm = null;
	Connection conn = null;
	ResultSet rs = null;
	try{
		conn = DbPoolConnection.getInstance().getReadConnection();
		pstm = conn.prepareStatement("SELECT id,name FROM filter WHERE create_user = ? and xml !=null order by name");
		pstm.setString(1, userName);
		rs = pstm.executeQuery();
		while(rs.next()){
			idNameMap.put(rs.getString("id"), rs.getString("name"));
		}
	}
	catch(Exception e){
		e.printStackTrace();
	}
	finally{
		DbPoolConnection.getInstance().closeAll(rs, pstm, conn);
	}
	return idNameMap;
}
 

@Override
public void addMember(String groupName, JID user, boolean administrator) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(ADD_USER);
        pstmt.setString(1, groupName);
        pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
        pstmt.setInt(3, administrator ? 1 : 0);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

/**
 * Perform export using SYSCS_UTIL.EXPORT_QUERY procedure.
 */
protected void doExportQuery(String query,
                           String fileName,
                           String colDel , 
                           String charDel, 
                           String codeset) 
    throws SQLException 
{
    String expsql = 
        "call SYSCS_UTIL.EXPORT_QUERY(? , ? , ? , ?, ?)";
    PreparedStatement ps = prepareStatement(expsql);
    ps.setString(1, query);
    ps.setString(2, fileName);
    ps.setString(3, colDel);
    ps.setString(4, charDel);
    ps.setString(5, codeset);
    ps.execute();
    ps.close();
}
 

@Test
public void testSumUngroupedHavingAggregation() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time) FROM "+tablename+" WHERE organization_id=? HAVING sum(unique_users) > 200 AND sum(db_utilization) > 4.5";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        initTableValues(tablename, tenantId, getSplits(tenantId));
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(210, rs.getInt(1));
        assertEquals(BigDecimal.valueOf(14.5), rs.getBigDecimal(2));
        assertEquals(2100L, rs.getLong(3));
        assertEquals(BigDecimal.valueOf(4.6), rs.getBigDecimal(4));
        assertEquals(0, rs.getLong(5));
        assertEquals(true, rs.wasNull());
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@SuppressWarnings( "WeakerAccess" )
protected Long findEntityPK( EntityState state, Lazy<PreparedStatement, SQLException> queryPKPS )
    throws SQLException
{
    // TODO build cache: Polygene Identity -> PK
    Long entityPK = null;
    PreparedStatement ps = queryPKPS.getValue();
    ps.setString( 1, state.entityReference().identity().toString() );
    ResultSet rs = null;
    try
    {
        rs = ps.executeQuery();

        if( rs.next() )
        {
            entityPK = rs.getLong( 1 );
        }
    }
    finally
    {
        SQLUtil.closeQuietly( rs );
    }

    return entityPK;
}
 

public boolean saveRankPermission(Rank rank, String perm) {
    LOG.debug("Saving RankPermission {} for Rank {} in Town {}", perm, rank.getName(), rank.getTown().getName());
    try {
        PreparedStatement s = prepare("INSERT INTO " + prefix + "RankPermissions (node, rank, townName) VALUES(?, ?, ?)", true);
        s.setString(1, perm);
        s.setString(2, rank.getName());
        s.setString(3, rank.getTown().getName());
        s.execute();

        rank.permissionsContainer.add(perm);
    } catch (SQLException e) {
        LOG.error("Failed to add permission ({}) to Rank ({})", perm, rank.getName());
        LOG.error(ExceptionUtils.getStackTrace(e));
        return false;
    }
    return true;
}
 

public static void main(String arg[]) {

		try {

			Connection conn2 = getConnection();
			System.out.println(conn2);
			String selectSql = "select * from test";
			PreparedStatement pstm2 = conn2.prepareStatement(selectSql);
			ResultSet rs = pstm2.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(1));
			}
			pstm2.close();
			rs.close();

			Connection conn1 = getConnection();
			System.out.println(conn1);
			String insertSql = "insert into test(test) values(?)";
			PreparedStatement pstm = conn1.prepareStatement(insertSql);
			pstm.setString(1, "test12313");
			pstm.executeUpdate();
			conn1.commit();
			pstm.close();
			conn1.close();

			String sql2 = "select * from test where test=?";
			PreparedStatement pstm3 = conn2.prepareStatement(sql2);
			pstm3.setString(1, "test12313");
			ResultSet rs1 = pstm3.executeQuery();
			while (rs1.next()) {
				System.out.println("\n\n  ==" + rs1.getString(1));
			}

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

	}
 

/**
 * @see TradeServices#getClosedOrders(String)
 */
@Override
public Collection<OrderDataBean> getClosedOrders(String userID) throws Exception {
    Collection<OrderDataBean> orderDataBeans = new ArrayList<OrderDataBean>();
    Connection conn = null;
    try {
        if (Log.doTrace()) {
            Log.trace("TradeDirect:getClosedOrders - inSession(" + this.inSession + ")", userID);
        }

        conn = getConn();
        PreparedStatement stmt = getStatement(conn, getClosedOrdersSQL);
        stmt.setString(1, userID);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            OrderDataBean orderData = getOrderDataFromResultSet(rs);
            orderData.setOrderStatus("completed");
            updateOrderStatus(conn, orderData.getOrderID(), orderData.getOrderStatus());
            orderDataBeans.add(orderData);

        }

        stmt.close();
        commit(conn);
    } catch (Exception e) {
        Log.error("TradeDirect:getOrders -- error getting user orders", e);
        rollBack(conn, e);
    } finally {
        releaseConn(conn);
    }
    return orderDataBeans;
}
 

public void testNewGFETransactionNotCreatedForPKBasedOp() throws Exception {
  Connection conn = getConnection();
  Statement st = conn.createStatement();
  st.execute("create schema trade");    
  
  st.execute("create table trade.securities (sec_id int not null, " +
      "symbol varchar(10) not null, price decimal (30, 20), " +
      "exchange varchar(10) not null, tid int, " +
      "constraint sec_pk primary key (sec_id) ) " +
      " partition by column (tid) "+getSuffix());
  
  conn.setTransactionIsolation(getIsolationLevel());    
  PreparedStatement ps = conn.prepareStatement("insert into trade.securities values " +
      "(?, ?, ?, ?, ?)");
  for (int i = 0; i< 1 ; i++) {
    ps.setInt(1, i);
    ps.setString(2, "XXXX"+i);
    ps.setDouble(3, i);
    ps.setString(4, "nasdaq");
    ps.setInt(5, i);
    ps.executeUpdate();
  }
  //Get TxID of the transaction
  /*
  GemFireTransaction txn = TestUtil.getGFT((EmbedConnection)conn);
  TransactionId  tid = txn.getGFETransactionID();
  assertNull(GfxdConnectionHolder.getHolder().getConnectionID(tid));
  */
  conn.commit();
}
 

@Override
public JobInstance createJobInstance(final String name, final String jobXml) {
    Connection conn = null;
    PreparedStatement statement = null;
    ResultSet rs = null;

    try {
        conn = getConnection();
        statement = conn.prepareStatement(dictionary.getCreateJobInstanceWithJobXml(), Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, name);
        statement.setBytes(2, jobXml.getBytes(UTF_8));
        statement.executeUpdate();
        if (!conn.getAutoCommit()) {
            conn.commit();
        }
        rs = statement.getGeneratedKeys();
        if (rs.next()) {
            long jobInstanceID = rs.getLong(1);
            final JobInstanceImpl jobInstance = new JobInstanceImpl(jobInstanceID, jobXml);
            jobInstance.setJobName(name);
            return jobInstance;
        }
        return null;
    } catch (final SQLException e) {
        throw new PersistenceException(e);
    } finally {
        cleanupConnection(conn, rs, statement);
    }
}
 

/**
 * 得到消息列表
 * 
 * @param channelId
 * @param maxCreateTime
 * @param limit
 * @return
 */
@Override
public List<Message> listMessage(String channelId, long maxCreateAt, int limit) {
    Connection conn = ConnectionProvider.getConnection();
    if (conn == null) {
        throw new DAOException(CONNECTION_NOT_FOUND_EXCEPTION);
    }
    StringBuilder sql = new StringBuilder(256);
    sql.append("SELECT m.id,m.channel_id,m.sender_id,m.create_at,m.type,m.content,m.file_id,");
    sql.append("u.name,u.nickname,u.online_status,u.avatar_url,u.name_first_letter,");
    sql.append("f.name AS file_name,f.extension,f.size,f.mime_typ,f.width,f.height,f.path,f.thumb_width,f.thumb_height ");
    sql.append("FROM ").append(MESSAGE_TABLE).append(" m INNER JOIN ").append(USER_TABLE).append(" u ");
    sql.append("ON m.sender_id=u.id LEFT JOIN ").append(FILE_TABLE).append(" f ON m.file_id=f.id ");
    sql.append("WHERE m.channel_id=? AND delete_at=0");
    if (maxCreateAt > 0) {
        sql.append(" AND m.create_at<?");
    }
    sql.append(" ORDER BY m.create_at DESC LIMIT ?");
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int parameterIndex = 1;
    try {
        stmt = conn.prepareStatement(sql.toString());
        stmt.setString(parameterIndex, channelId);
        if (maxCreateAt > 0) {
            parameterIndex++;
            stmt.setLong(parameterIndex, maxCreateAt);
        }
        parameterIndex++;
        stmt.setInt(parameterIndex, limit);
        rs = stmt.executeQuery();
        return this.getMessageList(rs, limit);
    } catch (SQLException e) {
        throw new DAOException(e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.closeStatement(stmt);
    }
}
 

/**
 *	Generates random values and performs the inserts into the database
 */
public static int insertRow(PreparedStatement ps) {

	int rowsAdded = 0;
	try	{
		// Generate random values for the datatypes in the sample table
		Random rand = new Random();
		int intVal = Math.abs(rand.nextInt()%1000);

		String charVal = "Derby";

		synchronized(lock) {
			charVal += counter;
			counter++;
		}

		// Set parameter values
		ps.setInt(1, intVal);
		ps.setString(2,charVal);
		ps.setFloat(3, rand.nextFloat()*(float)Math.pow(10,Math.abs(rand.nextInt()%30)));
		ps.setLong(4,rand.nextLong()%10000);
		rowsAdded = ps.executeUpdate();
		return rowsAdded;
	} catch (Exception e) {
		e.printStackTrace();
		return 0;
	  }
}
 

@Override
public Pair<List<Long>, Map<Long, Double>> orderClustersByAggregateCapacity(final long id, final short capacityTypeForOrdering, final boolean isZone) {
    final TransactionLegacy txn = TransactionLegacy.currentTxn();
    PreparedStatement pstmt = null;
    final List<Long> result = new ArrayList<>();
    final Map<Long, Double> clusterCapacityMap = new HashMap<>();
    final StringBuilder sql = new StringBuilder();
    if (capacityTypeForOrdering != Capacity.CAPACITY_TYPE_CPU && capacityTypeForOrdering != Capacity.CAPACITY_TYPE_MEMORY) {
        sql.append(ORDER_CLUSTERS_BY_AGGREGATE_CAPACITY_PART1);
    } else {
        sql.append(ORDER_CLUSTERS_BY_AGGREGATE_OVERCOMMIT_CAPACITY_PART1);
    }

    if (isZone) {
        sql.append(" data_center_id = ?");
    } else {
        sql.append(" pod_id = ?");
    }
    if (capacityTypeForOrdering != Capacity.CAPACITY_TYPE_CPU && capacityTypeForOrdering != Capacity.CAPACITY_TYPE_MEMORY) {
        sql.append(ORDER_CLUSTERS_BY_AGGREGATE_CAPACITY_PART2);
    } else {
        sql.append(ORDER_CLUSTERS_BY_AGGREGATE_OVERCOMMIT_CAPACITY_PART2);
    }

    try {
        pstmt = txn.prepareAutoCloseStatement(sql.toString());
        pstmt.setLong(1, id);
        pstmt.setShort(2, capacityTypeForOrdering);

        if (capacityTypeForOrdering == Capacity.CAPACITY_TYPE_CPU) {
            pstmt.setString(3, "cpuOvercommitRatio");
        } else if (capacityTypeForOrdering == Capacity.CAPACITY_TYPE_MEMORY) {
            pstmt.setString(3, "memoryOvercommitRatio");
        }

        final ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            final Long clusterId = rs.getLong(1);
            result.add(clusterId);
            clusterCapacityMap.put(clusterId, rs.getDouble(2));
        }
        return new Pair<>(result, clusterCapacityMap);
    } catch (final SQLException e) {
        throw new CloudRuntimeException("DB Exception on: " + sql, e);
    }
}
 

private static Pair<Integer, List<Pair<Integer, Pair<Integer, Integer>>>> getNXCodeResult(MapleCharacter chr, String code) {
    MapleClient c = chr.getClient();
    List<Pair<Integer, Pair<Integer, Integer>>> ret = new LinkedList<>();
    try {
        if (!c.attemptCsCoupon()) {
            return new Pair<>(-5, null);
        }
        
        Connection con = DatabaseConnection.getConnection();
        PreparedStatement ps = con.prepareStatement("SELECT * FROM nxcode WHERE code = ?");
        ps.setString(1, code);
        
        ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            return new Pair<>(-1, null);
        }
        
        if (rs.getString("retriever") != null) {
            return new Pair<>(-2, null);
        }
        
        if (rs.getLong("expiration") < Server.getInstance().getCurrentTime()) {
            return new Pair<>(-3, null);
        }
        
        int codeid = rs.getInt("id");
        rs.close();
        ps.close();
        
        ret = getNXCodeItems(chr, con, codeid);
        if (ret == null) {
            return new Pair<>(-4, null);
        }
        
        ps = con.prepareStatement("UPDATE nxcode SET retriever = ? WHERE code = ?");
        ps.setString(1, chr.getName());
        ps.setString(2, code);
        ps.executeUpdate();
        
        ps.close();
        con.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    
    c.resetCsCoupon();
    return new Pair<>(0, ret);
}
 

@Test
public void testPreparedStatementConfig() throws SQLException {
    PreparedStatement preparedStatement = swConnection.prepareStatement("INSERT INTO test VALUES( ? , ?)", 1);
    preparedStatement.setInt(1, 1);
    preparedStatement.setString(2, "a");
    preparedStatement.getUpdateCount();
    preparedStatement.setFetchDirection(1);
    preparedStatement.getFetchDirection();
    preparedStatement.getResultSetConcurrency();
    preparedStatement.getResultSetType();
    preparedStatement.isClosed();
    preparedStatement.setPoolable(false);
    preparedStatement.isPoolable();
    preparedStatement.getWarnings();
    preparedStatement.clearWarnings();
    preparedStatement.setCursorName("test");
    preparedStatement.setMaxFieldSize(11);
    preparedStatement.getMaxFieldSize();
    preparedStatement.setMaxRows(10);
    preparedStatement.getMaxRows();
    preparedStatement.getParameterMetaData();
    preparedStatement.setEscapeProcessing(true);
    preparedStatement.setFetchSize(1);
    preparedStatement.getFetchSize();
    preparedStatement.setQueryTimeout(1);
    preparedStatement.getQueryTimeout();
    Connection connection = preparedStatement.getConnection();

    preparedStatement.execute();

    preparedStatement.getMoreResults();
    preparedStatement.getMoreResults(1);
    preparedStatement.getResultSetHoldability();
    preparedStatement.getMetaData();
    preparedStatement.getResultSet();

    preparedStatement.close();
    verify(mysqlPreparedStatement).getUpdateCount();
    verify(mysqlPreparedStatement).getMoreResults();
    verify(mysqlPreparedStatement).setFetchDirection(anyInt());
    verify(mysqlPreparedStatement).getFetchDirection();
    verify(mysqlPreparedStatement).getResultSetType();
    verify(mysqlPreparedStatement).isClosed();
    verify(mysqlPreparedStatement).setPoolable(anyBoolean());
    verify(mysqlPreparedStatement).getWarnings();
    verify(mysqlPreparedStatement).clearWarnings();
    verify(mysqlPreparedStatement).setCursorName(anyString());
    verify(mysqlPreparedStatement).setMaxFieldSize(anyInt());
    verify(mysqlPreparedStatement).getMaxFieldSize();
    verify(mysqlPreparedStatement).setMaxRows(anyInt());
    verify(mysqlPreparedStatement).getMaxRows();
    verify(mysqlPreparedStatement).setEscapeProcessing(anyBoolean());
    verify(mysqlPreparedStatement).getResultSetConcurrency();
    verify(mysqlPreparedStatement).getResultSetConcurrency();
    verify(mysqlPreparedStatement).getResultSetType();
    verify(mysqlPreparedStatement).getMetaData();
    verify(mysqlPreparedStatement).getParameterMetaData();
    verify(mysqlPreparedStatement).getMoreResults(anyInt());
    verify(mysqlPreparedStatement).setFetchSize(anyInt());
    verify(mysqlPreparedStatement).getFetchSize();
    verify(mysqlPreparedStatement).getQueryTimeout();
    verify(mysqlPreparedStatement).setQueryTimeout(anyInt());
    verify(mysqlPreparedStatement).getResultSet();
    assertThat(connection, CoreMatchers.<Connection>is(swConnection));
}
 

private void insertLogData(Connection pCtx) {
  int currentBatchSize = 0;
  try {
    PreparedStatement prepareStatement = pCtx
        .prepareStatement(SELECT_LAST_UPDATED_BO_LOG_RECORD);
    PreparedStatement insertStmt = pCtx
        .prepareStatement(INSERT_INTO_LOG_TABLE_FOR_PURGED_RECORDS);
    ResultSet listOfAllPurgeReadyBOIds = getListOfAllPurgeReadyBOIdsFromTempTable(pCtx);
    if (null != listOfAllPurgeReadyBOIds) {
      while (listOfAllPurgeReadyBOIds.next()) {
        String boId = listOfAllPurgeReadyBOIds.getString("BO_TXN_ID");
        prepareStatement.setString(1, boId);
        ResultSet resultSet = prepareStatement.executeQuery();
        while (resultSet.next()) {
          insertStmt.setString(1, resultSet.getString("BO_TXN_ID"));
          insertStmt.setString(2, resultSet.getString("BACKOFFICE_CODE"));
          insertStmt.setString(3, resultSet.getString("CHANNEL_NAME"));
          insertStmt.setString(4, resultSet.getString("TXN_TYPE"));
          insertStmt.setInt(5, MARK_AS_TO_PURGE);
          insertStmt.setString(6, resultSet.getString("MATCH_STATUS"));
          insertStmt.setString(7, resultSet.getString("MATCH_CATEG_ID"));
          insertStmt.setString(8, resultSet.getString("HIT_STATUS"));
          insertStmt.setString(9, resultSet.getString("ACTUAL_VALUE_DATE"));
          insertStmt.setString(10, "SCREENING_TIME");
          insertStmt.addBatch();
          currentBatchSize++;
          if (currentBatchSize > maxBatchSize) {
            insertStmt.executeBatch();
            pCtx.commit();
            currentBatchSize = 0;
          }
          break;
        }
      }
      insertStmt.executeBatch();
      pCtx.commit();
    }
  } catch (Exception e) {
    Log.getLogWriter().error("Component: UseCase1-SECURITAS:appstat|Event Severity: Fatal|Event Class: MatchingEngine|Description: Issue while insering raw data.-insertLogData Summary:  " + TestHelper.getStackTrace(e));
  }
}
 

@Test
public void testPointInTimeDeleteUngroupedAggregation() throws Exception {
    String updateStmt = 
        "upsert into " +
        "ATABLE(" +
        "    ORGANIZATION_ID, " +
        "    ENTITY_ID, " +
        "    A_STRING) " +
        "VALUES (?, ?, ?)";
    
    // Override value that was set at creation time
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 1); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);

    // Remove column value at ts + 1 (i.e. equivalent to setting the value to null)
    Connection conn = DriverManager.getConnection(url, props);
    PreparedStatement stmt = conn.prepareStatement(updateStmt);
    stmt.setString(1, tenantId);
    stmt.setString(2, ROW7);
    stmt.setString(3, null);
    stmt.execute();
    
    // Delete row 
    stmt = conn.prepareStatement("delete from atable where organization_id=? and entity_id=?");
    stmt.setString(1, tenantId);
    stmt.setString(2, ROW5);
    stmt.execute();
    conn.commit();
    conn.close();
    
    // Delete row at timestamp 3. This should not be seen by the query executing
    // Remove column value at ts + 1 (i.e. equivalent to setting the value to null)
    Connection futureConn = DriverManager.getConnection(PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 3), props);
    stmt = futureConn.prepareStatement("delete from atable where organization_id=? and entity_id=?");
    stmt.setString(1, tenantId);
    stmt.setString(2, ROW6);
    stmt.execute();
    futureConn.commit();
    futureConn.close();

    String query = "SELECT count(1) FROM atable WHERE organization_id=? and a_string = ?";
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    PreparedStatement statement = conn.prepareStatement(query);
    statement.setString(1, tenantId);
    statement.setString(2, B_VALUE);
    ResultSet rs = statement.executeQuery();
    assertTrue(rs.next());
    assertEquals(2, rs.getLong(1));
    assertFalse(rs.next());
    conn.close();
}