List of usage examples for java.sql CallableStatement setInt
void setInt(String parameterName, int x) throws SQLException;
int
value. 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; }