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

/**
 * adds a host system to profile//www .j a v  a2 s  . c  om
 *
 * @param profileId profile id
 * @param systemId  host system id
 */
public static void addSystemToProfile(Long profileId, Long systemId) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con
                .prepareStatement("insert into system_map (profile_id, system_id) values (?,?)");
        stmt.setLong(1, profileId);
        stmt.setLong(2, systemId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        e.printStackTrace();
    }
    DBUtils.closeConn(con);
}

From source file:org.ulyssis.ipp.snapshot.Snapshot.java

public static void deleteAfter(Connection connection, Snapshot snapshot) throws SQLException {
    String statement = "DELETE FROM \"snapshots\" WHERE \"time\" > ? OR (\"time\" = ? AND \"event\" > ?)";
    try (PreparedStatement stmt = connection.prepareStatement(statement)) {
        Timestamp timestamp = Timestamp.from(snapshot.getSnapshotTime());
        stmt.setTimestamp(1, timestamp);
        stmt.setTimestamp(2, timestamp);
        stmt.setLong(3, snapshot.getEventId().orElse(-1L));
        LOG.debug("Executing query: {}", stmt);
        int affectedRows = stmt.executeUpdate();
        LOG.debug("deleteAfter affected {} rows", affectedRows);
    }// w w  w.j a  va2 s  . com
}

From source file:ca.qc.adinfo.rouge.social.db.SocialDb.java

public static boolean addFriend(DBManager dbManager, long userId, long friendId) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*w  ww.j a v  a  2  s. com*/
    String sql = null;

    sql = "INSERT INTO rouge_social_friends (`user_id`, `friend_user_id`) VALUES (?, ?)";

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

        stmt.setLong(1, userId);
        stmt.setLong(2, friendId);

        int ret = stmt.executeUpdate();

        if (ret < 1) {
            return false;
        }

        stmt.setLong(1, friendId);
        stmt.setLong(2, userId);

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

public static boolean setMailAsRead(DBManager dbManager, long mailId) {

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

    sql = "UPDATE rouge_mail SET `status` = ? WHERE `id` = ? ";

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

        stmt.setString(1, "rea");
        stmt.setLong(2, mailId);

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

public static boolean deleteMail(DBManager dbManager, long mailId) {

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

    sql = "UPDATE rouge_mail SET `status` = ? WHERE `id` = ? ";

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

        stmt.setString(1, "del");
        stmt.setLong(2, mailId);

        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.sql.SECExceptions.java

/**
 * Removes old exception based off of a global exception date timeframe
 *///from  w  w  w . j  a  v  a2 s.co  m
public static void removeOldExceptions() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "DELETE FROM SECExceptions WHERE " + "timeOccurred < dateadd("
                + Global.getExceptionTimeFrame() + ",-" + Global.getExceptionTimeAmount() + ", getdate())";
        ps = conn.prepareStatement(sql);
        ps.executeUpdate();
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
}

From source file:com.wso2telco.core.mnc.resolver.mncrange.McnRangeDbUtil.java

public static String getMncBrand(String mcc, String mnc) throws MobileNtException {
    Connection conn = null;
    PreparedStatement ps = null;/* w  w  w  .j  a  v a 2s . c o m*/
    ResultSet rs = null;
    String sql = "SELECT operatorname " + "FROM operators " + "WHERE mcc = ? AND mnc = ?";

    String mncBrand = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        ps.setString(2, mnc);
        rs = ps.executeQuery();
        if (rs.next()) {
            mncBrand = rs.getString("operatorname");
        }
    } catch (SQLException e) {
        handleException("Error occured while getting Brand for for mcc: and mnc: " + mcc + ":" + mnc
                + " from the database", e);
    } catch (Exception e) {
        handleException("Error occured while getting Brand for for mcc: and mnc: " + mcc + ":" + mnc
                + " from the database", e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, rs);
    }
    return mncBrand;
}

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

/**
 * Update a tables LifeCycleInfo/*  w w w  . j  a  va 2  s. c o m*/
 *
 * @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.wso2telco.util.DbUtil.java

public static void insertPinAttempt(String msisdn, int attempts, String sessionId)
        throws SQLException, AuthenticatorException {

    Connection connection = null;
    PreparedStatement ps = null;/* w ww. j a  v  a  2 s.com*/

    String sql = "insert into multiplepasswords(username, attempts, ussdsessionid) values  (?,?,?);";

    connection = getConnectDBConnection();

    ps = connection.prepareStatement(sql);

    ps.setString(1, msisdn);
    ps.setInt(2, attempts);
    ps.setString(3, sessionId);
    ps.execute();

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

From source file:org.ulyssis.ipp.snapshot.Snapshot.java

public static Optional<Snapshot> loadForEvent(Connection connection, Event event)
        throws SQLException, IOException {
    String statement = "SELECT \"id\", \"data\" FROM \"snapshots\" WHERE \"event\" = ?";
    try (PreparedStatement stmt = connection.prepareStatement(statement)) {
        stmt.setLong(1, event.getId().get());
        LOG.debug("executing query: {}", stmt);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            String data = rs.getString("data");
            Snapshot result = Serialization.getJsonMapper().readValue(data, Snapshot.class);
            result.id = rs.getLong("id");
            result.eventId = event.getId().get();
            return Optional.of(result);
        } else {//from  ww  w .  j  ava2 s . c  o  m
            return Optional.empty();
        }
    }
}