List of usage examples for java.sql Statement setFetchSize
void setFetchSize(int rows) throws SQLException;
ResultSet objects generated by this Statement. 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 w w w. j ava 2 s.c o 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.kuali.rice.kew.impl.document.lookup.DocumentLookupGeneratorImpl.java
/** * This method performs searches against the search attribute value tables (see classes implementing * {@link org.kuali.rice.kew.docsearch.SearchableAttributeValue}) to get data to fill in search attribute values on the given resultBuilder parameter * * @param resultBuilder - document search result object getting search attributes added to it * @param searchAttributeStatement - statement being used to call the database for queries * @throws SQLException/*from w w w .j a v a2s .com*/ */ public void populateDocumentAttributesValues(DocumentLookupResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException { searchAttributeStatement.setFetchSize(50); String documentId = resultBuilder.getDocument().getDocumentId(); List<SearchableAttributeValue> attributeValues = DocumentLookupInternalUtils .getSearchableAttributeValueObjectTypes(); PerformanceLogger perfLog = new PerformanceLogger(documentId); for (SearchableAttributeValue searchAttValue : attributeValues) { String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = " + documentId; ResultSet attributeResultSet = null; try { attributeResultSet = searchAttributeStatement.executeQuery(attributeSql); while (attributeResultSet.next()) { searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD")); searchAttValue.setupAttributeValue(attributeResultSet, "VAL"); if ((!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null)) { DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute(); resultBuilder.getDocumentAttributes() .add(DocumentAttributeFactory.loadContractIntoBuilder(documentAttribute)); } } } finally { if (attributeResultSet != null) { try { attributeResultSet.close(); } catch (Exception e) { LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(), e); } } } } perfLog.log("Time to execute doc search search attribute queries.", true); }
From source file:org.kuali.rice.kew.impl.document.search.DocumentSearchGeneratorImpl.java
/** * This method performs searches against the search attribute value tables (see classes implementing * {@link org.kuali.rice.kew.docsearch.SearchableAttributeValue}) to get data to fill in search attribute values on the given resultBuilder parameter * * @param resultBuilder - document search result object getting search attributes added to it * @param searchAttributeStatement - statement being used to call the database for queries * @throws SQLException/*from ww w.jav a2 s. c o m*/ */ public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException { searchAttributeStatement.setFetchSize(50); String documentId = resultBuilder.getDocument().getDocumentId(); List<SearchableAttributeValue> attributeValues = DocumentSearchInternalUtils .getSearchableAttributeValueObjectTypes(); PerformanceLogger perfLog = new PerformanceLogger(documentId); for (SearchableAttributeValue searchAttValue : attributeValues) { String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = '" + documentId + "'"; ResultSet attributeResultSet = null; try { attributeResultSet = searchAttributeStatement.executeQuery(attributeSql); while (attributeResultSet.next()) { searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD")); searchAttValue.setupAttributeValue(attributeResultSet, "VAL"); if ((!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null)) { DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute(); resultBuilder.getDocumentAttributes() .add(DocumentAttributeFactory.loadContractIntoBuilder(documentAttribute)); } } } finally { if (attributeResultSet != null) { try { attributeResultSet.close(); } catch (Exception e) { LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(), e); } } } } perfLog.log("Time to execute doc search search attribute queries.", true); }
From source file:org.onexus.collection.store.mysql.internal.MysqlCollectionStore.java
@Override public Statement createReadStatement(Connection dataConn) throws SQLException { Statement st = super.createReadStatement(dataConn); st.setFetchDirection(ResultSet.FETCH_FORWARD); st.setFetchSize(Integer.MIN_VALUE); return st;/*from w w w .j av a2s . c o m*/ }
From source file:org.openhab.persistence.mysql.internal.MysqlPersistenceService.java
/** * Connects to the database/*from w w w . jav a 2s. c o m*/ */ private void connectToDatabase() { try { // Reset the error counter errCnt = 0; logger.debug("mySQL: Attempting to connect to database {}", url); Class.forName(driverClass).newInstance(); connection = DriverManager.getConnection(url, user, password); logger.debug("mySQL: Connected to database {}", url); Statement st = connection.createStatement(); int result = st.executeUpdate("SHOW TABLES LIKE 'Items'"); st.close(); if (waitTimeout != -1) { logger.debug("mySQL: Setting wait_timeout to {} seconds.", waitTimeout); st = connection.createStatement(); st.executeUpdate("SET SESSION wait_timeout=" + waitTimeout); st.close(); } if (result == 0) { st = connection.createStatement(); st.executeUpdate( "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));", Statement.RETURN_GENERATED_KEYS); st.close(); } // Retrieve the table array st = connection.createStatement(); // Turn use of the cursor on. st.setFetchSize(50); ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items"); while (rs.next()) { sqlTables.put(rs.getString(2), "Item" + rs.getInt(1)); } rs.close(); st.close(); } catch (Exception e) { logger.error( "mySQL: Failed connecting to the SQL database using: driverClass={}, url={}, user={}, password={}", driverClass, url, user, password, e); } }
From source file:org.openhab.persistence.mysql.internal.MysqlPersistenceService.java
@Override public Iterable<HistoricItem> query(FilterCriteria filter) { if (!initialized) { logger.debug("Query aborted on item {} - mySQL not initialised!", filter.getItemName()); return Collections.emptyList(); }/*ww w.j a v a2 s .co m*/ if (!isConnected()) connectToDatabase(); if (!isConnected()) { logger.debug("Query aborted on item {} - mySQL not connected!", filter.getItemName()); return Collections.emptyList(); } SimpleDateFormat mysqlDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // Get the item name from the filter // Also get the Item object so we can determine the type Item item = null; String itemName = filter.getItemName(); logger.debug("mySQL query: item is {}", itemName); try { if (itemRegistry != null) { item = itemRegistry.getItem(itemName); } } catch (ItemNotFoundException e1) { logger.error("Unable to get item type for {}", itemName); // Set type to null - data will be returned as StringType item = null; } if (item instanceof GroupItem) { // For Group Items is BaseItem needed to get correct Type of Value. item = GroupItem.class.cast(item).getBaseItem(); } String table = sqlTables.get(itemName); if (table == null) { logger.error("mySQL: Unable to find table for query '{}'.", itemName); return Collections.emptyList(); } String filterString = new String(); if (filter.getBeginDate() != null) { if (filterString.isEmpty()) filterString += " WHERE"; else filterString += " AND"; filterString += " TIME>'" + mysqlDateFormat.format(filter.getBeginDate()) + "'"; } if (filter.getEndDate() != null) { if (filterString.isEmpty()) filterString += " WHERE"; else filterString += " AND"; filterString += " TIME<'" + mysqlDateFormat.format(filter.getEndDate().getTime()) + "'"; } if (filter.getOrdering() == Ordering.ASCENDING) { filterString += " ORDER BY Time ASC"; } else { filterString += " ORDER BY Time DESC"; } if (filter.getPageSize() != 0x7fffffff) filterString += " LIMIT " + filter.getPageNumber() * filter.getPageSize() + "," + filter.getPageSize(); try { long timerStart = System.currentTimeMillis(); // Retrieve the table array Statement st = connection.createStatement(); String queryString = new String(); queryString = "SELECT Time, Value FROM " + table; if (!filterString.isEmpty()) queryString += filterString; logger.debug("mySQL: query:" + queryString); // Turn use of the cursor on. st.setFetchSize(50); ResultSet rs = st.executeQuery(queryString); long count = 0; List<HistoricItem> items = new ArrayList<HistoricItem>(); State state; while (rs.next()) { count++; if (item instanceof NumberItem) state = new DecimalType(rs.getDouble(2)); else if (item instanceof ColorItem) state = new HSBType(rs.getString(2)); else if (item instanceof DimmerItem) state = new PercentType(rs.getInt(2)); else if (item instanceof SwitchItem) state = OnOffType.valueOf(rs.getString(2)); else if (item instanceof ContactItem) state = OpenClosedType.valueOf(rs.getString(2)); else if (item instanceof RollershutterItem) state = new PercentType(rs.getInt(2)); else if (item instanceof DateTimeItem) { Calendar calendar = Calendar.getInstance(); calendar.setTimeInMillis(rs.getTimestamp(2).getTime()); state = new DateTimeType(calendar); } else //Call, Location, String state = new StringType(rs.getString(2)); MysqlItem mysqlItem = new MysqlItem(itemName, state, rs.getTimestamp(1)); items.add(mysqlItem); } rs.close(); st.close(); long timerStop = System.currentTimeMillis(); logger.debug("mySQL: query returned {} rows in {}ms", count, timerStop - timerStart); // Success errCnt = 0; return items; } catch (SQLException e) { errCnt++; logger.error("mySQL: Error running querying : ", e.getMessage()); } return null; }
From source file:org.openhab.persistence.sql.internal.SqlPersistenceService.java
/** * Connects to the database/*from w ww . ja v a 2s.c om*/ */ private void connectToDatabase() { try { // Reset the error counter errCnt = 0; logger.debug("SQL: Attempting to connect to database " + url); Class.forName(driverClass).newInstance(); connection = DriverManager.getConnection(url, user, password); logger.debug("SQL: Connected to database " + url); Statement st = connection.createStatement(); int result = st.executeUpdate("SHOW TABLES LIKE 'Items'"); st.close(); if (result == 0) { st = connection.createStatement(); st.executeUpdate( "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));", Statement.RETURN_GENERATED_KEYS); st.close(); } // Retrieve the table array st = connection.createStatement(); // Turn use of the cursor on. st.setFetchSize(50); ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items"); while (rs.next()) { sqlTables.put(rs.getString(2), "Item" + rs.getInt(1)); } rs.close(); st.close(); } catch (Exception e) { logger.error("SQL: Failed connecting to the SQL database using: driverClass=" + driverClass + ", url=" + url + ", user=" + user + ", password=" + password, e); } }
From source file:org.openhab.persistence.sql.internal.SqlPersistenceService.java
@Override public Iterable<HistoricItem> query(FilterCriteria filter) { if (initialized) { if (!isConnected()) { connectToDatabase();/*from w w w . j a va 2s .c o m*/ } SimpleDateFormat mysqlDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if (isConnected()) { String itemName = filter.getItemName(); String table = sqlTables.get(itemName); if (table == null) { logger.error("SQL: Unable to find table for query '" + itemName + "'."); return Collections.emptyList(); } String filterString = new String(); if (filter.getBeginDate() != null) { if (filterString.isEmpty()) filterString += " WHERE"; else filterString += " AND"; filterString += " TIME>'" + mysqlDateFormat.format(filter.getBeginDate()) + "'"; } if (filter.getEndDate() != null) { if (filterString.isEmpty()) filterString += " WHERE"; else filterString += " AND"; filterString += " TIME<'" + mysqlDateFormat.format(filter.getEndDate().getTime()) + "'"; } if (filter.getOrdering() == Ordering.ASCENDING) { filterString += " ORDER BY 'Time' ASC"; } else { filterString += " ORDER BY Time DESC"; } if (filter.getPageSize() != 0x7fffffff) filterString += " LIMIT " + filter.getPageNumber() * filter.getPageSize() + "," + filter.getPageSize(); try { long timerStart = System.currentTimeMillis(); // Retrieve the table array Statement st = connection.createStatement(); String queryString = new String(); queryString = "SELECT Time, Value FROM " + table; if (!filterString.isEmpty()) queryString += filterString; logger.debug("SQL: " + queryString); // Turn use of the cursor on. st.setFetchSize(50); ResultSet rs = st.executeQuery(queryString); long count = 0; double value; List<HistoricItem> items = new ArrayList<HistoricItem>(); while (rs.next()) { count++; //TODO: Make this type specific ??? value = rs.getDouble(2); State v = new DecimalType(value); SqlItem sqlItem = new SqlItem(itemName, v, rs.getTimestamp(1)); items.add(sqlItem); } rs.close(); st.close(); long timerStop = System.currentTimeMillis(); logger.debug("SQL: query returned {} rows in {}ms", count, timerStop - timerStart); // Success errCnt = 0; return items; } catch (SQLException e) { errCnt++; logger.error("SQL: Error running querying : " + e.getMessage()); } } } return Collections.emptyList(); }
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); }//from w ww. j a v a 2 s .c om int maxRows = getMaxRows(); if (maxRows > 0) { stmt.setMaxRows(maxRows); } int timeout = getQueryTimeout(); if (timeout > 0) { stmt.setQueryTimeout(timeout); } }
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); }/*from w w w .j a v a2 s . c om*/ 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(); }