Example usage for java.sql PreparedStatement setInt

List of usage examples for java.sql PreparedStatement setInt

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setInt

Introduction

In this page you can find the example usage for java.sql PreparedStatement setInt.

Prototype

void setInt(int parameterIndex, int x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java int value.

Usage

From source file:com.wso2telco.gsma.authenticators.DBUtils.java

static int saveRequestType(String msisdn, Integer requestType)
        throws SQLException, NamingException, AuthenticatorException {
    Connection connection = null;
    //        String sql = "insert into pendingussd (msisdn, requesttype) values (?,?)";
    String sql = "insert into pendingussd (msisdn, requesttype) values (?,?) ON DUPLICATE KEY UPDATE "
            + "requesttype=VALUES(requesttype)";
    try {/* w  w  w. j a  v a2s.  c om*/
        connection = getConnectDBConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, msisdn);
        ps.setInt(2, requestType);
        ps.executeUpdate();
        return 1;
    } catch (SQLException e) {
        log.error("Error while saving request type ", e);
    } finally {
        if (connection != null) {
            connection.close();
        }
    }
    return -1;
}

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

protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, String filename, SCAN_MODE mode,
        SCAN_FILE_TYPE fileType, Integer plateId, Integer plateRow, Integer plateColumn) throws SQLException {
    stmt.setString(DB_FIELD.FILENAME.getInsertUpdateOffset(), filename);
    stmt.setString(DB_FIELD.MODE.getInsertUpdateOffset(), mode.name());
    stmt.setString(DB_FIELD.FILE_TYPE.getInsertUpdateOffset(), fileType.name());
    if (plateId != null) {
        stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId);
    } else {/*  w w w .  j  av  a  2s .  c  o m*/
        stmt.setNull(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), Types.INTEGER);
    }
    if (plateRow != null) {
        stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow);
    } else {
        stmt.setNull(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), Types.INTEGER);
    }
    if (plateColumn != null) {
        stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn);
    } else {
        stmt.setNull(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), Types.INTEGER);
    }
}

From source file:at.becast.youploader.database.SQLite.java

public static void savePlaylists(Playlists playlists, int account) throws SQLException, IOException {
    PreparedStatement prest = null;
    String sql = "INSERT INTO `playlists` (`name`, `playlistid`,`image`,`account`) " + "VALUES (?,?,?,?)";
    for (Playlists.Item i : playlists.items) {
        prest = c.prepareStatement(sql);
        prest.setString(1, i.snippet.title);
        prest.setString(2, i.id);/*from ww w.  j  av a 2s .  c o m*/
        URL url = new URL(i.snippet.thumbnails.default__.url);
        InputStream is = null;
        is = url.openStream();
        byte[] imageBytes = IOUtils.toByteArray(is);
        prest.setBytes(3, imageBytes);
        prest.setInt(4, account);
        prest.execute();
        prest.close();
    }
}

From source file:com.wso2telco.proxy.util.DBUtils.java

/**
 * Get MSISDN properties by operator Id.
 *
 * @param operatorId   operator Id./*from www.jav  a 2s  .c  o  m*/
 * @param operatorName operator Name.
 * @return MSISDN properties of given operator.
 * @throws SQLException    on errors
 * @throws NamingException on errors
 */
public static List<MSISDNHeader> getMSISDNPropertiesByOperatorId(int operatorId, String operatorName)
        throws SQLException, NamingException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    List<MSISDNHeader> msisdnHeaderList = new ArrayList<MSISDNHeader>();
    String queryToGetOperatorProperty = "SELECT  msisdnHeaderName, isHeaderEncrypted, encryptionImplementation, "
            + "msisdnEncryptionKey, priority FROM operators_msisdn_headers_properties WHERE operatorId = ? ORDER BY"
            + " priority ASC";
    try {
        connection = getConnectDBConnection();
        preparedStatement = connection.prepareStatement(queryToGetOperatorProperty);
        preparedStatement.setInt(1, operatorId);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            MSISDNHeader msisdnHeader = new MSISDNHeader();
            msisdnHeader.setMsisdnHeaderName(resultSet.getString(AuthProxyConstants.MSISDN_HEADER_NAME));
            msisdnHeader.setHeaderEncrypted(resultSet.getBoolean(AuthProxyConstants.IS_HEADER_ENCRYPTED));
            msisdnHeader.setHeaderEncryptionMethod(
                    resultSet.getString(AuthProxyConstants.ENCRYPTION_IMPLEMENTATION));
            msisdnHeader.setHeaderEncryptionKey(resultSet.getString(AuthProxyConstants.MSISDN_ENCRYPTION_KEY));
            msisdnHeader.setPriority(resultSet.getInt(AuthProxyConstants.PRIORITY));
            msisdnHeaderList.add(msisdnHeader);
        }
    } catch (SQLException e) {
        throw new SQLException(
                "Error occurred while retrieving operator MSISDN properties of operator : " + operatorName, e);
    } catch (NamingException e) {
        throw new ConfigurationException("DataSource could not be found in mobile-connect.xml");
    } finally {
        closeAllConnections(preparedStatement, connection, resultSet);
    }
    return msisdnHeaderList;
}

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Gets the subscription id./*from w ww. j a v a  2  s.c o m*/
 *
 * @param apiID the api id
 * @param applicationID the application id
 * @return the subscription id
 * @throws NamingException the naming exception
 * @throws SQLException the SQL exception
 */

/*
    public int findSubscriptionId(String appId, String apiId) throws
    Exception {
        
String sql = SQLConstants.GET_SUBSCRIPTION_ID_FOR_API_AND_APP_SQL;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_DB);
    ps = conn.prepareStatement(sql);
    ps.setInt(1, Integer.parseInt(apiId));
    ps.setInt(2, Integer.parseInt(appId));
        
    rs = ps.executeQuery();
    while (rs.next()) {
        return rs.getInt("SUBSCRIPTION_ID");
    }
} catch (SQLException e) {
    System.out.println(e.toString());
    throw e;
} finally {
    DbUtils.closeAllConnections(ps, conn, rs);
        
}
throw new Exception(
        "No record found in table AM_SUBSCRIPTION for APPLICATION_ID = " + appId + " and API_ID = " + apiId);
    }*/

public static int getSubscriptionId(String apiID, String applicationID) throws NamingException, SQLException {

    String subscriptionId = null;

    // String sql = "select * from am_subscription";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {

        String sql = VerificationConstants.GET_SUBSCRIPTION_ID_FOR_API_AND_APP_SQL;

        conn = getAMDBConnection();
        ps = conn.prepareStatement(sql);

        ps.setInt(1, Integer.parseInt(apiID));
        ps.setInt(2, Integer.parseInt(applicationID));
        rs = ps.executeQuery();

        while (rs.next()) {
            return rs.getInt("SUBSCRIPTION_ID");
        }

    } catch (SQLException e) {
        log.error("Error occured while writing southbound record.", e);
        throw e;
    } catch (NamingException e) {
        log.error("Error while finding the Datasource.", e);
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }

    return -1;
}

From source file:com.aurel.track.dbase.MigrateTo37.java

private static void addStatusChange(PreparedStatement pstmtStatusChangeFirst,
        PreparedStatement pstmtStatusChange, Integer fieldChangeID, Integer transactionID, Integer newStatus,
        Integer oldStatus) {//from w  w  w  .ja  v  a  2 s  . co  m
    if (oldStatus == null) {
        try {
            pstmtStatusChangeFirst.setInt(1, fieldChangeID);
            pstmtStatusChangeFirst.setInt(2, SystemFields.STATE);
            pstmtStatusChangeFirst.setInt(3, transactionID);
            pstmtStatusChangeFirst.setInt(4, newStatus);
            pstmtStatusChangeFirst.setLong(5, SystemFields.STATE);
            pstmtStatusChangeFirst.setInt(6, ValueType.SYSTEMOPTION);
            pstmtStatusChangeFirst.setString(7, UUID.randomUUID().toString());
            pstmtStatusChangeFirst.executeUpdate();
        } catch (Exception e) {
            LOGGER.error(
                    "Adding a field change for status with transactionID " + transactionID + " fieldChangeID "
                            + fieldChangeID + " new status " + newStatus + " failed with " + e.getMessage(),
                    e);
            System.err.println(ExceptionUtils.getStackTrace(e));
        }
    } else {
        try {
            pstmtStatusChange.setInt(1, fieldChangeID);
            pstmtStatusChange.setInt(2, SystemFields.STATE);
            pstmtStatusChange.setInt(3, transactionID);
            pstmtStatusChange.setInt(4, newStatus);
            pstmtStatusChange.setInt(5, oldStatus);
            pstmtStatusChange.setLong(6, SystemFields.STATE);
            pstmtStatusChange.setInt(7, ValueType.SYSTEMOPTION);
            pstmtStatusChange.setString(8, UUID.randomUUID().toString());
            pstmtStatusChange.executeUpdate();
        } catch (Exception e) {
            LOGGER.error("Adding a field change for status with transactionID " + transactionID
                    + " fieldChangeID " + fieldChangeID + " new status " + newStatus + " oldStatus " + oldStatus
                    + " failed with " + e.getMessage(), e);
            System.err.println(ExceptionUtils.getStackTrace(e));
        }
    }
}

From source file:com.chaosinmotion.securechat.server.commands.ForgotPassword.java

/**
 * Process a forgot password request. This generates a token that the
 * client is expected to return with the change password request.
 * @param requestParams/*from w  w  w.  java 2  s .co  m*/
 * @throws SQLException 
 * @throws IOException 
 * @throws ClassNotFoundException 
 * @throws JSONException 
 * @throws NoSuchAlgorithmException 
 */

public static void processRequest(JSONObject requestParams)
        throws SQLException, ClassNotFoundException, IOException, NoSuchAlgorithmException, JSONException {
    String username = requestParams.optString("username");

    /*
     * Step 1: Convert username to the userid for this
     */
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    int userID = 0;
    String retryID = UUID.randomUUID().toString();

    try {
        c = Database.get();
        ps = c.prepareStatement("SELECT userid " + "FROM Users " + "WHERE username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();
        if (rs.next()) {
            userID = rs.getInt(1);
        }

        if (userID == 0)
            return;
        ps.close();
        rs.close();

        /*
         * Step 2: Generate the retry token and insert into the forgot 
         * database with an expiration date 1 hour from now.
         */

        Timestamp ts = new Timestamp(System.currentTimeMillis() + 3600000);
        ps = c.prepareStatement("INSERT INTO ForgotPassword " + "    ( userid, token, expires ) " + "VALUES "
                + "    ( ?, ?, ?)");
        ps.setInt(1, userID);
        ps.setString(2, retryID);
        ps.setTimestamp(3, ts);
        ps.execute();
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }

    /*
     * Step 3: formulate a JSON string with the retry and send
     * to the user. The format of the command we send is:
     * 
     * { "cmd": "forgotpassword", "token": token }
     */

    JSONObject obj = new JSONObject();
    obj.put("cmd", "forgotpassword");
    obj.put("token", retryID);
    MessageQueue.getInstance().enqueueAdmin(userID, obj.toString(4));
}

From source file:com.krawler.esp.servlets.SuperAdminServlet.java

public static String getCompanyList(Connection conn, String start, String limit) throws ServiceException {
    String result = null;/*from w  w  w.  java2  s.  c  om*/
    PreparedStatement pstmt = null;
    KWLJsonConverter KWL = new KWLJsonConverter();
    ResultSet rs = null;
    String tp = null;
    try {
        pstmt = conn.prepareStatement(
                "SELECT company.companyid, company.image, company.companyname, company.createdon, company.address, company.city, company.modifiedon,"
                        + "company.state, company.country, company.phone, company.fax, company.zip, company.timezone, company.website, company.activated, "
                        + "count(companyusers.companyid) AS members FROM company LEFT JOIN companyusers ON company.companyid=companyusers.companyid "
                        + "GROUP BY company.companyid LIMIT ? OFFSET ?;");
        pstmt.setInt(1, Integer.parseInt(limit));
        pstmt.setInt(2, Integer.parseInt(start));
        rs = pstmt.executeQuery();
        result = KWL.GetJsonForGrid(rs);
        pstmt.close();
        pstmt = conn.prepareStatement("select count(*) as count from company;");
        rs = pstmt.executeQuery();
        rs.next();
        int count1 = rs.getInt("count");
        result = result.substring(1);
        tp = "{\"count\":" + count1 + "," + result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("SuperAdminHandler.getSUAdminData", e);
    } finally {
        DbPool.closeStatement(pstmt);
    }
    return tp;
}

From source file:com.jernejerin.traffic.helper.TripOperations.java

/**
 * Insert a trip into database.//from  ww  w  .j ava  2 s  .  c  om
 *
 * @param trip trip to insert.
 * @param table table into which we need to insert trip
 */
public static void insertTrip(Trip trip, String table) {
    //        LOGGER.log(Level.INFO, "Started inserting trip into DB for trip = " +
    //                trip.toString() + " from thread = " + Thread.currentThread());
    PreparedStatement insertTrip = null;
    Connection conn = null;
    try {
        // first we need to get connection from connection pool
        conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:taxi");

        // setting up prepared statement
        insertTrip = conn.prepareStatement("insert into " + table
                + " (eventId, medallion, hack_license, pickup_datetime, "
                + "dropoff_datetime, trip_time, trip_distance, pickup_longitude, pickup_latitude, dropoff_longitude, "
                + "dropoff_latitude, payment_type, fare_amount, surcharge, mta_tax, tip_amount, tolls_amount, "
                + "total_amount, timestampReceived) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        insertTrip.setInt(1, trip.getId());
        insertTrip.setString(2, trip.getMedallion());
        insertTrip.setString(3, trip.getHackLicense());
        insertTrip.setTimestamp(4,
                new Timestamp(trip.getPickupDatetime().toEpochSecond(ZoneOffset.UTC) * 1000));
        insertTrip.setTimestamp(5,
                new Timestamp(trip.getDropOffDatetime().toEpochSecond(ZoneOffset.UTC) * 1000));
        insertTrip.setInt(6, trip.getTripTime());
        insertTrip.setDouble(7, trip.getTripDistance());
        insertTrip.setDouble(8, trip.getPickupLongitude());
        insertTrip.setDouble(9, trip.getPickupLatitude());
        insertTrip.setDouble(10, trip.getDropOffLongitude());
        insertTrip.setDouble(11, trip.getDropOffLatitude());
        insertTrip.setString(12, trip.getPaymentType() != null ? trip.getPaymentType().name() : null);
        insertTrip.setDouble(13, trip.getFareAmount());
        insertTrip.setDouble(14, trip.getSurcharge());
        insertTrip.setDouble(15, trip.getMtaTax());
        insertTrip.setDouble(16, trip.getTipAmount());
        insertTrip.setDouble(17, trip.getTollsAmount());
        insertTrip.setDouble(18, trip.getTotalAmount());
        insertTrip.setLong(19, trip.getTimestampReceived());

        insertTrip.execute();
    } catch (SQLException e) {
        LOGGER.log(Level.SEVERE, "Problem when inserting ticket into DB for ticket = " + trip
                + " from thread = " + Thread.currentThread());
    } finally {
        try {
            if (insertTrip != null)
                insertTrip.close();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, "Problem with closing prepared statement for ticket = " + trip
                    + " from thread = " + Thread.currentThread());
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, "Problem with closing connection from thread = " + Thread.currentThread());
        }
        //            LOGGER.log(Level.INFO, "Finished inserting ticket into DB for for ticket = " +
        //                    trip + " from thread = " + Thread.currentThread());
    }
}

From source file:com.concursive.connect.web.modules.documents.dao.FileItemList.java

/**
 * Returns the number of fileItems that match the module and itemid
 *
 * @param db           Description of the Parameter
 * @param linkModuleId Description of the Parameter
 * @param linkItemId   Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 *///from   ww  w. j  a va 2s. c  o m
public static int retrieveRecordCount(Connection db, int linkModuleId, int linkItemId) throws SQLException {
    int count = 0;
    PreparedStatement pst = db.prepareStatement("SELECT COUNT(*) as filecount " + "FROM project_files pf "
            + "WHERE pf.link_module_id = ? and pf.link_item_id = ? ");
    pst.setInt(1, linkModuleId);
    pst.setInt(2, linkItemId);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        count = rs.getInt("filecount");
    }
    rs.close();
    pst.close();
    return count;
}