ID won't save in long format in database Sqlite

Question

I'm making an Android Note Taking app, which save a note and display it in list of notes. if you touch each note it show a title and details of it in another layout. i can save note and i can show them in list but my problem is when i try to use intent to show that specific note's title and details.

what is do is use intent.putExtra("ID",...) and get that intent and show the note's detail and title with use of its ID. but i get "android.database.CursorIndexOutOfBoundsException" error and app crash.

this is part of my Note class:

public class Note {

private String Title, Content, Date, Time;
private long ID;

Note(){}
Note(String Title,String Content,String Date,String Time){
    this.Title = Title;
    this.Content = Content;
    this.Date = Date;
    this.Time = Time;
}

Note(long ID,String Title,String Content,String Date,String Time) {
    this.ID = ID;
    this.Title = Title;
    this.Content = Content;
    this.Date = Date;
    this.Time = Time;
}

this is my NoteDataBase:

public class NoteDataBase extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 2;
private static final String DATABASE_NAME = "notedbs";
private static final String DATABASE_TABLE = "notestables";

//column name for database tables
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_CONTENT = "content";
private static final String KEY_DATE = "date";
private static final String KEY_TIME = "time";

public NoteDataBase(Context context){
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db){
    //create table
    String query = "CREATE TABLE " + DATABASE_TABLE + "("+
            KEY_ID + " INT PRIMARY KEY," +
            KEY_TITLE + " TEXT," +
            KEY_CONTENT + " TEXT," +
            KEY_DATE + " TEXT," +
            KEY_TIME + " TEXT" + ")";

    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){

    if(oldVersion >= newVersion) {
        return;
    } else {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }
}

public long addNote(Note note) {
    SQLiteDatabase db= this.getWritableDatabase();
    ContentValues c = new ContentValues();
    c.put(KEY_TITLE,note.getTitle());
    c.put(KEY_CONTENT,note.getContent());
    c.put(KEY_DATE,note.getDate());
    c.put(KEY_TIME,note.getTime());

    long ID = db.insert(DATABASE_TABLE,"null",c);
    //c.put(KEY_ID,ID);
    Log.d("Inserted", "addNote: note with id number " + KEY_ID + " has been inserted");
    return ID;
}

public Note getNote(long ID){
    //Select * from DatabaseTable where id = 1
    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.query(DATABASE_TABLE,
            new String []{KEY_ID,KEY_TITLE,KEY_CONTENT,
            KEY_DATE,KEY_TIME},KEY_ID + "=?",
            new String[]{String.valueOf(ID)},
            null, null,null);

    if(cursor != null)
        cursor.moveToFirst();

    return new Note(cursor.getLong(0), cursor.getString(1), cursor.getString(2),
            cursor.getString(3),cursor.getString(4));
    }

public List<Note> getNotes() {
    SQLiteDatabase db = getReadableDatabase();
    List<Note> allNotes = new ArrayList<>();
    // Select * from databaseTable

    String query = "SELECT * FROM "+ DATABASE_TABLE;
    Cursor cursor = db.rawQuery(query,null);

    if(cursor.moveToFirst()){
        do{
            Note note = new Note();
            note.setID(cursor.getLong(0));
            note.setTitle(cursor.getString(1));
            note.setContent(cursor.getString(2));
            note.setDate(cursor.getString(3));
            note.setTime(cursor.getString(4));

            allNotes.add(note);

        }while(cursor.moveToNext());
    }
    return allNotes;
}

}

this is my DetailActivity :

public class DetailActivity extends AppCompatActivity {

@Override
protected void onCreate(Bundle savedInstanceState){
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_detail);

    Toolbar toolbar = findViewById(R.id.toolbar);
    setSupportActionBar(toolbar);

    if (getSupportActionBar() != null){
        getSupportActionBar().setDisplayHomeAsUpEnabled(true);
        getSupportActionBar().setDisplayShowHomeEnabled(true);
    }

    Intent intent = getIntent();
    Long id = intent.getLongExtra("ID",0);

    NoteDataBase db = new NoteDataBase(this);
    Note note = db.getNote(id);
    Toast.makeText(this, "Title is "+ note.getTitle(), Toast.LENGTH_SHORT).show();

my error is in lines of both my Intent in DetailActivity and getNote method of NoteDataBase.

i should say because there seems to be misunderstandings..... but my real problem isn't that error. my problem is it looks like my Id won't save at all!! it show default value of 0 for all my notes! — sarvenaz, Jan 03 '21 at 20:47
When you are saving u r using Int, but retrieving is done using long. — NehaK, Jan 03 '21 at 20:50
i used INT in sqlite because i thought that was the only thing that i could use — sarvenaz, Jan 03 '21 at 20:52
based on this: https://stackoverflow.com/questions/8672473/is-there-type-long-in-sqlite — sarvenaz, Jan 03 '21 at 20:53

score 0 · Answer 1 · answered Jan 03 '21 at 20:56

By defining the column id of your table as:

INT PRIMARY KEY

it is not defined as AUTOINCREMENT, so in every row that you add to the table, the column id is null, because you don't provide any value for it.
The value that is returned by the method addNote(), when you insert a new row, is the value of the column rowid and not the column id.

You must define the column as:

INTEGER PRIMARY KEY

You can also add AUTOINCREMENT in the above definition, if you don't want any deleted ids to be reused.
After you make this change you must uninstall the app from the device, so that the db is deleted and rerun to recreate the db and the table.

Also in the method getNote(), first check with moveToFirst() if the query returned any row:

public Note getNote(long ID){
    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.query(DATABASE_TABLE,
            new String []{KEY_ID,KEY_TITLE,KEY_CONTENT,
            KEY_DATE,KEY_TIME},KEY_ID + "=?",
            new String[]{String.valueOf(ID)},
            null, null,null);

    if(cursor.moveToFirst())
        return new Note(
            cursor.getLong(0), cursor.getString(1), cursor.getString(2),
            cursor.getString(3),cursor.getString(4)
        );
    else return null;
}

ID won't save in long format in database Sqlite

1 Answers1