List of usage examples for java.sql Statement getUpdateCount
int getUpdateCount() throws SQLException;
ResultSet
object or there are no more results, -1 is returned. From source file:com.ibm.soatf.component.database.StatementExecutor.java
/** * Runs an SQL script from the file specified by the <code>inputScriptFile</code> parameter * /*from www . j a v a 2 s . co m*/ * @param conn SQL connection on which you want to run this script * @param file script file * @throws StatementExecutorException if SQL or IO exception occurs */ public void runScript(Connection conn, File file) throws DatabaseComponentException { OperationResult cor = OperationResult.getInstance(); String inputScriptFilePath = ""; String inputScriptRelativePath = ""; Statement stmt = null; try { ProgressMonitor.increment("Loading SQL script..."); inputScriptFilePath = file.getAbsolutePath(); inputScriptRelativePath = FileSystem.getRelativePath(file); String sql = FileUtils.readFileToString(file); if (sql.endsWith(";")) sql = sql.substring(0, sql.length() - 1); String msg = "Successfuly loaded script [FILE: %s]"; logger.debug(String.format(msg, inputScriptFilePath)); cor.addMsg(msg, "<a href='file://" + inputScriptFilePath + "'>" + inputScriptFilePath + "</a>", inputScriptRelativePath); conn.setAutoCommit(false); stmt = conn.createStatement(); ProgressMonitor.increment("Executing SQL script..."); boolean hasResults = stmt.execute(sql); conn.commit(); int updateCount = -1; if (!hasResults) { updateCount = stmt.getUpdateCount(); } msg = "Script run successful, update count: " + updateCount; logger.debug(msg); cor.addMsg(msg); final String logMsg = "Record has been inserted into source database '" + conn.getMetaData().getURL() + "'.\n" + "Insert statement executed:\n%s"; cor.addMsg(logMsg, sql, "[FILE: " + FileSystem.getRelativePath(file) + "]"); cor.markSuccessful(); } catch (IOException ex) { final String msg = "Failed to open statement [FILE: %s]."; cor.addMsg(msg, "<a href='file://" + inputScriptFilePath + "'>" + inputScriptFilePath + "</a>", inputScriptRelativePath); throw new DatabaseComponentException(String.format(msg, inputScriptFilePath), ex); } catch (SQLException ex) { final String msg = String.format("Failed to execute INSERT statement: %s", Utils.getSQLExceptionMessage(ex)); cor.addMsg(msg); throw new DatabaseComponentException(msg, ex); } finally { DatabaseComponent.closeStatement(stmt); } }
From source file:it.cnr.icar.eric.server.persistence.rdb.SlotDAO.java
public void deleteByParentIdAndSlots(String parentId, List<?> slots) throws RegistryException { Statement stmt = null; try {//from w w w. j a v a 2s. c o m stmt = context.getConnection().createStatement(); String str = "DELETE from " + getTableName() + " WHERE parent = '" + parentId + "' AND ("; Iterator<?> iter = slots.iterator(); while (iter.hasNext()) { SlotType1 slot = (SlotType1) iter.next(); String slotName = slot.getName(); if (iter.hasNext()) { str = str + "name_ = '" + Utility.escapeSQLChars(slotName) + "' OR "; } else { str = str + "name_ = '" + Utility.escapeSQLChars(slotName) + "' )"; } } log.trace("stmt = " + str); stmt.execute(str); int updateCount = stmt.getUpdateCount(); if (updateCount < slots.size()) { throw new SlotNotExistException(parentId); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:io.bibleget.BibleGetDB.java
public boolean addColumn(String colName, String type) { int count;/*from w ww . ja v a 2 s. co m*/ try { colName = colName.toUpperCase(); Statement stmt = instance.conn.createStatement(); String sqlexec = "ALTER TABLE OPTIONS ADD COLUMN " + colName + " " + type; boolean colAdded = stmt.execute(sqlexec); if (colAdded == false) { count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else if (count == 0) { //0 rows affected stmt.close(); return true; } } else { //returns true only when returning a resultset; should not be the case here } } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } return false; }
From source file:io.bibleget.BibleGetDB.java
public boolean setIntOption(String colname, int value) { int count;/*from w w w . j ava2s . co m*/ colname = colname.toUpperCase(); if (instance.connect()) { if (!colNames.contains(colname)) { boolean result = addColumn(colname, "INT"); if (result == false) { return false; } //System.out.println("Added "+colname+" column of type INT to OPTIONS table"); colNames.add(colname); colDataTypes.add(Integer.class); } try { Statement stmt = instance.conn.createStatement(); String sqlexec = "UPDATE OPTIONS SET " + colname + " = " + value + ""; boolean rowsUpdated = stmt.execute(sqlexec); if (rowsUpdated == false) { count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //should have affected only one row if (count == 1) { stmt.close(); instance.disconnect(); return true; } } } else { //returns true only when returning a resultset; should not be the case here } } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } } return false; }
From source file:io.bibleget.BibleGetDB.java
public boolean setBooleanOption(String colname, boolean value) { int count;/*from w w w .j a va 2 s. c om*/ colname = colname.toUpperCase(); if (instance.connect()) { if (!colNames.contains(colname)) { boolean result = addColumn(colname, "BOOLEAN"); if (result == false) { return false; } //System.out.println("Added "+colname+" column of type BOOLEAN to OPTIONS table"); colNames.add(colname); colDataTypes.add(Boolean.class); } try { Statement stmt = instance.conn.createStatement(); String sqlexec = "UPDATE OPTIONS SET " + colname + " = " + value + ""; boolean rowsUpdated = stmt.execute(sqlexec); if (rowsUpdated == false) { count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //should have affected only one row if (count == 1) { stmt.close(); instance.disconnect(); return true; } } } else { //returns true only when returning a resultset; should not be the case here } } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } } return false; }
From source file:io.bibleget.BibleGetDB.java
public boolean setStringOption(String colname, String value) { int count;/* w w w.j av a 2s . co m*/ colname = colname.toUpperCase(); if (instance.connect()) { if (!colNames.contains(colname)) { boolean result = addColumn(colname, "VARCHAR(50)"); if (result == false) { return false; } //System.out.println("Added "+colname+" column of type VARCHAR(50) to OPTIONS table"); colNames.add(colname); colDataTypes.add(String.class); } try { Statement stmt = instance.conn.createStatement(); String sqlexec = "UPDATE OPTIONS SET " + colname + " = '" + value + "'"; boolean rowsUpdated = stmt.execute(sqlexec); if (rowsUpdated == false) { count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //should have affected only one row if (count == 1) { //System.out.println(sqlexec+" seems to have returned true"); stmt.close(); instance.disconnect(); return true; } } } else { //returns true only when returning a resultset; should not be the case here } } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } } return false; }
From source file:com.nextep.designer.sqlclient.ui.editors.SQLFullClientGUI.java
/** * @see com.nextep.datadesigner.gui.model.IConnector#refreshConnector() *///from ww w .j a v a 2 s.c om @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:com.centeractive.ws.builder.soap.XmlUtils.java
public static String createJdbcXmlResult(Statement statement) throws SQLException, ParserConfigurationException { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document xmlDocumentResult = builder.newDocument(); Element resultsElement = xmlDocumentResult.createElement("Results"); xmlDocumentResult.appendChild(resultsElement); if (statement != null) { ResultSet resultSet = statement.getResultSet(); if (resultSet != null) { resultSet.setFetchSize(statement.getFetchSize()); xmlDocumentResult = addResultSetXmlPart(resultsElement, resultSet, xmlDocumentResult); while (statement.getMoreResults()) { xmlDocumentResult = addResultSetXmlPart(resultsElement, statement.getResultSet(), xmlDocumentResult); }/*from w ww. j a v a2 s . c o m*/ } else { Element errorElement = xmlDocumentResult.createElement("UpdateCount"); errorElement .appendChild(xmlDocumentResult.createTextNode(String.valueOf(statement.getUpdateCount()))); resultsElement.appendChild(errorElement); } } StringWriter out = new StringWriter(); OutputFormat outputFormat = new OutputFormat(xmlDocumentResult); outputFormat.setOmitComments(true); outputFormat.setOmitDocumentType(true); outputFormat.setOmitXMLDeclaration(true); // outputFormat.setLineSeparator( "\n" ); // add this line // // outputFormat.setPreserveSpace( true ); outputFormat.setIndent(3); outputFormat.setIndenting(true); try { XMLSerializer serializer = new XMLSerializer(new PrintWriter(out), outputFormat); serializer.asDOMSerializer(); serializer.serialize(xmlDocumentResult); } catch (IOException e) { throw new SoapBuilderException(e); } return out.toString(); }
From source file:cc.tooyoung.common.db.JdbcTemplate.java
public int[] batchUpdate(final String[] sql) throws DataAccessException { Assert.notEmpty(sql, "SQL array must not be empty"); if (ApiLogger.isTraceEnabled()) { ApiLogger.trace(new StringBuilder(128).append("Executing SQL batch update of ").append(sql.length) .append(" statements")); }/*from w ww .jav a 2s . c om*/ class BatchUpdateStatementCallback implements StatementCallback, SqlProvider { private String currSql; public Object doInStatement(Statement stmt) throws SQLException, DataAccessException { int[] rowsAffected = new int[sql.length]; if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) { for (int i = 0; i < sql.length; i++) { this.currSql = sql[i]; stmt.addBatch(sql[i]); } rowsAffected = stmt.executeBatch(); } else { for (int i = 0; i < sql.length; i++) { this.currSql = sql[i]; if (!stmt.execute(sql[i])) { rowsAffected[i] = stmt.getUpdateCount(); } else { throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]); } } } return rowsAffected; } public String getSql() { return currSql; } } return (int[]) execute(new BatchUpdateStatementCallback(), true); }
From source file:io.cloudslang.content.database.services.SQLCommandService.java
public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)) { connection.setReadOnly(false);/* w ww. j a v a2s. c o m*/ final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { //during a dump sybase sends back status as exceptions. if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } //For sybase, when dbcc command is executed, the result is shown in warning message else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }