Android中SQLite使用

Android sdk是自带数据库的，我们使用时需要继承SQLiteOpenHelper.java抽象类即可！

1.首先创建个SQLiteHelper来继承抽象类：

class SQLiteHelper(var context: Context?, name: String?, factory: SQLiteDatabase.CursorFactory?, version: Int) : SQLiteOpenHelper(context, name, factory, version) {

 private val sql="create table if not exists collect("
            + "id integer primary key autoincrement,"
            +"author text,"
            +"price real,"
            +"pages integer,"
            +"name text)"

    override fun onCreate(db: SQLiteDatabase?) {
        db!!.execSQL(sql)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db!!.execSQL("drop table if exists collect")
        onCreate(db)
    }
}

2.接下来去使用它，在使用之前记得授权读写权限

class MainActivity : AppCompatActivity(), View.OnClickListener {

    private var dbHelper: SQLiteHelper? = null
    private var databaseContext: DatabaseContext? = null
    private val TABLE_NAME = "collect"
    private val DATA_BASE = "CollectStore.db"
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        //运行时权限
        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {
            val perms = arrayOf(Manifest.permission.WRITE_EXTERNAL_STORAGE, Manifest.permission.READ_EXTERNAL_STORAGE)
            if (checkSelfPermission(perms[0]) == PackageManager.PERMISSION_DENIED && checkSelfPermission(perms[1]) == PackageManager.PERMISSION_DENIED) {
                requestPermissions(perms, 200)
            }
        }
        init()
    }

    private fun init() {
//        databaseContext = DatabaseContext(this)  指定存储路径
        dbHelper = SQLiteHelper(this, DATA_BASE, null, 1)

    }


    override fun onClick(v: View) {
        when (v.id) {
            R.id.btn_create ->
                dbHelper!!.writableDatabase //创建成功后就不会重复创建;
            R.id.btn_add -> {
                val db = dbHelper!!.writableDatabase
                val values = ContentValues()
                var i = 0
                while (i < 10) {
                    values.clear()
                    values.put("author", "范冰冰$i")
                    values.put("price", 188.88)
                    values.put("pages", 120)
                    values.put("name", "洪金宝$i")
                    i += 2
                }
                Toast.makeText(databaseContext, "添加成功！", Toast.LENGTH_SHORT).show()
            }

            R.id.btn_update -> {
                val db2 = dbHelper!!.writableDatabase
                val contentValues = ContentValues()
                contentValues.put("price", 12.99)
                db2.update(TABLE_NAME, contentValues, "author=?", arrayOf("范冰冰0"))
                Toast.makeText(databaseContext, "修改成功！", Toast.LENGTH_SHORT).show()
            }
            R.id.btn_delete -> {
                val db3 = dbHelper!!.writableDatabase
                db3.delete(TABLE_NAME, "author=?", arrayOf("范冰冰0"))
                Toast.makeText(databaseContext, "删除成功！", Toast.LENGTH_SHORT).show()
            }
            R.id.btn_query -> {
                val writableDatabase = dbHelper!!.writableDatabase
                val cursor = writableDatabase.query(TABLE_NAME, null, null, null, null, null, null)
                val list = ArrayList<DataBean>()
                while (cursor.moveToNext()) {
                    val dataBean = DataBean()
                    val author = cursor.getString(cursor.getColumnIndex("author"))
                    val price = cursor.getDouble(cursor.getColumnIndex("price"))
                    val pages = cursor.getInt(cursor.getColumnIndex("pages"))
                    val name = cursor.getString(cursor.getColumnIndex("name"))
                    dataBean.author = author
                    dataBean.price = price
                    dataBean.pages = pages
                    dataBean.name = name
                    list.add(dataBean)
                    Log.i("8888888", author + "---" + price + "---" + pages + "---" + name)
                }
                cursor.close()
            }
            else -> {
            }
        }
    }

    override fun onDestroy() {
        super.onDestroy()
        val db = dbHelper!!.getWritableDatabase()
        db.close()
    }
}

3.如上写了 增、删、改、查操作

这时候我们想要从设备上找到我们创建的数据库是找不到的，要root权限

因此需要我们自定义路径，我创建了DatabaseContext.java这个类

public class DatabaseContext extends ContextWrapper {

    private Context mContext;

    public DatabaseContext(Context context) {
        super(context);
        this.mContext=context;
    }

    /**
     * 获得数据库路径，如果不存在，则创建对象对象
     *
     * @param name : CollectStore.db
     */
    @Override
    public File getDatabasePath(String name) {
        //判断是否存在sd卡
        boolean sdExist = Environment.MEDIA_MOUNTED.equals(Environment.getExternalStorageState());
        if (!sdExist) {//如果不存在,
            Log.e("SD卡管理：", "SD卡不存在，请加载SD卡");
            return null;
        } else {
            String path = mContext.getExternalFilesDir(null).getAbsolutePath();
//            String path = Environment.getExternalStorageDirectory().getAbsolutePath() + File.separator + getPackageName();
            File dirFile = new File(path);
            if (!dirFile.exists()) {
                dirFile.mkdirs();
            }
            boolean isFileCreateSuccess = false;
            //判断文件是否存在，不存在则创建该文件
            File dbFile = new File(path + File.separator + name);
            if (!dbFile.exists()) {
                try {
                    boolean newFile = dbFile.createNewFile();
                    isFileCreateSuccess = newFile;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else {
                isFileCreateSuccess = true;
            }

            //返回数据库文件对象
            if (isFileCreateSuccess) {
                return dbFile;
            } else {
                return null;
            }
        }
    }

    /**
     * 重载这个方法，是用来打开SD卡上的数据库的，android 2.3及以下会调用这个方法。
     *
     * @param name
     * @param mode
     * @param factory
     */
    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode,
                                               CursorFactory factory) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
        return result;
    }

    /**
     * Android 4.0以上会调用此方法获取数据库。
     */
    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, CursorFactory factory,
                                               DatabaseErrorHandler errorHandler) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
        return result;
    }

调用后就可以看到数据库文件了

注释：witableDatabase内部调用了openOrCreateDatabase，可以不用重写该方法

最后附上数据库图片，亲测可用

 

eg: https://blog.youkuaiyun.com/mfysss/article/details/128059700