· 8 years ago · Mar 19, 2018, 12:52 PM
1package garbage360.jean.garbage360;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.SQLException;
7import android.database.sqlite.SQLiteDatabase;
8import android.database.sqlite.SQLiteOpenHelper;
9
10import java.util.ArrayList;
11import java.util.List;
12
13
14/**
15 * Created by Terence on 2/27/2017.
16 */
17
18public class LoginDatabase extends SQLiteOpenHelper
19{
20 // Database Version
21 private static final int DATABASE_VERSION = 1;
22
23 // Database Name
24 private static final String DATABASE_NAME = "UserData.db";
25
26 // User table name
27 private static final String TABLE_USER = "user";
28
29 // User Table Columns names
30 private static final String COLUMN_USER_ID = "user_id";
31 private static final String COLUMN_USER_NAME = "user_name";
32 private static final String COLUMN_USER_EMAIL = "user_email";
33 private static final String COLUMN_USER_ADDRESS = "user_address";
34 private static final String COLUMN_USER_PASSWORD = "user_password";
35
36 // create table sql query
37 private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "("
38 + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_USER_NAME + " TEXT," + COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_ADDRESS + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";
39
40 // drop table sql query
41 private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER;
42
43 /**
44 * Constructor
45 *
46 * @param context
47 */
48 public LoginDatabase(Context context) {
49 super(context, DATABASE_NAME, null, DATABASE_VERSION);
50 }
51
52 @Override
53 public void onCreate(SQLiteDatabase db) {
54 db.execSQL(CREATE_USER_TABLE);
55 }
56
57
58 @Override
59 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
60
61 //Drop User Table if exist
62 db.execSQL(DROP_USER_TABLE);
63
64 // Create tables again
65 onCreate(db);
66
67 }
68
69 /**
70 * This method is to create user record
71 *
72 * @param user
73 */
74 public void addUser(UserModel user) {
75 SQLiteDatabase db = this.getWritableDatabase();
76
77 ContentValues values = new ContentValues();
78 values.put(COLUMN_USER_NAME, user.getName());
79 values.put(COLUMN_USER_EMAIL, user.getEmail());
80 values.put(COLUMN_USER_ADDRESS, user.getAddress());
81 values.put(COLUMN_USER_PASSWORD, user.getPassword());
82
83 // Inserting Row
84 db.insert(TABLE_USER, null, values);
85 db.close();
86 }
87
88 /**
89 * This method is to fetch all user and return the list of user records
90 *
91 * @return list
92 */
93 public List<UserModel> getAllUser() {
94 // array of columns to fetch
95 String[] columns = {
96 COLUMN_USER_ID,
97 COLUMN_USER_EMAIL,
98 COLUMN_USER_NAME,
99 COLUMN_USER_ADDRESS,
100 // COLUMN_USER_PASSWORD
101 };
102 // sorting orders
103 String sortOrder =
104 COLUMN_USER_NAME + " ASC";
105 List<UserModel> userList = new ArrayList<UserModel>();
106
107 SQLiteDatabase db = this.getReadableDatabase();
108
109 // query the user table
110 /**
111 * Here query function is used to fetch records from user table this function works like we use sql query.
112 * SQL query equivalent to this query function is
113 * SELECT user_id,user_name,user_email,user_password FROM user ORDER BY user_name;
114 */
115 Cursor cursor = db.query(TABLE_USER, //Table to query
116 columns, //columns to return
117 null, //columns for the WHERE clause
118 null, //The values for the WHERE clause
119 null, //group the rows
120 null, //filter by row groups
121 sortOrder); //The sort order
122
123
124 // Traversing through all rows and adding to list
125 if (cursor.moveToFirst()) {
126 do {
127 UserModel user = new UserModel();
128 user.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ID))));
129 user.setName(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
130 user.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
131 user.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ADDRESS)));
132 // user.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
133 // Adding user record to list
134 userList.add(user);
135 } while (cursor.moveToNext());
136 }
137 cursor.close();
138 db.close();
139
140 // return user list
141 return userList;
142 }
143
144 /**
145 * This method to update user record
146 *
147 * @param user
148 */
149 public void updateUser(UserModel user) {
150 SQLiteDatabase db = this.getWritableDatabase();
151
152 ContentValues values = new ContentValues();
153 values.put(COLUMN_USER_NAME, user.getName());
154 values.put(COLUMN_USER_EMAIL, user.getEmail());
155 values.put(COLUMN_USER_ADDRESS, user.getAddress());
156 values.put(COLUMN_USER_PASSWORD, user.getPassword());
157
158 // updating row
159 db.update(TABLE_USER, values, COLUMN_USER_ID + " = ?",
160 new String[]{String.valueOf(user.getId())});
161 db.close();
162 }
163
164 /**
165 * This method is to delete user record
166 *
167 * @param user
168 */
169 public void deleteUser(UserModel user) {
170 SQLiteDatabase db = this.getWritableDatabase();
171 // delete user record by id
172 db.delete(TABLE_USER, COLUMN_USER_ID + " = ?",
173 new String[]{String.valueOf(user.getId())});
174 db.close();
175 }
176
177 /**
178 * This method to check user exist or not
179 *
180 * @param email
181 * @return true/false
182 */
183 public boolean checkUser(String email) {
184
185 // array of columns to fetch
186 String[] columns = {
187 COLUMN_USER_ID
188 };
189 SQLiteDatabase db = this.getReadableDatabase();
190
191 // selection criteria
192 String selection = COLUMN_USER_EMAIL + " = ?";
193 // String selection = COLUMN_USER_ADDRESS + "=?";
194
195 // selection argument
196 String[] selectionArgs = {email};
197
198 // query user table with condition
199 /**
200 * Here query function is used to fetch records from user table this function works like we use sql query.
201 * SQL query equivalent to this query function is
202 * SELECT user_id FROM user WHERE user_email = 'jack@androidtutorialshub.com';
203 */
204 Cursor cursor = db.query(TABLE_USER, //Table to query
205 columns, //columns to return
206 selection, //columns for the WHERE clause
207 selectionArgs, //The values for the WHERE clause
208 null, //group the rows
209 null, //filter by row groups
210 null); //The sort order
211 int cursorCount = cursor.getCount();
212 cursor.close();
213 db.close();
214
215 if (cursorCount > 0) {
216 return true;
217 }
218
219 return false;
220 }
221
222 /**
223 * This method to check user exist or not
224 *
225 * @param email
226 * @param password
227 * @return true/false
228 */
229 public boolean checkUser(String email, String password) {
230
231 // array of columns to fetch
232 String[] columns = {
233 COLUMN_USER_ID
234 };
235 SQLiteDatabase db = this.getReadableDatabase();
236 // selection criteria
237 String selection = COLUMN_USER_EMAIL + " = ?" + " AND " + COLUMN_USER_PASSWORD + " = ?";
238
239 // selection arguments
240 String[] selectionArgs = {email, password};
241
242 // query user table with conditions
243 /**
244 * Here query function is used to fetch records from user table this function works like we use sql query.
245 * SQL query equivalent to this query function is
246 * SELECT user_id FROM user WHERE user_email = 'jack@androidtutorialshub.com' AND user_password = 'qwerty';
247 */
248 Cursor cursor = db.query(TABLE_USER, //Table to query
249 columns, //columns to return
250 selection, //columns for the WHERE clause
251 selectionArgs, //The values for the WHERE clause
252 null, //group the rows
253 null, //filter by row groups
254 null); //The sort order
255
256 int cursorCount = cursor.getCount();
257
258 cursor.close();
259 db.close();
260 if (cursorCount > 0) {
261 return true;
262 }
263
264 return false;
265 }
266}
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293 /**
294 static final String DATABASE_NAME = "login.db";
295 static final int DATABASE_VERSION = 1;
296 public static final int NAME_COLUMN = 1;
297 // TODO: Create public field for each column in your table.
298 // SQL Statement to create a new database.
299 static final String DATABASE_CREATE = "create table "+"LOGIN"+
300 "( " +"ID"+" integer primary key autoincrement,"+ "USERNAME text,PASSWORD text); ";
301 // Variable to hold the database instance
302 public SQLiteDatabase db;
303 // Context of the application using the database.
304 private final Context context;
305 // Database open/upgrade helper
306 private DatabaseHelper dbHelper;
307 public LoginDatabase(Context _context)
308 {
309 context = _context;
310 dbHelper = new DatabaseHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
311 }
312 public LoginDatabase open() throws SQLException
313 {
314 db = dbHelper.getWritableDatabase();
315 return this;
316 }
317 public void close()
318 {
319 db.close();
320 }
321
322 public SQLiteDatabase getDatabaseInstance()
323 {
324 return db;
325 }
326
327 public void insertEntry(String userName,String password)
328 {
329 ContentValues newValues = new ContentValues();
330 // Assign values for each row.
331 newValues.put("USERNAME", userName);
332 newValues.put("PASSWORD",password);
333
334 // Insert the row into your table
335 db.insert("LOGIN", null, newValues);
336 ///Toast.makeText(context, "Reminder Is Successfully Saved", Toast.LENGTH_LONG).show();
337 }
338 public int deleteEntry(String UserName)
339 {
340 //String id=String.valueOf(ID);
341 String where="USERNAME=?";
342 int numberOFEntriesDeleted= db.delete("LOGIN", where, new String[]{UserName}) ;
343 // Toast.makeText(context, "Number fo Entry Deleted Successfully : "+numberOFEntriesDeleted, Toast.LENGTH_LONG).show();
344 return numberOFEntriesDeleted;
345 }
346 public String getSinlgeEntry(String userName)
347 {
348 Cursor cursor=db.query("LOGIN", null, " USERNAME=?", new String[]{userName}, null, null, null);
349 if(cursor.getCount()<1) // UserName Not Exist
350 {
351 cursor.close();
352 return "NOT EXIST";
353 }
354 cursor.moveToFirst();
355 String password= cursor.getString(cursor.getColumnIndex("PASSWORD"));
356 cursor.close();
357 return password;
358 }
359 public void updateEntry(String userName,String password)
360 {
361 // Define the updated row content.
362 ContentValues updatedValues = new ContentValues();
363 // Assign values for each row.
364 updatedValues.put("USERNAME", userName);
365 updatedValues.put("PASSWORD",password);
366
367 String where="USERNAME = ?";
368 db.update("LOGIN",updatedValues, where, new String[]{userName});
369 }**/