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:org.sakaiproject.search.transaction.impl.TransactionSequenceImpl.java

public long getNextId() {
    check();// w w w  .j av a 2 s.c om
    Connection connection = null;
    PreparedStatement selectpst = null;
    PreparedStatement updatepst = null;
    PreparedStatement resetpst = null;
    ResultSet rs = null;

    try {
        connection = datasource.getConnection();
        selectpst = connection
                .prepareStatement("select txid from search_transaction where txname = '" + name + "'");

        resetpst = connection.prepareStatement(
                "update search_transaction set txid = " + minValue + "  where  txname = '" + name + "'");
        updatepst = connection.prepareStatement(
                "update search_transaction set txid = txid + 1  where  txname = '" + name + "'");

        boolean success = false;
        long txid = 0;
        long retries = 0;
        while (!success) {
            updatepst.clearParameters();
            success = (updatepst.executeUpdate() == 1);
            if (!success) {
                connection.rollback();
                retries++;
            } else {
                // this works in a transaction since we read what we just
                // updated.
                // if the DB is non transactional this will not work
                rs = selectpst.executeQuery();
                if (rs.next()) {
                    txid = rs.getLong(1);
                    if (wrap && txid > maxValue) {
                        resetpst.clearParameters();
                        success = (resetpst.executeUpdate() == 1);
                        if (!success) {
                            throw new RuntimeException("Failed to reset ");
                        }
                        success = false;

                    }
                } else {
                    log.error("Transaction Record has been removed");
                }
                rs.close();
            }
            if (retries > 10) {
                throw new RuntimeException("Failed to get a transaction, retried 10 times ");
            }
        }
        connection.commit();
        return txid;

    } catch (Exception ex) {
        try {
            connection.rollback();
        } catch (Exception ex2) {
        }
        log.warn("Failed to get a transaction id, ignore for HSQLDB pre-1.9+ versions. " + ex.getMessage());
        // log.error("Failed to get a transaction id ", ex);
        return -1;
    } finally {
        try {
            rs.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
        try {
            selectpst.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
        try {
            updatepst.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
        try {
            resetpst.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
        try {
            connection.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
    }
}

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

public void handleClear(MessageReceivedEvent e, String[] args) throws SQLException {
    checkArgs(args, 2, "No todo ListName was specified. Usage: `" + getAliases().get(0) + " clear [ListName]`");

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

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

    int clearedEntries = 0;
    PreparedStatement removeTodoEntry = Database.getInstance().getStatement(REMOVE_TODO_ENTRY);
    for (Iterator<TodoEntry> it = todoList.entries.iterator(); it.hasNext();) {
        TodoEntry todoEntry = it.next();
        if (todoEntry.checked) {
            removeTodoEntry.setInt(1, todoEntry.id);
            if (removeTodoEntry.executeUpdate() == 0)
                throw new SQLException(REMOVE_TODO_ENTRY + " reported no updated rows!");
            removeTodoEntry.clearParameters();

            it.remove();
            clearedEntries++;
        }
    }
    sendMessage(e, "Cleared **" + clearedEntries + "** completed entries from the `" + label + "` todo list.");
}

From source file:org.netkernelroc.gradle.apposite.Package.java

public void install(Connection connection, File nkInstance) throws Exception {
    PackageVersion toInstall = versions.last();
    File packageFile = toInstall.download(nkInstance, connection);

    File expandedPackage = NetKernelConvention.createTempDir();
    NetKernelConvention.expandZip(packageFile, expandedPackage);

    Document manifestDocument = new Builder().build(new File(expandedPackage, "manifest.xml"));
    Nodes modulesNodes = manifestDocument.query("/manifest/module");

    final String setInstalledSql = "UPDATE PACKAGE_VERSIONS SET INSTALLED=TRUE WHERE ID=?;";
    final PreparedStatement setInstalledPS = connection.prepareStatement(setInstalledSql);

    final String addTransactionEventSql = "INSERT INTO PACKAGE_TRANSACTION_EVENTS VALUES (\n" + "    NULL,\n"
            + "    @TRANSACTIONID,\n" + "    1,\n" + "    ?\n" + ");";
    final PreparedStatement addTransactionEventPS = connection.prepareStatement(addTransactionEventSql);

    final String addModuleSql = "MERGE INTO MODULES (\n" + "    PACKAGEVID,\n" + "    IDENTITY,\n"
            + "    VERSION,\n" + "    LOCALSRC,\n" + "    RUNLEVEL,\n" + "    EXPANDED\n" + ")\n"
            + "KEY (IDENTITY, VERSION)\n" + "VALUES (\n" + "    ?,\n" + "    ?,\n" + "    ?,\n" + "    ?,\n"
            + "    ?,\n" + "    ?\n" + ");";
    final PreparedStatement addModulePS = connection.prepareStatement(addModuleSql);

    setInstalledPS.clearParameters();
    setInstalledPS.setLong(1, toInstall.getId());
    setInstalledPS.executeUpdate();// w  w  w.  j a v  a2 s. c o m

    addTransactionEventPS.clearParameters();
    addTransactionEventPS.setLong(1, id);
    addTransactionEventPS.executeUpdate();

    for (int moduleI = 0; moduleI < modulesNodes.size(); moduleI++) {
        Node moduleNode = modulesNodes.get(moduleI);

        String uri = moduleNode.query("uri").get(0).getValue();
        String version = moduleNode.query("version").get(0).getValue();
        int runLevel = Integer.parseInt(moduleNode.query("runlevel").get(0).getValue());
        String source = moduleNode.query("source").get(0).getValue();
        boolean expand = Boolean.parseBoolean(moduleNode.query("expand").get(0).getValue());

        Integer[] versionArray = RepositorySet.stringArrayToIntArray(version.split("\\."));

        File targetFile;
        if (uri.startsWith("urn:com:ten60:core:")) {
            expand = false;
            targetFile = new File(nkInstance, "lib");
        } else {
            targetFile = new File(nkInstance, "modules");
        }

        File moduleJar = new File(expandedPackage, source);

        String baseName = uri.replaceAll(":", ".") + "-" + version;

        File target;
        File jarTarget = new File(targetFile, baseName + ".jar");
        File expandedTarget = new File(targetFile, baseName);

        if (expand) {
            target = expandedTarget;
        } else {
            target = jarTarget;
        }

        if (target.exists()) {
            System.out.println("Not moving module into place as it already exists");
        } else {
            if (expand) {
                System.out.println("Expanding module " + uri + " to " + expandedTarget.getAbsolutePath());
                NetKernelConvention.expandZip(moduleJar, expandedTarget);
            } else {
                System.out.println("Moving module " + uri + " to " + jarTarget.getAbsolutePath());
                FileUtils.moveFile(moduleJar, jarTarget);
            }
        }

        addModulePS.clearParameters();
        addModulePS.setLong(1, toInstall.getId());
        addModulePS.setString(2, uri);
        addModulePS.setObject(3, versionArray);
        addModulePS.setString(4, nkInstance.toURI().relativize(target.toURI()).getPath());
        addModulePS.setInt(5, runLevel);
        addModulePS.setBoolean(6, expand);
        addModulePS.executeUpdate();
    }
    FileUtils.deleteDirectory(expandedPackage);

    latestInstalledVersion = toInstall;
}

From source file:org.owasp.dependencycheck.data.nvdcve.CveDB.java

/**
 * Returns the specified prepared statement.
 *
 * @param key the prepared statement from {@link PreparedStatementCveDb} to
 * return/*from  w ww. java  2  s  .  c om*/
 * @return the prepared statement
 * @throws SQLException thrown if a SQL Exception occurs
 */
private synchronized PreparedStatement getPreparedStatement(PreparedStatementCveDb key) throws SQLException {
    if (!preparedStatements.containsKey(key)) {
        return null;
    }
    final PreparedStatement preparedStatement = preparedStatements.get(key);
    preparedStatement.clearParameters();
    return preparedStatement;
}

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

public DisposableIterator<Map<String, Object>> find(final String keySpace, final String columnFamily,
        Map<String, Object> properties, final CachingManager cachingManager) throws StorageClientException {
    String[] keys = null;/*from   w w w.  ja va 2  s  .c  om*/
    if (properties != null && properties.containsKey(StorageConstants.CUSTOM_STATEMENT_SET)) {
        String customStatement = (String) properties.get(StorageConstants.CUSTOM_STATEMENT_SET);
        keys = new String[] { "wide-" + customStatement + "." + keySpace + "." + columnFamily,
                "wide-" + customStatement + "." + columnFamily, "wide-" + customStatement,
                "wide-block-find." + keySpace + "." + columnFamily, "wide-block-find." + columnFamily,
                "wide-block-find" };
    } else {
        keys = new String[] { "wide-block-find." + keySpace + "." + columnFamily,
                "wide-block-find." + columnFamily, "wide-block-find" };
    }

    final boolean rawResults = properties != null && properties.containsKey(StorageConstants.RAWRESULTS);

    String sql = client.getSql(keys);
    if (sql == null) {
        throw new StorageClientException("Failed to locate SQL statement for any of  " + Arrays.toString(keys));
    }

    // collect information on paging
    long page = 0;
    long items = 25;
    if (properties != null) {
        if (properties.containsKey(StorageConstants.PAGE)) {
            page = Long.valueOf(String.valueOf(properties.get(StorageConstants.PAGE)));
        }
        if (properties.containsKey(StorageConstants.ITEMS)) {
            items = Long.valueOf(String.valueOf(properties.get(StorageConstants.ITEMS)));
        }
    }
    long offset = page * items;

    // collect information on sorting
    List<String> sortingList = Lists.newArrayList();
    String sortProp = (String) properties.get(StorageConstants.SORT);
    if (sortProp != null) {
        String[] sorts = StringUtils.split(sortProp);
        if (sorts.length == 1) {
            if (shouldIndex(keySpace, columnFamily, sorts[0])
                    && !isColumnArray(keySpace, columnFamily, sorts[0])) {
                sortingList.add(getColumnName(keySpace, columnFamily, sorts[0]));
                sortingList.add("asc");
            }
        } else if (sorts.length > 1) {
            for (int i = 0; i < sorts.length; i += 2) {
                if (shouldIndex(keySpace, columnFamily, sorts[0])
                        && !isColumnArray(keySpace, columnFamily, sorts[i])) {
                    sortingList.add(getColumnName(keySpace, columnFamily, sorts[0]));
                    sortingList.add(sorts[i + 1]);
                }
            }
        }
    }
    String[] sorts = sortingList.toArray(new String[sortingList.size()]);
    String[] statementParts = StringUtils.split(sql, ';');
    /*
     * Part 0 basic SQL template; {0} is the where clause {1} is the sort clause {2} is the from {3} is the to record
     *   eg select rid from css where {0} {1} LIMIT {2} ROWS {3}
     * Part 1 where clause for non array matches; {0} is the columnName
     *   eg {0} = ?
     * Part 2 where clause for array matches (not possible to sort on array matches) {0} is the table alias, {1} is the where clause
     *   eg rid in ( select {0}.rid from css {0} where {1} )
     * Part 3 the where clause for array matches {0} is the table alias
     *   eg {0}.cid = ? and {0}.v = ?  
     * Part 3 sort clause {0} is the list to sort by
     *   eg sort by {0}
     * Part 4 sort elements, {0} is the column, {1} is the order
     *   eg {0} {1}
     * Dont include , AND or OR, the code will add those as appropriate. 
     */

    StringBuilder whereClause = new StringBuilder();
    List<Object> parameters = Lists.newArrayList();
    int set = 0;
    for (Entry<String, Object> e : properties.entrySet()) {
        Object v = e.getValue();
        String k = e.getKey();
        if (shouldFind(keySpace, columnFamily, k) || (v instanceof Map)) {
            if (v != null) {
                // check for a value map and treat sub terms as for OR terms.
                // Only go 1 level deep; don't recurse. That's just silly.
                if (v instanceof Map) {
                    // start the OR grouping
                    @SuppressWarnings("unchecked")
                    Set<Entry<String, Object>> subterms = ((Map<String, Object>) v).entrySet();
                    StringBuilder subQuery = new StringBuilder();
                    for (Iterator<Entry<String, Object>> subtermsIter = subterms.iterator(); subtermsIter
                            .hasNext();) {
                        Entry<String, Object> subterm = subtermsIter.next();
                        String subk = subterm.getKey();
                        Object subv = subterm.getValue();
                        // check that each subterm should be indexed
                        if (shouldFind(keySpace, columnFamily, subk)) {
                            set = processEntry(statementParts, keySpace, columnFamily, subQuery, parameters,
                                    subk, subv, sorts, set, " OR ");
                        }
                    }
                    if (subQuery.length() > 0) {
                        join(whereClause, " AND ").append("( ").append(subQuery.toString()).append(" ) ");
                    }
                } else {
                    // process a first level non-map value as an AND term

                    if (v instanceof Iterable<?>) {
                        for (Object vo : (Iterable<?>) v) {
                            set = processEntry(statementParts, keySpace, columnFamily, whereClause, parameters,
                                    k, vo, sorts, set, " AND ");
                        }
                    } else {
                        set = processEntry(statementParts, keySpace, columnFamily, whereClause, parameters, k,
                                v, sorts, set, " AND ");
                    }
                }
            } else if (!k.startsWith("_")) {
                LOGGER.debug("Search on {}:{} filter dropped due to null value.", columnFamily, k);
            }
        } else {
            if (!k.startsWith("_")) {
                LOGGER.warn("Search on {}:{} is not supported, filter dropped ", columnFamily, k);
            }
        }
    }
    // there was no where clause generated
    // to avoid returneing everything, we wont return anything.
    if (whereClause.length() == 0) {
        return new DisposableIterator<Map<String, Object>>() {

            private Disposer disposer;

            public boolean hasNext() {
                return false;
            }

            public Map<String, Object> next() {
                return null;
            }

            public void remove() {
            }

            public void close() {
                if (disposer != null) {
                    disposer.unregisterDisposable(this);
                }
            }

            public void setDisposer(Disposer disposer) {
                this.disposer = disposer;
            }

        };
    }

    StringBuilder sortClause = new StringBuilder();
    if (statementParts.length > SQL_SORT_CLAUSE_PART) {
        StringBuilder sortList = new StringBuilder();
        for (int i = 0; i < sorts.length; i += 2) {
            if (shouldFind(keySpace, columnFamily, sorts[0])) {
                join(sortList, ", ").append(
                        MessageFormat.format(statementParts[SQL_SORT_LIST_PART], sorts[i], sorts[i + 1]));
            }
        }
        if (sortList.length() > 0) {
            sortClause.append(MessageFormat.format(statementParts[SQL_SORT_CLAUSE_PART], sortList.toString()));
        }
    }

    final String sqlStatement = MessageFormat.format(statementParts[SQL_QUERY_TEMPLATE_PART],
            whereClause.toString(), sortClause.toString(), items, offset);

    PreparedStatement tpst = null;
    ResultSet trs = null;
    try {

        LOGGER.debug("Preparing {} ", sqlStatement);
        tpst = client.getConnection().prepareStatement(sqlStatement);
        client.inc("iterator");
        tpst.clearParameters();
        int i = 1;
        for (Object params : parameters) {
            tpst.setObject(i, params);
            LOGGER.debug("Setting {} ", params);
            i++;
        }

        long qtime = System.currentTimeMillis();
        trs = tpst.executeQuery();
        qtime = System.currentTimeMillis() - qtime;
        if (qtime > client.getSlowQueryThreshold() && qtime < client.getVerySlowQueryThreshold()) {
            JDBCStorageClient.SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] { qtime,
                    sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) });
        } else if (qtime > client.getVerySlowQueryThreshold()) {
            JDBCStorageClient.SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] { qtime,
                    sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) });
        }
        client.inc("iterator r");
        LOGGER.debug("Executed ");

        // pass control to the iterator.
        final PreparedStatement pst = tpst;
        final ResultSet rs = trs;
        final ResultSetMetaData rsmd = rs.getMetaData();
        tpst = null;
        trs = null;
        return client.registerDisposable(new PreemptiveIterator<Map<String, Object>>() {

            private Map<String, Object> nextValue = Maps.newHashMap();
            private boolean open = true;

            @Override
            protected Map<String, Object> internalNext() {
                return nextValue;
            }

            @Override
            protected boolean internalHasNext() {
                try {
                    if (open && rs.next()) {
                        if (rawResults) {
                            Builder<String, Object> b = ImmutableMap.builder();
                            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                                b.put(String.valueOf(i), rs.getObject(i));
                            }
                            nextValue = b.build();
                        } else {
                            String id = rs.getString(1);
                            nextValue = client.internalGet(keySpace, columnFamily, id, cachingManager);
                            LOGGER.debug("Got Row ID {} {} ", id, nextValue);
                        }
                        return true;
                    }
                    close();
                    nextValue = null;
                    LOGGER.debug("End of Set ");
                    return false;
                } catch (SQLException e) {
                    LOGGER.error(e.getMessage(), e);
                    close();
                    nextValue = null;
                    return false;
                } catch (StorageClientException e) {
                    LOGGER.error(e.getMessage(), e);
                    close();
                    nextValue = null;
                    return false;
                }
            }

            @Override
            public void close() {
                if (open) {
                    open = false;
                    try {
                        if (rs != null) {
                            rs.close();
                            client.dec("iterator r");
                        }
                    } catch (SQLException e) {
                        LOGGER.warn(e.getMessage(), e);
                    }
                    try {
                        if (pst != null) {
                            pst.close();
                            client.dec("iterator");
                        }
                    } catch (SQLException e) {
                        LOGGER.warn(e.getMessage(), e);
                    }
                    super.close();
                }

            }
        });
    } catch (SQLException e) {
        LOGGER.error(e.getMessage(), e);
        throw new StorageClientException(e.getMessage() + " SQL Statement was " + sqlStatement, e);
    } finally {
        // trs and tpst will only be non null if control has not been passed
        // to the iterator.
        try {
            if (trs != null) {
                trs.close();
                client.dec("iterator r");
            }
        } catch (SQLException e) {
            LOGGER.warn(e.getMessage(), e);
        }
        try {
            if (tpst != null) {
                tpst.close();
                client.dec("iterator");
            }
        } catch (SQLException e) {
            LOGGER.warn(e.getMessage(), e);
        }
    }
}

From source file:org.sakaiproject.search.index.soaktest.SharedTestDataSource.java

public int populateDocuments(long targetItems, String instanceName) throws SQLException {
    int nitems = 0;
    Connection connection = null;
    PreparedStatement insertPST = null;
    PreparedStatement deletePST = null;
    try {/*from ww  w . j av  a 2  s.c  o m*/
        connection = getDataSource().getConnection();
        insertPST = connection.prepareStatement("insert into searchbuilderitem "
                + "(id,version,name,context,searchaction,searchstate,itemscope) values " + "(?,?,?,?,?,?,?)");
        deletePST = connection.prepareStatement("delete from searchbuilderitem where name = ? ");
        for (int i = 0; i < targetItems; i++) {
            int state = i % SearchBuilderItem.states.length;
            String name = SearchBuilderItem.states[state];
            int action = i % 3;
            if (state == SearchBuilderItem.STATE_PENDING && action == SearchBuilderItem.ACTION_ADD) {
                nitems++;
            }
            String reference = "/" + instanceName + "/" + name + "/at/a/location/" + (ldoc++);
            deletePST.clearParameters();
            deletePST.setString(1, reference);
            deletePST.executeUpdate();

            insertPST.clearParameters();
            insertPST.setString(1, String.valueOf(instanceName + System.currentTimeMillis()) + String.valueOf(i)
                    + ":" + String.valueOf(docid++));
            insertPST.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            insertPST.setString(3, reference);
            insertPST.setString(4, "/" + name + "/at/a");
            insertPST.setInt(5, action);
            insertPST.setInt(6, state);
            insertPST.setInt(7, SearchBuilderItem.ITEM);
            insertPST.execute();
        }
        connection.commit();
    } finally {
        try {
            insertPST.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
        try {
            deletePST.close();
        } catch (Exception e) {
            log.debug(e);
        }
        try {
            connection.close();
        } catch (Exception ex2) {
            log.debug(ex2);
        }
    }
    return nitems;

}

From source file:org.sakaiproject.search.optimize.shared.impl.DbJournalOptimizationManager.java

/**
 * @see org.sakaiproject.search.journal.api.JournalManager#rollbackSave(org.sakaiproject.search.journal.api.JournalManagerState)
 *//*www  .j a  v  a  2s.  c o  m*/
public void rollbackSave(JournalManagerState jms) {
    OptimizeJournalManagerStateImpl ojms = (OptimizeJournalManagerStateImpl) jms;
    PreparedStatement updateTarget = null;
    Connection connection = null;
    try {
        connection = datasource.getConnection();
        // set the target to committed and then delete the rest
        // so the final segment becomes commtted with a writer id of
        // this+txiD,
        // and all merging-prepare states in this transaction are removed.
        updateTarget = connection.prepareStatement(
                "update search_journal set status = 'committed', txts = ? where indexwriter = ? and status = 'merging-prepare'  ");
        updateTarget.clearParameters();
        updateTarget.setLong(1, System.currentTimeMillis());
        updateTarget.setString(2, ojms.indexWriter);
        int i = updateTarget.executeUpdate();

        connection.commit();
        log.info("Rolled Back Failed Shared Index operation a retry will happen on annother node soon ");
    } catch (Exception ex) {
        try {
            connection.rollback();
        } catch (Exception ex2) {
            log.error("Rollback Of shared Journal Merge Failed ", ex);
        }
    } finally {
        try {
            updateTarget.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
        try {
            connection.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
    }

}

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

private void handleCreate(MessageReceivedEvent e, String[] args) throws SQLException {
    checkArgs(args, 2, "No ListName for the new todo list was provided. Usage: `" + getAliases().get(0)
            + " create [ListName]`");

    String label = args[2].toLowerCase();
    TodoList todoList = todoLists.get(label);

    if (todoList != null) {
        sendMessage(e, "A todo list already exists with the name `" + label + "`.");
        return;//from   w  w  w . ja va  2 s .  c  o  m
    }

    PreparedStatement addTodoList = Database.getInstance().getStatement(ADD_TODO_LIST);
    addTodoList.setString(1, label); //Label
    addTodoList.setString(2, e.getAuthor().getId());//OwnerId
    addTodoList.setBoolean(3, false); //Locked
    if (addTodoList.executeUpdate() == 0)
        throw new SQLException(ADD_TODO_LIST + " reported no modified rows!");

    todoList = new TodoList(Database.getAutoIncrement(addTodoList, 1), label, e.getAuthor().getId(), false);
    todoLists.put(label, todoList);
    addTodoList.clearParameters();

    sendMessage(e, "Created `" + label + "` todo list. Use `" + getAliases().get(0) + " add " + label
            + " [content...]` " + "to add entries to this todo list.");
}

From source file:org.sakaiproject.search.indexer.impl.SearchBuilderQueueManager.java

private List<SearchBuilderItem> getSiteMasterItems(Connection connection) throws SQLException {
    PreparedStatement pst = null;
    PreparedStatement lockMaster = null;

    ResultSet rst = null;//from w  ww.  java 2  s. c  o  m
    try {

        lockMaster = connection.prepareStatement("update " + SEARCH_BUILDER_ITEM_T
                + " set searchstate = ? where itemscope = ? and searchstate = ? ");
        lockMaster.clearParameters();
        lockMaster.setInt(1, nodeLock);
        lockMaster.setInt(2, SearchBuilderItem.ITEM_SITE_MASTER.intValue());
        lockMaster.setInt(3, SearchBuilderItem.STATE_PENDING.intValue());
        lockMaster.executeUpdate();

        pst = connection.prepareStatement("select " //$NON-NLS-1$
                + SEARCH_BUILDER_ITEM_FIELDS + " from " //$NON-NLS-1$
                + SEARCH_BUILDER_ITEM_T + " where itemscope =   ? and searchstate = ?  "); //$NON-NLS-1$
        pst.clearParameters();
        pst.setInt(1, SearchBuilderItem.ITEM_SITE_MASTER.intValue());
        pst.setInt(2, nodeLock);
        rst = pst.executeQuery();
        List<SearchBuilderItem> a = new ArrayList<SearchBuilderItem>();
        while (rst.next()) {
            SearchBuilderItemImpl sbi = new SearchBuilderItemImpl();
            populateSearchBuilderItem(rst, sbi);
            a.add(sbi);
        }
        if (a.size() > 0) {
            connection.commit();
        } else {
            connection.rollback();
        }
        return a;
    } finally {
        try {
            rst.close();
        } catch (Exception ex) {
            log.warn("Error closing rst", ex);
        }
        try {
            pst.close();
        } catch (Exception ex) {
            log.warn("Error closing pst", ex);
        }
        try {
            lockMaster.close();
        } catch (Exception ex) {
            log.warn("Error closing lockMaster", ex);
        }
    }
}

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

private void handleUsers(MessageReceivedEvent e, String[] args) throws SQLException {
    checkArgs(args, 2,/*from w w  w .j  ava 2  s. co m*/
            "No SubAction was specified. Usage: `" + getAliases().get(0) + " users [SubAction] [ListName]`");
    checkArgs(args, 3, "No todo ListName was specified. Usage: `" + getAliases().get(0)
            + " users [SubAction] [ListName]`");

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

    switch (action) {
    case "add": {
        if (!todoList.ownerId.equals(e.getAuthor().getId())) {
            sendMessage(e, "Sorry, but only the Owner of a list has permission add users to a todo list.");
            return;
        }

        if (e.getMessage().getMentionedUsers().size() == 0) {
            sendMessage(e, "No users were specified to add to the `" + label + "` todo list.");
            return;
        }

        int addedUsers = 0;
        PreparedStatement addTodoUser = Database.getInstance().getStatement(ADD_TODO_USER);
        for (User u : e.getMessage().getMentionedUsers()) {
            if (!todoList.isAuthUser(u)) {
                addTodoUser.setInt(1, todoList.id);
                addTodoUser.setString(2, u.getId());
                if (addTodoUser.executeUpdate() == 0)
                    throw new SQLException(ADD_TODO_LIST + " reported no updated rows!");
                addTodoUser.clearParameters();

                todoList.allowedUsers.add(u.getId());
                addedUsers++;
            }
        }

        sendMessage(e, "Added **" + addedUsers + "** users to the `" + label + "` todo list.");
        break;
    }
    case "remove": {
        if (!todoList.ownerId.equals(e.getAuthor().getId())) {
            sendMessage(e, "Sorry, but only the Owner of a list has permission remove users from a todo list.");
            return;
        }

        if (e.getMessage().getMentionedUsers().size() == 0) {
            sendMessage(e, "No users were specified to add to the `" + label + "` todo list.");
            return;
        }

        int removedUsers = 0;
        PreparedStatement removeTodoUser = Database.getInstance().getStatement(REMOVE_TODO_USER);
        for (User u : e.getMessage().getMentionedUsers()) {
            if (todoList.allowedUsers.stream().anyMatch(id -> u.getId().equals(id))) {
                removeTodoUser.setInt(1, todoList.id);
                removeTodoUser.setString(2, u.getId());
                if (removeTodoUser.executeUpdate() == 0)
                    throw new SQLException(REMOVE_TODO_USER + " reported no updated rows!");
                removeTodoUser.clearParameters();

                todoList.allowedUsers.remove(u.getId());
                removedUsers++;
            }
        }

        sendMessage(e, "Removed **" + removedUsers + "** users from the `" + label + "` todo list.");
        break;
    }
    case "list": {
        MessageBuilder builder = new MessageBuilder();
        builder.append("__Owner of `" + label + "`__\n");
        User owner = api.getUserById(todoList.ownerId);
        if (owner != null)
            builder.append(" - " + owner.getName());
        else
            builder.append(" - Unknown User ID: " + todoList.ownerId);
        builder.append("\n");
        builder.append("__Other Auth'd Users__\n");

        for (String id : todoList.allowedUsers) {
            User u = api.getUserById(id);
            if (u != null)
                builder.append(" - " + u.getName());
            else
                builder.append(" - Unknown User ID: " + id);
            builder.append("\n");
        }
        if (todoList.allowedUsers.isEmpty())
            builder.append(" - None.");
        sendMessage(e, builder.build());
        break;
    }
    default: {
        sendMessage(e, "Sorry, the provided sub-action argument for the `users` action is not recognized. "
                + "Provided argument: `" + action + "`");
        return;
    }
    }
}