· 9 years ago · Dec 26, 2016, 07:40 AM
1package com.fahimchowdhury.myapplication;
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.SQLiteException;
9import android.database.sqlite.SQLiteOpenHelper;
10
11
12import java.util.ArrayList;
13
14
15/**
16 Created by Misbah Ahmad Chowdhury Fahim on 8/27/2016.
17 */
18
19public class DBHelper extends SQLiteOpenHelper{
20
21 private static final String DB_NAME = "EXAMDB";
22
23 //private static final String ID_COLUMN = "id";
24 private static final String NAME_COLUMN = "name";
25 private static final String EMAIL_COLUMN = "email";
26 private static final String PASS_COLUMN = "password";
27 private static final String TABLE_NAME = "examTable";
28 //private static final String DB_VERSION = "dbVersion";
29
30 private static final String COMMA = ",";
31 private static final String PRIMARY_KEY = " PRIMARY KEY";
32 //private static final String AUTO_INCREMENT = " AUTOINCREMENT";
33 //private static final String TEXT_TYPE = " TEXT";
34 //private static final String INTEGER_TYPE = " INTEGER";
35 private static final String VARCHAR_TYPE = " VARCHAR";
36
37
38 private static final String TABLE_CREATE_QUERY = "CREATE TABLE IF NOT EXISTS "+ TABLE_NAME + "(" +
39 NAME_COLUMN + VARCHAR_TYPE + COMMA +
40 EMAIL_COLUMN + VARCHAR_TYPE + PRIMARY_KEY + COMMA +
41 PASS_COLUMN + VARCHAR_TYPE + ")";
42
43
44 private static final String SELECT_ALL_QUERY = "Select * From " + TABLE_NAME;
45
46
47
48 private Context context;
49 private SQLiteDatabase.CursorFactory cursorFactory;
50
51
52 //Constructor, Database can be created/opened by calling this constructor
53
54 public DBHelper(Context context, int version){
55 super(context, DB_NAME, null, version);
56 this.context = context;
57 }
58 public DBHelper(Context context, SQLiteDatabase.CursorFactory cursorFactory, int version){
59 super(context, DB_NAME, cursorFactory, version);
60 this.context = context;
61 this.cursorFactory = cursorFactory;
62 }
63
64
65 @Override
66 public void onOpen(SQLiteDatabase db) {
67 super.onOpen(db);
68
69 }
70
71 @Override
72 public void onCreate(SQLiteDatabase dBase) {
73 try {
74 //Log.d("Creating_DB_from", "OnCreate");
75 dBase.execSQL(TABLE_CREATE_QUERY);
76 } catch (Exception e){
77 //Log.d("Creating Exception ", "Creating Exception : "+e.getMessage());
78 }
79
80 }
81
82 @Override
83 public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
84
85 }
86
87 public long insertRow(Info info){
88 SQLiteDatabase dBase;
89
90 try {
91
92 dBase = this.getWritableDatabase();
93 ContentValues values = new ContentValues();
94 values.put(NAME_COLUMN, info.getName());
95 values.put(EMAIL_COLUMN, info.getEmail());
96 values.put(PASS_COLUMN, info.getPassword());
97
98 dBase.insert(TABLE_NAME, null, values);
99 dBase.close();
100 return 1;
101
102 } catch (SQLiteException ex){
103
104 //e.printStackTrace(); //comment printstack before releasing
105 }
106 return -1;
107 }
108
109
110 //selecting all rows from database and returning an arraylist of a class type
111 public ArrayList<Info> getAllRows(){
112 SQLiteDatabase dBase;
113 try{
114 dBase = this.getReadableDatabase();
115 ArrayList<Info> profiles = new ArrayList<>();
116 Info info = new Info();
117 Cursor cursor = dBase.rawQuery(SELECT_ALL_QUERY, null);
118
119 if (cursor.getCount()<=0)
120 return null;
121
122 DataContainer indices = new DataContainer(cursor);
123 cursor.moveToFirst();
124 do {
125 //Setting All property of info object then add to the arraylist
126 info.setName(cursor.getString(indices.nameColumnIndex));
127 info.setEmail(cursor.getString(indices.emailColumnIndex));
128 info.setPassword(cursor.getString(indices.passColumnIndex));
129 profiles.add(info);
130 }while (cursor.moveToNext());
131
132 cursor.close();
133 dBase.close();
134 return profiles;
135
136 } catch (SQLException e){
137 e.printStackTrace(); //comment print stack before releasing
138 }
139 return null;
140 }
141
142 //Authorize an user to log in
143 public int authorizeUser(String email, String userPass){
144 SQLiteDatabase dBase;
145
146 try{
147 dBase = this.getReadableDatabase();
148 String[] arg = {email, userPass};
149 // 1st question mark will be replaced with email and 2nd one with userPass, last parameter "1" is the limit
150 Cursor cursor = dBase.query(true, TABLE_NAME, null, EMAIL_COLUMN+"=? AND "+ PASS_COLUMN + "=?",
151 arg, null, null, null, "1");
152 if(cursor.getCount()>0){
153 //email password matched
154
155 cursor.close();
156 dBase.close();
157
158 return 1;
159 } else {
160 cursor.close();
161 dBase.close();
162
163 return 0;
164 }
165
166 } catch (SQLiteException e){
167 e.printStackTrace(); //comment print stack before releasing
168 }
169 return -1;
170 }
171
172
173 public Info getAuthorizedUser(String email, String userPass){
174 String[] arg = {email, userPass};
175
176 SQLiteDatabase dBase;
177 try {
178 dBase = this.getReadableDatabase();
179 Cursor cursor = dBase.query(true, TABLE_NAME, null, EMAIL_COLUMN+ "=? AND "+ PASS_COLUMN+"=?", arg, null, null, null, "1");
180 if(cursor.getCount()>0){
181 //user is found, extract all info(s) from cursor
182 cursor.moveToFirst();
183 DataContainer indices = new DataContainer(cursor);
184
185 //Make an object of fetched data to return
186 Info info = new Info(
187 cursor.getString(indices.nameColumnIndex),
188 cursor.getString(indices.emailColumnIndex),
189 cursor.getString(indices.passColumnIndex));
190 cursor.close();
191 dBase.close();
192 return info;
193 }
194 } catch (SQLException e){
195 //e.printStackTrace(); //comment print stack before releasing
196
197 }
198
199 return null;
200 }
201 public Info getAuthorizedUser(String email){
202 String[] arg = {email};
203
204 SQLiteDatabase dBase;
205 try {
206 dBase = this.getReadableDatabase();
207 Cursor cursor = dBase.query(true, TABLE_NAME, null, EMAIL_COLUMN+ "=?", arg, null, null, null, "1");
208 if(cursor.getCount()>0){
209 //user is found, extract all info(s) from cursor
210 cursor.moveToFirst();
211 DataContainer indices = new DataContainer(cursor);
212
213 //Make an object of fetched data to return
214 Info info = new Info(
215 cursor.getString(indices.nameColumnIndex),
216 cursor.getString(indices.emailColumnIndex),
217 cursor.getString(indices.passColumnIndex));
218 cursor.close();
219 dBase.close();
220 return info;
221 }
222 } catch (SQLException e){
223 //e.printStackTrace(); //comment print stack before releasing
224
225 }
226
227 return null;
228 }
229
230
231
232 private class DataContainer{
233
234 int nameColumnIndex;
235 int emailColumnIndex;
236 int passColumnIndex;
237 DataContainer(Cursor cursor){
238 this.nameColumnIndex = cursor.getColumnIndex(NAME_COLUMN);
239 this.emailColumnIndex = cursor.getColumnIndex(EMAIL_COLUMN);
240 this.passColumnIndex = cursor.getColumnIndex(PASS_COLUMN);
241 }
242
243 public int getIndex(Cursor cursor, String columnName){
244 return cursor.getColumnIndex(columnName);
245 }
246
247 }
248}
249
250insert_tableName1()
251
252insert_tableName2()
253
254insert_table(String tableName, Info info){
255 dBase = this.getWritableDatabase();
256 ContentValues values = new ContentValues();
257 values.put(NAME_COLUMN, info.getName());
258 values.put(EMAIL_COLUMN, info.getEmail());
259 values.put(PASS_COLUMN, info.getPassword());
260
261 dBase.insert(tableName, null, values);
262 dBase.close();
263 }