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:org.exist.xquery.modules.sql.ExecuteFunction.java
/** * evaluate the call to the XQuery execute() function, it is really the main entry point of this class. * * @param args arguments from the execute() function call * @param contextSequence the Context Sequence to operate on (not used here internally!) * * @return A node representing the SQL result set * * @throws XPathException DOCUMENT ME! * * @see org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], org.exist.xquery.value.Sequence) *//* w w w . j a v a2 s .co m*/ @Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { // was a connection and SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); } // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection con = SQLModule.retrieveConnection(context, connectionUID); if (con == null) { return (Sequence.EMPTY_SEQUENCE); } boolean preparedStmt = false; //setup the SQL statement String sql = null; Statement stmt = null; boolean executeResult = false; ResultSet rs = null; try { boolean makeNodeFromColumnName = false; MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; //SQL or PreparedStatement? if (args.length == 3) { // get the SQL statement sql = args[1].getStringValue(); stmt = con.createStatement(); makeNodeFromColumnName = ((BooleanValue) args[2].itemAt(0)).effectiveBooleanValue(); //execute the statement executeResult = stmt.execute(sql); } else if (args.length == 4) { preparedStmt = true; //get the prepared statement long statementUID = ((IntegerValue) args[1].itemAt(0)).getLong(); PreparedStatementWithSQL stmtWithSQL = SQLModule.retrievePreparedStatement(context, statementUID); sql = stmtWithSQL.getSql(); stmt = stmtWithSQL.getStmt(); makeNodeFromColumnName = ((BooleanValue) args[3].itemAt(0)).effectiveBooleanValue(); if (!args[2].isEmpty()) { setParametersOnPreparedStatement(stmt, (Element) args[2].itemAt(0)); } //execute the prepared statement executeResult = ((PreparedStatement) stmt).execute(); } else { //TODO throw exception } // DW: stmt can be null ? // execute the query statement if (executeResult) { /* SQL Query returned results */ // iterate through the result set building an XML document rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int iColumns = rsmd.getColumnCount(); builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); while (rs.next()) { builder.startElement(new QName("row", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(rs.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colElement = "field"; if (makeNodeFromColumnName && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement(new QName(colElement, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); if (!makeNodeFromColumnName || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName(TYPE_ATTRIBUTE_NAME, Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); //get the content if (rsmd.getColumnType(i + 1) == Types.SQLXML) { //parse sqlxml value try { final SQLXML sqlXml = rs.getSQLXML(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { SAXParserFactory factory = SAXParserFactory.newInstance(); factory.setNamespaceAware(true); InputSource src = new InputSource(sqlXml.getCharacterStream()); SAXParser parser = factory.newSAXParser(); XMLReader xr = parser.getXMLReader(); SAXAdapter adapter = new AppendingSAXAdapter(builder); xr.setContentHandler(adapter); xr.setProperty(Namespaces.SAX_LEXICAL_HANDLER, adapter); xr.parse(src); } } catch (Exception e) { throw new XPathException( "Could not parse column of type SQLXML: " + e.getMessage(), e); } } else { //otherwise assume string value final String colValue = rs.getString(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } } } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); } else { /* SQL Query performed updates */ builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("updateCount", null, null), String.valueOf(stmt.getUpdateCount())); builder.endElement(); } // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } catch (SQLException sqle) { LOG.error("sql:execute() Caught SQLException \"" + sqle.getMessage() + "\" for SQL: \"" + sql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(sqle.getSQLState()); builder.endElement(); builder.startElement(new QName("message", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); String state = sqle.getMessage(); if (state != null) { builder.characters(state); } builder.endElement(); builder.startElement(new QName("stack-trace", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("sql", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(sql)); builder.endElement(); if (stmt instanceof PreparedStatement) { Element parametersElement = (Element) args[2].itemAt(0); if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI) && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) { NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI, PARAM_ELEMENT_NAME); builder.startElement( new QName(PARAMETERS_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); for (int i = 0; i < paramElements.getLength(); i++) { Element param = ((Element) paramElements.item(i)); String value = param.getFirstChild().getNodeValue(); String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME); builder.startElement( new QName(PARAM_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), type); builder.characters(SQLUtils.escapeXmlText(value)); builder.endElement(); } builder.endElement(); } } builder.startElement(new QName("xquery", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(getLine())); builder.addAttribute(new QName("column", null, null), String.valueOf(getColumn())); builder.endElement(); builder.endElement(); builder.endDocument(); return ((NodeValue) builder.getDocument().getDocumentElement()); } finally { // close any record set or statement if (rs != null) { try { rs.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } rs = null; } if (!preparedStmt && stmt != null) { try { stmt.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } stmt = null; } } }
From source file:org.freebxml.omar.server.persistence.rdb.SlotDAO.java
public void deleteByParentIdAndSlots(String parentId, List slots) throws RegistryException { Statement stmt = null; try {/*w w w . jav a 2 s . c o m*/ stmt = context.getConnection().createStatement(); String str = "DELETE from " + getTableName() + " WHERE parent = '" + parentId + "' AND ("; Iterator iter = slots.iterator(); while (iter.hasNext()) { Slot slot = (Slot) 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("SQL = " + 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:org.hyperic.hq.plugin.sybase.SybaseMeasurementPlugin.java
private ResultSet getResultSet(Statement stmt, String col) throws SQLException { do {//ww w. j a v a2 s . c o m ResultSet rs = null; try { rs = stmt.getResultSet(); if (rs == null) break; rs.findColumn(col); return rs; } catch (SQLException e) { //don't close the resultset!!! } } while (stmt.getMoreResults() == true && stmt.getUpdateCount() != -1); throw new SQLException(); }
From source file:org.hyperic.hq.product.JDBCMeasurementPlugin.java
private String getSqlRow(Statement stmt) throws SQLException { StringBuffer buf = new StringBuffer(); do {//from w ww . j av a 2 s . c o m ResultSet rs = stmt.getResultSet(); if (stmt.getUpdateCount() != -1) { continue; } if (rs == null) { break; } setData(rs); buf.append(getOutput(rs.getMetaData())); } while (stmt.getMoreResults() == true); return buf.toString(); }
From source file:org.jbrain.qlink.db.DBUtils.java
public static int getNextID(int start, int type, int max) { Connection conn = null;/* w w w . j a v a2 s . co m*/ Statement stmt = null; ResultSet rs = null; String sql; try { conn = DBUtils.getConnection(); stmt = conn.createStatement(); _log.debug("Attempting to find next available message base ID after " + start); int orig_id = start; do { start++; if (start > max) start = 0; sql = "SELECT reference_id from entry_types where reference_id=" + start; _log.debug(sql); rs = stmt.executeQuery(sql); } while (rs.next() && start != orig_id); try { rs.close(); } catch (Exception e) { } if (start == orig_id) { // error _log.error("Cannot find ID <=" + max); return -1; } else { _log.debug("Creating new entry_types record"); sql = "insert into entry_types (reference_id,entry_type,create_date,last_update) VALUES (" + start + "," + type + ",now(),now())"; _log.debug(sql); stmt.execute(sql); if (stmt.getUpdateCount() == 0) { _log.error("Could not insert record into entry_types"); return -1; } } return start; } catch (SQLException e) { _log.error("SQL Exception", e); return -1; } finally { close(rs); close(stmt); close(conn); } }
From source file:org.jiemamy.utils.sql.SqlExecutor.java
void executeSingleSql(String sql, SqlExecutorHandler handler) throws SQLException { logger.info(LogMarker.DETAIL, sql);// w w w . jav a 2 s.c o m boolean isAutoCommit = connection.getAutoCommit(); connection.setAutoCommit(false); Statement stmt = null; ResultSet rs = null; try { stmt = connection.createStatement(); if (stmt.execute(sql)) { if (handler != null) { rs = stmt.getResultSet(); handler.handleResultSet(sql, rs); } } else { if (handler != null) { int count = stmt.getUpdateCount(); if (count >= 0) { handler.handleUpdateCount(sql, count); } } } connection.commit(); } catch (SQLException e) { logger.warn(sql, e); connection.rollback(); throw e; } finally { connection.setAutoCommit(isAutoCommit); DbUtils.closeQuietly(rs); DbUtils.closeQuietly(stmt); } }
From source file:org.jumpmind.db.sql.JdbcSqlTemplate.java
public int update(final String sql, final Object[] args, final int[] types) { logSql(sql, args);//from ww w .j a v a 2 s .c om return execute(new IConnectionCallback<Integer>() { public Integer execute(Connection con) throws SQLException { if (args == null) { Statement stmt = null; try { stmt = con.createStatement(); stmt.setQueryTimeout(settings.getQueryTimeout()); stmt.execute(sql); return stmt.getUpdateCount(); } finally { close(stmt); } } else { PreparedStatement ps = null; try { ps = con.prepareStatement(sql); ps.setQueryTimeout(settings.getQueryTimeout()); if (types != null) { setValues(ps, args, types, getLobHandler().getDefaultHandler()); } else { setValues(ps, args); } ps.execute(); return ps.getUpdateCount(); } finally { close(ps); } } } }); }
From source file:org.jumpmind.db.sql.JdbcSqlTemplate.java
public int update(final boolean autoCommit, final boolean failOnError, final boolean failOnDrops, final boolean failOnSequenceCreate, final int commitRate, final ISqlResultsListener resultsListener, final ISqlStatementSource source) { return execute(new IConnectionCallback<Integer>() { @SuppressWarnings("resource") public Integer execute(Connection con) throws SQLException { int totalUpdateCount = 0; boolean oldAutoCommitSetting = con.getAutoCommit(); Statement stmt = null; try { con.setAutoCommit(autoCommit); stmt = con.createStatement(); int statementCount = 0; for (String statement = source.readSqlStatement(); statement != null; statement = source .readSqlStatement()) { logSql(statement, null); try { boolean hasResults = stmt.execute(statement); int updateCount = stmt.getUpdateCount(); totalUpdateCount += updateCount; int rowsRetrieved = 0; if (hasResults) { ResultSet rs = null; try { rs = stmt.getResultSet(); while (rs.next()) { rowsRetrieved++; }//from w w w . j a v a 2 s . c o m } finally { close(rs); } } if (resultsListener != null) { resultsListener.sqlApplied(statement, updateCount, rowsRetrieved, statementCount); } statementCount++; if (statementCount % commitRate == 0 && !autoCommit) { con.commit(); } } catch (SQLException ex) { boolean isDrop = statement.toLowerCase().trim().startsWith("drop"); boolean isSequenceCreate = statement.toLowerCase().trim().startsWith("create sequence"); if (resultsListener != null) { resultsListener.sqlErrored(statement, translate(statement, ex), statementCount, isDrop, isSequenceCreate); } if ((isDrop && !failOnDrops) || (isSequenceCreate && !failOnSequenceCreate)) { log.debug("{}. Failed to execute: {}", ex.getMessage(), statement); } else { log.warn("{}. Failed to execute: {}", ex.getMessage(), statement); if (failOnError) { throw ex; } } } } if (!autoCommit) { con.commit(); } return totalUpdateCount; } catch (SQLException ex) { if (!autoCommit) { con.rollback(); } throw ex; } finally { close(stmt); if (!con.isClosed()) { con.setAutoCommit(oldAutoCommitSetting); } } } }); }
From source file:org.jumpmind.db.sql.JdbcSqlTransaction.java
public int execute(final String sql) { return executeCallback(new IConnectionCallback<Integer>() { public Integer execute(Connection con) throws SQLException { Statement stmt = null; ResultSet rs = null;/*from w w w. j a v a 2 s . co m*/ try { logSql(sql, null); stmt = con.createStatement(); if (stmt.execute(sql)) { rs = stmt.getResultSet(); while (rs.next()) { } } return stmt.getUpdateCount(); } finally { JdbcSqlTemplate.close(rs); JdbcSqlTemplate.close(stmt); } } }); }
From source file:org.kawanfw.sql.servlet.sql.ServerStatementRawExecute.java
/** * Execute the passed SQL Statement as execute(sql) and return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * /*from w w w . j a va2 s. c o m*/ * @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 executeStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); // sqlOrder = HtmlConverter.fromHtml(sqlOrder); // Should never be called? if (statementHolder.isDoExtractResultSetMetaData()) { sqlOrder = DbVendorManager.addLimit1(sqlOrder, connection); } Statement statement = null; try { if (!SqlConfiguratorCall.allowExecute(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized for execute}" + "{sql order : " + sqlOrder + "}" + "]"; throw new SecurityException(message); } statement = connection.createStatement(); ServerSqlUtil.setStatementProperties(statement, statementHolder); debug("before ServerPreparedStatementParameters"); boolean isAllowed = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, new Vector<Object>()); if (!isAllowed) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } debug("before statement.execute(sqlOrder)"); debug("sqlOrder: " + sqlOrder); ServerSqlUtil.setMaxRowsToReturn(statement, sqlConfigurator); boolean isResultSet = false; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { isResultSet = statement.execute(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { isResultSet = statement.execute(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { isResultSet = statement.execute(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { debug("before isResultSet = statement.execute(sqlOrder);"); isResultSet = statement.execute(sqlOrder); } debug("isResultSet :" + isResultSet); debug("usesAutoGeneratedKeys: " + usesAutoGeneratedKeys); if (isResultSet) { ResultSet rs = statement.getResultSet(); try { //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } else { debug("int rc = statement.getUpdateCount();"); int rc = statement.getUpdateCount(); //br.write(TransferStatus.SEND_OK + CR_LF); //br.write("getUpdateCount=" + rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "getUpdateCount=" + rc); if (usesAutoGeneratedKeys) { ResultSet rs = statement.getGeneratedKeys(); try { 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_PRODUCT_FAIL + CR_LF + "Statement: " + sqlOrder + CR_LF + "- sql order: " + sqlOrder + CR_LF + "- exception: " + e.toString()); throw e; } finally { IOUtils.closeQuietly(out); if (statement != null) { statement.close(); } } }