· 10 years ago · Apr 24, 2016, 09:36 PM
1public class DatabaseHelper extends SQLiteOpenHelper {
2 private static final String DATABASE_NAME = "FYPSQLApplicationA.db";
3
4 private static int DATABASE_VERSION = 3;
5 private SQLiteDatabase SQLITEDATABASE;
6
7
8 /*Users Table*/
9 public static final String USERS = "USERS";
10 // public static final String COLUMN_ID = "USER_ID";
11 public static final String COLUMN_USERNAME = "USERNAME";
12 public static final String COLUMN_PASSWORD = "PASSWORD";
13 public static final String COLUMN_NAME = "NAME";
14 public static final String COLUMN_EMAIL = "EMAIL";
15 public static final String COLUMN_PHRASE = "PHRASE";
16
17 private static final String CREATE_USERS = "CREATE TABLE " + USERS +
18 "( " +
19 COLUMN_USERNAME + " TEXT PRIMARY KEY , " +
20 COLUMN_PASSWORD + " TEXT NOT NULL , " +
21 COLUMN_NAME + " TEXT , " +
22 COLUMN_EMAIL + " TEXT , " +
23 COLUMN_PHRASE + " TEXT NOT NULL );";
24
25 // MAY INCLUDE A USER ID COLUMN COLUMN_ID + " INT NOT NULL , " +
26
27 /*Topics Table*/
28 public static final String TOPICS = "TOPICS";
29 public static final String TOPIC_ID = "TOPIC_ID";
30 public static final String TOPIC_NAME = "TOPIC_NAME";
31 public static final String CHAPTER_ID = "CHAPTER_ID";
32 public static final String CHAPTER_NAME = "CHAPTER_NAME";
33
34 private static final String CREATE_TOPICS = " CREATE TABLE " + TOPICS +
35 " ( "
36 + TOPIC_ID + " INT PRIMARY KEY , "
37 + TOPIC_NAME + " TEXT NOT NULL , "
38 + CHAPTER_ID + " INT NOT NULL , "
39 + CHAPTER_NAME + " TEXT NOT NULL " + ");";
40
41
42 /*Results Table*/
43 private static final String RESULTS = "RESULTS";
44// private static final String TEST_COLUMN_USERNAME = "USERNAME";
45 private static final String COLUMN_SCORE = "SCORE";
46// private static final String COLUMN_TIMES_TAKEN = "TIMES_TAKEN";
47 private static final String COLUMN_TIMESTAMP = "TIMESTAMP";
48
49 private static final String CREATE_RESULTS = " CREATE TABLE " + RESULTS +
50 " ( " +
51 COLUMN_USERNAME + " TEXT NOT NULL , " +
52 TOPIC_ID + " INT NOT NULL , " +
53 COLUMN_SCORE + " SCORE INT NOT NULL , " +
54 COLUMN_TIMESTAMP + " TIMESTAMP TEXT NOT NULL , " +
55 "FOREIGN KEY(TOPIC_ID) REFERENCES TOPICS(TOPIC_ID) " +
56 "FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME) " + "); ";
57
58
59 /*Questions Table*/
60 private static final String QUESTIONS = "QUESTIONS";
61 private static final String QUESTIONS_ID = "QUESTION_ID";
62 private static final String QUESTIONTEXT = "QUESTION";
63 private static final String ANSWER_A = "ANSWER_A"; //option a
64 private static final String ANSWER_B = "ANSWER_B"; //option b
65 private static final String ANSWER_C = "ANSWER_C"; //option c
66 private static final String QUESTION_ANSWER = "ANSWER"; //correct option
67
68 private static final String CREATE_QUESTIONS = " CREATE TABLE " +
69 QUESTIONS + " ( " +
70 TOPIC_ID + " INT NOT NULL , " +
71 QUESTIONS_ID + " INT PRIMARY KEY , " +
72 QUESTIONTEXT + " TEXT NOT NULL , " +
73 ANSWER_A + " TEXT NOT NULL , " +
74 ANSWER_B + " TEXT NOT NULL , " +
75 ANSWER_C + " TEXT NOT NULL , " +
76 QUESTION_ANSWER + " INT NOT NULL , " +
77 "FOREIGN KEY(TOPIC_ID) REFERENCES TOPICS(TOPIC_ID) " + "); ";
78
79
80
81 private static Context context;
82
83 public DatabaseHelper(Context c, String name, SQLiteDatabase.CursorFactory factory, int version) {
84 super(c, name, factory, version);
85 }
86 public DatabaseHelper(Context c) {
87 super(context = c, DATABASE_NAME, null, DATABASE_VERSION);
88 }
89
90 @Override
91 public void onCreate(SQLiteDatabase db) {
92 SQLITEDATABASE = db;
93 db.execSQL(CREATE_USERS);
94 db.execSQL(CREATE_TOPICS);
95 db.execSQL(CREATE_QUESTIONS);
96 db.execSQL(CREATE_RESULTS);
97 addQuestions();
98 addTopics();
99 this.SQLITEDATABASE = db;
100 }
101
102 @Override
103 public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
104 // Drop older table if existed
105 String queryuser = "DROP TABLE IF EXISTS " + USERS;
106 String queryquestion = "DROP TABLE IF EXISTS " + QUESTIONS;
107 String querytopic = "DROP TABLE IF EXISTS " + TOPICS;
108 String queryresult = "DROP TABLE IF EXISTS " + RESULTS;
109
110 db.execSQL(queryuser);
111 db.execSQL(queryquestion);
112 db.execSQL(querytopic);
113 db.execSQL(queryresult);
114
115 // Create tables again
116 onCreate(db);
117 }
118
119
120
121
122
123
124
125
126 public void insertScore (ScoreDetails SD) {
127 SQLITEDATABASE = this.getWritableDatabase();
128 ContentValues values = new ContentValues();
129
130 values.put(COLUMN_USERNAME, SD.GetUsername());
131 values.put(TOPIC_ID, SD.GetTopic());
132 values.put(COLUMN_SCORE, SD.GetScore());
133 values.put(COLUMN_TIMESTAMP, SD.getCurrentTimeStamp());
134
135
136 SQLITEDATABASE.insert(RESULTS, null, values);
137 SQLITEDATABASE.close();
138 }
139
140
141 // Getting All Contacts
142 public List<ScoreDetails> getAllResults() {
143 List<ScoreDetails> ResultList = new ArrayList<ScoreDetails>();
144 // Select All Query
145
146 GlobalVariables GlobalUser = new GlobalVariables();
147
148 String selectQuery = "SELECT * FROM " + RESULTS
149 + " WHERE USERNAME = ' " + GlobalUser.GetUsername() + " ' ";
150 SQLiteDatabase db = this.getWritableDatabase();
151 Cursor cursor = db.rawQuery(selectQuery, null);
152
153 /* Cursor cursor = db.rawQuery("SELECT * FROM RESULTS WHERE " +
154 " USERNAME = " + GlobalUser.GetUsername(), null);*/
155
156 // looping through all rows and adding to list
157 if (cursor.moveToFirst()) {
158 do {
159 ScoreDetails SD = new ScoreDetails();
160 SD.SetUsername(cursor.getString(0));
161 SD.SetTopic(Integer.parseInt(cursor.getString(1)));
162 SD.SetScore(Integer.parseInt(cursor.getString(2)));
163 SD.SetCurrentTimeStamp(cursor.getString(3));
164
165 String name = cursor.getString(0) +"n"+ cursor.getString(1) +"n"+ cursor.getString(2)
166 +"n"+ cursor.getString(3);
167 Display_results.ArrayofResults.add(name);
168 // Adding contact to list
169 ResultList.add(SD);
170 } while (cursor.moveToNext());
171 }
172 // return contact list
173 return ResultList;
174 }
175
176
177}
178
179public class Display_results extends AppCompatActivity {
180private ListView gridView;
181public static ArrayList<String> ArrayofResults = new ArrayList<String>();
182
183
184@Override
185public void onCreate(Bundle savedInstanceState) {
186 super.onCreate(savedInstanceState);
187 setContentView(R.layout.display_results_layout);
188
189 DatabaseHelper db = new DatabaseHelper(this);
190 List<ScoreDetails> results = db.getAllResults();
191
192 for (ScoreDetails SD : results) {
193 String log = "Id: "+ SD.GetUsername()
194 +" ,Name: " + SD.GetTopic()
195 + " ,Phone: " + SD.GetScore()
196 + " ,Phone: " + SD.getCurrentTimeStamp() ;
197 // Writing Contacts to log
198 Log.d("Name: ", log);
199
200 }
201
202 /*db.close();*/
203
204 gridView = (ListView) findViewById(R.id.contentlist);
205
206 ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
207 android.R.layout.simple_list_item_1, ArrayofResults);
208
209 gridView.setAdapter(adapter);
210
211 gridView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
212 public void onItemClick(AdapterView<?> parent, View v,
213 int position, long id) {
214 Toast.makeText(getApplicationContext(),
215 ((TextView) v).getText(), Toast.LENGTH_SHORT).show();
216 }
217 });
218
219}
220
221/*
222 public void onPause() {
223 super.onPause();
224 finish();
225 }*/
226}