时间戳

首页 > 解决方案 > 在 Android Studio 中使用 SQLite 时出现“没有这样的列：id”

java - 在 Android Studio 中使用 SQLite 时出现“没有这样的列：id”

问题描述

NoteDatabase 类中出现错误。它起源于我尝试使用db.getNote(ID)AddNote 类中的方法向数据库添加新注释时。该错误表明我没有 id 列，而我有。任何帮助，将不胜感激。错误消息发布在下面。

主要活动

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);
}


}

笔记类

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);
            }
        });

    }

}
}

适配器类

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);
            }
        });

    }

}
}

存储笔记的数据库类

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 类

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);
}
}

我不断收到的错误

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)

标签： javasqliteandroid-studioandroid-sqlite

解决方案

在变量的定义中，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"with KEY_ID、"KEY_TITLE"with KEY_TITLE、 ... 并更改语句，例如：

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

到：

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

推荐阅读