Example usage for java.sql CallableStatement setInt

List of usage examples for java.sql CallableStatement setInt

Introduction

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

Prototype

void setInt(String parameterName, int x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java int value.

Usage

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public List<Item> getItems(final int auctionUid, final int categoryuid, final int start, final int length,
        final String sort, final String dir) {
    List<Item> objs = Lists.newArrayList();

    Connection conn = null;/*  w  w w  .j a  va  2  s  . co  m*/
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETITEMS (?,?,?,?,?,?)}");
        stmt.setInt(1, auctionUid);
        stmt.setInt(2, categoryuid);
        stmt.setInt(3, start);
        stmt.setInt(4, length);
        stmt.setString(5, sort);
        stmt.setString(6, dir);

        rs = stmt.executeQuery();

        while (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).setDescription(rs.getString("DESCRIPTION"))
                    .setCategory(rs.getString("CATEGORY")).setSeller(rs.getString("SELLER"))
                    .setValPrice(rs.getDouble("VALPRICE")).setMinPrice(rs.getDouble("MINPRICE"))
                    .setIncPrice(rs.getDouble("INCPRICE")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT")).setUrl(rs.getString("URL"))
                    .setMultiSale(rs.getBoolean("MULTI"));

            objs.add(builder.build());
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("ITEM [method:{} result:{}]", new Object[] { "get", objs.size() });
    }

    return ImmutableList.copyOf(objs);
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public List<Auction> getAuctions(final int start, final int length, final String sort, final String dir) {
    List<Auction> objs = Lists.newArrayList();

    Connection conn = null;/*  w  w  w.  j  a  v a 2 s. c o  m*/
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETAUCTIONS (?,?,?,?)}");
        stmt.setInt(1, start);
        stmt.setInt(2, length);
        stmt.setString(3, sort);
        stmt.setString(4, dir);

        rs = stmt.executeQuery();

        while (rs.next()) {
            AuctionBuilder builder = Auction.newBuilder().setUid(rs.getInt("UID"))
                    .setName(rs.getString("NAME"));

            Date startdate = rs.getDate("STARTDATE");
            if (startdate != null) {
                builder.setStartDate(startdate.getTime());
            }

            Date enddate = rs.getDate("ENDDATE");
            if (enddate != null) {
                builder.setEndDate(enddate.getTime());
            }

            builder.setLogoUrl(rs.getString("LOGOURL"));
            builder.setColor(rs.getString("COLOR"));

            objs.add(builder.build());
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "get", objs.size() });
    }

    return ImmutableList.copyOf(objs);
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int editCategory(final Category category) {
    int uid = -1;

    Connection conn = null;/* w  w  w  . j av a2s  . co m*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITCATEGORY (?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, category.getAuctionUid());
        stmt.setString(3, category.getName());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));

        uid = -1;
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("CATEGORY [method:{} result:{}]", new Object[] { "edit", uid });
    }

    return uid;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int editUser(final User user) {
    int uid = -1;

    Connection conn = null;/*from   w w w  . j a  v  a2  s .  co  m*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITUSER (?,?,?,?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, user.getAuctionUid());
        stmt.setString(3, user.getBidderNumber());
        stmt.setString(4, user.getFirstName());
        stmt.setString(5, user.getLastName());
        stmt.setString(6, user.getPasswordHash());
        stmt.setInt(7, user.getRole().getId());
        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
        uid = -1;
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("USER [method:{} result:{}]", new Object[] { "add", uid });
    }

    return uid;
}

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#getServicesByAttribute(java.lang.String, int)
 *//*from w ww  .j  av a  2  s.  c om*/
public synchronized List<Object[]> getServicesByAttribute(final String attribute, final int startRow)
        throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#getServicesByAttribute(final String attribute, final int startRow) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", startRow);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        StringBuilder sBuilder = new StringBuilder();

        if (StringUtils.split(attribute, " ").length >= 2) {
            for (String str : StringUtils.split(attribute, " ")) {
                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", str);
                }

                sBuilder.append("+" + str);
                sBuilder.append(" ");
            }

            if (DEBUG) {
                DEBUGGER.debug("StringBuilder: {}", sBuilder);
            }
        } else {
            sBuilder.append("+" + attribute);
        }

        stmt = sqlConn.prepareCall("{CALL getServiceByAttribute(?, ?)}");
        stmt.setString(1, sBuilder.toString().trim());
        stmt.setInt(2, startRow);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<Object[]>();

                while (resultSet.next()) {
                    Object[] data = new Object[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // SERVICE_TYPE
                            resultSet.getInt(3) / 0 * 100 };

                    if (DEBUG) {
                        DEBUGGER.debug("Value: {}", data);
                    }

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int editItem(final Item item) {
    int uid = -1;

    Connection conn = null;//ww w  .  j  a  va 2 s .c  om
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITITEM (?,?,?,?,?,?,?,?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, item.getAuctionUid());
        stmt.setString(3, item.getItemNumber());
        stmt.setString(4, item.getName());
        stmt.setString(5, item.getDescription());
        stmt.setString(6, item.getCategory());
        stmt.setString(7, item.getSeller());
        stmt.setDouble(8, item.getValPrice());
        stmt.setDouble(9, item.getMinPrice());
        stmt.setDouble(10, item.getIncPrice());
        stmt.setString(11, item.getUrl());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));

        uid = -1;
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("ITEM [method:{} result:{}]", new Object[] { "edit", uid });
    }

    return uid;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public double addFund(final Fund fund) {
    int uid = -1;

    Connection conn = null;//from  w  w  w.  j  ava  2  s.  c om
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITFUND (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, fund.getAuctionUid());
        stmt.setInt(3, fund.getUserUid());
        stmt.setDouble(4, fund.getBidPrice());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("FUND [method:{} result:{}]", new Object[] { "edit", uid });
    }

    double sum = 0.0;

    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETFUNDSUM (?)}");
        stmt.setInt(1, fund.getAuctionUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            sum = rs.getDouble(1);
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("FUND [method:{} result:{}]", new Object[] { "sum", sum });
    }

    return sum;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int addDevice(final Device device) {
    int uid = -1;

    Connection conn = null;/* w  w  w  . j  av  a  2  s.  c o  m*/
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_REGISTERDEVICE (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, device.getUserUid());
        stmt.setString(3, device.getDeviceId());
        stmt.setString(4, device.getDeviceType());
        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("DEVICE [method:{} result:{}]", new Object[] { "edit", uid });
    }

    return uid;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public Item addWatch(final Watch watch) {
    Item obj = null;/*  ww w.j a va2s .  co m*/

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITWATCH (?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, watch.getItemUid());
        stmt.setInt(3, watch.getUserUid());

        stmt.execute();
        stmt.close(); // close statement to prevent leak

        stmt = conn.prepareCall("{call SP_GETITEM (?)}");
        stmt.setInt(1, watch.getItemUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT"));

            obj = builder.build();
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("WATCH [method:{} result:{}]",
                new Object[] { "add", obj != null ? obj.toString() : "[error]" });
    }

    return obj;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public Item addBid(final Bid bid) {
    Item obj = null;//from   w  w w  . ja v a  2s  .c o m

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITBID (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, bid.getItemUid());
        stmt.setInt(3, bid.getUserUid());
        stmt.setDouble(4, bid.getBidPrice());

        stmt.execute();
        stmt.close(); // close statement to prevent leak

        stmt = conn.prepareCall("{call SP_GETITEM (?)}");
        stmt.setInt(1, bid.getItemUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT"));

            obj = builder.build();
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("BID [method:{} result:{}]",
                new Object[] { "add", obj != null ? obj.toString() : "[error]" });
    }

    return obj;
}