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.flexive.core.storage.genericSQL.GenericTreeStorageSimple.java

/**
 * {@inheritDoc}/*  w  w  w.j  av a2 s.c  o  m*/
 */
@Override
public FxTreeNodeInfo getTreeNodeInfo(Connection con, FxTreeMode mode, long nodeId)
        throws FxApplicationException {
    PreparedStatement ps = null;
    try {
        ps = con.prepareStatement(
                prepareSql(mode, mode == FxTreeMode.Live ? TREE_LIVE_NODEINFO : TREE_EDIT_NODEINFO));
        ps.setBoolean(1, mode == FxTreeMode.Live);
        ps.setLong(2, nodeId);
        ps.setBoolean(3, true);
        ResultSet rs = ps.executeQuery();
        if (rs == null || !rs.next())
            throw new FxNotFoundException("ex.tree.node.notFound", nodeId, mode);
        FxType _type = CacheAdmin.getEnvironment().getType(rs.getLong(15));
        long _stepACL = CacheAdmin.getEnvironment().getStep(rs.getLong(17)).getAclId();
        long _createdBy = rs.getLong(18);
        long _mandator = rs.getLong(19);
        final FxPK reference = new FxPK(rs.getLong(9), rs.getInt(16));
        final List<Long> aclIds = fetchNodeACLs(con, reference);
        return new FxTreeNodeInfoSimple(rs.getLong(1), rs.getLong(2), rs.getLong(5), rs.getLong(6),
                rs.getInt(4), rs.getInt(8), rs.getInt(7), rs.getLong(3), nodeId, rs.getString(12), reference,
                aclIds, mode, rs.getInt(13), rs.getString(10), rs.getLong(11),
                FxPermissionUtils.getPermissionUnion(aclIds, _type, _stepACL, _createdBy, _mandator));
    } catch (SQLException e) {
        throw new FxTreeException(e, "ex.tree.nodeInfo.sqlError", nodeId, e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            LOG.error(e, e);
        }
    }
}

From source file:com.npstrandberg.simplemq.MessageQueueImp.java

private void startQueueMaintainers() {

    // delete 'to old' messages
    final Runnable deleteToOldMessagesRunnable = new Runnable() {
        public void run() {
            logger.info("Delete 'to old' messages: ");

            try {
                PreparedStatement ps = conn.prepareStatement("SELECT id FROM message WHERE time<?");

                ps.setLong(1, System.nanoTime() - TimeUnit.SECONDS.toNanos(queueConfig.getMessageRemoveTime()));

                ResultSet rs = ps.executeQuery();
                List<Long> ids = new ArrayList<Long>();

                while (rs.next()) {
                    ids.add(rs.getLong(1));
                }//  w  ww .  j ava  2  s.c om

                ps.close();
                ps = conn.prepareStatement("DELETE FROM message WHERE id=?");

                for (Long id : ids) {
                    ps.setLong(1, id);
                    ps.executeUpdate();
                }

                ps.close();
            } catch (SQLException e) {
                logger.error(e);
            }
        }
    };

    scheduler.scheduleWithFixedDelay(deleteToOldMessagesRunnable, queueConfig.getDeleteOldMessagesThreadDelay(),
            queueConfig.getDeleteOldMessagesThreadDelay(), TimeUnit.SECONDS);

    // revive messages that has been read, but not deleted.
    final Runnable reviveRunnable = new Runnable() {
        public void run() {
            logger.info("Do revieving: ");

            try {
                PreparedStatement ps = conn
                        .prepareStatement("SELECT id FROM message WHERE time<? AND read=true");

                ps.setLong(1, System.nanoTime() - TimeUnit.SECONDS.toNanos(queueConfig.getMessageReviveTime()));

                ResultSet rs = ps.executeQuery();
                List<Long> ids = new ArrayList<Long>();

                while (rs.next()) {
                    ids.add(rs.getLong(1));
                }

                ps.close();
                System.out.println("" + ids.size() + " messages!");
                ps = conn.prepareStatement("UPDATE message SET read=? WHERE id=?");

                for (Long id : ids) {
                    ps.setBoolean(1, false);
                    ps.setLong(2, id);
                    ps.executeUpdate();
                }

                ps.close();
            } catch (SQLException e) {
                logger.error(e);
            }
        }
    };

    scheduler.scheduleWithFixedDelay(reviveRunnable, queueConfig.getReviveNonDeletedMessagsThreadDelay(),
            queueConfig.getReviveNonDeletedMessagsThreadDelay(), TimeUnit.SECONDS);

}

From source file:com.buckwa.dao.impl.excise4.Form23DaoImpl.java

@Override
public void update(final Form23 form23) {
    logger.info("update");
    String user = "";
    try {// w  w w .j av a 2s .c om
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;
    final Timestamp currentDate = new Timestamp(System.currentTimeMillis());
    if (BeanUtils.isEmpty(form23.getCodeNo())) {
        form23.setCodeNo("" + System.currentTimeMillis());
    }

    String sqlform23 = "UPDATE `form23` SET `factory_id`=?,`update_date`=?,`update_by`=? "
            + " ,`totalScrap`=?,`part4flag`=?,`part4fullName`=?,`part4Date`=? ,"
            + " `part5flag`=?,`part5licenseNo`=?,`part5licenseDate`=?,`part5billingNo`=?,`part5billingDate`=?,`part5amount`=?,`part5Date`=?,"
            + " `part6flag`=?,`part6Date`=?,`step`=?,`codeNo`=? " + " WHERE `form23_id`=?";
    logger.info("update: " + sqlform23);
    this.jdbcTemplate.update(sqlform23,
            new Object[] { form23.getFactory().getFactoryId(), currentDate, userName, form23.getTotalScrap(),
                    form23.getPart4flag(), form23.getPart4fullName(), currentDate, form23.getPart5flag(),
                    form23.getPart5licenseNo(), getDateFormString(form23.getPart5licenseDate()),
                    form23.getPart5billingNo(), getDateFormString(form23.getPart5billingDate()),
                    form23.getPart5amount(), currentDate, form23.getPart6flag(), currentDate, form23.getStep(),
                    form23.getCodeNo(), form23.getForm23Id() });

    //ID PRODUCT

    String productSql = "UPDATE `form23_product` SET `seq`=?,`productName`=?,`size`=?,`bandColor`=?,`backgroudColor`=?,`licenseNo`=?"
            + ",`grossnumber200`=?,`grossnumber400`=?,`corkScrap`=?,`totalScrap`=?,`update_date`=?,`update_by`=? "
            + "WHERE `product_id`=?";

    final StringBuilder psql = new StringBuilder();
    psql.append(
            "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
            .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

    List<Product> products = form23.getProductList();
    if (products != null) {
        for (final Product p : products) {
            if (BeanUtils.isEmpty(p.getProcuctId())) {
                logger.info("create");
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(psql.toString(),
                                Statement.RETURN_GENERATED_KEYS);
                        long returnidform23 = form23.getForm23Id();
                        ps.setLong(1, returnidform23);
                        ps.setString(2, p.getSeq());
                        ps.setString(3, p.getProductName());
                        ps.setString(4, p.getSize());
                        ps.setString(5, p.getBandColor());
                        ps.setString(6, p.getBackgroudColor());
                        ps.setString(7, p.getLicenseNo());
                        ps.setBigDecimal(8, p.getGrossnumber200());
                        ps.setBigDecimal(9, p.getGrossnumber400());
                        ps.setBigDecimal(10, p.getCorkScrap());
                        ps.setBigDecimal(11, p.getTotalScrap());

                        ps.setTimestamp(12, currentDate);
                        ps.setString(13, userName);
                        ps.setTimestamp(14, currentDate);
                        ps.setString(15, userName);
                        return ps;
                    }

                }, keyHolder);

                long returnidproduct = keyHolder.getKey().longValue();
                p.setProcuctId(returnidproduct);
                logger.info("returnidproduct : " + returnidproduct);
            } else {
                logger.info("update");
                this.jdbcTemplate.update(productSql,
                        new Object[] { p.getSeq(), p.getProductName(), p.getSize(), p.getBandColor(),
                                p.getBackgroudColor(), p.getLicenseNo(), p.getGrossnumber200(),
                                p.getGrossnumber400(), p.getCorkScrap(), p.getTotalScrap(), currentDate,
                                userName, p.getProcuctId() });
            }
        }
    }

}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

@Test
public void testInsertQuery() throws SQLException {
    String INSERT = "Insert into " + TABLE_NAME + "(column1,column2,column3) values (?,?,?);";

    PreparedStatement prep = conn.prepareStatement(INSERT);

    prep.setInt(1, 2);/*  w  w w. j av  a  2s .c om*/
    prep.setLong(2, 33445);
    prep.setString(3, "testInsertQuery");
    int ret = prep.executeUpdate();

    assertTrue(ret == 1);

    prep = conn.prepareStatement(
            "select column1,column2,column3 from " + TABLE_NAME + " where column1=? and column2=?");
    prep.setInt(1, 2);
    prep.setLong(2, 33445);

    ResultSet rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(2, rs.getInt("column1"));
    assertEquals(33445, rs.getLong("column2"));
    assertEquals("testInsertQuery", rs.getString("column3"));
    prep.close();
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void update(final Form24 form24) {
    logger.info("update");
    String user = "";
    try {/*from   w w  w  .j  a v a  2s.  c o m*/
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;
    final Timestamp currentDate = new Timestamp(System.currentTimeMillis());

    String sqlform24 = "UPDATE `form24` SET `factory_id`=?,`update_date`=?,`update_by`=? WHERE `form24_id`=?";
    logger.info("update: " + sqlform24);
    this.jdbcTemplate.update(sqlform24,
            new Object[] { form24.getFactory().getFactoryId(), currentDate, userName, form24.getForm24Id() });

    //ID PRODUCT

    String productSql = "UPDATE `form24_product` SET `seq`=?,`productName`=?,`size`=?,`bandColor`=?,`backgroudColor`=?,`licenseNo`=?"
            + ",`grossnumber200`=?,`grossnumber400`=?,`corkScrap`=?,`totalScrap`=?,`update_date`=?,`update_by`=? "
            + "WHERE `product_id`=?";

    final StringBuilder psql = new StringBuilder();
    psql.append(
            "INSERT INTO `form24_product`(`form24_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
            .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

    List<Product> products = form24.getProductList();
    if (products != null) {
        for (final Product p : products) {
            if (BeanUtils.isEmpty(p.getProcuctId())) {
                logger.info("create");
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(psql.toString(),
                                Statement.RETURN_GENERATED_KEYS);
                        long returnidform24 = form24.getForm24Id();
                        ps.setLong(1, returnidform24);
                        ps.setString(2, p.getSeq());
                        ps.setString(3, p.getProductName());
                        ps.setString(4, p.getSize());
                        ps.setString(5, p.getBandColor());
                        ps.setString(6, p.getBackgroudColor());
                        ps.setString(7, p.getLicenseNo());
                        ps.setBigDecimal(8, p.getGrossnumber200());
                        ps.setBigDecimal(9, p.getGrossnumber400());
                        ps.setBigDecimal(10, p.getCorkScrap());
                        ps.setBigDecimal(11, p.getTotalScrap());

                        ps.setTimestamp(12, currentDate);
                        ps.setString(13, userName);
                        ps.setTimestamp(14, currentDate);
                        ps.setString(15, userName);
                        return ps;
                    }

                }, keyHolder);

                long returnidproduct = keyHolder.getKey().longValue();
                p.setProcuctId(returnidproduct);
                logger.info("returnidproduct : " + returnidproduct);
            } else {
                logger.info("update");
                this.jdbcTemplate.update(productSql,
                        new Object[] { p.getSeq(), p.getProductName(), p.getSize(), p.getBandColor(),
                                p.getBackgroudColor(), p.getLicenseNo(), p.getGrossnumber200(),
                                p.getGrossnumber400(), p.getCorkScrap(), p.getTotalScrap(), currentDate,
                                userName, p.getProcuctId() });
            }
        }
    }

}

From source file:mayoapp.migrations.V0400_2024__move_order_items_to_own_table.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);// w ww. ja va  2  s. co  m

    Statement queryStatement = connection.createStatement();
    ResultSet data = queryStatement.executeQuery("SELECT * from purchase_order");

    List<Order> orders = Lists.newArrayList();
    List<OrderItem> orderItems = Lists.newArrayList();

    ObjectMapper mapper = new ObjectMapper();

    while (data.next()) {
        Order order = new Order();
        order.setId((UUID) data.getObject("entity_id"));

        String orderDataString = data.getString("order_data");
        Map<String, Object> orderData = mapper.readValue(orderDataString,
                new TypeReference<Map<String, Object>>() {
                });

        List<Map<String, Object>> items = (List<Map<String, Object>>) orderData.get("items");

        for (Map<String, Object> item : items) {
            OrderItem orderItem = new OrderItem();

            orderItem.setId(UUID.randomUUID());
            orderItem.setOrderId(order.getId());

            if (item.containsKey("id") && String.class.isAssignableFrom(item.get("id").getClass())) {
                orderItem.setPurchasableId(UUID.fromString((String) item.get("id")));
            }
            orderItem.setType((String) item.get("type"));
            orderItem.setTitle((String) item.get("title"));
            orderItem.setQuantity(((Integer) item.get("quantity")).longValue());
            orderItem.setUnitPrice(BigDecimal.valueOf((Double) item.get("unitPrice")));
            orderItem.setItemTotal(BigDecimal.valueOf((Double) item.get("itemTotal")));
            if (item.containsKey("vatRate")) {
                orderItem.setVatRate(BigDecimal.valueOf((Double) item.get("vatRate")));
            }

            if (item.containsKey("addons")) {
                orderItem.addData("addons", convertAddonsToMap((List<Map<String, Object>>) item.get("addons")));
            }

            orderItems.add(orderItem);
        }

        orderData.remove("items");
        order.setOrderData(orderData);
        orders.add(order);
    }

    queryStatement.close();

    // 1. Update orders

    PreparedStatement updateOrders = connection
            .prepareStatement("UPDATE purchase_order SET order_data = CAST (? AS json) WHERE entity_id =?");

    for (Order order : orders) {
        updateOrders.setObject(1, mapper.writeValueAsString(order.getOrderData()));
        updateOrders.setObject(2, order.getId());
        updateOrders.addBatch();
    }

    updateOrders.executeBatch();

    // 2. Insert items

    PreparedStatement insertItems = connection.prepareStatement(
            "INSERT INTO purchase_order_item (id, order_id, purchasable_id, type, title, quantity, unit_price, "
                    + "item_total, vat_rate, data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CAST (? as json))");

    for (OrderItem item : orderItems) {
        insertItems.setObject(1, item.getId());
        insertItems.setObject(2, item.getOrderId());
        insertItems.setObject(3, item.getPurchasableId());
        insertItems.setString(4, item.getType());
        insertItems.setString(5, item.getTitle());
        insertItems.setLong(6, item.getQuantity());
        insertItems.setBigDecimal(7, item.getUnitPrice());
        insertItems.setBigDecimal(8, item.getItemTotal());
        insertItems.setBigDecimal(9, item.getVatRate());
        insertItems.setString(10, mapper.writeValueAsString(item.getData()));
        insertItems.addBatch();
    }

    insertItems.executeBatch();
}

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

/**
 * {@inheritDoc}/*from   w  w w . j a  v a2  s .co  m*/
 */
@Override
protected void afterNodeRemoved(Connection con, FxTreeNodeInfo nodeInfo, boolean removeChildren)
        throws FxApplicationException {
    //close the gap between left and right:
    PreparedStatement ps = null;
    try {
        if (!removeChildren) {
            ps = con.prepareStatement("UPDATE " + getTable(nodeInfo.getMode())
                    + " SET LFT=LFT-1, RGT=RGT-1 WHERE LFT BETWEEN ? AND ?");
            ps.setLong(1, nodeInfo.getLeft().longValue());
            ps.setLong(2, nodeInfo.getRight().longValue());
            ps.executeUpdate();
            ps.close();
            ps = con.prepareStatement("UPDATE " + getTable(nodeInfo.getMode()) + " SET LFT=LFT-2 WHERE LFT>?");
            ps.setLong(1, nodeInfo.getRight().longValue());
            ps.executeUpdate();
            ps.close();
            ps = con.prepareStatement("UPDATE " + getTable(nodeInfo.getMode()) + " SET RGT=RGT-2 WHERE RGT>?");
            ps.setLong(1, nodeInfo.getRight().longValue());
            ps.executeUpdate();
        } else {
            final long gap = nodeInfo.getRight().longValue() - nodeInfo.getLeft().longValue() + 1;
            ps = con.prepareStatement("UPDATE " + getTable(nodeInfo.getMode()) + " SET LFT=LFT-? WHERE LFT>?");
            ps.setLong(1, gap);
            ps.setLong(2, nodeInfo.getRight().longValue());
            ps.executeUpdate();
            ps.close();
            ps = con.prepareStatement("UPDATE " + getTable(nodeInfo.getMode()) + " SET RGT=RGT-? WHERE RGT>?");
            ps.setLong(1, gap);
            ps.setLong(2, nodeInfo.getRight().longValue());
            ps.executeUpdate();
        }
    } catch (SQLException e) {
        throw new FxTreeException(e, "ex.tree.closeGap.error", nodeInfo.getId(), nodeInfo.getLeft(),
                nodeInfo.getRight(), e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            LOG.error(e, e);
        }
    }
}

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

private String getFileAsString(Connection conn, IRepositoryFile file) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;//from   ww  w  .java  2s  .  c o  m
    InputStream blobStream = null;
    InputStreamReader reader = null;
    StringWriter os = null;
    try {
        // Querying blob
        stmt = conn.prepareStatement("SELECT rf.file_content " //$NON-NLS-1$
                + "FROM rep_files rf " //$NON-NLS-1$
                + "WHERE rf.file_id = ? "); //$NON-NLS-1$
        stmt.setLong(1, file.getUID().rawId());
        rs = stmt.executeQuery();
        if (rs.next()) {
            // Retrieving blob input stream
            blobStream = rs.getBinaryStream(1);
            if (blobStream == null) {
                return ""; //$NON-NLS-1$
            }
            reader = new InputStreamReader(blobStream);

            // Opening output file
            os = new StringWriter(10240);

            // Large 10K buffer for efficient read
            char[] buffer = new char[10240];
            int bytesRead = 0;
            while ((bytesRead = reader.read(buffer)) >= 0) {
                os.write(buffer, 0, bytesRead);
            }
            return os.toString();
        } else {
            throw new ErrorException(VCSMessages.getString("files.notFound")); //$NON-NLS-1$
        }
    } catch (IOException e) {
        throw new ErrorException(VCSMessages.getString("files.readRepositoryProblem"), //$NON-NLS-1$
                e);
    } finally {
        safeClose(os);
        safeClose(blobStream);
        safeClose(reader);
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
    }
}

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

@Override
public Long createTestGroup(TestGroup testGroup) throws Exception {
    PreparedStatement ps = getConnection()
            .prepareStatement("insert into test_groups (name, description, project_id) values (?, ?, ?)");
    logger.info(ps);//from   w  w w .  j a  v a2s  .  c om
    ps.setString(1, testGroup.getName());
    ps.setString(2, testGroup.getDescription());
    ps.setLong(3, testGroup.getProjectId());
    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();

    Long groupId = 0L;
    if (rs.next()) {
        groupId = rs.getLong(1);
    }
    return groupId;
}

From source file:com.amazonbird.announce.ProductMgrImpl.java

public void updateProduct(Product product) {
    Connection connection = null;
    PreparedStatement ps = null;

    try {//from  ww w .  j  a  v  a2s  .  co  m
        connection = dbMgr.getConnection();
        ps = connection.prepareStatement(UPDATE_PRODUCT);
        ps.setString(1, product.getName());
        ps.setDouble(2, product.getPrice());
        ps.setString(3, product.getDestination());
        ps.setString(4, product.getAlternativeDestionation());
        ps.setString(5, product.getLocale());
        ps.setLong(6, product.getAnnouncerId());
        ps.setLong(7, product.getId());
        ps.executeUpdate();
        logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString());
    } catch (SQLException ex) {
        logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex);
    } finally {
        dbMgr.closeResources(connection, ps, null);
    }
}