· 6 years ago · May 03, 2019, 03:26 PM
1
21
32
43
54
65
76
87
98
109
1110
1211
1312
1413
1514
1615
1716
1817
1918
2019
2120
2221
2322
2423
2524
2625
2726
2827
2928
3029
3130
3231
3332
3433
3534
3635
3736
3837
3938
4039
4140
4241
4342
4443
4544
4645
4746
4847
4948
5049
5150
5251
5352
5453
5554
5655
5756
5857
5958
6059
6160
6261
6362
6463
6564
6665
6766
6867
6968
7069
7170
7271
7372
7473
7574
7675
7776
7877
7978
8079
8180
8281
8382
8483
8584
8685
8786
8887
8988
9089
9190
9291
9392
9493
9594
9695
9796
9897
9998
10099
101100
102101
103102
104103
105104
106105
107106
108107
109108
110109
111110
112111
113112
114113
115114
116115
117116
118117
119118
120119
121120
122121
123122
124123
125124
126125
127126
128127
129128
130129
131130
132131
133132
134133
135134
136135
137136
138137
139138
140139
141140
142141
143142
144143
145144
146145
147146
148147
149148
150149
151150
152151
153152
154153
155154
156155
157156
158157
159158
160159
161160
162161
163162
164163
165164
166165
167166
168167
169168
170169
171170
172171
173172
174173
175174
176175
177176
178177
179178
180179
181180
182181
183182
184183
185184
186185
187186
188187
189188
190189
191190
192191
193192
194193
195194
196195
197196
198197
199198
200199
201200
202201
203202
204203
205204
206205
207206
208207
209208
210209
211210
212211
213212
214213
215214
216215
217216
218217
219218
220219
221220
222221
223222
224223
225224
226225
227226
228227
229228
230229
231230
232231
233232
234233
235234
236235
237236
238237
239238
240239
241240
242241
243242
244243
245244
246245
247246
248247
249248
250249
251250
252251
253252
254253
255254
256255
257256
258257
259package com.androidtutorialshub.loginregister.sql;
260
261import android.content.ContentValues;
262import android.content.Context;
263import android.database.Cursor;
264import android.database.sqlite.SQLiteDatabase;
265import android.database.sqlite.SQLiteOpenHelper;
266
267import com.androidtutorialshub.loginregister.model.User;
268
269import java.util.ArrayList;
270import java.util.List;
271
272public class DatabaseHelper extends SQLiteOpenHelper {
273
274 // Database Version
275 private static final int DATABASE_VERSION = 1;
276
277 // Database Name
278 private static final String DATABASE_NAME = "UserManager.db";
279
280 // User table name
281 private static final String TABLE_USER = "user";
282
283 // User Table Columns names
284 private static final String COLUMN_USER_ID = "user_id";
285 private static final String COLUMN_USER_NAME = "user_name";
286 private static final String COLUMN_USER_EMAIL = "user_email";
287 private static final String COLUMN_USER_PASSWORD = "user_password";
288
289 // create table sql query
290 private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "("
291 + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_USER_NAME + " TEXT,"
292 + COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";
293
294 // drop table sql query
295 private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER;
296
297 /**
298 * Constructor
299 *
300 * @param context
301 */
302 public DatabaseHelper(Context context) {
303 super(context, DATABASE_NAME, null, DATABASE_VERSION);
304 }
305
306 @Override
307 public void onCreate(SQLiteDatabase db) {
308 db.execSQL(CREATE_USER_TABLE);
309 }
310
311
312 @Override
313 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
314
315 //Drop User Table if exist
316 db.execSQL(DROP_USER_TABLE);
317
318 // Create tables again
319 onCreate(db);
320
321 }
322
323 /**
324 * This method is to create user record
325 *
326 * @param user
327 */
328 public void addUser(User user) {
329 SQLiteDatabase db = this.getWritableDatabase();
330
331 ContentValues values = new ContentValues();
332 values.put(COLUMN_USER_NAME, user.getName());
333 values.put(COLUMN_USER_EMAIL, user.getEmail());
334 values.put(COLUMN_USER_PASSWORD, user.getPassword());
335
336 // Inserting Row
337 db.insert(TABLE_USER, null, values);
338 db.close();
339 }
340
341 /**
342 * This method is to fetch all user and return the list of user records
343 *
344 * @return list
345 */
346 public List<User> getAllUser() {
347 // array of columns to fetch
348 String[] columns = {
349 COLUMN_USER_ID,
350 COLUMN_USER_EMAIL,
351 COLUMN_USER_NAME,
352 COLUMN_USER_PASSWORD
353 };
354 // sorting orders
355 String sortOrder =
356 COLUMN_USER_NAME + " ASC";
357 List<User> userList = new ArrayList<User>();
358
359 SQLiteDatabase db = this.getReadableDatabase();
360
361 // query the user table
362 /**
363 * Here query function is used to fetch records from user table this function works like we use sql query.
364 * SQL query equivalent to this query function is
365 * SELECT user_id,user_name,user_email,user_password FROM user ORDER BY user_name;
366 */
367 Cursor cursor = db.query(TABLE_USER, //Table to query
368 columns, //columns to return
369 null, //columns for the WHERE clause
370 null, //The values for the WHERE clause
371 null, //group the rows
372 null, //filter by row groups
373 sortOrder); //The sort order
374
375
376 // Traversing through all rows and adding to list
377 if (cursor.moveToFirst()) {
378 do {
379 User user = new User();
380 user.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ID))));
381 user.setName(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
382 user.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
383 user.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
384 // Adding user record to list
385 userList.add(user);
386 } while (cursor.moveToNext());
387 }
388 cursor.close();
389 db.close();
390
391 // return user list
392 return userList;
393 }
394
395 /**
396 * This method to update user record
397 *
398 * @param user
399 */
400 public void updateUser(User user) {
401 SQLiteDatabase db = this.getWritableDatabase();
402
403 ContentValues values = new ContentValues();
404 values.put(COLUMN_USER_NAME, user.getName());
405 values.put(COLUMN_USER_EMAIL, user.getEmail());
406 values.put(COLUMN_USER_PASSWORD, user.getPassword());
407
408 // updating row
409 db.update(TABLE_USER, values, COLUMN_USER_ID + " = ?",
410 new String[]{String.valueOf(user.getId())});
411 db.close();
412 }
413
414 /**
415 * This method is to delete user record
416 *
417 * @param user
418 */
419 public void deleteUser(User user) {
420 SQLiteDatabase db = this.getWritableDatabase();
421 // delete user record by id
422 db.delete(TABLE_USER, COLUMN_USER_ID + " = ?",
423 new String[]{String.valueOf(user.getId())});
424 db.close();
425 }
426
427 /**
428 * This method to check user exist or not
429 *
430 * @param email
431 * @return true/false
432 */
433 public boolean checkUser(String email) {
434
435 // array of columns to fetch
436 String[] columns = {
437 COLUMN_USER_ID
438 };
439 SQLiteDatabase db = this.getReadableDatabase();
440
441 // selection criteria
442 String selection = COLUMN_USER_EMAIL + " = ?";
443
444 // selection argument
445 String[] selectionArgs = {email};
446
447 // query user table with condition
448 /**
449 * Here query function is used to fetch records from user table this function works like we use sql query.
450 * SQL query equivalent to this query function is
451 * SELECT user_id FROM user WHERE user_email = 'jack@androidtutorialshub.com';
452 */
453 Cursor cursor = db.query(TABLE_USER, //Table to query
454 columns, //columns to return
455 selection, //columns for the WHERE clause
456 selectionArgs, //The values for the WHERE clause
457 null, //group the rows
458 null, //filter by row groups
459 null); //The sort order
460 int cursorCount = cursor.getCount();
461 cursor.close();
462 db.close();
463
464 if (cursorCount > 0) {
465 return true;
466 }
467
468 return false;
469 }
470
471 /**
472 * This method to check user exist or not
473 *
474 * @param email
475 * @param password
476 * @return true/false
477 */
478 public boolean checkUser(String email, String password) {
479
480 // array of columns to fetch
481 String[] columns = {
482 COLUMN_USER_ID
483 };
484 SQLiteDatabase db = this.getReadableDatabase();
485 // selection criteria
486 String selection = COLUMN_USER_EMAIL + " = ?" + " AND " + COLUMN_USER_PASSWORD + " = ?";
487
488 // selection arguments
489 String[] selectionArgs = {email, password};
490
491 // query user table with conditions
492 /**
493 * Here query function is used to fetch records from user table this function works like we use sql query.
494 * SQL query equivalent to this query function is
495 * SELECT user_id FROM user WHERE user_email = 'jack@androidtutorialshub.com' AND user_password = 'qwerty';
496 */
497 Cursor cursor = db.query(TABLE_USER, //Table to query
498 columns, //columns to return
499 selection, //columns for the WHERE clause
500 selectionArgs, //The values for the WHERE clause
501 null, //group the rows
502 null, //filter by row groups
503 null); //The sort order
504
505 int cursorCount = cursor.getCount();
506
507 cursor.close();
508 db.close();
509 if (cursorCount > 0) {
510 return true;
511 }
512
513 return false;
514 }
515}