Android SQLiteOpenHelper使用

作者: Android_冯星 | 来源:发表于2018-06-01 16:11 被阅读0次

Android 数据库初窥
Android SQLiteOpenHelper使用
greenDAO3 入门（配置，基本操作，数据库升级）
Android ORMLite框架入门级介绍
数据持久化-数据库基础SQLiteOpenHelper
Android 笔记 —— 数据库存储
Android 使用Sqlite
getWritableDatabase和getReadableD
greenDAO数据本地化
android 中 SQLiteOpenHelper的封装使用

官方实例代码:
https://developer.android.com/training/data-storage/sqlite#java
https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper
使用：
创建一个类，继承SQLiteOpenHelper

public class SQLiteDbHelp extends SQLiteOpenHelper {

定义好数据库的名字和版本号

public static final String DATABASE_NAME = "saved_recordings.db";
private static final int DATABASE_VERSION = 1;

实现下面的方法,构造中填入数据库名称和版本号.

 public SQLiteDbHelp(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }
     
    /**
     * 当数据库需要升级时调用。实现应使用此方法删除表，添加表或执行升级到新模式版本所需的任何其他操作。
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//        db.execSQL(SQL_DELETE_ENTRIES);
//        onCreate(db);
    }

    }

在onCreate方法中开始创建表和字段.官方文档中都有模板代码，把自己的字段替换即可。

    /**
     * 创建数据库和字段
     */
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + RecorderEntry.TABLE_NAME + " (" +
                    RecorderEntry._ID + " INTEGER PRIMARY KEY," +
                    RecorderEntry.COLUMN_NAME_RECORDER_NAME + " TEXT," +
                    RecorderEntry.COLUMN_NAME_FILE_PATH + " TEXT," +
                    RecorderEntry.COLUMN_NAME_RECORDER_LENGTH + " INTEGER," +
                    RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME + " INTEGER" + ")";

表的实体类

public static class RecorderEntry implements BaseColumns {

        public static final String TABLE_NAME = "save_recorder";

        public static final String COLUMN_NAME_RECORDER_NAME = "recorder_name";
        public static final String COLUMN_NAME_FILE_PATH = "file_path";
        public static final String COLUMN_NAME_RECORDER_LENGTH = "recorder_length";
        public static final String COLUMN_NAME_RECORDER_ADD_TIME = "added_time";
    }

现在数据库和表都已经创建好了。剩下的就是增删改查了。

public long insertRecorder(String name, String path, long time, long length) {
        SQLiteDatabase database = getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(RecorderEntry.COLUMN_NAME_RECORDER_NAME, name);
        contentValues.put(RecorderEntry.COLUMN_NAME_FILE_PATH, path);
        contentValues.put(RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME, time);
        contentValues.put(RecorderEntry.COLUMN_NAME_RECORDER_LENGTH, length);
        long id = database.insert(RecorderEntry.TABLE_NAME, null, contentValues);
        return id;
    }

如果id返回-1则插入失败。

 public void deleteItem(int id) {
        SQLiteDatabase database = getReadableDatabase();
        database.delete(RecorderEntry.TABLE_NAME, RecorderEntry._ID + "=?", new String[]{String.valueOf(id)});
    }

public void renameItem(RecorderItem item, String newName, String path) {
        SQLiteDatabase database = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(RecorderEntry.COLUMN_NAME_RECORDER_NAME, newName);
        values.put(RecorderEntry.COLUMN_NAME_FILE_PATH, path);
        database.update(RecorderEntry.TABLE_NAME, values, RecorderEntry._ID + "=?", new String[]{String.valueOf(item.getId())});
    }

public RecorderItem getRecorderItem(int position) {
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.query(RecorderEntry.TABLE_NAME, PROJECTION, null, null, null, null, null);
        cursor.moveToFirst();
        if (cursor.moveToPosition(position)) {
            RecorderItem item = new RecorderItem();
            item.setId(cursor.getLong(cursor.getColumnIndex(RecorderEntry._ID)));
            item.setLength(cursor.getLong(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_LENGTH)));
            item.setTime(cursor.getLong(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME)));
            item.setName(cursor.getString(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_NAME)));
            item.setPath(cursor.getString(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_FILE_PATH)));
            cursor.close();
            return item;
        } else {
            return null;
        }
    }

都是模板代码，把自己项目中的字段替换即可。

db文件存放位置

db文件默认存放在data/data/目录下。
如何改变目录的位置呢？

有两个方法。嗯.. 相当于是一个吧

先说先简单的实现亲测有效。

/**
     * 设置数据库路径
     *
     * @param context
     */
    public static void setDBPath(Context context) {
//获取的是目录位置 这个随便了
        File dataBaseFile = FileUtils.getTypeFile(context, "db2");
//后面的是数据库的名称
        File dbFile = new File(dataBaseFile, SQLiteDbHelp.DATABASE_NAME);
        SQLiteDatabase.openOrCreateDatabase(dbFile.getAbsolutePath(), null);
    }

这个。。。
继承ContextWrapper 并重写下面的方法

public class SQLiteContext extends ContextWrapper {

    public SQLiteContext(Context base) {
        super(base);
    }


    @Override
    public File getDatabasePath(String name) {
        //        String path1= SDCardUtil.getDiskFilePath(name);
//        String dirPath=path1.replace(name,"");

        //String dirPath= SDCardUtil.getInnerSDCardPath();
        Log.d("fengxing", "getDatabasePath: " + FileUtils.getTypeFile(this, "db").getAbsolutePath());
        //       /storage/emulated/0/Android/data/com.picc.mcp.lifemobile/files/db

        String dirPath = FileUtils.getTypeFile(this, "db") + File.separator;
        Log.d("fengxing", "dirPath: " + dirPath);
        //  /storage/emulated/0/Android/data/com.picc.mcp.lifemobile/files/db/

        /**
         * 差别我就不说了
         */

        String path = null;

        File parentFile = new File(dirPath);
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        String parentPath = parentFile.getAbsolutePath();
        if (parentPath.lastIndexOf("\\/") != -1) {
            path = dirPath + File.separator + name;
        } else {
            path = dirPath + name;
        }

        File file = new File(path);

        return file;
    }

    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory) {
        return SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), factory);

    }

    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory, DatabaseErrorHandler errorHandler) {
        return SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name).getAbsolutePath(), factory, errorHandler);

    }
}

如何使用？

new SQLiteDbHelp(new SQLiteContext(context));

想要知道为什么，去看源码吧。

完整代码

public class SQLiteDbHelp extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "saved_recordings.db";
    private static final int DATABASE_VERSION = 1;
    private static final String COLUMN_COUNT = "count";


    /**
     * 创建数据库和字段
     */
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + RecorderEntry.TABLE_NAME + " (" +
                    RecorderEntry._ID + " INTEGER PRIMARY KEY," +
                    RecorderEntry.COLUMN_NAME_RECORDER_NAME + " TEXT," +
                    RecorderEntry.COLUMN_NAME_FILE_PATH + " TEXT," +
                    RecorderEntry.COLUMN_NAME_RECORDER_LENGTH + " INTEGER," +
                    RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME + " INTEGER" + ")";

    /**
     * 删除表
     */
    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + RecorderEntry.TABLE_NAME;

    /**
     * 要查询的字段
     */
    private static final String[] PROJECTION = new String[]{
            RecorderEntry._ID,
            RecorderEntry.COLUMN_NAME_FILE_PATH,
            RecorderEntry.COLUMN_NAME_RECORDER_NAME,
            RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME,
            RecorderEntry.COLUMN_NAME_RECORDER_LENGTH
    };
    String sortOrder =
            RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME + " DESC";

    public static class RecorderEntry implements BaseColumns {

        public static final String TABLE_NAME = "save_recorder";

        public static final String COLUMN_NAME_RECORDER_NAME = "recorder_name";
        public static final String COLUMN_NAME_FILE_PATH = "file_path";
        public static final String COLUMN_NAME_RECORDER_LENGTH = "recorder_length";
        public static final String COLUMN_NAME_RECORDER_ADD_TIME = "added_time";
    }

    public interface OnDataBaseChangeListener {

        void onNewDatabaseEntryAdded();

        void onDatabaseEntryRenamed();
    }


    private OnDataBaseChangeListener listener;

    private static SQLiteDatabase INSTANCE;

    private SQLiteContext mContext;

    public SQLiteDatabase getInstance() {
        if (INSTANCE == null) {
            INSTANCE = new SQLiteDbHelp(mContext).getWritableDatabase();
        }
        return INSTANCE;
    }


    public SQLiteDbHelp(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mContext = (SQLiteContext) context;

    }

    public String getDBPath() {
        return mContext.getDatabasePath(DATABASE_NAME).getAbsolutePath();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    /**
     * 当数据库需要升级时调用。实现应使用此方法删除表，添加表或执行升级到新模式版本所需的任何其他操作。
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//        db.execSQL(SQL_DELETE_ENTRIES);
//        onCreate(db);
    }

    /**
     * 当数据库需要降级时使用
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //onUpgrade(db, oldVersion, newVersion);
    }

    public void setOnDataBaseChangeListener(OnDataBaseChangeListener listener) {
        this.listener = listener;
    }


    public List<RecorderItem> getRecorderItems() {
        ArrayList<RecorderItem> list = new ArrayList<>();
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.query(RecorderEntry.TABLE_NAME, PROJECTION, null, null, null, null, null);
        while (cursor.moveToNext()) {
            RecorderItem item = new RecorderItem();
            item.setId(cursor.getLong(cursor.getColumnIndex(RecorderEntry._ID)));
            item.setLength(cursor.getLong(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_LENGTH)));
            item.setTime(cursor.getLong(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME)));
            item.setName(cursor.getString(cursor.getColumnIndex(RecorderEntry.TABLE_NAME)));
            item.setPath(cursor.getString(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_FILE_PATH)));
            cursor.close();
            list.add(item);
        }
        return list;
    }

    private static final String SELECTION = RecorderEntry._ID + "=?";

    public void getItemWithId(long id) {
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.query(RecorderEntry.TABLE_NAME, PROJECTION, SELECTION, new String[]{String.valueOf(id)}, null, null, null);
        cursor.close();
    }

    public RecorderItem getRecorderItem(int position) {
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.query(RecorderEntry.TABLE_NAME, PROJECTION, null, null, null, null, null);
        cursor.moveToFirst();
        if (cursor.moveToPosition(position)) {
            RecorderItem item = new RecorderItem();
            item.setId(cursor.getLong(cursor.getColumnIndex(RecorderEntry._ID)));
            item.setLength(cursor.getLong(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_LENGTH)));
            item.setTime(cursor.getLong(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME)));
            item.setName(cursor.getString(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_RECORDER_NAME)));
            item.setPath(cursor.getString(cursor.getColumnIndex(RecorderEntry.COLUMN_NAME_FILE_PATH)));
            cursor.close();
            return item;
        } else {
            return null;
        }
    }

    public long insertRecorder(String name, String path, long time, long length) {
        SQLiteDatabase database = getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(RecorderEntry.COLUMN_NAME_RECORDER_NAME, name);
        contentValues.put(RecorderEntry.COLUMN_NAME_FILE_PATH, path);
        contentValues.put(RecorderEntry.COLUMN_NAME_RECORDER_ADD_TIME, time);
        contentValues.put(RecorderEntry.COLUMN_NAME_RECORDER_LENGTH, length);
        long id = database.insert(RecorderEntry.TABLE_NAME, null, contentValues);
        if (listener != null) {
            listener.onNewDatabaseEntryAdded();
        }
        return id;
    }

    public void renameItem(RecorderItem item, String newName, String path) {
        SQLiteDatabase database = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(RecorderEntry.COLUMN_NAME_RECORDER_NAME, newName);
        values.put(RecorderEntry.COLUMN_NAME_FILE_PATH, path);
        database.update(RecorderEntry.TABLE_NAME, values, RecorderEntry._ID + "=?", new String[]{String.valueOf(item.getId())});
    }

    public void deleteItem(int id) {
        SQLiteDatabase database = getReadableDatabase();
        database.delete(RecorderEntry.TABLE_NAME, RecorderEntry._ID + "=?", new String[]{String.valueOf(id)});
    }

    public int getCount() {
        SQLiteDatabase database = getReadableDatabase();
        String[] projection = {RecorderEntry._ID};
        Cursor c = database.query(RecorderEntry.TABLE_NAME, projection, null, null, null, null, null);
        int count = c.getCount();
        c.close();
        return count;
    }


}

Android 数据库初窥
在Android中使用最多的就是SQLiteOpenHelper，这篇文章会从SQLiteOpenHelper入手...
Android SQLiteOpenHelper使用
官方实例代码:https://developer.android.com/training/data-storag...
greenDAO3 入门（配置，基本操作，数据库升级）
Sqlite作为android重要的数据存储库，原生类SQLiteOpenHelper使用起来繁琐容易出错，本着"...
Android ORMLite框架入门级介绍
以前使用数据库都是使用Android官方推荐的SQLiteOpenHelper，用过的都知道，比较难用。一直想找一...
数据持久化-数据库基础SQLiteOpenHelper
Android基础的SQLite使用 1. SQLiteOpenHelper 借助该类实现数据库创建与升级通过继承...
Android 笔记 —— 数据库存储
创建数据库 SQliteOpenHelper 帮助类的使用使用 SQliteOpenHelper 帮助类可以很方...
Android 使用Sqlite
如何使用继承SQLiteOpenHelper，通过SQLiteOpenHelper可以方便的管理SQLiteDa...
getWritableDatabase和getReadableD
Android中调用SQLiteOpenHelper的getWritableDatabase（）或getReada...
greenDAO数据本地化
还记得刚开始学Android那会，使用SQLiteOpenHelper来操作SQLite数据库，然后第一个APP开...
android 中 SQLiteOpenHelper的封装使用
在android中常用存储数据的基本就三种，sqlite,SharedPreferences,文件存储，其中针对于...