问未能从具有1行9列的CursorWindow中读取第0行第1列
EN

Stack Overflow用户

提问于 2022-06-13 22:16:31

回答 1查看 73关注 0票数 -1

我试图在我的活动中将数据从sqlite中的表加载到我的ExpandableListView。我跟踪了这个问题的答案，Android ExpandableListView and SQLite Database。

我的父列表将显示MVP_INDUSTRY_TYPE列取决于选定的日期，我的子列表将显示MVP_BRCH_CODE_NAME和MVP_BRCH_ADDRESS。

DatabaseHelper。java

package com.example.spmapp;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class DataBaseHelper extends SQLiteOpenHelper {

    public static final String MVP_TBL = "MVP_tbl";
    public static final String MVP_BRCH_CODE_NAME = "MVP_BRCH_CODE_NAME";
    public static final String MVP_ID = "MVP_ID";
    public static final String MVP_BRCH_ADDRESS = "MVP_BRCH_ADDRESS";
    public static final String MVP_AREA = "AREA";
    public static final String MVP_AREA_CODE = "AREA_CODE";
    public static final String MVP_INDUSTRY_TYPE = "MVP_INDUSTRY_TYPE";
    public static final String token = "TOKEN";
    public static final String MVP_DATE = "SCHEDULED_DATE";
    public static final String MVP_CLASS_ID = "MVP_CLASS_ID";



    public DataBaseHelper(@Nullable Context context) {
        super(context, "taggedList.db", null, 1);

    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String createMVPVirtualTableStatement = "CREATE VIRTUAL TABLE IF NOT EXISTS " + MVP_TBL + " USING FTS3(" + MVP_BRCH_CODE_NAME + ", " + MVP_ID + " , " + MVP_BRCH_ADDRESS + ", " + MVP_AREA + ", " + MVP_AREA_CODE + ", " + MVP_INDUSTRY_TYPE + ", " + token + ", " + MVP_DATE + ", " + MVP_CLASS_ID + ")";
        sqLiteDatabase.execSQL(createMVPVirtualTableStatement);

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        onCreate(sqLiteDatabase);
    }
    public Cursor getSelectedParentMVPDate(String txtMVPDate){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "Select * from "+MVP_TBL+" WHERE " +MVP_DATE+ " LIKE '%"+txtMVPDate+"%' LIMIT 50";
        return db.rawQuery(query, null);
    }
    public Cursor getSelectedChildMVPDate(String MVPIndustry){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "Select * from "+MVP_TBL+" WHERE " +MVP_INDUSTRY_TYPE+ " LIKE '%"+MVPIndustry+"%' LIMIT 50";
        return db.rawQuery(query, null);
    }

}

MasterVisitPlan.java

public class MasterVisitPlan extends AppCompatActivity implements AdapterView.OnItemSelectedListener,CalendarAdapter.OnItemListener {

    private boolean isBackPressedOnce = false;

    public static String email;
    public static String VisitDate;
    public static String ItemBrnchCodeName;
    public static String mrCode;
    public static String ClassID;
    private static String token;
    private static String bearerToken;
    public static int counterVisit;
    private RecyclerView calendarRecyclerView;
    private LocalDate selectedDate;
    private TextView monthYearText;


    TextView empName, empPos, date;

    GoogleSignInClient mGoogleSignInClient;

    DrawerLayout drawerLayout;

    ImageView gps, empPhoto;

    ConstraintLayout calendar;

    DataBaseHelper dataBaseHelper;
    private Cursor mGroupsCursor; // cursor for list of groups (list top nodes)
    private int mGroupIdColumnIndex;
    private MyExpandableListAdapter mAdapter;

    SimpleCursorAdapter sca;
    Cursor csr;
    SearchView searchView;
    ListView Searchlstview;


    @Override
    protected void onCreate(Bundle savedIntanceState) {
        super.onCreate(savedIntanceState);
        setContentView(R.layout.activity_master_visit_plan);
        initWidgets();
        selectedDate = LocalDate.now();
        setMonthView();
        setOrRefreshListView();
        getCurrentDate();

        dataBaseHelper = new DataBaseHelper(MasterVisitPlan.this);
        fillMVPdata();  
    }

    private void fillMVPdata(){
        mGroupsCursor = dataBaseHelper.getSelectedParentMVPDate(date.getText().toString());
        startManagingCursor(mGroupsCursor);
        mGroupsCursor.moveToFirst();


        ExpandableListView Selectedlstview = (ExpandableListView) findViewById(R.id.MVPListitem);
        mAdapter = new MyExpandableListAdapter(mGroupsCursor, this,

                R.layout.mvp_list_parent,
                R.layout.mvp_list_child,
                new String[] {DataBaseHelper.MVP_INDUSTRY_TYPE},
                new int[] {R.id.txtMVPParent},
                new String[] {DataBaseHelper.MVP_BRCH_CODE_NAME, DataBaseHelper.MVP_BRCH_ADDRESS},
                new int[] {R.id.txtviewBrnchCodeName, R.id.txtviewBrchAddr});

                Selectedlstview.setAdapter(mAdapter);

    }
    public class MyExpandableListAdapter extends SimpleCursorTreeAdapter {
        public MyExpandableListAdapter(Cursor cursor, Context context,int groupLayout, int childLayout, String[] groupFrom,
                                       int[] groupTo, String[] childrenFrom, int[] childrenTo) {
            super(context, cursor, groupLayout, groupFrom, groupTo,
                    childLayout, childrenFrom, childrenTo);
        }

        @Override
        protected Cursor getChildrenCursor(Cursor cursor) {
            @SuppressLint("Range") Cursor childCursor = dataBaseHelper.getSelectedChildMVPDate(cursor.getString(cursor.getColumnIndex("MVP_INDUSTRY_TYPE")));
            startManagingCursor(childCursor);
            childCursor.moveToFirst();
            return childCursor;
        }
    }
}

java

android-sqlite

单节点MySQL

低成本高体验，解决您的基础业务数据需求

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-06-14 17:59:56

列getColumnIndex -1表示在使用方法时列不在游标中。

由于提供的代码中唯一出现的方法是在getSelectedChildMVPDate类中的DatabaseHelper方法中，所以问题必须发生在那里。

使用DataBaseHelper类中的附加方法复制代码的要点:-

public void insertMVPTBLRow(String branchCodeName, long id, String address, String area, String areacode, String industryType, String tkn, String date, String classId) {
    ContentValues cv = new ContentValues();
    cv.put(MVP_BRCH_CODE_NAME,branchCodeName);
    cv.put(MVP_ID,id);
    cv.put(MVP_BRCH_ADDRESS,address);
    cv.put(MVP_AREA,area);
    cv.put(MVP_AREA_CODE,areacode);
    cv.put(MVP_INDUSTRY_TYPE,industryType);
    cv.put(token,tkn);
    cv.put(MVP_DATE,date);
    cv.put(MVP_CLASS_ID,classId);
    this.getWritableDatabase().insert(MVP_TBL,null,cv);
}

然后在活动中使用以下代码：

    dataBaseHelper = new DataBaseHelper(this);
    dataBaseHelper.getWritableDatabase().delete(DataBaseHelper.MVP_TBL,null,null);
    dataBaseHelper.insertMVPTBLRow("B1",100,"StreetX","Area1","A001","I001","tkn001",String.valueOf(System.currentTimeMillis()),"CLASS1");
    dataBaseHelper.insertMVPTBLRow("B1",101,"StreetX","Area1","A001","I001","tkn002",String.valueOf(System.currentTimeMillis()),"CLASS1");
    mGroupsCursor = dataBaseHelper.getSelectedParentMVPDate("");
    startManagingCursor(mGroupsCursor);
    mGroupsCursor.moveToFirst();
    Cursor cursor = mGroupsCursor;
    @SuppressLint("Range") Cursor childCursor = dataBaseHelper.getSelectedChildMVPDate(cursor.getString(cursor.getColumnIndex("MVP_INDUSTRY_TYPE")));
    startManagingCursor(childCursor);
    childCursor.moveToFirst();
    DatabaseUtils.dumpCursor(mGroupsCursor);
    DatabaseUtils.dumpCursor(childCursor);

传递

Note空字符串，以便提取所有行。

使用日志成功运行的结果包括：-

2022-06-15 10:55:26.892 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2caa87e
2022-06-15 10:55:26.893 I/System.out: 0 {
2022-06-15 10:55:26.893 I/System.out:    MVP_BRCH_CODE_NAME=B1
2022-06-15 10:55:26.893 I/System.out:    MVP_ID=100
2022-06-15 10:55:26.893 I/System.out:    MVP_BRCH_ADDRESS=StreetX
2022-06-15 10:55:26.893 I/System.out:    AREA=Area1
2022-06-15 10:55:26.893 I/System.out:    AREA_CODE=A001
2022-06-15 10:55:26.893 I/System.out:    MVP_INDUSTRY_TYPE=I001
2022-06-15 10:55:26.893 I/System.out:    TOKEN=tkn001
2022-06-15 10:55:26.893 I/System.out:    SCHEDULED_DATE=1655254526888
2022-06-15 10:55:26.893 I/System.out:    MVP_CLASS_ID=CLASS1
2022-06-15 10:55:26.893 I/System.out: }
2022-06-15 10:55:26.893 I/System.out: 1 {
2022-06-15 10:55:26.893 I/System.out:    MVP_BRCH_CODE_NAME=B1
2022-06-15 10:55:26.893 I/System.out:    MVP_ID=101
2022-06-15 10:55:26.893 I/System.out:    MVP_BRCH_ADDRESS=StreetX
2022-06-15 10:55:26.894 I/System.out:    AREA=Area1
2022-06-15 10:55:26.894 I/System.out:    AREA_CODE=A001
2022-06-15 10:55:26.894 I/System.out:    MVP_INDUSTRY_TYPE=I001
2022-06-15 10:55:26.894 I/System.out:    TOKEN=tkn002
2022-06-15 10:55:26.895 I/System.out:    SCHEDULED_DATE=1655254526889
2022-06-15 10:55:26.895 I/System.out:    MVP_CLASS_ID=CLASS1
2022-06-15 10:55:26.895 I/System.out: }
2022-06-15 10:55:26.895 I/System.out: <<<<<
2022-06-15 10:55:26.895 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f1451df
2022-06-15 10:55:26.895 I/System.out: 0 {
2022-06-15 10:55:26.895 I/System.out:    MVP_BRCH_CODE_NAME=B1
2022-06-15 10:55:26.895 I/System.out:    MVP_ID=100
2022-06-15 10:55:26.895 I/System.out:    MVP_BRCH_ADDRESS=StreetX
2022-06-15 10:55:26.895 I/System.out:    AREA=Area1
2022-06-15 10:55:26.895 I/System.out:    AREA_CODE=A001
2022-06-15 10:55:26.895 I/System.out:    MVP_INDUSTRY_TYPE=I001
2022-06-15 10:55:26.895 I/System.out:    TOKEN=tkn001
2022-06-15 10:55:26.896 I/System.out:    SCHEDULED_DATE=1655254526888
2022-06-15 10:55:26.896 I/System.out:    MVP_CLASS_ID=CLASS1
2022-06-15 10:55:26.896 I/System.out: }
2022-06-15 10:55:26.896 I/System.out: 1 {
2022-06-15 10:55:26.896 I/System.out:    MVP_BRCH_CODE_NAME=B1
2022-06-15 10:55:26.896 I/System.out:    MVP_ID=101
2022-06-15 10:55:26.896 I/System.out:    MVP_BRCH_ADDRESS=StreetX
2022-06-15 10:55:26.896 I/System.out:    AREA=Area1
2022-06-15 10:55:26.896 I/System.out:    AREA_CODE=A001
2022-06-15 10:55:26.896 I/System.out:    MVP_INDUSTRY_TYPE=I001
2022-06-15 10:55:26.896 I/System.out:    TOKEN=tkn002
2022-06-15 10:55:26.896 I/System.out:    SCHEDULED_DATE=1655254526889
2022-06-15 10:55:26.896 I/System.out:    MVP_CLASS_ID=CLASS1
2022-06-15 10:55:26.896 I/System.out: }
2022-06-15 10:55:26.897 I/System.out: <<<<<

现在考虑到消息未能从包含1行、9列的CursorWindow中读取第0行第1列。

然后

光标定位在第0行(而不是位于第一行之前的第1行)

。

未找到指定列的9列的

从两个游标的转储中有9列.

getColumnIndex有一个错误，因为它是区分大小写的

然而，

，由于上面的工作，这不是您的代码的问题，尽管我强烈建议将您的代码更改为使用cursor.getColumnIndex(/*"MVP_INDUSTRY_TYPE" <<<<< CHANGED*/ DataBaseHelper.MVP_INDUSTRY_TYPE) (显然可以省略注释)

因此，

的问题是：-

1. Elsewhere in your code, or
2. That the columns name coded/used in the `onCreate` method have been changed BUT that they have not been applied. 
    - The `onCreate` method runs once and only once when the database is created.
    - The `onCreate` method will never automatically run again unless the database is deleted.
    - Uninstalling the App will delete the database BUT will lose any existing data.
    - If you need to retain existing data, then you need to rebuild the table with the correct column name, noting that virtual tables has restrictions. You would probably need to utilise the `onOpen` method and within the method:- 
        - create the new table BUT with a different name

        - populate the newly created table with the data from the orginal table

        - drop the original table

        - rename the newly created table with the original/required name

        - However, as `onOpen` runs whenever the database is opened you would want to limit this to only do the above when required (i.e. when there is a naming discrepancy). The following `onOPen` method could be the basis

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    Log.d("ONOPEN","ON OPEN INVOKED");
    Cursor checkit = db.query(MVP_TBL,null,null,null,null,null,null,"1");
    boolean needToProceed = false;
    for (String s: new String[]{MVP_AREA,MVP_AREA_CODE,MVP_DATE,MVP_ID,MVP_BRCH_ADDRESS,MVP_BRCH_CODE_NAME,MVP_CLASS_ID,MVP_INDUSTRY_TYPE,token}) {
        boolean found = false;
        for (String cc: checkit.getColumnNames()) {
            if (cc.equals(s)) {
                Log.d("ONOPEN","Column " + cc + " matched " + s);
                found = true;
                break;
            }
        }
        if (!found) {
            Log.d("ONOPEN","!!" + s + " NOT MATCHED.!!" );
            needToProceed = true;
            break;
        }
    }
    if (!needToProceed) return;
    Log.d("ONOPEN","PROCEEDING WITH RECREATION OF " + MVP_TBL +" TABLE.");
    String renamed = "ORIGINAL_" + MVP_TBL;
    db.execSQL("ALTER TABLE " + MVP_TBL + " RENAME TO " + renamed + ";");
    onCreate(db);
    db.execSQL("INSERT INTO " + renamed + " SELECT * FROM " + MVP_TBL);
    db.execSQL("DROP TABLE IF EXISTS " + MVP_TBL);
    db.execSQL("ALTER TABLE " + renamed + " RENAME TO " + MVP_TBL);
}