android - SQLite SELECT 给出异常如何解决？

Question

我正在开发一个 android 应用程序(使用 Eclipse 和 5554 模拟器)。当我执行以下代码时，似乎 CREATE TABLE 和 INSERT 工作正常，但 SELECT 有一些问题。

您认为问题出在 CREATE TABLE 和 INSERT 上吗？或者 SELECT 语法不对？

public void onCreateA(SQLiteDatabase db) {

        System.out.println("On create works");

        try {
            db.execSQL("DROP TABLE IF EXISTS actuator;");
            db.execSQL("CREATE TABLE IF NOT EXISTS actuator ("+
                    "ACTUATOR_ID INT(11) NOT NULL,"+
                    "ACTUATOR_HW_ID int(11) DEFAULT NULL,"+
                    "DEVICE_NAME TEXT(45) DEFAULT NULL,"+
                    "DEVICE_TYPE TEXT(45) DEFAULT NULL,"+
                    "DEVICE_SUB_TYPE TEXT(45) DEFAULT NULL,"+
                    "LOCATION_ID INT(11) DEFAULT NULL,"+
                    "STATUES_VALUE REAL DEFAULT NULL,"+
                    "MAX_STATUES_VALUE REAL DEFAULT NULL,"+
                    "MIN_STATUES_VALUE REAL DEFAULT NULL,"+
                    "PRIMARY KEY (`ACTUATOR_ID`))");

            System.out.println("table created now values will be added");

            System.out.print("DONE!");


        } catch (SQLException e) {

            System.out.println("table is not created");
        }

        try {
            db.execSQL("INSERT INTO actuator (ACTUATOR_ID, ACTUATOR_HW_ID, DEVICE_NAME, DEVICE_TYPE, DEVICE_SUB_TYPE, LOCATION_ID, STATUES_VALUE, MAX_STATUES_VALUE, MIN_STATUES_VALUE) " +
                    "VALUES (1, 2, 'Heat Sensor FX615','Heat Sensor', 'Optic Heat Sensor', 4, 6, 7, 8) " );
            System.out.println("values are added");
        } catch (Exception e) {

            e.printStackTrace();
            System.out.println("values are not added");
        }

        try {
            db.execSQL("SELECT * FROM actuator");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("not selected");
        }


    }

但输出是:

04-11 08:49:24.513: I/System.out(1499): **On create works** 
04-11 08:49:24.571: I/System.out(1499): **table created now values will be added** 
04-11 08:49:24.627: I/System.out(1499): **DONE!values are added** 
04-11 08:49:24.513: I/System.out(1499): **On create works** 
04-11 08:49:24.631: W/System.err(1499): android.database.sqlite.SQLiteException: unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only. 
04-11 08:49:24.631: W/System.err(1499):     at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
04-11 08:49:24.631: W/System.err(1499):     at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:727) 
04-11 08:49:24.641: W/System.err(1499):     at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754) 
04-11 08:49:24.641: W/System.err(1499):     at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64) 
04-11 08:49:24.641: W/System.err(1499):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1665) 
04-11 08:49:24.641: W/System.err(1499):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
04-11 08:49:24.641: W/System.err(1499):     at com.example.databasetryout.DataBaseTryOut.onCreateA(DataBaseTryOut.java:196) 
04-11 08:49:24.641: W/System.err(1499):     at com.example.databasetryout.DataBaseTryOut$3.onClick(DataBaseTryOut.java:75) 
04-11 08:49:24.641: W/System.err(1499):     at android.view.View.performClick(View.java:4204) 
04-11 08:49:24.641: W/System.err(1499):     at android.view.View$PerformClick.run(View.java:17355) 
04-11 08:49:24.641: W/System.err(1499):     at android.os.Handler.handleCallback(Handler.java:725) 
04-11 08:49:24.641: W/System.err(1499):     at android.os.Handler.dispatchMessage(Handler.java:92) 
04-11 08:49:24.651: W/System.err(1499):     at android.os.Looper.loop(Looper.java:137) 04-11 08:49:24.651: W/System.err(1499):  at android.app.ActivityThread.main(ActivityThread.java:5041) 
04-11 08:49:24.651: W/System.err(1499):     at java.lang.reflect.Method.invokeNative(Native Method) 
04-11 08:49:24.651: W/System.err(1499):     at java.lang.reflect.Method.invoke(Method.java:511) 
04-11 08:49:24.651: W/System.err(1499):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793) 
04-11 08:49:24.651: W/System.err(1499):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560) 
04-11 08:49:24.651: W/System.err(1499):     at dalvik.system.NativeStart.main(Native Method) 04-11 08:49:24.651: I/System.out(1499): **not selected** 
04-11 08:49:24.651: I/System.out(1499): **finish**

Answer 1

db.execSQL("SELECT * FROM actuator");

您不能对返回数据的语句使用 execSQL 方法。完全来自文档

Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

你必须使用 rawQuery()或 query()方法。

因此对于您的情况，您可以使用以下代码 fragment 从数据库中获取数据:

String query = "SELECT * FROM actuator";
Cursor c = db.rawQuery(query, null);
if (c != null && c.moveToFirst()) {
   // do your stuff
}

实际上，您的代码什么都不做。如果要从数据库中检索数据，则需要通过上述方法将它们分配给 Cursor。