· 9 years ago · May 05, 2017, 02:40 PM
1package com.ahmed.database;
2
3import java.util.ArrayList;
4import java.util.HashMap;
5
6import android.content.ContentValues;
7import android.content.Context;
8import android.database.Cursor;
9import android.database.DatabaseUtils;
10import android.database.sqlite.SQLiteDatabase;
11import android.database.sqlite.SQLiteOpenHelper;
12import android.database.sqlite.SQLiteStatement;
13
14public class DBController extends SQLiteOpenHelper {
15
16 public static final String tablename1 = "MyUsers"; // tablename
17 public static final String username = "username"; // column name
18 public static final String id = "id"; // auto generated ID column
19 public static final String password = "password"; // column name
20 public static final String firstName = "firstname";
21 public static final String lastName = "lastname";
22 public static final String email = "email";
23 public static final String gender = "gender";
24 public static final String country = "country";
25 public static final String city = "city";
26 public static final String phone = "phone";
27 public static final String bloodgroup = "bloodgroup";
28 public static final String donorOrNot = "donorOrNot";
29 public static final String iamge = "image";
30 public static final String databasename = "BloodBankDb"; // Dtabasename
31
32 public static final String tablename2 = "requests"; // tablename
33 public static final String request = "request"; // tablename
34 public static final String quantity = "quantity"; // tablename
35
36 public static final int versioncode = 8; // versioncode of the database
37
38 // constructor
39 public DBController(Context context) {
40 super(context, databasename, null, versioncode);
41
42 }
43
44 @Override
45 public void onCreate(SQLiteDatabase database) {
46 String query;
47 query = "CREATE TABLE IF NOT EXISTS " + tablename1 + "(" + id + " integer primary key, " + this.firstName
48 + " text, " + this.lastName + " text , " + this.email + " text , " + this.gender + " text , "
49 + this.country + " text , " + this.phone + " text , " + this.city + " text , " + this.username
50 + " text , " + this.password + " text, " + this.bloodgroup + " text, " + this.donorOrNot + " text, "
51 + this.iamge + " blob" + ")";
52
53 database.execSQL(query);
54
55 query = "CREATE TABLE IF NOT EXISTS " + tablename2 + "(" + request + " text, " + this.quantity + " text )";
56 database.execSQL(query);
57
58 }
59
60 @Override
61 public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
62 String query;
63 query = "DROP TABLE IF EXISTS " + tablename1;
64 database.execSQL(query);
65
66 query = "DROP TABLE IF EXISTS " + tablename2;
67 database.execSQL(query);
68
69 onCreate(database);
70 }
71
72 /**
73 * This method deletes all records of the table 1
74 * the users table
75 *
76 */
77 public void deleteAllRecords() {
78 // TODO Auto-generated method stub
79
80 SQLiteDatabase db = this.getWritableDatabase();
81 // db.execSQL("delete from "+ this.tablename1);
82 db.delete(this.tablename1, null, null);
83 db.close();
84
85 /*
86 * or, if you want the function to return the count of deleted rows,
87 *
88 * db.delete(TABLE_NAME, "1", null); From the documentation of
89 * SQLiteDatabase delete method:
90 *
91 * To remove all rows and get a count pass "1" as the whereClause.
92 */
93
94 }
95
96 /**
97 * didnot used
98 * @param bytes
99 * @param id
100 * @return
101 */
102 public boolean saveBytes(byte[] bytes, int id) {
103
104 boolean ret = false;
105 SQLiteDatabase db = getWritableDatabase();
106 db.beginTransaction();
107
108 try {
109
110 String sql = "INSERT INTO IMAGES " + " ( IMAGE_ID" + ", IMAGE_BLOB" + " ) VALUES(?,?)";
111
112 SQLiteStatement insertStmt = db.compileStatement(sql);
113 insertStmt.clearBindings();
114 insertStmt.bindLong(1, id);
115 insertStmt.bindBlob(2, bytes);
116 insertStmt.executeInsert();
117
118 db.setTransactionSuccessful();
119 db.endTransaction();
120
121 ret = true;
122 } catch (Exception e) {
123 e.printStackTrace();
124 ret = false;
125 }
126
127 return ret;
128 }
129 /**
130 * didnot used
131 * @param id
132 * @return
133 * @throws Exception
134 */
135
136 public byte[] getBytes(int id) throws Exception {
137
138 byte[] ret = null;
139
140 try {
141
142 String selectQuery = "SELECT I.IMAGE_BLOB " + " FROM IMAGES I WHERE I.IMAGE_ID = ?";
143
144 SQLiteDatabase db = this.getReadableDatabase();
145 Cursor c = db.rawQuery(selectQuery, new String[] { String.valueOf(id) });
146
147 if (!c.isClosed() && c.moveToFirst() && c.getCount() > 0) {
148
149 if (c.getBlob(c.getColumnIndex("IMAGE_BLOB")) != null) {
150 ret = c.getBlob(c.getColumnIndex("IMAGE_BLOB"));
151
152 }
153 c.close();
154 if (db != null && db.isOpen())
155 db.close();
156 }
157 System.gc();
158 } catch (Exception e) {
159 System.gc();
160 throw e;
161
162 }
163 return ret;
164 }
165
166 /**
167 * this method get the requested data from user registration
168 * then insert data in database table one the users table
169 * @param fn
170 * @param ln
171 * @param email
172 * @param gender
173 * @param country
174 * @param city
175 * @param username
176 * @param password
177 * @param phone
178 * @param donorOrNot
179 * @param image
180 * @param bloodGroup
181 * @return
182 */
183 public boolean insertIntoDb(String fn, String ln, String email, String gender, String country, String city,
184 String username, String password, String phone, String donorOrNot, byte[] image, String bloodGroup) {
185 // TODO Auto-generated method stub
186
187 SQLiteDatabase db = this.getWritableDatabase();
188 ContentValues cv = new ContentValues();
189 cv.put(this.firstName, fn);
190 cv.put(this.lastName, ln);
191 cv.put(this.email, email);
192 cv.put(this.gender, gender);
193 cv.put(this.country, country);
194 cv.put(this.city, city);
195 cv.put(this.username, username);
196 cv.put(this.password, password);
197 cv.put(this.phone, phone);
198 cv.put(this.bloodgroup, bloodGroup);
199 cv.put(this.donorOrNot, donorOrNot);
200 cv.put(this.iamge, image);
201 db.insert(this.tablename1, null, cv);
202 // int i = (int) db.insert(this.tablename1, null, cv);
203 // if (i == -1) {
204 // return false;
205 // } else {
206 //
207 // }
208 db.close();
209 return true;
210 }
211
212 /**
213 * This method returns all users by using username and password
214 * @param username
215 * @param password
216 * @return
217 */
218 // For returning all users from database
219 public ArrayList<HashMap<String, String>> getAllUsers(String username, String password) {
220 ArrayList<HashMap<String, String>> arrayList;
221 arrayList = new ArrayList<HashMap<String, String>>();
222 // String selectQuery = "SELECT * FROM " + this.tablename1;
223 String selectQuery = "select * from " + this.tablename1 + " where username = '" + username
224 + "' and password = '" + password + "' ";
225 SQLiteDatabase database = this.getWritableDatabase();
226 Cursor cursor = database.rawQuery(selectQuery, null);
227 if (cursor.moveToFirst()) {
228 do {
229
230 HashMap<String, String> map = new HashMap<String, String>();
231 // Respectively as inserted at first time
232 map.put(this.id, cursor.getString(0));
233 map.put(this.firstName, cursor.getString(1));
234 map.put(this.lastName, cursor.getString(2));
235 map.put(this.email, cursor.getString(3));
236 map.put(this.gender, cursor.getString(4));
237 map.put(this.country, cursor.getString(5));
238 map.put(this.phone, cursor.getString(6));
239 map.put(this.city, cursor.getString(7));
240 map.put(this.username, cursor.getString(8));
241 map.put(this.password, cursor.getString(9));
242 map.put(this.bloodgroup, cursor.getString(10));
243 map.put(this.donorOrNot, cursor.getString(11));
244 // map.put(this.iamge, cursor.getBlob(12));
245 arrayList.add(map);
246
247 } while (cursor.moveToNext());
248
249 }
250
251 // return contact list
252 return arrayList;
253 }
254
255 /**
256 * This method returns all users by select query
257 * from developer
258 *
259 * @param selectQuery
260 * @return
261 */
262 // For returning all users from database
263 public ArrayList<HashMap<String, String>> getAllUsers(String selectQuery) {
264 ArrayList<HashMap<String, String>> arrayList;
265 arrayList = new ArrayList<HashMap<String, String>>();
266 // //String selectQuery = "SELECT * FROM " + this.tablename1;
267 // String selectQuery = "select * from " + this.tablename1 +
268 // " where username = '"+username+"' and password = '"+password+"' ";
269
270 SQLiteDatabase database = this.getWritableDatabase();
271 Cursor cursor = database.rawQuery(selectQuery, null);
272 if (cursor.moveToFirst()) {
273 do {
274 HashMap<String, String> map = new HashMap<String, String>();
275 // Respectively as inserted at first time
276 map.put(this.id, cursor.getString(0));
277 map.put(this.firstName, cursor.getString(1));
278 map.put(this.lastName, cursor.getString(2));
279 map.put(this.email, cursor.getString(3));
280 map.put(this.gender, cursor.getString(4));
281 map.put(this.country, cursor.getString(5));
282 map.put(this.phone, cursor.getString(6));
283 map.put(this.city, cursor.getString(7));
284 map.put(this.username, cursor.getString(8));
285 map.put(this.password, cursor.getString(9));
286 map.put(this.bloodgroup, cursor.getString(10));
287 map.put(this.donorOrNot, cursor.getString(11));
288 // map.put(this.iamge, cursor.getBlob(12));
289 arrayList.add(map);
290
291 } while (cursor.moveToNext());
292
293 }
294 return arrayList;
295 }
296
297
298 /**
299 * This function retrieve all requests from requests table
300 * for the user and donors
301 *
302 * @param selectQuery
303 * @return
304 */
305 public ArrayList<HashMap<String, String>> getAllRequests(String selectQuery) {
306 ArrayList<HashMap<String, String>> arrayList;
307 arrayList = new ArrayList<HashMap<String, String>>();
308 SQLiteDatabase database = this.getWritableDatabase();
309 Cursor cursor = database.rawQuery(selectQuery, null);
310 if (cursor.moveToFirst()) {
311 do {
312 HashMap<String, String> map = new HashMap<String, String>();
313 map.put(this.request, cursor.getString(0));
314 map.put(this.quantity, cursor.getString(1));
315 arrayList.add(map);
316 } while (cursor.moveToNext());
317 }
318 return arrayList;
319 }
320
321
322
323
324 /**
325 * This method will insert requestName as blood group
326 * and quantity of request in the requests table
327 * @param requestName
328 * @param quan
329 */
330 public void insertRequest(String requestName, String quan) {
331 // TODO Auto-generated method stub
332
333 SQLiteDatabase db = this.getWritableDatabase();
334 ContentValues cv = new ContentValues();
335 cv.put(this.request, requestName);
336 cv.put(this.quantity, quan);
337 db.insert(this.tablename2, null, cv);
338 db.close();
339 }
340
341
342 /**
343 * This method will get the inserted image as blob for
344 * the user to see it and update it
345 *
346 * @param selectQuery
347 * @return
348 */
349 // For returning all users from database
350 public ArrayList<HashMap<String, byte[]>> getAllImagesBySql(String selectQuery) {
351 ArrayList<HashMap<String, byte[]>> arrayList;
352 arrayList = new ArrayList<HashMap<String, byte[]>>();
353 // //String selectQuery = "SELECT * FROM " + this.tablename1;
354 // String selectQuery = "select * from " + this.tablename1 +
355 // " where username = '"+username+"' and password = '"+password+"' ";
356
357 SQLiteDatabase database = this.getWritableDatabase();
358 Cursor cursor = database.rawQuery(selectQuery, null);
359 if (cursor.moveToFirst()) {
360 do {
361 HashMap<String, byte[]> map = new HashMap<String, byte[]>();
362 map.put(this.iamge, cursor.getBlob(12));
363 arrayList.add(map);
364
365 } while (cursor.moveToNext());
366
367 }
368
369 // return contact list
370 return arrayList;
371 }
372
373 /**
374 * didnot used
375 * @return
376 */
377 public int numberOfRows() {
378 SQLiteDatabase db = this.getReadableDatabase();
379 int numRows = (int) DatabaseUtils.queryNumEntries(db, this.tablename1);
380 return numRows;
381 }
382
383 /**
384 * didnot used
385 * @param id
386 * @return
387 */
388 public Cursor getData(int id) {
389 SQLiteDatabase db = this.getReadableDatabase();
390 Cursor res = db.rawQuery("select * from '" + this.tablename1 + "' where id=" + id + "", null);
391 return res;
392 }
393
394 /**
395 * This method will update the password of one record when the firstname and lastname
396 * match with this specific record
397 *
398 * @param password
399 * @param firstName
400 * @param lastName
401 * @return
402 */
403 public boolean updateData(String password, String firstName, String lastName) {
404 SQLiteDatabase db = this.getWritableDatabase();
405 ContentValues contentValues = new ContentValues();
406 // contentValues.put("username", username);
407 contentValues.put("password", password);
408 // contentValues.put("firstname", firstName);
409 // contentValues.put("lastname", lastName);
410 db.update(this.tablename1, contentValues, "firstname = ? and lastname = ?",
411 new String[] { firstName, lastName });
412 return true;
413 }
414
415
416 /**
417 * This method will update password when the username matches with this
418 * record from users table
419 * @param username
420 * @param password
421 * @return
422 */
423 public boolean updatePassword(String username, String password) {
424 SQLiteDatabase db = this.getWritableDatabase();
425 ContentValues contentValues = new ContentValues();
426 // contentValues.put("username", username);
427 contentValues.put("password", password);
428 // contentValues.put("firstname", firstName);
429 // contentValues.put("lastname", lastName);
430 db.update(this.tablename1, contentValues, "username = ?", new String[] { username });
431 return true;
432 }
433
434 /**
435 *
436 * @param username
437 * @param password
438 * @param firstName
439 * @param lastName
440 * @return
441 */
442 public boolean updateData(String username, String password, String firstName, String lastName) {
443 SQLiteDatabase db = this.getWritableDatabase();
444 ContentValues contentValues = new ContentValues();
445 contentValues.put("username", username);
446 contentValues.put("password", password);
447 contentValues.put("firstname", firstName);
448 contentValues.put("lastname", lastName);
449 db.update(this.tablename1, contentValues, "firstname = ? and lastname = ?",
450 new String[] { firstName, lastName });
451 return true;
452 }
453
454 /**
455 * This method will update all user data that has been logged in using
456 * the username and password
457 *
458 * @param username
459 * @param password
460 * @param firstName
461 * @param lastName
462 * @param phone
463 * @param gender
464 * @param bloodGroup
465 * @param email
466 * @param city
467 * @param country
468 * @param donorOrNot
469 * @param bytes
470 * @return
471 */
472 public boolean updateData(String username, String password, String firstName, String lastName, String phone,
473 String gender, String bloodGroup, String email, String city, String country, String donorOrNot,
474 byte[] bytes) {
475 SQLiteDatabase db = this.getWritableDatabase();
476 ContentValues contentValues = new ContentValues();
477 contentValues.put("username", username);
478 contentValues.put("password", password);
479 contentValues.put("firstname", firstName);
480 contentValues.put("lastname", lastName);
481 contentValues.put("city", city);
482 contentValues.put("country", country);
483 contentValues.put("email", email);
484 contentValues.put("phone", phone);
485 contentValues.put("donorornot", donorOrNot);
486 contentValues.put("bloodgroup", bloodGroup);
487 contentValues.put("image", bytes);
488 contentValues.put("gender", gender);
489
490 db.update(this.tablename1, contentValues, "username = ? and password = ?", new String[] { username, password });
491 return true;
492 }
493
494}