List of usage examples for java.sql Statement setQueryTimeout
void setQueryTimeout(int seconds) throws SQLException;
Statement object to execute to the given number of seconds. From source file:com.flexive.core.search.SqlSearch.java
/** * Executes the search.// w ww .j a v a 2s.c om * * @return the resultset * @throws FxSqlSearchException if the search failed */ public FxResultSet executeQuery() throws FxSqlSearchException { parseQuery(); // Check if the statement will produce any resultset at all if (statement.getType() == FxStatement.Type.EMPTY) { return new FxResultSetImpl(statement, this.parserExecutionTime, 0, startIndex, fetchRows, location, viewType, null, -1, -1); } // Execute select Statement stmt = null; Connection con = null; FxResultSetImpl fx_result = null; final long startTime = java.lang.System.currentTimeMillis(); DataSelector ds = null; DataFilter df = null; String selectSql = null; try { // Init switch (params.getCacheMode()) { case ON: /*cacheTbl = DatabaseConst.TBL_SEARCHCACHE_PERM; searchId = seq.getId(SequencerEngine.System.SEARCHCACHE_PERM); break;*/ case OFF: case READ_ONLY: if (params.isHintNoResultInfo() && storage.isDirectSearchSupported()) { cacheTbl = ""; // will be set to the SELECT statement later searchId = 1; // not relevant, since no cache table is populated } else { cacheTbl = DatabaseConst.TBL_SEARCHCACHE_MEMORY; searchId = seq.getId(FxSystemSequencer.SEARCHCACHE_MEMORY); } break; default: // Can never happen cacheTbl = null; } // initialize search languages final String[] searchLanguages = statement.getTables()[0].getSearchLanguages(); if (searchLanguages == null || searchLanguages.length == 0) { this.searchLanguageIds = null; } else { this.searchLanguageIds = Lists.newArrayListWithCapacity(searchLanguages.length); for (String searchLanguage : searchLanguages) { this.searchLanguageIds.add(environment.getLanguage(searchLanguage).getId()); } } con = Database.getDbConnection(); pr = new PropertyResolver(con); //init filter and selector df = StorageManager.getDataFilter(con, this); ds = StorageManager.getDataSelector(this); // Find all matching objects df.build(); // Wildcard handling depending on the found entries replaceWildcard(df); if (statement.getOrderByValues().isEmpty() && !(params.isIgnoreResultPreferences() && params.isNoInternalSort())) { // add user-defined order by final List<ResultOrderByInfo> orderByColumns; if (params.isIgnoreResultPreferences()) { orderByColumns = Arrays .asList(new ResultOrderByInfo(Table.CONTENT, "@pk", null, SortDirection.ASCENDING)); } else { orderByColumns = getResultPreferences(df).getOrderByColumns(); } for (ResultOrderByInfo column : orderByColumns) { try { statement.addOrderByValue(new OrderByValue(column.getColumnName(), column.getDirection().equals(SortDirection.ASCENDING))); } catch (SqlParserException e) { if (LOG.isTraceEnabled()) { LOG.trace("Ignoring user preferences column " + column + " since it was not selected."); } } } } // If specified create a briefcase with the found data long createdBriefcaseId = -1; if (this.params.getWillCreateBriefcase()) { createdBriefcaseId = copyToBriefcase(con, ds, df); } final UserTicket ticket = FxContext.getUserTicket(); //list containing all used types with property permission checks enabled final List<FxType> propertyPermTypes = new ArrayList<FxType>(df.getContentTypes().size()); //cache for assignments that are allowed/denied for types with property permission checks enabled List<String> allowedAssignment = null; List<String> deniedAssignment = null; //gather all types with property permission checks enabled if (!ticket.isGlobalSupervisor()) { for (FxFoundType check : df.getContentTypes()) { FxType c = environment.getType(check.getContentTypeId()); if (c.isUsePropertyPermissions()) propertyPermTypes.add(c); } } if (this.params.isHintNoResultInfo() && storage.isDirectSearchSupported()) { // take the filter SQL and select directly cacheTbl = "(" + df.getDataSelectSql() + ")"; } // Select all desired rows for the resultset selectSql = ds.build(con); stmt = con.createStatement(); if (df.isQueryTimeoutSupported()) stmt.setQueryTimeout(this.params.getQueryTimeout()); df.setVariable(stmt, "rownr", "1"); // Fetch the result ResultSet rs = stmt.executeQuery(selectSql); int dbSearchTime = (int) (java.lang.System.currentTimeMillis() - startTime); fx_result = new FxResultSetImpl(statement, this.parserExecutionTime, dbSearchTime, startIndex, fetchRows, location, viewType, df.getContentTypes(), getTypeFilter() != null ? getTypeFilter().getId() : -1, createdBriefcaseId); fx_result.setUserWildcardIndex(indexOfUserPropsWildcard != -1 ? indexOfUserPropsWildcard + 1 : -1); fx_result.setTotalRowCount(this.params.isHintNoResultInfo() ? -1 : df.getFoundEntries()); fx_result.setTruncated(df.isTruncated()); final long fetchStart = java.lang.System.currentTimeMillis(); while (rs.next()) { Object[] row = new Object[pr.getResultSetColumns().size()]; int i = 0; final long typeId = rs.getLong(DataSelector.COL_TYPEID); for (PropertyEntry entry : pr.getResultSetColumns()) { Object val = entry.getResultValue(rs, language.getId(), true, typeId); //in case we have types with property permissions enabled, inaccessible //properties have to be wrapped with with FxNoAccess objects if (val instanceof FxValue && !((FxValue) val).isEmpty() && propertyPermTypes.size() > 0) { if (allowedAssignment == null) allowedAssignment = new ArrayList<String>(20); if (deniedAssignment == null) deniedAssignment = new ArrayList<String>(20); FxValue v = (FxValue) val; String xp = XPathElement.toXPathNoMult(v.getXPath()); if (!allowedAssignment.contains(xp)) { if (!deniedAssignment.contains(xp)) { FxPropertyAssignment pa = (FxPropertyAssignment) environment.getAssignment(xp); if (pa.getAssignedType().isUsePropertyPermissions() && !ticket .mayReadACL(pa.getACL().getId(), rs.getLong(DataSelector.COL_CREATED_BY))) { deniedAssignment.add(xp); val = new FxNoAccess(ticket, (FxValue) val); } else allowedAssignment.add(xp); } else val = new FxNoAccess(ticket, (FxValue) val); } } row[i] = val; i++; } fx_result.addRow(row); if (fx_result.getRowCount() == fetchRows) { // Maximum fetch size reached, stop break; } } int timeSpent = (int) (java.lang.System.currentTimeMillis() - fetchStart); fx_result.setFetchTime(timeSpent); return fx_result; } catch (FxSqlSearchException exc) { throw exc; } catch (SQLException exc) { if (StorageManager.isQueryTimeout(exc)) { if (LOG.isWarnEnabled()) { LOG.warn("Query timeout after " + params.getQueryTimeout() + " seconds:\n" + query + "\n\nSQL:\n" + selectSql); } throw new FxSqlSearchException(exc, "ex.sqlSearch.query.timeout", params.getQueryTimeout()); } else if (StorageManager.isDeadlock(exc)) { if (LOG.isInfoEnabled()) { LOG.info("Deadlock detected during query executing, waiting 100ms and retrying..."); try { Thread.sleep(100); } catch (InterruptedException e) { // ignore } return executeQuery(); } } throw new FxSqlSearchException(LOG, exc, "ex.sqlSearch.failed", exc.getMessage(), query, selectSql); } catch (Exception e) { throw new FxSqlSearchException(LOG, e, "ex.sqlSearch.failed", e.getMessage(), query, selectSql); } finally { try { if (ds != null) ds.cleanup(con); } catch (Throwable t) { /*ignore*/} try { if (df != null) df.cleanup(); } catch (Throwable t) { /*ignore*/} Database.closeObjects(SqlSearch.class, con, stmt); if (fx_result != null) { int timeSpent = (int) (java.lang.System.currentTimeMillis() - startTime); fx_result.setTotalTime(timeSpent); } if (LOG.isTraceEnabled() && fx_result != null) { LOG.trace(String.format("FxSQL query in [%5dms]: " + query.replace('\n', ' '), fx_result.getTotalTime())); } } }
From source file:com.flexive.core.search.genericSQL.GenericSQLDataFilter.java
/** * Builds the filter.//from www . j a va2s .c o m * * @throws FxSqlSearchException if the build failed */ @Override public void build() throws FxSqlSearchException, SQLException { Statement stmt = null; String sql = null; try { final String dataSelect; final String columnFilter = "SELECT search_id, id, ver, tdef, created_by FROM "; if (getStatement().getType() == FxStatement.Type.ALL) { // The statement will not filter the data final String securityFilter = getSecurityFilter("data2"); final String filters = StringUtils.defaultIfEmpty(securityFilter, "1=1") + (getStatement().getBriefcaseFilter().length == 0 ? getVersionFilter("data2") : "") + getDeactivatedTypesFilter("data2") + getInactiveMandatorsFilter("data2"); dataSelect = columnFilter + "(" + selectOnMainTable(filters, "data2") + ") r"; } else if (isMainTableQuery(getStatement().getRootBrace())) { // optimize queries that operate only on FX_CONTENT final String securityFilter = getSecurityFilter("cd"); final String filters = StringUtils.defaultIfEmpty(securityFilter, "1=1") + (getStatement().getBriefcaseFilter().length == 0 ? getVersionFilter("cd") : "") + getDeactivatedTypesFilter("cd") + getInactiveMandatorsFilter("cd") + " AND (" + getOptimizedMainTableConditions(getStatement().getRootBrace(), "cd") + ")"; dataSelect = columnFilter + "(" + selectOnMainTable(filters, "cd") + ") r"; } else { // The statement filters the data StringBuilder result = new StringBuilder(5000); // extract top-level conditions on the main table final Brace mainTableConditions = getStatement().getRootBrace() .extractConditions(new Brace.ExtractFunction() { @Override public boolean shouldExtract(Condition cond) { try { final Pair<String, ConditionTableInfo> pi = getPropertyInfo(cond); return getStatement().getRootBrace().isAnd() && DatabaseConst.TBL_CONTENT.equals(pi.getFirst()); } catch (FxSqlSearchException e) { throw e.asRuntimeException(); } } }); // build main condition (without top-level main table conditions) build(result, getStatement().getRootBrace()); // Remove leading and ending brace result.deleteCharAt(0); result.deleteCharAt(result.length() - 1); // Finalize the select final String securityFilter = getSecurityFilter("data2"); final String mainConditions = mainTableConditions.getSize() > 0 ? getOptimizedMainTableConditions(mainTableConditions, "main") : null; dataSelect = columnFilter + "(SELECT DISTINCT " + search.getSearchId() + " AS search_id,data.id,data.ver,main.tdef,main.created_by,main.step,main.acl,main.mandator \n" + "FROM (" + result.toString() + ") data, " + DatabaseConst.TBL_CONTENT + " main\n" + "WHERE data.ver=main.ver AND data.id=main.id" + (StringUtils.isNotBlank(mainConditions) ? " AND " + mainConditions : "") + ") data2\n" + (StringUtils.isNotBlank(securityFilter) // Limit by the specified max items ? "WHERE " + securityFilter + getResultRowsLimit(true) : getResultRowsLimit(false)); } this.dataSelectSql = dataSelect; if (!search.getParams().isHintNoResultInfo() || !search.getStorage().isDirectSearchSupported()) { // Find all matching data entities and store them sql = "INSERT INTO " + search.getCacheTable() + " " + dataSelect; if (LOG.isDebugEnabled()) { LOG.debug("Filter SQL: " + sql); } stmt = getConnection().createStatement(); if (isQueryTimeoutSupported()) stmt.setQueryTimeout(search.getParams().getQueryTimeout()); stmt.executeUpdate(sql); if (!search.getParams().isHintNoResultInfo()) { analyzeResult(); } } } catch (SQLException exc) { LOG.error("Failed to execute (" + exc.getMessage() + "): " + sql); throw exc; } catch (Exception e) { throw new FxSqlSearchException(LOG, e, "ex.sqlSearch.failedToBuildDataFilter", e.getMessage(), search.getQuery()); } finally { Database.closeObjects(GenericSQLDataFilter.class, null, stmt); } }