Example usage for java.sql PreparedStatement getUpdateCount

List of usage examples for java.sql PreparedStatement getUpdateCount

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getUpdateCount

Introduction

In this page you can find the example usage for java.sql PreparedStatement getUpdateCount.

Prototype

int getUpdateCount() throws SQLException;

Source Link

Document

Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned.

Usage

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

/**
 * Execute given query on database.//w w  w. ja  v  a2  s  . c o m
 *
 * @param query Query that should be executed
 */
private void runQuery(String query, Connection conn, Object... args) {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(query);

        for (int i = 0; i < args.length; ++i) {
            if (args[i] instanceof String) {
                stmt.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof Long) {
                stmt.setLong(i + 1, (Long) args[i]);
            } else {
                stmt.setObject(i + 1, args[i]);
            }
        }

        if (stmt.execute()) {
            ResultSet rset = stmt.getResultSet();
            int count = 0;
            while (rset.next()) {
                count++;
            }
            LOG.info("QUERY(" + query + ") produced unused resultset with " + count + " rows");
        } else {
            int updateCount = stmt.getUpdateCount();
            LOG.info("QUERY(" + query + ") Update count: " + updateCount);
        }
    } catch (SQLException ex) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0003, query, ex);
    } finally {
        closeStatements(stmt);
    }
}

From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java

private Event removeEventByExtId(Connection con, ObmUser calendar, AccessToken token, EventExtId extId) {
    Event ev = findEventByExtId(token, calendar, extId);
    if (ev == null) {
        return null;
    }/*  www.  j  a  v a 2 s .c om*/
    PreparedStatement dev = null;
    try {
        dev = con.prepareStatement(
                "INSERT INTO DeletedEvent (deletedevent_event_id, deletedevent_user_id, deletedevent_origin, deletedevent_timestamp, deletedevent_event_ext_id) "
                        + "VALUES (?, ?, ?, now(), ?)");
        EventObmId databaseId = ev.getObmId();
        for (Attendee at : ev.getAttendees()) {
            Integer userId = userDao.userIdFromEmail(con, at.getEmail(), token.getDomain().getId());
            if (userId != null) {
                dev.setInt(1, databaseId.getObmId());
                dev.setInt(2, userId);
                dev.setString(3, token.getOrigin());
                dev.setString(4, extId.getExtId());
                dev.addBatch();
            }
        }
        dev.executeBatch();
        dev.close();

        removeEventExceptions(con, databaseId);

        dev = con.prepareStatement("DELETE FROM Event WHERE event_id=?");
        dev.setInt(1, databaseId.getObmId());
        dev.executeUpdate();
        if (dev.getUpdateCount() <= 0) {
            throw new EventNotFoundException("Unexpected Event deletion failure : " + databaseId.getObmId());
        }
    } catch (Throwable se) {
        logger.error(se.getMessage(), se);
    } finally {
        obmHelper.cleanup(null, dev, null);
    }

    return ev;
}

From source file:org.openmrs.util.databasechange.MigrateConceptReferenceTermChangeSet.java

/**
 * @see liquibase.change.custom.CustomTaskChange#execute(liquibase.database.Database)
 *///  w w w. j  a  v  a 2s . com
@Override
public void execute(Database database) throws CustomChangeException {
    final JdbcConnection connection = (JdbcConnection) database.getConnection();
    Boolean prevAutoCommit = null;

    PreparedStatement selectTypes = null;
    PreparedStatement batchUpdateMap = null;
    PreparedStatement selectMap = null;
    PreparedStatement updateMapTerm = null;
    PreparedStatement insertTerm = null;
    PreparedStatement updateMapType = null;

    try {
        prevAutoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);

        //Prepare a list of types and their ids.
        Map<String, Integer> typesToIds = new HashMap<String, Integer>();

        selectTypes = connection.prepareStatement("select * from concept_map_type");
        selectTypes.execute();
        ResultSet selectTypeResult = selectTypes.getResultSet();

        while (selectTypeResult.next()) {
            typesToIds.put(selectTypeResult.getString("name").trim().toUpperCase(),
                    selectTypeResult.getInt("concept_map_type_id"));
        }
        selectTypes.close();

        //The FK on concept_reference_term_id is not yet created so we are safe to copy over IDs. 
        //The trims are done to be able to compare properly.
        batchUpdateMap = connection.prepareStatement(
                "update concept_reference_map set" + " concept_reference_term_id = concept_map_id,"
                        + " source_code = trim(source_code), comment = trim(comment)");
        batchUpdateMap.execute();
        batchUpdateMap.close();

        //Preparing statements for use in the loop.
        updateMapTerm = connection.prepareStatement(
                "update concept_reference_map set" + " concept_reference_term_id = ? where concept_map_id = ?");
        insertTerm = connection.prepareStatement("insert into concept_reference_term"
                + " (concept_reference_term_id, uuid, concept_source_id, code, creator, date_created, description)"
                + " values (?, ?, ?, ?, ?, ?, ?)");
        updateMapType = connection.prepareStatement(
                "update concept_reference_map set" + " concept_map_type_id = ? where concept_map_id = ?");

        int prevSource = -1;
        String prevSourceCode = null;
        String prevComment = null;
        int prevInsertedTerm = -1;

        //In addition to source and source_code we order by UUID to always insert the same term if run on different systems.
        selectMap = connection.prepareStatement(
                "select * from concept_reference_map" + " order by source, source_code, uuid");
        selectMap.execute();

        final ResultSet selectMapResult = selectMap.getResultSet();

        while (selectMapResult.next()) {
            final int conceptMapId = selectMapResult.getInt("concept_map_id");
            final int source = selectMapResult.getInt("source");
            final String sourceCode = selectMapResult.getString("source_code");
            final String comment = selectMapResult.getString("comment");
            final int creator = selectMapResult.getInt("creator");
            final Date dateCreated = selectMapResult.getDate("date_created");
            final String uuid = selectMapResult.getString("uuid");

            final Integer mapTypeId = determineMapTypeId(comment, typesToIds);
            final int updatedMapTypeId = (mapTypeId == null) ? typesToIds.get(DEFAULT_CONCEPT_MAP_TYPE)
                    : mapTypeId;
            updateMapType.setInt(1, updatedMapTypeId);
            updateMapType.setInt(2, conceptMapId);
            updateMapType.execute();
            if (updateMapType.getUpdateCount() != 1) {
                throw new CustomChangeException("Failed to set map type: " + mapTypeId + " for map: "
                        + conceptMapId + ", updated rows: " + updateMapType.getUpdateCount());
            }

            if (source == prevSource && (sourceCode == prevSourceCode
                    || (sourceCode != null && sourceCode.equals(prevSourceCode)))) {
                if (mapTypeId == null && comment != null && !comment.equals(prevComment)) {
                    log.warn("Lost comment '" + comment + "' for map " + conceptMapId + ". Preserved comment "
                            + prevComment);
                }

                //We need to use the last inserted term.
                updateMapTerm.setInt(1, prevInsertedTerm);
                updateMapTerm.setInt(2, conceptMapId);

                updateMapTerm.execute();
                if (updateMapTerm.getUpdateCount() != 1) {
                    throw new CustomChangeException(
                            "Failed to set reference term: " + prevInsertedTerm + " for map: " + conceptMapId
                                    + ", updated rows: " + updateMapTerm.getUpdateCount());
                }
            } else {
                insertTerm.setInt(1, conceptMapId);
                //We need to guaranty that UUIDs are always the same when run on different systems.
                insertTerm.setString(2, UUID.nameUUIDFromBytes(uuid.getBytes()).toString());
                insertTerm.setInt(3, source);
                insertTerm.setString(4, sourceCode);
                insertTerm.setInt(5, creator);
                insertTerm.setDate(6, dateCreated);
                if (mapTypeId == null) {
                    insertTerm.setString(7, comment);
                } else {
                    insertTerm.setString(7, null);
                }

                insertTerm.execute();

                prevInsertedTerm = conceptMapId;
            }

            prevSource = source;
            prevSourceCode = sourceCode;
            prevComment = comment;
        }
        selectMap.close();
        updateMapType.close();
        updateMapTerm.close();
        insertTerm.close();

        connection.commit();
    } catch (Exception e) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (Exception ex) {
            log.error("Failed to rollback", ex);
        }

        throw new CustomChangeException(e);
    } finally {
        closeStatementQuietly(selectTypes);
        closeStatementQuietly(batchUpdateMap);
        closeStatementQuietly(selectMap);
        closeStatementQuietly(updateMapTerm);
        closeStatementQuietly(insertTerm);
        closeStatementQuietly(updateMapType);

        if (connection != null && prevAutoCommit != null) {
            try {
                connection.setAutoCommit(prevAutoCommit);
            } catch (DatabaseException e) {
                log.error("Failed to reset auto commit", e);
            }
        }
    }
}

From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java

private boolean changeParticipation(Connection con, AccessToken token, EventExtId extId,
        RecurrenceId recurrenceId, ObmUser calendarOwner, Participation participation)
        throws SQLException, ParseException {

    PreparedStatement ps = null;

    String q = "UPDATE EventLink " + "SET eventlink_state = ?, eventlink_userupdate = ?, eventlink_comment = ? "
            + "WHERE eventlink_event_id IN " + "(" + "SELECT event_id " + "FROM Event e "
            + "LEFT JOIN EventException eexp ON e.event_id = eventexception_child_id "
            + "WHERE event_ext_id = ? " + "AND eexp.eventexception_date = ?" + ") AND "
            + "eventlink_entity_id IN "
            + "( SELECT userentity_entity_id FROM UserEntity WHERE userentity_user_id = ? )";

    Integer loggedUserId = token.getObmId();

    try {/*from   w w  w  . jav a 2s. c  o m*/
        ps = con.prepareStatement(q);

        int idx = 1;
        ps.setObject(idx++,
                obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, participation.getState().toString()));
        ps.setInt(idx++, loggedUserId);
        ps.setString(idx++, participation.getSerializedCommentToString());
        ps.setString(idx++, extId.getExtId());
        ps.setTimestamp(idx++, recurrenceHelper.timestampFromDateString(recurrenceId.getRecurrenceId()));
        ps.setInt(idx++, calendarOwner.getUid());
        ps.execute();
        if (ps.getUpdateCount() > 0) {
            return true;
        }
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } catch (ParseException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
    return false;
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private boolean changeParticipation(Connection con, AccessToken token, EventExtId extId,
        RecurrenceId recurrenceId, ObmUser calendarOwner, Participation participation)
        throws SQLException, ParseException {

    PreparedStatement ps = null;

    String q = "UPDATE EventLink " + "SET eventlink_state = ?, eventlink_userupdate = ?, eventlink_comment = ? "
            + "WHERE eventlink_event_id IN " + "(" + "SELECT event_id " + "FROM Event e "
            + "LEFT JOIN EventException eexp ON e.event_id = eventexception_child_id "
            + "WHERE event_ext_id = ? " + "AND eexp.eventexception_date = ?" + ") AND "
            + "eventlink_entity_id IN "
            + "( SELECT userentity_entity_id FROM UserEntity WHERE userentity_user_id = ? )";

    Integer loggedUserId = token.getObmId();

    try {//  w  ww .  jav  a2 s .  co  m
        ps = con.prepareStatement(q);

        int idx = 1;
        ps.setObject(idx++,
                obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, participation.getState().toString()));
        ps.setInt(idx++, loggedUserId);
        ps.setString(idx++, participation.getSerializedCommentToString());
        ps.setString(idx++, extId.getExtId());
        Date recId = new DateTime(recurrenceId.getRecurrenceId());
        ps.setTimestamp(idx++, new Timestamp(recId.getTime()));
        ps.setInt(idx++, calendarOwner.getUid());
        ps.execute();
        if (ps.getUpdateCount() > 0) {
            return true;
        }
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } catch (ParseException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
    return false;
}

From source file:org.apache.ode.scheduler.simple.jdbc.SchedulerDAOConnectionImpl.java

@SuppressWarnings("unchecked")
public List<JobDAO> dequeueImmediate(String nodeId, long maxtime, int maxjobs) throws DatabaseException {
    ArrayList<JobDAO> ret = new ArrayList<JobDAO>(maxjobs);
    Connection con = null;/*w ww  .ja va 2  s .  co  m*/
    PreparedStatement ps = null;
    try {
        con = getConnection();
        ps = con.prepareStatement(SCHEDULE_IMMEDIATE);
        ps.setString(1, nodeId);
        ps.setLong(2, maxtime);
        ps.setMaxRows(maxjobs);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Scheduler.JobDetails details = new Scheduler.JobDetails();
            details.instanceId = asLong(rs.getObject("instanceId"));
            details.mexId = (String) rs.getObject("mexId");
            details.processId = (String) rs.getObject("processId");
            details.type = (String) rs.getObject("type");
            details.channel = (String) rs.getObject("channel");
            details.correlatorId = (String) rs.getObject("correlatorId");
            details.correlationKeySet = (String) rs.getObject("correlationKeySet");
            details.retryCount = asInteger(rs.getObject("retryCount"));
            details.inMem = rs.getBoolean("inMem");
            if (rs.getObject("detailsExt") != null) {
                try {
                    ObjectInputStream is = new ObjectInputStream(rs.getBinaryStream("detailsExt"));
                    details.detailsExt = (Map<String, Object>) is.readObject();
                    is.close();
                } catch (Exception e) {
                    throw new DatabaseException("Error deserializing job detailsExt", e);
                }
            }

            {
                //For compatibility reasons, we check whether there are entries inside
                //jobDetailsExt blob, which correspond to extracted entries. If so, we
                //use them.

                Map<String, Object> detailsExt = details.getDetailsExt();
                if (detailsExt.get("type") != null) {
                    details.type = (String) detailsExt.get("type");
                }
                if (detailsExt.get("iid") != null) {
                    details.instanceId = (Long) detailsExt.get("iid");
                }
                if (detailsExt.get("pid") != null) {
                    details.processId = (String) detailsExt.get("pid");
                }
                if (detailsExt.get("inmem") != null) {
                    details.inMem = (Boolean) detailsExt.get("inmem");
                }
                if (detailsExt.get("ckey") != null) {
                    details.correlationKeySet = (String) detailsExt.get("ckey");
                }
                if (detailsExt.get("channel") != null) {
                    details.channel = (String) detailsExt.get("channel");
                }
                if (detailsExt.get("mexid") != null) {
                    details.mexId = (String) detailsExt.get("mexid");
                }
                if (detailsExt.get("correlatorId") != null) {
                    details.correlatorId = (String) detailsExt.get("correlatorId");
                }
                if (detailsExt.get("retryCount") != null) {
                    details.retryCount = Integer.parseInt((String) detailsExt.get("retryCount"));
                }
            }

            JobDAO job = new JobDAOImpl(rs.getLong("ts"), rs.getString("jobid"), rs.getBoolean("transacted"),
                    details);
            ret.add(job);
        }
        rs.close();
        ps.close();

        // mark jobs as scheduled, UPDATE_SCHEDULED_SLOTS at a time
        int j = 0;
        int updateCount = 0;
        ps = con.prepareStatement(UPDATE_SCHEDULED);
        for (int updates = 1; updates <= (ret.size() / UPDATE_SCHEDULED_SLOTS) + 1; updates++) {
            for (int i = 1; i <= UPDATE_SCHEDULED_SLOTS; i++) {
                ps.setString(i, j < ret.size() ? ret.get(j).getJobId() : "");
                j++;
            }
            ps.execute();
            updateCount += ps.getUpdateCount();
        }
        if (updateCount != ret.size()) {
            __log.error("Updating scheduled jobs failed to update all jobs; expected=" + ret.size() + " actual="
                    + updateCount);
            return null;

        }
    } catch (SQLException se) {
        throw new DatabaseException(se);
    } finally {
        close(ps);
        close(con);
    }
    return ret;
}

From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java

/**
 * Remove the root ('@') or tail ('@@') relationship node for the given
 * coding scheme.// w ww. jav a 2s  .  com
 * 
 * @param codingScheme
 *            The coding scheme to remove the root node from.
 * @param relationName
 *            The relation container for the root node. If null, the native
 *            relation for the coding scheme is used.
 * @param root
 *            - true for root ('@'), false for tail ('@@').
 * @throws SQLException
 */
public void removeRootRelationNode(String codingScheme, String relationName, boolean root) throws SQLException {
    if (!doTablesExist())
        return;

    int count = 0;
    Connection conn = getConnection();
    try {
        // Define the SQL statements to locate and delete affected entries
        // ...
        StringBuffer sb = new StringBuffer("SELECT * FROM ")
                .append(stc_.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_ENTITY))
                .append(" WHERE " + stc_.codingSchemeNameOrId + " = ? AND " + stc_.containerNameOrContainerDC
                        + " = ? AND ")
                .append(root ? (stc_.sourceEntityCodeOrId + " = '@'")
                        : (stc_.targetEntityCodeOrId + " = '@@'"));
        PreparedStatement getRoots = conn.prepareStatement(gsm_.modifySQL(sb.toString()));

        sb = new StringBuffer("DELETE FROM ")
                .append(stc_.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_ENTITY))
                .append(" WHERE " + stc_.codingSchemeNameOrId + " = ? AND " + stc_.containerNameOrContainerDC
                        + " = ? AND " + stc_.entityCodeOrAssociationId + " = ?")
                .append(" AND " + stc_.sourceCSIdOrEntityCodeNS + " = ? AND " + stc_.sourceEntityCodeOrId
                        + " = ?")
                .append(" AND " + stc_.targetCSIdOrEntityCodeNS + " = ? AND " + stc_.targetEntityCodeOrId
                        + " = ?");
        PreparedStatement deleteAssoc = conn.prepareStatement(gsm_.modifySQL(sb.toString()));

        sb = new StringBuffer("DELETE FROM ")
                .append(stc_.getTableName(SQLTableConstants.ENTITY_ASSOCIATION_TO_E_QUALS))
                .append(" WHERE " + stc_.codingSchemeNameOrId + " = ? AND "
                        + SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY + " = ?");
        PreparedStatement deleteCQual = conn.prepareStatement(gsm_.modifySQL(sb.toString()));

        // Locate matching entries and clear, along with associated
        // qualifiers ...
        try {
            getRoots.setString(1, codingScheme);
            getRoots.setString(2, relationName != null ? relationName : getNativeRelation(codingScheme));
            ResultSet rs = getRoots.executeQuery();
            while (rs.next()) {
                // Remove matching qualifiers ...
                String multiKey = rs.getString(SQLTableConstants.TBLCOL_MULTIATTRIBUTESKEY);
                if (multiKey != null && multiKey.length() > 0) {
                    deleteCQual.clearParameters();
                    deleteCQual.clearWarnings();
                    deleteCQual.setString(1, codingScheme);
                    deleteCQual.setString(2, multiKey);
                    deleteCQual.execute();
                }

                // Remove the association/source/target ...
                deleteAssoc.clearParameters();
                deleteAssoc.clearWarnings();
                deleteAssoc.setString(1, codingScheme);
                deleteAssoc.setString(2, relationName);
                deleteAssoc.setString(3, rs.getString(stc_.entityCodeOrAssociationId));
                deleteAssoc.setString(4, rs.getString(stc_.sourceCSIdOrEntityCodeNS));
                deleteAssoc.setString(5, rs.getString(stc_.sourceEntityCodeOrId));
                deleteAssoc.setString(6, rs.getString(stc_.targetCSIdOrEntityCodeNS));
                deleteAssoc.setString(7, rs.getString(stc_.targetEntityCodeOrId));
                if (!deleteAssoc.execute() && deleteAssoc.getUpdateCount() > 0)
                    count += deleteAssoc.getUpdateCount();
            }
            rs.close();
        } finally {
            getRoots.close();
            deleteAssoc.close();
            deleteCQual.close();
        }
    } finally {
        returnConnection(conn);
        log.info("Removed " + count + " root associations.");
    }
}

From source file:org.kawanfw.sql.servlet.sql.ServerStatementRawExecute.java

/**
 * Execute the passed SQL PreparedStatement as execute() sand 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 v  a  2 s  .  com*/
 * @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 executePrepStatement(OutputStream out) throws SQLException, IOException {
    String sqlOrder = statementHolder.getSqlOrder();

    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();

        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 + " [" + "{Prepared Statement not authorized for execute}"
                    + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}"
                    + "{sql values: " + parameterStringValues + "}]";

            throw new SecurityException(message);
        }

        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 isAllowed = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder,
                serverPreparedStatementParameters.getParameterValues());

        if (!isAllowed) {

            String ipAddress = request.getRemoteAddr();

            SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress,
                    sqlOrder, serverPreparedStatementParameters.getParameterValues());

            String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}"
                    + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}"
                    + "{sql values: " + parameterStringValues + "}]";

            throw new SecurityException(message);
        }

        debug("before preparedStatement.execute()");

        ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator);

        boolean isResultSet = preparedStatement.execute();

        if (isResultSet) {
            ResultSet rs = preparedStatement.getResultSet();

            try {

                //out.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 {
            int rc = preparedStatement.getUpdateCount();

            //out.write(TransferStatus.SEND_OK + CR_LF);
            //out.write("getUpdateCount=" + rc + CR_LF);
            ServerSqlManager.writeLine(out, TransferStatus.SEND_OK);
            ServerSqlManager.writeLine(out, "getUpdateCount=" + rc);

            // Write the preparedStatement.getGeneratedKeys() on the stream
            // if necessary
            if (usesAutoGeneratedKeys) {
                ResultSet rs = preparedStatement.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 + "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();
        }

    }
}

From source file:org.pentaho.di.core.database.Database.java

public Result execStatement(String rawsql, RowMetaInterface params, Object[] data)
        throws KettleDatabaseException {
    Result result = new Result();

    // Replace existing code with a class that removes comments from the raw
    // SQL.//  w  w  w  . j  a va 2s  . c o m
    // The SqlCommentScrubber respects single-quoted strings, so if a
    // double-dash or a multiline comment appears
    // in a single-quoted string, it will be treated as a string instead of
    // comments.
    String sql = SqlCommentScrubber.removeComments(rawsql).trim();
    try {
        boolean resultSet;
        int count;
        if (params != null) {
            PreparedStatement prep_stmt = connection.prepareStatement(databaseMeta.stripCR(sql));
            setValues(params, data, prep_stmt); // set the parameters!
            resultSet = prep_stmt.execute();
            count = prep_stmt.getUpdateCount();
            prep_stmt.close();
        } else {
            String sqlStripped = databaseMeta.stripCR(sql);
            // log.logDetailed("Executing SQL Statement: ["+sqlStripped+"]");
            Statement stmt = connection.createStatement();
            resultSet = stmt.execute(sqlStripped);
            count = stmt.getUpdateCount();
            stmt.close();
        }
        String upperSql = sql.toUpperCase();
        if (!resultSet) {
            // if the result is a resultset, we don't do anything with it!
            // You should have called something else!
            // log.logDetailed("What to do with ResultSet??? (count="+count+")");
            if (count > 0) {
                if (upperSql.startsWith("INSERT")) {
                    result.setNrLinesOutput(count);
                } else if (upperSql.startsWith("UPDATE")) {
                    result.setNrLinesUpdated(count);
                } else if (upperSql.startsWith("DELETE")) {
                    result.setNrLinesDeleted(count);
                }
            }
        }

        // See if a cache needs to be cleared...
        if (upperSql.startsWith("ALTER TABLE") || upperSql.startsWith("DROP TABLE")
                || upperSql.startsWith("CREATE TABLE")) {
            DBCache.getInstance().clear(databaseMeta.getName());
        }
    } catch (SQLException ex) {
        throw new KettleDatabaseException("Couldn't execute SQL: " + sql + Const.CR, ex);
    } catch (Exception e) {
        throw new KettleDatabaseException("Unexpected error executing SQL: " + Const.CR, e);
    }

    return result;
}

From source file:nl.nn.adapterframework.jdbc.JdbcTransactionalStorage.java

protected String storeMessageInDatabase(Connection conn, String messageId, String correlationId,
        Timestamp receivedDateTime, String comments, String label, Serializable message)
        throws IOException, SQLException, JdbcException, SenderException {
    PreparedStatement stmt = null;
    try {//from  w w  w.ja  v  a  2 s. c  o m
        IDbmsSupport dbmsSupport = getDbmsSupport();
        if (log.isDebugEnabled())
            log.debug("preparing insert statement [" + insertQuery + "]");
        if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) {
            stmt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
        } else {
            stmt = conn.prepareStatement(insertQuery);
        }
        stmt.clearParameters();
        int parPos = 0;

        if (StringUtils.isNotEmpty(getTypeField())) {
            stmt.setString(++parPos, type);
        }
        if (StringUtils.isNotEmpty(getSlotId())) {
            stmt.setString(++parPos, getSlotId());
        }
        if (StringUtils.isNotEmpty(getHostField())) {
            stmt.setString(++parPos, host);
        }
        if (StringUtils.isNotEmpty(getLabelField())) {
            stmt.setString(++parPos, label);
        }
        stmt.setString(++parPos, messageId);
        stmt.setString(++parPos, correlationId);
        stmt.setTimestamp(++parPos, receivedDateTime);
        stmt.setString(++parPos, comments);
        if (type.equalsIgnoreCase(TYPE_MESSAGELOG_PIPE) || type.equalsIgnoreCase(TYPE_MESSAGELOG_RECEIVER)) {
            if (getRetention() < 0) {
                stmt.setTimestamp(++parPos, null);
            } else {
                Date date = new Date();
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                cal.add(Calendar.DAY_OF_MONTH, getRetention());
                stmt.setTimestamp(++parPos, new Timestamp(cal.getTime().getTime()));
            }
        } else {
            stmt.setTimestamp(++parPos, null);
        }

        if (!isStoreFullMessage()) {
            if (isOnlyStoreWhenMessageIdUnique()) {
                stmt.setString(++parPos, messageId);
                stmt.setString(++parPos, slotId);
            }
            stmt.execute();
            return null;
        }
        if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) {
            ByteArrayOutputStream out = new ByteArrayOutputStream();

            if (isBlobsCompressed()) {
                DeflaterOutputStream dos = new DeflaterOutputStream(out);
                ObjectOutputStream oos = new ObjectOutputStream(dos);
                oos.writeObject(message);
                dos.close();
            } else {
                ObjectOutputStream oos = new ObjectOutputStream(out);
                oos.writeObject(message);
            }

            stmt.setBytes(++parPos, out.toByteArray());
            if (isOnlyStoreWhenMessageIdUnique()) {
                stmt.setString(++parPos, messageId);
                stmt.setString(++parPos, slotId);
            }
            stmt.execute();
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getString(1);
            } else {
                return null;
            }
        }
        if (isOnlyStoreWhenMessageIdUnique()) {
            stmt.setString(++parPos, messageId);
            stmt.setString(++parPos, slotId);
        }
        stmt.execute();
        int updateCount = stmt.getUpdateCount();
        if (log.isDebugEnabled())
            log.debug("update count for insert statement: " + updateCount);
        if (updateCount > 0) {
            if (log.isDebugEnabled())
                log.debug("preparing select statement [" + selectKeyQuery + "]");
            stmt = conn.prepareStatement(selectKeyQuery);
            ResultSet rs = null;
            try {
                // retrieve the key
                rs = stmt.executeQuery();
                if (!rs.next()) {
                    throw new SenderException("could not retrieve key of stored message");
                }
                String newKey = rs.getString(1);
                rs.close();

                // and update the blob
                if (log.isDebugEnabled())
                    log.debug("preparing update statement [" + updateBlobQuery + "]");
                stmt = conn.prepareStatement(updateBlobQuery);
                stmt.clearParameters();
                stmt.setString(1, newKey);

                rs = stmt.executeQuery();
                if (!rs.next()) {
                    throw new SenderException("could not retrieve row for stored message [" + messageId + "]");
                }
                //                  String newKey = rs.getString(1);
                //                  BLOB blob = (BLOB)rs.getBlob(2);
                Object blobHandle = dbmsSupport.getBlobUpdateHandle(rs, 1);
                OutputStream out = dbmsSupport.getBlobOutputStream(rs, 1, blobHandle);
                //               OutputStream out = JdbcUtil.getBlobUpdateOutputStream(rs,1);
                if (isBlobsCompressed()) {
                    DeflaterOutputStream dos = new DeflaterOutputStream(out);
                    ObjectOutputStream oos = new ObjectOutputStream(dos);
                    oos.writeObject(message);
                    oos.close();
                    dos.close();
                } else {
                    ObjectOutputStream oos = new ObjectOutputStream(out);
                    oos.writeObject(message);
                    oos.close();
                }
                out.close();
                dbmsSupport.updateBlob(rs, 1, blobHandle);
                return newKey;

            } finally {
                if (rs != null) {
                    rs.close();
                }
            }
        } else {
            if (isOnlyStoreWhenMessageIdUnique()) {
                return "already there";
            } else {
                throw new SenderException(
                        "update count for update statement not greater than 0 [" + updateCount + "]");
            }
        }

    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}