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.tethrnet.manage.db.SystemDB.java

/**
 * method to do order by based on the sorted set object for systems for user
 *
 * @param sortedSet sorted set object/* ww w.  ja  v  a  2  s. c om*/
 * @param userId    user id
 * @return sortedSet with list of host systems
 */
public static SortedSet getUserSystemSet(SortedSet sortedSet, Long userId) {
    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 where id in (select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=? ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and um.profile_id=? "
            : "";
    sql += ") " + orderBy;

    //get user for auth token
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        //filter by profile id if exists
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(2, 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.SystemDB.java

/**
 * deletes host system//from ww w  . jav a2s  .  c o m
 *
 * @param hostSystemId host system id
 */
public static void deleteSystem(Long hostSystemId) {

    Connection con = null;

    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("delete from system where id=?");
        stmt.setLong(1, hostSystemId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

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

/**
 * method to do order by based on the sorted set object for systems for user
 *
 * @param sortedSet sorted set object/*from w  w  w .  j a v a2 s .  c om*/
 * @param userId    user id
 * @return sortedSet with list of host systems
 */
public static SortedSet getUserSystemSet(SortedSet sortedSet, Long userId) {
    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 where id in (select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=? ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and um.profile_id=? "
            : "";
    sql += ") " + orderBy;

    //get user for auth token
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        //filter by profile id if exists
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(2, 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.tethrnet.manage.db.SystemDB.java

/**
 * returns system by id/*from w  ww  . j a  v  a2s.  c  o  m*/
 *
 * @param con DB connection
 * @param id  system id
 * @return system
 */
public static HostSystem getSystem(Connection con, Long id) {

    HostSystem hostSystem = null;

    try {

        PreparedStatement stmt = con.prepareStatement("select * from  system where id=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            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"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return hostSystem;
}

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

/**
 * returns system by id/*from   w ww  .  ja  va 2 s.  c  o  m*/
 *
 * @param con DB connection
 * @param id  system id
 * @return system
 */
public static HostSystem getSystem(Connection con, Long id) {

    HostSystem hostSystem = null;

    try {

        PreparedStatement stmt = con.prepareStatement("select * from  system where id=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            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));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return hostSystem;
}

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

/**
 * checks to see if username is unique while ignoring current user
 *
 * @param userId user id/* w w w  .  ja v  a 2s .  c o  m*/
 * @param username username
 * @return true false indicator
 */
public static boolean isUnique(Long userId, String username) {

    boolean isUnique = true;
    if (userId == null) {
        userId = -99L;
    }

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from users where enabled=true and lower(username) like lower(?) and id != ?");
        stmt.setString(1, username);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            isUnique = false;
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    }
    DBUtils.closeConn(con);

    return isUnique;

}

From source file:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static boolean submitScore(DBManager dbManager, String key, long userId, long score) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*from   w  w  w  .  j  a  v a  2  s . c  o  m*/
    String sql = null;

    sql = "INSERT INTO rouge_leaderboard_score (`leaderboard_key`, `user_id`, `score`) "
            + "VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE score = GREATEST(?, score);";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);
        stmt.setLong(2, userId);
        stmt.setLong(3, score);
        stmt.setLong(4, score);

        int ret = stmt.executeUpdate();

        return (ret > 0);

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

    } finally {

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

From source file:com.predic8.membrane.core.interceptor.statistics.util.JDBCUtil.java

public static void setData(AbstractExchange exc, PreparedStatement prepSt, boolean idGenerated, int flag,
        String tId, String[] gatewayHCIDs) throws SQLException {
    int startIndex = 0;
    if (!idGenerated) {
        UUID id = UUID.randomUUID();
        prepSt.setLong(++startIndex, id.getLeastSignificantBits());
    }//from w  w w.  j a v  a 2 s .  c  o m
    boolean isReq = (exc.getResponse() == null);

    log.info("Handling interceptor id is: " + tId);
    log.info((isReq) ? "logging for request" : "logging for response");

    prepSt.setInt(++startIndex, (isReq) ? 200 : exc.getResponse().getStatusCode());
    prepSt.setString(++startIndex, (flag == 0) ? "REQUEST" : "RESPONSE");
    prepSt.setTimestamp(++startIndex, new Timestamp(ExchangesUtil.getDate(exc).getTime()));//skb
    prepSt.setString(++startIndex, exc.getRule().toString());
    prepSt.setString(++startIndex, exc.getRequest().getMethod());
    prepSt.setString(++startIndex, exc.getRequest().getUri());
    prepSt.setString(++startIndex,
            (gatewayHCIDs != null && !"".equals(gatewayHCIDs[2])) ? gatewayHCIDs[2] : exc.getSourceHostname());
    prepSt.setString(++startIndex,
            (gatewayHCIDs != null && !"".equals(gatewayHCIDs[3])) ? gatewayHCIDs[3] : exc.getServer());

    if (gatewayHCIDs != null) {
        prepSt.setString(++startIndex, gatewayHCIDs[0]);
        prepSt.setString(++startIndex, gatewayHCIDs[1]);
    } else {
        prepSt.setString(++startIndex, exc.getSourceHostname());
        prepSt.setString(++startIndex, exc.getServer());
    }

    prepSt.setString(++startIndex, (isReq) ? exc.getRequestContentType() : exc.getResponseContentType());
    prepSt.setInt(++startIndex, (isReq) ? exc.getRequestContentLength() : exc.getResponseContentLength());
    /*
    prepSt.setString(++ startIndex, (isReq)?null:exc.getResponseContentType());
    prepSt.setInt(++ startIndex, (isReq)?null:exc.getResponseContentLength());
    */
    prepSt.setLong(++startIndex, (isReq) ? 0 : (exc.getTimeResReceived() - exc.getTimeReqSent()));

    prepSt.setString(++startIndex, (String) getExProperty(exc, FileExchangeStore.MESSAGE_FILE_PATH));

    /* skb */
    String[] colList = { JDBCUtil.MSG_HEADER, JDBCUtil.MSG };

    if (isReq) {
        for (String col : colList) {
            log.info("processing col:" + col);

            ++startIndex;
            try {
                byte[] os = (byte[]) getExProperty(exc, col);
                if (os != null) {
                    prepSt.setBytes(startIndex, os);
                } else
                    prepSt.setBytes(startIndex, null);

            } catch (Exception ex) {
                prepSt.setBytes(startIndex, null);
            }
        }
    } else {

        for (String col : colList) {
            log.info("processing col:" + col);

            ++startIndex;
            try {
                byte[] os = null;
                if (col.equals(JDBCUtil.MSG)) {
                    try {
                        os = IOUtils.toByteArray((exc.getResponse().getBodyAsStream()));
                    } catch (Exception ex) {
                        log.info(ex.toString());
                    }
                } else if (col.equals(JDBCUtil.MSG_HEADER)) {
                    Message msg2 = exc.getResponse();

                    ByteArrayOutputStream header2 = new ByteArrayOutputStream();

                    msg2.writeStartLine(header2);
                    msg2.getHeader().write(header2);
                    header2.write((Constants.CRLF).getBytes());

                    os = header2.toByteArray();
                }
                if (os != null) {
                    prepSt.setBytes(startIndex, os);
                } else
                    prepSt.setBytes(startIndex, null);

            } catch (Exception ex) {
                prepSt.setBytes(startIndex, null);
            }
        }

    }

}

From source file:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static boolean updateAchievement(DBManager dbManager, String key, long userId, double progress) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from w  w w .  j  av  a 2s.  c o  m
    String sql = null;

    sql = "INSERT INTO rouge_achievement_progress (`achievement_key`, `user_id`, `progress`) "
            + "VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE progress = GREATEST(progress, ?)";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);
        stmt.setLong(2, userId);
        stmt.setDouble(3, progress);
        stmt.setDouble(4, progress);

        int ret = stmt.executeUpdate();

        return (ret > 0);

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

    } finally {

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

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

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

    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;

}