Android使用Sqlite出现缺少"_id"列问题解决方法

妖狐艹你老母 2022-01-30 23:19 281阅读 0赞

在Android使用Sqlite时，《Head First Android 开发》里就提到，一定要有"\_id"列，一般平常建表都会把 \_id 列建上，但是就不一定会读取出来。多次都没出错，所以没觉得这有啥重要的。直到有一次对多表进行联合查询时，麻烦才出现。

有2个表：

public static final String C_MONTHDETAIL = "create table monthdetail(_id integer primary key autoincrement," +
                "sId integer," +
                "mId integer," +
                "mPrice float," +
                "mDetail text)";
    
    public static final String C_SHARENAME = "create table sharename(_id integer primary key autoincrement," +
                "sName text," +
                "sCode text)";

现在要读出mPrice和sName，这两个表sId和sharename的\_id是一致的。

String rawsql="SELECT monthdetail.mPrice,monthdetail.mDetail,sharename.sName FROM monthdetail INNER JOIN sharename ON monthdetail.sId=sharename._id WHERE monthdetail.mId=?";
                cursor_dList=sqLiteDatabase.rawQuery(rawsql,new String[]{String.valueOf(dataID)});
                SimpleCursorAdapter simpleCursorAdapter=new SimpleCursorAdapter(MainActivity.this,R.layout.main_detaillist,cursor_dList,
                        new String[]{"sharename.sName","monthdetail.mPrice"},new int[]{R.id.main_sharename,R.id.main_mPrice},0);
                detailList.setAdapter(simpleCursorAdapter);

其中dataID是当需查询的mId值。

结果Activity报错：

java.lang.IllegalArgumentException: column '\_id' does not exist

百度了一下，发现正是因为使用INNER JOIN联合查询时，表内不存在 \_id 列所导致的。有些大神使用AS重新命名一列可以解决问题。于是修改了一下代码：

String rawsql="SELECT monthdetail.mPrice,monthdetail.mDetail,sharename.sName,monthdetail._id as _id  FROM monthdetail INNER JOIN sharename ON monthdetail.sId=sharename._id WHERE monthdetail.mId=?";
                cursor_dList=sqLiteDatabase.rawQuery(rawsql,new String[]{String.valueOf(dataID)});
                SimpleCursorAdapter simpleCursorAdapter=new SimpleCursorAdapter(MainActivity.this,R.layout.main_detaillist,cursor_dList,
                        new String[]{"sharename.sName","monthdetail.mPrice"},new int[]{R.id.main_sharename,R.id.main_mPrice},0);
                detailList.setAdapter(simpleCursorAdapter);

把monthdetail里的\_id列读取出来，使用 as \_id，变为\_id 列，因为其在本表也是无重复的，所以跟普通的\_id 列属性一致。重新编译，ListView的detailList就可以正常显示了。

但是问题还没彻底解决，查看Log，会出现错误，但虽然显示错误，数据显示正常的：

E/SQLiteCursor: requesting column name with table name -- monthdetail.mPrice  
java.lang.Exception

E/SQLiteCursor: requesting column name with table name -- sharename.sName  
java.lang.Exception

再把语句改下，都用AS重新命名一下列名，就不会出错了：

String rawsql="SELECT monthdetail.mPrice AS mPrice,monthdetail.mDetail AS mDetail,sharename.sName AS sName,monthdetail._id AS _id  FROM monthdetail INNER JOIN sharename ON monthdetail.sId=sharename._id WHERE monthdetail.mId=?";
                cursor_dList=sqLiteDatabase.rawQuery(rawsql,new String[]{String.valueOf(dataID)});