java - sqlite 数据库未填充且查询不起作用

Question

我无法处理我的应用程序中的问题。有两个类用于处理数据库，但它们只是创建 db 并在其中创建表，但基数未填充，并且查询数据库时出现严重错误，表明数据库为空。请帮助我

第一个数据库类:

package ru.psdevteam.syndicttest.app;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SynDB extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "synonyms.db";
public static final String TABLE_NAME = "words";
private static final int DATABASE_VERSION = 1;
public static final String WordID = "_id";
public static final String Word = "word";


private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
        + TABLE_NAME + " (" + WordID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,"
        + Word + " text NOT NULL); ";

public static final String SQL_INSERT_WORDS = "INSERT INTO " + TABLE_NAME + " (_id,word)  " + "VALUES " + "(1,'а'),(2,'же'),(3,'да'),(4,'напротив'),(5,'но'),(6,'ан'),(7,'однако'),(8,'инак'),(9,'следовательно'),(10,'потом')";


private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS "
        + TABLE_NAME;

public SynDB(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

    db.execSQL(SQL_CREATE_ENTRIES);
    db.execSQL(SQL_INSERT_WORDS);


}

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

    Log.w("LOG_TAG", "Обновление базы данных с версии " + oldVersion
            + " до версии " + newVersion + ", которое удалит все старые данные");

    db.execSQL(SQL_DELETE_ENTRIES);

    onCreate(db);
 }

}     

第二个数据库类:

package ru.psdevteam.syndicttest.app;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class IdDB extends SQLiteOpenHelper {
//constants
private static final String DATABASE_NAME = "synonyms.db";
public static final String TABLE_NAME_2 = "synonyms";
private static final int DATABASE_VERSION = 1;
public static final String StringID = "s_id";
public static final String SynWordID = "w_id";

// test database with test data inside
private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
        + TABLE_NAME_2 + " (" + SynWordID + " INTEGER NOT NULL ,"
        + StringID + " INTEGER NOT NULL); ";

public static final String SQL_INSERT_IDS = "INSERT INTO " + TABLE_NAME_2 + " (w_id,s_id)  " + "VALUES " + "(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9),(1,10)";

private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + TABLE_NAME_2;

public IdDB(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase dbsyn) {

    dbsyn.execSQL(SQL_CREATE_ENTRIES);
    dbsyn.execSQL(SQL_INSERT_IDS);
}

@Override
public void onUpgrade(SQLiteDatabase dbsyn, int oldSynVersion, int newSynVersion) {
    Log.w("LOG_TAG", "Обновление базы данных с версии " + oldSynVersion
            + " до версии " + newSynVersion + ", которое удалит все старые данные");

    dbsyn.execSQL(SQL_DELETE_ENTRIES);

    onCreate(dbsyn);
 }
}

和主要 Activity :

package ru.psdevteam.syndicttest.app;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;


public class MainActivity extends Activity implements OnClickListener {

final String LOG_TAG = "myLogs";

IdDB idh;
SynDB sqh;
SQLiteDatabase sqdb, iddb;
Button btnOk;
EditText etWord;
String eWord;



String[] syns = { "Synonym1", "Synonym2", "Synonym3", "Synonym4", "Synonym5", "Synonym6", // it's just for example. I must change it for display list of synonyms from DB
        "Synonym7", "Synonym8", "Synonym9", "Synonym10", "Synonym11" };


public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    etWord = (EditText) findViewById(R.id.etWord);
    btnOk = (Button) this.findViewById(R.id.btnOk);
    ListView lvMain = (ListView) findViewById(R.id.lvMain);
    btnOk.setOnClickListener(btnOkListener);

    //initialize our class-cover IdDB
    idh = new IdDB(this);

    // initialize our class-cover SynDB
    sqh = new SynDB(this);

    // we need db to read and write
    sqdb = sqh.getWritableDatabase();
    iddb = idh.getWritableDatabase();




    // создаем адаптер
    ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_list_item_1, syns);

    // присваиваем адаптер списку
    lvMain.setAdapter(adapter);

}

   public OnClickListener btnOkListener = new OnClickListener(){





        @Override
        public void onClick(View v) {

            eWord = etWord.getText().toString();



            switch (v.getId()){
                //if "Ок" is pressed (it is the only one)
                case R.id.btnOk:
                    //i want to unite all my queries and out in listView result of FinalQuery

                    String query = "SELECT " + SynDB.WordID +" FROM " + SynDB.TABLE_NAME + " WHERE " + SynDB.Word + " ='" + eWord + "'"; // first query: here you enter the word and search it's ID in the table
                    String IdQuery = "SELECT " + IdDB.SynWordID +"," + IdDB.StringID  + " FROM " + IdDB.TABLE_NAME_2 + " WHERE " + IdDB.SynWordID + " ='" + 1 + "' " + "or " + IdDB.StringID + " ='" + 1 + "'";// second query: here ID from first table compare with ID from second table
                    String FinalQuery = "SELECT " + SynDB.Word + " FROM " + SynDB.TABLE_NAME + " WHERE " + SynDB.WordID + " IN " + "('1','2','3','4')" ; // wrong final query.  here should be selected already sorted by ID synonyms

                    Cursor cursor3 = iddb.rawQuery(IdQuery, null);
                    Cursor cursorLast = sqdb.rawQuery(FinalQuery, null);
                    Log.d(LOG_TAG, "--- Rows in mytable: ---");
                    // делаем запрос всех данных из таблицы words, получаем Cursor
                    Cursor cursor2 = sqdb.rawQuery(query, null);

                    // ставим позицию курсора на первую строку выборки
                    // если в выборке нет строк, вернется false
                    if (cursor2.moveToFirst()) {

                        // определяем номера столбцов по имени в выборке
                        int idColIndex = cursor2.getColumnIndex("_id");
                        int wordColIndex = cursor2.getColumnIndex("word");


                        do {
                            // получаем значения по номерам столбцов и пишем все в лог
                            Log.d(LOG_TAG,
                                    "_id = " + cursor2.getInt(idColIndex) +
                                            ", word = " + cursor2.getString(wordColIndex));

                            // переход на следующую строку
                            // а если следующей нет (текущая - последняя), то false - выходим из цикла
                        } while (cursor2.moveToNext());
                    } else
                        Log.d(LOG_TAG, "0 rows");
                    cursor2.close();
                    break;

                default:
                    break;
            } // close switch
        } // close onClick
    };




protected void onStop() {
    // TODO Auto-generated method stub
    super.onStop();
    // закрываем соединения с базой данных
    sqdb.close();
    sqh.close();
}
public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.main, menu);
    return true;
}


@Override
public void onClick(View view) {

 }
}

日志猫:

05-08 09:08:32.559    1583-1583/ru.psdevteam.syndicttest.app W/KeyCharacterMap﹕ Can't          open keycharmap file
05-08 09:08:32.559    1583-1583/ru.psdevteam.syndicttest.app W/KeyCharacterMap﹕ Error  loading keycharmap file '/system/usr/keychars/AT_Translated_Set_2_keyboard.kcm.bin'.  hw.keyboards.65540.devname='AT Translated Set 2 keyboard'
05-08 09:08:32.559    1583-1583/ru.psdevteam.syndicttest.app W/KeyCharacterMap﹕ Using  default keymap: /system/usr/keychars/qwerty.kcm.bin
05-08 09:08:42.309    1583-1583/ru.psdevteam.syndicttest.app D/myLogs﹕ --- Rows in mytable: ---
05-08 09:08:42.309    1583-1583/ru.psdevteam.syndicttest.app E/CursorWindow﹕ Bad request for field slot 0,-1. numRows = 1, numColumns = 1
05-08 09:08:42.309    1583-1583/ru.psdevteam.syndicttest.app D/AndroidRuntime﹕ Shutting down VM
05-08 09:08:42.309    1583-1583/ru.psdevteam.syndicttest.app W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xb574d4f0)
05-08 09:08:42.319    1583-1583/ru.psdevteam.syndicttest.app E/AndroidRuntime﹕ FATAL     EXCEPTION: main
java.lang.IllegalStateException: get field slot from row 0 col -1 failed
        at android.database.CursorWindow.getString_native(Native Method)
        at android.database.CursorWindow.getString(CursorWindow.java:338)
        at  android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:49)
        at ru.psdevteam.syndicttest.app.MainActivity$1.onClick(MainActivity.java:106)
        at android.view.View.performClick(View.java:2485)
        at android.view.View$PerformClick.run(View.java:9081)
        at android.os.Handler.handleCallback(Handler.java:587)
        at android.os.Handler.dispatchMessage(Handler.java:92)
        at android.os.Looper.loop(Looper.java:130)
        at android.app.ActivityThread.main(ActivityThread.java:3683)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:507)
        at     com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
        at dalvik.system.NativeStart.main(Native Method)
05-08 09:08:46.649    1583-1583/ru.psdevteam.syndicttest.app I/Process﹕ Sending signal.      PID: 1583 SIG: 9

Answer 1

您的查询是

String query = "SELECT " + SynDB.WordID +" FROM " + SynDB.TABLE_NAME + " WHERE " + SynDB.Word + " ='" + eWord + "'"; // first query: here you enter the word and search it's ID in the table

仅选择一列。

然后您尝试在那里获取两列:

int idColIndex = cursor2.getColumnIndex("_id");
int wordColIndex = cursor2.getColumnIndex("word");

// ...

Log.d(LOG_TAG,
            "_id = " + cursor2.getInt(idColIndex) +
                    ", word = " + cursor2.getString(wordColIndex));

getColumnIndex() 对于光标中不存在的列返回 -1。

将 word 添加到 SELECT 以将其包含在光标中。