· 8 years ago · Apr 23, 2018, 10:22 PM
1package com.example.xrhstos.bookapp;
2
3import java.util.ArrayList;
4import android.content.Context;
5import android.database.sqlite.SQLiteConstraintException;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.content.ContentValues;
9import android.database.Cursor;
10
11import java.util.ArrayList;
12
13//DATABASE NAME,TABLES' NAMES
14private static final String DATABASE_NAME = "book_db.db";
15private static final int DATABASE_VERSION = 2;
16private static final String BOOKS_TABLE = "books";//<--TABLE
17private static final String BOOK_CATEGORIES_TABLE = "categories";//<--TABLE
18private static final String AUTHORS_TABLE = "authorsTable";//<--TABLE
19
20//BOOK_TABLE ATTRIBUTES
21private static final String BOOK_KEY = "bookKey";
22private static final String BOOK_TITLE = "_bookTitle";
23private static final String PERSONAL_RATING = "_personalRating";
24private static final String AVERAGE_RATING = "_averageRating";
25private static final String IMAGE_DATA = "imageData";
26private static final String BOOK_ID = "bookId";
27private static final String GOOGLE_ID = "googleId";
28private static final String ISBN_10 = "isbn10";
29private static final String ISBN_13 = "isbn13";
30private static final String BOOK_COVER_URL = "bookCoverUrl";
31private static final String DESCRIPTION = "description";
32private static final String CALLBACK_URL = "callBackUrl";
33private static final String PREVIEW_URL = "previewUrl";
34private static final String BUY_URL = "buyUrl";
35private static final String PAGE_COUNT = "pageCount";
36private static final String PUBLISHED_DATE = "publishedDate";
37private static final String IS_BOOK_COLLECTION = "isBookCollection";
38private static final String IS_BOOK_READ = "isBookRead";
39private static final String IS_BOOK_IN_WHISHLIST = "isBookInWishList";
40
41
42
43//AUTHORS TABLE ATTRIBUTES
44//private static final String GOOGLE_ID = "googleId"; //Already declared above
45private static final String AUTHOR = "author";
46
47//CATEGORIES TABLE ATTRIBUTES
48//private static final String GOOGLE_ID = "googleId"; //Already declared above
49private static final String CATEGORY = "category";
50
51private static Database mInstance;
52
53public static synchronized Database getInstance(Context c) {
54 if (mInstance == null ) {
55 mInstance = new Database(c);
56 }
57 return mInstance;
58}
59
60
61private Database(Context context) {
62 super(context, DATABASE_NAME, null, 1);
63 //TODO Auto-generated constructor stub
64}
65
66
67@Override
68public void onCreate(SQLiteDatabase sqLiteDatabase) {
69
70 //BOOKS TABLE CREATE
71 sqLiteDatabase.execSQL(" CREATE TABLE " + BOOKS_TABLE + " ( " + BOOK_TITLE + " TEXT, " + BOOK_KEY + " TEXT," + PERSONAL_RATING + " REAL,"
72 + AVERAGE_RATING + " REAL," +
73 BOOK_ID + " TEXT," + GOOGLE_ID +" TEXT," + ISBN_13 + " TEXT," + ISBN_10 + " TEXT," + BOOK_COVER_URL + " TEXT," + DESCRIPTION + " TEXT," +
74 CALLBACK_URL + " TEXT," + PREVIEW_URL + " TEXT," + BUY_URL + " TEXT," + PAGE_COUNT + " INT," + PUBLISHED_DATE + " TEXT,"
75 + IS_BOOK_COLLECTION + " INT," + IS_BOOK_READ + " INT," + IS_BOOK_IN_WHISHLIST + " INT," + IMAGE_DATA + " BLOB," + " PRIMARY KEY(" + BOOK_KEY +"));");
76 //No boolean type in SQLite, INT used instead.
77
78 //CATEGORIES TABLE CREATE
79 sqLiteDatabase.execSQL(" CREATE TABLE " + AUTHORS_TABLE + " ( " + BOOK_KEY + " TEXT, " + CATEGORY +
80 " TEXT, PRIMARY KEY("+ BOOK_KEY +"," + CATEGORY +"));");
81
82 //AUTHORS TABLE CREATE
83 sqLiteDatabase.execSQL(" CREATE TABLE " + BOOK_CATEGORIES_TABLE + " ( " + BOOK_KEY + " TEXT, " + AUTHOR +
84 " TEXT, PRIMARY KEY("+ BOOK_KEY +"," + AUTHOR +"));");
85}
86
87@Override
88public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
89 sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + BOOKS_TABLE);
90 sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + BOOK_CATEGORIES_TABLE);
91 sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + AUTHORS_TABLE);
92 onCreate(sqLiteDatabase);
93}
94
95/**
96 * This method adds a Book to the database
97 * @param book
98 * @return
99 */
100public boolean addRecord(Book book){
101
102 boolean resultBooksTable = false;
103 boolean resultAuthorsTable = false;
104 boolean resultCategoriesTable = false;
105
106 SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
107 ContentValues booksTableValues = new ContentValues();
108
109 //BOOK TABLE VALUES \\------///
110 booksTableValues.put(BOOK_KEY,book.getKey());
111 booksTableValues.put(BOOK_TITLE,book.getBookTitle());
112 booksTableValues.put(AVERAGE_RATING,book.getAverageRating());
113 booksTableValues.put(PERSONAL_RATING,book.getPersonalRating());
114 booksTableValues.put(BOOK_ID,book.getId());
115 booksTableValues.put(GOOGLE_ID,book.getGoogleID());
116 booksTableValues.put(ISBN_13,book.getISBN13());
117 booksTableValues.put(ISBN_10,book.getISBN10());
118 booksTableValues.put(BOOK_COVER_URL,book.getBookCoverURL());
119 booksTableValues.put(DESCRIPTION,book.getDescription());
120 booksTableValues.put(CALLBACK_URL,book.getCallbackURL());
121 booksTableValues.put(PREVIEW_URL,book.getPreviewURL());
122 booksTableValues.put(BUY_URL,book.getBuyURL());
123 booksTableValues.put(PAGE_COUNT,book.getPageCount());
124 booksTableValues.put(PUBLISHED_DATE,book.getPublishedDate());
125 booksTableValues.put(IS_BOOK_COLLECTION,book.isBookInCollection());
126 booksTableValues.put(IS_BOOK_READ,book.isBookRead());
127 booksTableValues.put(IS_BOOK_IN_WHISHLIST,book.isBookInWishlist());
128 booksTableValues.put(IMAGE_DATA,book.getByteArray());
129
130 //Inserting into BOOKS_TABLE
131
132 try{//Try inserting into BOOKS_TABLE
133
134 sqLiteDatabase.insertOrThrow(BOOKS_TABLE,null, booksTableValues);
135 resultBooksTable = true;
136 }catch (SQLiteConstraintException alreadyInserted){
137 //Row is already inserted
138 }
139
140 //CATEGORY Table values
141 ContentValues CategoriesTableValues = new ContentValues();
142 CategoriesTableValues.put(BOOK_KEY,book.getKey());
143
144 //AUTHORS_TABLE values
145 ContentValues authorsTableValues = new ContentValues();
146 authorsTableValues.put(BOOK_KEY,book.getKey());
147
148 //Insert categories into BOOK_CATEGORIES_TABLE
149 int i = 0;
150 if (book.getCategories().length >= 0) try {//Try inserting into CATEGORIES_TABLE
151
152 for (i = 0; i < book.getCategories().length; i++) {
153
154
155 CategoriesTableValues.put(CATEGORY, book.getCategories()[i]);
156 sqLiteDatabase.insertOrThrow(BOOK_CATEGORIES_TABLE, null, CategoriesTableValues);
157 }
158
159 resultCategoriesTable = true;
160
161 } catch (SQLiteConstraintException alreadyInserted) {
162 //Row is already inserted
163 }
164
165 //Insert authors into AUTHORS_TABLE
166 i = 0;
167 if (book.getAuthor().length >= 0) try {//Try inserting into CATEGORIES_TABLE
168
169 for (i = 0; i < book.getAuthor().length; i++) {
170
171
172 CategoriesTableValues.put(AUTHOR, book.getAuthor()[i]);
173 sqLiteDatabase.insertOrThrow(AUTHORS_TABLE, null, authorsTableValues);
174 }
175
176 resultAuthorsTable = true;
177
178 } catch (SQLiteConstraintException alreadyInserted) {
179 //Row is already inserted
180 }
181
182
183 return resultBooksTable && resultAuthorsTable && resultCategoriesTable;
184
185}
186
187/**
188 * Packs all books from database to a bookList and returns the list
189 * @return
190 */
191public ArrayList<Book> getSavedBooksList(){
192
193 ArrayList<Book> savedBooksList = new ArrayList<Book>();
194
195 Book book=new Book();//First declaration so it's not undeclared.
196
197
198
199 SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
200 //Query
201 String q = "SELECT * FROM " + BOOKS_TABLE;//The entire table
202 Cursor cursor = sqLiteDatabase.rawQuery(q,null);
203
204 /////->One iteration for each book
205
206 try{
207 //Index of each collumn
208 int bookKeyIndex = cursor.getColumnIndex(BOOK_KEY);
209 int titleIndex = cursor.getColumnIndex(BOOK_TITLE);
210 int averageRatingIndex = cursor.getColumnIndex(AVERAGE_RATING);
211 int personalRatingIndex = cursor.getColumnIndex(PERSONAL_RATING);
212 int IdIndex = cursor.getColumnIndex(BOOK_ID);
213 int googleIdIndex = cursor.getColumnIndex(GOOGLE_ID);
214 int isbn13Index = cursor.getColumnIndex(ISBN_13);
215 int isbn10Index = cursor.getColumnIndex(ISBN_10);
216 int urlIndex = cursor.getColumnIndex(BOOK_COVER_URL);
217 int descriptionIndex = cursor.getColumnIndex(DESCRIPTION);
218 int callbackUrlIndex = cursor.getColumnIndex(CALLBACK_URL);
219 int previewUrlIndex = cursor.getColumnIndex(PREVIEW_URL);
220 int buyUrlIndex = cursor.getColumnIndex(BUY_URL);
221 int pageCountIndex = cursor.getColumnIndex(PAGE_COUNT);
222 int dateIndex = cursor.getColumnIndex(PUBLISHED_DATE);
223 int isBookCollectionIndex = cursor.getColumnIndex(IS_BOOK_COLLECTION);
224 int isBookReadIndex = cursor.getColumnIndex(IS_BOOK_READ);
225 int isBookInWishlistIndex = cursor.getColumnIndex(IS_BOOK_IN_WHISHLIST);
226 int bookCoverIndex = cursor.getColumnIndex(IMAGE_DATA);
227
228
229
230 cursor.moveToPosition(-1);
231
232 while(cursor.moveToNext()){
233
234
235 book = new Book();
236
237 book.setId(cursor.getString(IdIndex));
238 book.setAverageRating(averageRatingIndex);
239 book.setPersonalRating(personalRatingIndex);
240 book.setBookTitle(cursor.getString(titleIndex));
241 book.setBookCoverURL(cursor.getString(urlIndex));
242 book.setDescription(cursor.getString(descriptionIndex));
243 book.setGoogleID(cursor.getString(googleIdIndex));
244 book.setCallbackURL(cursor.getString(callbackUrlIndex));
245 book.setPreviewURL(cursor.getString(previewUrlIndex));
246 book.setBuyURL(cursor.getString(buyUrlIndex));
247 book.setPageCount(cursor.getInt(pageCountIndex));
248 book.setPublishedDate(cursor.getString(dateIndex));
249 book.setISBN13(cursor.getString(isbn13Index));
250 book.setISBN10(cursor.getString(isbn10Index));
251 book.setBookInCollection(1 == cursor.getInt(isBookCollectionIndex));//1==int var (converts int to boolean)
252 book.setBookRead(1 == cursor.getInt(isBookReadIndex));//SQLite doesn't support boolean.
253 book.setBookInWishlist(1 == cursor.getInt(isBookInWishlistIndex));
254 book.setBitmapFromByteArray(cursor.getBlob(bookCoverIndex));
255
256
257 //Query of categories of the current book
258 String q1 = null;
259
260 q1 = "SELECT * FROM " + BOOK_CATEGORIES_TABLE
261 + " WHERE " + BOOK_CATEGORIES_TABLE + "." + BOOK_KEY + "='" + book.getKey() + "'";
262
263
264 Cursor cursorCategories = sqLiteDatabase.rawQuery(q1,null);
265
266 //Query of authors of the current book
267 String q2 = null;
268 q2 = "SELECT * FROM " + AUTHORS_TABLE
269 + " WHERE " + AUTHORS_TABLE + "." + BOOK_KEY + "='" + book.getKey() + "'";
270
271 Cursor cursorAuthors = sqLiteDatabase.rawQuery(q2,null);
272
273 int categoryIndex = cursorCategories.getColumnIndex(CATEGORY);
274
275 int authorIndex = cursorAuthors.getColumnIndex(AUTHOR);
276
277 String[] categories = new String[cursorCategories.getCount()];
278
279 String[] authors = new String[cursorAuthors.getCount()];
280
281 cursorCategories.moveToPosition(-1);
282 cursorAuthors.moveToPosition(-1);
283
284 //Create String[] of categories
285 while(cursorCategories.moveToNext()) {
286 categories[cursorCategories.getPosition()] = cursorCategories.getString(categoryIndex);
287
288 }
289 book.setCategories(categories);
290
291 //Create String[] of authors
292 while(cursorAuthors.moveToNext()) {
293 authors[cursorAuthors.getPosition()] = cursorAuthors.getString(authorIndex);
294
295 }
296 book.setAuthor(authors);
297
298 //Add book to list
299 savedBooksList.add(book);
300 }
301 }catch (Exception outOfBounds){
302
303 }
304 finally {
305 cursor.close();
306 }
307
308
309
310
311 return savedBooksList;
312}
313
314/**
315 * This method deletes a row from the database
316 * @param book
317 * @return
318 */
319public boolean deleteRecord(Book book){
320
321 SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
322
323 int numOfRowsDeleted = 0;
324
325 try{//Delete everything for this book from categories table
326 numOfRowsDeleted += sqLiteDatabase.delete(BOOK_CATEGORIES_TABLE, BOOK_KEY + "='" + book.getKey() +"'", null);
327
328 }catch (Exception noSuchColumn){
329 //No such column
330 //numOfRowsDeleted = 0;
331 }
332
333 try{
334 numOfRowsDeleted += sqLiteDatabase.delete(BOOKS_TABLE, BOOK_KEY + "='" + book.getKey() +"'", null);
335
336 }catch (Exception noSuchColumn){
337 //No such column
338 //numOfRowsDeleted = 0;
339 }
340
341 try{//Delete everything for this book from authors table
342 numOfRowsDeleted += sqLiteDatabase.delete(AUTHORS_TABLE, BOOK_KEY + "='" + book.getKey() +"'", null);
343
344 }catch (Exception noSuchColumn){
345 //No such column
346 //numOfRowsDeleted = 0;
347 }
348
349 if(numOfRowsDeleted > 0){//If 1 row at least deleted return true
350 return true;
351 }else{
352 return false;
353 }
354
355
356}
357
358/** Returns true if book exists in the database.
359 * Returns false if it doesn't.
360 *
361 * @param book
362 * @return
363 */
364public boolean isBookSaved(Book book){
365 SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
366 String q = "SELECT * FROM " + BOOKS_TABLE
367 + " WHERE "
368 + BOOK_KEY + "='" + book.getKey() + "'";
369
370
371
372 Cursor cursor = sqLiteDatabase.rawQuery(q,null);
373
374 return cursor.moveToFirst();
375
376}
377
378/**
379 * UPDATE PERSONAL RATING
380 * The only variable for update is PERSONAL_RATING
381 * The update takes place only on BOOKS_TABLE.
382 * Other tables don't have to be updated.
383 * @param book
384 * @return
385 */
386public boolean updateRecord(Book book){
387 SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
388 boolean result = false;
389 ContentValues booksTableValues = new ContentValues();
390 //BOOK TABLE VALUES \\------///
391 booksTableValues.put(BOOK_TITLE,book.getBookTitle());
392 booksTableValues.put(AVERAGE_RATING,book.getAverageRating());
393 booksTableValues.put(PERSONAL_RATING,book.getPersonalRating());
394 booksTableValues.put(BOOK_ID,book.getId());
395 booksTableValues.put(GOOGLE_ID,book.getGoogleID());
396 booksTableValues.put(ISBN_13,book.getISBN13());
397 booksTableValues.put(ISBN_10,book.getISBN10());
398 booksTableValues.put(BOOK_COVER_URL,book.getBookCoverURL());
399 booksTableValues.put(DESCRIPTION,book.getDescription());
400 booksTableValues.put(CALLBACK_URL,book.getCallbackURL());
401 booksTableValues.put(PREVIEW_URL,book.getPreviewURL());
402 booksTableValues.put(BUY_URL,book.getBuyURL());
403 booksTableValues.put(PAGE_COUNT,book.getPageCount());
404 booksTableValues.put(PUBLISHED_DATE,book.getPublishedDate());
405 booksTableValues.put(IS_BOOK_COLLECTION,book.isBookInCollection());
406 booksTableValues.put(IS_BOOK_READ,book.isBookRead());
407 booksTableValues.put(IS_BOOK_IN_WHISHLIST,book.isBookInWishlist());
408 booksTableValues.put(IMAGE_DATA,book.getByteArray());
409
410 String q = null;
411
412 q = "SELECT * FROM " + BOOKS_TABLE +" WHERE " + BOOK_KEY + " ='" + book.getKey() + "'";
413
414 Cursor cursor = sqLiteDatabase.rawQuery(q,null);
415 if(cursor.moveToFirst()){
416 sqLiteDatabase.update(BOOKS_TABLE, booksTableValues, q, null);
417 result = true;
418 }
419 cursor.close();
420 //sqLiteDatabase.close();
421 return result;
422}