SQLite学习与总结（一）

最新推荐文章于 2021-10-14 23:19:36 发布

TouchOfSunJ

最新推荐文章于 2021-10-14 23:19:36 发布

阅读量352

点赞数

CC 4.0 BY-SA版权

分类专栏： android source code study 文章标签： SQLite

本文链接：https://blog.youkuaiyun.com/weixin_38699155/article/details/80234508

study 同时被 2 个专栏收录

8 篇文章

订阅专栏

android source code

3 篇文章

订阅专栏

本文介绍了SQLite的特点及优势，包括其作为嵌入式数据库的应用场景，并通过一个具体的代码示例展示了如何使用SQLite进行数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLite概念：

1、是一种嵌入式数据库，这点与（Oracal,mysql）不同，这是程序的一个组成部分

2、它与程序一起编译，不需要独立维护，简约而不简单

3、Html5,Android,ios都内嵌支持 SQLite

数据的存储的几种方案：

数据可以存储在程序之外或者数据存储在程序内部，目前比较流行的：Properties,XML,SQLite

SQLite优点：

1、在读取数据的时候可以减少网络访问的消耗，简化数据库的管理，程序的部署也比较容易

2、简单，访问速度快（最好把它看作一个本地的文件系统）

3、支持常见的sql语法：包括事务，索引，视图，触发器的部分功能

SQLite数据类型:

1、sqlite可以给每个字段定义类型，但是也可以不制定，如果字段没有类型则与Javascript一样字段属于动态类型

2、常见SQLite的数据类型

Integer:有符号的整数类型

Real：浮点型

Text: 字符串（编码取决于DB的编码）

BLOB：存储图片、等二进制信息

Boolean数据类型：sqlite默认没有，逻辑值为0代表false

SQL查询语句：

drop table if exists person;

create table if not exists person (_id integer primary key autoincrement,name text);

/*查询表结构*/

select * from sqlite_master where type = 'table';

/*查询表数据*/

select * from person;

/*增加数据*/

insert into person(name,salary) values ('小王',6000.00);

SQLite使用样例:

public class VideoDB extends SQLiteOpenHelper {

    public static final String DATABASENAME = "videodb1.db";
    private static final int VERSION = 6;
    private static VideoDB sInstance = null;

    private final Context mContext;


    public VideoDB(final Context context) {
        super(context, DATABASENAME, null, VERSION);
        mContext = context;
    }

    public static  synchronized VideoDB getInstance(final Context context) {
        if (sInstance == null) {
            sInstance = new VideoDB(context.getApplicationContext());
        }

        return sInstance;
    }



    @Override
    public void onCreate(SQLiteDatabase db) {

        DownFileStore.getInstance(mContext).onCreate(db);
    }


    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {


        DownFileStore.getInstance(mContext).onDowngrade(db, oldVersion, newVersion);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        DownFileStore.getInstance(mContext).onUpgrade(db, oldVersion, newVersion);
    }
}

/创建一个管理器进行控制多线程状态下对数据库的访问/

public class DownFileStore {


    public static String TAG = "DownFileStore";

    private static DownFileStore sInstance = null;
    private VideoDB mVideoDatabase=null;
    private SQLiteDatabase database=null;

    public DownFileStore(final Context context) {

        mVideoDatabase= VideoDB.getInstance(context);
        //database = mVideoDatabase.getWritableDatabase();
        //database.beginTransaction();

    }

    public static synchronized DownFileStore getInstance(Context context) {
        if (sInstance == null) {
            sInstance = new DownFileStore(context.getApplicationContext());
        }
        return sInstance;
    }





    public void onCreate(final SQLiteDatabase db) {

        Log.e(TAG,"数据库的创建会调用此方法");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + DownFileStoreColumns.NAME + " ("
                + DownFileStoreColumns.ID + " TEXT NOT NULL PRIMARY KEY," + DownFileStoreColumns.TOOL_SIZE + " INT NOT NULL,"
                + DownFileStoreColumns.FILE_LENGTH + " INT NOT NULL, " + DownFileStoreColumns.URL + " TEXT NOT NULL,"
                + DownFileStoreColumns.DIR + " TEXT NOT NULL," + DownFileStoreColumns.FILE_NAME + " TEXT NOT NULL,"
                + DownFileStoreColumns.ARTIST_NAME + " TEXT NOT NULL,"
                + DownFileStoreColumns.DOWNSTATUS + " INT NOT NULL);");
    }

    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
        Log.e(TAG,"数据库的更新会调用此方法");
        db.execSQL("DROP TABLE IF EXISTS " + DownFileStoreColumns.NAME);
        onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.e(TAG,"onDowngrade");
        db.execSQL("DROP TABLE IF EXISTS " + DownFileStoreColumns.NAME);
        onCreate(db);
    }

    public synchronized void insert(DownloadDBEntity entity) {
        Log.e(TAG, " id = " + entity.getDownloadId());
        final SQLiteDatabase database = mVideoDatabase.getWritableDatabase();
        database.beginTransaction();
        try {
            ContentValues values = new ContentValues(8);
            values.put(DownFileStoreColumns.ID, entity.getDownloadId());
            values.put(DownFileStoreColumns.TOOL_SIZE, entity.getTotalSize());
            values.put(DownFileStoreColumns.FILE_LENGTH, entity.getCompletedSize());
            values.put(DownFileStoreColumns.URL, entity.getUrl());
            values.put(DownFileStoreColumns.DIR, entity.getSaveDirPath());
            values.put(DownFileStoreColumns.FILE_NAME, entity.getFileName());
            values.put(DownFileStoreColumns.ARTIST_NAME, entity.getArtist());
            values.put(DownFileStoreColumns.DOWNSTATUS, entity.getDownloadStatus());
            database.replace(DownFileStoreColumns.NAME, null, values);
            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }

    public synchronized void update(DownloadDBEntity entity) {

        final SQLiteDatabase database = mVideoDatabase.getWritableDatabase();
        database.beginTransaction();
        try {
            ContentValues values = new ContentValues(6);
            values.put(DownFileStoreColumns.TOOL_SIZE, entity.getTotalSize());
            values.put(DownFileStoreColumns.FILE_LENGTH, entity.getCompletedSize());
            values.put(DownFileStoreColumns.URL, entity.getUrl());
            values.put(DownFileStoreColumns.DIR, entity.getSaveDirPath());
            values.put(DownFileStoreColumns.FILE_NAME, entity.getFileName());
            values.put(DownFileStoreColumns.DOWNSTATUS, entity.getDownloadStatus());
            database.update(DownFileStoreColumns.NAME, values, DownFileStoreColumns.ID + " = ?",
                    new String[]{entity.getDownloadId()});
            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }

    public synchronized void deleteAll() {
        final SQLiteDatabase database = mVideoDatabase.getWritableDatabase();
        database.delete(DownFileStoreColumns.NAME, null, null);
    }

    public synchronized DownloadDBEntity getDownLoadedList(String Id) {
        Cursor cursor = null;
        DownloadDBEntity entity = null;
        try {
            cursor = mVideoDatabase.getReadableDatabase().query(DownFileStoreColumns.NAME, null,
                    DownFileStoreColumns.ID + " = ?", new String[]{String.valueOf(Id)}, null, null, null);
            if (cursor == null) {
                return null;
            }

            if (cursor.moveToFirst()) {

                do {
                    entity = new DownloadDBEntity(cursor.getString(0), cursor.getInt(1), cursor.getInt(2),
                            cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(7));
                } while (cursor.moveToNext());
                return entity;
            } else return null;

        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    public synchronized ArrayList<DownloadDBEntity> getDownLoadedListAllDowning() {
        ArrayList<DownloadDBEntity> results = new ArrayList<>();

        Cursor cursor = null;
        try {
            cursor = mVideoDatabase.getReadableDatabase().query(DownFileStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {
                    if (cursor.getInt(7) != DownloadStatus.DOWNLOAD_STATUS_COMPLETED)
                        results.add(new DownloadDBEntity(cursor.getString(0), cursor.getInt(1), cursor.getInt(2),
                                cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(7)));
                } while (cursor.moveToNext());
            }

            return results;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    public synchronized String[] getDownLoadedListAllDowningIds() {
        ArrayList<String> results = new ArrayList<>();

        Cursor cursor = null;
        try {
            cursor = mVideoDatabase.getReadableDatabase().query(DownFileStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {
                    if (cursor.getInt(7) != DownloadStatus.DOWNLOAD_STATUS_COMPLETED)
                        results.add(cursor.getString(0));

                } while (cursor.moveToNext());
            }
            String[] t = new String[results.size()];
            for (int i = 0; i < results.size(); i++) {
                t[i] = results.get(i);
            }
            return t;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    public synchronized ArrayList<DownloadDBEntity> getDownLoadedListAll() {
        ArrayList<DownloadDBEntity> results = new ArrayList<>();

        Cursor cursor = null;
        try {
            cursor = mVideoDatabase.getReadableDatabase().query(DownFileStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {

                    results.add(new DownloadDBEntity(cursor.getString(0), cursor.getInt(1), cursor.getInt(2),
                            cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(7)));
                } while (cursor.moveToNext());
            }

            return results;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }


    public void closeDataBase() {

        //if (null !=mVideoDatabase
                //&& database.isOpen()){
            //database.close();
       // }
    }
}

#值得注意的是，推荐的做法是永远不要关闭数据库，所以我并没有进行关闭数据库的操作