英文:
Can't access a column?
问题
你的代码中存在一些问题，导致数据库查询时出现错误。以下是你应该注意的问题：
- Song 类的构造函数中，应该使用参数名作为字段初始化，而不是参数的名称。修正如下：
public song (int id, String title, String singers, int year, int stars) {
this.id = id;
this.title = title;
this.singers = singers;
this.year = year;
this.stars = stars;
}
- DBHelper 中创建表格的 SQL 查询语句中，COLUMN_STARS 字段的数据类型与列名之间应该有一个空格。修正如下：
private static final String COLUMN_STARS = "stars";
// ...
String createTableSql = "CREATE TABLE " + TABLE_SONG + " ("
+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_TITLE + " TEXT, "
+ COLUMN_SINGERS + " TEXT, "
+ COLUMN_YEAR + " INTEGER, "
+ COLUMN_STARS + " INTEGER)";
- 在 DBHelper 的 getSongs 方法中，你应该使用
cursor.getString()获取字符串列的值，而不是String.valueOf(cursor.getColumnIndex())。修正如下：
if (cursor.moveToFirst()) {
do {
int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
String singers = cursor.getString(cursor.getColumnIndex(COLUMN_SINGERS));
int year = cursor.getInt(cursor.getColumnIndex(COLUMN_YEAR));
int stars = cursor.getInt(cursor.getColumnIndex(COLUMN_STARS));
song obj = new song(id, title, singers, year, stars);
tasks.add(obj);
} while (cursor.moveToNext());
}
通过修正这些问题，你的代码应该能够正确地查询并显示歌曲列表了。希望这对你有所帮助！
英文:
My code is meant to retrieve a song from a list with 5 columns (id, title, singers, year (of release) and stars). But I for some reason can't retrieve that last one.
So I made a Song Class:
package com.myapplicationdev.android.songdbmanager;
import androidx.annotation.NonNull;
public class song {
private int id;
private String title;
private String singers;
private int year;
private int stars;
public song (int id,
String title,
String singers,
int year,
int stars
){
this.id=id;
this.title=title;
this.singers=singers;
this.year=year;
this.stars=stars;
}
public String getTitle() {
return title;
}
public int getId(){
return id;
}
public int getYear() {
return year;
}
public int getStars() {
return stars;
}
public String getSingers() {
return singers;
}
@NonNull
@Override
public String toString() {
return id + "\n" + title+"\n"+year + "\n" + stars + "\n" + singers;
}
}
This class is meant to intreact with MainActivity:
package com.myapplicationdev.android.songdbmanager;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.TextView;
import android.widget.ToggleButton;
import java.util.ArrayList;
public class MainActivity extends AppCompatActivity {
Button btnInsert, btnGetSongs;
TextView tvResults;
EditText YearInput;
EditText SongInput;
EditText SingerInput;
RadioGroup RadioGroupRating;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btnInsert = findViewById(R.id.btnInsert);
btnGetSongs = findViewById(R.id.btnGetTasks);
//List View->
YearInput=findViewById(R.id.insertYear);
SongInput=findViewById(R.id.insertTitle);
SingerInput=findViewById(R.id.insertSingers);
RadioGroupRating=findViewById(R.id.radio_group);
btnInsert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
DBHelper db = new DBHelper(MainActivity.this);
String title=SongInput.getText().toString();
String singers=SingerInput.getText().toString();
int year=Integer.parseInt(YearInput.getText().toString());
int selectedRatingId=RadioGroupRating.getCheckedRadioButtonId();
RadioButton selectedRatingButton=findViewById(selectedRatingId);
int rating=Integer.parseInt(selectedRatingButton.getText().toString());
db.insertSong(title,singers,year,rating);
SongInput.setText("");
SingerInput.setText("");
YearInput.setText("");
RadioGroupRating.clearCheck();
}
});
btnGetSongs.setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v) {
// Create the DBHelper object, passing in the
// activity's Context
DBHelper db = new DBHelper(MainActivity.this);
// Insert a task
ArrayList<song> data = db.getSongs();
db.close();
Intent intent = new Intent(MainActivity.this, EditActivity.class);
//Label the actual editactivity EditActivity2
startActivity(intent);
}
});
}
}
which is supplemented by DBHelper:
package com.myapplicationdev.android.songdbmanager;
import android.annotation.SuppressLint;
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 androidx.annotation.Nullable;
import java.util.ArrayList;
public class DBHelper extends SQLiteOpenHelper { // Start version with 1
// increment by 1 whenever db schema changes.
private static final int DATABASE_VER = 1;
private static final String TABLE_SONG = "song";
private static final String COLUMN_ID = "id";
private static final String COLUMN_TITLE = "title";
private static final String COLUMN_SINGERS = "singers";
private static final String COLUMN_YEAR="year";
private static final String COLUMN_STARS="stars";
// Filename of the database
private static final String DATABASE_NAME = "songs.db";
public DBHelper(@Nullable Context context) {
super(context, DATABASE_NAME, null, DATABASE_VER);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createTableSql = "CREATE TABLE " + TABLE_SONG + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_TITLE + " TEXT,"
+ COLUMN_SINGERS+ " TEXT ,"
+ COLUMN_YEAR + " INTEGER,"
+ COLUMN_STARS+"INTEGER)";
db.execSQL(createTableSql);
Log.i("info" ,"created tables");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_SONG);
// Create table(s) again
onCreate(db);
}
public void insertSong(String title, String singers, int year, int stars){
// Get an instance of the database for writing
SQLiteDatabase db = this.getWritableDatabase();
// We use ContentValues object to store the values for
// the db operation
ContentValues values = new ContentValues();
// Store the column name as key and the description as value
values.put(COLUMN_TITLE, title);
values.put(COLUMN_SINGERS, singers);
values.put(COLUMN_YEAR, year);
values.put(COLUMN_STARS, stars);
// Store the column name as key and the date as value
// Insert the row into the TABLE_TASK
db.insert(TABLE_SONG, null, values);
// Close the database connection
db.close();
}
public ArrayList<song> getSongs() {
ArrayList<song> tasks = new ArrayList<song>();
SQLiteDatabase db = this.getReadableDatabase();
String[] columns = {COLUMN_ID, COLUMN_TITLE, COLUMN_SINGERS,COLUMN_YEAR,COLUMN_STARS};
Cursor cursor = db.query(TABLE_SONG, columns, null, null, null, null, null , null);
if (cursor.moveToFirst()) {
do {
int id = cursor.getColumnIndex(COLUMN_ID);
String title= String.valueOf(cursor.getColumnIndex(COLUMN_TITLE));
String singers= String.valueOf(cursor.getColumnIndex(COLUMN_SINGERS));
@SuppressLint("Range") int year = cursor.getInt(cursor.getColumnIndex(COLUMN_YEAR));
@SuppressLint("Range") int stars = cursor.getInt(cursor.getColumnIndex(COLUMN_STARS));
song obj = new song(id, title, singers,year,stars);
tasks.add(obj);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return tasks;
}
}
And for good measure, here the xml of my app:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input1" />
<EditText
android:id="@+id/insertTitle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/InputPrompt1" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input2" />
<EditText
android:id="@+id/insertSingers"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="@string/InputPrompt2" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input3" />
<EditText
android:id="@+id/insertYear"
android:layout_width="match_parent"
android:layout_height="50dp"
android:text="@string/InputPrompt3"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input4" />
<RadioGroup
android:id="@+id/radio_group"
android:layout_width="match_parent"
android:layout_height="50dp"
android:orientation="horizontal">
<RadioButton
android:id="@+id/radio_1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input4.1" />
<RadioButton
android:id="@+id/radio_2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input4.2" />
<RadioButton
android:id="@+id/radio_3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input4.3" />
<RadioButton
android:id="@+id/radio_4"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input4.4" />
<RadioButton
android:id="@+id/radio_5"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/Input4.5" />
</RadioGroup>
<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/btnInsert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/FinalButton" />
<Button
android:id="@+id/btnGetTasks"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/ViewListings" />
</LinearLayout>
</LinearLayout>
My intent was pressing btnRetrieve would cause a list to display in a separate activity.
The Activity's xml:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".EditActivity">
<ListView
android:id="@+id/list_view"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1" />
</LinearLayout>
and its java coding
package com.myapplicationdev.android.songdbmanager;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import java.util.ArrayList;
public class EditActivity extends AppCompatActivity {
ListView lv;
ArrayList<song> songsList;
ArrayAdapter<song> adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_edit);
lv=findViewById(R.id.list_view);
DBHelper dbHelper = new DBHelper(this);
songsList = dbHelper.getSongs();
ArrayList<String> songTitles = new ArrayList<>();
for (int i = 0; i < songsList.size(); i++) {
song song = songsList.get(i);
songTitles.add(song.getTitle());
}
adapter = new ArrayAdapter<>(EditActivity.this,android.R.layout.simple_list_item_1,songsList);
lv.setAdapter(adapter);
}
}
Unfortunately it caused this to appear in my logcat:
2023-07-17 09:00:34.942 2045-2045 SQLiteLog com...tiondev.android.songdbmanager E (1) no such column: stars in "SELECT id, title, singers, year, stars FROM song"
2023-07-17 09:00:34.945 2045-2045 AndroidRuntime com...tiondev.android.songdbmanager D Shutting down VM
2023-07-17 09:00:34.988 2045-2045 AndroidRuntime com...tiondev.android.songdbmanager E FATAL EXCEPTION: main
Process: com.myapplicationdev.android.songdbmanager, PID: 2045
android.database.sqlite.SQLiteException: no such column: stars (code 1 SQLITE_ERROR): , while compiling: SELECT id, title, singers, year, stars FROM song
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
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:1545)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1392)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1263)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1469)
at com.myapplicationdev.android.songdbmanager.DBHelper.getSongs(DBHelper.java:78)
at com.myapplicationdev.android.songdbmanager.MainActivity$2.onClick(MainActivity.java:73)
at android.view.View.performClick(View.java:7448)
at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:1131)
at android.view.View.performClickInternal(View.java:7425)
at android.view.View.access$3600(View.java:810)
at android.view.View$PerformClick.run(View.java:28305)
at android.os.Handler.handleCallback(Handler.java:938)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:223)
at android.app.ActivityThread.main(ActivityThread.java:7656)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)
答案1
得分: 0
有两个可能的问题我看到了：
- 当你创建表格时，你写成了：
COLUMN_STARS+"INTEGER)";，应该是COLUMN_STARS+" INTEGER)";，我猜是吗？在INTEGER前面缺少了空格。 - 你确定表格已经被创建，并且在日志中看到了你的“created tables”语句吗？只是为了确保表格确实是用最新版本的代码创建的。
希望对你有所帮助。
英文:
There are two possible problems that I see:
- When you create the table you do:
COLUMN_STARS+"INTEGER)";, it must beCOLUMN_STARS+" INTEGER)";, I suppose? The space is missing beforeINTEGER - Are you sure that the table was created and you see your
created tablesstatement in logs? Just to make sure that the table for really created with the last version of the code.
I hope it helps.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论