GChanna GChanna - 7 months ago 10
Java Question

How to refresh a listview that presents SQLite database

I have a quiz application that stores the result of the user and their username, the topic and the timestamp and have a small issue I am stuck on with putting this data into a listview.

I have noticed that the listview never refreshes, as you can see from the timestamps in the first photo, I have taken 4 quizzes but it doesn't get rid of the results from the previous times I have visited this activity. Image of the results in the xml activity. This is what the logcat returns from the log message in display_results.java Logcat of results

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "FYPSQLApplicationA.db";

private static int DATABASE_VERSION = 3;
private SQLiteDatabase SQLITEDATABASE;


/*Users Table*/
public static final String USERS = "USERS";
// public static final String COLUMN_ID = "USER_ID";
public static final String COLUMN_USERNAME = "USERNAME";
public static final String COLUMN_PASSWORD = "PASSWORD";
public static final String COLUMN_NAME = "NAME";
public static final String COLUMN_EMAIL = "EMAIL";
public static final String COLUMN_PHRASE = "PHRASE";

private static final String CREATE_USERS = "CREATE TABLE " + USERS +
"( " +
COLUMN_USERNAME + " TEXT PRIMARY KEY , " +
COLUMN_PASSWORD + " TEXT NOT NULL , " +
COLUMN_NAME + " TEXT , " +
COLUMN_EMAIL + " TEXT , " +
COLUMN_PHRASE + " TEXT NOT NULL );";

// MAY INCLUDE A USER ID COLUMN COLUMN_ID + " INT NOT NULL , " +

/*Topics Table*/
public static final String TOPICS = "TOPICS";
public static final String TOPIC_ID = "TOPIC_ID";
public static final String TOPIC_NAME = "TOPIC_NAME";
public static final String CHAPTER_ID = "CHAPTER_ID";
public static final String CHAPTER_NAME = "CHAPTER_NAME";

private static final String CREATE_TOPICS = " CREATE TABLE " + TOPICS +
" ( "
+ TOPIC_ID + " INT PRIMARY KEY , "
+ TOPIC_NAME + " TEXT NOT NULL , "
+ CHAPTER_ID + " INT NOT NULL , "
+ CHAPTER_NAME + " TEXT NOT NULL " + ");";


/*Results Table*/
private static final String RESULTS = "RESULTS";
// private static final String TEST_COLUMN_USERNAME = "USERNAME";
private static final String COLUMN_SCORE = "SCORE";
// private static final String COLUMN_TIMES_TAKEN = "TIMES_TAKEN";
private static final String COLUMN_TIMESTAMP = "TIMESTAMP";



private static final String CREATE_RESULTS = " CREATE TABLE " + RESULTS +
" (

" +
COLUMN_USERNAME + " TEXT NOT NULL , " +
TOPIC_ID + " INT NOT NULL , " +
COLUMN_SCORE + " SCORE INT NOT NULL , " +
COLUMN_TIMESTAMP + " TIMESTAMP TEXT NOT NULL , " +
"FOREIGN KEY(TOPIC_ID) REFERENCES TOPICS(TOPIC_ID) " +
"FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME) " + "); ";


/*Questions Table*/
private static final String QUESTIONS = "QUESTIONS";
private static final String QUESTIONS_ID = "QUESTION_ID";
private static final String QUESTIONTEXT = "QUESTION";
private static final String ANSWER_A = "ANSWER_A"; //option a
private static final String ANSWER_B = "ANSWER_B"; //option b
private static final String ANSWER_C = "ANSWER_C"; //option c
private static final String QUESTION_ANSWER = "ANSWER"; //correct option

private static final String CREATE_QUESTIONS = " CREATE TABLE " +
QUESTIONS + " ( " +
TOPIC_ID + " INT NOT NULL , " +
QUESTIONS_ID + " INT PRIMARY KEY , " +
QUESTIONTEXT + " TEXT NOT NULL , " +
ANSWER_A + " TEXT NOT NULL , " +
ANSWER_B + " TEXT NOT NULL , " +
ANSWER_C + " TEXT NOT NULL , " +
QUESTION_ANSWER + " INT NOT NULL , " +
"FOREIGN KEY(TOPIC_ID) REFERENCES TOPICS(TOPIC_ID) " + "); ";



private static Context context;

public DatabaseHelper(Context c, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(c, name, factory, version);
}
public DatabaseHelper(Context c) {
super(context = c, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
SQLITEDATABASE = db;
db.execSQL(CREATE_USERS);
db.execSQL(CREATE_TOPICS);
db.execSQL(CREATE_QUESTIONS);
db.execSQL(CREATE_RESULTS);
addQuestions();
addTopics();
this.SQLITEDATABASE = db;
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
// Drop older table if existed
String queryuser = "DROP TABLE IF EXISTS " + USERS;
String queryquestion = "DROP TABLE IF EXISTS " + QUESTIONS;
String querytopic = "DROP TABLE IF EXISTS " + TOPICS;
String queryresult = "DROP TABLE IF EXISTS " + RESULTS;

db.execSQL(queryuser);
db.execSQL(queryquestion);
db.execSQL(querytopic);
db.execSQL(queryresult);

// Create tables again
onCreate(db);
}








public void insertScore (ScoreDetails SD) {
SQLITEDATABASE = this.getWritableDatabase();
ContentValues values = new ContentValues();

values.put(COLUMN_USERNAME, SD.GetUsername());
values.put(TOPIC_ID, SD.GetTopic());
values.put(COLUMN_SCORE, SD.GetScore());
values.put(COLUMN_TIMESTAMP, SD.getCurrentTimeStamp());


SQLITEDATABASE.insert(RESULTS, null, values);
SQLITEDATABASE.close();
}


// Getting All Contacts
public List<ScoreDetails> getAllResults() {
List<ScoreDetails> ResultList = new ArrayList<ScoreDetails>();
// Select All Query



GlobalVariables GlobalUser = new GlobalVariables();

/* String selectQuery = "SELECT * FROM " + RESULTS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);*/

String log = "global username "+ GlobalUser.GetUsername();
// Writing Contacts to log
Log.d("Name: ", log);

String log11 = " SELECT * FROM RESULTS WHERE " + " USERNAME = " + " '" + username + "' " ;
// Writing Contacts to log
Log.d("LOG QUERY Name: ", log11);

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(" SELECT * FROM RESULTS WHERE " + " USERNAME = " +
" '" + username + "' ", null);

/* String selectQuery = "SELECT * FROM " + RESULTS + " WHERE USERNAME = ?";*/

/* SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, new String[]{GlobalUser.GetUsername()});*/

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
ScoreDetails SD = new ScoreDetails();
SD.SetUsername(cursor.getString(0));
SD.SetTopic(Integer.parseInt(cursor.getString(1)));
SD.SetScore(Integer.parseInt(cursor.getString(2)));
SD.SetCurrentTimeStamp(cursor.getString(3));

String name = cursor.getString(0) +"\n"+ cursor.getString(1) +"\n"+ cursor.getString(2)
+"\n"+ cursor.getString(3);
Display_results.ArrayofResults.add(name);
// Adding contact to list
ResultList.add(SD);
} while (cursor.moveToNext());
}
// return contact list
return ResultList;
}


}


Display_results.java

public class Display_results extends AppCompatActivity {
private ListView gridView;
public static ArrayList<String> ArrayofResults = new ArrayList<String>();


@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.display_results_layout);

DatabaseHelper db = new DatabaseHelper(this);
List<ScoreDetails> results = db.getAllResults(GlobalVariables.getInstance().GetUsername());

for (ScoreDetails SD : results) {
String log = "Id: "+ SD.GetUsername()
+" ,Name: " + SD.GetTopic()
+ " ,Phone: " + SD.GetScore()
+ " ,Phone: " + SD.getCurrentTimeStamp() ;
// Writing Contacts to log
Log.d("Name: ", log);

}

/*db.close();*/

gridView = (ListView) findViewById(R.id.contentlist);

ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, ArrayofResults);

gridView.setAdapter(adapter);

gridView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
public void onItemClick(AdapterView<?> parent, View v,
int position, long id) {
Toast.makeText(getApplicationContext(),
((TextView) v).getText(), Toast.LENGTH_SHORT).show();
}
});

}

/*
public void onPause() {
super.onPause();
finish();
}*/
}

Answer
String selectQuery = "SELECT * FROM " + RESULTS + " WHERE USERNAME =?";

SQLiteDatabase db = this.getWritableDatabase();

Cursor cursor = db.rawQuery(selectQuery, new String[]{GlobalUser.GetUsername()});