Java tutorial
/* * Copyright (c) 2010 mobiaware.com. * * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except * in compliance with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software distributed under the License * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express * or implied. See the License for the specific language governing permissions and limitations under * the License. */ package com.mobiaware.auction.data.impl; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.Date; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.List; import javax.inject.Inject; import javax.sql.DataSource; import org.apache.commons.dbutils.DbUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.base.Throwables; import com.google.common.collect.ImmutableList; import com.google.common.collect.Lists; import com.mobiaware.auction.auth.Roles; import com.mobiaware.auction.data.DataService; import com.mobiaware.auction.model.Auction; import com.mobiaware.auction.model.AuctionBuilder; import com.mobiaware.auction.model.Bid; import com.mobiaware.auction.model.BidBuilder; import com.mobiaware.auction.model.Category; import com.mobiaware.auction.model.CategoryBuilder; import com.mobiaware.auction.model.Device; import com.mobiaware.auction.model.DeviceBuilder; import com.mobiaware.auction.model.Fund; import com.mobiaware.auction.model.FundBuilder; import com.mobiaware.auction.model.Item; import com.mobiaware.auction.model.ItemBuilder; import com.mobiaware.auction.model.User; import com.mobiaware.auction.model.UserBuilder; import com.mobiaware.auction.model.Watch; import com.mobiaware.auction.model.WatchBuilder; public class MySqlDataServiceImpl implements DataService { private static final String NAME = MySqlDataServiceImpl.class.getSimpleName(); private static final Logger LOG = LoggerFactory.getLogger(NAME); private final DataSource _dataSource; @Inject public MySqlDataServiceImpl(final DataSource dataSource) { _dataSource = dataSource; } @Override public boolean healthCheck() { Connection conn = null; CallableStatement stmt = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("select now()"); stmt.execute(); return true; } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, null); } return false; } @Override public String signin(final int userUid) { Connection conn = null; CallableStatement stmt = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_SIGNIN (?,?)}"); stmt.setInt(1, userUid); stmt.registerOutParameter(2, Types.VARCHAR); stmt.execute(); return stmt.getString(2); } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, null); } return null; } @Override public void signout(final int userUid) { Connection conn = null; CallableStatement stmt = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_SIGNOUT (?)}"); stmt.setInt(1, userUid); stmt.execute(); } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, null); } } @Override public Auction getAuction(final int uid) { Auction obj = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETAUCTION (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (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")); obj = 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", obj != null ? obj.toString() : "[error]" }); } return obj; } @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; 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); } @Override public int getAuctionCount() { int count = 0; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETAUCTIONCOUNT ()}"); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "count", count }); } return count; } @Override public int editAuction(final Auction auction) { int uid = -1; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_EDITAUCTION (?,?,?,?,?,?)}"); stmt.registerOutParameter(1, Types.INTEGER); stmt.setString(2, auction.getName()); stmt.setDate(3, new Date(auction.getStartDate())); stmt.setDate(4, new Date(auction.getEndDate())); stmt.setString(5, auction.getLogoUrl()); stmt.setString(6, auction.getColor()); 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("AUCTION [method:{} result:{}]", new Object[] { "edit", uid }); } return uid; } @Override public int deleteAuction(final int uid) { int result = -1; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_DELETEAUCTION (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { result = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "delete", result }); } return result; } @Override public Category getCategory(final int uid) { Category obj = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETCATEGORY (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); while (rs.next()) { CategoryBuilder builder = Category.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setName(rs.getString("NAME")); obj = builder.build(); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("CATEGORY [method:{} result:{}]", new Object[] { "get", obj != null ? obj.toString() : "[error]" }); } return obj; } @Override public List<Category> getCategories(final int auctionUid, final int start, final int length, final String sort, final String dir) { List<Category> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETCATEGORIES (?,?,?,?,?)}"); stmt.setInt(1, auctionUid); stmt.setInt(2, start); stmt.setInt(3, length); stmt.setString(4, sort); stmt.setString(5, dir); rs = stmt.executeQuery(); while (rs.next()) { CategoryBuilder builder = Category.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setName(rs.getString("NAME")); objs.add(builder.build()); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("CATEGORY [method:{} result:{}]", new Object[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public int getCategoryCount(final int auctionUid) { int count = 0; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETCATEGORYCOUNT (?)}"); stmt.setInt(1, auctionUid); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("CATEGORY [method:{} result:{}]", new Object[] { "count", count }); } return count; } @Override public int editCategory(final Category category) { int uid = -1; Connection conn = null; 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; } @Override public int deleteCategory(final int uid) { int result = -1; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_DELETECATEGORY (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { result = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("CATEGORY [method:{} result:{}]", new Object[] { "delete", result }); } return result; } @Override public int addDevice(final Device device) { int uid = -1; Connection conn = null; 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; } @Override public List<Device> getDevicesByUser(final int userUid) { List<Device> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETDEVICESFORUSER (?)}"); stmt.setInt(1, userUid); rs = stmt.executeQuery(); while (rs.next()) { DeviceBuilder builder = Device.newBuilder().setUid(rs.getInt("UID")) .setUserUid(rs.getInt("USERUID")).setDeviceId(rs.getString("DEVICEID")) .setDeviceType(rs.getString("DEVICETYPE")); objs.add(builder.build()); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("DEVICE [method:{} result:{}]", new Object[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public double addFund(final Fund fund) { int uid = -1; Connection conn = null; 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; } @Override public Item getItem(final int uid) { Item obj = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETITEM (?)}"); stmt.setInt(1, uid); 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")).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")); obj = 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", obj != null ? obj.toString() : "[error]" }); } return obj; } @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; 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); } @Override public int getItemCount(final int auctionUid, final int categoryuid) { int count = 0; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETITEMCOUNT (?,?)}"); stmt.setInt(1, auctionUid); stmt.setInt(2, categoryuid); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("ITEM [method:{} result:{}]", new Object[] { "count", count }); } return count; } @Override public int editItem(final Item item) { int uid = -1; Connection conn = null; 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; } @Override public int deleteItem(final int uid) { int result = -1; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_DELETEITEM (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { result = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("ITEM [method:{} result:{}]", new Object[] { "delet", result }); } return result; } @Override public List<Item> getItemsUpdatesOnly(final int auctionUid) { List<Item> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETITEMUPDATES (?)}"); stmt.setInt(1, auctionUid); rs = stmt.executeQuery(); while (rs.next()) { ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER")) .sertCurPrice(rs.getDouble("CURPRICE")).setWinner(rs.getString("WINNER")) .setBidCount(rs.getInt("BIDCOUNT")).setWatchCount(rs.getInt("WATCHCOUNT")); 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); } @Override public List<Bid> getBidsByItem(final int itemUid) { List<Bid> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETBIDSBYITEM (?)}"); stmt.setInt(1, itemUid); rs = stmt.executeQuery(); while (rs.next()) { BidBuilder builder = Bid.newBuilder().setUid(rs.getInt("UID")).setItemUid(rs.getInt("ITEMUID")) .setUserUid(rs.getInt("USERUID")).setBidPrice(rs.getDouble("BIDPRICE")) .setBidDate(rs.getDate("BIDDATE").getTime()); objs.add(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[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public Item addBid(final Bid bid) { Item obj = null; 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; } @Override public List<Watch> getWatchesByItem(final int itemUid) { List<Watch> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETWATCHESBYITEM (?)}"); stmt.setInt(1, itemUid); rs = stmt.executeQuery(); while (rs.next()) { WatchBuilder builder = Watch.newBuilder().setUid(rs.getInt("UID")).setUserUid(rs.getInt("USERUID")) .setItemUid(rs.getInt("ITEMUID")); objs.add(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[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public Item addWatch(final Watch watch) { Item obj = null; 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; } @Override public Bid getMaxBidByItem(final int itemUid) { Bid obj = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETMAXBIDBYITEM (?)}"); stmt.setInt(1, itemUid); rs = stmt.executeQuery(); if (rs.next()) { BidBuilder builder = Bid.newBuilder().setUid(rs.getInt("UID")).setItemUid(rs.getInt("ITEMUID")) .setUserUid(rs.getInt("USERUID")).setBidPrice(rs.getDouble("BIDPRICE")) .setBidDate(rs.getDate("BIDDATE").getTime()); 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[] { "get", obj != null ? obj.toString() : "[error]" }); } return obj; } @Override public User getUser(final int uid) { User obj = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETUSER (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { UserBuilder builder = User.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setBidderNumber(rs.getString("BIDDERNUMBER")) .setFirstName(rs.getString("FIRSTNAME")).setLastName(rs.getString("LASTNAME")) .setRole(Roles.getId(rs.getInt("ROLE"))); obj = builder.build(); obj.setPasswordHash(rs.getString("PASSWORDHASH")); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("USER [method:{} result:{}]", new Object[] { "get", obj != null ? obj.toString() : "[error]" }); } return obj; } @Override public List<User> getUsers(final int auctionUid, final int start, final int length, final String sort, final String dir) { List<User> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETUSERS (?,?,?,?,?)}"); stmt.setInt(1, auctionUid); stmt.setInt(2, start); stmt.setInt(3, length); stmt.setString(4, sort); stmt.setString(5, dir); rs = stmt.executeQuery(); while (rs.next()) { UserBuilder builder = User.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setBidderNumber(rs.getString("BIDDERNUMBER")) .setFirstName(rs.getString("FIRSTNAME")).setLastName(rs.getString("LASTNAME")) .setRole(Roles.getId(rs.getInt("ROLE"))); User obj = builder.build(); obj.setPasswordHash(rs.getString("PASSWORDHASH")); objs.add(obj); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("USER [method:{} result:{}]", new Object[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public int getUserCount(final int auctionUid) { int count = 0; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETUSERCOUNT (?)}"); stmt.setInt(1, auctionUid); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("USER [method:{} result:{}]", new Object[] { "count", count }); } return count; } @Override public int editUser(final User user) { int uid = -1; Connection conn = null; 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; } @Override public int deleteUser(final int uid) { int result = -1; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_DELETEUSER (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { result = rs.getInt(1); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("USER [method:{} result:{}]", new Object[] { "delete", result }); } return result; } @Override public User getUserByBidderNumber(final String biddernumber) { User obj = null; Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETUSERBYBIDDERNUMBER (?)}"); stmt.setString(1, biddernumber); rs = stmt.executeQuery(); if (rs.next()) { UserBuilder builder = User.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setBidderNumber(rs.getString("BIDDERNUMBER")) .setFirstName(rs.getString("FIRSTNAME")).setLastName(rs.getString("LASTNAME")) .setRole(Roles.getId(rs.getInt("ROLE"))); obj = builder.build(); obj.setPasswordHash(rs.getString("PASSWORDHASH")); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("USER [method:{} result:{}]", new Object[] { "get", obj != null ? obj.toString() : "[error]" }); } return obj; } @Override public List<Bid> getBidsByUser(final int userUid) { List<Bid> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETBIDSBYUSER (?)}"); stmt.setInt(1, userUid); rs = stmt.executeQuery(); while (rs.next()) { BidBuilder builder = Bid.newBuilder().setUid(rs.getInt("UID")).setItemUid(rs.getInt("ITEMUID")) .setUserUid(rs.getInt("USERUID")).setBidPrice(rs.getDouble("BIDPRICE")) .setBidDate(rs.getDate("BIDDATE").getTime()); objs.add(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[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public List<Watch> getWatchesByUser(final int userUid) { List<Watch> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETWATCHESBYUSER (?)}"); stmt.setInt(1, userUid); rs = stmt.executeQuery(); while (rs.next()) { WatchBuilder builder = Watch.newBuilder().setUid(rs.getInt("UID")).setUserUid(rs.getInt("USERUID")) .setItemUid(rs.getInt("ITEMUID")); objs.add(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[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } @Override public List<Fund> getFundsByUser(final int userUid) { List<Fund> objs = Lists.newArrayList(); Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETFUNDSBYUSER (?)}"); stmt.setInt(1, userUid); rs = stmt.executeQuery(); while (rs.next()) { FundBuilder builder = Fund.newBuilder().setUid(rs.getInt("UID")).setUserUid(rs.getInt("USERUID")) .setBidPrice(rs.getDouble("FUNDPRICE")).setBidDate(rs.getDate("FUNDDATE").getTime()); objs.add(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[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); } }