Java Code Examples for java.sql.Statement#setMaxRows()

private void applyHints(Statement statement, Hints hints) throws SQLException {
	Integer fetchSize = (Integer)hints.get(HintEnum.fetchSize);
	
	if(fetchSize != null && fetchSize > 0) {
           statement.setFetchSize(fetchSize);
       }

	Integer maxRows = (Integer)hints.get(HintEnum.maxRows);
	if (maxRows != null && maxRows > 0) {
           statement.setMaxRows(maxRows);
       }

       Integer timeout = (Integer)hints.get(HintEnum.timeout);
       if (timeout == null || timeout < 0) {
           timeout = StatusManager.getTimeoutMarkdown().getTimeoutThreshold();
       }

	statement.setQueryTimeout(timeout);
}
 

private Statement createInnerStatement(Connection conn, boolean isBatch) throws SQLException {
	Statement stmt;
	if (isBatch) {
		stmt = conn.createStatement();
	} else {
		int tmpResultSetHoldability = this.resultSetHoldability;
		if (tmpResultSetHoldability == -1) {
			tmpResultSetHoldability = conn.getHoldability();
		}

		stmt = conn.createStatement(this.resultSetType, this.resultSetConcurrency, tmpResultSetHoldability);
	}

	stmt.setQueryTimeout(queryTimeout);
	stmt.setFetchSize(fetchSize);
	stmt.setMaxRows(maxRows);

	setInnerStatement(stmt);
	return stmt;
}
 

@Test
  public void testMaxRows() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    String sqlSelect = "select seq4() from table(generator(rowcount=>3))";
    assertEquals(0, statement.getMaxRows());

//    statement.setMaxRows(1);
//    assertEquals(1, statement.getMaxRows());
    ResultSet rs = statement.executeQuery(sqlSelect);
    int resultSizeCount = getSizeOfResultSet(rs);
//    assertEquals(1, resultSizeCount);

    statement.setMaxRows(0);
    rs = statement.executeQuery(sqlSelect);
//    assertEquals(3, getSizeOfResultSet(rs));

    statement.setMaxRows(-1);
    rs = statement.executeQuery(sqlSelect);
//    assertEquals(3, getSizeOfResultSet(rs));
    statement.close();

    connection.close();
  }
 

@Override
public ResultSet getData(IDataReader dataReader, Object... resources) {
	logger.debug("IN");
	Statement stmt = (Statement) resources[0];
	ResultSet resultSet = null;
	try {
		if (getMaxResults() > 0) {
			stmt.setMaxRows(getMaxResults());
		}
		String sqlQuery = getStatement();
		LogMF.info(logger, "Executing query:\n{0}", sqlQuery);
		Monitor timeToExecuteStatement = MonitorFactory.start("Knowage.JDBCDataProxy.executeStatement:" + sqlQuery);
		try {
			resultSet = stmt.executeQuery(sqlQuery);
		} finally {
			timeToExecuteStatement.stop();
		}
		LogMF.debug(logger, "Executed query:\n{0}", sqlQuery);
		return resultSet;
	} catch (SQLException e) {
		throw new SpagoBIRuntimeException(e);
	} finally {
		logger.debug("OUT");
	}
}
 

/**
 * Positive tests for forward only cursors.
 *
 * This method tests forward only cursors.
 *
 *
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */

public void testForwardOnlyPositive() throws SQLException {
    Connection conn = getConnection();
    ResultSet rs;
    Statement s_f_r = createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    // We should have gotten no warnings and a read only forward only cursor
    //JDBC.assertNoWarnings(conn.getWarnings());
    conn.clearWarnings();

    // Verify that setMaxRows(4) succeeds
    s_f_r.setMaxRows(5);
    assertEquals(5, s_f_r.getMaxRows());

    rs = s_f_r.executeQuery("values 1, 2, 3, 4, 5, 6");
    // Iterate straight thru RS, expect only 5 rows.
    JDBC.assertDrainResults(rs, 5);

    s_f_r.close();

}
 

/**
 * 会调用setBaseStatement以关闭已有的Statement
 */
private Statement createStatementInternal(Connection conn, String sql, boolean isBatch) throws SQLException {
    Statement stmt;
    if (isBatch) {
        stmt = conn.createStatement();
    } else {
        int resultSetHoldability = this.resultSetHoldability;
        if (resultSetHoldability == -1) {// 未调用过setResultSetHoldability
            resultSetHoldability = conn.getHoldability();
        }
        stmt = conn.createStatement(this.resultSetType, this.resultSetConcurrency, resultSetHoldability);
    }

    setBaseStatement(stmt); // 会关闭已有的Statement
    stmt.setQueryTimeout(queryTimeout); // 这句也有可能抛出异常，放在最后
    stmt.setFetchSize(fetchSize);
    stmt.setMaxRows(maxRows);
    // 填充sql元信息
    fillSqlMetaData(stmt, sql);
    return stmt;
}
 

/**
 * Sets the max number of rows that should be returned from executing a
 * statement. The operation is automatically bypassed if Jive knows that the
 * the JDBC driver or database doesn't support it.
 *
 * @param stmt    the Statement to set the max number of rows for.
 * @param maxRows the max number of rows to return.
 */
public static void setMaxRows(Statement stmt, int maxRows) {
    if (isMaxRowsSupported()) {
        try {
            stmt.setMaxRows(maxRows);
        }
        catch (Throwable t) {
            // Ignore. Exception may happen if the driver doesn't support
            // this operation and we didn't set meta-data correctly.
            // However, it is a good idea to update the meta-data so that
            // we don't have to incur the cost of catching an exception
            // each time.
            Log.error("Disabling JDBC method stmt.setMaxRows(maxRows).", t);
            maxRowsSupported = false;
        }
    }
}
 

private void applyHints(Statement statement, DalHints hints) throws SQLException {
	Integer fetchSize = (Integer)hints.get(DalHintEnum.fetchSize);
	
	if(fetchSize != null && fetchSize > 0)
		statement.setFetchSize(fetchSize);

	Integer maxRows = (Integer)hints.get(DalHintEnum.maxRows);
	if (maxRows != null && maxRows > 0)
		statement.setMaxRows(maxRows);

	Integer timeout = (Integer)hints.get(DalHintEnum.timeout);
	if (timeout != null && timeout >= 0) {
		statement.setQueryTimeout(timeout);
	} else {
		timeout = DalStatusManager.getTimeoutMarkdown().getTimeoutThreshold();
		if (timeout >= 0)
			statement.setQueryTimeout(timeout);
	}
	
}
 

/**
 * 会调用setBaseStatement以关闭已有的Statement
 */
private Statement createStatementInternal(Connection conn, String sql, boolean isBatch) throws SQLException {
    Statement stmt;
    if (isBatch) {
        stmt = conn.createStatement();
    } else {
        int resultSetHoldability = this.resultSetHoldability;
        if (resultSetHoldability == -1) {// 未调用过setResultSetHoldability
            resultSetHoldability = conn.getHoldability();
        }
        stmt = conn.createStatement(this.resultSetType, this.resultSetConcurrency, resultSetHoldability);
    }

    setBaseStatement(stmt); // 会关闭已有的Statement
    stmt.setQueryTimeout(queryTimeout); // 这句也有可能抛出异常，放在最后
    stmt.setFetchSize(fetchSize);
    stmt.setMaxRows(maxRows);
    // 填充sql元信息
    if (stmt instanceof DataChannel) {
        ((DataChannel) stmt).fillMetaData(sqlMetaData);
    }
    return stmt;
}
 

/**
 * Positive tests for forward only cursors.
 * 
 * This method tests forward only cursors.
 * 
 * 
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */

public void testForwardOnlyPositive() throws SQLException {
    Connection conn = getConnection();
    ResultSet rs;
    Statement s_f_r = createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    // We should have gotten no warnings and a read only forward only cursor
    JDBC.assertNoWarnings(conn.getWarnings());
    conn.clearWarnings();

    // Verify that setMaxRows(4) succeeds
    s_f_r.setMaxRows(5);
    assertEquals(5, s_f_r.getMaxRows());

    rs = s_f_r.executeQuery("values 1, 2, 3, 4, 5, 6");
    // Iterate straight thru RS, expect only 5 rows.
    JDBC.assertDrainResults(rs, 5);
    
    s_f_r.close();

}
 

private static void sql(final Connection connection, String sqlText)
throws SQLException
{
  // Create a warehouse for the test
  Statement stmt = connection.createStatement();
  stmt.setMaxRows(1);
  boolean hasResultSet = stmt.execute(sqlText);
  if (hasResultSet)
  {
    assertTrue(stmt.getResultSet().next());
  }
  stmt.close();
}
 

@Test
public void testJoinWithSetMaxRows() throws Exception {
    Connection conn = getConnection();
    String [] queries = new String[2];
    queries[0] = "SELECT \"order_id\", i.name, quantity FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN "
            + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o ON o.\"item_id\" = i.\"item_id\"";
    queries[1] = "SELECT o.\"order_id\", i.name, o.quantity FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " 
            + "(SELECT \"order_id\", \"item_id\", quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") o " 
            + "ON o.\"item_id\" = i.\"item_id\"";
    try {
        for (String query : queries) {
            Statement statement = conn.createStatement();
            statement.setMaxRows(4);
            ResultSet rs = statement.executeQuery(query);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000001");
            assertEquals(rs.getString(2), "T1");
            assertEquals(rs.getInt(3), 1000);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000003");
            assertEquals(rs.getString(2), "T2");
            assertEquals(rs.getInt(3), 3000);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000005");
            assertEquals(rs.getString(2), "T3");
            assertEquals(rs.getInt(3), 5000);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000002");
            assertEquals(rs.getString(2), "T6");
            assertEquals(rs.getInt(3), 2000);

            assertFalse(rs.next());
            
            rs = statement.executeQuery("EXPLAIN " + query);
            assertPlansEqual(plans[21], QueryUtil.getExplainPlan(rs));
        }
    } finally {
        conn.close();
    }
}
 

/**
 * Prepare the given JDBC Statement (or PreparedStatement or CallableStatement),
 * applying statement settings such as fetch size, max rows, and query timeout.
 * @param stmt the JDBC Statement to prepare
 * @throws SQLException if thrown by JDBC API
 * @see #setFetchSize
 * @see #setMaxRows
 * @see #setQueryTimeout
 * @see org.springframework.jdbc.datasource.DataSourceUtils#applyTransactionTimeout
 */
protected void applyStatementSettings(Statement stmt) throws SQLException {
	int fetchSize = getFetchSize();
	if (fetchSize > 0) {
		stmt.setFetchSize(fetchSize);
	}
	int maxRows = getMaxRows();
	if (maxRows > 0) {
		stmt.setMaxRows(maxRows);
	}
	DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
}
 

void setStatementState(Statement oldStatement, Statement newStatement) throws SQLException {
	if (cursorName != null)
		newStatement.setCursorName(cursorName);
	if (escapeProcessing != null)
		newStatement.setEscapeProcessing(escapeProcessing);

	newStatement.setFetchDirection(oldStatement.getFetchDirection());
	newStatement.setFetchSize(oldStatement.getFetchSize());
	newStatement.setMaxFieldSize(oldStatement.getMaxFieldSize());
	newStatement.setMaxRows(oldStatement.getMaxRows());
	newStatement.setQueryTimeout(oldStatement.getQueryTimeout());
}
 

/**
 * Prepare the given JDBC Statement (or PreparedStatement or CallableStatement),
 * applying statement settings such as fetch size, max rows, and query timeout.
 * @param stmt the JDBC Statement to prepare
 * @throws SQLException if thrown by JDBC API
 * @see #setFetchSize
 * @see #setMaxRows
 * @see #setQueryTimeout
 * @see org.springframework.jdbc.datasource.DataSourceUtils#applyTransactionTimeout
 */
protected void applyStatementSettings(Statement stmt) throws SQLException {
	int fetchSize = getFetchSize();
	if (fetchSize != -1) {
		stmt.setFetchSize(fetchSize);
	}
	int maxRows = getMaxRows();
	if (maxRows != -1) {
		stmt.setMaxRows(maxRows);
	}
	DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
}
 

protected void processStatementAttr(Statement s, SQLRequest sqlRequest) throws SQLException {
    Integer statementMaxRows = BackdoorToggles.getStatementMaxRows();
    if (statementMaxRows != null) {
        logger.info("Setting current statement's max rows to {}", statementMaxRows);
        s.setMaxRows(statementMaxRows);
    }
}
 

@Test
public void assertSetMaxRows() throws SQLException {
    for (Statement each : statements.values()) {
        each.executeQuery(sql);
        each.setMaxRows(10);
        assertThat(each.getMaxRows(), is(10));
    }
}
 

@POST
  @Path("setMaxRows")    
  public void setMaxRows(MaxRowsDTO maxRowsDTO) {
  	Statement statement = StatementHolder.get().get(maxRowsDTO.id);
  	try {
	statement.setMaxRows(maxRowsDTO.max);
} catch (SQLException e) {
	// TODO
	throw new RuntimeException(e);
}		
  }
 

/**
 * Sets the provided maximum number of rows on the given statement.
 *
 * @param statement The JDBC Statement to operate on
 * @param maxRowCount The maximum number of rows which should be returned for the query
 */
void setMaxRows(Statement statement, long maxRowCount) throws SQLException {
  // Special handling of maxRowCount as JDBC 0 is unlimited, our meta 0 row
  if (maxRowCount > 0) {
    AvaticaUtils.setLargeMaxRows(statement, maxRowCount);
  } else if (maxRowCount < 0) {
    statement.setMaxRows(0);
  }
}
 

@Override
public final void setMaxRows(final int max) throws SQLException {
    if (getRoutedStatements().isEmpty()) {
        recordMethodInvocation(recordTargetClass, "setMaxRows", new Class[] {int.class}, new Object[] {max});
        return;
    }
    for (Statement each : getRoutedStatements()) {
        each.setMaxRows(max);
    }
}