java - android sql-query 问题..."no such column"？

Question

我想使用 SQL 数据库来存储游戏记录。其实我对SQL一窍不通。我有一个“记录”类，它应该管理记录的输入和输出。另外，我有一个 SQLDatabaseHelper 类，它提供 SQL 数据库。

我的问题是以下行:

crsRecord = sqliteDatabase.rawQuery(QUERY_GET_RECORD + category, null);

我总是收到错误“没有这样的列:SYS103”“SYS103”是类别的名称。不知道为什么可以读取。你有什么想法吗？

创建 SQL 表:

CREATE TABLE records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category VARCHAR(30) NOT NULL,
            displaytime VARCHAR(12) NOT NULL,
            recordtime VARCHAR(10) NOT NULL);

我认为写作有效，光阅读是行不通的。

public class Records {
            private SQLiteOpenHelper sqliteOpenHelper;
            private SQLiteDatabase sqliteDatabase;

            private static final String INSERT_NEW_RECORD = "insert into records(category, displayrecord, timerecord) values(";
            private static final String QUERY_GET_RECORD = "SELECT * FROM  records WHERE category = ";

            public Records(Context context){
                sqliteOpenHelper = new SQLDatabaseHelper(context);
                sqliteDatabase = sqliteOpenHelper.getWritableDatabase();
            }

            public void addRecord(String category, String displaytime, String timerecord){
                ContentValues data = new ContentValues();

                data.put("category", category);
                data.put("displaytime", displaytime);
                data.put("recordtime", timerecord);

                sqliteDatabase.insert("records", null, data);
        //      sqliteDatabase.execSQL(INSERT_NEW_RECORD + category + ", " + strTime + ", " + dblTime + ");");
            }

            public String[] getRecord(String category){
                String[] record = new String[3];
                Cursor crsRecord;
                try{
                    crsRecord = sqliteDatabase.rawQuery(QUERY_GET_RECORD + category, null);
                }catch(SQLiteException e){
                    Log.d("database", e.getMessage());
                    String[] nullRecord = {category, "00:00.0", "0"};
                    return nullRecord;
                }

                int i=0;


                while(crsRecord.moveToNext()){
                    record[i] = crsRecord.getString(0);
                    i++;
                }

                return record;

            }
        }

    public class SQLDatabaseHelper extends SQLiteOpenHelper {
        private Context context;

        public SQLDatabaseHelper(Context context){
            super(
                context,
                context.getResources().getString(R.string.dbname),
                null,
                Integer.parseInt(context.getResources().getString(R.string.version)));
             this.context=context;
         }

        @Override
        public void onCreate(SQLiteDatabase db) {
            for(String sql : context.getResources().getStringArray(R.array.create)){
                db.execSQL(sql);
            }
            Log.d("Database", "creat succesfully");
         }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        }
    }

我从数据库中获取数据的方法，但由于某种原因columnIndex始终为-1:

public String[] getRecord(String category){
    String[] record = new String[3];
    Cursor crsRecord;

        crsRecord = sqliteDatabase.rawQuery(QUERY_GET_RECORD, new String[]{ category } );

    int i=0;
    crsRecord.moveToFirst();
    while(!crsRecord.isAfterLast()){

        // Instead of using an int literal to get the colum index,
        // use the getColumnIndex method
        int index = crsRecord.getColumnIndex(category);
        if (index == -1) {
            String[] nullRecord = {category, "00:00.0", "0"};
            return nullRecord;
        }
        else {
            record[i] = crsRecord.getString(index);
            i++;
        }

        crsRecord.moveToNext();
    }



    while(crsRecord.moveToNext()){
        record[i] = crsRecord.getString(0);
        i++;
    }

    return record;

}

Answer 1

您需要转义参数。

照原样，您的代码执行查询:

SELECT * FROM  records WHERE category = SYS103

这不是有效的 SQL。它应该看起来像这样:

SELECT * FROM  records WHERE category = 'SYS103'

并且您需要转义撇号。您最好依靠rawQuery来转义您的参数:

private static final String QUERY_GET_RECORD 
                            = "SELECT * FROM  records WHERE category = ?";

和

crsRecord = 
    sqliteDatabase.rawQuery(QUERY_GET_RECORD, new String[]{ category } );