Example usage for java.sql PreparedStatement clearParameters

List of usage examples for java.sql PreparedStatement clearParameters

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

Introduction

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

Prototype

void clearParameters() throws SQLException;

Source Link

Document

Clears the current parameter values immediately.

Usage

From source file:net.dv8tion.discord.commands.TodoCommand.java

private void handleLock(MessageReceivedEvent e, String[] args, boolean locked) throws SQLException {
    checkArgs(args, 2,/*from  w  w w.java 2s . com*/
            "No todo ListName was specified. Usage: `" + getAliases().get(0) + " lock/unlock [ListName]`");

    String label = args[2].toLowerCase();
    TodoList todoList = todoLists.get(label);
    if (todoList == null) {
        sendMessage(e, "Sorry, `" + label + "` isn't a known todo list.");
        return;
    }

    if (!todoList.isAuthUser(e.getAuthor())) {
        sendMessage(e, "Sorry, you do not have permission to lock or unlock the `" + label + "` todo list.");
        return;
    }

    PreparedStatement setTodoListLocked = Database.getInstance().getStatement(SET_TODO_LIST_LOCKED);
    setTodoListLocked.setBoolean(1, locked);
    setTodoListLocked.setInt(2, todoList.id);
    if (setTodoListLocked.executeUpdate() == 0)
        throw new SQLException(SET_TODO_LIST_LOCKED + " reported no updated rows!");
    setTodoListLocked.clearParameters();

    todoList.locked = locked;
    sendMessage(e, "The `" + label + "` todo list was `" + (locked ? "locked`" : "unlocked`"));
}

From source file:com.spvp.dal.MySqlDatabase.java

@Override
public Boolean ucitajPrognozeUBazu(ArrayList<Prognoza> prognoze) throws SQLException {

    Connection conn = null;//from   w w  w  .j  ava 2  s . c  om
    Boolean status = false;

    try {
        conn = getConnection();
        conn.setAutoCommit(false);

        Statement s = conn.createStatement();
        ResultSet rs = s.executeQuery("SELECT AUTO_INCREMENT " + "FROM  INFORMATION_SCHEMA.TABLES "
                + "WHERE TABLE_SCHEMA = 'weather_forecasting' " + "AND   TABLE_NAME   = 'historija_prognoze';");

        int zadnjiId = -1;

        rs.next();
        zadnjiId = rs.getInt("AUTO_INCREMENT");

        int idGrada = -1;

        PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO historija_prognoze (id, vrijeme, temp, pritisak, brzina_vjetra, vlaznost_zraka, datum) "
                        + "VALUES(?, ?,?,?,?,?,?)");

        PreparedStatement pstmt3 = conn
                .prepareStatement("INSERT INTO gradovi_prognoze (prognoza_id, grad_id) " + "VALUES(?,?)");

        for (Prognoza x : prognoze) {

            pstmt.clearParameters();
            pstmt.setInt(1, zadnjiId);
            pstmt.setString(2, x.getVrijeme());
            pstmt.setString(3, x.getTemperatura());
            pstmt.setString(4, x.getPritisakZraka());
            pstmt.setString(5, x.getBrzinaVjetra());
            pstmt.setString(6, x.getVlaznostZraka());
            pstmt.setDate(7, new java.sql.Date(x.getDatum().getTime()));
            pstmt.addBatch();

            idGrada = dajIdGradaPoImenu(x.getZaGrad().getImeGrada());

            pstmt3.clearParameters();
            pstmt3.setInt(1, zadnjiId);
            pstmt3.setInt(2, idGrada);

            pstmt3.addBatch();

            zadnjiId++;
        }

        pstmt.executeBatch();
        pstmt3.executeBatch();

        conn.commit();
        status = true;

    } catch (SQLException ex) {
        Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex);
        if (conn != null)
            conn.rollback();

    } finally {

        if (conn != null)
            conn.close();
    }

    return status;
}

From source file:org.sakaiproject.search.indexer.impl.test.TDataSource.java

/**
 * @param items/*  w  w w . jav a 2  s .  c o  m*/
 * @return
 * @throws SQLException 
 */
public List<SearchBuilderItem> deleteSomeItems(List<SearchBuilderItem> items) throws SQLException {
    int nitems = 0;

    Connection connection = null;
    PreparedStatement updatePST = null;
    try {
        connection = getDataSource().getConnection();
        updatePST = connection.prepareStatement(
                "update searchbuilderitem  set searchaction = ?, searchstate = ? where id = ?");
        int i = 0;
        for (SearchBuilderItem sbi : items) {
            if (sbi.getSearchaction().equals(SearchBuilderItem.ACTION_ADD)) {
                i++;
                if (i % 3 == 0) {
                    sbi.setSearchaction(SearchBuilderItem.ACTION_DELETE);
                    sbi.setSearchstate(SearchBuilderItem.STATE_PENDING);
                    updatePST.clearParameters();
                    updatePST.setInt(1, sbi.getSearchaction());
                    updatePST.setInt(2, sbi.getSearchstate());
                    updatePST.setString(3, sbi.getId());
                    if (updatePST.executeUpdate() != 1) {
                        throw new SQLException("Failed to update for delete " + sbi.getId());
                    }
                    log.info("Marked " + sbi.getName() + " for deletion ");
                }
            }
        }
        connection.commit();
    } finally {
        try {
            updatePST.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
        try {
            connection.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
    }
    return items;
}

From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateDependencyHelper.java

private void myDoInPreparedStatement(long sysTime, MMDDependency dependency, PreparedStatement ps)
        throws SQLException {

    String ownerModelId = dependency.getOwnerMetadata().getClassifierId();
    String valueModelId = dependency.getValueMetadata().getClassifierId();

    try {// w w w .  java2s .  c  om
        // ?ID
        ps.setString(1, dependency.getOwnerMetadata().getId());
        // ?ID
        ps.setString(2, ownerModelId);
        // ?ID
        ps.setString(3, dependency.getValueMetadata().getId());
        // ?ID
        ps.setString(4, valueModelId);
        // ???
        ps.setString(5, dependency.getCode());
        // 
        ps.setLong(6, sysTime);
        ps.setString(7, "1");
        ps.setString(8, "1");

        setPs(ps, 8);

        ps.addBatch();
        // ??
        ps.clearParameters();
        if (++super.count % super.batchSize == 0) {
            ps.executeBatch();
            ps.clearBatch();
        }
    } catch (SQLException e) {
        String logMsg = new StringBuilder().append("?,?:??ID:")
                .append(dependency.getOwnerMetadata().getId()).append(",?:")
                .append(ownerModelId).append(",??ID:")
                .append(dependency.getValueMetadata().getId()).append(",?:")
                .append(valueModelId).append(", ???:").append(dependency.getCode()).toString();
        log.error(logMsg);
        AdapterExtractorContext.addExtractorLog(ExtractorLogLevel.ERROR, logMsg);
        throw e;
    }
}

From source file:com.commander4j.db.JDBPrinters.java

public boolean delete() {
    PreparedStatement stmtupdate;
    boolean result = false;
    setErrorMessage("");

    try {/*w w w.  j av  a  2s .co  m*/

        stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(
                Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBPrinters.delete"));
        stmtupdate.setString(1, getPrinterID());
        stmtupdate.setString(2, getGroupID());
        stmtupdate.execute();
        stmtupdate.clearParameters();
        Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit();
        stmtupdate.close();
        result = true;

        /* Remove all links to printer */
        JDBPrinterLineMembership plm = new JDBPrinterLineMembership(getHostID(), getSessionID());
        plm.deleteForPrinterID(getPrinterID());

    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }

    return result;
}

From source file:org.silverpeas.migration.jcr.version.VersionedDocumentMigration.java

private void cleanAll(List<OldDocumentMetadata> metadata) throws SQLException {
    console.printMessage("   Clean all the deprecated documents in " + this.componentId);
    Connection connection = getConnection();
    connection.setAutoCommit(false);/*from w  ww.  j ava 2s  . c  om*/
    PreparedStatement deleteVersions = connection.prepareStatement(DELETE_DOCUMENT_VERSIONS);
    PreparedStatement deleteAttachment = connection.prepareStatement(DELETE_DOCUMENT);
    try {
        for (OldDocumentMetadata document : metadata) {
            try {
                deleteVersions.setLong(1, document.getOldSilverpeasId());
                deleteVersions.executeUpdate();
                deleteAttachment.setLong(1, document.getOldSilverpeasId());
                deleteAttachment.executeUpdate();
                connection.commit();
                deleteVersions.clearParameters();
                deleteAttachment.clearParameters();
            } catch (SQLException ex) {
                console.printError("Error while cleaning up in database the document " + document.getTitle()
                        + " (id = " + document.getOldSilverpeasId() + ")");
                throw ex;
            }
            for (Version version : document.getHistory()) {
                File file = null;
                try {
                    file = version.getAttachment();
                    if (file != null) {
                        ConverterUtil.deleteFile(file);
                    }
                } catch (IOException ioex) {
                    String fileName = (file != null ? file.getPath() : "");
                    console.printError("Error deleting file " + fileName, ioex);
                }
            }
        }
    } finally {
        DbUtils.closeQuietly(deleteVersions);
        DbUtils.closeQuietly(deleteAttachment);
        DbUtils.closeQuietly(connection);
    }
}

From source file:org.panbox.core.keymgmt.JDBCHelperNonRevokeable.java

private void storeSharePaticipants(Connection con, ShareMetaData smd)
        throws SQLException, PersistanceException {
    Iterator<String> it = smd.shareParticipants.getAliases();

    Statement s = con.createStatement();
    s.executeUpdate(DROP_SPL);// www.j  a v  a 2s.  c  om
    s.executeUpdate(CREATE_SPL);
    s.close();
    PreparedStatement insert = con.prepareStatement(INSERT_SPL);
    while (it.hasNext()) {
        String alias = it.next();
        PublicKey pKey = smd.shareParticipants.getPublicKey(alias);
        insert.setString(1, alias);
        insert.setBytes(2, pKey.getEncoded());
        int i = insert.executeUpdate();
        logger.debug("Inserted " + i + " rows of shareparticipants");
        insert.clearParameters();
    }
    if (insert != null) {
        try {
            insert.close();
        } catch (Exception e) {
            logger.warn("Could not close Statement", e);
        }
    }
    storeSignature(con, smd.shareParticipants.getSignature());
}

From source file:org.apache.oozie.util.db.SqlStatement.java

/**
 * Assign the values to Prepared Statement. setValues should be called to set the Real Values for place holders
 *
 * @param pStmt Prepared Statement/*from  w w w  .j av a2 s  . c om*/
 * @return PreparedStatement with values set
 * @throws SQLException
 */
public PreparedStatement prepare(PreparedStatement pStmt) throws SQLException {
    int i = 1;
    pStmt.clearParameters();
    for (Object value : this.values) {
        pStmt.setObject(i, value);
        i++;
    }
    return pStmt;
}

From source file:org.sakaiproject.nakamura.lite.storage.jdbc.WideColumnIndexer.java

public void index(Map<String, PreparedStatement> statementCache, String keySpace, String columnFamily,
        String key, String rid, Map<String, Object> values) throws StorageClientException, SQLException {
    ResultSet rs = null;//from w  w  w  .j a v a2s.com

    try {
        Set<String> removeArrayColumns = Sets.newHashSet();
        Set<String> removeColumns = Sets.newHashSet();
        Map<String, Object[]> updateArrayColumns = Maps.newHashMap();
        Map<String, Object> updateColumns = Maps.newHashMap();
        for (Entry<String, Object> e : values.entrySet()) {
            String k = e.getKey();
            Object o = e.getValue();
            Object[] valueMembers = (o instanceof Object[]) ? (Object[]) o : new Object[] { o };
            if (shouldIndex(keySpace, columnFamily, k)) {
                if (isColumnArray(keySpace, columnFamily, k)) {
                    if (o instanceof RemoveProperty || o == null || valueMembers.length == 0) {
                        removeArrayColumns.add(k);
                    } else {
                        removeArrayColumns.add(k);
                        updateArrayColumns.put(k, valueMembers);
                    }
                } else {
                    if (o instanceof RemoveProperty || o == null || valueMembers.length == 0) {
                        removeColumns.add(k);
                    } else {
                        updateColumns.put(k, valueMembers[0]);
                    }

                }
            }
        }

        if (!StorageClientUtils.isRoot(key)
                && getColumnName(keySpace, columnFamily, InternalContent.PARENT_HASH_FIELD) != null) {
            String parent = StorageClientUtils.getParentObjectPath(key);
            String hash = client.rowHash(keySpace, columnFamily, parent);
            LOGGER.debug("Hash of {}:{}:{} is {} ", new Object[] { keySpace, columnFamily, parent, hash });
            updateColumns.put(InternalContent.PARENT_HASH_FIELD, hash);
        }

        LOGGER.debug("Removing Array {} ", removeArrayColumns);
        LOGGER.debug("Updating Array {} ", updateArrayColumns);
        LOGGER.debug("Removing  {} ", removeColumns);
        LOGGER.debug("Updating  {} ", updateColumns);

        // arrays are stored in css, so we can re-use css sql.
        PreparedStatement removeStringColumn = client.getStatement(keySpace, columnFamily,
                JDBCStorageClient.SQL_REMOVE_STRING_COLUMN, rid, statementCache);
        int nbatch = 0;
        for (String column : removeArrayColumns) {
            removeStringColumn.clearWarnings();
            removeStringColumn.clearParameters();
            removeStringColumn.setString(1, rid);
            removeStringColumn.setString(2, column);
            removeStringColumn.addBatch();
            LOGGER.debug("Removing {} {} ", rid, column);
            nbatch++;
        }
        if (nbatch > 0) {
            long t = System.currentTimeMillis();
            removeStringColumn.executeBatch();
            checkSlow(t, client.getSql(keySpace, columnFamily, JDBCStorageClient.SQL_REMOVE_STRING_COLUMN));
            nbatch = 0;
        }

        // add the column values in
        PreparedStatement insertStringColumn = client.getStatement(keySpace, columnFamily,
                JDBCStorageClient.SQL_INSERT_STRING_COLUMN, rid, statementCache);
        for (Entry<String, Object[]> e : updateArrayColumns.entrySet()) {
            for (Object o : e.getValue()) {
                insertStringColumn.clearWarnings();
                insertStringColumn.clearParameters();
                insertStringColumn.setString(1, o.toString());
                insertStringColumn.setString(2, rid);
                insertStringColumn.setString(3, e.getKey());
                insertStringColumn.addBatch();
                LOGGER.debug("Inserting {} {} {} ", new Object[] { o.toString(), rid, e.getKey() });
                nbatch++;
            }
        }
        if (nbatch > 0) {
            long t = System.currentTimeMillis();
            insertStringColumn.executeBatch();
            checkSlow(t, client.getSql(keySpace, columnFamily, JDBCStorageClient.SQL_INSERT_STRING_COLUMN));
            nbatch = 0;
        }
        if (removeColumns.size() == 0 && updateColumns.size() == 0) {
            return; // nothing to add or remove, do nothing.
        }

        if (removeColumns.size() > 0 && updateColumns.size() == 0) {
            // exists, columns to remove, none to update, therefore
            // delete row this assumes that the starting point is a
            // complete map
            PreparedStatement deleteWideStringColumn = client.getStatement(keySpace, columnFamily,
                    SQL_DELETE_WIDESTRING_ROW, rid, statementCache);
            deleteWideStringColumn.clearParameters();
            deleteWideStringColumn.setString(1, rid);
            long t = System.currentTimeMillis();
            deleteWideStringColumn.execute();
            checkSlow(t, client.getSql(keySpace, columnFamily, SQL_DELETE_WIDESTRING_ROW));
            LOGGER.debug("Executed {} with {} ", deleteWideStringColumn, rid);
        } else if (updateColumns.size() > 0 || removeColumns.size() > 0) {
            //
            // build an update query, record does not exists, but there
            // is stuff to add
            String[] sqlParts = StringUtils
                    .split(client.getSql(keySpace, columnFamily, SQL_UPDATE_WIDESTRING_ROW), ";");
            StringBuilder setOperations = new StringBuilder();
            for (Entry<String, Object> e : updateColumns.entrySet()) {
                join(setOperations, " ,").append(
                        MessageFormat.format(sqlParts[1], getColumnName(keySpace, columnFamily, e.getKey())));
            }
            for (String toRemove : removeColumns) {
                join(setOperations, " ,").append(
                        MessageFormat.format(sqlParts[1], getColumnName(keySpace, columnFamily, toRemove)));
            }
            String finalSql = MessageFormat.format(sqlParts[0], setOperations);
            LOGGER.debug("Performing {} ", finalSql);
            PreparedStatement updateColumnPst = client.getStatement(finalSql, statementCache);
            updateColumnPst.clearWarnings();
            updateColumnPst.clearParameters();
            int i = 1;
            for (Entry<String, Object> e : updateColumns.entrySet()) {
                updateColumnPst.setString(i, e.getValue().toString());
                LOGGER.debug("   Param {} {} ", i, e.getValue().toString());
                i++;
            }
            for (String toRemove : removeColumns) {
                updateColumnPst.setNull(i, toSqlType(columnFamily, toRemove));
                LOGGER.debug("   Param {} NULL ", i);
                i++;
            }
            updateColumnPst.setString(i, rid);
            long t = System.currentTimeMillis();
            int n = updateColumnPst.executeUpdate();
            checkSlow(t, finalSql);
            if (n == 0) {
                // part 0 is the final ,part 1 is the template for column names,
                // part 2 is the template for parameters.
                // insert into x ( columnsnames ) values ()
                StringBuilder columnNames = new StringBuilder();
                StringBuilder paramHolders = new StringBuilder();
                for (Entry<String, Object> e : updateColumns.entrySet()) {
                    columnNames.append(" ,").append(getColumnName(keySpace, columnFamily, e.getKey()));
                    paramHolders.append(" ,").append("?");
                }
                finalSql = MessageFormat.format(
                        client.getSql(keySpace, columnFamily, SQL_INSERT_WIDESTRING_ROW),
                        columnNames.toString(), paramHolders.toString());
                LOGGER.debug("Insert SQL {} ", finalSql);
                PreparedStatement insertColumnPst = client.getStatement(finalSql, statementCache);
                insertColumnPst.clearWarnings();
                insertColumnPst.clearParameters();
                insertColumnPst.setString(1, rid);
                i = 2;
                for (Entry<String, Object> e : updateColumns.entrySet()) {
                    LOGGER.debug("   Param {} {} ", i, e.getValue().toString());
                    insertColumnPst.setString(i, e.getValue().toString());
                    i++;
                }
                t = System.currentTimeMillis();
                insertColumnPst.executeUpdate();
                checkSlow(t, finalSql);
            }
        }
    } finally {
        if (rs != null) {
            rs.close();
        }
    }

}

From source file:net.dv8tion.discord.commands.TodoCommand.java

private void handleAdd(MessageReceivedEvent e, String[] args) throws SQLException {
    checkArgs(args, 2,//from   www. j  a va2 s.  c o m
            "No todo ListName was specified. Usage: `" + getAliases().get(0) + " add [ListName] [content...]`");
    checkArgs(args, 3, "No content was specified. Cannot create an empty todo entry!" + "Usage: `"
            + getAliases().get(0) + " add [ListName] [content...]`");

    String label = args[2].toLowerCase();
    String content = StringUtils.join(args, " ", 3, args.length);
    TodoList todoList = todoLists.get(label);

    if (todoList == null) {
        sendMessage(e, "Sorry, `" + label + "` isn't a known todo list. " + "Try using `" + getAliases().get(0)
                + " create " + label + "` to create a new list by this name.");
        return;
    }

    if (todoList.locked && !todoList.isAuthUser(e.getAuthor())) {
        sendMessage(e,
                "Sorry, `" + label + "` is a locked todo list and you do not have permission to modify it.");
        return;
    }

    PreparedStatement addTodoEntry = Database.getInstance().getStatement(ADD_TODO_ENTRY);
    addTodoEntry.setInt(1, todoList.id);
    addTodoEntry.setString(2, content);
    addTodoEntry.setBoolean(3, false);
    if (addTodoEntry.executeUpdate() == 0)
        throw new SQLException(ADD_TODO_ENTRY + " reported no modified rows!");

    todoList.entries.add(new TodoEntry(Database.getAutoIncrement(addTodoEntry, 1), content, false));
    addTodoEntry.clearParameters();

    sendMessage(e, "Added to `" + label + "` todo list.");
}