1
package com.bittercode.service.impl;
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;
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;
17
public class BookServiceImpl implements BookService {
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 + " = ?";
23
private static final String deleteBookByIdQuery = "DELETE FROM " + BooksDBConstants.TABLE_BOOK + " WHERE "
24
+ BooksDBConstants.COLUMN_BARCODE + "=?";
26
private static final String addBookQuery = "INSERT INTO " + BooksDBConstants.TABLE_BOOK + " VALUES(?,?,?,?,?)";
28
private static final String updateBookQtyByIdQuery = "UPDATE " + BooksDBConstants.TABLE_BOOK + " SET "
29
+ BooksDBConstants.COLUMN_QUANTITY + "=? WHERE " + BooksDBConstants.COLUMN_BARCODE
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
41
public Book getBookById(String bookId) throws StoreException {
43
Connection con = DBUtil.getConnection();
45
PreparedStatement ps = con.prepareStatement(getBookByIdQuery);
46
ps.setString(1, bookId);
47
ResultSet rs = ps.executeQuery();
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);
56
book = new Book(bCode, bName, bAuthor, bPrice, bQty);
58
} catch (SQLException e) {
65
public List<Book> getAllBooks() throws StoreException {
66
List<Book> books = new ArrayList<Book>();
67
Connection con = DBUtil.getConnection();
70
PreparedStatement ps = con.prepareStatement(getAllBooksQuery);
71
ResultSet rs = ps.executeQuery();
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);
80
Book book = new Book(bCode, bName, bAuthor, bPrice, bQty);
83
} catch (SQLException e) {
90
public String deleteBookById(String bookId) throws StoreException {
91
String response = ResponseCode.FAILURE.name();
92
Connection con = DBUtil.getConnection();
94
PreparedStatement ps = con.prepareStatement(deleteBookByIdQuery);
95
ps.setString(1, bookId);
96
int k = ps.executeUpdate();
98
response = ResponseCode.SUCCESS.name();
100
} catch (Exception e) {
101
response += " : " + e.getMessage();
108
public String addBook(Book book) throws StoreException {
109
String responseCode = ResponseCode.FAILURE.name();
110
Connection con = DBUtil.getConnection();
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();
120
responseCode = ResponseCode.SUCCESS.name();
122
} catch (Exception e) {
123
responseCode += " : " + e.getMessage();
130
public String updateBookQtyById(String bookId, int quantity) throws StoreException {
131
String responseCode = ResponseCode.FAILURE.name();
132
Connection con = DBUtil.getConnection();
134
PreparedStatement ps = con.prepareStatement(updateBookQtyByIdQuery);
135
ps.setInt(1, quantity);
136
ps.setString(2, bookId);
138
responseCode = ResponseCode.SUCCESS.name();
139
} catch (Exception e) {
140
responseCode += " : " + e.getMessage();
147
public List<Book> getBooksByCommaSeperatedBookIds(String commaSeperatedBookIds) throws StoreException {
148
List<Book> books = new ArrayList<Book>();
149
Connection con = DBUtil.getConnection();
151
String getBooksByCommaSeperatedBookIdsQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK
153
BooksDBConstants.COLUMN_BARCODE + " IN ( " + commaSeperatedBookIds + " )";
154
PreparedStatement ps = con.prepareStatement(getBooksByCommaSeperatedBookIdsQuery);
155
ResultSet rs = ps.executeQuery();
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);
164
Book book = new Book(bCode, bName, bAuthor, bPrice, bQty);
167
} catch (SQLException e) {
174
public String updateBook(Book book) throws StoreException {
175
String responseCode = ResponseCode.FAILURE.name();
176
Connection con = DBUtil.getConnection();
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());
185
responseCode = ResponseCode.SUCCESS.name();
186
} catch (Exception e) {
187
responseCode += " : " + e.getMessage();