代码之家  ›  专栏  ›  技术社区  ›  Manohar

基于字符串名称的Sqlite数据库查询

sqliteopenhelper android-sqlite android
  •  1
  • Manohar  · 技术社区  · 8 年前

    我在我的应用程序中创建了sqlite数据库,并试图保存突出显示的文本,保存一切正常,甚至检索所有突出显示也正常,但当我基于bookname查询突出显示时,我得到了一个错误。

    这是我迄今为止尝试过的-这是我的 oncreate sqlite数据库的: 

    @Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_HIGHLIGHTS + "("  
            + KEY_ID + " INTEGER PRIMARY KEY," +KEY_BOOKNAME +" TEXT," + KEY_HIGHLIGHT+" TEXT,"   + KEY_TEXTSIZE + " INTEGER,"  
            + KEY_PAGENO + " INTEGER" + ")";  
    db.execSQL(CREATE_CONTACTS_TABLE);  
}

    这很好用 

      public List<HighlightSave> getAllHighlights() {

    List<HighlightSave> saveAllHighlights=new ArrayList<HighlightSave>();

    String selectQuery="SELECT * FROM "+TABLE_HIGHLIGHTS;

    SQLiteDatabase db=this.getWritableDatabase();

    Cursor cursor=db.rawQuery( selectQuery,null);

    if(cursor.moveToFirst()) { 
        do {
            HighlightSave hs=new HighlightSave();
            hs.setBookName(cursor.getString(1));
            hs.setHighlightedText(cursor.getString(2));

            hs.setTextSize(Integer.parseInt(cursor.getString(3)));
            hs.setPageno(Integer.parseInt(cursor.getString(4)));

            saveAllHighlights.add(hs);
        } while(cursor.moveToNext());
    }

    cursor.close();
    db.close();

    return saveAllHighlights;
}

    问题是,当我尝试使用下面这样的书名进行查询时 

     public List<HighlightSave> getBookHighlights(String bookname) {

    SQLiteDatabase db= this.getReadableDatabase();

    List<HighlightSave> AllHighlightsOfBook= new ArrayList<HighlightSave>();

       Cursor cursor = db.query(TABLE_HIGHLIGHTS, new String[] {KEY_BOOKNAME,KEY_HIGHLIGHT,KEY_TEXTSIZE,KEY_PAGENO}, KEY_BOOKNAME + "=?", new String[] { bookname }, null, null, null, null);

    if(cursor.moveToFirst()){

        do{
            HighlightSave hs=new HighlightSave();
            hs.setBookName(cursor.getString(1));
            hs.setHighlightedText(cursor.getString(2));

            hs.setTextSize(Integer.parseInt(cursor.getString(3)));
            hs.setPageno(Integer.parseInt(cursor.getString(4)));

            AllHighlightsOfBook.add(hs);
        } while(cursor.moveToNext());
    }

    cursor.close();
    db.close();

    return AllHighlightsOfBook; 
}

    我得到这个错误

    12-05 11:03:48.025:E/AndroidRuntime(8811):致命异常:main
    12-05 11:03:48.025:E/AndroidRuntime(8811):进程:com.wowio。电子书阅读器,PID:8811

    12-05 11:03:48.025:E/AndroidRuntime(8811):在android.database.CursorWindow。nativeGetString(本机方法) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.database.CursorWindow.getString(CursorWindow.java:438) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.database.AbstractWindowedCursor.getString(AbstractWindowsedCursors.java:51) 12-05 11:03:48.025:E/AndroidRuntime(8811):在com.eminosoft.epubreader.HighlightDatabase.getBookHighlights(HighlightDatabase.java:160) 12-05 11:03:48.025:E/AndroidRuntime(8811):在com.eminosoft.epubreader.HighlightFragment.onActivityCreated(HighlightFragment.java:66) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.support.v4.app.Fragment.performacitivity创建(Fragment.java:1508) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:958) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1115) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.support.v4.app.BackStackRecord.run(BackStackRecord.java:682) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.support.v4.app.FragmentManagerImpl.ExependingActions(FragmentManager.java:1478) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.os.Handler.handleCallback(handle.java:739) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.os.Handler.dispatchMessage(Handler.java:95) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.os.Looper.loop(Looper.java:148) 12-05 11:03:48.025:E/AndroidRuntime(8811):在android.app.ActivityThread.main(ActivityThread.java:5422) 12-05 11:03:48.025:E/AndroidRuntime(8811):位于java.lang.reflect.Method。调用(本机方法) 12-05 11:03:48.025:E/AndroidRuntime(8811):在com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 12-05 11:03:48.025:E/AndroidRuntime(8811):在com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

    所以我认为游标有问题,所以在getBookHighlights()中这样初始化它 

    String query = "Select * from "+TABLE_HIGHLIGHTS+ " Where " +KEY_BOOKNAME + " = " + bookname ;
    Cursor cursor=  db.rawQuery(query,null);

    然后我得到了这个错误 

    12-05 11:20:06.514: E/AndroidRuntime(12305): FATAL EXCEPTION: main
12-05 11:20:06.514: E/AndroidRuntime(12305): Process: com.wowio.ebookreader, PID: 12305
12-05 11:20:06.514: E/AndroidRuntime(12305): android.database.sqlite.SQLiteException: near "Raven": syntax error (code 1): , while compiling: Select * from highlights Where book_Name = The Raven
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.eminosoft.epubreader.HighlightDatabase.getBookHighlights(HighlightDatabase.java:146)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.eminosoft.epubreader.HighlightFragment.onActivityCreated(HighlightFragment.java:66)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.Fragment.performActivityCreated(Fragment.java:1508)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:958)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1115)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.BackStackRecord.run(BackStackRecord.java:682)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1478)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl$1.run(FragmentManager.java:446)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.os.Handler.handleCallback(Handler.java:739)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.os.Handler.dispatchMessage(Handler.java:95)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.os.Looper.loop(Looper.java:148)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.app.ActivityThread.main(ActivityThread.java:5422)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at java.lang.reflect.Method.invoke(Native Method)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

    我做错了什么?(我在每次运行前都卸载了应用程序,因此以前的数据库不会有任何问题。)

    3 回复  |  直到 6 年前
        1
  •  2
  •   IntelliJ Amiya    8 年前

    纠正 选择 首先询问 

     String query = "SELECT * FROM " + TABLE_HIGHLIGHTS+ " WHERE " + KEY_BOOKNAME + " = '" + bookname + "'";

    然后 卸载旧应用程序并再次运行 .

        2
  •  2
  •   MashukKhan Ijaz    8 年前

    有关获取列值的错误。

    列索引以Sqlite游标中的零开始。

    java.lang.IllegalStateException: Couldn't read row 0, col 4 from CursorWindow.

    因此,上一行的含义是“4”列在游标对象中不可用

    您尝试0到3。我认为应该解决此问题。

        3
  •  1
  •   Rushi Ayyappa    8 年前 
        String query="select * from "+TABLE_HIGHLIGHTS+" where "+KEY_BOOKNAME+"='"+bookname+"'";
    推荐文章
    user4813855  ·  在sqlite-Android中加速选择查询
    7 年前
    KEERTHANA NAGARAJ  ·  我正在Android Studio中做一个小项目,我正在使用SQLite。插入操作附近有一个错误
    7 年前
    Manohar  ·  基于字符串名称的Sqlite数据库查询
    8 年前
    Othmane  ·  android.database.sqlite。SQLiteException:编译时没有这样的列:UPDATE[duplicate]
    8 年前
    Nick  ·  onCreate时无法使用SQLite Default
    10 年前
    user3388473  ·  SQLiteOpenHelper:NullPointerException
    11 年前
    Qadir Hussain  ·  为什么我们需要onUpgrade();SQLiteOpenHelper类中的方法
    12 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号