“No such column: id” 当在Android Studio中使用SQLite时。

huangapple
go评论72阅读模式
英文:

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

问题

错误出现在NoteDatabase类中。它是在AddNote类中尝试使用db.getNote(ID)方法将新的笔记添加到数据库时产生的。

错误消息显示称没有找到id列，但实际上我是有这个列的。

错误示例：

E/SQLiteLog: (1) no such column: id

请帮助解决这个问题。

英文:

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)

答案1

得分: 0

在变量query的定义中，与其使用你为列名定义的变量，你将它们用作字符串文字。
因此，不要将列命名为id，title，...，而是将它们命名为KEY_ID，KEY_TITLE，...。
将变量query更改为以下内容：

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

这样，您将变量连接到存储列名的变量，以生成SQL语句。
您可能需要从设备中卸载应用程序，以便删除数据库，然后重新运行应用程序，以重新创建具有正确列名的数据库和表。
此外，您还必须在代码的每个部分进行类似的更改，其中使用了列名和表名。
因此，将所有出现的"KEY_ID"替换为KEY_ID，将所有出现的"KEY_TITLE"替换为KEY_TITLE，...，并将类似这样的语句更改为：

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

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.<br/>
So instead of naming the columns id, title, ... you named them KEY_ID, KEY_TITLE, ....<br/>
Change the variable query to this:

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

This way you concatenate the variables which store the column names to the SQL statement.<br/>
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. <br/>
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.<br/>
So replace all occurrences of &quot;KEY_ID&quot; with KEY_ID, &quot;KEY_TITLE&quot; with KEY_TITLE, ... and change statements like:

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

to:

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

huangapple
  • 本文由 发表于 2020年4月4日 06:04:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/61021104.html
  • android-sqlite
  • android-studio
  • java
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定