Sqlite的增删改查 和java.lang.IllegalStateException: Couldn't read row 1, col -1 from CursorWindow

隔了挺久的，又遇到使用sqlite数据库了。写了个小demo，放在这用来加强记忆。

1.建个数据表

 

package com.xing.xiagaocheng.fileupdownload3.db;

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

public class SqliteHelper extends SQLiteOpenHelper {

    public SqliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        StringBuffer vStringBuffer=new StringBuffer();

        //新建上传表头  文件名（用来管理文件的上传，上传大小，上传）
        vStringBuffer.append("Create table upload_file(");
        vStringBuffer.append("AutoID integer primary key autoincrement,");
//        vStringBuffer.append("BillID varchar(50),");
//        vStringBuffer.append("FileDate varchar(20)");
        vStringBuffer.append("FilePath varchar(50),");  //文件途径
        vStringBuffer.append("FileSize varchar(50),");  //文件上传大小
        vStringBuffer.append("UploadState int)");       //文件上传状态 , 0表示未上传，1表示已上传，2表示正在上传

        String sSQL=vStringBuffer.toString();
        sqLiteDatabase.execSQL(sSQL);

//        //新建上传表明细
//        vStringBuffer=new StringBuffer();
//        vStringBuffer.append("create table t_upload_list(");
//        vStringBuffer.append("AutoID integer primary key autoincrement,");
//        vStringBuffer.append("BillID varchar(50),");
//        vStringBuffer.append("UploadNum int)");
//        sSQL=vStringBuffer.toString();
//        sqLiteDatabase.execSQL(sSQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

2.建立一个类，编写增删改查方法

package com.intefly.xiagaocheng.fileupdownload3.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class SqliteManager {

    private SqliteHelper sqliteHelper;
    private static SqliteManager sqliteManager;

    private SqliteManager(Context context,String name,int version){
        this.sqliteHelper=new SqliteHelper(context,name,null,version);
    }

    public static SqliteManager getInstance(Context context,String name,int version){
        if (sqliteManager==null){
            synchronized (SqliteManager.class){
                sqliteManager=new SqliteManager(context,name,version);
            }
        }
        return sqliteManager;
    }

    /**
     * 添加一个item进去
     * @param contentValues
     */
    public void saveItem(ContentValues contentValues){
        SQLiteDatabase sdb=sqliteHelper.getWritableDatabase();
        sdb.insert("upload_file",null,contentValues);
        Log.e("tiwolf", "saveItem: "+contentValues.get("FilePath") );
    }

    /**
     * 删除某一个item
     * @param filePath
     */
    public void delete(String filePath){
        SQLiteDatabase sdb=sqliteHelper.getWritableDatabase();
        sdb.execSQL("delete from upload_file where FilePath=?",new Object[]{filePath});
        Log.e("tiwolf", "delete: "+filePath );
    }

    /**
     * 更新某一条数据,这个需要注意符号
     * @param contentValues
     */
    public void update(ContentValues contentValues){
        SQLiteDatabase sdb=sqliteHelper.getWritableDatabase();
        Log.e("tiwolf", "update: "+contentValues.get("FilePath")+"--"+contentValues.get("UploadState") );
//        sdb.update("upload_file",contentValues,null,null);
        //修改SQL语句
        String sql="update upload_file set UploadState = "+contentValues.get("UploadState")+",FileSize = '"+contentValues.get("FileSize")+"' where FilePath = '"+contentValues.get("FilePath")+"'";
        sdb.execSQL(sql);

    }

    /**
     * 仅仅查询一条数据
     * @param fileState
     * @return
     */
    public String check(String fileState){
        SQLiteDatabase sdb=sqliteHelper.getReadableDatabase();
//        sdb.execSQL("select FilePath from upload_file where UploadState=?");
        //获取FilePath，FileSize 在条件是什么的时候。如果这个缺少，则缺少哪个，下面哪个就会报错
        Cursor cursor = sdb.rawQuery("select FilePath,FileSize from upload_file where UploadState=?", new String[]{fileState});
//        Cursor cursor = sdb.rawQuery("select FilePath from upload_file where UploadState=?", new String[]{fileState});
        String filePath="kong";
//        cursor.moveToLast();
        if(cursor.moveToLast()){
            //一开始我在select 后面写少了FileSize，结果老是报错
            //java.lang.IllegalStateException: Couldn't read row 1, col -1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.
            filePath=cursor.getString(cursor.getColumnIndex("FilePath"));
            String filesize=cursor.getString(cursor.getColumnIndex("FileSize"));
            filePath=filePath+":"+filesize;

        }
        Log.e("tiwolf", "check:"+filePath );
        cursor.close();
        return filePath;
    }

    public List<String> chechAll(String fileSate){
        SQLiteDatabase sdb=sqliteHelper.getReadableDatabase();
//        vCursor=vReadSQLite.query("t_upload_head",new String[]{"BillID","FilePath","FileSize"},"UploadState=?",new String[]{"0"},null,null,null);

        Cursor cursor= sdb.query("upload_file",new String[]{"FilePath","FileSize"},"UploadState=?",new String[]{fileSate},null,null,null);
        List<String> list=new ArrayList<>();
        while (cursor.moveToNext()){
            Log.e("tiwolf", "chechAll:"+cursor.getString(0));
            list.add("路径："+cursor.getString(cursor.getColumnIndex("FilePath"))+",大小为："+cursor.getString(cursor.getColumnIndex("FileSize")));
        }
        cursor.close();
        return list;
    }
}

3.主界面使用

package com.intefly.xiagaocheng.fileupdownload3;

import android.content.ContentValues;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import com.hjq.permissions.OnPermission;
import com.hjq.permissions.Permission;
import com.hjq.permissions.XXPermissions;
import com.intefly.xiagaocheng.fileupdownload3.db.SqliteManager;

import java.util.List;

import androidx.appcompat.app.AppCompatActivity;


public class MainActivity extends AppCompatActivity {

    /**
     * 动态申请的权限
     */
    private static final String[] PERMISSIONS = {Permission.READ_EXTERNAL_STORAGE, Permission.WRITE_EXTERNAL_STORAGE,Permission.CAMERA,Permission.RECORD_AUDIO};

    SqliteManager sqliteManager;

    Button addBtn,delBtn,updateBtn,queryBtn,queryAllBtn;
    EditText inputEt,inputEt1;
    TextView showTv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        requestPermission();

        sqliteManager=SqliteManager.getInstance(MainActivity.this,"solar",1);

        addBtn=findViewById(R.id.add);
        delBtn=findViewById(R.id.delete);
        updateBtn=findViewById(R.id.update);
        queryBtn=findViewById(R.id.check);
        queryAllBtn=findViewById(R.id.check1);

        inputEt=findViewById(R.id.input);
        inputEt1=findViewById(R.id.input1);
        showTv=findViewById(R.id.content);

        addBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (inputEt.getText().toString().length()!=0 && inputEt1.getText().toString().length()!=0){
                    ContentValues contentValues=new ContentValues();
                    contentValues.put("FilePath",inputEt.getText().toString());
                    contentValues.put("UploadState",Integer.valueOf(inputEt1.getText().toString()));
                    sqliteManager.saveItem(contentValues);
                }

            }
        });

        delBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (inputEt.getText().toString().length()!=0){
                    sqliteManager.delete(inputEt.getText().toString());
                }

            }
        });

        updateBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (inputEt1.getText().toString().length()!=0 && inputEt.getText().toString().length()!=0){
                    ContentValues contentValues=new ContentValues();
                    contentValues.put("FilePath",inputEt.getText().toString());
                    contentValues.put("UploadState",Integer.valueOf(inputEt1.getText().toString()));
                    contentValues.put("FileSize","21453");
                    sqliteManager.update(contentValues);
                }
            }
        });

        queryBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (inputEt1.getText().toString().length()!=0){
                    String get=sqliteManager.check(inputEt1.getText().toString());
                    showTv.setText(get);
                }
            }
        });

        queryAllBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                List<String> stringList = sqliteManager.chechAll(inputEt1.getText().toString());
                for (int i = 0; i < stringList.size(); i++) {
                    Log.e("tiwolf", "onClick: "+stringList.get(i) );
                }
            }
        });

    }


    /**
     * 请求权限
     */
    private void requestPermission() {
        if (!XXPermissions.isHasPermission(MainActivity.this, PERMISSIONS)) {
            XXPermissions.with(this).constantRequest().permission(PERMISSIONS)
                    .request(new OnPermission() {
                        @Override
                        public void hasPermission(List<String> granted, boolean isAll) {
                            if (isAll) {
//                                ToastUtils.toast(MainActivity.this, "获取权限成功");
                                Log.e("tiwolf", "获取权限成功");

                            } else {
//                                ToastUtils.toast(MainActivity.this, "获取权限成功，部分权限未正常授予");
                                Log.e("tiwolf", "获取权限成功，部分权限未正常授予");
                            }
                        }

                        @Override
                        public void noPermission(List<String> denied, boolean quick) {
                            if (quick) {
//                                ToastUtils.toast(MainActivity.this, "被永久拒绝授权，请手动授予权限");
                                Log.e("tiwolf", "被永久拒绝授权，请手动授予权限");
                                //如果是被永久拒绝就跳转到应用权限系统设置页面
                                XXPermissions.gotoPermissionSettings(MainActivity.this);
                            } else {
//                                ToastUtils.toast(MainActivity.this, "获取权限失败");
                                Log.e("tiwolf", "获取权限失败");
                            }
                        }
                    });
        }
    }

}

 

搞定收工