List of usage examples for java.sql Statement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:com.linkedin.pinot.integration.tests.BaseClusterIntegrationTest.java
/** * Run equivalent Pinot and H2 query and compare the results. * <p>LIMITATIONS:/*from ww w. j av a 2s .c o m*/ * <ul> * <li>Skip comparison for selection and aggregation group-by when H2 results are too large to exhaust.</li> * <li>Do not examine the order of result records.</li> * </ul> * * @param pqlQuery Pinot PQL query. * @param sqlQueries H2 SQL queries. * @throws Exception */ protected void runQuery(String pqlQuery, @Nullable List<String> sqlQueries) throws Exception { try { _queryCount++; // Run the query. // TODO Use Pinot client API for this JSONObject response = postQuery(pqlQuery); // Check exceptions. JSONArray exceptions = response.getJSONArray("exceptions"); if (exceptions.length() > 0) { String failureMessage = "Got exceptions: " + exceptions; failure(pqlQuery, sqlQueries, failureMessage, null); return; } // Check total docs. long numTotalDocs = response.getLong("totalDocs"); if (numTotalDocs != TOTAL_DOCS) { String failureMessage = "Number of total documents does not match, expected: " + TOTAL_DOCS + ", got: " + numTotalDocs; failure(pqlQuery, sqlQueries, failureMessage, null); return; } // Skip comparison if SQL queries not specified. if (sqlQueries == null) { return; } // Check results. Statement h2statement = _connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); int numDocsScanned = response.getInt("numDocsScanned"); if (response.has("aggregationResults")) { // Aggregation and Group-by results. // Get results type. JSONArray aggregationResultsArray = response.getJSONArray("aggregationResults"); int numAggregationResults = aggregationResultsArray.length(); int numSqlQueries = sqlQueries.size(); if (numAggregationResults != numSqlQueries) { String failureMessage = "Number of aggregation results: " + numAggregationResults + " does not match number of SQL queries: " + numSqlQueries; failure(pqlQuery, sqlQueries, failureMessage); } JSONObject firstAggregationResult = aggregationResultsArray.getJSONObject(0); if (firstAggregationResult.has("value")) { // Aggregation results. // Check over all aggregation functions. for (int i = 0; i < numAggregationResults; i++) { // Get expected value for the aggregation. h2statement.execute(sqlQueries.get(i)); ResultSet sqlResultSet = h2statement.getResultSet(); sqlResultSet.first(); String sqlValue = sqlResultSet.getString(1); // If SQL value is null, it means no record selected in H2. if (sqlValue == null) { // Check number of documents scanned is 0. if (numDocsScanned != 0) { String failureMessage = "No record selected in H2 but number of records selected in Pinot: " + numDocsScanned; failure(pqlQuery, sqlQueries, failureMessage); } // Skip further comparison. return; } // Get actual value for the aggregation. String pqlValue = aggregationResultsArray.getJSONObject(i).getString("value"); // Fuzzy compare expected value and actual value. double expectedValue = Double.parseDouble(sqlValue); double actualValue = Double.parseDouble(pqlValue); if (Math.abs(actualValue - expectedValue) >= 1.0) { String failureMessage = "Value: " + i + " does not match, expected: " + sqlValue + ", got: " + pqlValue; failure(pqlQuery, sqlQueries, failureMessage); return; } } } else if (firstAggregationResult.has("groupByResult")) { // Group-by results. // Get number of groups and number of group keys in each group. JSONArray firstGroupByResults = aggregationResultsArray.getJSONObject(0) .getJSONArray("groupByResult"); int numGroups = firstGroupByResults.length(); // If no group-by result returned by Pinot, set numGroupKeys to 0 since no comparison needed. int numGroupKeys; if (numGroups == 0) { numGroupKeys = 0; } else { numGroupKeys = firstGroupByResults.getJSONObject(0).getJSONArray("group").length(); } // Check over all aggregation functions. for (int i = 0; i < numAggregationResults; i++) { // Get number of group keys. JSONArray groupByResults = aggregationResultsArray.getJSONObject(i) .getJSONArray("groupByResult"); // Construct expected result map from group keys to value. h2statement.execute(sqlQueries.get(i)); ResultSet sqlResultSet = h2statement.getResultSet(); Map<String, String> expectedValues = new HashMap<>(); int sqlNumGroups; for (sqlNumGroups = 0; sqlResultSet.next() && sqlNumGroups < MAX_COMPARISON_LIMIT; sqlNumGroups++) { if (numGroupKeys != 0) { StringBuilder groupKey = new StringBuilder(); for (int groupKeyIndex = 1; groupKeyIndex <= numGroupKeys; groupKeyIndex++) { // Convert boolean value to lower case. groupKey.append( convertBooleanToLowerCase(sqlResultSet.getString(groupKeyIndex))) .append(' '); } expectedValues.put(groupKey.toString(), sqlResultSet.getString(numGroupKeys + 1)); } } if (sqlNumGroups == 0) { // No record selected in H2. // Check if no record selected in Pinot. if (numGroups != 0) { String failureMessage = "No group returned in H2 but number of groups returned in Pinot: " + numGroups; failure(pqlQuery, sqlQueries, failureMessage); return; } // Check if number of documents scanned is 0. if (numDocsScanned != 0) { String failureMessage = "No group returned in Pinot but number of records selected: " + numDocsScanned; failure(pqlQuery, sqlQueries, failureMessage); } // Skip further comparison. return; } else if (sqlNumGroups < MAX_COMPARISON_LIMIT) { // Only compare exhausted results. // Check that all Pinot results are contained in the H2 results. for (int resultIndex = 0; resultIndex < numGroups; resultIndex++) { // Fetch Pinot group keys. JSONObject groupByResult = groupByResults.getJSONObject(resultIndex); JSONArray group = groupByResult.getJSONArray("group"); StringBuilder groupKeyBuilder = new StringBuilder(); for (int groupKeyIndex = 0; groupKeyIndex < numGroupKeys; groupKeyIndex++) { groupKeyBuilder.append(group.getString(groupKeyIndex)).append(' '); } String groupKey = groupKeyBuilder.toString(); // Check if Pinot group keys contained in H2 results. if (!expectedValues.containsKey(groupKey)) { String failureMessage = "Group returned in Pinot but not in H2: " + groupKey; failure(pqlQuery, sqlQueries, failureMessage); return; } // Fuzzy compare expected value and actual value. String sqlValue = expectedValues.get(groupKey); String pqlValue = groupByResult.getString("value"); double expectedValue = Double.parseDouble(sqlValue); double actualValue = Double.parseDouble(pqlValue); if (Math.abs(actualValue - expectedValue) >= 1.0) { String failureMessage = "Value: " + i + " does not match, expected: " + sqlValue + ", got: " + pqlValue + ", for group: " + groupKey; failure(pqlQuery, sqlQueries, failureMessage); return; } } } else { // Cannot get exhausted results. // Skip further comparison. LOGGER.debug("SQL: {} returned at least {} rows, skipping comparison.", sqlQueries.get(0), MAX_COMPARISON_LIMIT); return; } } } else { // Neither aggregation or group-by results. String failureMessage = "Inside aggregation results, no aggregation or group-by results found"; failure(pqlQuery, sqlQueries, failureMessage); } } else if (response.has("selectionResults")) { // Selection results. // Construct expected result set. h2statement.execute(sqlQueries.get(0)); ResultSet sqlResultSet = h2statement.getResultSet(); ResultSetMetaData sqlMetaData = sqlResultSet.getMetaData(); Set<String> expectedValues = new HashSet<>(); Map<String, String> reusableExpectedValueMap = new HashMap<>(); Map<String, List<String>> reusableMultiValuesMap = new HashMap<>(); List<String> reusableColumnOrder = new ArrayList<>(); int numResults; for (numResults = 0; sqlResultSet.next() && numResults < MAX_COMPARISON_LIMIT; numResults++) { reusableExpectedValueMap.clear(); reusableMultiValuesMap.clear(); reusableColumnOrder.clear(); int numColumns = sqlMetaData.getColumnCount(); for (int i = 1; i <= numColumns; i++) { String columnName = sqlMetaData.getColumnName(i); // Handle null result and convert boolean value to lower case. String columnValue = sqlResultSet.getString(i); if (columnValue == null) { columnValue = "null"; } else { columnValue = convertBooleanToLowerCase(columnValue); } // Handle multi-value columns. int length = columnName.length(); if (length > 5 && columnName.substring(length - 5, length - 1).equals("__MV")) { // Multi-value column. String multiValueColumnName = columnName.substring(0, length - 5); List<String> multiValue = reusableMultiValuesMap.get(multiValueColumnName); if (multiValue == null) { multiValue = new ArrayList<>(); reusableMultiValuesMap.put(multiValueColumnName, multiValue); reusableColumnOrder.add(multiValueColumnName); } multiValue.add(columnValue); } else { // Single-value column. reusableExpectedValueMap.put(columnName, columnValue); reusableColumnOrder.add(columnName); } } // Add multi-value column results to the expected values. // The reason for this step is that Pinot does not maintain order of elements in multi-value columns. for (Map.Entry<String, List<String>> entry : reusableMultiValuesMap.entrySet()) { List<String> multiValue = entry.getValue(); Collections.sort(multiValue); reusableExpectedValueMap.put(entry.getKey(), multiValue.toString()); } // Build expected value String. StringBuilder expectedValue = new StringBuilder(); for (String column : reusableColumnOrder) { expectedValue.append(column).append(':').append(reusableExpectedValueMap.get(column)) .append(' '); } expectedValues.add(expectedValue.toString()); } JSONObject selectionColumnsAndResults = response.getJSONObject("selectionResults"); JSONArray selectionColumns = selectionColumnsAndResults.getJSONArray("columns"); JSONArray selectionResults = selectionColumnsAndResults.getJSONArray("results"); int numSelectionResults = selectionResults.length(); if (numResults == 0) { // No record selected in H2. // Check if no record selected in Pinot. if (numSelectionResults != 0) { String failureMessage = "No record selected in H2 but number of records selected in Pinot: " + numSelectionResults; failure(pqlQuery, sqlQueries, failureMessage); return; } // Check if number of documents scanned is 0. if (numDocsScanned != 0) { String failureMessage = "No selection result returned in Pinot but number of records selected: " + numDocsScanned; failure(pqlQuery, sqlQueries, failureMessage); } } else if (numResults < MAX_COMPARISON_LIMIT) { // Only compare exhausted results. // Check that Pinot results are contained in the H2 results. int numColumns = selectionColumns.length(); for (int i = 0; i < numSelectionResults; i++) { // Build actual value String. StringBuilder actualValueBuilder = new StringBuilder(); JSONArray selectionResult = selectionResults.getJSONArray(i); for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { // Convert column name to all uppercase to make it compatible with H2. String columnName = selectionColumns.getString(columnIndex).toUpperCase(); Object columnResult = selectionResult.get(columnIndex); if (columnResult instanceof JSONArray) { // Multi-value column. JSONArray columnResultsArray = (JSONArray) columnResult; List<String> multiValue = new ArrayList<>(); int length = columnResultsArray.length(); for (int elementIndex = 0; elementIndex < length; elementIndex++) { multiValue.add(columnResultsArray.getString(elementIndex)); } for (int elementIndex = length; elementIndex < MAX_ELEMENTS_IN_MULTI_VALUE; elementIndex++) { multiValue.add("null"); } Collections.sort(multiValue); actualValueBuilder.append(columnName).append(':').append(multiValue.toString()) .append(' '); } else { // Single-value column. actualValueBuilder.append(columnName).append(':').append((String) columnResult) .append(' '); } } String actualValue = actualValueBuilder.toString(); // Check actual value in expected values set. if (!expectedValues.contains(actualValue)) { String failureMessage = "Selection result returned in Pinot but not in H2: " + actualValue; failure(pqlQuery, sqlQueries, failureMessage); return; } } } else { // Cannot get exhausted results. LOGGER.debug("SQL: {} returned at least {} rows, skipping comparison.", sqlQueries.get(0), MAX_COMPARISON_LIMIT); } } else { // Neither aggregation or selection results. String failureMessage = "No aggregation or selection results found for query: " + pqlQuery; failure(pqlQuery, sqlQueries, failureMessage); } } catch (Exception e) { String failureMessage = "Caught exception while running query."; failure(pqlQuery, sqlQueries, failureMessage, e); } }
From source file:mom.trd.opentheso.bdd.helper.CandidateHelper.java
/** * Cette fonction permet de retourner l'Id du candidat d'aprs son nom * * @param ds//from ww w . j av a 2 s.c om * @param title * @param idThesaurus * @return idTermCandidat */ public String getIdCandidatFromTitle(HikariDataSource ds, String title, String idThesaurus) { Connection conn; Statement stmt; ResultSet resultSet; String idTermCandidat = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT concept_candidat.id_concept" + " FROM concept_candidat, concept_term_candidat, term_candidat" + " WHERE" + " concept_candidat.id_concept = concept_term_candidat.id_concept" + " AND" + " concept_term_candidat.id_thesaurus = term_candidat.id_thesaurus" + " AND" + " term_candidat.id_term = concept_term_candidat.id_term" + " AND" + " term_candidat.id_thesaurus = '" + idThesaurus + "'" + " and" + " term_candidat.lexical_value = '" + title + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet.next()) { idTermCandidat = resultSet.getString("id_concept"); } else return null; } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting idCandidat from candidat value : " + title, sqle); } return idTermCandidat; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de savoir si le terme existe ou non * * @param ds//from w w w .j ava 2 s. co m * @param idConcept * @param idThesaurus * @param idLang * @return Objet class NodeConceptTree */ public boolean isTraductionExistOfConcept(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; boolean existe = false; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select term.id_term from term, preferred_term" + " where term.id_term = preferred_term.id_term and" + " preferred_term.id_concept = '" + idConcept + "'" + " and term.lang = '" + idLang + "'" + " and term.id_thesaurus = '" + idThesaurus + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { resultSet.next(); existe = resultSet.getRow() != 0; } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while asking if Traduction of Concept exist : " + idConcept, sqle); } return existe; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de savoir si le terme existe ou non dans le thsaurus * mais il faut ignorer le terme lui mme; ceci nous permet de faire * la modification dans le cas suivant : helene -> en Hlne * * @param ds/*from w w w.j ava2 s . c om*/ * @param title * @param idThesaurus * @param idTerm * @param idLang * @return boolean */ public boolean isTermExistForEdit(HikariDataSource ds, String title, String idThesaurus, String idTerm, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; boolean existe = false; title = new StringPlus().convertString(title); try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select id_term from term where " + "unaccent_string(lexical_value) ilike " + "unaccent_string('" + title + "') and lang = '" + idLang + "' and id_thesaurus = '" + idThesaurus + "'" + " and id_term != '" + idTerm + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet.next()) { existe = true; } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while asking if Title of Term exist : " + title, sqle); } return existe; }
From source file:mom.trd.opentheso.bdd.helper.CandidateHelper.java
/** * Permet de retourner une ArrayList de nodeTraductionCandidat par * thsaurus/*from ww w . j a v a 2 s. co m*/ * * @param ds le pool de connexion * @param idConcept * @param idThesaurus * @param idLang * @return Objet Class ArrayList nodeTraductionCandidat */ public ArrayList<NodeTraductionCandidat> getNodeTraductionCandidat(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<NodeTraductionCandidat> nodeTraductionCandidatList = null; String idTermCandidat = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { idTermCandidat = getIdTermOfConceptCandidat(ds, idConcept, idThesaurus); String query = "SELECT term_candidat.lexical_value, term_candidat.lang," + " users.username, users.id_user" + " FROM users, term_candidat WHERE" + " term_candidat.contributor = users.id_user" + " and term_candidat.lang != '" + idLang + "'" + " and term_candidat.id_thesaurus = '" + idThesaurus + "'" + " and term_candidat.id_term = '" + idTermCandidat + "'" + " order by users.username ASC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeTraductionCandidatList = new ArrayList<>(); while (resultSet.next()) { NodeTraductionCandidat nodeTraductionCandidat = new NodeTraductionCandidat(); nodeTraductionCandidat.setIdLang(resultSet.getString("lang")); nodeTraductionCandidat.setTitle(resultSet.getString("lexical_value")); nodeTraductionCandidat.setUseId(resultSet.getInt("id_user")); nodeTraductionCandidat.setUser(resultSet.getString("username")); nodeTraductionCandidatList.add(nodeTraductionCandidat); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Traductions of Candidat : " + idConcept, sqle); } return nodeTraductionCandidatList; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de rcuprer l'historique d'un terme * * @param ds/*from w w w .java 2 s.c o m*/ * @param idTerm * @param idThesaurus * @param idLang * @return Objet class Concept */ public ArrayList<Term> getTermsHistoriqueAll(HikariDataSource ds, String idTerm, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<Term> nodeTermList = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT lexical_value, modified, source, status, username FROM term_historique, users" + " WHERE id_term = '" + idTerm + "'" + " and id_thesaurus = '" + idThesaurus + "'" + " and lang ='" + idLang + "'" + " and term_historique.id_user=users.id_user" + " order by modified DESC, lexical_value ASC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeTermList = new ArrayList<>(); while (resultSet.next()) { Term t = new Term(); t.setId_term(idTerm); t.setId_thesaurus(idThesaurus); t.setLexical_value(resultSet.getString("lexical_value")); t.setModified(resultSet.getDate("modified")); t.setSource(resultSet.getString("source")); t.setStatus(resultSet.getString("status")); t.setIdUser(resultSet.getString("username")); t.setLang(idLang); nodeTermList.add(t); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting all historique of Term : " + idTerm, sqle); } return nodeTermList; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de rcuprer les termes synonymes suivant un * id_term et son thsaurus et sa langue sous forme de classe NodeEM * * @param ds//from w w w . java2s .co m * @param idTerm * @param idThesaurus * @param idLang * @return Objet class Concept */ public ArrayList<NodeEM> getNonPreferredTerms(HikariDataSource ds, String idTerm, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<NodeEM> nodeEMList = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT lexical_value, created, modified," + " source, status, hiden" + " FROM non_preferred_term" + " WHERE non_preferred_term.id_term = '" + idTerm + "'" + " and non_preferred_term.id_thesaurus = '" + idThesaurus + "'" + " and non_preferred_term.lang ='" + idLang + "'" + " order by lexical_value ASC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeEMList = new ArrayList<>(); while (resultSet.next()) { NodeEM nodeEM = new NodeEM(); nodeEM.setLexical_value(resultSet.getString("lexical_value")); nodeEM.setCreated(resultSet.getDate("created")); nodeEM.setModified(resultSet.getDate("modified")); nodeEM.setSource(resultSet.getString("source")); nodeEM.setStatus(resultSet.getString("status")); nodeEM.setHiden(resultSet.getBoolean("hiden")); nodeEMList.add(nodeEM); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting NonPreferedTerm of Term : " + idTerm, sqle); } return nodeEMList; }
From source file:com.nextep.designer.sqlclient.ui.editors.SQLFullClientGUI.java
/** * @see com.nextep.datadesigner.gui.model.IConnector#refreshConnector() *///from www . j av a2 s .co m @Override public void refreshConnector() { // Clearing current table columns // clearSQLView(); final ISQLScript script = (ISQLScript) getModel(); if (script.getSql() == null || "".equals(script.getSql())) { return; } try { // sqlText.add("select * from " + o.getName()); // sqlText.select(sqlText.getItemCount()-1); // Creating result table final CTabItem sqlItem = new CTabItem(sqlFolder, SWT.NONE); Composite resultPane = new Composite(sqlFolder, SWT.NONE); final Table sqlView = new Table(resultPane, SWT.FULL_SELECTION | SWT.BORDER); final NextepTableEditor editor = NextepTableEditor.handle(sqlView); CoolBar statsBar = new CoolBar(resultPane, SWT.NONE); statsBar.setLayoutData(new GridData(GridData.FILL_HORIZONTAL)); final CoolItem rowsItem = new CoolItem(statsBar, SWT.NONE); rowsItem.setSize(rowsItem.computeSize(100, 20)); final Label rowsCount = new Label(statsBar, SWT.NONE); rowsItem.setControl(rowsCount); final CoolItem timeItem = new CoolItem(statsBar, SWT.NONE); final Label timeLabel = new Label(statsBar, SWT.NONE); timeItem.setControl(timeLabel); timeItem.setSize(timeItem.computeSize(200, 20)); sqlView.setHeaderVisible(true); sqlView.setLinesVisible(true); sqlView.setLayoutData(new GridData(GridData.FILL, GridData.FILL, true, true, 1, 1)); resultPane.setLayoutData(new GridData(GridData.FILL, GridData.FILL, true, true, 1, 1)); GridLayout grid = new GridLayout(); grid.marginBottom = grid.marginHeight = grid.marginLeft = grid.marginRight = grid.marginTop = grid.marginWidth = 0; resultPane.setLayout(grid); sqlItem.setControl(resultPane); final Listener sortListener = new SortListener(sqlView); final String query = formatQuery(script.getSql()); final int queryLen = query.length(); sqlItem.setText(queryLen < 30 ? query : query.substring(0, 30) + "..."); sqlItem.setToolTipText(query); sqlFolder.setSelection(sqlItem); final List<ICommand> bufferedCommands = new ArrayList<ICommand>(); // Initializing lines Job refreshJob = new Job("Fetching SQL data...") { @Override protected IStatus run(IProgressMonitor monitor) { Statement s = null; ResultSet r = null; try { s = connection.createStatement(); final Date startDate = new Date(); final boolean isResultSet = s.execute(query); final Date afterExecDate = new Date(); if (!isResultSet) { final int updates = s.getUpdateCount(); bufferedCommands.add(new ICommand() { @Override public String getName() { return null; } @Override public Object execute(Object... parameters) { if (sqlView != null && !sqlView.isDisposed()) { TableColumn c = new TableColumn(sqlView, SWT.NONE); c.setText(SQLClientMessages.getString("sql.result")); c.setWidth(300); c.addListener(SWT.Selection, sortListener); if (updates > 0) { final TableItem i = new TableItem(sqlView, SWT.NONE); i.setText(MessageFormat.format( SQLClientMessages.getString("sql.updatedRows"), updates)); } else { final TableItem i = new TableItem(sqlView, SWT.NONE); i.setText(SQLClientMessages.getString("sql.queryOk")); } } return null; } }); syncProcessCommands(bufferedCommands); return Status.OK_STATUS; } r = s.getResultSet(); // Initializing columns final ResultSetMetaData md = r.getMetaData(); // Initializing sizing table final int[] colMaxWidth = new int[md.getColumnCount() + 1]; for (int i = 1; i <= md.getColumnCount(); i++) { final int index = i; final String colName = md.getColumnName(index); // final int colPrecision = md.getPrecision(index); final int colType = md.getColumnType(index); final int colIndex = i - 1; bufferedCommands.add(new ICommand() { @Override public String getName() { return null; } @Override public Object execute(Object... parameters) { if (!sqlView.isDisposed()) { TableColumn c = new TableColumn(sqlView, SWT.NONE); c.addListener(SWT.Selection, sortListener); c.setText(colName); c.setWidth(colName.length() * 8); colMaxWidth[colIndex] = c.getWidth(); c.setData(COL_TYPE, colType); TextColumnEditor.handle(editor, colIndex, ChangeEvent.CUSTOM_1, new IEventListener() { @Override public void handleEvent(ChangeEvent event, IObservable source, Object data) { } }); } return null; } }); } final ResultSet rset = r; int rows = 0; final long execTime = afterExecDate.getTime() - startDate.getTime(); bufferedCommands.add(new ICommand() { @Override public String getName() { return null; } @Override public Object execute(Object... parameters) { timeLabel.setText(MessageFormat .format(SQLClientMessages.getString("sql.executionTime"), execTime)); return null; } }); syncProcessCommands(bufferedCommands); while (r.next()) { rows++; // Handling cancellation while fetching SQL lines if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } final String[] colValues = new String[md.getColumnCount()]; final Collection<Integer> nullCols = new ArrayList<Integer>(); for (int i = 1; i <= md.getColumnCount(); i++) { Object val = null; try { val = rset.getObject(i); } catch (SQLException e) { LOGGER.error("Error while fetching column value : " + e.getMessage(), e); val = e.getMessage(); } final String strVal = strVal(val); colValues[i - 1] = strVal; // Building list of null columns if (val == null) { nullCols.add(i - 1); } // Updating max sizes final int colWidth = colMaxWidth[i - 1]; if (strVal.length() * 8 > colWidth) { colMaxWidth[i - 1] = strVal.length() * 8; } } // Adding the row as a command bufferedCommands.add(buildAddRowCommand(colValues, sqlView, nullCols)); // Flushing to display every N lines if (bufferedCommands.size() > MAX_ROWS_BEFORE_REFRESH) { bufferedCommands.add(buildAdjustWidthCommand(sqlView, colMaxWidth)); syncProcessCommands(bufferedCommands); } } // Flushing any left row bufferedCommands.add(buildAdjustWidthCommand(sqlView, colMaxWidth)); final Date afterFetchDate = new Date(); final int nbRows = rows; bufferedCommands.add(new ICommand() { @Override public String getName() { // TODO Auto-generated method stub return null; } @Override public Object execute(Object... parameters) { long fetchTime = afterFetchDate.getTime() - afterExecDate.getTime(); timeLabel.setText( MessageFormat.format(SQLClientMessages.getString("sql.executionFetchTime"), execTime, fetchTime)); rowsCount.setText(MessageFormat .format(SQLClientMessages.getString("sql.fetchedRows"), nbRows)); return null; } }); syncProcessCommands(bufferedCommands); } catch (final SQLException e) { PlatformUI.getWorkbench().getDisplay().syncExec(new Runnable() { @Override public void run() { if (!sqlView.isDisposed()) { sqlView.removeAll(); for (TableColumn c : sqlView.getColumns()) { c.dispose(); } TableColumn c = new TableColumn(sqlView, SWT.NONE); c.setText("SQL Exception " + e.getErrorCode()); c.setWidth(300); TableItem i = new TableItem(sqlView, SWT.NONE); i.setText(e.getMessage()); } } }); // throw new ErrorException(e); } finally { try { if (r != null) {// && !r.isClosed()) { r.close(); } if (s != null) { // && !s.isClosed()) { s.close(); } } catch (SQLException e) { throw new ErrorException(e); } finally { PlatformUI.getWorkbench().getDisplay().asyncExec(new Runnable() { @Override public void run() { // If the user has closed his SQL Query editor, we will // fall here (exception) with a disposed button if (runSQLButton != null && !runSQLButton.isDisposed()) { runSQLButton.setEnabled(true); } } }); } } return Status.OK_STATUS; } }; runSQLButton.setEnabled(false); refreshJob.schedule(); // } catch(SQLException e) { // throw new ErrorException(e); } finally { // try { // if(stmt != null && !stmt.isClosed()) { // stmt.close(); // } // if(rset != null && !rset.isClosed()) { // rset.close(); // } // } catch(SQLException e) { // throw new ErrorException(e); // } } }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de retourner les traductions d'un term sauf la * langue en cours/* w ww . ja va 2s . c o m*/ * * @param ds * @param idConcept * @param idThesaurus * @param idLang * @return Objet class NodeConceptTree */ public ArrayList<NodeTermTraduction> getTraductionsOfConcept(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<NodeTermTraduction> nodeTraductionsList = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT term.id_term, term.lexical_value, term.lang FROM" + " term, preferred_term WHERE" + " term.id_term = preferred_term.id_term" + " and preferred_term.id_concept = '" + idConcept + "'" + " and term.lang != '" + idLang + "'" + " and term.id_thesaurus = '" + idThesaurus + "'" + " order by term.lexical_value"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeTraductionsList = new ArrayList<>(); while (resultSet.next()) { NodeTermTraduction nodeTraductions = new NodeTermTraduction(); nodeTraductions.setLang(resultSet.getString("lang")); nodeTraductions.setLexicalValue(resultSet.getString("lexical_value")); nodeTraductionsList.add(nodeTraductions); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Traductions of Term : " + idConcept, sqle); } return nodeTraductionsList; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de rcuprer l'historique d'un terme une date prcise * * @param ds/* w w w. j a v a2 s . c om*/ * @param idTerm * @param idThesaurus * @param idLang * @param date * @return Objet class Concept */ public ArrayList<Term> getTermsHistoriqueFromDate(HikariDataSource ds, String idTerm, String idThesaurus, String idLang, Date date) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<Term> nodeTermList = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT lexical_value, modified, source, status, username FROM term_historique, users" + " WHERE id_term = '" + idTerm + "'" + " and id_thesaurus = '" + idThesaurus + "'" + " and lang ='" + idLang + "'" + " and term_historique.id_user=users.id_user" + " and modified <= '" + date.toString() + "' order by modified DESC, lexical_value ASC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeTermList = new ArrayList<>(); resultSet.next(); Term t = new Term(); t.setId_term(idTerm); t.setId_thesaurus(idThesaurus); t.setLexical_value(resultSet.getString("lexical_value")); t.setModified(resultSet.getDate("modified")); t.setSource(resultSet.getString("source")); t.setStatus(resultSet.getString("status")); t.setIdUser(resultSet.getString("username")); t.setLang(idLang); nodeTermList.add(t); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting date historique of Term : " + idTerm, sqle); } return nodeTermList; }