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:com.tonbeller.jpivot.mondrian.ScriptableMondrianDrillThroughTableModel.java

/**
 * execute sql query//from w  w  w. j  a va 2  s.c  om
 * @throws Exception
 */
private void executeQuery() {
    Connection con = null;
    try {
        InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader()
                .getResourceAsStream("/" + catalogExtension);
        Digester catExtDigester = new Digester();
        catExtDigester.push(this);
        catExtDigester.addSetProperties("extension");
        catExtDigester.addObjectCreate("extension/script", "com.tonbeller.jpivot.mondrian.script.ScriptColumn");
        catExtDigester.addSetProperties("extension/script");
        catExtDigester.addSetNext("extension/script", "addScript");
        catExtDigester.parse(catExtIs);

        URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl);
        scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL });

        con = getConnection();
        Statement s = con.createStatement();
        s.setMaxRows(maxResults);
        ResultSet rs = s.executeQuery(sql);
        ResultSetMetaData md = rs.getMetaData();
        int numCols = md.getColumnCount();
        List columnTitlesList = new ArrayList();
        // set column headings
        for (int i = 0; i < numCols; i++) {
            //   columns are 1 based
            columnTitlesList.add(i, md.getColumnName(i + 1));
        }
        // loop on script columns
        for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) {
            final ScriptColumn sc = (ScriptColumn) sIt.next();
            columnTitlesList.add(sc.getPosition() - 1, sc.getTitle());
        }
        columnTitles = (String[]) columnTitlesList.toArray(new String[0]);
        // loop through rows
        List tempRows = new ArrayList();
        Map scriptInput = new HashMap();
        Binding binding = new Binding();
        while (rs.next()) {
            List rowList = new ArrayList();
            scriptInput.clear();
            // loop on columns, 1 based
            for (int i = 0; i < numCols; i++) {
                rowList.add(i, rs.getObject(i + 1));
                scriptInput.put(columnTitles[i], rs.getObject(i + 1));
            }
            binding.setVariable("input", scriptInput);
            // loop on script columns
            for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) {
                final ScriptColumn sc = (ScriptColumn) sIt.next();
                scriptEngine.run(sc.getFile(), binding);
                final Object output = binding.getVariable("output");
                if (output instanceof Map) {
                    Map outMap = (Map) output;
                    rowList.add(sc.getPosition() - 1,
                            new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value")));
                } else if (output instanceof String) {
                    rowList.add(sc.getPosition() - 1, (String) output);
                } else {
                    throw new Exception("Unknown groovy script return type (not a Map nor String).");
                }
            }
            tempRows.add(new DefaultTableRow(rowList.toArray()));
        }
        rs.close();
        rows = (TableRow[]) tempRows.toArray(new TableRow[0]);
    } catch (Exception e) {
        e.printStackTrace();
        logger.error("?", e);
        // problem occured, set table model to zero size
        rows = new TableRow[1];
        columnTitles = new String[1];
        columnTitles[0] = "An error occured";
        Object[] row = new Object[1];
        row[0] = e.toString();
        rows[0] = new DefaultTableRow(row);
        ready = false;
        return;
    } finally {
        try {
            con.close();
        } catch (Exception e1) {
            // ignore
        }
    }
    ready = true;
}

From source file:com.tonbeller.jpivot.mondrian.script.ScriptableMondrianDrillThroughTableModel.java

/**
 * execute sql query//from   w w  w  . ja v a2  s . com
 * @throws Exception
 */
private void executeQuery() {
    Connection con = null;
    try {
        InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader()
                .getResourceAsStream("/" + catalogExtension);
        if (catExtIs != null) {
            Digester catExtDigester = new Digester();
            catExtDigester.push(this);
            catExtDigester.addSetProperties("extension");
            catExtDigester.addObjectCreate("extension/script",
                    "com.tonbeller.jpivot.mondrian.script.ScriptColumn");
            catExtDigester.addSetProperties("extension/script");
            catExtDigester.addSetNext("extension/script", "addScript");
            catExtDigester.parse(catExtIs);

            URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl);
            scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL });
        }
        con = getConnection();
        Statement s = con.createStatement();
        s.setMaxRows(maxResults);
        ResultSet rs = s.executeQuery(sql);
        ResultSetMetaData md = rs.getMetaData();
        int numCols = md.getColumnCount();
        List columnTitlesList = new ArrayList();
        // set column headings
        for (int i = 0; i < numCols; i++) {
            //   columns are 1 based
            columnTitlesList.add(i, md.getColumnName(i + 1));
        }
        // loop on script columns
        for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) {
            final ScriptColumn sc = (ScriptColumn) sIt.next();
            columnTitlesList.add(sc.getPosition() - 1, sc.getTitle());
        }
        columnTitles = (String[]) columnTitlesList.toArray(new String[0]);
        // loop through rows
        List tempRows = new ArrayList();
        Map scriptInput = new HashMap();
        Binding binding = new Binding();
        while (rs.next()) {
            List rowList = new ArrayList();
            scriptInput.clear();
            // loop on columns, 1 based
            for (int i = 0; i < numCols; i++) {
                rowList.add(i, rs.getObject(i + 1));
                scriptInput.put(columnTitles[i], rs.getObject(i + 1));
            }
            binding.setVariable("input", scriptInput);
            // loop on script columns
            for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) {
                final ScriptColumn sc = (ScriptColumn) sIt.next();
                scriptEngine.run(sc.getFile(), binding);
                final Object output = binding.getVariable("output");
                if (output instanceof Map) {
                    Map outMap = (Map) output;
                    rowList.add(sc.getPosition() - 1,
                            new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value")));
                } else if (output instanceof String) {
                    rowList.add(sc.getPosition() - 1, (String) output);
                } else {
                    throw new Exception("Unknown groovy script return type (not a Map nor String).");
                }
            }
            tempRows.add(new DefaultTableRow(rowList.toArray()));
        }
        rs.close();
        rows = (TableRow[]) tempRows.toArray(new TableRow[0]);
    } catch (Exception e) {
        e.printStackTrace();
        logger.error("?", e);
        // problem occured, set table model to zero size
        rows = new TableRow[1];
        columnTitles = new String[1];
        columnTitles[0] = "An error occured";
        Object[] row = new Object[1];
        row[0] = e.toString();
        rows[0] = new DefaultTableRow(row);
        ready = false;
        return;
    } finally {
        try {
            con.close();
        } catch (Exception e1) {
            // ignore
        }
    }
    ready = true;
}

From source file:com.slemarchand.sqlqueryscripting.scripting.sqlquery.SQLQueryExecutor.java

private List<List<Object>> _execQuery(String sqlQuery, int maxRows, List<String> columnLabels)
        throws SQLException {

    List<List<Object>> rows = null;

    Connection con = null;//from  w ww .  ja  va2s  . c  om
    Statement stmt = null;
    ResultSet rs = null;

    try {
        con = DataAccess.getConnection();

        con.setAutoCommit(false); // Prevent data updates

        stmt = con.createStatement();
        stmt.setMaxRows(maxRows);
        rs = stmt.executeQuery(sqlQuery);

        ResultSetMetaData md = rs.getMetaData();
        int cc = md.getColumnCount();

        rows = new ArrayList<List<Object>>(cc);

        columnLabels.clear();

        for (int c = 1; c <= cc; c++) {
            String cl = md.getColumnLabel(c);
            columnLabels.add(cl);
        }

        while (rs.next()) {
            List<Object> row = new ArrayList<Object>(cc);
            for (int c = 1; c <= cc; c++) {
                Object value = rs.getObject(c);
                row.add(value);
            }
            rows.add(row);
        }

    } finally {
        DataAccess.cleanUp(con, stmt, rs);
    }

    return rows;
}

From source file:com.taobao.tddl.jdbc.group.TGroupStatement.java

/**
 * setBaseStatementStatement/*from   w  w w  . jav  a2 s  .c o  m*/
 */
private Statement createStatementInternal(Connection conn, 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);

    return stmt;
}

From source file:com.aurel.track.ApplicationStarter.java

private void setColumnSizes() {
    Connection conn = null;//from w ww.  ja  v  a 2  s.  c  o  m
    int descSize = 32000;// Firebird
    int cSize = 10000;// Firebird
    int sizeMySQL = 16777215;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = Torque.getConnection(BaseTSitePeer.DATABASE_NAME);
        stmt = conn.createStatement();
        stmt.setMaxRows(2);
        rs = stmt.executeQuery("SELECT PACKAGEDESCRIPTION FROM TWORKITEM");
        if (rs != null) {
            descSize = rs.getMetaData().getColumnDisplaySize(1);
            if (descSize <= 0) {
                // MySQL bug (MySql 5.0.26 for linux returns -1)
                System.err.println("Incorrect maximum description length retrieved: " + descSize);
                LOGGER.debug("Incorrect maximum description length retrieved: " + descSize);
                descSize = sizeMySQL;
            }
            rs.close();
        }
        rs = stmt.executeQuery("SELECT CHANGEDESCRIPTION FROM TSTATECHANGE");
        if (rs != null) {
            cSize = rs.getMetaData().getColumnDisplaySize(1);
            if (cSize <= 0) {
                // MySQL bug (MySql 5.0.26 for linux returns -1)
                System.err.println("Incorrect maximum comment length retrieved: " + cSize);
                LOGGER.debug("Incorrect maximum comment length retrieved: " + cSize);
                cSize = sizeMySQL;
            }
        }
    } catch (Exception e) {
        LOGGER.debug(e);
        System.err.println("Could not retrieve column sizes from database: " + e.getMessage());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOGGER.info("Closing the resultset failed with " + e.getMessage());
                LOGGER.debug(ExceptionUtils.getStackTrace(e));
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                LOGGER.info("Closing the statement failed with " + e.getMessage());
                LOGGER.debug(ExceptionUtils.getStackTrace(e));
            }
        }
        if (conn != null) {
            Torque.closeConnection(conn);
        }
    }
    LOGGER.info("Maximum description length: " + descSize);
    LOGGER.info("Maximum comment length: " + cSize);
    ApplicationBean.getInstance().setDescriptionMaxLength(descSize);
    ApplicationBean.getInstance().setCommentMaxLength(cSize);
}

From source file:net.hydromatic.optiq.test.JdbcTest.java

/** Tests that {@link java.sql.Statement#setMaxRows(int)} is honored. */
@Test/*ww w .  jav a2  s . com*/
public void testSetMaxRows() throws Exception {
    OptiqAssert.assertThat().with(OptiqAssert.Config.REGULAR)
            .doWithConnection(new Function1<OptiqConnection, Object>() {
                public Object apply(OptiqConnection a0) {
                    try {
                        final Statement statement = a0.createStatement();
                        try {
                            statement.setMaxRows(-1);
                            fail("expected error");
                        } catch (SQLException e) {
                            assertEquals(e.getMessage(), "illegal maxRows value: -1");
                        }
                        statement.setMaxRows(2);
                        assertEquals(2, statement.getMaxRows());
                        final ResultSet resultSet = statement.executeQuery("select * from \"hr\".\"emps\"");
                        assertTrue(resultSet.next());
                        assertTrue(resultSet.next());
                        assertFalse(resultSet.next());
                        resultSet.close();
                        statement.close();
                        return null;
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                }
            });
}

From source file:com.p5solutions.core.jpa.orm.EntityUtility.java

/**
 * Build the database-meta-dta for a given table entity, using an existing
 * connection.//from w ww .  ja  v a  2  s.c om
 * 
 * @param table
 *          annotation
 * @param detail
 *          {@link EntityDetail} probably provided by the
 *          {@link #cacheEntityDetails}
 * @param connection
 *          an existing mock or real, database connection.
 */
protected void buildColumnMetaData(Table table, EntityDetail<?> detail, Connection connection) {

    Statement stmt = null;
    ResultSet rs = null;

    try {
        String sql = "SELECT * FROM " + table.name() + " WHERE 1=0";

        stmt = connection.createStatement();

        // set the maximum result set to zero, just in-case!?
        stmt.setMaxRows(0);

        rs = stmt.executeQuery(sql);
        ResultSetMetaData rsMeta = rs.getMetaData();

        logger.info("** Building Database MetaData for Table " + table.name());

        for (int ic = 1; ic <= rsMeta.getColumnCount(); ic++) {
            String columnName = rsMeta.getColumnName(ic);
            ParameterBinder binder = detail.getParameterBinderByAny(columnName);
            if (binder == null) {
                if (logger.isErrorEnabled()) {
                    String error = " -- Column " + columnName
                            + " as defined by the table meta-data, cannot be found within the scope of "
                            + detail.getEntityClass();
                    logger.error(error);
                }

                // TODO ?? throw new RuntimeException(new
                // NoColumnDefinedException(error));
            } else {
                ParameterBinderColumnMetaData columnMetaData = new ParameterBinderColumnMetaData();
                // columnMetaData.setColumnIndex(ic); // USELESS, EVERY UNIQUE QUERY
                // STRING WOULD RESULT IN A DIFFERENT INDEX. EASIER TO CACHE IT BASED
                // ON UNIQUE QUERY STRINGS.
                // columnMetaData.setColumnLabel(rsMeta.getColumnLabel(ic));
                columnMetaData.setColumnName(columnName);
                columnMetaData.setLength(rsMeta.getColumnDisplaySize(ic));
                columnMetaData.setPrecision(rsMeta.getPrecision(ic));
                columnMetaData.setScale(rsMeta.getScale(ic));
                columnMetaData.setColumnType(rsMeta.getColumnType(ic));
                columnMetaData.setColumnTypeName(rsMeta.getColumnTypeName(ic));
                binder.setColumnMetaData(columnMetaData);

                if (logger.isDebugEnabled()) {
                    logger.debug(" -- [" + columnMetaData.toString() + "]");
                }
            }
        }

    } catch (SQLException e) {
        logger.error(">> *UNABLE* to retrieve meta data for table " + table.name() + ", doesn't exist?");
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                ;
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                ;
            }
            stmt = null;
        }

    }
}

From source file:cc.tooyoung.common.db.JdbcTemplate.java

/**
 * 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//from w  w w  .j  av a2s  .  c om
 * @see #setMaxRows
 * @see #setQueryTimeout
 * @see org.springframework.jdbc.datasource.DataSourceUtils#applyTransactionTimeout
 */
protected void applyStatementSettings(DataSource dataSource, 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, dataSource, getQueryTimeout());
}

From source file:lib.JdbcTemplate.java

/**
 * 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/*w ww.ja  va 2 s .  c o  m*/
 * @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());
}

From source file:edu.arizona.rice.kew.docsearch.dao.impl.DocumentSearchDAOJdbcImpl.java

/**
 * // www. j  a v  a2 s  . c  o m
 * @param criteria
 * @param criteriaModified
 * @param searchFields
 * @return 
 */
protected DocumentSearchResults.Builder doInternalSearch(DocumentSearchCriteria criteria,
        boolean criteriaModified, final List<RemotableAttributeField> searchFields) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet res = null;

    DocumentSearchResults.Builder retval = DocumentSearchResults.Builder
            .create(DocumentSearchCriteria.Builder.create(criteria));
    retval.setCriteriaModified(criteriaModified);
    retval.setSearchResults(new ArrayList<DocumentSearchResult.Builder>());

    try {
        conn = dataSource.getConnection();
        conn.setReadOnly(true);
        stmt = conn.createStatement();
        int maxRows = getMaxResultCap(criteria) + 1;

        stmt.setMaxRows(maxRows);

        int fetchSize = stmt.getFetchSize();

        // use default fetch size if document id is included
        // otherwize use custom fetch size
        if (StringUtils.isBlank(criteria.getDocumentId())) {
            fetchSize = customFetchSize;
        }

        String sql = getSearchSql(criteria, searchFields);

        res = stmt.executeQuery(sql);

        // run up to the starting row if required
        if (criteria.getStartAtIndex() != null) {
            for (int i = 0; (i < criteria.getStartAtIndex()) && res.next(); ++i) {
            }
            ;
        }

        List<DocumentInformation> results = new ArrayList<DocumentInformation>();

        // load up document information from the query
        while (res.next() && (results.size() < maxRows)) {
            results.add(new DocumentInformation(res));
        }

        // if we have threshold+1 results, then we have more results than we are going to display
        retval.setOverThreshold(res.next());
        if (isUsingAtLeastOneSearchAttribute(criteria)) {
            // now that we have a list of documents, load the attributes
            loadDocumentAttributes(results, fetchSize);
        }

        // generate the DocumentSearchResults.Builder to return
        for (DocumentInformation docinfo : results) {
            retval.getSearchResults().add(docinfo.getSearchResult());
        }

        if (LOG.isDebugEnabled()) {
            LOG.debug("document search result rows processed: " + retval.getSearchResults().size());
        }
    }

    catch (Exception ex) {
        throw new RuntimeException(ex.toString(), ex);
    }

    finally {
        DocumentSearchUtils.closeDbObjects(conn, stmt, res);
    }

    return retval;
}