没有这样的列，SQLite的例外 [英] No such column sqlite exception

问题描述

我试图获取从用户简档的一些数据，但它没有找到该柱。我已经检查空间中的每个查询之间，似乎无可厚非。到现在我看不出这个问题，任何想法？由于前 这是我的code和错误信息

表名称

 私有静态最后弦乐TABLE_PROFILE =用户;
私有静态最后弦乐TABLE_STATUS =状态;
 

用户列名

  //用户列名
私有静态最后弦乐KEY_USER_ID =USER_ID;
私有静态最后弦乐KEY_USER_IMG =图片;
私有静态最后弦乐KEY_USER_EMAIL =电子邮件;
私有静态最后弦乐KEY_USER_NAME =名;
私有静态最后弦乐KEY_USER_PASSWORD =密码;
私有静态最后弦乐KEY_USER_PHONE =手机;
 

状态列名

  //状态列名
私有静态最后弦乐KEY_STATUS_ID =status_id;
私有静态最后弦乐KEY_STATUS =状态;
 

表CREATE语句

  //用户表create语句
私有静态最后弦乐CREATE_TABLE_PROFILE =CREATE TABLE+ TABLE_PROFILE +
        （+ KEY_USER_ID +INTEGER PRIMARY KEY，+ KEY_USER_IMG +BLOB，+
        KEY_USER_EMAIL +VARCHAR，+ KEY_USER_NAME +VARCHAR，+ KEY_USER_PASSWORD +VARCHAR
        + KEY_USER_PHONE +VARCHAR，+ KEY_CREATED_AT +DATETIME，+ KEY_STATUS_ID +INTEGER
        +主键+（+ KEY_STATUS_ID +）+参考+ TABLE_STATUS +（+ KEY_STATUS_ID +）+）;
 

getProfile方法

 公开资料getProfile（字符串username）{
    SQLiteDatabase DB = this.getReadableDatabase（）;
    字符串selectQuery =SELECT * FROM+ TABLE_PROFILE +WHERE+ KEY_USER_EMAIL +=+用户名;
    Log.e（LOG，selectQuery）;
    光标C = db.rawQuery（selectQuery，NULL）;

    如果（C！= NULL）
        c.moveToFirst（）;

    简介PF =新的配置文件（）;
    pf.setID（c.getInt（c.getColumnIndex（KEY_USER_ID）））;
    pf.setImg（c.getBlob（c.getColumnIndex（KEY_USER_IMG）））;
    pf.setEmail（c.getString（c.getColumnIndex（KEY_USER_EMAIL）））;
    pf.setName（c.getString（c.getColumnIndex（KEY_USER_NAME）））;
    pf.setPassword（c.getString（c.getColumnIndex（KEY_USER_PASSWORD）））;
    pf.setPhone（c.getString（c.getColumnIndex（KEY_USER_PHONE）））;
    pf.setCreatedAt（c.getString（c.getColumnIndex（KEY_CREATED_AT）））;

    返回PF;
}
 

logcat的消息

  2月12号至18号：50：32.616：E / DatabaseHelper（7093）：SELECT * FROM用户WHERE电子邮件=的test1
2月12号至18日：50：32.616：E / SQLiteLog（7093）：（1）没有这样的列：TEST1
2月12号至18日：50：32.636：E / AndroidRuntime（7093）：致命异常：主要
2月12号至18日：50：32.636：E / AndroidRuntime（7093）：android.database.sqlite.SQLiteException：没有这样的列：TEST1（code 1）：在编制：SELECT * FROM用户WHERE电子邮件=的test1
 

解决方案

在SQL中，字符串必须加引号。

然而，为了避免这样的格式问题（和SQL注入攻击），使用参数来代替：

光标C = db.rawQuery（SELECT * FROM+ TABLE_PROFILE +                        WHE​​RE+ KEY_USER_EMAIL +=？，                        新的String [] {用户名}）;

I am trying to get some data from user profile, but it doesn't find the column. I already check space between every query and it seems nothing wrong. Till now i can't see the problem, any idea? Thanks before Here is my code and error message

Table Name

private static final String TABLE_PROFILE = "users"; 
private static final String TABLE_STATUS = "status";

Users Column Names

//Users column names
private static final String KEY_USER_ID = "user_id";
private static final String KEY_USER_IMG = "picture";
private static final String KEY_USER_EMAIL = "email";
private static final String KEY_USER_NAME = "name";
private static final String KEY_USER_PASSWORD = "password";
private static final String KEY_USER_PHONE = "phone";

Status Column Names

//Status column names
private static final String KEY_STATUS_ID = "status_id";
private static final String KEY_STATUS = "status";

Table Create Statement

 //Users table create statement
private static final String CREATE_TABLE_PROFILE = "CREATE TABLE " + TABLE_PROFILE + 
        "(" + KEY_USER_ID + " INTEGER PRIMARY KEY," + KEY_USER_IMG + " BLOB," + 
        KEY_USER_EMAIL + " VARCHAR," + KEY_USER_NAME + " VARCHAR," + KEY_USER_PASSWORD + " VARCHAR," 
        + KEY_USER_PHONE + " VARCHAR," + KEY_CREATED_AT + " DATETIME," + KEY_STATUS_ID + " INTEGER," 
        + " FOREIGN KEY " + "(" + KEY_STATUS_ID + ")" + " REFERENCES " + TABLE_STATUS + "(" + KEY_STATUS_ID + ")" + ")";

getProfile method

public Profile getProfile(String username){
    SQLiteDatabase db = this.getReadableDatabase();
    String selectQuery = "SELECT * FROM " + TABLE_PROFILE + " WHERE " + KEY_USER_EMAIL + " = " + username;
    Log.e(LOG, selectQuery);
    Cursor c = db.rawQuery(selectQuery, null);

    if(c!=null)
        c.moveToFirst();

    Profile pf = new Profile();
    pf.setID(c.getInt(c.getColumnIndex(KEY_USER_ID)));
    pf.setImg(c.getBlob(c.getColumnIndex(KEY_USER_IMG)));
    pf.setEmail(c.getString(c.getColumnIndex(KEY_USER_EMAIL)));
    pf.setName(c.getString(c.getColumnIndex(KEY_USER_NAME)));
    pf.setPassword(c.getString(c.getColumnIndex(KEY_USER_PASSWORD)));
    pf.setPhone(c.getString(c.getColumnIndex(KEY_USER_PHONE)));
    pf.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

    return pf;
}

logcat message

12-18 02:50:32.616: E/DatabaseHelper(7093): SELECT * FROM users WHERE email = test1
12-18 02:50:32.616: E/SQLiteLog(7093): (1) no such column: test1
12-18 02:50:32.636: E/AndroidRuntime(7093): FATAL EXCEPTION: main
12-18 02:50:32.636: E/AndroidRuntime(7093): android.database.sqlite.SQLiteException: no such column: test1 (code 1): , while compiling: SELECT * FROM users WHERE email = test1

解决方案

In SQL, string literals must be quoted.

However, to avoid formatting problems like this (and SQL injection attacks), use parameters instead:

Cursor c = db.rawQuery("SELECT * FROM " + TABLE_PROFILE +
                       " WHERE " + KEY_USER_EMAIL + " = ?",
                       new String[]{ username });

这篇关于没有这样的列，SQLite的例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！