test
Форк от lirfrnk/test
1package com.bittercode.service.impl;
2
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.util.ArrayList;
8import java.util.List;
9
10import com.bittercode.constant.ResponseCode;
11import com.bittercode.constant.db.BooksDBConstants;
12import com.bittercode.model.Book;
13import com.bittercode.model.StoreException;
14import com.bittercode.service.BookService;
15import com.bittercode.util.DBUtil;
16
17public class BookServiceImpl implements BookService {
18
19private static final String getAllBooksQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK;
20private static final String getBookByIdQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK
21+ " WHERE " + BooksDBConstants.COLUMN_BARCODE + " = ?";
22
23private static final String deleteBookByIdQuery = "DELETE FROM " + BooksDBConstants.TABLE_BOOK + " WHERE "
24+ BooksDBConstants.COLUMN_BARCODE + "=?";
25
26private static final String addBookQuery = "INSERT INTO " + BooksDBConstants.TABLE_BOOK + " VALUES(?,?,?,?,?)";
27
28private static final String updateBookQtyByIdQuery = "UPDATE " + BooksDBConstants.TABLE_BOOK + " SET "
29+ BooksDBConstants.COLUMN_QUANTITY + "=? WHERE " + BooksDBConstants.COLUMN_BARCODE
30+ "=?";
31
32private 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
41public Book getBookById(String bookId) throws StoreException {
42Book book = null;
43Connection con = DBUtil.getConnection();
44try {
45PreparedStatement ps = con.prepareStatement(getBookByIdQuery);
46ps.setString(1, bookId);
47ResultSet rs = ps.executeQuery();
48
49while (rs.next()) {
50String bCode = rs.getString(1);
51String bName = rs.getString(2);
52String bAuthor = rs.getString(3);
53int bPrice = rs.getInt(4);
54int bQty = rs.getInt(5);
55
56book = new Book(bCode, bName, bAuthor, bPrice, bQty);
57}
58} catch (SQLException e) {
59
60}
61return book;
62}
63
64@Override
65public List<Book> getAllBooks() throws StoreException {
66List<Book> books = new ArrayList<Book>();
67Connection con = DBUtil.getConnection();
68
69try {
70PreparedStatement ps = con.prepareStatement(getAllBooksQuery);
71ResultSet rs = ps.executeQuery();
72
73while (rs.next()) {
74String bCode = rs.getString(1);
75String bName = rs.getString(2);
76String bAuthor = rs.getString(3);
77int bPrice = rs.getInt(4);
78int bQty = rs.getInt(5);
79
80Book book = new Book(bCode, bName, bAuthor, bPrice, bQty);
81books.add(book);
82}
83} catch (SQLException e) {
84
85}
86return books;
87}
88
89@Override
90public String deleteBookById(String bookId) throws StoreException {
91String response = ResponseCode.FAILURE.name();
92Connection con = DBUtil.getConnection();
93try {
94PreparedStatement ps = con.prepareStatement(deleteBookByIdQuery);
95ps.setString(1, bookId);
96int k = ps.executeUpdate();
97if (k == 1) {
98response = ResponseCode.SUCCESS.name();
99}
100} catch (Exception e) {
101response += " : " + e.getMessage();
102e.printStackTrace();
103}
104return response;
105}
106
107@Override
108public String addBook(Book book) throws StoreException {
109String responseCode = ResponseCode.FAILURE.name();
110Connection con = DBUtil.getConnection();
111try {
112PreparedStatement ps = con.prepareStatement(addBookQuery);
113ps.setString(1, book.getBarcode());
114ps.setString(2, book.getName());
115ps.setString(3, book.getAuthor());
116ps.setDouble(4, book.getPrice());
117ps.setInt(5, book.getQuantity());
118int k = ps.executeUpdate();
119if (k == 1) {
120responseCode = ResponseCode.SUCCESS.name();
121}
122} catch (Exception e) {
123responseCode += " : " + e.getMessage();
124e.printStackTrace();
125}
126return responseCode;
127}
128
129@Override
130public String updateBookQtyById(String bookId, int quantity) throws StoreException {
131String responseCode = ResponseCode.FAILURE.name();
132Connection con = DBUtil.getConnection();
133try {
134PreparedStatement ps = con.prepareStatement(updateBookQtyByIdQuery);
135ps.setInt(1, quantity);
136ps.setString(2, bookId);
137ps.executeUpdate();
138responseCode = ResponseCode.SUCCESS.name();
139} catch (Exception e) {
140responseCode += " : " + e.getMessage();
141e.printStackTrace();
142}
143return responseCode;
144}
145
146@Override
147public List<Book> getBooksByCommaSeperatedBookIds(String commaSeperatedBookIds) throws StoreException {
148List<Book> books = new ArrayList<Book>();
149Connection con = DBUtil.getConnection();
150try {
151String getBooksByCommaSeperatedBookIdsQuery = "SELECT * FROM " + BooksDBConstants.TABLE_BOOK
152+ " WHERE " +
153BooksDBConstants.COLUMN_BARCODE + " IN ( " + commaSeperatedBookIds + " )";
154PreparedStatement ps = con.prepareStatement(getBooksByCommaSeperatedBookIdsQuery);
155ResultSet rs = ps.executeQuery();
156
157while (rs.next()) {
158String bCode = rs.getString(1);
159String bName = rs.getString(2);
160String bAuthor = rs.getString(3);
161int bPrice = rs.getInt(4);
162int bQty = rs.getInt(5);
163
164Book book = new Book(bCode, bName, bAuthor, bPrice, bQty);
165books.add(book);
166}
167} catch (SQLException e) {
168
169}
170return books;
171}
172
173@Override
174public String updateBook(Book book) throws StoreException {
175String responseCode = ResponseCode.FAILURE.name();
176Connection con = DBUtil.getConnection();
177try {
178PreparedStatement ps = con.prepareStatement(updateBookByIdQuery);
179ps.setString(1, book.getName());
180ps.setString(2, book.getAuthor());
181ps.setDouble(3, book.getPrice());
182ps.setInt(4, book.getQuantity());
183ps.setString(5, book.getBarcode());
184ps.executeUpdate();
185responseCode = ResponseCode.SUCCESS.name();
186} catch (Exception e) {
187responseCode += " : " + e.getMessage();
188e.printStackTrace();
189}
190return responseCode;
191}
192
193}
194