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:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static HashMap<String, Achievement> getAchievements(DBManager dbManager, long userId) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//  w w  w.j  a  v a 2s  .  c o  m
    HashMap<String, Achievement> returnValue = new HashMap<String, Achievement>();

    String sql = "SELECT ach.`key` as `key`, ach.`name` as name, " + "ach.point_value as point_value, "
            + "prg.progress as progress, ach.total as total "
            + "FROM rouge_achievement_progress as prg, rouge_achievements as ach "
            + "WHERE ach.key = prg.achievement_key and prg.user_id = ?; ";

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

        rs = stmt.executeQuery();

        while (rs.next()) {

            String key = rs.getString("key");

            Achievement achievement = new Achievement(key, rs.getString("name"), rs.getInt("point_value"),
                    rs.getDouble("total"), rs.getDouble("progress"));

            returnValue.put(key, achievement);
        }

        return returnValue;

    } 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.PublicKeyDB.java

public static List<String> getPublicKeysForSystem(Connection con, Long systemId) {
    List<String> publicKeyList = new ArrayList<String>();

    if (systemId == null) {
        systemId = -99L;// w ww.jav a 2 s  .  c  o  m
    }
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from public_keys where (profile_id is null or profile_id in (select profile_id from system_map where system_id=?)) and enabled=true");
        stmt.setLong(1, systemId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            publicKeyList.add(rs.getString("public_key"));
        }
        DBUtils.closeStmt(stmt);

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

    return publicKeyList;

}

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

public static List<String> getPublicKeysForSystem(Connection con, Long systemId) {
    List<String> publicKeyList = new ArrayList<>();

    if (systemId == null) {
        systemId = -99L;//from w  w w  .  j  a v a2  s  .c om
    }
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from public_keys where (profile_id is null or profile_id in (select profile_id from system_map where system_id=?)) and enabled=true");
        stmt.setLong(1, systemId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            publicKeyList.add(rs.getString(PUBLIC_KEY));
        }
        DBUtils.closeStmt(stmt);

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

    return publicKeyList;

}

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

/**
 * Update a tables LifeCycleInfo/*from  ww w  . j a va  2 s .c  om*/
 *
 * @param table         table that contains the lifecycle
 * @param idField       field containing the id
 * @param id            the id to update
 * @param verField      field containing the id (optional)
 * @param ver           the version to update (optional)
 * @param updateCreated update created by/at as well?
 * @param throwOnNone   throw an exception if no rows were updated?
 * @throws FxUpdateException if a database field could not be updated
 */
public static void updateLifeCycleInfo(String table, String idField, String verField, long id, int ver,
        boolean updateCreated, boolean throwOnNone) throws FxUpdateException {
    final UserTicket ticket = FxContext.getUserTicket();

    Connection con = null;
    PreparedStatement stmt = null;

    try {
        con = Database.getDbConnection();
        stmt = con.prepareStatement("UPDATE " + table + " SET MODIFIED_BY=?, MODIFIED_AT=?"
                + (updateCreated ? ", CREATED_BY=?, CREATED_AT=?" : "") + " WHERE " + idField + "=?"
                + (verField != null && ver > 0 ? " AND " + verField + "=?" : ""));
        final long now = System.currentTimeMillis();
        stmt.setInt(1, (int) ticket.getUserId());
        stmt.setLong(2, now);
        if (updateCreated) {
            stmt.setInt(3, (int) ticket.getUserId());
            stmt.setLong(4, now);
            stmt.setLong(5, id);
        } else
            stmt.setLong(3, id);
        if (verField != null && ver > 0)
            stmt.setInt((updateCreated ? 6 : 4), ver);
        int iCnt = stmt.executeUpdate();
        if (iCnt != 1 && throwOnNone)
            throw new FxUpdateException("Updating LifeCycleInfo failed. " + iCnt + " rows were updated!");
    } catch (SQLException se) {
        throw new FxUpdateException(LOG, se.getMessage(), se);
    } finally {
        Database.closeObjects(LifeCycleInfoImpl.class, con, stmt);
    }
}

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

/**
 * returns public keys based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @return sorted script list//from   w w  w.j a va 2s .  co m
 */
public static SortedSet getPublicKeySet(SortedSet sortedSet) {

    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 p.*, u.username from public_keys p, users u where u.id=p.user_id  ";

    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID)) ? " and p.user_id=? " : "";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and p.profile_id=? "
            : "";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED)) ? " and p.enabled=? "
            : " and p.enabled=true";
    sql = sql + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        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_PROFILE_ID))) {
            stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED))) {
            stmt.setBoolean(i++, Boolean.valueOf(sortedSet.getFilterMap().get(FILTER_BY_ENABLED)));
        }
        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"));
            publicKey.setUsername(rs.getString("username"));
            publicKey.setUserId(rs.getLong("user_id"));
            publicKey.setEnabled(rs.getBoolean("enabled"));
            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.keybox.manage.db.PublicKeyDB.java

/**
 * returns public keys based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @return sorted script list/*  w ww . j a v a  2  s . co m*/
 */
public static SortedSet getPublicKeySet(SortedSet sortedSet) {

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

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select p.*, u.username from public_keys p, users u where u.id=p.user_id  ";

    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID)) ? " and p.user_id=? " : "";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and p.profile_id=? "
            : "";
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED)) ? " and p.enabled=? "
            : " and p.enabled=true";
    sql = sql + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        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_PROFILE_ID))) {
            stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED))) {
            stmt.setBoolean(i, Boolean.valueOf(sortedSet.getFilterMap().get(FILTER_BY_ENABLED)));
        }
        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));
            publicKey.setUsername(rs.getString("username"));
            publicKey.setUserId(rs.getLong("user_id"));
            publicKey.setEnabled(rs.getBoolean("enabled"));
            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:lineage2.gameserver.model.pledge.SubUnit.java

/**
 * Method removeMemberInDatabase./*w w  w  .  j a  va 2  s. c o m*/
 * @param member UnitMember
 */
private static void removeMemberInDatabase(UnitMember member) {
    Connection con = null;
    PreparedStatement statement = null;
    try {
        con = DatabaseFactory.getInstance().getConnection();
        statement = con.prepareStatement(
                "UPDATE characters SET clanid=0, pledge_type=?, pledge_rank=0, lvl_joined_academy=0, apprentice=0, title='', leaveclan=? WHERE obj_Id=?");
        statement.setInt(1, Clan.SUBUNIT_NONE);
        statement.setLong(2, System.currentTimeMillis() / 1000);
        statement.setInt(3, member.getObjectId());
        statement.execute();
    } catch (Exception e) {
        _log.warn("Exception: " + e, e);
    } finally {
        DbUtils.closeQuietly(con, statement);
    }
}

From source file:dk.netarkivet.harvester.datamodel.JobDAOTester.java

public static void changeStatus(long jobID, JobStatus newStatus) {
    PreparedStatement s = null;
    Connection c = HarvestDBConnection.get();
    try {//from www .j  a  v a 2  s  .co m
        s = c.prepareStatement("update jobs set status=? where job_id=?");
        s.setLong(1, newStatus.ordinal());
        s.setLong(2, jobID);
        s.executeUpdate();
    } catch (SQLException e) {
        String message = "SQL error changing job state for job with id=" + jobID + " in database";
        throw new IOFailure(message, e);
    } finally {
        HarvestDBConnection.release(c);
    }
}

From source file:edu.lafayette.metadb.model.userman.UserManDAO.java

/**
 * Create a new user/*w  w w . jav  a 2  s.  c o m*/
 * 
 * @param userName The username for the new user.
 * @param password The password for the new user.
 * @param type The type for the new user. ("admin" or "worker")
 * @return true if the user is added successfully, false otherwise
 */
public static boolean createUser(String userName, String password, String type, String authType) {
    if (MetaDbHelper.userExists(userName)) //duplicate user
        return false;

    Connection conn = Conn.initialize(); // Establish connection
    if (conn != null) {
        try {
            PreparedStatement createUser = conn.prepareStatement(CREATE_USER);

            createUser.setString(1, userName);
            createUser.setString(2, encryptPassword(password));
            createUser.setString(3, type);
            createUser.setString(4, authType);
            createUser.setLong(5, 0);

            createUser.executeUpdate();

            createUser.close();
            conn.close();
            return true;

        } catch (Exception e) {
            MetaDbHelper.logEvent(e);
        }
    }
    return false;
}

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

/**
 * checks if key has already been registered under user's profile
 *
 * @param userId user id/*w  w w  .ja  va 2  s .  c  o  m*/
 * @param publicKey public key 
 * @return true if duplicate
 */
public static boolean isKeyRegistered(Long userId, PublicKey publicKey) {
    boolean isDuplicate = false;
    PreparedStatement stmt;
    Connection con = null;
    try {
        con = DBUtils.getConn();

        stmt = con.prepareStatement(
                "select * from public_keys where user_id=? and fingerprint like ? and profile_id is ? and id is not ?");
        stmt.setLong(1, userId);
        stmt.setString(2, SSHUtil.getFingerprint(publicKey.getPublicKey()));
        if (publicKey.getProfile() != null && publicKey.getProfile().getId() != null) {
            stmt.setLong(3, publicKey.getProfile().getId());
        } else {
            stmt.setNull(3, Types.NULL);
        }
        if (publicKey.getId() != null) {
            stmt.setLong(4, publicKey.getId());
        } else {
            stmt.setNull(4, Types.NULL);
        }

        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            isDuplicate = true;
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    DBUtils.closeConn(con);

    return isDuplicate;
}