List of usage examples for java.sql Statement setMaxRows
void setMaxRows(int max) throws SQLException;
ResultSet object generated by this Statement object can contain to the given number. From source file:org.kawanfw.sql.servlet.sql.ServerStatement.java
/** * Execute the passed SQL Statement and return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * // w ww .j ava 2 s . com * @param sqlOrder * the qsql order * @param sqlParms * the sql parameters * @param out * the output stream where to write to result set output * * * @throws SQLException */ private void executeQueryOrUpdateStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); debug("statementHolder: " + statementHolder.getSqlOrder()); debug("sqlOrder : " + sqlOrder); // sqlOrder = HtmlConverter.fromHtml(sqlOrder); if (statementHolder.isDoExtractResultSetMetaData()) { sqlOrder = DbVendorManager.addLimit1(sqlOrder, connection); } Statement statement = null; try { if (!SqlConfiguratorCall.allowStatementClass(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } statement = connection.createStatement(); ServerSqlUtil.setStatementProperties(statement, statementHolder); debug("before ServerPreparedStatementParameters"); boolean isAllowedAfterAnalysis = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, new Vector<Object>()); if (!isAllowedAfterAnalysis) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } isAllowedAfterAnalysis = SqlConfiguratorCall.allowResultSetGetMetaData(sqlConfigurator, username, connection); if (statementHolder.isDoExtractResultSetMetaData() && !isAllowedAfterAnalysis) { String message = Tag.PRODUCT_SECURITY + " ResultSet.getMetaData() Query not authorized."; throw new SecurityException(message); } debug("before executeQuery() / executeUpdate(sqlOrder)"); if (statementHolder.isExecuteUpdate()) { if (!SqlConfiguratorCall.allowExecuteUpdate(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized for ExecuteUpdate}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } int rc = -1; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { rc = statement.executeUpdate(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { rc = statement.executeUpdate(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { rc = statement.executeUpdate(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { rc = statement.executeUpdate(sqlOrder); } //br.write(TransferStatus.SEND_OK + CR_LF); //br.write(rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "" + rc); if (usesAutoGeneratedKeys) { ResultSet rs = null; try { rs = statement.getGeneratedKeys(); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) { rs.close(); } } } } else { ResultSet rs = null; try { if (statementHolder.isDoExtractResultSetMetaData()) { statement.setMaxRows(1); } else { ServerSqlUtil.setMaxRowsToReturn(statement, sqlConfigurator); } rs = statement.executeQuery(sqlOrder); //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); if (statementHolder.isDoExtractResultSetMetaData()) { ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(out, commonsConfigurator, sqlConfigurator); resultSetMetaDataWriter.write(rs); } else { // print(rs, br); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } } finally { if (rs != null) { rs.close(); } } } } catch (SQLException e) { ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT_EXCEPTION_RAISED + CR_LF + "Statement: " + sqlOrder + CR_LF + "- sql order: " + sqlOrder + CR_LF + "- exception: " + e.toString()); throw e; } finally { IOUtils.closeQuietly(out); if (statement != null) { statement.close(); } } }
From source file:org.kuali.rice.kew.docsearch.dao.impl.DocumentSearchDAOJdbcImpl.java
@Override public DocumentSearchResults.Builder findDocuments(final DocumentSearchGenerator documentSearchGenerator, final DocumentSearchCriteria criteria, final boolean criteriaModified, final List<RemotableAttributeField> searchFields) { final int maxResultCap = getMaxResultCap(criteria); try {//from ww w . j av a 2s . co m final JdbcTemplate template = new JdbcTemplate(dataSource); return template.execute(new ConnectionCallback<DocumentSearchResults.Builder>() { @Override public DocumentSearchResults.Builder doInConnection(final Connection con) throws SQLException { final Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { final int fetchIterationLimit = getFetchMoreIterationLimit(); final int fetchLimit = fetchIterationLimit * maxResultCap; statement.setFetchSize(maxResultCap + 1); statement.setMaxRows(fetchLimit + 1); PerformanceLogger perfLog = new PerformanceLogger(); String sql = documentSearchGenerator.generateSearchSql(criteria, searchFields); perfLog.log("Time to generate search sql from documentSearchGenerator class: " + documentSearchGenerator.getClass().getName(), true); LOG.info("Executing document search with statement max rows: " + statement.getMaxRows()); LOG.info( "Executing document search with statement fetch size: " + statement.getFetchSize()); perfLog = new PerformanceLogger(); final ResultSet rs = statement.executeQuery(sql); try { perfLog.log("Time to execute doc search database query.", true); final Statement searchAttributeStatement = con .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { return documentSearchGenerator.processResultSet(criteria, criteriaModified, searchAttributeStatement, rs, maxResultCap, fetchLimit); } finally { try { searchAttributeStatement.close(); } catch (SQLException e) { LOG.warn("Could not close search attribute statement."); } } } finally { try { rs.close(); } catch (SQLException e) { LOG.warn("Could not close result set."); } } } finally { try { statement.close(); } catch (SQLException e) { LOG.warn("Could not close statement."); } } } }); } catch (DataAccessException dae) { String errorMsg = "DataAccessException: " + dae.getMessage(); LOG.error("getList() " + errorMsg, dae); throw new RuntimeException(errorMsg, dae); } catch (Exception e) { String errorMsg = "LookupException: " + e.getMessage(); LOG.error("getList() " + errorMsg, e); throw new RuntimeException(errorMsg, e); } }
From source file:org.nuclos.server.dblayer.impl.standard.StandardSqlDBAccess.java
protected boolean setStatementHint(Statement stmt, String hint, Object value) throws SQLException { if (MAX_ROWS_HINT.equals(hint)) { stmt.setMaxRows((Integer) value); return true; } else if (QUERY_TIMEOUT_HINT.equals(hint)) { stmt.setQueryTimeout((Integer) value); return true; }/* w ww . j av a2 s . com*/ return false; }
From source file:org.opoo.oqs.core.AbstractQuery.java
protected void applyStatementSettings(Statement stmt) throws SQLException { int fetchSize = getFetchSize(); if (fetchSize > 0) { stmt.setFetchSize(fetchSize);/* w w w . j a v a2 s .co m*/ } int maxRows = getMaxRows(); if (maxRows > 0) { stmt.setMaxRows(maxRows); } int timeout = getQueryTimeout(); if (timeout > 0) { stmt.setQueryTimeout(timeout); } }
From source file:org.pentaho.metadata.SQLModelGeneratorTest.java
private Domain generateModel() throws SQLModelGeneratorException { String query = "select customername from customers where customernumber < 171"; Connection connection = null; Boolean securityEnabled = true; List<String> users = new ArrayList<String>(); users.add("suzy"); List<String> roles = new ArrayList<String>(); roles.add("Authenticated"); int defaultAcls = 31; String createdBy = "joe"; String[] columnHeaders = null; int[] columnTypes = null; Object[][] rawdata = null;/* w w w . j a v a2 s .c om*/ Statement stmt = null; ResultSet rs = null; try { connection = getDataSourceConnection("org.hsqldb.jdbcDriver", "SampleData", "pentaho_user", "password", "jdbc:hsqldb:file:test/solution/system/data/sampledata"); stmt = connection.createStatement(); stmt.setMaxRows(5); rs = stmt.executeQuery(query); ResultSetMetaData metadata = rs.getMetaData(); columnHeaders = new String[metadata.getColumnCount()]; columnTypes = new int[metadata.getColumnCount()]; columnHeaders = getColumnNames(metadata); columnTypes = getColumnTypes(metadata); } catch (Exception e) { e.printStackTrace(); } finally { try { closeAll(connection, stmt, rs, true); } catch (SQLException e) { } } SQLModelGenerator generator = new SQLModelGenerator("newdatasource", "SampleData", "Hypersonic", columnTypes, columnHeaders, query, securityEnabled, users, roles, defaultAcls, createdBy); return generator.generate(); }
From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.java
protected TableModel parametrizeAndQuery(final DataRow parameters, final String translatedQuery, final String[] preparedParameterNames) throws SQLException { final boolean callableStatementQuery = isCallableStatementQuery(translatedQuery); final boolean callableStatementUsed = callableStatementQuery || isCallableStatement(translatedQuery); final Statement statement; if (preparedParameterNames.length == 0) { statement = getConnection(parameters).createStatement(getBestResultSetType(parameters), ResultSet.CONCUR_READ_ONLY); } else {/* w w w . j a va 2 s.com*/ if (callableStatementUsed) { final CallableStatement pstmt = getConnection(parameters).prepareCall(translatedQuery, getBestResultSetType(parameters), ResultSet.CONCUR_READ_ONLY); if (isCallableStatementQuery(translatedQuery)) { pstmt.registerOutParameter(1, Types.OTHER); parametrize(parameters, preparedParameterNames, pstmt, false, 1); } else { parametrize(parameters, preparedParameterNames, pstmt, false, 0); } statement = pstmt; } else { final PreparedStatement pstmt = getConnection(parameters).prepareStatement(translatedQuery, getBestResultSetType(parameters), ResultSet.CONCUR_READ_ONLY); parametrize(parameters, preparedParameterNames, pstmt, isExpandArrays(), 0); statement = pstmt; } } final Object queryLimit = parameters.get(DataFactory.QUERY_LIMIT); try { if (queryLimit instanceof Number) { final Number i = (Number) queryLimit; final int max = i.intValue(); if (max > 0) { statement.setMaxRows(max); } } } catch (SQLException sqle) { // this fails for MySQL as their driver is buggy. We will not add workarounds here, as // all drivers are buggy and this is a race we cannot win. Put pressure on the driver // manufacturer instead. logger.warn("Driver indicated error: Failed to set query-limit: " + queryLimit, sqle); } final Object queryTimeout = parameters.get(DataFactory.QUERY_TIMEOUT); try { if (queryTimeout instanceof Number) { final Number i = (Number) queryTimeout; final int seconds = i.intValue(); if (seconds > 0) { statement.setQueryTimeout(seconds); } } } catch (SQLException sqle) { logger.warn("Driver indicated error: Failed to set query-timeout: " + queryTimeout, sqle); } // Track the currently running statement - just in case someone needs to cancel it final ResultSet res; try { currentRunningStatement = statement; if (preparedParameterNames.length == 0) { res = statement.executeQuery(translatedQuery); } else { final PreparedStatement pstmt = (PreparedStatement) statement; res = pstmt.executeQuery(); } } finally { currentRunningStatement = null; } // equalsIgnore, as this is what the ResultSetTableModelFactory uses. final boolean simpleMode = "simple".equalsIgnoreCase(getConfiguration().getConfigProperty( //$NON-NLS-1$ ResultSetTableModelFactory.RESULTSET_FACTORY_MODE)); //$NON-NLS-1$ if (simpleMode) { return ResultSetTableModelFactory.getInstance().generateDefaultTableModel(res, columnNameMapping); } return ResultSetTableModelFactory.getInstance().createTableModel(res, columnNameMapping, true); }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * _more_/*ww w . j ava 2s. c o m*/ * * @param connection _more_ * @param sql _more_ * @param max _more_ * @param timeout _more_ * * @return _more_ * * @throws Exception _more_ */ public Statement execute(Connection connection, String sql, int max, int timeout) throws Exception { Statement statement = connection.createStatement(); if (timeout > 0) { statement.setQueryTimeout(timeout); } if (max > 0) { statement.setMaxRows(max); } long t1 = System.currentTimeMillis(); try { statement.execute(sql); } catch (Exception exc) { // logError("Error executing sql:" + sql, exc); throw exc; } long t2 = System.currentTimeMillis(); if (getRepository().debug || (t2 - t1 > 300)) { logInfo("query took:" + (t2 - t1) + " " + sql); } if (t2 - t1 > 2000) { // Misc.printStack("query:" + sql); } return statement; }
From source file:org.rhq.plugins.database.CustomTableDiscoveryComponent.java
@Override public Set<DiscoveredResourceDetails> discoverResources( ResourceDiscoveryContext<ResourceComponent<?>> discoveryContext) throws InvalidPluginConfigurationException, Exception { ResourceComponent<?> parentComponent = discoveryContext.getParentResourceComponent(); Configuration config = discoveryContext.getDefaultPluginConfiguration(); String table = config.getSimpleValue("table", ""); ResourceType resourceType = discoveryContext.getResourceType(); String resourceName = config.getSimpleValue("name", resourceType.getName()); String resourceDescription = config.getSimpleValue("description", resourceType.getDescription()); if (!canProvideConnection(parentComponent)) { if (log.isDebugEnabled()) { log.debug("Parent component does not provide JDBC connections, cannot discover" + resourceName); }// w w w .j a v a 2 s . c o m return Collections.emptySet(); } if (table.isEmpty()) { if (log.isDebugEnabled()) { log.debug("'table' value not set, cannot discover " + resourceName); } return Collections.emptySet(); } Statement statement = null; Connection connection = null; ResultSet resultSet = null; try { connection = getConnectionFromComponent(parentComponent); if (connection == null) { throw new InvalidPluginConfigurationException("cannot obtain connection from parent"); } statement = connection.createStatement(); statement.setMaxRows(1); statement.setFetchSize(1); // This is more efficient than 'count(*)' // unless the JDBC driver fails to support setMaxRows or doesn't stream results resultSet = statement.executeQuery("SELECT * FROM " + table); DiscoveredResourceDetails details = new DiscoveredResourceDetails(discoveryContext.getResourceType(), table + resourceName, resourceName, null, resourceDescription, config, null); if (log.isDebugEnabled()) { log.debug("discovered " + details); } return Collections.singleton(details); } catch (SQLException e) { if (log.isDebugEnabled()) { log.debug("discovery failed " + e + " for " + table); } // table not found, don't inventory } finally { DatabasePluginUtil.safeClose(null, statement, resultSet); if (hasConnectionPoolingSupport(parentComponent)) { DatabasePluginUtil.safeClose(connection, statement, resultSet); } } return Collections.emptySet(); }
From source file:org.seasar.dbflute.s2dao.sqlhandler.TnAbstractBasicSqlHandler.java
protected void setMaxRows(Statement statement, int maxRows) { if (statement == null) { return;//from ww w .j a v a 2 s. c o m } try { statement.setMaxRows(maxRows); } catch (SQLException e) { handleSQLException(e, statement); } }
From source file:ro.nextreports.engine.util.QueryUtil.java
public List<NameType> executeQueryForColumnNames(String sql) throws Exception { // long t = System.currentTimeMillis(); StringWriter sw = new StringWriter(100); // sw.append("SELECT * FROM ("); sw.append(sql);/*w w w .j a v a 2 s . com*/ // sw.append(") A WHERE 1 = -1"); String sqlForHeader = sw.toString(); LOG.info("call for header columns = " + sqlForHeader); ResultSet rs = null; Statement stmt = null; try { if (isProcedureCall(sqlForHeader)) { Dialect dialect = DialectUtil.getDialect(con); CallableStatement cs = con.prepareCall("{" + sqlForHeader + "}"); stmt = cs; if (dialect.hasProcedureWithCursor()) { cs.registerOutParameter(1, dialect.getCursorSqlType()); } rs = cs.executeQuery(); if (dialect.hasProcedureWithCursor()) { rs = (ResultSet) (cs.getObject(1)); } } else { stmt = con.createStatement(); stmt.setMaxRows(1); rs = stmt.executeQuery(sqlForHeader); } ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); List<NameType> columnNames = new ArrayList<NameType>(); for (int i = 0; i < columnCount; i++) { columnNames.add(new NameType(rsmd.getColumnLabel(i + 1), dialect.getJavaType( rsmd.getColumnTypeName(i + 1), rsmd.getPrecision(i + 1), rsmd.getScale(i + 1)))); // rsmd.getColumnClassName(i + 1))); } // t = System.currentTimeMillis() - t; // System.out.println("execute query for column names in " + t + // "ms"); return columnNames; } finally { ConnectionUtil.closeResultSet(rs); ConnectionUtil.closeStatement(stmt); } }