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

public static Collection<Long> getFriends(DBManager dbManager, long userId) {

    Collection<Long> friends = new ArrayList<Long>();

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/* w ww.j av  a  2 s .c om*/

    String sql = "SELECT `friend_user_id` FROM rouge_social_friends WHERE `user_id` = ? ";

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

        rs = stmt.executeQuery();

        while (rs.next()) {
            friends.add(rs.getLong("friend_user_id"));
        }

        return friends;

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

    } finally {

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

From source file:fll.db.NonNumericNominees.java

/**
 * Get all subjective categories know for the specified tournament.
 * /*from ww  w  . jav  a2 s .c o  m*/
 * @throws SQLException
 */
public static Set<String> getCategories(final Connection connection, final int tournamentId)
        throws SQLException {
    final Set<String> result = new HashSet<>();
    PreparedStatement get = null;
    ResultSet rs = null;
    try {
        get = connection
                .prepareStatement("SELECT DISTINCT category FROM non_numeric_nominees WHERE tournament = ?");
        get.setInt(1, tournamentId);
        rs = get.executeQuery();
        while (rs.next()) {
            final String category = rs.getString(1);
            result.add(category);
        }
    } finally {
        SQLFunctions.close(rs);
        SQLFunctions.close(get);
    }
    return result;
}

From source file:com.firewallid.util.FISQL.java

public static String getFirstFieldInsertIfNotExist(Connection conn, String tableName, String field,
        Map<String, String> fields) throws SQLException {
    /* Query *//* w  w w  .  j  a v  a2 s . c  o m*/
    String query = "SELECT " + field + " FROM " + tableName + " WHERE "
            + Joiner.on(" = ? AND ").join(fields.keySet()) + " = ?";

    /* Execute */
    PreparedStatement pst = conn.prepareStatement(query);
    int i = 1;
    for (String value : fields.values()) {
        pst.setString(i, value);
        i++;
    }
    ResultSet executeQuery = pst.executeQuery();
    if (executeQuery.next()) {
        return executeQuery.getString(field);
    }

    /* Row is not exists. Insert */
    query = "INSERT INTO " + tableName + " (" + Joiner.on(", ").join(fields.keySet()) + ") VALUES ("
            + StringUtils.repeat("?, ", fields.size() - 1) + "?)";
    pst = conn.prepareStatement(query);
    i = 1;
    for (String value : fields.values()) {
        pst.setString(i, value);
        i++;
    }
    if (pst.execute()) {
        return null;
    }
    return getFirstFieldInsertIfNotExist(conn, tableName, field, fields);
}

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

/**
 * returns all key placement statuses/*from   w  ww  .ja  v a2  s  .co m*/
 *
 * @param con DB connection object
 * @param userId user id
 */
private static List<HostSystem> getAllSystemStatus(Connection con, Long userId) {

    List<HostSystem> hostSystemList = new ArrayList<>();
    try {

        PreparedStatement stmt = con.prepareStatement("select * from status where user_id=? order by id asc");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

}

From source file:com.seventh_root.ld33.common.player.Player.java

public static Player getByName(Connection databaseConnection, String playerName) throws SQLException {
    if (playersByName.containsKey(playerName))
        return playersByName.get(playerName);
    if (databaseConnection != null) {
        PreparedStatement statement = databaseConnection.prepareStatement(
                "SELECT uuid, name, password_hash, password_salt, resources FROM player WHERE name = ? LIMIT 1");
        statement.setString(1, playerName);
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            return new Player(databaseConnection, UUID.fromString(resultSet.getString("uuid")),
                    resultSet.getString("name"), resultSet.getString("password_hash"),
                    resultSet.getString("password_salt"), resultSet.getInt("resources"));
        }//from w ww.ja v  a 2 s  .  c o  m
    }
    return null;
}

From source file:com.seventh_root.ld33.common.player.Player.java

public static Player getByUUID(Connection databaseConnection, UUID uuid) throws SQLException {
    if (playersByUUID.containsKey(uuid.toString()))
        return playersByUUID.get(uuid.toString());
    if (databaseConnection != null) {
        PreparedStatement statement = databaseConnection.prepareStatement(
                "SELECT uuid, name, password_hash, password_salt, resources FROM player WHERE uuid = ? LIMIT 1");
        statement.setString(1, uuid.toString());
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            Player player = new Player(databaseConnection, UUID.fromString(resultSet.getString("uuid")),
                    resultSet.getString("name"), resultSet.getString("password_hash"),
                    resultSet.getString("password_salt"), resultSet.getInt("resources"));
            cachePlayer(player);/*from ww  w  .j  av  a  2 s . co m*/
            return player;
        }
    }
    return null;
}

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

/**
 * returns key placement status of system
 *
 * @param systemId system id//from   ww  w . j a v  a  2s  . c om
 * @param userId user id
 */
public static HostSystem getSystemStatus(Long systemId, Long userId) {

    Connection con = null;
    HostSystem hostSystem = null;
    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("select * from status where id=? and user_id=?");
        stmt.setLong(1, systemId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

        while (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.wso2telco.util.DbUtil.java

public static void updatePin(int pin, String sessionId) throws SQLException, AuthenticatorException {

    Connection connection = null;
    PreparedStatement ps = null;//from w ww  .j  a va2 s  .  c  om

    String sql = "update multiplepasswords set pin=? where ussdsessionid = ?;";

    connection = getConnectDBConnection();

    ps = connection.prepareStatement(sql);

    ps.setInt(1, pin);
    ps.setString(2, sessionId);
    ps.execute();

    if (connection != null) {
        connection.close();
    }
}

From source file:com.act.lcms.db.model.CuratedChemical.java

public static boolean updateCuratedChemical(DB db, CuratedChemical chem) throws SQLException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(QUERY_UPDATE_CURATED_CHEMICAL_BY_ID)) {
        bindInsertOrUpdateParameters(stmt, chem);
        stmt.setInt(INSERT_UPDATE_FIELDS.size() + 1, chem.getId());
        return stmt.executeUpdate() > 0;
    }/*ww  w  . j  ava2 s  .  com*/
}

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

/**
 * returns all key placement statuses/*from  w  w w  . j  av  a 2  s  .co  m*/
 *
 * @param con DB connection object
 * @param userId user id
 */
private static List<HostSystem> getAllSystemStatus(Connection con, Long userId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {

        PreparedStatement stmt = con.prepareStatement("select * from status where user_id=? order by id asc");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

}