java sqlite android-studio android-sqlite

"No such column: id" when using SQLite in Android Studio

Error is in NoteDatabase class. It originates when I try to use the db.getNote(ID) method in the AddNote class to add a new note to database. The error says that I do not have an id column, whereas I do. Any help would be appreciated. Error message is posted below.

main activity

package com.example.multi_note;

import androidx.annotation.NonNull;
import androidx.annotation.RequiresApi;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;
import androidx.appcompat.widget.Toolbar;

import android.content.Intent;
import android.os.Build;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.widget.Toast;

import java.util.List;

public class MainActivity extends AppCompatActivity {

RecyclerView recyclerView;
Adapter adapter;
List<Note> notes;
Toolbar toolbar;
NoteDatabase db;


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

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

    //calls database to get notes
    db = new NoteDatabase(this);
    notes = db.getNotes();

    //gets recyclerview
    recyclerView = (RecyclerView)findViewById(R.id.recyclerView);

    //declares adapter
    adapter = new Adapter(this, notes);


    recyclerView.setLayoutManager(new LinearLayoutManager(this));
    recyclerView.setAdapter(adapter);

}


//inflates to show add_menu toolbar
@Override
public boolean onCreateOptionsMenu(Menu menu) {
    MenuInflater inflater = getMenuInflater();
    inflater.inflate(R.menu.add_menu, menu);
    return true;
}

//identifies which item in menu was clicked and can do stuff with that
@Override
public boolean onOptionsItemSelected(@NonNull MenuItem item) {
    if(item.getItemId() == R.id.add) {
        Intent intent = new Intent(this, AddNote.class);
        startActivity(intent);

        //Toast.makeText(this,"Add",Toast.LENGTH_SHORT).show();
    }
    return super.onOptionsItemSelected(item);
}


}

Note class

package com.example.multi_note;

import android.content.Context;
import android.content.Intent;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;
import android.widget.Toast;

import androidx.annotation.NonNull;
import androidx.recyclerview.widget.RecyclerView;

import java.util.List;

public class Adapter extends RecyclerView.Adapter<Adapter.ViewHolder> {
MainActivity mainAct;
List<Note> notes;

Adapter(MainActivity ma, List<Note> notes) {
    this.notes = notes;
    this.mainAct = ma;
}

@NonNull
@Override
public Adapter.ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
    View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.custom_listview, parent, false);
    return new ViewHolder(view);
}

@Override
public void onBindViewHolder(@NonNull Adapter.ViewHolder holder, int i) {
    String  title    = notes.get(i).getTitle();
    String  date     = notes.get(i).getDate();
    String  time     = notes.get(i).getTime();
    long    ID       = notes.get(i).getID();
    Note n = notes.get(i);

    holder.title.setText(title);
    //Log.d("title:", n.getTitle());
    holder.date.setText(date);
    //Log.d("date:", n.getDate());
    holder.time.setText(time);
    //Log.d("time:", n.getTime());
    holder.ID.setText(String.valueOf(ID));
    //Log.d("ID:", Long.toString(n.getID()));
}

@Override
public int getItemCount() {
    return notes.size();
}

//ViewHolder class
public class ViewHolder extends RecyclerView.ViewHolder {
    TextView title;
    TextView date;
    TextView time;
    TextView ID;

    public ViewHolder(@NonNull View itemView) {
        super(itemView);

        title = (TextView)itemView.findViewById(R.id.title);
        date = (TextView)itemView.findViewById(R.id.date);
        time = (TextView)itemView.findViewById(R.id.time);
        ID = (TextView)itemView.findViewById(R.id.ID);

        itemView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent = new Intent(v.getContext(), EditNote.class);
                intent.putExtra("ID", notes.get(getAdapterPosition()).getID());
                v.getContext().startActivity(intent);
            }
        });

    }

}
}

Adapter class

package com.example.multi_note;

import android.content.Context;
import android.content.Intent;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;
import android.widget.Toast;

import androidx.annotation.NonNull;
import androidx.recyclerview.widget.RecyclerView;

import java.util.List;

public class Adapter extends RecyclerView.Adapter<Adapter.ViewHolder> {

MainActivity mainAct;
List<Note> notes;

Adapter(MainActivity ma, List<Note> notes) {
    this.notes = notes;
    this.mainAct = ma;
}

@NonNull
@Override
public Adapter.ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
    View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.custom_listview, parent, false);
    return new ViewHolder(view);
}

@Override
public void onBindViewHolder(@NonNull Adapter.ViewHolder holder, int i) {
    String  title    = notes.get(i).getTitle();
    String  date     = notes.get(i).getDate();
    String  time     = notes.get(i).getTime();
    long    ID       = notes.get(i).getID();
    Note n = notes.get(i);

    holder.title.setText(title);
    //Log.d("title:", n.getTitle());
    holder.date.setText(date);
    //Log.d("date:", n.getDate());
    holder.time.setText(time);
    //Log.d("time:", n.getTime());
    holder.ID.setText(String.valueOf(ID));
    //Log.d("ID:", Long.toString(n.getID()));
}

@Override
public int getItemCount() {
    return notes.size();
}

//ViewHolder class
public class ViewHolder extends RecyclerView.ViewHolder {
    TextView title;
    TextView date;
    TextView time;
    TextView ID;

    public ViewHolder(@NonNull View itemView) {
        super(itemView);

        title = (TextView)itemView.findViewById(R.id.title);
        date = (TextView)itemView.findViewById(R.id.date);
        time = (TextView)itemView.findViewById(R.id.time);
        ID = (TextView)itemView.findViewById(R.id.ID);

        itemView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent = new Intent(v.getContext(), EditNote.class);
                intent.putExtra("ID", notes.get(getAdapterPosition()).getID());
                v.getContext().startActivity(intent);
            }
        });

    }

}
}

Database class to store notes

package com.example.multi_note;

import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class NoteDatabase extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 2;
private static final String DATABASE_NAME = "db4";
private static final String DATABASE_TABLE = "table4";

//column names for database table
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_DETAIL = "detail";
private static final String KEY_DATE = "date";
private static final String KEY_TIME = "time";

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

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

    db.execSQL(query);
}

//if older version of DB exists then drop and make table with current version
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion >= newVersion) {
        return;
    }

    db.execSQL("DROP TABLE IF EXISTS DATABASE_TABLE");
    onCreate(db);
}

public long addNote(Note note) {
    //gets data repository in write mode
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues c = new ContentValues();

    //create map of values with column names as keys
    c.put("KEY_TITLE", note.getTitle());
    c.put("KEY_DETAIL", note.getDetail());
    c.put("KEY_DATE", note.getDate());
    c.put("KEY_TIME", note.getTime());

    //insert new row returning primary key
    long ID = db.insert("DATABASE_TABLE", null, c);
    Log.d("inserted", "ID -> " + ID);
    return ID;
}

public Note getNote(long ID) {
    //select * from databaseTable where id=1
    SQLiteDatabase db = this.getWritableDatabase();
    String[] query = new String[] {"KEY_ID", "KEY_TITLE", "KEY_DETAIL", "KEY_DATE", "KEY_TIME"};

    //cursor is pointer that points to specific row in database column
    Cursor cursor = db.query("DATABASE_TABLE", query,KEY_ID + "=?", new String[]{String.valueOf(ID)}, null, null, null);

    if (cursor != null) {
        cursor.moveToFirst();
    }
    Note note = new Note(Long.parseLong(cursor.getString(0)),
            cursor.getString(1),
            cursor.getString(2),
            cursor.getString(3),
            cursor.getString(4));

    return note;
}

public List<Note> getNotes() {
    List<Note> allNotes = new ArrayList<Note>();

    String query = "SELECT * FROM DATABASE_TABLE ORDER BY KEY_ID DESC";
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(query, null);


    if (cursor.moveToFirst()) {
        do {
            Note note = new Note();
            //note.setID(Long.parseLong(cursor.getString(0)));
            note.setTitle(cursor.getString(1));
            note.setDetail(cursor.getString(2));
            note.setDate(cursor.getString(3));
            note.setTime(cursor.getString(4));

            allNotes.add(note);
        }
        while (cursor.moveToNext());
    }

    return allNotes;
}
}

AddNote class

package com.example.multi_note;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.util.Log;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.widget.EditText;
import android.widget.Toast;

import androidx.appcompat.widget.Toolbar;

import java.util.Calendar;

public class AddNote extends AppCompatActivity {
Toolbar toolbar;
EditText title;
EditText details;
Calendar calendar;
String dateToday;
String timeNow;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    //links to this activity when plus button is clicked
    setContentView(R.layout.activity_add_note);

    //brings in toolbar
    toolbar = (Toolbar)findViewById(R.id.toolbar);
    setSupportActionBar(toolbar);
    getSupportActionBar().setTitle("New Note");
    toolbar.setTitleTextColor(getResources().getColor(R.color.white));

    //setting up back button
    getSupportActionBar().setDisplayHomeAsUpEnabled(true);

    title = findViewById(R.id.title);
    details = findViewById(R.id.details);

    //to make title actively change with edit
    title.addTextChangedListener(new TextWatcher() {
        @Override
        public void beforeTextChanged(CharSequence s, int start, int count, int after) {

        }

        @Override
        public void onTextChanged(CharSequence s, int start, int before, int count) {
            if(s.length() != 0) {
                getSupportActionBar().setTitle(s);
            }
            else {
                getSupportActionBar().setTitle("New Note");
            }
        }

        @Override
        public void afterTextChanged(Editable s) {

        }
    });

    //get current date and time
    calendar = Calendar.getInstance();
    dateToday = calendar.get(Calendar.YEAR) + "/" + pad(calendar.get(Calendar.MONTH)) + "/" +
            calendar.get(Calendar.DAY_OF_MONTH);
    timeNow = pad(calendar.get(Calendar.HOUR)) + ":" + pad(calendar.get(Calendar.MINUTE));
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
    MenuInflater inflater = getMenuInflater();
    inflater.inflate(R.menu.save_del_menu, menu);
    return true;
}

@Override
public boolean onOptionsItemSelected(@NonNull MenuItem item) {
    //add delete function when note is added!!
    if(item.getItemId() == R.id.delete) {
        onBackPressed();
        Toast.makeText(this,"Deleted",Toast.LENGTH_SHORT).show();
    }
    if(item.getItemId() == R.id.save) {
        if (title.getText().length() != 0) {
            Note note = new Note(title.getText().toString(), details.getText().toString(),
                    dateToday, timeNow);

            NoteDatabase db = new NoteDatabase(this);
            long ID = db.addNote(note);
            db.addNote(note);
            Note check = db.getNote(ID);
            Log.d("Inserted", "Note: " + ID + " -> Title:" + check.getTitle() + " Date: " + check.getDate());
            goToMain();
            Toast.makeText(this, "Saved", Toast.LENGTH_SHORT).show();
        }
    }
    else {
        title.setError("Title cannot be BLANK");
    }

    return super.onOptionsItemSelected(item);
}

private String pad(int i) {
    if(i < 10) {
        return "0" + i;
    }
    return String.valueOf(i);
}

private void goToMain() {
    Intent intent = new Intent(this, MainActivity.class);
    startActivity(intent);
}
}

error I keep getting

D/inserted: ID -> 1
D/inserted: ID -> 2
E/SQLiteLog: (1) no such column: id
D/AndroidRuntime: Shutting down VM


--------- beginning of crash
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.multi_note, PID: 12114
android.database.sqlite.SQLiteException: no such column: id (code 1): , while compiling: SELECT 
KEY_ID, KEY_TITLE, KEY_DETAIL, KEY_DATE, KEY_TIME FROM DATABASE_TABLE WHERE id=?
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:890)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:501)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1392)
    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1239)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1110)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1278)
    at com.example.multi_note.NoteDatabase.getNote(NoteDatabase.java:78)
    at com.example.multi_note.AddNote.onOptionsItemSelected(AddNote.java:100)
    at android.app.Activity.onMenuItemSelected(Activity.java:3450)
    at androidx.fragment.app.FragmentActivity.onMenuItemSelected(FragmentActivity.java:436)
    at androidx.appcompat.app.AppCompatActivity.onMenuItemSelected(AppCompatActivity.java:196)
    at androidx.appcompat.view.WindowCallbackWrapper.onMenuItemSelected(WindowCallbackWrapper.java:109)
    at androidx.appcompat.view.WindowCallbackWrapper.onMenuItemSelected(WindowCallbackWrapper.java:109)
    at androidx.appcompat.app.ToolbarActionBar$2.onMenuItemClick(ToolbarActionBar.java:64)
    at androidx.appcompat.widget.Toolbar$1.onMenuItemClick(Toolbar.java:204)
    at androidx.appcompat.widget.ActionMenuView$MenuBuilderCallback.onMenuItemSelected(ActionMenuView.java:781)
    at androidx.appcompat.view.menu.MenuBuilder.dispatchMenuItemSelected(MenuBuilder.java:840)
    at androidx.appcompat.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:158)
    at androidx.appcompat.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:991)
    at androidx.appcompat.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:981)
    at androidx.appcompat.widget.ActionMenuView.invokeItem(ActionMenuView.java:625)
    at androidx.appcompat.view.menu.ActionMenuItemView.onClick(ActionMenuItemView.java:151)
    at android.view.View.performClick(View.java:6294)
    at android.view.View$PerformClick.run(View.java:24770)
    at android.os.Handler.handleCallback(Handler.java:790)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:164)
    at android.app.ActivityThread.main(ActivityThread.java:6494)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)

Solution

In the definition of the variable query instead of using the variables that you have defined for the column names you use them as string literals.
So instead of naming the columns id, title, ... you named them KEY_ID, KEY_TITLE, ....
Change the variable query to this:

String query = "CREATE TABLE " + DATABASE_TABLE  +
        "(" + KEY_ID + " INTEGER PRIMARY KEY, " +
        KEY_TITLE + " TEXT, " +
        KEY_DETAIL + " TEXT, " +
        KEY_DATE + " TEXT, " +
        KEY_TIME + " TEXT)";

This way you concatenate the variables which store the column names to the SQL statement.
You may have to uninstall the app from the device so the database is deleted and rerun to recreate the database and the table with the correct column names.
Also you will have to do changes like this in every part of your code where you use column names and the table's name.
So replace all occurrences of "KEY_ID" with KEY_ID, "KEY_TITLE" with KEY_TITLE, ... and change statements like:

String query = "SELECT * FROM DATABASE_TABLE ORDER BY KEY_ID DESC";

to:

String query = "SELECT * FROM " + DATABASE_TABLE + " ORDER BY " + KEY_ID + " DESC";