java - 如何从 SQLite 数据库中读取数据？

Question

我正在尝试使用列名作为键来读取一行，但我无法获取它。

例子我输入了一个字符串“name”

列表只在ListView中显示“1”

这是我的插入代码

public void insertStudent(String name, int faculty){
        ContentValues cv = new ContentValues();
        cv.put("name", name);
        cv.put("faculty", faculty);
        mSqliteDatabase.insert("students", null, cv);
    }

下面是我查询数据的方式:

public List<String> selectAllEngg(){
        List<String> allStudents = new ArrayList<>();
        Cursor cursor = mSqliteDatabase.query("students", new String[]{"faculty"}, null, null, null, null, null);
        if(cursor != null && cursor.moveToFirst()){
            do {

                allStudents.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        return allStudents;
    }

---

package test;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

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

public class MyDBAdapter {
    public static final String DATABASE_NAME = "data";

    private Context mContext;
    private MyDBHelper mDbHelper;
    private SQLiteDatabase mSqliteDatabase;
    private int DATABASE_VERSION = 1;

    public MyDBAdapter(Context context){
        this.mContext = context;
        mDbHelper = new MyDBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void open(){
        mSqliteDatabase = mDbHelper.getWritableDatabase();
    }

    public class MyDBHelper extends SQLiteOpenHelper{
        public MyDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version){
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db){
            String query = "CREATE TABLE students(id integer primary key autoincrement, name text, faculty integer);";
            db.execSQL(query);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
            String query = "DROP TABLE IF EXISTS students;";
            db.execSQL(query);
            onCreate(db);
        }
    }

    public void insertStudent(String name, int faculty){
        ContentValues cv = new ContentValues();
        cv.put("name", name);
        cv.put("faculty", faculty);
        mSqliteDatabase.insert("students", null, cv);
    }

//    public List<String> selectAllStudents(){
//        List<String> allStudents = new ArrayList<>();
//        Cursor cursor = mSqliteDatabase.query("students", null, null, null, null, null, null);
//        if(cursor != null && cursor.moveToFirst()){
//            do {
//                allStudents.add(cursor.getString(1));
//            } while (cursor.moveToNext());
//        }
//        return allStudents;
//    }

    public List<String> selectAllEngg(){
        List<String> allStudents = new ArrayList<>();

        Cursor cursor = mSqliteDatabase.query("students", new String[]{"faculty"}, null, null, null, null, null);
        if(cursor != null && cursor.moveToFirst()){
            do {
                if(cursor.getString(cursor.getColumnIndex("faculty")).equals("1")){
                    allStudents.add(cursor.getString(cursor.getColumnIndex("name")));
                }
//                allStudents.add(cursor.getString(0));

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

    public void deleteAllEngineers(){
        mSqliteDatabase.delete("students", null, null);
    }
}

我添加了我的 DB java 文件，请查看。

Answer 1

在 cursor.getString(0) 中使用零返回 1，因为您的查询仅包含一列“faculty”，根据您的表规范，该列为整数。但同意 Zoe 的观点，这是一种不好的做法，会导致难以维护和调试代码。

为了查询姓名和教职员工，您需要将两者都包含在查询的列数组字符串中，我推荐这种方式(更具可读性的代码):

String[] columns = {"name","faculty"};
Cursor cursor = mSqliteDatabase.query("students", columns, null, null, null, null, null);

或者你的方式:

Cursor cursor = mSqliteDatabase.query("students", new String[]  {"name","faculty"}, null, null, null, null, null);

通过这种方式，您需要将 arraylist 更改为映射(接受键和值的集合)或将表列实现为类并使用该类类型的 arraylist，或者创建字符串类型的列表数组如:

ArrayList<String[]> allStudents  = new ArrayList();
allStudents.add(new String[] {"put here faculty integer","put here student name"});

希望对您有所帮助。