SQLite详解

最新推荐文章于 2024-02-26 21:00:00 发布

weixin_34144450

最新推荐文章于 2024-02-26 21:00:00 发布

阅读量268

点赞数 1

文章标签：数据库移动开发嵌入式

原文链接：https://juejin.im/post/5a420ff2f265da431b6d7518

版权

微信WCDB

一、基本用法

1.1 SQL语句基本用法

参考网站

//查询数据
SELECT * FROM tableName 

SELECT * FROM tableName [WHERE _ID = 1 AND first_name = 'Liming'] [ORDER BY _ID, first_name] [DESC/ASC]
SELECT COUNT(*) FROM tableName [WHERE first_name LIKE 'a%']
SELECT * FROM tableName [WHERE first_name BETWEEN 'Liming' AND 'Lining'
SELECT * FROM tableName [WHERE first_name IN ('Liming', 'Lining', 'Lily')]
SELECT * FROM tableName [GROUPBY _ID HAVING COUNT(*) > 2]
//删除
DELETE FROM tableName [WHERE _ID > 10]
//更新

复制代码

1.2 ACID

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.3 SQLite数据

.db:数据库文件

.journal:数据库事务文件。用于记录数据库操作过程中产生的事务，以便回滚。一般而言在事务产生时创建该文件，事务结束时删除该文件。但在Android操作系统中，为了避免重复创建文件带来的性能消耗，该文件一致存在。

1.4 SQLITE3 WAL模式

sqlite是支持write ahead logging(WAL)模式的，开启WAL模式可以提高写入数据库的速度，读和写之间不会阻塞，但是写与写之间依然是阻塞的，但是如果使用默认的TRUNCATE模式，当写入数据时会阻塞Android中其他线程或者进程的读操作，并发降低。相反，使用WAL可以提高并发。由于使用WAL比ROLLBACK JOURNAL的模式减少了写的I/O，所以写入时速度较快，但是由于在读取数据时也需要读取WAL日志验证数据的正确性，所以读取数据相对要慢。所以大家也要根据自己应用的场景去使用这种模式。

db.enableWriteAheadLogging即可开启WAL模式

参考-SQLite的WAL机制
 Android使用SQLITE3 WAL模式

1.5 SQLite Cipher

SQLCipher 使用 AES-256 进行全数据库加密，包括文件头以及 Journal/WA

1.5 SQLiteOpenHelper基本用法

public class ProviderSQLiteHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "provider.db";
    private static final int DB_VERSION = 1;
    public static final String BOOK_TABLE_NAME = "books";
    public static final String USER_TABLE_NAME = "users";
    private static final String CREATE_BOOKS_TABLE = "CREATE TABLE IF NOT EXISTS " + BOOK_TABLE_NAME +
            "( _ID INTEGER PRIMARY KEY, " +
            "  NAME TEXT)";

    private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + USER_TABLE_NAME +
            "(_ID INTEGER PRIMARY KEY, " +
            " NAME TEXT)";

    //创建数据库
    public ProviderSQLiteHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    //创建表
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOKS_TABLE);
        db.execSQL(CREATE_USER_TABLE);
    }

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

//创建数据库对象
ProviderSQLiteHelper db = new ProviderSQLiteHelper(this.getContext()).getWritableDatabase();

//查询
//public Cursor query(String table, String[] columns, String selection,
//            String[] selectionArgs, String groupBy, String having,
//            String orderBy)
//SELECT (columns) FROM table WHERE selection = selectionArgs GROUPBY groupBy HAVING having
db.query(tableName, projection, selection, selectionArgs, null, null, sortOrder)


//添加数据
//public long insert(String table, String nullColumnHack, ContentValues values)
//INSERT * [INTO] tableName [columnNames] (values) 
ContentValues values = new ContentValues();
contentValues.put("_ID", 7);
contentValues.put("first_name", "Jne");
db.insert(tableName, null, values)

db.execSQL("insert into " + tableName + " (_ID, NAME) " + "values" + " (1, 'Liming')")

//更新数据
//public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
//UPDATE tableName SET fist_name = 'LiNing' where selection = selectionArgs
db.update(tableName, values, "_ID=? AND first_name=?", whereArgs)
db.execSQL("update ")

//删除数据
//db.delete(tableName, selection, selectionArgs)
//DELETE [FROM] tableName WHERE selection = selectionArgs
db.execSQL()

复制代码

二、db.execSQL与db.insert/delete/update的区别

函数原型

public void execSQL(String sql) throws SQLException
public long insert(String table, String nullColumnHack, ContentValues values)
public long insertOrThrow(String table, String nullColumnHack, ContentValues values) throws SQLException
public int delete(String table, String whereClause, String[] whereArgs)
public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
...
复制代码

db.execSQL不返回任何数据，因此不可用来查询数据

Android为什么采用了SQLite，而不使用其他诸如MySQL、PostgreSQL等开源数据库呢？

SQLite是一款轻型数据库，它遵守ACID，能够嵌入到使用它的应用程序中。作为一个自包含的、基于文件的数据库，SQLite提供了非常出色的工具集能够处理所有类型的数据，与托管在服务器上基于进程的关系型数据库相比它的约束更少，也更易用。

当应用程序使用SQLite时，SQLite并非作为一个独立进程通过某种通信协议（例如socket）与应用程序通信，而是作为应用程序的一部分，应用程序通过调用SQLite的接口直接访问数据文件。

SQLite支持的数据类型包括：NULL、INTEGER、REAL、TEXT、BLOB。

2.1 SQLite的优点

基于文件 整个数据库完全由磁盘上的一个文件构成，这使得它的可移植性非常好。
标准化 尽管它看起来像一个“简化版”的数据库实现，但是SQLite确实支持SQL。它省略了一些功能（RIGHT OUTER JOIN和FOR EACH STATEMENT），但同时也增加了一些额外的功能。
非常适合开发甚至是测试 在大多数应用程序的开发阶段，大部分开发人员可能都非常需要一个能够支持并发扩展的解决方案。SQLite 包含丰富的功能，所能提供的特性超乎开发所需，使用起来也非常简洁——只需要一个文件和一个C链接库。

2.2 SQLite的缺点

没有用户管理 高级数据库都支持用户系统，例如管理连接对数据库和表的访问权限。鉴于SQLite的目的和性质（没有多客户端并发的高层设计），它并不包含这些功能。
缺少通过优化获得额外性能的空间 还是由于设计方面的原因，无法通过优化SQLite获得大量的额外性能。这个类库非常容易调整、也非常容易使用。它并不复杂，所以从技术上无法让它变得更快，因为它已经很快了。

何时应该使用SQLite

嵌入式应用程序 所有需要可移植性、不需要扩展的应用程序，例如单用户的本地应用、移动应用或者游戏。
替代磁盘访问 在很多情况下，需要直接读写磁盘文件的应用程序可以切换到SQLite从而受益于SQLite提供的额外功能以及使用结构化查询语言（SQL）所带来的简便性。

2.3 何时不应该使用SQLite

多用户应用程序 如果有多个客户端需要访问并使用同一个数据库，那么最好使用功能完整的关系型数据库（例如MySQL），而不是选择SQLite。
需要高写入量的应用程序 写操作是SQLite的一个局限。该DBMS在同一时刻仅允许一个写操作，因而也限制了其吞吐量。

三、SQLite使用优化

3.1 更优性能

Sqlite默认会为每个插入、更新操作创建一个事务，并且在每次插入、更新后立即提交。

这样如果连续插入100次数据实际是创建事务->执行语句->提交这个过程被重复执行了100次。如果我们显示的创建事务->执行100条语句->提交会使得这个创建事务和提交这个过程只做一次，通过这种一次性事务可以使得性能大幅提升。尤其当数据库位于sd卡时，时间上能节省两个数量级左右。 Sqlte显示使用事务，示例代码如下：

public void insertWithOneTransaction() {
    SQLiteDatabase db = sqliteOpenHelper.getWritableDatabase();
    // Begins a transaction
    db.beginTransaction();
    try {
        // your sqls
        for (int i = 0; i < 100; i++) {
            db.insert(yourTableName, null, value);
        }

        // marks the current transaction as successful
        db.setTransactionSuccessful();
    } catch (Exception e) {
        // process it
        e.printStackTrace();
    } finally {
        // end a transaction
        db.endTransaction();
    }
}
复制代码

其中sqliteOpenHelper.getWritableDatabase()表示得到写表权限。

3.2 其他针对Sqlite的优化

(1) 语句的拼接使用StringBuilder代替String

这个就不多说了，简单的string相加会导致创建多个临时对象消耗性能。StringBuilder的空间预分配性能好得多。如果你对字符串的长度有大致了解，如100字符左右，可以直接new StringBuilder(128)指定初始大小，减少空间不够时的再次分配。

(2) 查询时返回更少的结果集及更少的字段。

查询时只取需要的字段和结果集，更多的结果集会消耗更多的时间及内存，更多的字段会导致更多的内存消耗。

(3) 少用cursor.getColumnIndex

根据性能调优过程中的观察cursor.getColumnIndex的时间消耗跟cursor.getInt相差无几。可以在建表的时候用static变量记住某列的index，直接调用相应index而不是每次查询。

public static final String       HTTP_RESPONSE_TABLE_ID                  = android.provider.BaseColumns._ID;
public static final String       HTTP_RESPONSE_TABLE_RESPONSE            = "response";
public List<Object> getData() {
	……
	cursor.getString(cursor.getColumnIndex(HTTP_RESPONSE_TABLE_RESPONSE));
	……
}
复制代码

优化为

public static final String       HTTP_RESPONSE_TABLE_ID                  = android.provider.BaseColumns._ID;
public static final String       HTTP_RESPONSE_TABLE_RESPONSE            = "response";
public static final int          HTTP_RESPONSE_TABLE_ID_INDEX            = 0;
public static final int          HTTP_RESPONSE_TABLE_URL_INDEX           = 1;
public List<Object> getData() {
	……
	cursor.getString(HTTP_RESPONSE_TABLE_RESPONSE_INDEX);
	……
}
复制代码

(4) 异步线程

Sqlite是常用于嵌入式开发中的关系型数据库，完全开源。与Web常用的数据库Mysql、Oracle db、sql server不同，Sqlite是一个内嵌式的数据库，数据库服务器就在你的程序中，无需网络配置和管理，数据库服务器端和客户端运行在同一进程内，减少了网络访问的消耗，简化了数据库管理。不过Sqlite在并发、数据库大小、网络方面存在局限性，并且为表级锁，所以也没必要多线程操作。

Android中数据不多时表查询可能耗时不多，不会导致anr，不过大于100ms时同样会让用户感觉到延时和卡顿，可以放在线程中运行，但sqlite在并发方面存在局限，多线程控制较麻烦，这时候可使用单线程池，在任务中执行db操作，通过handler返回结果和ui线程交互，既不会影响UI线程，同时也能防止并发带来的异常。可使用Android提供的AsyncQueryHandler(感谢@内网无法登陆账号反馈)或类似如下代码完成：

ExecutorService singleThreadExecutor = Executors.newSingleThreadExecutor();
singleThreadExecutor.execute(new Runnable() {

	@Override
	public void run() {
		// db operetions, u can use handler to send message after
		db.insert(yourTableName, null, value);
		handler.sendEmptyMessage(xx);
	}
});
复制代码

转载于:https://juejin.im/post/5a420ff2f265da431b6d7518

weixin_34144450

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQLite详解

微信WCDB一、基本用法1.1 SQL语句基本用法参考网站//查询数据SELECT * FROM tableName SELECT * FROM tableName [WHERE _ID = 1 AND first_name = 'Liming'] [ORDER BY _ID, first_name] [DESC/ASC]SELECT COUNT(*) FROM tableName...
复制链接

扫一扫