Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:Main.java

public static PreparedStatement createFieldsInsert(Connection conn, int layerId, String name,
        String description, String fieldId, String fieldType, String sid, String sname, String sdesc,
        boolean indb, boolean enabled, boolean namesearch, boolean defaultlayer, boolean intersect,
        boolean layerbranch, boolean analysis, boolean addToMap) throws SQLException {
    // TOOD slightly different statement if sdesc is null...

    PreparedStatement stFieldsInsert = conn.prepareStatement(
            "INSERT INTO fields (name, id, \"desc\", type, spid, sid, sname, sdesc, indb, enabled, last_update, namesearch, defaultlayer, \"intersect\", layerbranch, analysis, addtomap)"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
    stFieldsInsert.setString(1, name);//w w w .  j  a  va2  s. c o m
    stFieldsInsert.setString(2, fieldId);
    stFieldsInsert.setString(3, description);
    stFieldsInsert.setString(4, fieldType);
    stFieldsInsert.setString(5, Integer.toString(layerId));
    stFieldsInsert.setString(6, sid);
    stFieldsInsert.setString(7, sname);

    if (sdesc == null || sdesc.isEmpty()) {
        stFieldsInsert.setNull(8, Types.VARCHAR);
    } else {
        stFieldsInsert.setString(8, sdesc);
    }

    stFieldsInsert.setBoolean(9, indb);
    stFieldsInsert.setBoolean(10, enabled);
    stFieldsInsert.setTimestamp(11, new Timestamp(System.currentTimeMillis()));
    stFieldsInsert.setBoolean(12, namesearch);
    stFieldsInsert.setBoolean(13, defaultlayer);
    stFieldsInsert.setBoolean(14, intersect);
    stFieldsInsert.setBoolean(15, layerbranch);
    stFieldsInsert.setBoolean(16, analysis);
    stFieldsInsert.setBoolean(17, addToMap);

    return stFieldsInsert;
}

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

/**
 * get salt by user name/*from  w w w .  j av  a  2  s  .  c  om*/
 *
 * @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/*from ww  w . j a  v  a  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;
}

From source file:com.concursive.connect.web.modules.communications.utils.EmailUpdatesUtils.java

public static void manageQueue(Connection db, TeamMember teamMember) throws SQLException {
    //Determine if the member is part of any other projects and has a matching email updates preference
    PreparedStatement pst = db.prepareStatement("SELECT count(*) AS record_count " + "FROM project_team pt "
            + "WHERE pt.user_id = ? " + "AND pt.email_updates_schedule = ? ");
    int i = 0;/*from w ww  . ja v  a  2s  .  c o  m*/
    pst.setInt(++i, teamMember.getUserId());
    pst.setInt(++i, teamMember.getEmailUpdatesSchedule());
    ResultSet rs = pst.executeQuery();
    int records = 0;
    if (rs.next()) {
        records = rs.getInt("record_count");
    }
    rs.close();
    pst.close();

    if (records == 0) {
        //Delete the queue since it is no longer needed.
        String field = "";
        int emailUpdatesSchedule = teamMember.getEmailUpdatesSchedule();
        if (emailUpdatesSchedule > 0) {
            if (emailUpdatesSchedule == TeamMember.EMAIL_OFTEN) {
                field = "schedule_often";
            } else if (emailUpdatesSchedule == TeamMember.EMAIL_DAILY) {
                field = "schedule_daily";
            } else if (emailUpdatesSchedule == TeamMember.EMAIL_WEEKLY) {
                field = "schedule_weekly";
            } else if (emailUpdatesSchedule == TeamMember.EMAIL_MONTHLY) {
                field = "schedule_monthly";
            }
            i = 0;
            pst = db.prepareStatement(
                    "DELETE FROM email_updates_queue " + "WHERE enteredby = ? AND " + field + " = ? ");
            pst.setInt(++i, teamMember.getUserId());
            pst.setBoolean(++i, true);
            pst.executeUpdate();
            pst.close();
        }
    }
}

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

/**
 * returns the shared secret based on user id
 *
 * @param userId user id//from  www  .j  av a  2  s.  com
 * @return auth object
 */
public static String getSharedSecret(Long userId) {

    String sharedSecret = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from users where id like ?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            sharedSecret = EncryptionUtil.decrypt(rs.getString("otp_secret"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return sharedSecret;

}

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

/**
 * returns user base on username//from w w w . j  a  v a2s  .c o  m
 *
 * @param con DB connection
 * @param uid username id
 * @return user object
 */
public static User getUserByUID(Connection con, String uid) {

    User user = null;
    try {
        PreparedStatement stmt = con
                .prepareStatement("select * from  users where lower(username) like lower(?) and enabled=true");
        stmt.setString(1, uid);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setUserType(rs.getString("user_type"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, user.getId()));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

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

/**
 * deletes user//from w  w w  .j  a va2  s. c  o m
 * @param userId user id
 */
public static void disableUser(Long userId) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("update users set enabled=false where id=?");
        stmt.setLong(1, userId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

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

/**
 * resets shared secret for user//from w  ww .  jav  a 2s  . c o  m
 * @param userId user id
 */
public static void resetSharedSecret(Long userId) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("update users set otp_secret=null where id=?");
        stmt.setLong(1, userId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

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

/**
 * returns user base on username/*from w  w  w.  ja  v a  2  s  .  c  om*/
 *
 * @param con DB connection
 * @param uid username id
 * @return user object
 */
public static User getUserByUID(Connection con, String uid) {

    User user = null;
    try {
        PreparedStatement stmt = con
                .prepareStatement("select * from  users where lower(username) like lower(?) and enabled=true");
        stmt.setString(1, uid);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString("first_nm"));
            user.setLastNm(rs.getString("last_nm"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setUserType(rs.getString("user_type"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, user.getId()));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

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

/**
 * returns user id based on auth token//from ww  w  . ja v a  2s. c om
 *
 * @param authToken auth token
 * @param con       DB connection
 * @return user
 */
public static User getUserByAuthToken(Connection con, String authToken) {

    User user = null;
    try {
        PreparedStatement stmt = con
                .prepareStatement("select * from users where enabled=true and auth_token like ?");
        stmt.setString(1, authToken);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            Long userId = rs.getLong("id");

            user = UserDB.getUser(con, userId);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;

}