Example usage for java.sql PreparedStatement setString

List of usage examples for java.sql PreparedStatement setString

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

Introduction

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

Prototype

void setString(int parameterIndex, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:eu.sisob.uma.restserver.AuthorizationManager.java

private static boolean DBAuthorizeUserIn(String user, String pass) {
    boolean success = false;
    Connection conn = null;//from ww  w  .  j a va2 s . c om
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        String query = "SELECT 1 FROM USERS WHERE user_email = ? and user_pass = ?";
        conn = SystemManager.getInstance().getSystemDbPool().getConnection();
        statement = conn.prepareStatement(query);
        statement.setString(1, user);
        statement.setString(2, pass);

        rs = statement.executeQuery();
        if (rs.next())
            success = true;
        else
            success = false;
    } catch (SQLException ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } catch (Exception ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (statement != null)
            try {
                statement.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        statement = null;
        rs = null;
    }

    return success;
}

From source file:com.chaosinmotion.securechat.server.commands.DropMessages.java

public static void processRequest(UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    ArrayList<Message> messages = new ArrayList<Message>();

    JSONArray a = requestParams.getJSONArray("messages");
    int i, len = a.length();
    for (i = 0; i < len; ++i) {
        JSONObject item = a.getJSONObject(i);
        Message msg = new Message();
        msg.message = item.getInt("messageid");
        msg.checksum = item.getString("checksum");
        messages.add(msg);/* w  w  w. ja va2 s.  c o m*/
    }

    /*
     * Iterate through the messages, deleting each. We only delete a
     * message if message belongs to the user and the checksum matches.
     * This assumes it's our message and it was read with someone who
     * can read the message.
     * 
     * (Thus, the weird query)
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        int count = 0;
        c = Database.get();
        ps = c.prepareStatement("DELETE FROM Messages " + "WHERE messageid IN "
                + "    (SELECT Messages.messageid " + "     FROM Messages, Devices "
                + "     WHERE Messages.messageid = ? " + "     AND Messages.checksum = ? "
                + "     AND Devices.deviceid = Messages.deviceid " + "     AND Devices.userid = ?)");

        for (Message msg : messages) {
            /*
             * Get the device ID for this device. Verify it belongs to the
             * user specified
             */

            ps.setInt(1, msg.message);
            ps.setString(2, msg.checksum);
            ps.setInt(3, userinfo.getUserID());
            ps.addBatch();
            ++count;
            if (count > 10240) {
                ps.executeBatch();
            }
        }
        if (count > 0) {
            ps.executeBatch();
        }
    } catch (BatchUpdateException batch) {
        throw batch.getNextException();
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.concursive.connect.web.modules.profile.utils.ProjectUtils.java

private static String generateUniqueId(String title, int projectId, Connection db) throws SQLException {
    // Title can look like...
    // Some Project Name
    // some-project-name
    // some-project-name-2

    // Format to allowed characters to get extension (some will be treated later)
    String allowed = "abcdefghijklmnopqrstuvwxyz1234567890-/& ";
    String nameToSearch = StringUtils.toAllowedOnly(allowed, title.trim().toLowerCase());
    if (!StringUtils.hasText(nameToSearch)) {
        nameToSearch = "listing";
    }//w ww. j a  va  2 s  .c  o  m

    // Break out any numbered extension: ex. name-5
    String originalExtension = null;
    int dotIndex = nameToSearch.lastIndexOf("-");
    if (dotIndex > -1 && dotIndex + 1 < nameToSearch.length()) {
        if (StringUtils.isNumber(nameToSearch.substring(dotIndex + 1))) {
            originalExtension = nameToSearch.substring(dotIndex);
            nameToSearch = nameToSearch.substring(0, dotIndex);
        }
    }

    // Convert spaces to - for url compliance and search engine readability
    nameToSearch = StringUtils.replace(nameToSearch, " ", "-");
    nameToSearch = StringUtils.replace(nameToSearch, "&", "and");
    nameToSearch = StringUtils.replace(nameToSearch, "/", "-");

    // See if there is a dupe in the database, and retrieve the latest value
    boolean originalExtensionExists = false;
    PreparedStatement pst = db.prepareStatement(
            "SELECT project_id, projecttextid " + "FROM projects " + "WHERE projecttextid LIKE ? ");
    pst.setString(1, nameToSearch + "%");
    ResultSet rs = pst.executeQuery();
    long value = 0;
    while (rs.next()) {
        long thisProjectId = rs.getLong("project_id");
        String thisTextId = rs.getString("projecttextid");
        // If it already owns this id, then keep it
        if (projectId > -1 && projectId == thisProjectId && nameToSearch.equals(thisTextId)) {
            return nameToSearch;
        }
        if (originalExtension != null) {
            if (thisTextId.equals(nameToSearch + originalExtension)) {
                originalExtensionExists = true;
            }
        }
        // Only compare to this name exactly, or this named iteration
        if (thisTextId.equals(nameToSearch)) {
            if (1 > value) {
                value = 1;
            }
        }

        if (thisTextId.startsWith(nameToSearch + "-")) {
            String foundExtensionValue = thisTextId.substring(thisTextId.lastIndexOf("-") + 1);
            if (StringUtils.isNumber(foundExtensionValue)) {
                try {
                    long thisValue = Long.parseLong(foundExtensionValue);
                    if (thisValue > value) {
                        value = thisValue;
                    }
                } catch (Exception e) {
                    // The extension is big... so add another extension
                    rs.close();
                    pst.close();
                    return generateUniqueId(nameToSearch + "-2", projectId, db);
                }
            }
        }
    }
    if (originalExtension != null && !originalExtensionExists) {
        return (nameToSearch + originalExtension);
    }
    // Set this one accordingly
    if (value == 0) {
        return nameToSearch;
    } else {
        ++value;
        return (nameToSearch + "-" + value);
    }
}

From source file:com.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the approved operators by application.
 *
 * @param applicationId the application id
 * @param operator the operator//from w  w w  . ja  v a2s.co m
 * @return the approved operators by application
 */
public static String getApprovedOperatorsByApplication(int applicationId, String operator) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    String sql = "SELECT opco.operatorname FROM " + ReportingTable.OPERATORAPPS + " opcoApp INNER JOIN "
            + ReportingTable.OPERATORS
            + " opco ON opcoApp.operatorid = opco.id WHERE opcoApp.isactive = 1 AND opcoApp.applicationid = ? AND opco.operatorname like ?";

    String approvedOperators = "";

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setInt(1, applicationId);

        if (operator.equals("__ALL__")) {
            ps.setString(2, "%");
        } else {
            ps.setString(2, operator);
        }

        log.debug("getApprovedOperatorsByApplication");
        rs = ps.executeQuery();
        while (rs.next()) {
            String temp = rs.getString("operatorname");
            approvedOperators = approvedOperators + ", " + temp;
        }
    } catch (Exception e) {
        log.error("Error occured while getting approved operators of application from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, rs);
    }
    if (approvedOperators == "") {
        approvedOperators = "NONE";
    } else {
        approvedOperators = approvedOperators.replaceFirst(",", "");
    }

    return approvedOperators;
}

From source file:com.sql.EMail.java

/**
 * Inserts email message into email table.
 *
 * @param eml EmailMessageModel//  www. j av a2s  . c o m
 * @return Integer - generated key of the email
 */
public static int InsertEmail(EmailMessageModel eml) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "INSERT INTO EMail (" + "section, " + "emailFrom, " + "emailTo, " + "emailSubject, "
                + "sentDate, " + "receivedDate, " + "emailCC, " + "emailBCC, " + "emailBody, "
                + "emailBodyFileName, " + "readyToFile " + ") VALUES (" + "?, " //1
                + "?, " //2
                + "?, " //3
                + "?, " //4
                + "?, " //5
                + "?, " //6
                + "?, " //7
                + "?, " //8
                + "?, " //9
                + "?, " //10
                + "0)"; // Ready to File False
        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, StringUtils.left(eml.getSection(), 4));
        ps.setString(2, StringUtils.left(eml.getEmailFrom(), 200));
        ps.setString(3, eml.getEmailTo());
        ps.setString(4, eml.getEmailSubject());
        ps.setTimestamp(5, eml.getSentDate());
        ps.setTimestamp(6, eml.getReceivedDate());
        ps.setString(7, eml.getEmailCC());
        ps.setString(8, eml.getEmailBCC());
        ps.setString(9, eml.getEmailBody());
        ps.setString(10, eml.getEmailBodyFileName());
        ps.executeUpdate();
        ResultSet newRow = ps.getGeneratedKeys();
        if (newRow.next()) {
            return newRow.getInt(1);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
    return 0;
}

From source file:com.stratelia.webactiv.util.DBUtil.java

private static int updateMaxFromTable(Connection connection, String tableName) throws SQLException {
    String table = tableName.toLowerCase(Locale.ROOT);
    int max = 0;//from w ww. j av a  2 s  .  c  o m
    PreparedStatement prepStmt = null;
    int count = 0;
    try {
        prepStmt = connection.prepareStatement("UPDATE UniqueId SET maxId = maxId + 1 WHERE tableName = ?");
        prepStmt.setString(1, table);
        count = prepStmt.executeUpdate();
        connection.commit();
    } catch (SQLException sqlex) {
        rollback(connection);
        throw sqlex;
    } finally {
        close(prepStmt);
    }

    if (count == 1) {
        PreparedStatement selectStmt = null;
        ResultSet rs = null;
        try {
            // l'update c'est bien passe, on recupere la valeur
            selectStmt = connection.prepareStatement("SELECT maxId FROM UniqueId WHERE tableName = ?");
            selectStmt.setString(1, table);
            rs = selectStmt.executeQuery();
            if (!rs.next()) {
                SilverTrace.error("util", "DBUtil.getNextId", "util.MSG_NO_RECORD_FOUND");
                throw new RuntimeException("Erreur Interne DBUtil.getNextId()");
            }
            max = rs.getInt(1);
        } finally {
            close(rs, selectStmt);
        }
        return max;
    }
    throw new SQLException("Update impossible : Ligne non existante");
}

From source file:com.keybox.manage.db.PublicKeyDB.java

/**
 * checks if key has already been registered under user's profile
 *
 * @param userId user id/* ww  w.j av  a2  s. co  m*/
 * @param publicKey public key 
 * @return true if duplicate
 */
public static boolean isKeyRegistered(Long userId, PublicKey publicKey) {
    boolean isDuplicate = false;
    PreparedStatement stmt;
    Connection con = null;
    try {
        con = DBUtils.getConn();

        stmt = con.prepareStatement(
                "select * from public_keys where user_id=? and fingerprint like ? and profile_id is ? and id is not ?");
        stmt.setLong(1, userId);
        stmt.setString(2, SSHUtil.getFingerprint(publicKey.getPublicKey()));
        if (publicKey.getProfile() != null && publicKey.getProfile().getId() != null) {
            stmt.setLong(3, publicKey.getProfile().getId());
        } else {
            stmt.setNull(3, Types.NULL);
        }
        if (publicKey.getId() != null) {
            stmt.setLong(4, publicKey.getId());
        } else {
            stmt.setNull(4, Types.NULL);
        }

        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            isDuplicate = true;
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    }

    DBUtils.closeConn(con);

    return isDuplicate;
}

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Read blacklist numbers./*from   ww w . j a v a2s .com*/
 *
 * @param apiId the api name
 * @return the list
 * @throws SQLException the SQL exception
 * @throws NamingException the naming exception
 * @deprecated
 */
@Deprecated
public static List<String> ReadBlacklistNumbers(String apiId) throws SQLException, NamingException {

    String sql = "select * from blacklistmsisdn where API_ID = ?";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = getStatsDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, apiId);

        rs = ps.executeQuery();

        if (rs != null) {
            while (rs.next()) {
                String msisdnTable = rs.getString("MSISDN").replace("tel3A+", "");
                log.debug("msisdn in the table = " + msisdnTable);
                msisdn.add(msisdnTable);

            }
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource..", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }

    return msisdn;

}

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Read whitelist numbers./*from   www.ja  v  a 2s .  com*/
 *
 * @param subscriptionID the subscription id
 * @return the list
 * @throws SQLException the SQL exception
 * @throws NamingException the naming exception
 */
public static List<String> ReadWhitelistNumbers(String subscriptionID) throws SQLException, NamingException {

    String sql = "select msisdn " + "from subscription_WhiteList where " + "subscriptionID=?;";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = getStatsDBConnection();
        ps = conn.prepareStatement(sql);

        ps.setString(1, subscriptionID);

        rs = ps.executeQuery();
        whitelistedmsisdn.clear();
        if (rs != null) {

            while (rs.next()) {
                String msisdnTable = rs.getString("msisdn").replace("tel3A+", "");
                log.info("msisdn in the table = " + msisdnTable);
                whitelistedmsisdn.add(msisdnTable);

            }
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource.", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }
    return whitelistedmsisdn;
}

From source file:com.flexive.core.security.FxDBAuthentication.java

/**
 * Increase the number of failed login attempts for the given user
 *
 * @param con    an open and valid connection
 * @param userId user id//from ww  w  .  j  a  va2  s .c  o m
 * @throws SQLException on errors
 */
private static void increaseFailedLoginAttempts(Connection con, long userId) throws SQLException {
    PreparedStatement ps = null;
    try {
        ps = con.prepareStatement(
                "UPDATE " + TBL_ACCOUNT_DETAILS + " SET FAILED_ATTEMPTS=FAILED_ATTEMPTS+1 WHERE ID=?");
        ps.setLong(1, userId);
        if (ps.executeUpdate() == 0) {
            ps.close();
            ps = con.prepareStatement("INSERT INTO " + TBL_ACCOUNT_DETAILS
                    + " (ID,APPLICATION,ISLOGGEDIN,LAST_LOGIN,LAST_LOGIN_FROM,FAILED_ATTEMPTS,AUTHSRC) "
                    + "VALUES (?,?,?,?,?,?,?)");
            ps.setLong(1, userId);
            ps.setString(2, FxContext.get().getApplicationId());
            ps.setBoolean(3, false);
            ps.setLong(4, System.currentTimeMillis());
            ps.setString(5, FxContext.get().getRemoteHost());
            ps.setLong(6, 1); //one failed attempt
            ps.setString(7, AuthenticationSource.Database.name());
            ps.executeUpdate();
        }
    } finally {
        if (ps != null)
            ps.close();
    }
}