在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)});