Iam delevoping Android app with local database.I need to get Highscore of a every user individually.I tried some code below to get user score in Textview
EDIT : CREATE DATABASE
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "Mydatabase.db";
private static final String TABLE_REGISTER= "register";
public static final String KEY_ID = "id";
public static final String KEY_FIRST_NAME = " first_name";
public static final String KEY_LAST_NAME = "last_name";
public static final String KEY_EMAIL_ID="email_id";
public static final String KEY_MOB_NO = "mobile_number";
public static final String KEY_PASSWORD = "password";
public static final String KEY_SCORE="score";
public static final String CREATE_TABLE="CREATE TABLE " + TABLE_REGISTER + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_FIRST_NAME + " TEXT,"+ KEY_LAST_NAME + " TEXT,"+ KEY_EMAIL_ID + " TEXT,"
+ KEY_MOB_NO + " TEXT," + KEY_PASSWORD + " TEXT ," + KEY_SCORE +
" INTEGER)";
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGISTER);
// Create tables again
onCreate(db); }
void addregister(RegisterData registerdata)
// code to add the new register
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FIRST_NAME,registerdata.getfirstName()); // register first Name
values.put(KEY_lAST_NAME, registerdata. getlastName() ); // register last name
values.put(KEY_EMAIL_ID, registerdata.getEmailId());//register email id
values.put(KEY_MOB_NO, registerdata.getMobNo());//register mobile no
values.put(KEY_PASSWORD, registerdata.getPassword());
values.put(KEY_SCORE,registerdata.getScore());
// Inserting Row
db.insert(TABLE_REGISTER, null, values);
db.close(); // Closing database connection
}
EDIT:
Below code is used to get single highscore
public int getScoreByUsername(String username){
int highscore = 0;
String[] col=new String[]{"MAX ( " + KEY_SCORE + ") AS Max_Score"};
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor=db.query(TABLE_REGISTER,col,
"email_id=?",new String[]{username},null,null,null,null);
if(cursor.getCount()<1){
cursor.close();
return 0;
}
else if(cursor.getCount()>=1 && cursor.moveToFirst()){
highscore = cursor.getInt(cursor.getColumnIndex(KEY_SCORE));
cursor.close();
}
return highscore;
}
Below code it to used to display highscore in textview
MAINACTIVITY
highscorelabel.setText(String.valueOf(db.getScoreByUsername(username)));
But iam getting java.lang.IllegalStateException Error.Here my logcat
Caused by: java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
at android.database.CursorWindow.nativeGetLong(Native Method)
at android.database.CursorWindow.getLong(CursorWindow.java:511)
at android.database.CursorWindow.getInt(CursorWindow.java:578)
at android.database.AbstractWindowedCursor.getInt(AbstractWindowedCursor.java:69)
at com.example.mathu.loginregister.DataBAseHandler.getScoreByUsername(DataBAseHandler.java:201)
at com.example.mathu.loginregister.ResultActivity.onCreate(ResultActivity.java:69)
at android.app.Activity.performCreate(Activity.java:6847)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1119)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2677)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2785)
at android.app.ActivityThread.-wrap12(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1532)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:163)
at android.app.ActivityThread.main(ActivityThread.java:6342)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:880)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:770)
Your issue is that you are trying to get data from a column that does not exist in the Cursor.
Change
highscore = cursor.getInt(cursor.getColumnIndex(KEY_SCORE));
To
highscore = cursor.getInt(cursor.getColumnIndex("Max_Score"));
The reasoning is explained by the comment :-
Continuing on, the getScoreByEmail method would also result in the same error.
The reason is that a Cursor only has the column names that you specified to extract.
The Cursor does not have the column names as per the table(s) used as it's source (although it can e.g.
SELECT *says get all columns from the table(s)). As you are effectively sayingSELECT max(score) AS Max_Score FROM register WHERE email_id=the_value_passedthen the Cursor will have just one column and that will be named Max_Score (hence there is no score column and thus the -1 from the getColumnIndex).
P.S. the method could be simplified to :-
public int getScoreByUsername(String username){
int highscore = 0;
String[] col=new String[]{"MAX ( " + KEY_SCORE + ") AS Max_Score"};
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor=db.query(TABLE_REGISTER,col,
"email_id=?",new String[]{username},null,null,null,null);
if(cursor.moveToFirst()) {
highscore = cursor.getInt(cursor.getColumnIndex("Max_Score"));
}
cursor.close();
return highscore;
}
moveToFirst, if the count is less than 1 then moveToFirst will return false.The following is a working example using your code with some gaps filled in. The result (albeit that there is only 1 row per user) shows that the code works.
That is the log contains (as expected) :-
Mary's High Score is 70000
Fred's High Score is 50000
In the MainActivity it has :-
RegisterData fred = new RegisterData("Fred","Bloggs","[email protected]","0000 000 000","mypassword",50000);
RegisterData mary = new RegisterData("Mary","Smith","marysmith@emailcom","0000 000 000","mypassword",60000);
mary.addScore(10000);
This is then followed by (adds the 2 rows to the register table):-
MyDBHlpr.addregister(fred);
MyDBHlpr.addregister(mary);
Which is then followed by (to retrieve the High Score according to the EmailId and then output the values to the log) :- :-
int mary_max_score = MyDBHlpr.getScoreByUsername(mary.getEmailId());
int fred_max_score = MyDBHlpr.getScoreByUsername(fred.getEmailId());
Log.d("HIGHSCORES","Mary's High Score is " + String.valueOf(mary_max_score));
Log.d("HIGHSCORES", "Fred's High Score is " + String.valueOf(fred_max_score));
The code in full is :-
MainActivity.java
public class MainActivity extends AppCompatActivity {
DBHelper MyDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
MyDBHlpr = new DBHelper(this);
RegisterData fred = new RegisterData("Fred","Bloggs","[email protected]","0000 000 000","mypassword",50000);
RegisterData mary = new RegisterData("Mary","Smith","marysmith@emailcom","0000 000 000","mypassword",60000);
mary.addScore(10000);
MyDBHlpr.addregister(fred);
MyDBHlpr.addregister(mary);
int mary_max_score = MyDBHlpr.getScoreByUsername(mary.getEmailId());
int fred_max_score = MyDBHlpr.getScoreByUsername(fred.getEmailId());
Log.d("HIGHSCORES","Mary's High Score is " + String.valueOf(mary_max_score));
Log.d("HIGHSCORES", "Fred's High Score is " + String.valueOf(fred_max_score));
}
}
DBHelper.java (note includes some debugging code, see below)
public class DBHelper extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "Mydatabase.db";
private static final String TABLE_REGISTER= "register";
public static final String KEY_ID = "id";
public static final String KEY_FIRST_NAME = " first_name";
public static final String KEY_LAST_NAME = "last_name";
public static final String KEY_EMAIL_ID="email_id";
public static final String KEY_MOB_NO = "mobile_number";
public static final String KEY_PASSWORD = "password";
public static final String KEY_SCORE="score";
public static final String CREATE_TABLE="CREATE TABLE " + TABLE_REGISTER + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_FIRST_NAME + " TEXT,"+ KEY_LAST_NAME + " TEXT,"+ KEY_EMAIL_ID + " TEXT,"
+ KEY_MOB_NO + " TEXT," + KEY_PASSWORD + " TEXT ," + KEY_SCORE +
" INTEGER)";
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.getWritableDatabase();
}
@Override
public synchronized void close() {
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGISTER);
// Create tables again
onCreate(db); }
void addregister(RegisterData registerdata)
// code to add the new register
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_FIRST_NAME,registerdata.getfirstName()); // register first Name
values.put(KEY_LAST_NAME, registerdata. getlastName() ); // register last name
values.put(KEY_EMAIL_ID, registerdata.getEmailId());//register email id
values.put(KEY_MOB_NO, registerdata.getMobNo());//register mobile no
values.put(KEY_PASSWORD, registerdata.getPassword());
values.put(KEY_SCORE,registerdata.getScore());
// Inserting Row
db.insert(TABLE_REGISTER, null, values);
db.close(); // Closing database connection
}
public int getScoreByUsername(String username){
int highscore = 0;
String[] col=new String[]{"MAX ( " + KEY_SCORE + ") AS Max_Score"};
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor=db.query(TABLE_REGISTER,col,
"email_id=?",new String[]{username},null,null,null,null);
//<<<<<<<<<< DEBUG CODE
Log.d("CURSOR COUNT","There are " + String.valueOf(cursor.getCount()) + " rows in the Cursor.");
logAllRows();
//<<<<<<<<<< END OF DEBUG CODE
if(cursor.moveToFirst()) {
highscore = cursor.getInt(cursor.getColumnIndex("Max_Score"));
}
cursor.close();
return highscore;
}
//<<<<<<<<<< ADDED METHOD TO SHOW ALL DATA IN THE REGISTER TABLE
public void logAllRows() {
SQLiteDatabase db = this.getWritableDatabase();
Cursor csr = db.query(TABLE_REGISTER,null,null,null,null,null,null);
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
RegisterData.java (created based upon your code, although the addScore method was improvised)
public class RegisterData {
private String firstName;
private String lastName;
private String EmailId;
private String MobNo;
private String Password;
private int Score;
RegisterData (String firstName,
String lastName,
String EmailId,
String MobNo,
String Password, int Score) {
this.firstName = firstName;
this.lastName = lastName;
this.EmailId = EmailId;
this.MobNo = MobNo;
this.Password = Password;
this.Score = Score;
}
RegisterData (String firstName,
String lastName,
String EmailId,
String MobNo,
String Password) {
new RegisterData(firstName, lastName, EmailId, MobNo, Password, 0);
}
public String getfirstName() {
return firstName;
}
public void setfirstName(String firstName) {
this.firstName = firstName;
}
public String getlastName() {
return lastName;
}
public void setlastName(String lastName) {
this.lastName = lastName;
}
public String getEmailId() {
return EmailId;
}
public void setEmailId(String emailId) {
EmailId = emailId;
}
public String getMobNo() {
return MobNo;
}
public void setMobNo(String mobNo) {
MobNo = mobNo;
}
public String getPassword() {
return Password;
}
public void setPassword(String password) {
Password = password;
}
public int getScore() {
return Score;
}
public void setScore(int score) {
Score = score;
}
public void addScore(int score) {
Score = Score + score;
}
}
As such, intrinsically your code that you have shown is fine. The issue with retrieving 0 must lie elsewhere. It could be that you are getting no rows.
However, some code has been added to the DBHelper class, which you could copy, which should at least split the problem between being an issue with adding the data to the table or it being an issue with the data in the table.
There are 2 parts to the dubegging code, both are added to the getScoreByusername method.
The first part prints the number of rows extracted, you would expect it to be 1. If it is 1 and the max_score is 0 then the issue must be that the score column is 0, null or a value that cannot be converted to a number (thus 0 is used) by the getInt method. If it's 1 then the second part should give an indication of the issue.
using the example above resulted in :-
09-16 10:12:18.618 1535-1535/? D/CURSOR COUNT: There are 1 rows in the Cursor.
The second part extracts all rows/columns from the table and uses the DatabaseUtils dumpCursor method to display the data in the cursor.
This, for the example above (a row for Fred and a row for Mary), would look like :-
09-16 10:12:18.618 1535-1535/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5345b12c
09-16 10:12:18.622 1535-1535/? I/System.out: 0 {
id=1
first_name=Fred
last_name=Bloggs
[email protected]
mobile_number=0000 000 000
password=mypassword
score=50000
}
1 {
id=2
first_name=Mary
last_name=Smith
email_id=marysmith@emailcom
mobile_number=0000 000 000
password=mypassword
score=70000
}
<<<<<
As you can see the score column for Fred has 50000 and the score column for Mary has 70000.
If the first debug shows that 1 row was selected then it is likely that the score will not be a valid number and hence the 0.
If the first debug shows 0 then it may be that the email_id column is not as expected.