com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

Source

/*
 * 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);
    }
}