Example usage for java.sql PreparedStatement setLong

List of usage examples for java.sql PreparedStatement setLong

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

Introduction

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

Prototype

void setLong(int parameterIndex, long x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java long value.

Usage

From source file:at.becast.youploader.database.SQLite.java

public static Boolean updateUploadProgress(int id, long progress) {
    PreparedStatement prest = null;
    String sql = "UPDATE `uploads` SET `uploaded`=? WHERE `id`=?";
    try {//from  w w  w. ja  v  a 2  s  .  c  o m
        prest = c.prepareStatement(sql);
        prest.setLong(1, progress);
        prest.setInt(2, id);
        boolean res = prest.execute();
        prest.close();
        return res;
    } catch (SQLException e) {
        LOG.error("Error updating upload progress", e);
        return false;
    }
}

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

/**
 * returns sessions based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @return session list// w w w . j  a  v a  2  s.  com
 */
public static SortedSet getSessions(SortedSet sortedSet) {
    //get db connection
    Connection con = null;
    List<SessionAudit> outputList = new LinkedList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }

    String sql = "select * from session_log, users where users.id= session_log.user_id ";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))
            ? " and session_log.user_id=? "
            : "";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM_ID))
            ? " and session_log.id in ( select session_id from terminal_log where terminal_log.system_id=? ) "
            : "";
    sql += orderBy;

    try {

        con = DBUtils.getConn();
        deleteAuditHistory(con);

        PreparedStatement stmt = con.prepareStatement(sql);
        int i = 1;
        //set filters in prepared statement
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))) {
            stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_USER_ID)));
        }
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM_ID))) {
            stmt.setLong(i, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM_ID)));
        }

        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            SessionAudit sessionAudit = new SessionAudit();
            sessionAudit.setId(rs.getLong("session_log.id"));
            sessionAudit.setSessionTm(rs.getTimestamp(SESSION_TM));
            sessionAudit.setUser(UserDB.getUser(con, rs.getLong(USER_ID)));
            outputList.add(sessionAudit);

        }

        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(outputList);

    return sortedSet;

}

From source file:ca.qc.adinfo.rouge.mail.db.MailDb.java

public static Collection<Mail> getMails(DBManager dbManager, long userId, boolean unreadOnly) {

    Collection<Mail> mailbox = new ArrayList<Mail>();

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*www . jav  a2s .co m*/

    String sql = "SELECT `id`, `from`, `to`, `content`, `status`, `time_sent` "
            + "FROM rouge_mail WHERE `to` = ? ";

    if (unreadOnly) {
        sql += " AND `status` = 'unr'";
    } else {
        sql += " AND NOT `status` = 'del'";
    }

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);
        stmt.setLong(1, userId);

        rs = stmt.executeQuery();

        while (rs.next()) {
            mailbox.add(new Mail(rs.getLong("id"), rs.getLong("from"), rs.getLong("to"),
                    new RougeObject(JSONObject.fromObject(rs.getString("content")))));
        }

        return mailbox;

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return null;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

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

/**
 * updates existing user/*w w  w.  j a  v a2  s.co m*/
 * @param user user object
 */
public static void updateUserNoCredentials(User user) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con
                .prepareStatement("update users set  email=?, username=?, user_type=? where id=?");
        stmt.setString(1, user.getEmail());
        stmt.setString(2, user.getUsername());
        stmt.setString(3, user.getUserType());
        stmt.setLong(4, user.getId());
        stmt.execute();
        DBUtils.closeStmt(stmt);
        if (User.ADMINISTRATOR.equals(user.getUserType())) {
            PublicKeyDB.deleteUnassignedKeysByUser(con, user.getId());
        }

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

}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object/*from www .  j a  va  2  s.c om*/
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  system s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}

From source file:dsd.dao.DAOProvider.java

/**
 * calls the correct method for setting the command parameter depending on
 * parameter type//from   w ww.j  a  v  a2s. c  o m
 * 
 * @param command
 * @param object
 * @param parameterIndex
 * @throws SQLException
 */
private static void SetParameter(PreparedStatement command, Object object, int parameterIndex)
        throws SQLException {
    if (object instanceof Timestamp) {
        command.setTimestamp(parameterIndex, (Timestamp) object);
    } else if (object instanceof String) {
        command.setString(parameterIndex, (String) object);
    } else if (object instanceof Long) {
        command.setLong(parameterIndex, (Long) object);
    } else if (object instanceof Integer) {
        command.setInt(parameterIndex, (Integer) object);
    } else if (object instanceof Boolean) {
        command.setBoolean(parameterIndex, (Boolean) object);
    } else if (object instanceof Float) {
        command.setFloat(parameterIndex, (Float) object);
    } else {
        throw new IllegalArgumentException(
                "type needs to be inserted in Set parameter method of DAOProvider class");
    }

}

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

/**
 * returns public keys based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @param userId user id/*from  www .  j  a va  2s. c  om*/
 * @return sorted script list
 */
public static SortedSet getPublicKeySet(SortedSet sortedSet, Long userId) {

    ArrayList<PublicKey> publicKeysList = new ArrayList<PublicKey>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from public_keys where user_id = ? and enabled=true " + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            PublicKey publicKey = new PublicKey();
            publicKey.setId(rs.getLong("id"));
            publicKey.setKeyNm(rs.getString("key_nm"));
            publicKey.setPublicKey(rs.getString("public_key"));
            publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
            publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
            publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
            publicKey.setCreateDt(rs.getTimestamp("create_dt"));
            publicKeysList.add(publicKey);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(publicKeysList);
    return sortedSet;
}

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

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id//from w w w .  j av  a 2  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.keybox.manage.db.SystemDB.java

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object/*from w ww  .  j  a va2s .  co  m*/
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    List<HostSystem> hostSystemList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  system s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}

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  .  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;

}