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:dbcount.DbCountInitializeJob.java

private int populateAccess(final Connection conn) throws SQLException {
    final Random random = new Random();

    final int PROBABILITY_PRECISION = 100; // 1 / 100
    final int NEW_PAGE_PROBABILITY = 15; // 15 / 100

    // Pages in the site :
    final String[] pages = { "/a", "/b", "/c", "/d", "/e", "/f", "/g", "/h", "/i", "/j" };
    // linkMatrix[i] is the array of pages(indexes) that page_i links to.
    final int[][] linkMatrix = { { 1, 5, 7 }, { 0, 7, 4, 6, }, { 0, 1, 7, 8 }, { 0, 2, 4, 6, 7, 9 }, { 0, 1 },
            { 0, 3, 5, 9 }, { 0 }, { 0, 1, 3 }, { 0, 2, 6 }, { 0, 2, 6 } };

    int totalPageview = 0;
    PreparedStatement statement = null;
    try {// ww w.j  a  v a2 s  .  c o m
        statement = conn.prepareStatement("INSERT INTO Access(url, referrer, time) VALUES (?, ?, ?)");

        int currentPage = random.nextInt(pages.length);
        String referrer = null;

        final int time = random.nextInt(50) + 50;
        for (int i = 0; i < time; i++) {
            statement.setString(1, pages[currentPage]);
            if (referrer == null) {
                statement.setNull(2, Types.VARCHAR);
            } else {
                statement.setString(2, referrer);
            }
            statement.setLong(3, i);
            statement.execute();
            ++totalPageview;

            // go to a new page with probability NEW_PAGE_PROBABILITY /
            // PROBABILITY_PRECISION
            int action = random.nextInt(PROBABILITY_PRECISION);
            if (action < NEW_PAGE_PROBABILITY) {
                currentPage = random.nextInt(pages.length); // a random page
                referrer = null;
            } else {
                referrer = pages[currentPage];
                action = random.nextInt(linkMatrix[currentPage].length);
                currentPage = linkMatrix[currentPage][action];
            }
        }

        conn.commit();

    } catch (SQLException ex) {
        conn.rollback();
        throw ex;
    } finally {
        if (statement != null) {
            statement.close();
        }
    }
    return totalPageview;
}

From source file:com.flexive.core.storage.MySQL.MySQLHierarchicalStorage.java

protected boolean tryLock(Connection con, long id, int version) {
    try {//  w  w  w.j  a v a  2 s. c om
        PreparedStatement ps = null;
        try {
            //grouping by (dummy) 1 prevents reading the data and just issues the lock
            String ver = (version <= 0 ? "" : " AND VER=?") + " GROUP BY 1";
            final String LOCK_MODE = " FOR UPDATE";
            //see: http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlocks.html
            //and http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html
            //                final String LOCK_MODE = " LOCK IN SHARE MODE";
            ps = con.prepareStatement(
                    "SELECT 1 FROM " + DatabaseConst.TBL_CONTENT + " WHERE ID=?" + ver + LOCK_MODE);
            ps.setLong(1, id);
            if (version > 0)
                ps.setInt(2, version);
            ps.executeQuery();
            ps.close();
            ps = con.prepareStatement(
                    "SELECT 1 FROM " + DatabaseConst.TBL_CONTENT_DATA + " WHERE ID=?" + ver + LOCK_MODE);
            ps.setLong(1, id);
            if (version > 0)
                ps.setInt(2, version);
            ps.executeQuery();
            ps.close();
            /*ps = con.prepareStatement("SELECT 1 FROM " + DatabaseConst.TBL_CONTENT_BINARY + " WHERE ID=?" + ver + LOCK_MODE);
            ps.setLong(1, id);
            if (version > 0) ps.setInt(2, version);
            ps.executeQuery();*/
            //fulltext table uses MyISAM engine and can not be locked
        } finally {
            Database.closeObjects(MySQLHierarchicalStorage.class, null, ps);
        }
        if (LOG.isDebugEnabled())
            LOG.debug("Locked instances of id #" + id
                    + (version > 0 ? " and version #" + version : " (all versions)"));
        return true;
    } catch (SQLException e) {
        final DBStorage si = StorageManager.getStorageImpl();
        if (si.isDeadlock(e) || si.isQueryTimeout(e)) {
            //                if (LOG.isDebugEnabled()) {
            LOG.warn("Possible (dead)lock detected while locking content tables.");
            //                }
            return false;
        } else {
            //noinspection ThrowableInstanceNeverThrown
            throw new FxDbException(LOG, e, "ex.db.sqlError", e.getMessage()).asRuntimeException();
        }
    }
}

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

/**
 * {@inheritDoc}/*from  w w  w.java2  s .  com*/
 */
@Override
@SuppressWarnings({ "ThrowableInstanceNeverThrown" })
public FxLock extend(Connection con, FxLock lock, long duration) throws FxLockException {
    final UserTicket ticket = FxContext.getUserTicket();
    if (lock.isExpired()) {
        final Object obj = lock.isContentLock() ? lock.getLockedPK() : lock.getLockedResource();
        return _lock(con, lock.getLockType(), obj, duration);
    }
    final boolean allowExtend = lock.getLockType() == FxLockType.Loose
            || (lock.getLockType() == FxLockType.Permanent && (ticket.getUserId() == lock.getUserId()
                    || ticket.isGlobalSupervisor() || ticket.isMandatorSupervisor()));
    if (!allowExtend)
        throw new FxLockException("ex.lock.extend.denied." + (lock.isContentLock() ? "pk" : "resource"),
                (lock.isContentLock() ? lock.getLockedPK() : lock.getLockedResource()));
    PreparedStatement ps = null;
    try {
        if (lock.isContentLock()) {
            ps = con.prepareStatement(
                    "UPDATE " + TBL_LOCKS + " SET EXPIRES_AT=? WHERE LOCK_ID=? AND LOCK_VER=?");
            ps.setLong(2, lock.getLockedPK().getId());
            ps.setInt(3, lock.getLockedPK().getVersion());
        } else {
            ps = con.prepareStatement("UPDATE " + TBL_LOCKS + " SET EXPIRES_AT=? WHERE LOCK_RESOURCE=?");
            ps.setString(2, lock.getLockedResource());
        }
        ps.setLong(1, lock.getExpiresTimestamp() + duration);
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new FxDbException(e, "ex.db.sqlError", e.getMessage()).asRuntimeException();
    } finally {
        Database.closeObjects(GenericLockStorage.class, null, ps);
    }
    Object obj = lock.isContentLock() ? lock.getLockedPK() : lock.getLockedResource();
    return new FxLock(lock.getLockType(), lock.getCreatedTimestamp(), lock.getExpiresTimestamp() + duration,
            lock.getUserId(), obj);
}

From source file:lineage2.gameserver.tables.ClanTable.java

/**
 * Method deleteClanFromDb.//w  w w. java 2 s . com
 * @param clanId int
 */
public void deleteClanFromDb(int clanId) {
    long curtime = System.currentTimeMillis();
    Connection con = null;
    PreparedStatement statement = null;
    try {
        con = DatabaseFactory.getInstance().getConnection();
        statement = con.prepareStatement(
                "UPDATE characters SET clanid=0,title='',pledge_type=0,pledge_rank=0,lvl_joined_academy=0,apprentice=0,leaveclan=? WHERE clanid=?");
        statement.setLong(1, curtime / 1000L);
        statement.setInt(2, clanId);
        statement.execute();
        DbUtils.close(statement);
        statement = con.prepareStatement("DELETE FROM clan_data WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        DbUtils.close(statement);
        statement = con.prepareStatement("DELETE FROM clan_subpledges WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        DbUtils.close(statement);
        statement = con.prepareStatement("DELETE FROM clan_privs WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        DbUtils.close(statement);
        statement = con.prepareStatement("DELETE FROM clan_skills WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
    } catch (Exception e) {
        _log.warn("could not dissolve clan:" + e);
    } finally {
        DbUtils.closeQuietly(con, statement);
    }
}

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

/**
 * {@inheritDoc}/*  w  w w .j a v  a  2s  . c  o m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void assignMetaData(int mandatorId, long contentId) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    final FxEnvironment environment;
    // Security
    FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor);
    environment = CacheAdmin.getEnvironment();
    // check existance
    Mandator mand = environment.getMandator(mandatorId);
    EJBLookup.getContentEngine().load(new FxPK(contentId)); // throws exception if content doesn't exist
    Connection con = null;
    PreparedStatement ps = null;
    String sql;

    try {
        // get Database connection
        con = Database.getDbConnection(); //1             //2            //3        //4
        sql = "UPDATE " + TBL_MANDATORS + " SET METADATA=?, MODIFIED_BY=?, MODIFIED_AT=? WHERE ID=?";
        final long NOW = System.currentTimeMillis();
        ps = con.prepareStatement(sql);
        ps.setLong(1, contentId);
        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(), contentId, true,
                        new LifeCycleInfoImpl(mand.getLifeCycleInfo().getCreatorId(),
                                mand.getLifeCycleInfo().getCreationTime(), ticket.getUserId(), NOW)));
    } catch (SQLException e) {
        EJBUtils.rollback(ctx);
        throw new FxUpdateException(LOG, e, "ex.mandator.assignMetaDataFailed", contentId, mand.getName(),
                mand.getId(), e.getMessage());
    } finally {
        Database.closeObjects(MandatorEngineBean.class, con, ps);
    }
}

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

/**
 * {@inheritDoc}/* ww w .java2  s .  c  o  m*/
 */
@Override
public void move(Connection con, SequencerEngine seq, FxTreeMode mode, long nodeId, long newParentId,
        int newPosition) throws FxApplicationException {
    FxTreeNodeInfo node = getTreeNodeInfo(con, mode, nodeId);
    final FxTreeNodeInfo parentNode = getTreeNodeInfo(con, mode, node.getParentId());
    if (newPosition < 0)
        newPosition = 0;
    else if (newPosition > parentNode.getDirectChildCount())
        newPosition = parentNode.getDirectChildCount() - 1;
    final boolean sameParent = parentNode.getId() == newParentId;
    if (sameParent && node.getPosition() == newPosition)
        return; //nothing to do

    int depthDelta = 0;
    FxTreeNodeInfo newParentNode;
    if (!sameParent) {
        newParentNode = getTreeNodeInfo(con, mode, newParentId);
        depthDelta = (newParentNode.getDepth() + 1) - node.getDepth();
    } else
        newParentNode = parentNode;

    //make room for the node at destination position
    long destLeft, destRight;
    if (!newParentNode.hasChildren() || newPosition == 0) {
        //first
        destLeft = newParentNode.getLeft().longValue() + 1;
    } else if (newParentNode.getDirectChildCount() - 1 == newPosition) {
        //last
        destLeft = newParentNode.getRight().longValue()/* - 2*/;
    } else {
        //middle
        destLeft = getTreeNodeInfoAt(con, mode, newParentNode, newPosition).getLeft().longValue();
    }
    //dest right = dest left + "width" of node
    final long nodeWidth = node.getRight().longValue() - node.getLeft().longValue() + 1;
    destRight = destLeft + nodeWidth - 2;

    PreparedStatement ps = null;
    Statement stmt = null;
    try {
        //open enough space to hold the node
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET LFT=LFT+? WHERE LFT>=?");
        ps.setLong(1, nodeWidth);
        ps.setLong(2, destLeft);
        ps.executeUpdate();
        ps.close();
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET RGT=RGT+? WHERE RGT>=?");
        ps.setLong(1, nodeWidth);
        ps.setLong(2, destRight);
        ps.executeUpdate();
        ps.close();
        //node may have been moved as well, refetch it
        FxTreeNodeInfo movedNode = getTreeNodeInfo(con, mode, nodeId);
        //move the node into the created gap
        final long delta = movedNode.getLeft().longValue() - destLeft;

        ps = con.prepareStatement(
                "UPDATE " + getTable(mode) + " SET LFT=LFT-(?), RGT=RGT-(?) WHERE LFT>=? AND RGT<=?");
        ps.setLong(1, delta);
        ps.setLong(2, delta);
        ps.setLong(3, movedNode.getLeft().longValue());
        ps.setLong(4, movedNode.getRight().longValue());
        ps.executeUpdate();
        //close the gap from the original node
        ps.close();
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET RGT=RGT-? WHERE RGT>?");
        ps.setLong(1, nodeWidth);
        ps.setLong(2, movedNode.getRight().longValue());
        ps.executeUpdate();
        ps.close();
        ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET LFT=LFT-? WHERE LFT>?");
        ps.setLong(1, nodeWidth);
        ps.setLong(2, movedNode.getRight().longValue());
        ps.executeUpdate();

        // Update the parent of the node
        stmt = con.createStatement();
        stmt.addBatch("UPDATE " + getTable(mode) + " SET PARENT=" + newParentId + " WHERE ID=" + nodeId);
        if (mode != FxTreeMode.Live)
            stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=TRUE WHERE ID=" + nodeId);
        stmt.executeBatch();
        stmt.close();

        // Update the childcount of the new and old parent if needed + set dirty flag
        if (!sameParent) {
            FxTreeNodeInfo nodeOldParent = getTreeNodeInfo(con, mode, node.getParentId());
            node = getTreeNodeInfo(con, mode, nodeId);
            stmt = con.createStatement();
            stmt.addBatch("UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT+1 WHERE ID=" + newParentId);
            stmt.addBatch("UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT-1 WHERE ID="
                    + nodeOldParent.getId());
            if (mode != FxTreeMode.Live) {
                stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=TRUE, DEPTH=DEPTH+(" + depthDelta
                        + ") WHERE LFT>=" + node.getLeft().longValue() + " AND " + "RGT<="
                        + node.getRight().longValue());
                stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=TRUE WHERE ID IN(" + node.getParentId()
                        + "," + newParentId + ")");
            }
            stmt.executeBatch();
            stmt.close();
        }
    } catch (SQLException e) {
        throw new FxTreeException(e, "ex.tree.move.failed", node.getId(), newParentId, newPosition,
                e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            //ignore
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            //ignore
        }
    }
}

From source file:net.mindengine.oculus.frontend.service.test.JdbcTestDAO.java

@Override
public long create(Test test) throws Exception {
    PreparedStatement ps = getConnection().prepareStatement(
            "insert into tests (name, description, project_id, author_id, date, mapping, group_id, content, automated) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");

    ps.setString(1, test.getName());/*from w ww .ja va2  s .c o  m*/
    ps.setString(2, test.getDescription());
    ps.setLong(3, test.getProjectId());
    ps.setLong(4, test.getAuthorId());
    ps.setTimestamp(5, new Timestamp(test.getDate().getTime()));
    ps.setString(6, test.getMapping());
    ps.setLong(7, test.getGroupId());
    ps.setString(8, test.getContent());
    ps.setBoolean(9, test.getAutomated());

    logger.info(ps);
    ps.executeUpdate();

    ResultSet rs = ps.getGeneratedKeys();
    Long testId = 0L;
    if (rs.next()) {
        testId = rs.getLong(1);
    }

    /*
     * Increasing the tests_count value for current project
     */
    update("update projects set tests_count=tests_count+1 where id = :id", "id", test.getProjectId());
    return testId;
}

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

/**
 * {@inheritDoc}//from w  w w .  j ava 2  s . co  m
 */
@Override
public void removeIndexForProperty(long propertyId) {
    PreparedStatement ps = null;
    try {
        ps = con.prepareStatement(
                (propertyId < 0 ? CONTENT_FULLTEXT_REMOVE_ALL : CONTENT_FULLTEXT_REMOVE_PROPERTY));
        if (propertyId >= 0)
            ps.setLong(1, propertyId);
        int removed = ps.executeUpdate();
        LOG.info("Removed " + removed + " fulltext entries.");
        commitChanges();
    } catch (SQLException e) {
        throw new FxDbException(e, "ex.db.sqlError", e.getMessage()).asRuntimeException();
    } finally {
        Database.closeObjects(GenericSQLFulltextIndexer.class, ps);
    }
}

From source file:com.bt.aloha.dao.StateInfoDaoTest.java

private SimpleTestInfo insertSimpleTestInfo(String id, String f1, String f2) {
    SimpleTestInfo sti = new SimpleTestInfo(id, f1, f2);
    byte[] stiBytes = null;
    try {/*from   w  w w  .java 2 s .  co m*/
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        ObjectOutputStream oos = new ObjectOutputStream(bos);
        oos.writeObject(sti);
        oos.flush();
        oos.close();
        stiBytes = bos.toByteArray();
    } catch (Exception e) {
        throw new RuntimeException("Unable to serialize SimpleTestInfo", e);
    }
    PreparedStatement s = null;
    try {
        s = connection.prepareStatement("insert into StateInfo"
                + "(object_id, object_type, object_version, last_use_time, is_dead, force_housekeep, object_value) values(?, 'Collection', ?, ?, ?, 0, ?)");
        s.setString(1, id);
        s.setString(2, "1");
        s.setLong(3, new java.util.Date().getTime());
        s.setInt(4, 0);
        s.setBytes(5, stiBytes);
        log.debug("Inserted row in Collection " + "for current SimpleTestInfo");
        s.execute();
        connection.commit();
    } catch (SQLException e) {
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            throw new RuntimeException("Unable to rollback operation on SimpleTestInfo", e);
        }
        throw new RuntimeException("Unable to execute db operation on SimpleTestInfo. op rolledback", e);
    } finally {
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                log.warn("Unable to close prepared statement", e);
            }
    }
    return sti;
}

From source file:com.silverwrist.dynamo.security.AuditReadOps_mysql.java

Map getAuditRecordData(long rec_id) throws DatabaseException {
    Connection conn = null;//w  w w. j  a  v  a2s  . co  m
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try { // get a connection
        conn = getConnection();

        // get our statement and execute it
        stmt = conn.prepareStatement("SELECT ae.event_nsid, ae.event_name, a.on_date, a.uid, a.subid, a.ip, "
                + "a.prop0, a.prop1, a.prop2, a.prop3, a.prop4, a.prop5, a.prop6, a.prop7 "
                + "FROM audit a, auditevent ae WHERE ae.eventid = a.event " + "AND a.record = ?;");
        stmt.setLong(1, rec_id);
        rs = stmt.executeQuery();
        if (!(rs.next())) { // shouldn't happen - throw exception
            DatabaseException de = new DatabaseException(AuditReadOps_mysql.class, "SecurityMessages",
                    "audit.noRecord");
            de.setParameter(0, String.valueOf(rec_id));
            throw de;

        } // end if

        HashMap rc = new HashMap();
        rc.put(HMKEY_NSID, new Integer(rs.getInt(1)));
        rc.put(HMKEY_NAME, rs.getString(2));
        rc.put(HMKEY_DATE, m_utils.getDateTime(rs, 3));
        rc.put(HMKEY_UID, new Integer(rs.getInt(4)));
        rc.put(HMKEY_SUBID, new Integer(rs.getInt(5)));
        rc.put(HMKEY_IP, rs.getString(6));
        Object[] props = new Object[8];
        for (int i = 0; i < props.length; i++) { // get the property string from the result set
            String s = rs.getString(7 + i);
            if (rs.wasNull())
                props[i] = null; // null property
            else { // deserialize the property
                props[i] = m_psz.deserializeProperty(s);
                if (props[i] == null) { // deserialization failed, throw an exception
                    DatabaseException de = new DatabaseException(AuditReadOps_mysql.class, "SecurityMessages",
                            "audit.deserialize");
                    de.setParameter(0, String.valueOf(rec_id));
                    throw de;

                } // end if

            } // end else

        } // end for

        rc.put(HMKEY_PROPERTIES, props);
        return rc;

    } // end try
    catch (SQLException e) { // translate to a general DatabaseException
        throw generalException(e);

    } // end catch
    finally { // shut everything down
        SQLUtils.shutdown(rs);
        SQLUtils.shutdown(stmt);
        SQLUtils.shutdown(conn);

    } // end finally

}