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

/**
 * Gathers a list of attachments for a specific email address.
 * /* w w w .  j  ava2s.co  m*/
 * @param emailID Integer
 * @return List (EmailOutAttachmentModel) 
 */
public static List<EmailOutAttachmentModel> getAttachmentsByEmail(int emailID) {
    List<EmailOutAttachmentModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutAttachment WHERE emailOutID = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, emailID);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutAttachmentModel item = new EmailOutAttachmentModel();
            item.setId(rs.getInt("id"));
            item.setEmailOutID(rs.getInt("emailOutID"));
            item.setFileName(rs.getString("fileName"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:net.codjo.dataprocess.server.treatmenthelper.TreatmentHelper.java

public static void deleteRepository(Connection con, int repositoryId) throws SQLException {
    String sql = "delete PM_REPOSITORY_CONTENT where REPOSITORY_ID = ? "
            + " delete PM_REPOSITORY where REPOSITORY_ID = ?";
    PreparedStatement pStmt = con.prepareStatement(sql);
    try {//from   ww  w.ja  v  a 2 s  .  co  m
        pStmt.setInt(1, repositoryId);
        pStmt.setInt(2, repositoryId);
        pStmt.executeUpdate();
    } finally {
        pStmt.close();
    }
}

From source file:com.sql.EmailOutRelatedCase.java

public static List<EmailOutRelatedCaseModel> getRelatedCases(EmailOutModel eml) {
    List<EmailOutRelatedCaseModel> list = new ArrayList();
    Connection conn = null;/*ww w  . java 2  s .c o  m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutRelatedCase WHERE emailOutId = ? ";

        ps = conn.prepareStatement(sql);
        ps.setInt(1, eml.getId());

        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutRelatedCaseModel item = new EmailOutRelatedCaseModel();
            item.setId(rs.getInt("id"));
            item.setEmailOutId(rs.getInt("emailOutId"));
            item.setCaseYear(rs.getString("caseYear"));
            item.setCaseType(rs.getString("caseType"));
            item.setCaseMonth(rs.getString("caseMonth"));
            item.setCaseNumber(rs.getString("caseNumber"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.l2jfree.gameserver.instancemanager.RaidPointsManager.java

public static void addPoints(L2Player player, int bossId, int points) {
    final Map<Integer, Integer> pointsByBossId = getList(player.getObjectId());

    points += pointsByBossId.containsKey(bossId) ? pointsByBossId.get(bossId).intValue() : 0;

    pointsByBossId.put(bossId, points);//  w w  w  .  j a v  a  2s  .co m

    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement;
        statement = con.prepareStatement(
                "REPLACE INTO character_raid_points (`charId`,`boss_id`,`points`) VALUES (?,?,?)");
        statement.setInt(1, player.getObjectId());
        statement.setInt(2, bossId);
        statement.setInt(3, points);
        statement.executeUpdate();
        statement.close();
    } catch (Exception e) {
        _log.fatal("could not update char raid points:", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:at.becast.youploader.account.Account.java

public static Account read(int id) throws IOException {
    PreparedStatement stmt;
    try {/*  w w w . j a  v a  2s.  c  o m*/
        stmt = c.prepareStatement("SELECT * FROM `accounts` WHERE `id`=? LIMIT 1");
        stmt.setInt(1, id);
        ResultSet rs = stmt.executeQuery();
        ObjectMapper mapper = new ObjectMapper();
        List<Cookie> c = mapper.readValue(rs.getString("cookie"), new TypeReference<List<Cookie>>() {
        });
        String name = rs.getString("name");
        String token = rs.getString("refresh_token");
        stmt.close();
        rs.close();
        return new Account(id, name, token, c);
    } catch (SQLException e) {
        LOG.error("Account read error!", e);
        return null;
    }
}

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

public static boolean processRequest(Login.UserInfo userinfo, JSONObject requestParams, String token)
        throws ClassNotFoundException, SQLException, IOException {
    String oldpassword = requestParams.optString("oldpassword");
    String newpassword = requestParams.optString("newpassword");

    /*// ww w  . j a  va  2s  . c o  m
     * Validate the old password against the token we received
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();
        ps = c.prepareStatement("SELECT password " + "FROM Users " + "WHERE userid = ?");
        ps.setInt(1, userinfo.getUserID());
        rs = ps.executeQuery();

        if (rs.next()) {
            /*
             * If the result is found, hash the entry in the way it would
             * be hashed by the front end, and compare to see if the
             * hash codes match. (This requires that the hashed password
             * stored in the back-end has a consistent capitalization.
             * We arbitrarily pick lower-case for our SHA-256 hex string.
             */
            String spassword = rs.getString(1);

            /*
             * Encrypt password with token and salt
             */

            spassword = spassword + Constants.SALT + token;
            spassword = Hash.sha256(spassword);

            /*
             * Compare; if matches, then return the user info record
             * so we can store away. While the SHA256 process returns
             * consistent case, we compare ignoring case anyway, just
             * because. :-)
             */

            if (!spassword.equalsIgnoreCase(oldpassword)) {
                /* Wrong password */
                return false;
            }
        }

        /*
         * Update password stored with the updated value passed in.
         */

        rs.close();
        ps.close();

        ps = c.prepareStatement("UPDATE Users " + "SET password = ? " + "WHERE userid = ?");
        ps.setString(1, newpassword);
        ps.setInt(2, userinfo.getUserID());
        ps.execute();

        return true;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.sql.Audit.java

/**
 * Adds an entry to the audit table//  w w  w  . j  ava2s .c o m
 * @param action performed action to be stored
 */
public static void addAuditEntry(String action) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {

        conn = DBConnection.connectToDB();

        String sql = "INSERT INTO Audit VALUES" + "(?,?,?)";

        ps = conn.prepareStatement(sql);
        ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
        ps.setInt(2, 0);
        ps.setString(3, action == null ? "MISSING ACTION" : StringUtils.left(action, 255));

        ps.executeUpdate();
    } catch (SQLException ex) {
        if (ex.getCause() instanceof SQLServerException) {
            addAuditEntry(action);
        }
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
}

From source file:com.sql.Activity.java

/**
 * Updates activity set to no longer awaiting timestamp for items that have 
 * been properly stamped//from  w w  w. j a  v  a  2  s  . co  m
 * 
 * @param id Integer
 */
public static void markEntryStamped(int id) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "UPDATE Activity SET awaitingTimestamp = 0 WHERE id = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
}

From source file:dataMappers.PictureDataMapper.java

public static void addPictureToReport(DBConnector dbconnector, HttpServletRequest request)
        throws FileUploadException, IOException, SQLException {

    if (!ServletFileUpload.isMultipartContent(request)) {
        System.out.println("Invalid upload request");
        return;/*from w w  w .  ja v a 2s  . co  m*/
    }

    // Define limits for disk item
    DiskFileItemFactory factory = new DiskFileItemFactory();
    factory.setSizeThreshold(THRESHOLD_SIZE);

    // Define limit for servlet upload
    ServletFileUpload upload = new ServletFileUpload(factory);
    upload.setFileSizeMax(MAX_FILE_SIZE);
    upload.setSizeMax(MAX_REQUEST_SIZE);

    FileItem itemFile = null;
    int reportID = 0;

    // Get list of items in request (parameters, files etc.)
    List formItems = upload.parseRequest(request);
    Iterator iter = formItems.iterator();

    // Loop items
    while (iter.hasNext()) {
        FileItem item = (FileItem) iter.next();

        if (!item.isFormField()) {
            itemFile = item; // If not form field, must be item
        } else if (item.getFieldName().equalsIgnoreCase("reportID")) { // else it is a form field
            try {
                System.out.println(item.getString());
                reportID = Integer.parseInt(item.getString());
            } catch (NumberFormatException e) {
                reportID = 0;
            }
        }
    }

    // This will be null if no fields were declared as image/upload.
    // Also, reportID must be > 0
    if (itemFile != null || reportID == 0) {

        try {

            // Create credentials from final vars
            BasicAWSCredentials awsCredentials = new BasicAWSCredentials(AMAZON_ACCESS_KEY, AMAZON_SECRET_KEY);

            // Create client with credentials
            AmazonS3 s3client = new AmazonS3Client(awsCredentials);
            // Set region
            s3client.setRegion(Region.getRegion(Regions.EU_WEST_1));

            // Set content length (size) of file
            ObjectMetadata om = new ObjectMetadata();
            om.setContentLength(itemFile.getSize());

            // Get extension for file
            String ext = FilenameUtils.getExtension(itemFile.getName());
            // Generate random filename
            String keyName = UUID.randomUUID().toString() + '.' + ext;

            // This is the actual upload command
            s3client.putObject(new PutObjectRequest(S3_BUCKET_NAME, keyName, itemFile.getInputStream(), om));

            // Picture was uploaded to S3 if we made it this far. Now we insert the row into the database for the report.
            PreparedStatement stmt = dbconnector.getCon()
                    .prepareStatement("INSERT INTO reports_pictures" + "(REPORTID, PICTURE) VALUES (?,?)");

            stmt.setInt(1, reportID);
            stmt.setString(2, keyName);

            stmt.executeUpdate();

            stmt.close();

        } catch (AmazonServiceException ase) {

            System.out.println("Caught an AmazonServiceException, which " + "means your request made it "
                    + "to Amazon S3, but was rejected with an error response" + " for some reason.");
            System.out.println("Error Message:    " + ase.getMessage());
            System.out.println("HTTP Status Code: " + ase.getStatusCode());
            System.out.println("AWS Error Code:   " + ase.getErrorCode());
            System.out.println("Error Type:       " + ase.getErrorType());
            System.out.println("Request ID:       " + ase.getRequestId());

        } catch (AmazonClientException ace) {

            System.out.println("Caught an AmazonClientException, which " + "means the client encountered "
                    + "an internal error while trying to " + "communicate with S3, "
                    + "such as not being able to access the network.");
            System.out.println("Error Message: " + ace.getMessage());

        }

    }
}

From source file:com.nabla.dc.server.handler.fixed_asset.Asset.java

static public void dispose(final Connection conn, final Integer assetId, final IDisposal disposal)
        throws SQLException, DispatchException {
    final PreparedStatement redo = conn
            .prepareStatement("INSERT INTO fa_transaction_redo (fa_asset_id, command) VALUES(?,?);");
    try {/*from ww w . jav a2s  . c o  m*/
        redo.setInt(1, assetId);
        // backup transaction after disposal if any
        if (log.isDebugEnabled())
            log.debug("backing up transactions after disposal date");
        // charge monthly depreciation in disposal month if disposal is after 15
        final Calendar dt = Util.dateToCalendar(disposal.getDate());
        if (dt.get(GregorianCalendar.DAY_OF_MONTH) >= dt.getActualMaximum(GregorianCalendar.DAY_OF_MONTH) / 2)
            dt.add(GregorianCalendar.MONTH, 1);
        dt.set(GregorianCalendar.DAY_OF_MONTH, 1);
        final Date from = Util.calendarToSqlDate(dt);
        // get list of transactions to backup before we delete them
        final IntegerSet transIds = new IntegerSet();
        final PreparedStatement stmt = StatementFormat.prepare(conn,
                "SELECT t.*" + " FROM fa_transaction AS t INNER JOIN period_end AS p ON t.period_end_id=p.id"
                        + " WHERE t.fa_asset_id=? AND p.end_date>?;",
                assetId, from);
        try {
            final ResultSet rs = stmt.executeQuery();
            try {
                while (rs.next()) {
                    transIds.add(rs.getInt("id"));
                    final String command = MessageFormat.format("INSERT INTO fa_transaction"
                            + " (id,fa_asset_id,period_end_id,amount,class,type,depreciation_period)"
                            + " VALUES({0,number,0},{1,number,0},{2,number,0},{3,number,0},''{4}'',''{5}'',{6,number,0});",
                            rs.getInt("id"), rs.getInt("fa_asset_id"), rs.getInt("period_end_id"),
                            rs.getInt("amount"), rs.getString("class"), rs.getString("type"),
                            Database.getInteger(rs, "depreciation_period"));
                    if (log.isTraceEnabled())
                        log.trace("redo = " + command);
                    redo.setString(2, command);
                    redo.addBatch();
                }
            } finally {
                rs.close();
            }
        } finally {
            stmt.close();
        }
        // remove any transaction after disposal date
        if (log.isDebugEnabled())
            log.debug("removing transactions after disposal date");
        Database.executeUpdate(conn, "DELETE FROM fa_transaction WHERE id IN (?);", transIds);
        // add disposal transactions
        if (log.isDebugEnabled())
            log.debug("adding transactions for disposal");
        final TransactionList transactions = new TransactionList(assetId);
        // closing cost
        transactions.add(new Transaction(TransactionClasses.COST, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetCostBeforeDisposal(conn, assetId)));
        // closing accumulated depreciation
        transactions.add(new Transaction(TransactionClasses.DEP, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetDepreciationBeforeDisposal(conn, assetId)));
        for (Integer newTransId : transactions.save(conn, true)) {
            redo.setString(2,
                    MessageFormat.format("DELETE FROM fa_transaction WHERE id={0,number,0};", newTransId));
            redo.addBatch();
        }
        if (!Database.isBatchCompleted(redo.executeBatch()))
            throw new InternalErrorException("failed to save disposal transactions");
    } finally {
        redo.close();
    }
}