List of usage examples for java.sql PreparedStatement setMaxRows
void setMaxRows(int max) throws SQLException;
ResultSet object generated by this Statement object can contain to the given number. From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
/** * {@inheritDoc}//from w ww . j a v a2 s . co m */ @Override public MSubmission findSubmissionLastForJob(long jobId, Connection conn) { PreparedStatement stmt = null; ResultSet rs = null; try { stmt = conn.prepareStatement(STMT_SELECT_SUBMISSIONS_FOR_JOB); stmt.setLong(1, jobId); stmt.setMaxRows(1); rs = stmt.executeQuery(); if (!rs.next()) { return null; } return loadSubmission(rs, conn); } catch (SQLException ex) { logException(ex, jobId); throw new SqoopException(DerbyRepoError.DERBYREPO_0040, ex); } finally { closeResultSets(rs); closeStatements(stmt); } }
From source file:org.apache.stratos.adc.mgt.utils.PersistenceManager.java
public static boolean isAliasAlreadyTaken(String alias, String cartridgeType) throws Exception { boolean aliasAlreadyTaken = false; Connection con = null;/*from w w w .j a v a 2 s . c om*/ PreparedStatement statement = null; ResultSet resultSet = null; try { con = StratosDBUtils.getConnection(); String sql = "SELECT SUBSCRIPTION_ID FROM CARTRIDGE_SUBSCRIPTION where ALIAS=? AND CARTRIDGE=? AND STATE != 'UNSUBSCRIBED'"; statement = con.prepareStatement(sql); statement.setString(1, alias); statement.setString(2, cartridgeType); statement.setMaxRows(1); if (log.isDebugEnabled()) { log.debug("Executing query: " + sql); } resultSet = statement.executeQuery(); if (resultSet.next()) { log.info("Already taken.."); aliasAlreadyTaken = true; } } catch (Exception s) { String msg = "Error while sql connection :" + s.getMessage(); log.error(msg, s); throw s; } finally { StratosDBUtils.closeAllConnections(con, statement, resultSet); } return aliasAlreadyTaken; }
From source file:org.efs.openreports.engine.QueryReportEngine.java
public ReportEngineOutput generateReport(ReportEngineInput input) throws ProviderException { Connection conn = null;/*w w w . j a va 2 s. co m*/ PreparedStatement pStmt = null; ResultSet rs = null; try { Report report = input.getReport(); Map parameters = input.getParameters(); ReportDataSource dataSource = report.getDataSource(); conn = dataSourceProvider.getConnection(dataSource.getId()); if (parameters == null || parameters.isEmpty()) { pStmt = conn.prepareStatement(report.getQuery()); } else { // Use JasperReports Query logic to parse parameters in chart // queries JRDesignQuery query = new JRDesignQuery(); query.setText(report.getQuery()); // convert parameters to JRDesignParameters so they can be // parsed Map jrParameters = ORUtil.buildJRDesignParameters(parameters); pStmt = JRQueryExecuter.getStatement(query, jrParameters, parameters, conn); } ORProperty maxRows = propertiesProvider.getProperty(ORProperty.QUERYREPORT_MAXROWS); if (maxRows != null && maxRows.getValue() != null) { pStmt.setMaxRows(Integer.parseInt(maxRows.getValue())); } rs = pStmt.executeQuery(); RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs); List results = rowSetDynaClass.getRows(); DynaProperty[] dynaProperties = rowSetDynaClass.getDynaProperties(); DisplayProperty[] properties = new DisplayProperty[dynaProperties.length]; for (int i = 0; i < dynaProperties.length; i++) { properties[i] = new DisplayProperty(dynaProperties[i].getName(), dynaProperties[i].getType().getName()); } rs.close(); QueryEngineOutput output = new QueryEngineOutput(); output.setResults(results); output.setProperties(properties); return output; } catch (Exception e) { throw new ProviderException("Error executing report query: " + e.getMessage()); } finally { try { if (pStmt != null) pStmt.close(); if (conn != null) conn.close(); } catch (Exception c) { log.error("Error closing"); } } }
From source file:org.fastcatsearch.datasource.reader.DBReader.java
@Override public SchemaSetting getAutoGeneratedSchemaSetting() { Map<String, String> properties = singleSourceConfig.getProperties(); String jdbcSourceId = properties.get("jdbcSourceId"); String dataSQL = properties.get("dataSQL"); IRService service = ServiceManager.getInstance().getService(IRService.class); Connection con = null;//from w w w . j ava2 s .c om PreparedStatement pst = null; ResultSet res = null; ResultSetMetaData meta = null; try { JDBCSourceInfo jdbcInfo = service.getJDBCSourceInfo(jdbcSourceId); if (jdbcInfo != null) { con = getConnection(jdbcInfo); } logger.trace("get jdbc connection : {}", con); if (con != null) { logger.trace("executing sql :{}", dataSQL); pst = con.prepareStatement(dataSQL); pst.setFetchSize(1); pst.setMaxRows(1); res = pst.executeQuery(); res.next(); meta = res.getMetaData(); SchemaSetting setting = new SchemaSetting(); PrimaryKeySetting primaryKeySetting = new PrimaryKeySetting(); List<FieldSetting> fieldSettingList = new ArrayList<FieldSetting>(); List<AnalyzerSetting> analyzerSetting = new ArrayList<AnalyzerSetting>(); List<GroupIndexSetting> groupIndexSetting = new ArrayList<GroupIndexSetting>(); List<IndexSetting> indexSetting = new ArrayList<IndexSetting>(); List<FieldIndexSetting> fieldIndexSetting = new ArrayList<FieldIndexSetting>(); logger.trace("columnCount:{}", meta.getColumnCount()); String tableName = null; for (int inx = 0; inx < meta.getColumnCount(); inx++) { if (tableName == null) { tableName = meta.getTableName(inx + 1); } FieldSetting field = new FieldSetting(); Type type = null; int size = 0; switch (meta.getColumnType(inx + 1)) { case Types.INTEGER: case Types.TINYINT: case Types.SMALLINT: case Types.NUMERIC: type = Type.INT; break; case Types.BIGINT: type = Type.LONG; break; case Types.FLOAT: type = Type.FLOAT; break; case Types.DOUBLE: type = Type.DOUBLE; break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: type = Type.DATETIME; break; case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: type = Type.STRING; break; default: type = Type.STRING; break; } field.setId(meta.getColumnLabel(inx + 1)); field.setName(field.getId()); field.setType(type); field.setSize(size); logger.trace("field add {}", field); fieldSettingList.add(field); } setting.setFieldSettingList(fieldSettingList); setting.setPrimaryKeySetting(primaryKeySetting); setting.setFieldIndexSettingList(fieldIndexSetting); setting.setAnalyzerSettingList(analyzerSetting); setting.setGroupIndexSettingList(groupIndexSetting); setting.setIndexSettingList(indexSetting); return setting; } } catch (IRException e) { logger.error("", e); } catch (SQLException e) { logger.error("", e); } finally { if (res != null) try { res.close(); } catch (SQLException ignore) { } if (pst != null) try { pst.close(); } catch (SQLException ignore) { } if (con != null) try { con.close(); } catch (SQLException ignore) { } } return null; }
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 * //from ww w. ja va 2 s .c om * @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 executeQueryOrUpdatePrepStatement(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); } PreparedStatement preparedStatement = null; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { preparedStatement = connection.prepareStatement(sqlOrder); } Map<Integer, Integer> parameterTypes = null; Map<Integer, String> parameterStringValues = null; // Class to set all the statement parameters ServerPreparedStatementParameters serverPreparedStatementParameters = null; try { ServerSqlUtil.setStatementProperties(preparedStatement, statementHolder); parameterTypes = statementHolder.getParameterTypes(); parameterStringValues = statementHolder.getParameterStringValues(); debug("before ServerPreparedStatementParameters"); serverPreparedStatementParameters = new ServerPreparedStatementParameters(request, username, fileConfigurator, preparedStatement, statementHolder); serverPreparedStatementParameters.setParameters(); // Throws a SQL exception if the order is not authorized: debug("before new SqlSecurityChecker()"); boolean isAllowedAfterAnalysis = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, serverPreparedStatementParameters.getParameterValues()); if (!isAllowedAfterAnalysis) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); debug("Before SqlConfiguratorCall.runIfStatementRefused"); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, ipAddress, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); debug("After SqlConfiguratorCall.runIfStatementRefused"); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; 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()"); if (statementHolder.isExecuteUpdate()) { if (!SqlConfiguratorCall.allowExecuteUpdate(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized for executeUpdate}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } int rc = preparedStatement.executeUpdate(); //br.write(TransferStatus.SEND_OK + CR_LF); //br.write(rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "" + rc); // Write the preparedStatement.getGeneratedKeys() on the stream // if necessary if (usesAutoGeneratedKeys) { ResultSet rs = null; try { rs = preparedStatement.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()) { preparedStatement.setMaxRows(1); } else { ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator); } rs = preparedStatement.executeQuery(); //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); // If a a ResultSet.getMetaData() has been asked, send it // back! 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 + "Prepared statement: " + sqlOrder + CR_LF + "- sql order : " + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF + "- sql values: " + parameterStringValues + CR_LF + "- exception : " + e.toString()); throw e; } finally { // Close the ServerPreparedStatementParameters if (serverPreparedStatementParameters != null) { serverPreparedStatementParameters.close(); } if (preparedStatement != null) { preparedStatement.close(); } // Clean all parameterTypes = null; parameterStringValues = null; serverPreparedStatementParameters = null; } }
From source file:org.LexGrid.LexBIG.Impl.dataAccess.SQLImplementedMethods.java
public static Boolean isCodeInGraph(ConceptReference code, ArrayList<Operation> operations, String internalCodingSchemeName, String internalVersionString, String relationName) throws Exception { SQLInterface si = ResourceManager.instance().getSQLInterface(internalCodingSchemeName, internalVersionString);/*from w w w. j a v a2 s . c o m*/ PreparedStatement isCodeInSet = null; PreparedStatement isCodeInSetT = null; try { RestrictToCodes r = new RestrictToCodes(code); ArrayList<Operation> modifiedOps = new ArrayList<Operation>(); modifiedOps.add(r); modifiedOps.addAll(operations); GraphQuestionQuery query = buildGraphQuestionQuery(modifiedOps, internalCodingSchemeName, internalVersionString, relationName, false); isCodeInSet = si.modifyAndCheckOutPreparedStatement("SELECT " + si.getSQLTableConstants().sourceEntityCodeOrId + " " + getGraphQuestionQueryFromPart(query, si, false) + getGraphQuestionQueryWherePart(query, si)); isCodeInSet.setMaxRows(1); setGraphQuestionQueryParameters(query, isCodeInSet); ResultSet results = isCodeInSet.executeQuery(); // true if there is a result. boolean result = results.next(); results.close(); si.checkInPreparedStatement(isCodeInSet); for (int i = 0; i < operations.size(); i++) { Operation current = operations.get(i); if (!result && current instanceof Union) { // check the unioned graph. Union union = (Union) current; Boolean bool = union.getGraph().isCodeInGraph(code); // if the unioned graph says yes, we say yes. if (bool.booleanValue()) { result = true; } } else if (result && current instanceof Intersection) { // only keep the concept references if they are in both // graphs. No need to process // if we have already decided no. Intersection intersection = (Intersection) current; Boolean bool = intersection.getGraph().isCodeInGraph(code); // if the intersected graph says no, we say no. if (!bool.booleanValue()) { result = false; } } } return new Boolean(result); } catch (Exception e) { throw e; } finally { si.checkInPreparedStatement(isCodeInSet); si.checkInPreparedStatement(isCodeInSetT); } }
From source file:org.LexGrid.LexBIG.Impl.dataAccess.SQLImplementedMethods.java
public static Boolean areCodesRelated(NameAndValue association, ConceptReference sourceCode, ConceptReference targetCode, boolean directOnly, ArrayList<Operation> operations, String internalCodingSchemeName, String internalVersionString, String relationName) throws Exception { SQLInterface si = ResourceManager.instance().getSQLInterface(internalCodingSchemeName, internalVersionString);/* ww w .ja v a 2 s.c o m*/ PreparedStatement areCodesRelated = null; PreparedStatement areCodesRelatedT = null; try { ArrayList<Operation> modifiedOps = new ArrayList<Operation>(); modifiedOps.addAll(operations); RestrictToSourceCodes r1 = new RestrictToSourceCodes(sourceCode); modifiedOps.add(r1); RestrictToTargetCodes r2 = new RestrictToTargetCodes(targetCode); modifiedOps.add(r2); NameAndValueList associations = new NameAndValueList(); associations.addNameAndValue(association); RestrictToAssociations r3 = new RestrictToAssociations(associations, null); modifiedOps.add(r3); GraphQuestionQuery query = buildGraphQuestionQuery(modifiedOps, internalCodingSchemeName, internalVersionString, relationName, false); areCodesRelated = si .modifyAndCheckOutPreparedStatement("SELECT " + si.getSQLTableConstants().sourceEntityCodeOrId + " " + getGraphQuestionQueryFromPart(query, si, false) + " " + getGraphQuestionQueryWherePart(query, si)); areCodesRelated.setMaxRows(1); setGraphQuestionQueryParameters(query, areCodesRelated); ResultSet results = areCodesRelated.executeQuery(); boolean related = results.next(); results.close(); si.checkInPreparedStatement(areCodesRelated); // check the transitive table if (!related && !directOnly) { // I don't need to check if the association is defined as // transitive, because only // transitive associations have entries in the transitive table. query = buildGraphQuestionQuery(modifiedOps, internalCodingSchemeName, internalVersionString, relationName, true); areCodesRelatedT = si.modifyAndCheckOutPreparedStatement( "SELECT " + si.getSQLTableConstants().sourceEntityCodeOrId + " " + getGraphQuestionQueryFromPart(query, si, true) + " " + getGraphQuestionQueryWherePart(query, si)); areCodesRelatedT.setMaxRows(1); setGraphQuestionQueryParameters(query, areCodesRelatedT); results = areCodesRelatedT.executeQuery(); related = results.next(); results.close(); si.checkInPreparedStatement(areCodesRelatedT); } for (int i = 0; i < operations.size(); i++) { Operation current = operations.get(i); if (!related && current instanceof Union) { // check the unioned graph. Union union = (Union) current; Boolean bool = union.getGraph().areCodesRelated(association, sourceCode, targetCode, directOnly); // if the unioned graph says yes, we say yes. if (bool.booleanValue()) { related = true; } } else if (related && current instanceof Intersection) { // only keep the concept references if they are in both // graphs. No need to process // if we have already decided no. Intersection intersection = (Intersection) current; Boolean bool = intersection.getGraph().areCodesRelated(association, sourceCode, targetCode, directOnly); // if the intersected graph says no, we say no. if (!bool.booleanValue()) { related = false; } } } return new Boolean(related); } catch (MissingResourceException e) { throw new LBParameterException("Either the source or the target code could not be properly resolved"); } catch (Exception e) { throw e; } finally { si.checkInPreparedStatement(areCodesRelated); si.checkInPreparedStatement(areCodesRelatedT); } }
From source file:org.LexGrid.LexBIG.Impl.dataAccess.SQLImplementedMethods.java
/** * This returns a list of ConceptReferences that are found to be new - or * unqueried./* w w w . j ava 2 s . c o m*/ * * Note: As of LexGrid database tables version 1.8, the AssociationQualifiers * are loaded via a JOIN instead of separate SELECTs. To preserve backwards * compatibility, if the table does not implement version 1.8, separate * selects will still be used. * * @throws UnexpectedInternalError * @throws MissingResourceException */ protected static ConceptReferenceList helper(SQLInterface si, GHolder resultsToReturn, ArrayList<Operation> operations, boolean forward, String internalCodingSchemeName, String internalVersionString, int userMaxToReturn, String relationName) throws SQLException, LBInvocationException, LBParameterException, MissingResourceException, UnexpectedInternalError { PreparedStatement getRelations = null; PreparedStatement getQualifiers = null; try { GraphQuestionQuery query = buildGraphQuestionQuery(operations, internalCodingSchemeName, internalVersionString, relationName, false); ConceptReferenceList next = new ConceptReferenceList(); getQualifiers = si.checkOutPreparedStatement("Select " + SQLTableConstants.TBLCOL_QUALIFIERNAME + ", " + SQLTableConstants.TBLCOL_QUALIFIERVALUE + " from " + si.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_E_QUALS) + " where " + si.getSQLTableConstants().codingSchemeNameOrId + " = ? and " + SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY + " = ? "); getRelations = si.modifyAndCheckOutPreparedStatement( "SELECT ca." + si.getSQLTableConstants().entityCodeOrAssociationId + ", " + "ca." + si.getSQLTableConstants().sourceCSIdOrEntityCodeNS + ", " + "ca." + si.getSQLTableConstants().sourceEntityCodeOrId + ", " + "ca." + si.getSQLTableConstants().targetCSIdOrEntityCodeNS + ", " + "ca." + si.getSQLTableConstants().targetEntityCodeOrId + ", " + "ca." + SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY + ", " + " entitySource." + SQLTableConstants.TBLCOL_ENTITYDESCRIPTION + " {AS} " + " sourceEntityDescription" + ", " + " entityTarget." + SQLTableConstants.TBLCOL_ENTITYDESCRIPTION + " {AS} " + " targetEntityDescription" + (isEntityAssnsToEQualsIndexPresent(si) && !query.qualiferTableRequired_ ? ", " + "caa." + SQLTableConstants.TBLCOL_QUALIFIERNAME + ", " + "caa." + SQLTableConstants.TBLCOL_QUALIFIERVALUE : " ") + getGraphQuestionQueryFromPart(query, si, false) + " left join " + si.getTableName(SQLTableConstants.ENTITY) + " {AS} entitySource on " + " ca." + si.getSQLTableConstants().codingSchemeNameOrId + " = entitySource." + si.getSQLTableConstants().codingSchemeNameOrId + " and " + (si.supports2009Model() ? " ca." + SQLTableConstants.TBLCOL_SOURCEENTITYCODENAMESPACE + " = entitySource." + SQLTableConstants.TBLCOL_ENTITYCODENAMESPACE + " and " : "") + " ca." + si.getSQLTableConstants().sourceEntityCodeOrId + " = entitySource." + si.getSQLTableConstants().entityCodeOrId + " left join " + si.getTableName(SQLTableConstants.ENTITY) + " {AS} entityTarget on " + " ca." + si.getSQLTableConstants().codingSchemeNameOrId + " = entityTarget." + si.getSQLTableConstants().codingSchemeNameOrId + " and " + (si.supports2009Model() ? " ca." + SQLTableConstants.TBLCOL_TARGETENTITYCODENAMESPACE + " = entityTarget." + SQLTableConstants.TBLCOL_ENTITYCODENAMESPACE + " and " : "") + " ca." + si.getSQLTableConstants().targetEntityCodeOrId + " = entityTarget." + si.getSQLTableConstants().entityCodeOrId + (isEntityAssnsToEQualsIndexPresent(si) && !query.qualiferTableRequired_ ? " left join " + si.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_E_QUALS) + " {AS} caa on " + " ca." + SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY + " = caa." + SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY : "") + getGraphQuestionQueryWherePart(query, si)); setGraphQuestionQueryParameters(query, getRelations); int curCount = resultsToReturn.getNodeCount(); int systemMaxToReturn = ResourceManager.instance().getSystemVariables().getMaxResultSize(); if (curCount >= systemMaxToReturn) { resultsToReturn.setResultsSkipped(true); getLogger().info("Potential graph results are being skipped due to hitting system max limit"); return next; } // add a small amount over the limit - this way a warning should be // generated farther down. getRelations.setMaxRows(systemMaxToReturn - curCount + 10); if (userMaxToReturn > 0) { if (curCount >= userMaxToReturn) { resultsToReturn.setResultsSkipped(true); getLogger() .info("Potential graph results are being skipped due to hitting user requested limit"); return next; } if (userMaxToReturn < systemMaxToReturn) { // add a small amount over the limit - this way a warning // should be generated farther // down. getRelations.setMaxRows(userMaxToReturn - curCount + 10); } } ResultSet results = getRelations.executeQuery(); Map<String, AssociationInfoHolder> relationsMap = new HashMap<String, AssociationInfoHolder>(); while (results.next()) { String multiAttributeKey = results.getString(SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY); //if the multiAttributesKey is blank, generate one. It is now the primary key, so it should //always be present... but if its not (in earlier table versions it could be null) then we //want to generated a random one. Otherwise, we can't use it as a key for the relations map //(above). It is ok to generate a random one because if it is null, we know there are going //to be no Qualifiers associated with it. if (StringUtils.isBlank(multiAttributeKey)) { multiAttributeKey = UUID.randomUUID().toString(); } String association = results.getString(si.getSQLTableConstants().entityCodeOrAssociationId); String sourceCodingSchemeIdOrNS = results .getString(si.getSQLTableConstants().sourceCSIdOrEntityCodeNS); String sourceCodingSchemeName = mapToCodingSchemeName(si, internalCodingSchemeName, sourceCodingSchemeIdOrNS); String sourceConceptCode = results.getString(si.getSQLTableConstants().sourceEntityCodeOrId); String targetCodingSchemeIdOrNS = results .getString(si.getSQLTableConstants().targetCSIdOrEntityCodeNS); String targetCodingSchemeName = mapToCodingSchemeName(si, internalCodingSchemeName, targetCodingSchemeIdOrNS); String targetConceptCode = results.getString(si.getSQLTableConstants().targetEntityCodeOrId); String sourceEntityDescription = results.getString("sourceEntityDescription"); String targetEntityDescription = results.getString("targetEntityDescription"); String qualifierName = null; String qualifierValue = null; if (isEntityAssnsToEQualsIndexPresent(si) && !query.qualiferTableRequired_) { qualifierName = results.getString(SQLTableConstants.TBLCOL_QUALIFIERNAME); qualifierValue = results.getString(SQLTableConstants.TBLCOL_QUALIFIERVALUE); } if (StringUtils.isBlank(multiAttributeKey) || !relationsMap.containsKey(multiAttributeKey)) { if (sourceConceptCode.equals("@")) { if (forward) { ConceptReference cr = resultsToReturn.addNode(targetCodingSchemeName, targetCodingSchemeIdOrNS, targetConceptCode, null, targetEntityDescription, internalCodingSchemeName, internalVersionString, true); if (cr != null) { next.addConceptReference(cr); } } } else if (targetConceptCode.equals("@@")) { if (!forward) { ConceptReference cr = resultsToReturn.addNode(sourceCodingSchemeName, sourceCodingSchemeIdOrNS, sourceConceptCode, null, sourceEntityDescription, internalCodingSchemeName, internalVersionString, false); if (cr != null) { next.addConceptReference(cr); } } } else { NameAndValueList quals = null; //check if the index on the multiAttributes key is present //in the quals table. If it is is not, we want to get the //quals as seperate SELECT statements. if ((!isEntityAssnsToEQualsIndexPresent(si) || query.qualiferTableRequired_) && multiAttributeKey != null && multiAttributeKey.length() > 0) { quals = new NameAndValueList(); getQualifiers.setString(1, internalCodingSchemeName); getQualifiers.setString(2, multiAttributeKey); ResultSet qualifiers = getQualifiers.executeQuery(); while (qualifiers.next()) { NameAndValue nv = new NameAndValue(); nv.setName(qualifiers.getString(SQLTableConstants.TBLCOL_QUALIFIERNAME)); nv.setContent(qualifiers.getString(SQLTableConstants.TBLCOL_QUALIFIERVALUE)); quals.addNameAndValue(nv); } qualifiers.close(); } else { if (StringUtils.isNotBlank(qualifierName)) { quals = new NameAndValueList(); NameAndValue nv = new NameAndValue(); nv.setName(qualifierName); nv.setContent(qualifierValue); quals.addNameAndValue(nv); } } AssociationInfoHolder infoHolder = new SQLImplementedMethods.AssociationInfoHolder(); infoHolder.sourceCodingSchemeName = sourceCodingSchemeName; infoHolder.sourceCodingSchemeIdOrNS = sourceCodingSchemeIdOrNS; infoHolder.sourceConceptCode = sourceConceptCode; infoHolder.sourceCodeTypes = null; infoHolder.sourceEntityDescription = sourceEntityDescription; infoHolder.relationName = relationName; infoHolder.association = association; infoHolder.targetCodingSchemeName = targetCodingSchemeName; infoHolder.targetCodingSchemeIdOrNS = targetCodingSchemeIdOrNS; infoHolder.targetConceptCode = targetConceptCode; infoHolder.targetCodeTypes = null; infoHolder.targetEntityDescription = targetEntityDescription; infoHolder.quals = quals; infoHolder.forward = forward; infoHolder.internalCodingSchemeName = internalCodingSchemeName; infoHolder.internalVersionString = internalVersionString; relationsMap.put(multiAttributeKey, infoHolder); } } else { AssociationInfoHolder assocInfo = relationsMap.get(multiAttributeKey); NameAndValue nv = new NameAndValue(); nv.setName(qualifierName); nv.setContent(qualifierValue); assocInfo.quals.addNameAndValue(nv); } curCount = resultsToReturn.getNodeCount(); if (curCount >= systemMaxToReturn) { resultsToReturn.setResultsSkipped(true); getLogger().info("Potential graph results are being skipped due to hitting system max limit"); next.removeAllConceptReference(); return next; } if (userMaxToReturn > 0 && curCount >= userMaxToReturn) { resultsToReturn.setResultsSkipped(true); getLogger() .info("Potential graph results are being skipped due to hitting user requested limit"); next.removeAllConceptReference(); return next; } } results.close(); for (AssociationInfoHolder infoHolder : relationsMap.values()) { ConceptReference cr = resultsToReturn.addAssociation(infoHolder.sourceCodingSchemeName, infoHolder.sourceCodingSchemeIdOrNS, infoHolder.sourceConceptCode, infoHolder.sourceCodeTypes, infoHolder.sourceEntityDescription, infoHolder.relationName, infoHolder.association, infoHolder.targetCodingSchemeName, infoHolder.targetCodingSchemeIdOrNS, infoHolder.targetConceptCode, infoHolder.targetCodeTypes, infoHolder.targetEntityDescription, infoHolder.quals, infoHolder.forward, infoHolder.internalCodingSchemeName, infoHolder.internalVersionString); if (cr != null) { next.addConceptReference(cr); } } return next; } finally { si.checkInPreparedStatement(getRelations); si.checkInPreparedStatement(getQualifiers); } }
From source file:org.LexGrid.LexBIG.Impl.dataAccess.SQLImplementedMethods.java
/** * This returns a list of ConceptReferences that are orphaned. * // w w w . j a v a 2s. c om * @throws UnexpectedInternalError * @throws MissingResourceException */ public static ResolvedConceptReferenceList orphanedEntityQuery(SQLInterface si, ArrayList<Operation> operations, boolean forward, String internalCodingSchemeName, String internalVersionString, String containerName) throws LBException { String orphanedSQLString = null; String orphanedSubSelectSQLStringTran = null; String orphanedSubSelectSQLString = null; PreparedStatement orphanedPreparedStmt = null; SQLTableConstants stc = si.getSQLTableConstants(); long startTime = System.currentTimeMillis(); try { orphanedSQLString = "Select * from " + si.getTableName(SQLTableConstants.ENTITY) + " where "; String entityNS_and_enitityCode_subQuery = "(" + SQLTableConstants.TBLCOL_ENTITYCODENAMESPACE + ", " + SQLTableConstants.TBLCOL_ENTITYCODE + ") NOT IN "; GraphQuestionQuery queryTran = buildGraphQuestionQuery(operations, internalCodingSchemeName, internalVersionString, containerName, true); if (forward) { orphanedSubSelectSQLStringTran = "SELECT ca." + stc.targetCSIdOrEntityCodeNS + " , ca." + stc.targetEntityCodeOrId + getGraphQuestionQueryFromPart(queryTran, si, true) + getGraphQuestionQueryWherePart(queryTran, si); } else { orphanedSubSelectSQLStringTran = "SELECT ca." + stc.sourceCSIdOrEntityCodeNS + ", ca." + stc.sourceEntityCodeOrId + getGraphQuestionQueryFromPart(queryTran, si, true) + getGraphQuestionQueryWherePart(queryTran, si); } orphanedSQLString += entityNS_and_enitityCode_subQuery + "( " + orphanedSubSelectSQLStringTran + " ) "; orphanedSQLString += " AND " + SQLTableConstants.TBLCOL_ENTITYCODE + " NOT IN ('@' , '@@') "; orphanedPreparedStmt = si.modifyAndCheckOutPreparedStatement(orphanedSQLString); setGraphQuestionQueryParameters(queryTran, orphanedPreparedStmt); int systemMaxToReturn = ResourceManager.instance().getSystemVariables().getMaxResultSize(); // add a small amount over the limit - this way a warning should be // generated farther down. orphanedPreparedStmt.setMaxRows(systemMaxToReturn); //getLogger().debug("\nstatement= " + orphanedPreparedStmt); System.out.println("\nstatement= " + orphanedPreparedStmt); ResultSet results = orphanedPreparedStmt.executeQuery(); ResolvedConceptReferenceList rcrl = new ResolvedConceptReferenceList(); while (results.next()) { ResolvedConceptReference rcr = new ResolvedConceptReference(); String codingSchemeIdOrNS = results.getString(si.getSQLTableConstants().codingSchemeNameOrId); String entityCode = results.getString(si.getSQLTableConstants().entityCodeOrId); EntityDescription ed = new EntityDescription(); ed.setContent(results.getString(SQLTableConstants.TBLCOL_ENTITYDESCRIPTION)); if (si.supports2009Model()) { String namespace = results.getString(SQLTableConstants.TBLCOL_ENTITYCODENAMESPACE); rcr.setCodeNamespace(namespace); } rcr.setCodingSchemeName(codingSchemeIdOrNS); rcr.setConceptCode(entityCode); rcr.setEntityDescription(ed); rcrl.addResolvedConceptReference(rcr); } getLogger().debug("Time to execute orphanedQuery=" + (System.currentTimeMillis() - startTime)); return rcrl; } catch (Exception e) { String logId = getLogger().error("Unexpected Error", e); throw new LBException("Unexpected Internal Error", e); } finally { si.checkInPreparedStatement(orphanedPreparedStmt); } }
From source file:org.LexGrid.LexBIG.Impl.dataAccess.SQLImplementedMethods.java
/** * This returns a list of CountConceptReferences that hold the count of the concepts at the next/prev level depending * the value of the forward flag//from www .jav a 2 s . c o m * @throws UnexpectedInternalError * @throws MissingResourceException */ public static ConceptReferenceList countQuery(SQLInterface si, ArrayList<Operation> operations, boolean forward, String internalCodingSchemeName, String internalVersionString, String containerName) throws LBException { PreparedStatement getCountStmt = null; PreparedStatement getQualifiers = null; SQLTableConstants stc = si.getSQLTableConstants(); long startTime = System.currentTimeMillis(); try { GraphQuestionQuery query = buildGraphQuestionQuery(operations, internalCodingSchemeName, internalVersionString, containerName, false); query.whereClause_.append(" AND " + stc.sourceEntityCodeOrId + " NOT IN ('@' , '@@') " + " AND " + stc.targetEntityCodeOrId + " NOT IN ('@' , '@@') "); ConceptReferenceList crl = new ConceptReferenceList(); if (forward) { query.whereClause_.append( " group by ca." + stc.sourceCSIdOrEntityCodeNS + ", ca." + stc.sourceEntityCodeOrId); getCountStmt = si.modifyAndCheckOutPreparedStatement("SELECT ca." + stc.sourceCSIdOrEntityCodeNS + " {AS} codingSchemeIdOrNS, " + "ca." + stc.sourceEntityCodeOrId + " {AS} entityCode, " + "count(distinct ca." + stc.targetEntityCodeOrId + ") {AS} count " + " " + getGraphQuestionQueryFromPart(query, si, false) + getGraphQuestionQueryWherePart(query, si)); } else { query.whereClause_.append( " group by ca." + stc.targetCSIdOrEntityCodeNS + ", ca." + stc.targetEntityCodeOrId); getCountStmt = si.modifyAndCheckOutPreparedStatement("SELECT ca." + stc.targetCSIdOrEntityCodeNS + " {AS} codingSchemeIdOrNS, " + "ca." + stc.targetEntityCodeOrId + " {AS} entityCode, " + "count(distinct ca." + stc.sourceEntityCodeOrId + ") {AS} count " + " " + getGraphQuestionQueryFromPart(query, si, false) + getGraphQuestionQueryWherePart(query, si)); } setGraphQuestionQueryParameters(query, getCountStmt); int systemMaxToReturn = ResourceManager.instance().getSystemVariables().getMaxResultSize(); // add a small amount over the limit - this way a warning should be // generated farther down. getCountStmt.setMaxRows(systemMaxToReturn); //getLogger().debug("\nstatement= "+getCountStmt); ResultSet results = getCountStmt.executeQuery(); while (results.next()) { String codingSchemeIdOrNS = results.getString("codingSchemeIdOrNS"); String entityCode = results.getString("entityCode"); int count = results.getInt("count"); CountConceptReference ccr = new CountConceptReference(internalCodingSchemeName, codingSchemeIdOrNS, entityCode, count); crl.addConceptReference(ccr); } //getLogger().debug("Time to execute countQuery=" + (System.currentTimeMillis() - startTime)); return crl; } catch (Exception e) { String logId = getLogger().error("Unexpected Error", e); throw new LBException("Unexpected Internal Error", e); } finally { si.checkInPreparedStatement(getCountStmt); si.checkInPreparedStatement(getQualifiers); } }