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:com.webpagebytes.wpbsample.database.WPBDatabase.java

public Transaction getTransaction(long id) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    try {//from  w  w w  .  j  av  a 2s  . c  o  m
        statement = connection.prepareStatement(GET_ACCOUNTOPERATIONS_STATEMENT);
        statement.setLong(1, id);
        ResultSet rs = statement.executeQuery();
        if (rs.next()) {
            Transaction transaction = new Transaction();
            if (rs.getInt(3) != ACCOUNT_OPERATION_PAYMENT) {
                return null;
            }
            transaction.setId(id);
            transaction.setSource_user_id(rs.getInt(6));
            transaction.setDestination_user_id(rs.getInt(7));
            transaction.setDate(rs.getTimestamp(5));
            transaction.setAmount(rs.getLong(4));
            return transaction;
        } else {
            return null;
        }
    } catch (SQLException e) {
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

From source file:org.ohmage.query.impl.UserCampaignQueries.java

@Override
public void createUserCampaignMask(final CampaignMask mask) throws DataAccessException {

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Creating an observer.");

    try {/*  ww w  .j  av  a2 s  .c o m*/
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        // Campaign mask creation SQL.
        final String campaignMaskSql = "INSERT INTO campaign_mask(" + "assigner_user_id, "
                + "assignee_user_id, " + "campaign_id, " + "mask_id, " + "creation_time) " + "VALUES ("
                + "(SELECT id FROM user WHERE username = ?), " + "(SELECT id FROM user WHERE username = ?), "
                + "(SELECT id FROM campaign WHERE urn = ?), " + "?, " + "?)";

        // Campaign mask creation statement with parameters.
        PreparedStatementCreator maskCreator = new PreparedStatementCreator() {
            /*
             * (non-Javadoc)
             * @see org.springframework.jdbc.core.PreparedStatementCreator#createPreparedStatement(java.sql.Connection)
             */
            @Override
            public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {

                PreparedStatement ps = connection.prepareStatement(campaignMaskSql, new String[] { "id" });

                ps.setString(1, mask.getAssignerUserId());
                ps.setString(2, mask.getAssigneeUserId());
                ps.setString(3, mask.getCampaignId());
                ps.setString(4, mask.getId().toString());
                ps.setLong(5, mask.getCreationTime().getMillis());

                return ps;
            }

        };

        // The auto-generated key for the observer.
        KeyHolder maskKeyHolder = new GeneratedKeyHolder();

        // Create the observer.
        try {
            getJdbcTemplate().update(maskCreator, maskKeyHolder);
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error executing SQL '" + campaignMaskSql + "' with parameters: "
                    + mask.getAssignerUserId() + ", " + mask.getAssigneeUserId() + ", " + mask.getCampaignId()
                    + ", " + mask.getId().toString() + ", " + mask.getCreationTime().getMillis(), e);
        }

        // Get the mask's DB ID.
        long key = maskKeyHolder.getKey().longValue();

        // Create each of the masks.
        final String campaignMaskPromptIdSql = "INSERT INTO campaign_mask_survey_prompt_map("
                + "campaign_mask_id, " + "survey_id, " + "prompt_id)" + "VALUES (?, ?, ?)";

        // Get the survey IDs from the mask.
        Map<String, Set<String>> promptIds = mask.getSurveyPromptMap();

        // Create the list of parameters for each of the survey IDs.
        List<Object[]> maskPromptIdParameters = new ArrayList<Object[]>(promptIds.size());

        // Cycle through the survey IDs building the parameters list.
        for (String surveyId : promptIds.keySet()) {
            for (String promptId : promptIds.get(surveyId)) {
                maskPromptIdParameters.add(new Object[] { key, surveyId, promptId });
            }
        }

        // Add the mask survey IDs.
        getJdbcTemplate().batchUpdate(campaignMaskPromptIdSql, maskPromptIdParameters);

        // Commit the transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while attempting to rollback the transaction.", e);
    }

}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public DepositWithdrawal getDepositOrWithdrawal(long id) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    try {/*from   ww w  . j  a va 2 s.  com*/
        statement = connection.prepareStatement(GET_ACCOUNTOPERATIONS_STATEMENT);
        statement.setLong(1, id);
        ResultSet rs = statement.executeQuery();
        if (rs.next()) {
            DepositWithdrawal operation = new DepositWithdrawal();
            operation.setId(id);
            operation.setUser_id(rs.getInt(2));
            int type = rs.getInt(3);
            if (type == ACCOUNT_OPERATION_DEPOSIT) {
                operation.setType(OperationType.DEPOSIT);
            } else if (type == ACCOUNT_OPERATION_WITHDRAWAL) {
                operation.setType(OperationType.WITHDRAWAL);
            } else {
                return null;
            }
            operation.setDate(rs.getTimestamp(5));
            operation.setAmount(rs.getLong(4));
            return operation;
        } else {
            return null;
        }
    } catch (SQLException e) {
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

From source file:com.flexive.ejb.beans.MandatorEngineBean.java

/**
 * {@inheritDoc}/*from  ww  w . j a  v a2s  .co m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void activate(long mandatorId) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    final FxEnvironment environment;
    // Security
    FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor);
    environment = CacheAdmin.getEnvironment();
    //exist check
    Mandator mand = environment.getMandator(mandatorId);
    if (mand.isActive())
        return; //silently ignore
    Connection con = null;
    PreparedStatement ps = null;
    String sql;

    try {

        // Obtain a database connection
        con = Database.getDbConnection();

        //                                                1              2              3          4
        sql = "UPDATE " + TBL_MANDATORS + " SET IS_ACTIVE=?, MODIFIED_BY=?, MODIFIED_AT=? WHERE ID=?";
        final long NOW = System.currentTimeMillis();
        ps = con.prepareStatement(sql);

        ps.setBoolean(1, true);
        ps.setLong(2, ticket.getUserId());
        ps.setLong(3, NOW);
        ps.setLong(4, mandatorId);
        ps.executeUpdate();
        StructureLoader.updateMandator(FxContext.get().getDivisionId(),
                new Mandator(mand.getId(), mand.getName(), mand.getMetadataId(), true,
                        new LifeCycleInfoImpl(mand.getLifeCycleInfo().getCreatorId(),
                                mand.getLifeCycleInfo().getCreationTime(), ticket.getUserId(), NOW)));
    } catch (SQLException exc) {
        EJBUtils.rollback(ctx);
        throw new FxUpdateException(LOG, exc, "ex.mandator.updateFailed", mand.getName(), exc.getMessage());
    } finally {
        Database.closeObjects(MandatorEngineBean.class, con, ps);
    }
}

From source file:com.flexive.ejb.beans.MandatorEngineBean.java

/**
 * {@inheritDoc}//from w  w  w.  j  a v a 2s  .  c  om
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void deactivate(long mandatorId) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    final FxEnvironment environment;
    // Security
    FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor);
    environment = CacheAdmin.getEnvironment();
    //exist check
    Mandator mand = environment.getMandator(mandatorId);
    if (!mand.isActive())
        return; //silently ignore
    if (mand.getId() == ticket.getMandatorId())
        throw new FxInvalidParameterException("mandatorId", "ex.mandator.deactivate.own", mand.getName(),
                mand.getId());
    Connection con = null;
    PreparedStatement ps = null;
    String sql;

    try {

        // Obtain a database connection
        con = Database.getDbConnection();

        //                                                1              2              3          4
        sql = "UPDATE " + TBL_MANDATORS + " SET IS_ACTIVE=?, MODIFIED_BY=?, MODIFIED_AT=? WHERE ID=?";
        final long NOW = System.currentTimeMillis();
        ps = con.prepareStatement(sql);

        ps.setBoolean(1, false);
        ps.setLong(2, ticket.getUserId());
        ps.setLong(3, NOW);
        ps.setLong(4, mandatorId);
        ps.executeUpdate();
        StructureLoader.updateMandator(FxContext.get().getDivisionId(),
                new Mandator(mand.getId(), mand.getName(), mand.getMetadataId(), false,
                        new LifeCycleInfoImpl(mand.getLifeCycleInfo().getCreatorId(),
                                mand.getLifeCycleInfo().getCreationTime(), ticket.getUserId(), NOW)));
    } catch (SQLException exc) {
        EJBUtils.rollback(ctx);
        throw new FxUpdateException(LOG, exc, "ex.mandator.updateFailed", mand.getName(), exc.getMessage());
    } finally {
        Database.closeObjects(MandatorEngineBean.class, con, ps);
    }
}

From source file:com.flexive.core.storage.genericSQL.GenericLockStorage.java

/**
 * Internal method that returns a lock if <code>obj</code> is locked, or <code>FxLockType.None</code> if not
 *
 * @param con an open and valid connection
 * @param obj resource or primary key//from  w ww.  j a  v  a2s  . co m
 * @return FxLock or <code>FxLockType.None</code> if not
 * @throws FxLockException on errors
 */
@SuppressWarnings({ "ThrowableInstanceNeverThrown" })
protected FxLock _getLock(Connection con, Object obj) throws FxLockException {
    if (obj instanceof FxPK) {
        obj = getDistinctPK(con, (FxPK) obj);
    } else if (obj instanceof String) {
        if (StringUtils.isEmpty((String) obj))
            throw new FxLockException("ex.lock.invalidResource");
    } else
        throw new FxLockException("ex.lock.invalidResource");
    PreparedStatement ps = null;
    try {
        if (obj instanceof FxPK) {
            //                                1       2        3          4
            ps = con.prepareStatement("SELECT USER_ID,LOCKTYPE,CREATED_AT,EXPIRES_AT FROM " + TBL_LOCKS
                    + " WHERE LOCK_ID=? AND LOCK_VER=?");
            ps.setLong(1, ((FxPK) obj).getId());
            ps.setInt(2, ((FxPK) obj).getVersion());
        } else {
            //                                1       2        3          4
            ps = con.prepareStatement("SELECT USER_ID,LOCKTYPE,CREATED_AT,EXPIRES_AT FROM " + TBL_LOCKS
                    + " WHERE LOCK_RESOURCE=?");
            ps.setString(1, (String) obj);
        }
        ResultSet rs = ps.executeQuery();
        if (rs == null || !rs.next())
            return (obj instanceof FxPK ? FxLock.noLockPK() : FxLock.noLockResource());
        FxLock ret = new FxLock(FxLockType.getById(rs.getInt(2)), rs.getLong(3), rs.getLong(4), rs.getLong(1),
                obj);
        if (ret.isExpired()) {
            ps.close();
            if (ret.isContentLock()) {
                ps = con.prepareStatement("DELETE FROM " + TBL_LOCKS + " WHERE LOCK_ID=? AND LOCK_VER=?");
                ps.setLong(1, ((FxPK) obj).getId());
                ps.setInt(2, ((FxPK) obj).getVersion());
            } else {
                ps = con.prepareStatement("DELETE FROM " + TBL_LOCKS + " WHERE LOCK_RESOURCE=?");
                ps.setString(1, String.valueOf(obj));
            }
            ps.executeUpdate();
            return (obj instanceof FxPK ? FxLock.noLockPK() : FxLock.noLockResource());
        }
        return ret;
    } catch (SQLException e) {
        throw new FxDbException(e, "ex.db.sqlError", e.getMessage()).asRuntimeException();
    } finally {
        Database.closeObjects(GenericLockStorage.class, null, ps);
    }
}

From source file:com.l2jfree.gameserver.datatables.TradeListTable.java

public void dataCountStore() {
    Connection con = null;//from  w  w  w . j  a va 2  s .  c o m
    PreparedStatement statement;

    int listId;
    if (_lists == null)
        return;

    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);

        for (L2TradeList list : _lists.values()) {
            if (list == null)
                continue;
            listId = list.getListId();

            for (L2ItemInstance Item : list.getItems()) {
                if (Item.getCount() < Item.getInitCount()) //needed?
                {
                    statement = con.prepareStatement(
                            "UPDATE merchant_buylists SET currentCount=? WHERE item_id=? AND shop_id=?");
                    statement.setLong(1, Item.getCount());
                    statement.setInt(2, Item.getItemId());
                    statement.setInt(3, listId);
                    statement.executeUpdate();
                    statement.close();
                }
            }
        }
    } catch (Exception e) {
        _log.fatal("TradeController: Could not store Count Item");
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.nextep.datadesigner.vcs.services.VCSFiles.java

private void writeMySQLBlob(Connection conn, IRepositoryFile file, File localFile) throws SQLException {
    file.setFileSizeKB((localFile.length() / 1024) + 1);
    PreparedStatement stmt = null;
    InputStream is = null;/* w ww .j av  a2s.c om*/
    try {
        /*
         * Columns names in the SET clause cannot be qualified with an alias name because it
         * would fail in Postgres.
         */
        stmt = conn.prepareStatement("UPDATE rep_files rf " //$NON-NLS-1$
                + "  SET file_content = ? " //$NON-NLS-1$
                + "    , filesize = ? " //$NON-NLS-1$
                + "WHERE rf.file_id = ? "); //$NON-NLS-1$
        is = new FileInputStream(localFile);
        stmt.setBinaryStream(1, is);
        stmt.setLong(2, file.getFileSizeKB());
        stmt.setLong(3, file.getUID().rawId());
        stmt.execute();
    } catch (IOException e) {
        throw new ErrorException(e);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        safeClose(is);
    }
}

From source file:net.mybox.mybox.ServerClientConnection.java

private void sendServerFileList() {

    System.out.println("getting local file list for: " + localDir);

    Statement statement = null;/*from  w  w  w .  ja  v a  2s.  c  om*/

    // refresh the database
    try {
        statement = connection.createStatement();
        statement.setQueryTimeout(30);

        statement.executeUpdate(
                "create table if not exists archive (name text primary key, type char(1), lastupdate integer)");
    } catch (Exception e) {
        System.out.println("SQLite error: " + e.getMessage());
    }

    try {
        List<MyFile> files = Common.getFileListing(new File(localDir));

        JSONArray jsonArray = new JSONArray();

        PreparedStatement prep = connection.prepareStatement("insert or ignore into archive values(?,?,?);");

        for (MyFile thisFile : files) {
            System.out.println(" " + thisFile.name);

            // TODO: do not insert directories that are already in the DB
            //  compare against getFilesFromDB ?

            prep.setString(1, thisFile.name);
            prep.setString(2, thisFile.getTypeChar() + "");
            prep.setLong(3, thisFile.modtime);
            prep.addBatch();

            jsonArray.add(thisFile.serialize());
        }

        prep.executeBatch();
        connection.commit();

        sendCommandToClient(Common.Signal.requestServerFileList_response);
        ByteStream.toStream(outStream, jsonArray.toJSONString());

        System.out.println("local file list: " + jsonArray.size() + " files");

    } catch (Exception e) {
        System.out.println("Error when getting local file list " + e.getMessage());
    }

}

From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.DeleteMetadataAlterHelper.java

protected void doInPreparedStatement(PreparedStatement ps, String metaModelCode, boolean hasChildMetaModel,
        List<AbstractMetadata> metadatas) throws SQLException {
    try {/* w ww  .j a v a2  s . c o  m*/
        for (AbstractMetadata metadata : metadatas) {
            // ?ID
            String sequenceId = sequenceDao.getUuid();
            ps.setString(1, sequenceId);
            // ?,1
            ps.setString(2, "1");
            // ID
            ps.setString(3, taskInstanceId);
            // // ?ID
            // ps.setString(4, metadata.getId());
            // // 
            // ps.setString(5, metaModelCode);
            // ID
            ps.setString(4, userId);

            // START_TIME?START_TIME
            ps.setLong(5, metadata.getStartTime());
            // : ALTERATION_TIME
            ps.setLong(6, startTime);

            // OLD_START_TIME ???OLD_START_TIME??
            ps.setNull(7, java.sql.Types.BIGINT);
            // ?ID
            ps.setString(8, metadata.getId());

            ps.addBatch();
            ps.clearParameters();

            if (++super.count % super.batchSize == 0) {
                ps.executeBatch();
                ps.clearBatch();
            }

        }
    } catch (SQLException e) {
        // ??,????,,??
        log.warn("??!", e);
    }

}