test

1
package com.bittercode.service.impl;
2

3
import java.sql.Connection;
4
import java.sql.PreparedStatement;
5
import java.sql.ResultSet;
6
import java.sql.SQLException;
7
import java.util.ArrayList;
8
import java.util.List;
9

10
import com.bittercode.constant.ResponseCode;
11
import com.bittercode.constant.db.BooksDBConstants;
12
import com.bittercode.model.Book;
13
import com.bittercode.model.StoreException;
14
import com.bittercode.service.BookService;
15
import com.bittercode.util.DBUtil;
16

17
public class BookServiceImpl implements BookService {
18

19
    private static final String getAllBooksQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK;
20
    private static final String getBookByIdQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK
21
            + " WHERE " + BooksDBConstants.COLUMN_BARCODE + " = ?";
22

23
    private static final String deleteBookByIdQuery = "DELETE FROM " + BooksDBConstants.TABLE_BOOK + "  WHERE "
24
            + BooksDBConstants.COLUMN_BARCODE + "=?";
25

26
    private static final String addBookQuery = "INSERT INTO " + BooksDBConstants.TABLE_BOOK + "  VALUES(?,?,?,?,?)";
27

28
    private static final String updateBookQtyByIdQuery = "UPDATE " + BooksDBConstants.TABLE_BOOK + " SET "
29
            + BooksDBConstants.COLUMN_QUANTITY + "=? WHERE " + BooksDBConstants.COLUMN_BARCODE
30
            + "=?";
31

32
    private static final String updateBookByIdQuery = "UPDATE " + BooksDBConstants.TABLE_BOOK + " SET "
33
            + BooksDBConstants.COLUMN_NAME + "=? , "
34
            + BooksDBConstants.COLUMN_AUTHOR + "=?, "
35
            + BooksDBConstants.COLUMN_PRICE + "=?, "
36
            + BooksDBConstants.COLUMN_QUANTITY + "=? "
37
            + "  WHERE " + BooksDBConstants.COLUMN_BARCODE
38
            + "=?";
39

40
    @Override
41
    public Book getBookById(String bookId) throws StoreException {
42
        Book book = null;
43
        Connection con = DBUtil.getConnection();
44
        try {
45
            PreparedStatement ps = con.prepareStatement(getBookByIdQuery);
46
            ps.setString(1, bookId);
47
            ResultSet rs = ps.executeQuery();
48

49
            while (rs.next()) {
50
                String bCode = rs.getString(1);
51
                String bName = rs.getString(2);
52
                String bAuthor = rs.getString(3);
53
                int bPrice = rs.getInt(4);
54
                int bQty = rs.getInt(5);
55

56
                book = new Book(bCode, bName, bAuthor, bPrice, bQty);
57
            }
58
        } catch (SQLException e) {
59

60
        }
61
        return book;
62
    }
63

64
    @Override
65
    public List<Book> getAllBooks() throws StoreException {
66
        List<Book> books = new ArrayList<Book>();
67
        Connection con = DBUtil.getConnection();
68

69
        try {
70
            PreparedStatement ps = con.prepareStatement(getAllBooksQuery);
71
            ResultSet rs = ps.executeQuery();
72

73
            while (rs.next()) {
74
                String bCode = rs.getString(1);
75
                String bName = rs.getString(2);
76
                String bAuthor = rs.getString(3);
77
                int bPrice = rs.getInt(4);
78
                int bQty = rs.getInt(5);
79

80
                Book book = new Book(bCode, bName, bAuthor, bPrice, bQty);
81
                books.add(book);
82
            }
83
        } catch (SQLException e) {
84

85
        }
86
        return books;
87
    }
88

89
    @Override
90
    public String deleteBookById(String bookId) throws StoreException {
91
        String response = ResponseCode.FAILURE.name();
92
        Connection con = DBUtil.getConnection();
93
        try {
94
            PreparedStatement ps = con.prepareStatement(deleteBookByIdQuery);
95
            ps.setString(1, bookId);
96
            int k = ps.executeUpdate();
97
            if (k == 1) {
98
                response = ResponseCode.SUCCESS.name();
99
            }
100
        } catch (Exception e) {
101
            response += " : " + e.getMessage();
102
            e.printStackTrace();
103
        }
104
        return response;
105
    }
106

107
    @Override
108
    public String addBook(Book book) throws StoreException {
109
        String responseCode = ResponseCode.FAILURE.name();
110
        Connection con = DBUtil.getConnection();
111
        try {
112
            PreparedStatement ps = con.prepareStatement(addBookQuery);
113
            ps.setString(1, book.getBarcode());
114
            ps.setString(2, book.getName());
115
            ps.setString(3, book.getAuthor());
116
            ps.setDouble(4, book.getPrice());
117
            ps.setInt(5, book.getQuantity());
118
            int k = ps.executeUpdate();
119
            if (k == 1) {
120
                responseCode = ResponseCode.SUCCESS.name();
121
            }
122
        } catch (Exception e) {
123
            responseCode += " : " + e.getMessage();
124
            e.printStackTrace();
125
        }
126
        return responseCode;
127
    }
128

129
    @Override
130
    public String updateBookQtyById(String bookId, int quantity) throws StoreException {
131
        String responseCode = ResponseCode.FAILURE.name();
132
        Connection con = DBUtil.getConnection();
133
        try {
134
            PreparedStatement ps = con.prepareStatement(updateBookQtyByIdQuery);
135
            ps.setInt(1, quantity);
136
            ps.setString(2, bookId);
137
            ps.executeUpdate();
138
            responseCode = ResponseCode.SUCCESS.name();
139
        } catch (Exception e) {
140
            responseCode += " : " + e.getMessage();
141
            e.printStackTrace();
142
        }
143
        return responseCode;
144
    }
145

146
    @Override
147
    public List<Book> getBooksByCommaSeperatedBookIds(String commaSeperatedBookIds) throws StoreException {
148
        List<Book> books = new ArrayList<Book>();
149
        Connection con = DBUtil.getConnection();
150
        try {
151
            String getBooksByCommaSeperatedBookIdsQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK
152
                    + " WHERE " +
153
                    BooksDBConstants.COLUMN_BARCODE + " IN ( " + commaSeperatedBookIds + " )";
154
            PreparedStatement ps = con.prepareStatement(getBooksByCommaSeperatedBookIdsQuery);
155
            ResultSet rs = ps.executeQuery();
156

157
            while (rs.next()) {
158
                String bCode = rs.getString(1);
159
                String bName = rs.getString(2);
160
                String bAuthor = rs.getString(3);
161
                int bPrice = rs.getInt(4);
162
                int bQty = rs.getInt(5);
163

164
                Book book = new Book(bCode, bName, bAuthor, bPrice, bQty);
165
                books.add(book);
166
            }
167
        } catch (SQLException e) {
168

169
        }
170
        return books;
171
    }
172

173
    @Override
174
    public String updateBook(Book book) throws StoreException {
175
        String responseCode = ResponseCode.FAILURE.name();
176
        Connection con = DBUtil.getConnection();
177
        try {
178
            PreparedStatement ps = con.prepareStatement(updateBookByIdQuery);
179
            ps.setString(1, book.getName());
180
            ps.setString(2, book.getAuthor());
181
            ps.setDouble(3, book.getPrice());
182
            ps.setInt(4, book.getQuantity());
183
            ps.setString(5, book.getBarcode());
184
            ps.executeUpdate();
185
            responseCode = ResponseCode.SUCCESS.name();
186
        } catch (Exception e) {
187
            responseCode += " : " + e.getMessage();
188
            e.printStackTrace();
189
        }
190
        return responseCode;
191
    }
192

193
}
194