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:com.magnet.mmx.server.plugin.mmxmgmt.db.TopicItemDAOImplTest.java

@AfterClass
public static void cleanupDatabase() {
    final String unboundStr = "DELETE FROM ofPubsubItem where serviceID = ? AND nodeID = ?";
    Connection conn = null;/* www  .j a v a 2s .  c o m*/
    PreparedStatement pstmt = null;
    try {
        conn = UnitTestDSProvider.getDataSource().getConnection();
        pstmt = conn.prepareStatement(unboundStr);
        pstmt.setString(1, SERVICE_ID);
        pstmt.setString(2, NODE_ID);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        LOGGER.error("cleanupDatabase : caught exception cleaning ofPubsubItem");
    } finally {
        CloseUtil.close(LOGGER, pstmt, conn);
    }
}

From source file:com.tethrnet.manage.db.SystemStatusDB.java

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id//ww  w.  jav a2  s. co  m
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}

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

/**
 * Process the create account request. This should receive the following
 * objects: the username, the password, the device ID and the public key
 * for the device. This adds a new entry in the account database, and
 * creates a new device./*from   w  ww.  j  a v  a 2  s  .  co  m*/
 * 
 * If the user account cannot be created, this returns nil.
 * @param requestParams
 * @return
 */
public static UserInfo processRequest(JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String username = requestParams.optString("username");
    String password = requestParams.optString("password");
    String deviceid = requestParams.optString("deviceid");
    String pubkey = requestParams.optString("pubkey");

    /*
     * Attempt to insert a new user into the database
     */

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

    try {
        c = Database.get();
        ps = c.prepareStatement("INSERT INTO Users " + "    ( username, password ) " + "VALUES "
                + "    ( ?, ? ); SELECT currval('Users_userid_seq')");
        ps.setString(1, username);
        ps.setString(2, password);

        try {
            ps.execute();
        } catch (SQLException ex) {
            return null; // Can't insert; duplicate username?
        }
        int utc = ps.getUpdateCount();
        int userid = 0;
        if ((utc == 1) && ps.getMoreResults()) {
            rs = ps.getResultSet();
            if (rs.next()) {
                userid = rs.getInt(1);
            }
            rs.close();
            rs = null;
        }

        ps.close();
        ps = null;

        /*
         * We now have the user index. Insert the device. Note that it is
         * highly unlikely we will have a UUID collision, but we verify
         * we don't by deleting any rows in the device table with the
         * specified UUID. The worse case scenario is a collision which
         * knocks someone else off the air. (The alternative would be
         * to accidentally send the wrong person duplicate messages.)
         * 
         * Note that we don't actually use a device-identifying identifer,
         * choosing instead to pick a UUID, so we need to deal with
         * the possibility (however remote) of duplicate UUIDs.
         * 
         * In the off chance we did have a collision, we also delete all
         * old messages to the device; that prevents messages from being
         * accidentally delivered.
         */

        ps = c.prepareStatement("DELETE FROM Messages " + "WHERE messageid IN "
                + "    (SELECT Messages.messageid " + "     FROM Messages, Devices "
                + "     WHERE Messages.deviceid = Devices.deviceid " + "     AND Devices.deviceuuid = ?)");
        ps.setString(1, deviceid);
        ps.execute();
        ps.close();
        ps = null;

        ps = c.prepareStatement("DELETE FROM Devices WHERE deviceuuid = ?");
        ps.setString(1, deviceid);
        ps.execute();
        ps.close();
        ps = null;

        ps = c.prepareStatement("INSERT INTO Devices " + "    ( userid, deviceuuid, publickey ) " + "VALUES "
                + "    ( ?, ?, ?)");
        ps.setInt(1, userid);
        ps.setString(2, deviceid);
        ps.setString(3, pubkey);
        ps.execute();

        /*
         * Complete; return the user info record
         */

        return new Login.UserInfo(userid);
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

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

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id/*w ww  .ja v a 2  s . c om*/
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}

From source file:org.red5.server.plugin.admin.dao.UserDAO.java

public static boolean addUser(String username, String hashedPassword) {
    boolean result = false;

    Connection conn = null;/*ww  w  . j av  a  2s  .c o m*/
    PreparedStatement stmt = null;
    try {
        // JDBC stuff
        DataSource ds = UserDatabase.getDataSource();

        conn = ds.getConnection();
        //make a statement
        stmt = conn
                .prepareStatement("INSERT INTO APPUSER (username, password, enabled) VALUES (?, ?, 'enabled')");
        stmt.setString(1, username);
        stmt.setString(2, hashedPassword);
        log.debug("Add user: {}", stmt.execute());
        //add role
        stmt = conn.prepareStatement("INSERT INTO APPROLE (username, authority) VALUES (?, 'ROLE_SUPERVISOR')");
        stmt.setString(1, username);
        log.debug("Add role: {}", stmt.execute());
        //
        result = true;
    } catch (Exception e) {
        log.error("Error connecting to db", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
    return result;
}

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

public static ReturnResult processRequest(Login.UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String deviceid = requestParams.optString("deviceid");
    MessageReturnResult mrr = new MessageReturnResult();

    /*/* w  w  w  .  ja  v  a  2  s.  c  om*/
     * Save message to the database.
     */

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

    try {
        /*
         * Get the device ID for this device. Verify it belongs to the
         * user specified
         */
        c = Database.get();
        ps = c.prepareStatement("SELECT deviceid " + "FROM Devices " + "WHERE deviceuuid = ? AND userid = ?");
        ps.setString(1, deviceid);
        ps.setInt(2, userinfo.getUserID());
        rs = ps.executeQuery();

        int deviceID = 0;
        if (rs.next()) {
            deviceID = rs.getInt(1);
        }

        rs.close();
        ps.close();
        if (deviceID == 0) {
            return new ReturnResult(Errors.ERROR_UNKNOWNDEVICE, "Unknown device");
        }

        /*
         * Run query to get messages
         */

        ps = c.prepareStatement("SELECT Messages.messageid, " + "    Messages.senderid, "
                + "    Users.username, " + "    Messages.toflag, " + "    Messages.received, "
                + "    Messages.message " + "FROM Messages, Users " + "WHERE Messages.deviceid = ? "
                + "  AND Messages.senderid = Users.userid");
        ps.setInt(1, deviceID);

        rs = ps.executeQuery();
        while (rs.next()) {
            int messageID = rs.getInt(1);
            int senderID = rs.getInt(2);
            String senderName = rs.getString(3);
            boolean toflag = rs.getBoolean(4);
            Timestamp received = rs.getTimestamp(5);
            byte[] message = rs.getBytes(6);

            mrr.addMessage(messageID, senderID, senderName, toflag, received, message);
        }

        /*
         * Return messages
         */
        return mrr;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.wso2telco.core.mnc.resolver.mncrange.McnRangeDbUtil.java

/**
 * Gets the mcc number ranges./*from  w w  w  . j  a  v  a  2 s .co  m*/
 *
 * @param mcc the mcc
 * @return the mcc number ranges
 * @throws MobileNtException the mobile nt exception
 */
public static List<NumberRange> getMccNumberRanges(String mcc) throws MobileNtException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "SELECT mnccode,brand,rangefrom,rangeto " + "FROM mcc_number_ranges " + "WHERE mcccode = ?";

    List<NumberRange> lstranges = new ArrayList();

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        rs = ps.executeQuery();
        while (rs.next()) {
            lstranges.add(new NumberRange(rs.getLong("rangefrom"), rs.getLong("rangeto"),
                    rs.getString("mnccode"), rs.getString("brand")));
        }
    } catch (Exception e) {
        handleException("Error occured while getting Number ranges for mcc: " + mcc + " from the database", e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, rs);
    }
    return lstranges;
}

From source file:com.wso2telco.mnc.resolver.mncrange.McnRangeDbUtil.java

public static String getMncBrand(String mcc, String mnc) throws MobileNtException {
    Connection conn = null;/*from www .  ja  v a  2s.c  om*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "SELECT operatorname " + "FROM operators " + "WHERE mcc = ? AND mnc = ?";

    String mncBrand = null;

    try {
        conn = getAxiataDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        ps.setString(2, mnc);
        rs = ps.executeQuery();
        if (rs.next()) {
            mncBrand = rs.getString("operatorname");
        }
    } catch (SQLException e) {
        handleException("Error occured while getting Brand for for mcc: and mnc: " + mcc + ":" + mnc
                + " from the database", e);
    } finally {
        McnRangeDbUtil.closeAllConnections(ps, conn, rs);
    }
    return mncBrand;
}

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

/**
 * get salt by user name/*from www.j a v  a 2  s.  c o  m*/
 *
 * @param con      DB connection
 * @param username username
 * @return salt
 */
private static String getSaltByUsername(Connection con, String username) {

    String salt = "";
    try {
        PreparedStatement stmt = con
                .prepareStatement("select salt from users where enabled=true and username=?");
        stmt.setString(1, username);
        ResultSet rs = stmt.executeQuery();
        if (rs.next() && rs.getString("salt") != null) {
            salt = rs.getString("salt");
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return salt;
}

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

/**
 * get salt by authentication token/*  w  ww .java  2 s .co m*/
 *
 * @param con       DB connection
 * @param authToken auth token
 * @return salt
 */
private static String getSaltByAuthToken(Connection con, String authToken) {

    String salt = "";
    try {
        PreparedStatement stmt = con
                .prepareStatement("select salt from users where enabled=true and auth_token=?");
        stmt.setString(1, authToken);
        ResultSet rs = stmt.executeQuery();
        if (rs.next() && rs.getString("salt") != null) {
            salt = rs.getString("salt");
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return salt;
}