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:com.keybox.manage.db.ProfileSystemsDB.java

/**
 * returns a list of systems for a given profile
 *
 * @param con       DB connection//  ww w . java2  s.co  m
 * @param profileId profile id
 * @return list of host systems
 */
public static List<HostSystem> getSystemsByProfile(Connection con, Long profileId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from  system s, system_map m where s.id=m.system_id and m.profile_id=? order by display_nm asc");
        stmt.setLong(1, profileId);
        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.setEnabled(rs.getBoolean("enabled"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return hostSystemList;
}

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

/**
 * checks to see if user is an admin based on auth token
 *
 * @param userId    user id/*from w  ww  . jav a2s. c o  m*/
 * @param authToken auth token string
 * @return user type if authorized, null if not authorized
 */
public static String isAuthorized(Long userId, String authToken) {

    String authorized = null;

    Connection con = null;
    if (authToken != null && !authToken.trim().equals("")) {

        try {
            con = DBUtils.getConn();
            PreparedStatement stmt = con
                    .prepareStatement("select * from users where enabled=true and id=? and auth_token=?");
            stmt.setLong(1, userId);
            stmt.setString(2, authToken);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                authorized = rs.getString("user_type");

            }
            DBUtils.closeRs(rs);

            DBUtils.closeStmt(stmt);

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

}

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

/**
 * insert new session record for user/*  ww w  . j  ava  2 s  .  co m*/
 *
 * @param con    DB connection
 * @param userId user id
 * @return session id
 */
public static Long createSessionLog(Connection con, Long userId) {
    Long sessionId = null;
    try {

        //insert
        PreparedStatement stmt = con.prepareStatement("insert into session_log (user_id) values(?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setLong(1, userId);
        stmt.execute();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            sessionId = rs.getLong(1);
        }

        DBUtils.closeStmt(stmt);

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

}

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

/**
 * returns terminal logs for user session for host system
 *
 * @param con       DB connection//w w  w  . j av a2  s . c o m
 * @param sessionId session id
 * @return session output for session
 */
public static List<HostSystem> getHostSystemsForSession(Connection con, Long sessionId) {

    List<HostSystem> hostSystemList = new ArrayList<>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct instance_id, system_id from terminal_log where session_id=?");
        stmt.setLong(1, sessionId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("system_id"));
            hostSystem.setInstanceId(rs.getInt("instance_id"));
            hostSystemList.add(hostSystem);
        }

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

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

}

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

/**
 * returns a list of system ids for a given profile
 *
 * @param con       DB con/*w w w  .j  a  v a  2s .  co m*/
 * @param profileId profile id
 * @param userId user id
 * @return list of host systems
 */
public static List<Long> getSystemIdsByProfile(Connection con, Long profileId, Long userId) {

    List<Long> systemIdList = new ArrayList<Long>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select sm.system_id from  system_map sm, user_map um where um.profile_id=sm.profile_id and sm.profile_id=? and um.user_id=?");
        stmt.setLong(1, profileId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            systemIdList.add(rs.getLong("system_id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        e.printStackTrace();
    }
    return systemIdList;
}

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 www. j a va2 s.co  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();
    }
}

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

/**
 * insert new terminal history for user//from w  ww .j  a  v  a2s  .c o  m
 *
 * @param con           DB connection
 * @param sessionOutput output from session terminals
 * @return session id
 */
public static void insertTerminalLog(Connection con, SessionOutput sessionOutput) {

    try {

        if (sessionOutput != null && sessionOutput.getSessionId() != null
                && sessionOutput.getInstanceId() != null && sessionOutput.getOutput() != null
                && !sessionOutput.getOutput().toString().equals("")) {
            //insert
            PreparedStatement stmt = con.prepareStatement(
                    "insert into terminal_log (session_id, instance_id, system_id, output) values(?,?,?,?)");
            stmt.setLong(1, sessionOutput.getSessionId());
            stmt.setLong(2, sessionOutput.getInstanceId());
            stmt.setLong(3, sessionOutput.getId());
            stmt.setString(4, sessionOutput.getOutput().toString());
            stmt.execute();
            DBUtils.closeStmt(stmt);
        }

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

}

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

/**
 * returns terminal logs for user session for host system
 *
 * @param sessionId     session id/*from  w ww  .j a va 2  s.  c o m*/
 * @param instanceId    instance id for terminal session
 * @return session output for session
 */
public static List<SessionOutput> getTerminalLogsForSession(Connection con, Long sessionId,
        Integer instanceId) {

    List<SessionOutput> outputList = new LinkedList<SessionOutput>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from terminal_log where instance_id=? and session_id=? order by log_tm asc");
        stmt.setLong(1, instanceId);
        stmt.setLong(2, sessionId);
        ResultSet rs = stmt.executeQuery();
        String output = "";
        while (rs.next()) {
            output = output + rs.getString("output");
        }

        output = output.replaceAll("\\u0007|\u001B\\[K|\\]0;|\\[\\d\\d;\\d\\dm|\\[\\dm", "");
        while (output.contains("\b")) {
            output = output.replaceFirst(".\b", "");
        }
        DBUtils.closeRs(rs);

        SessionOutput sessionOutput = new SessionOutput();
        sessionOutput.setSessionId(sessionId);
        sessionOutput.setInstanceId(instanceId);
        sessionOutput.getOutput().append(output);

        outputList.add(sessionOutput);

        DBUtils.closeRs(rs);

        DBUtils.closeStmt(stmt);

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

}

From source file:com.flexive.core.LifeCycleInfoImpl.java

/**
 * Store a complete lifecycle info/*from   ww w  .  j av  a  2s  . com*/
 *
 * @param ps                     prepared statement
 * @param creatorColumn          column index of the create user reference
 * @param creationTimeColumn     column index of the create timestamp
 * @param modificatorColumn      column index of the modified by user reference
 * @param modificationTimeColumn column index of the modified by timestamp
 * @throws java.sql.SQLException if a column could not be set
 */
public static void store(PreparedStatement ps, int creatorColumn, int creationTimeColumn, int modificatorColumn,
        int modificationTimeColumn) throws SQLException {
    final UserTicket ticket = FxContext.getUserTicket();
    final long ts = System.currentTimeMillis();
    ps.setLong(creatorColumn, ticket.getUserId());
    ps.setLong(creationTimeColumn, ts);
    ps.setLong(modificatorColumn, ticket.getUserId());
    ps.setLong(modificationTimeColumn, ts);
}

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

/**
 * returns terminal logs for user session for host system
 *
 * @param sessionId     session id//from   w  w  w  .  ja  va  2s .  c  o m
 * @param instanceId    instance id for terminal session
 * @return session output for session
 */
public static List<SessionOutput> getTerminalLogsForSession(Connection con, Long sessionId,
        Integer instanceId) {

    List<SessionOutput> outputList = new LinkedList<>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from terminal_log where instance_id=? and session_id=? order by log_tm asc");
        stmt.setLong(1, instanceId);
        stmt.setLong(2, sessionId);
        ResultSet rs = stmt.executeQuery();
        StringBuilder outputBuilder = new StringBuilder("");
        while (rs.next()) {
            outputBuilder.append(rs.getString("output"));
        }

        String output = outputBuilder.toString();
        output = output.replaceAll("\\u0007|\u001B\\[K|\\]0;|\\[\\d\\d;\\d\\dm|\\[\\dm", "");
        while (output.contains("\b")) {
            output = output.replaceFirst(".\b", "");
        }
        DBUtils.closeRs(rs);

        SessionOutput sessionOutput = new SessionOutput();
        sessionOutput.setSessionId(sessionId);
        sessionOutput.setInstanceId(instanceId);
        sessionOutput.getOutput().append(output);

        outputList.add(sessionOutput);

        DBUtils.closeRs(rs);

        DBUtils.closeStmt(stmt);

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

}