Example usage for java.sql Statement setMaxRows

List of usage examples for java.sql Statement setMaxRows

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. setMaxRows

Introduction

In this page you can find the example usage for java.sql Statement setMaxRows.

Prototype

void setMaxRows(int max) throws SQLException;

Source Link

Document

Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number.

Usage

From source file:org.apache.calcite.test.CalciteAssert.java

static void assertQuery(Connection connection, String sql, int limit, boolean materializationsEnabled,
        List<Pair<Hook, Function>> hooks, Function<ResultSet, Void> resultChecker,
        Function<Integer, Void> updateChecker, Function<Throwable, Void> exceptionChecker) throws Exception {
    final String message = "With materializationsEnabled=" + materializationsEnabled + ", limit=" + limit;
    try (final Closer closer = new Closer()) {
        if (connection instanceof CalciteConnection) {
            CalciteConnection calciteConnection = (CalciteConnection) connection;
            calciteConnection.getProperties().setProperty(
                    CalciteConnectionProperty.MATERIALIZATIONS_ENABLED.camelName(),
                    Boolean.toString(materializationsEnabled));
            calciteConnection.getProperties().setProperty(
                    CalciteConnectionProperty.CREATE_MATERIALIZATIONS.camelName(),
                    Boolean.toString(materializationsEnabled));
        }//  w  w  w  .j av  a2s .  c  o m
        for (Pair<Hook, Function> hook : hooks) {
            closer.add(hook.left.addThread(hook.right));
        }
        Statement statement = connection.createStatement();
        statement.setMaxRows(limit <= 0 ? limit : Math.max(limit, 1));
        ResultSet resultSet = null;
        Integer updateCount = null;
        try {
            if (updateChecker == null) {
                resultSet = statement.executeQuery(sql);
            } else {
                updateCount = statement.executeUpdate(sql);
            }
            if (exceptionChecker != null) {
                exceptionChecker.apply(null);
                return;
            }
        } catch (Exception | Error e) {
            if (exceptionChecker != null) {
                exceptionChecker.apply(e);
                return;
            }
            throw e;
        }
        if (resultChecker != null) {
            resultChecker.apply(resultSet);
        }
        if (updateChecker != null) {
            updateChecker.apply(updateCount);
        }
        if (resultSet != null) {
            resultSet.close();
        }
        statement.close();
        connection.close();
    } catch (Error | RuntimeException e) {
        // We ignore extended message for non-runtime exception, however
        // it does not matter much since it is better to have AssertionError
        // at the very top level of the exception stack.
        throw e;
    } catch (Throwable e) {
        throw new RuntimeException(message, e);
    }
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception {
    boolean isPartitionTable = tableName.equals(partitionedTableName);

    Statement stmt = con.createStatement();
    if (maxRows >= 0) {
        stmt.setMaxRows(maxRows);
    }/*w ww  .j av a2s .c o  m*/
    if (fetchSize > 0) {
        stmt.setFetchSize(fetchSize);
        assertEquals(fetchSize, stmt.getFetchSize());
    }

    // JDBC says that 0 means return all, which is the default
    int expectedMaxRows = maxRows < 1 ? 0 : maxRows;

    assertNotNull("Statement is null", stmt);
    assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows());
    assertFalse("Statement should not be closed", stmt.isClosed());

    ResultSet res;

    // run some queries
    res = stmt.executeQuery("select * from " + tableName);
    assertNotNull("ResultSet is null", res);
    assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet());
    assertEquals("get update count not as expected", 0, stmt.getUpdateCount());
    int i = 0;

    ResultSetMetaData meta = res.getMetaData();
    int expectedColCount = isPartitionTable ? 3 : 2;
    assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount());

    String colQualifier = ((tableName != null) && !tableName.isEmpty()) ? tableName.toLowerCase() + "." : "";
    boolean moreRow = res.next();
    while (moreRow) {
        try {
            i++;
            assertEquals(res.getInt(1), res.getInt(colQualifier + "under_col"));
            assertEquals(res.getString(1), res.getString(colQualifier + "under_col"));
            assertEquals(res.getString(2), res.getString(colQualifier + "value"));
            if (isPartitionTable) {
                assertEquals(res.getString(3), partitionedColumnValue);
                assertEquals(res.getString(3), res.getString(colQualifier + partitionedColumnName));
            }
            assertFalse("Last result value was not null", res.wasNull());
            assertNull("No warnings should be found on ResultSet", res.getWarnings());
            res.clearWarnings(); // verifying that method is supported

            // System.out.println(res.getString(1) + " " + res.getString(2));
            assertEquals("getInt and getString don't align for the same result value",
                    String.valueOf(res.getInt(1)), res.getString(1));
            assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2));
            moreRow = res.next();
        } catch (SQLException e) {
            System.out.println(e.toString());
            e.printStackTrace();
            throw new Exception(e.toString());
        }
    }

    // supposed to get 500 rows if maxRows isn't set
    int expectedRowCount = maxRows > 0 ? maxRows : 500;
    assertEquals("Incorrect number of rows returned", expectedRowCount, i);

    // should have no more rows
    assertEquals(false, moreRow);

    assertNull("No warnings should be found on statement", stmt.getWarnings());
    stmt.clearWarnings(); // verifying that method is supported

    assertNull("No warnings should be found on connection", con.getWarnings());
    con.clearWarnings(); // verifying that method is supported

    stmt.close();
    assertTrue("Statement should be closed", stmt.isClosed());
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception {
    boolean isPartitionTable = tableName.equals(partitionedTableName);

    Statement stmt = con.createStatement();
    if (maxRows >= 0) {
        stmt.setMaxRows(maxRows);
    }//from   w ww  .  j a v  a 2s.com
    if (fetchSize > 0) {
        stmt.setFetchSize(fetchSize);
        assertEquals(fetchSize, stmt.getFetchSize());
    }

    // JDBC says that 0 means return all, which is the default
    int expectedMaxRows = maxRows < 1 ? 0 : maxRows;

    assertNotNull("Statement is null", stmt);
    assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows());
    assertFalse("Statement should not be closed", stmt.isClosed());

    ResultSet res;

    // run some queries
    res = stmt.executeQuery("select * from " + tableName);
    assertNotNull("ResultSet is null", res);
    assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet());
    assertEquals("get update count not as expected", -1, stmt.getUpdateCount());
    int i = 0;

    ResultSetMetaData meta = res.getMetaData();
    int expectedColCount = isPartitionTable ? 3 : 2;
    assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount());

    boolean moreRow = res.next();
    while (moreRow) {
        try {
            i++;
            assertEquals(res.getInt(1), res.getInt(tableName + ".under_col"));
            assertEquals(res.getInt(1), res.getInt("under_col"));
            assertEquals(res.getString(1), res.getString(tableName + ".under_col"));
            assertEquals(res.getString(1), res.getString("under_col"));
            assertEquals(res.getString(2), res.getString(tableName + ".value"));
            assertEquals(res.getString(2), res.getString("value"));
            if (isPartitionTable) {
                assertEquals(res.getString(3), partitionedColumnValue);
                assertEquals(res.getString(3), res.getString(partitionedColumnName));
                assertEquals(res.getString(3), res.getString(tableName + "." + partitionedColumnName));
            }
            assertFalse("Last result value was not null", res.wasNull());
            assertNull("No warnings should be found on ResultSet", res.getWarnings());
            res.clearWarnings(); // verifying that method is supported

            // System.out.println(res.getString(1) + " " + res.getString(2));
            assertEquals("getInt and getString don't align for the same result value",
                    String.valueOf(res.getInt(1)), res.getString(1));
            assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2));
            moreRow = res.next();
        } catch (SQLException e) {
            System.out.println(e.toString());
            e.printStackTrace();
            throw new Exception(e.toString());
        }
    }

    // supposed to get 500 rows if maxRows isn't set
    int expectedRowCount = maxRows > 0 ? maxRows : 500;
    assertEquals("Incorrect number of rows returned", expectedRowCount, i);

    // should have no more rows
    assertEquals(false, moreRow);

    assertNull("No warnings should be found on statement", stmt.getWarnings());
    stmt.clearWarnings(); // verifying that method is supported

    assertNull("No warnings should be found on connection", con.getWarnings());
    con.clearWarnings(); // verifying that method is supported

    stmt.close();
    assertTrue("Statement should be closed", stmt.isClosed());
}

From source file:org.apache.kylin.jdbc.ITJDBCDriverTest.java

@Test
public void testResultSetWithMaxRows() throws Exception {
    String sql = "select LSTG_FORMAT_NAME, sum(price) as GMV, count(1) as TRANS_CNT from test_kylin_fact \n"
            + " group by LSTG_FORMAT_NAME ";

    Connection conn = getConnection();
    Statement statement = conn.createStatement();
    statement.setMaxRows(2);

    statement.execute(sql);//from  w  ww.  j  av a2s.com

    ResultSet rs = statement.getResultSet();

    int count = 0;
    while (rs.next()) {
        count++;
        String lstg = rs.getString(1);
        double gmv = rs.getDouble(2);
        int trans_count = rs.getInt(3);

        System.out.println(
                "Get a line: LSTG_FORMAT_NAME=" + lstg + ", GMV=" + gmv + ", TRANS_CNT=" + trans_count);
    }

    Assert.assertTrue(count == 2);
    statement.close();
    rs.close();
    conn.close();

}

From source file:org.apache.kylin.rest.service.QueryService.java

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);
    }/*from  www .  ja  va2 s. c  o  m*/
}

From source file:org.apache.metamodel.jdbc.JdbcDataContextTest.java

public void testMaxRows() throws Exception {
    final Connection realCon = getTestDbConnection();
    final Statement realStatement = realCon.createStatement();

    final Connection mockCon = EasyMock.createMock(Connection.class);
    final Statement mockStatement = EasyMock.createMock(Statement.class);

    EasyMock.expect(mockCon.getMetaData()).andReturn(realCon.getMetaData()).anyTimes();

    EasyMock.expect(mockCon.getAutoCommit()).andReturn(true);

    EasyMock.expect(mockCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY))
            .andReturn(mockStatement);/*  w  ww .j  a  v  a 2  s . c om*/

    EasyMock.expect(mockStatement.getFetchSize()).andReturn(10);
    mockStatement.setFetchSize(EasyMock.anyInt());
    mockStatement.setMaxRows(3);
    EasyMock.expectLastCall().andThrow(new SQLException("I wont allow max rows"));

    EasyMock.expect(mockStatement.executeQuery(
            "SELECT a.\"CUSTOMERNUMBER\", a.\"CUSTOMERNAME\", a.\"CONTACTLASTNAME\", a.\"CONTACTFIRSTNAME\", "
                    + "a.\"PHONE\", a.\"ADDRESSLINE1\", a.\"ADDRESSLINE2\", a.\"CITY\", a.\"STATE\", "
                    + "a.\"POSTALCODE\", a.\"COUNTRY\", a.\"SALESREPEMPLOYEENUMBER\", "
                    + "a.\"CREDITLIMIT\" FROM PUBLIC.\"CUSTOMERS\" a"))
            .andReturn(realStatement
                    .executeQuery("SELECT a.\"CUSTOMERNUMBER\", a.\"CUSTOMERNAME\", a.\"CONTACTLASTNAME\", "
                            + "a.\"CONTACTFIRSTNAME\", a.\"PHONE\", a.\"ADDRESSLINE1\", a.\"ADDRESSLINE2\", a.\"CITY\", "
                            + "a.\"STATE\", a.\"POSTALCODE\", a.\"COUNTRY\", a.\"SALESREPEMPLOYEENUMBER\", "
                            + "a.\"CREDITLIMIT\" FROM PUBLIC.\"CUSTOMERS\" a"));

    mockStatement.close();

    EasyMock.replay(mockCon, mockStatement);

    JdbcDataContext dc = new JdbcDataContext(mockCon, new TableType[] { TableType.TABLE, TableType.VIEW },
            null);
    dc.setQueryRewriter(new DefaultQueryRewriter(dc));
    Schema schema = dc.getDefaultSchema();

    Query q = new Query().setMaxRows(3);
    Table table = schema.getTables()[0];
    q.from(table, "a");
    q.select(table.getColumns());
    assertEquals(
            "SELECT a.\"CUSTOMERNUMBER\", a.\"CUSTOMERNAME\", a.\"CONTACTLASTNAME\", a.\"CONTACTFIRSTNAME\", "
                    + "a.\"PHONE\", a.\"ADDRESSLINE1\", a.\"ADDRESSLINE2\", a.\"CITY\", a.\"STATE\", a.\"POSTALCODE\", "
                    + "a.\"COUNTRY\", a.\"SALESREPEMPLOYEENUMBER\", a.\"CREDITLIMIT\" FROM PUBLIC.\"CUSTOMERS\" a",
            q.toString());
    DataSet result = dc.executeQuery(q);
    assertTrue(result.next());
    assertEquals(
            "Row[values=[103, Atelier graphique, Schmitt, Carine, 40.32.2555, 54, rue Royale, null, Nantes, null, "
                    + "44000, France, 1370, 21000.0]]",
            result.getRow().toString());
    assertTrue(result.next());
    assertTrue(result.next());
    assertFalse(result.next());

    result.close();

    EasyMock.verify(mockCon, mockStatement);
    realStatement.close();
}

From source file:org.apache.zeppelin.hopshive.HopsHiveInterpreter.java

private InterpreterResult executeSql(String propertyKey, String sql, InterpreterContext interpreterContext) {
    Connection connection;/*from   w w w  .  j a  v  a2  s.c o m*/
    Statement statement;
    ResultSet resultSet = null;
    String paragraphId = interpreterContext.getParagraphId();
    String user = interpreterContext.getAuthenticationInfo().getUser();

    boolean splitQuery = false;
    String splitQueryProperty = getProperty(String.format("%s.%s", propertyKey, SPLIT_QURIES_KEY));
    if (StringUtils.isNotBlank(splitQueryProperty) && splitQueryProperty.equalsIgnoreCase("true")) {
        splitQuery = true;
    }

    InterpreterResult interpreterResult = new InterpreterResult(InterpreterResult.Code.SUCCESS);
    try {
        connection = getConnection(propertyKey, interpreterContext);
        if (connection == null) {
            return new InterpreterResult(Code.ERROR, "Prefix not found.");
        }

        List<String> sqlArray;
        if (splitQuery) {
            sqlArray = splitSqlQueries(sql);
        } else {
            sqlArray = Arrays.asList(sql);
        }

        for (int i = 0; i < sqlArray.size(); i++) {
            String sqlToExecute = sqlArray.get(i);
            statement = connection.createStatement();

            // fetch n+1 rows in order to indicate there's more rows available (for large selects)
            statement.setFetchSize(getMaxResult());
            statement.setMaxRows(getMaxResult() + 1);

            if (statement == null) {
                return new InterpreterResult(Code.ERROR, "Prefix not found.");
            }

            try {
                getJDBCConfiguration(user).saveStatement(paragraphId, statement);

                boolean isResultSetAvailable = statement.execute(sqlToExecute);
                getJDBCConfiguration(user).setConnectionInDBDriverPoolSuccessful(propertyKey);
                if (isResultSetAvailable) {
                    resultSet = statement.getResultSet();

                    // Regards that the command is DDL.
                    if (isDDLCommand(statement.getUpdateCount(), resultSet.getMetaData().getColumnCount())) {
                        interpreterResult.add(InterpreterResult.Type.TEXT, "Query executed successfully.");
                    } else {
                        String results = getResults(resultSet,
                                !containsIgnoreCase(sqlToExecute, EXPLAIN_PREDICATE));
                        interpreterResult.add(results);
                        if (resultSet.next()) {
                            interpreterResult.add(ResultMessages.getExceedsLimitRowsMessage(getMaxResult(),
                                    String.format("%s.%s", COMMON_KEY, MAX_LINE_KEY)));
                        }
                    }
                } else {
                    // Response contains either an update count or there are no results.
                    int updateCount = statement.getUpdateCount();
                    interpreterResult.add(InterpreterResult.Type.TEXT,
                            "Query executed successfully. Affected rows : " + updateCount);
                }
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        /*ignored*/ }
                }
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        /*ignored*/ }
                }
            }
        }
        //In case user ran an insert/update/upsert statement
        if (connection != null) {
            try {
                if (!connection.getAutoCommit()) {
                    connection.commit();
                }
            } catch (SQLException e) {
                /*ignored*/ }
        }
        getJDBCConfiguration(user).removeStatement(paragraphId);
    } catch (Throwable e) {
        logger.error("Cannot run " + sql, e);
        String errorMsg = Throwables.getStackTraceAsString(e);
        interpreterResult.add(errorMsg);
        return new InterpreterResult(Code.ERROR, interpreterResult.message());
    }
    return interpreterResult;
}

From source file:org.bml.util.geo.util.geolite.GeoLiteCityBlock.java

public static Map<Integer, GeoLiteCityBlock> readFromDB(ComboPooledDataSource dataSource) {
    Map<Integer, GeoLiteCityBlock> mapOut = new TreeMap<Integer, GeoLiteCityBlock>();
    Connection con = null;//from   w w  w . j a  va2s . c  o  m
    Statement st = null;
    ResultSet rs = null;
    GeoLiteCityBlock tmp = null;
    int c = 0;
    try {
        con = dataSource.getConnection();

        st = con.createStatement();
        st.setMaxRows(Integer.MAX_VALUE);
        st.setQueryTimeout(600000);
        st.setFetchSize(100000);

        rs = st.executeQuery(GeoLiteCityBlock.PREPARED_SELECT_SQL);
        while (rs.next()) {
            c++;
            mapOut.put(rs.getInt(FIELD.STARTIP.fieldName),
                    new GeoLiteCityBlock(rs.getInt(FIELD.STARTIP.fieldName), rs.getInt(FIELD.ENDIP.fieldName),
                            rs.getInt(FIELD.LOCID.fieldName)));
            if ((c % 100000) == 0) {
                if (LOG.isInfoEnabled()) {
                    LOG.info("Loaded " + c + " IP Block to Location mappings");
                }
            }
        }
    } catch (SQLException ex) {
        if (LOG.isWarnEnabled()) {
            LOG.warn("SQLException caught while loading GeoLiteCityBlock objects ", ex);
        }
    } finally {
        DbUtils.closeQuietly(con, st, rs);
    }
    return mapOut;
}

From source file:org.bml.util.geo.util.geolite.GeoLiteCityLocation.java

public static Map<Integer, GeoLiteCityLocation> readFromDB(ComboPooledDataSource dataSource) {
    Map<Integer, GeoLiteCityLocation> mapOut = new HashMap<Integer, GeoLiteCityLocation>();
    Connection con = null;/*  w  w  w .  ja va  2  s.c om*/
    Statement st = null;
    ResultSet rs = null;
    GeoLiteCityBlock tmp = null;
    int c = 0;
    try {
        con = dataSource.getConnection();

        st = con.createStatement();
        st.setMaxRows(Integer.MAX_VALUE);
        st.setQueryTimeout(600000);
        st.setFetchSize(100000);

        rs = st.executeQuery(PREPARED_SELECT_SQL);
        while (rs.next()) {
            c++;
            mapOut.put(rs.getInt(FIELD.LOCID.fieldName),
                    new GeoLiteCityLocation(rs.getInt(FIELD.LOCID.fieldName),
                            rs.getString(FIELD.COUNTRY.fieldName), rs.getString(FIELD.REGION.fieldName),
                            rs.getString(FIELD.CITY.fieldName), rs.getString(FIELD.POSTALCODE.fieldName),
                            rs.getDouble(FIELD.LATITUDE.fieldName), rs.getDouble(FIELD.LONGITUDE.fieldName),
                            rs.getString(FIELD.METROCODE.fieldName), rs.getLong(FIELD.AREACODE.fieldName)));
            if ((c % 100000) == 0) {
                if (LOG.isInfoEnabled()) {
                    LOG.info("Loaded " + c + " Location mappings");
                }
            }
        }
    } catch (SQLException ex) {
        if (LOG.isWarnEnabled()) {
            LOG.warn("SQLException caught while loading GeoLiteCityBlock objects ", ex);
        }
    } finally {
        DbUtils.closeQuietly(con, st, rs);
    }
    return mapOut;
}

From source file:org.jobjects.dao.annotation.Manager.java

/**
 * @param min Numero de ligne minimum//w  w  w .  j ava 2  s.  c  o m
 * @param max Numero de ligne maximum
 * @param wherefields Filtre
 * @param orderfields Ordonancement
 * @return la liste des beans
 * @throws FinderException retourne un exception si il y a une erreur.
 * La liste peut tre vide.
 */
public final List<T> findAll(final int min, final int max, final WhereFields wherefields,
        final OrderFields orderfields) throws FinderException {
    List<T> returnValue = null;
    try {
        returnValue = new ArrayList<T>(); // 26.375

        if (null == sql_findAll) {
            sql_findAll = loadSqlFindAll(usualTable, fields);
        }

        String sql = sql_findAll + getSqlWhereAndOrder(wherefields, orderfields);
        // sql = "SELECT * FROM (SELECT ROWNUM N, P.* FROM (" + sql;
        // sql += ") P WHERE ROWNUM < " + max + ")";
        // sql += "WHERE (N>" + min + ")AND(N<" + max + ")";
        try {
            Connection connection = getConnection();
            try {
                Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                try {
                    ResultSet rs = stmt.executeQuery(sql);
                    rs.absolute(min);
                    stmt.setMaxRows(max);
                    try {
                        while (rs.next()) {
                            T data = entityClass.newInstance();
                            int i = 1;

                            for (Field field : fields) {
                                Annotation[] annotations = field.getAnnotations();
                                for (Annotation annotation : annotations) {
                                    if (annotation instanceof DaoField) {
                                        BeanUtils.setProperty(returnValue, field.getName(), rs.getObject(i++));
                                        break;
                                    }
                                }
                            }

                            returnValue.add(data);
                        }
                    } finally {
                        rs.close();
                    }
                    rs = null;
                } finally {
                    stmt.close();
                }
                stmt = null;
            } finally {
                connection.close();
            }
            connection = null;
        } catch (SQLException sqle) {
            log.error(sql, sqle);
            throw new FinderException(sql, sqle);
        }
    } catch (Exception e) {
        throw new FinderException(e);
    }
    return returnValue;

}