关于sqlite:SQLite原理与运用

SQLite 是一个开源的关系型数据库，实现自容纳、零配置、反对事务的 SQL 数据库引擎。其特点是高度便携、使用方便、结构紧凑、高效、牢靠。并且 SQLite 是在世界上最宽泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限度。本篇文章书要是记述了 SQLite 的根本架构以及 SQLite 的几种操作形式，其中比拟重要的就是 ADB Shell 命令操作与 SQL 语句，另外在开发中还是 Litepal 这款开源 ORM 框架用的比拟多一些，应用起来的确十分不便。

<!– more –>

SQLite 根本构造

接口由 SQLite C API 组成，程序与 SQLite 交互的根底就是用 C 语言编写的 API，JDBC 也只是 JNI 调用而已。

在编译器中，词法分析器与语法分析器把 SQL 翻译为语法树，Code Generator 依据语法树生产 SQLite 的汇编代码，交给虚拟机执行。

虚拟机，与 Java 虚拟机执行 class 中的指令相似，SQLite 的汇编代码由 SQLite 虚拟机来执行，由虚拟机负责 SQL 到数据存取的交互，对于虚拟机的更多内容能够查看官网《The Virtual Database Engine of SQLite》

更多对于 SQLite 架构的内容能够查看官网《Architecture of SQLite》, 外面介绍的比拟具体。

SQLite 数据类型

Android 中操作 SQLite

SQLite 的 SQL 语句其实和一般 SQL 没什么特地的不同，Windows 下可视化操作 SQLite 能够应用 SQLite Expert Personal 4 – 这款工具，下载地址如下：http://www.sqliteexpert.com/v4/SQLiteExpertPersSetup64.exe。关上后即可通过图形化界面的形式操作 SQLite，同样也能够通过 SQL 语句来操作：

# 建表
create table stu_info (
       id integer primary key autoincrement,
       name varchar(30) not null,
       age integer ,
       gender varchar(2) not null
)

# 插入数据
insert into stu_info(name, age, gender) values ('Mike', 24, '女');
insert into stu_info(name, age, gender) values ('Jone', 26, '男');
insert into stu_info(name, age, gender) values ('Tom', 28, '女');

# 查问数据
select * from stu_info;

# 删除数据
delete from stu_info where id = 13;

# 批改数据
update stu_info set name = 'Jack' where id = 10;

# 按条件查问
select * from stu_info where age = 24; 

当初次要还是看看在 Android 平台如何应用吧！SQLiteOpenHelper：Android 平台里一个数据库辅助类，用于创立或关上数据库，并且对数据库的创立和版本进行治理。

SQLiteDatabase：用于治理和操作 SQLite 数据库，简直所有的数据库操作，最终都将由这个类实现。

ADB Shell 操作 SQLite

关上 CMD 窗口，输出 adb shell，找到 sqlite 文件，通过 sqlite3 + sqlite 文件名就能够进入 sqlite shell：

SQLiteDatabase + SQL 语句

上面这个例子是通过 SQLiteDatabase + SQL 语句来操作数据库，即须要本人手动拼接 SQL，如果是插入数据、批改数据、删除数据都是用 sqLiteDatabase.execSQL(insertSQL/updateSQL/deleteSQL) 来实现的，查问数据时应用 sqLiteDatabase.rawQuery() 办法来实现，因为应用起来须要拼接 SQL，略微麻烦一点。

main_activity.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:padding="20dp"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/et_name"
        android:hint="姓名"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>

    <EditText
        android:id="@+id/et_age"
        android:hint="年龄"
        android:inputType="number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>

    <RadioGroup
        android:id="@+id/rg_sex"
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <RadioButton
            android:id="@+id/rb_male"
            android:text="男"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>
        <RadioButton
            android:id="@+id/rb_female"
            android:text="女"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>
    </RadioGroup>

    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <Button
            android:id="@+id/btn_add"
            android:text="增加"
            android:onClick="operatorData"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>
        <Button
            android:id="@+id/btn_update"
            android:text="批改"
            android:onClick="operatorData"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>
        <Button
            android:id="@+id/btn_delete"
            android:onClick="operatorData"
            android:text="删除"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>
    </LinearLayout>
    <EditText
        android:id="@+id/et_id"
        android:hint="批改 / 删除的 ID"
        android:inputType="number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>
    <ListView
        android:id="@+id/lv_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"/>
</LinearLayout>

MainActivity.java

public class MainActivity extends AppCompatActivity {
    private static final String TAG = "MainActivity";

    private EditText etId;
    private EditText etName;
    private EditText etAge;
    private String sex;
    private SQLiteDatabase sqLiteDatabase;
    private ListView lvData;

    // 申请权限申请码
    private static final int REQUEST_EXTERNAL_STORAGE = 1001;

    // 查看权限，这种写法次要是针对比拟新的 Android6.0 及当前的版本
    public static void verifyStoragePermissions(Activity activity) {int writePermission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.WRITE_EXTERNAL_STORAGE);
        int readPermission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.READ_EXTERNAL_STORAGE);

        if (writePermission != PackageManager.PERMISSION_GRANTED
                || readPermission != PackageManager.PERMISSION_GRANTED) {
            // 如果没有权限须要动静地去申请权限
            ActivityCompat.requestPermissions(
                    activity,
                    // 权限数组
                    new String[]{Manifest.permission.READ_EXTERNAL_STORAGE, Manifest.permission.WRITE_EXTERNAL_STORAGE},
                    // 权限申请码
                    REQUEST_EXTERNAL_STORAGE
            );
        }
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        etId = findViewById(R.id.et_id);
        etName = findViewById(R.id.et_name);
        etAge = findViewById(R.id.et_age);

        // 单选框组件
        RadioGroup rgSex = findViewById(R.id.rg_sex);
        lvData = findViewById(R.id.lv_data);
        verifyStoragePermissions(this);
        /*
         * 结构参数：* 1、上下文
         * 2、数据库名称，默认地位利用的公有目录（外部存储的 database 文件夹）* 3、CursorFactory 类型
         * 4、数据库版本
         */
        String path = Environment.getExternalStorageDirectory() + "/sqlite_demo.db";
        SQLiteOpenHelper helper = new SQLiteOpenHelper(this, path, null, 1){
            // 创立数据库
            @Override
            public void onCreate(SQLiteDatabase db) {Toast.makeText(MainActivity.this, "数据库创立", Toast.LENGTH_SHORT).show();
                // 如果当时没有数据库的话，创立表的操作就能够在这里进行
                db.execSQL("create table stu_info (id integer primary key autoincrement, name varchar(30) not null, age integer,gender varchar(2) not null)");
            }
            // 版本号变动之后会调用这个办法
            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {Toast.makeText(MainActivity.this, "数据库降级", Toast.LENGTH_SHORT).show();}
        };
        // 获取数据库对象
        sqLiteDatabase = helper.getWritableDatabase();
        // 设置单选框的监听
        rgSex.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(RadioGroup group, int checkedId) {switch (checkedId){
                    case R.id.rb_female:
                        sex = "女";
                        break;
                    case R.id.rb_male:
                        sex = "男";
                        break;
                }
            }
        });
        flushStuData();}

    private void flushStuData() {List<StudentInfo> stuList = new ArrayList<>();
        String selectSQL = "select * from stu_info";
        Cursor cursor = sqLiteDatabase.rawQuery(selectSQL, new String[]{});
        cursor.moveToFirst();
        while (!cursor.isAfterLast()){int id = cursor.getInt(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            String sex = cursor.getString(3);
            stuList.add(new StudentInfo(id, name, age, sex));
            cursor.moveToNext();}
        cursor.close();
        lvData.setAdapter(new StuInfoAdapter(this, stuList));
    }


    public void operatorData(View view) {int viewId = view.getId();
        switch (viewId) {
            case R.id.btn_add:
                if(TextUtils.isEmpty(sex)) {Toast.makeText(MainActivity.this, "请抉择性别", Toast.LENGTH_SHORT).show();
                    return;
                }
                String insertSQL = String.format(Locale.CHINA,"insert into stu_info(name, age, gender) values ('%s', %d,'%s')",
                        etName.getText().toString(),
                        Integer.parseInt(etAge.getText().toString()),
                        sex);
                Log.i(TAG, "operatorData: insertSQL =" + insertSQL);
                sqLiteDatabase.execSQL(insertSQL);
                // 刷新数据展现
                flushStuData();
                Toast.makeText(MainActivity.this, "增加胜利", Toast.LENGTH_SHORT).show();
                break;
            case R.id.btn_update:
                String idStr = etId.getText().toString();
                if(TextUtils.isEmpty(idStr)){Toast.makeText(MainActivity.this, "请输出 ID", Toast.LENGTH_SHORT).show();
                    return;
                }
                int id = Integer.parseInt(idStr);
                String updateSQL = String.format(Locale.CHINA,
                        "update stu_info set name ='%s', age=%d, gender='%s'where id = %d",
                        etName.getText().toString(),
                        Integer.parseInt(etAge.getText().toString()),
                        sex, id);
                Log.i(TAG, "operatorData: updateSQL =" + updateSQL);
                sqLiteDatabase.execSQL(updateSQL);
                // 刷新数据展现
                flushStuData();
                Toast.makeText(MainActivity.this, "更新胜利", Toast.LENGTH_SHORT).show();
                break;
            case R.id.btn_delete:
                String deleteIdStr = etId.getText().toString();
                if(TextUtils.isEmpty(deleteIdStr)){Toast.makeText(MainActivity.this, "请输出 ID", Toast.LENGTH_SHORT).show();
                    return;
                }
                String deleteSQL = String.format(Locale.CHINA, "delete from stu_info where id = %d", Integer.parseInt(deleteIdStr));
                Log.i(TAG, "operatorData: deleteSQL =" + deleteSQL);
                sqLiteDatabase.execSQL(deleteSQL);
                // 刷新数据展现
                flushStuData();
                Toast.makeText(MainActivity.this, "删除胜利", Toast.LENGTH_SHORT).show();
                break;
        }
    }
}

class StudentInfo {
    public int id;
    public String name;
    public int age;
    public String sex;

    public StudentInfo() {}

    public StudentInfo(int id, String name, int age, String sex) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
}

StuInfoAdapter.java

public class StuInfoAdapter extends BaseAdapter {
    private List<StudentInfo> stuList;
    private Activity context;

    public StuInfoAdapter(Activity context, List<StudentInfo> stuList) {
        this.stuList = stuList;
        this.context = context;
    }

    @Override
    public int getCount() {return stuList.size();
    }

    @Override
    public Object getItem(int position) {return stuList.get(position);
    }

    @Override
    public long getItemId(int position) {return position;}

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        ViewHolder viewHolder;
        if(convertView == null){LayoutInflater inflater = context.getLayoutInflater();
            convertView = inflater.inflate(R.layout.stu_item, null);
            viewHolder = new ViewHolder();
            viewHolder.tvName = convertView.findViewById(R.id.tv_item_name);
            viewHolder.tvId = convertView.findViewById(R.id.tv_item_id);
            viewHolder.tvAge = convertView.findViewById(R.id.tv_item_age);
            viewHolder.tvSex = convertView.findViewById(R.id.tv_item_sex);
            convertView.setTag(viewHolder);
        }else {viewHolder = (ViewHolder) convertView.getTag();}
        StudentInfo studentInfo = stuList.get(position);
        viewHolder.tvId.setText(String.valueOf(studentInfo.id));
        viewHolder.tvName.setText(studentInfo.name);
        viewHolder.tvSex.setText(studentInfo.sex);
        viewHolder.tvAge.setText(String.valueOf(studentInfo.age));
        return convertView;
    }

    // ViewHolder
    static class ViewHolder{
        TextView tvId;
        TextView tvName;
        TextView tvSex;
        TextView tvAge;
    }
}

最好不要遗记申明读写的权限（以及在 Android6.0 当前的动静权限申请），最初成果展现如下：

在 Android 中应用 SQLiteDatabase 的静态方法 openOrCreateDatabase(String path，SQLiteDatabae.CursorFactory factory) 关上或者创立一个数据库。它会主动去检测是否存在这个数据库，如果存在则关上，不存在则创立一个数据库；创立胜利则返回一个 SQLiteDatabase 对象，否则抛出异样 FileNotFoundException。

还有须要留神的就是，查问数据返回的后果是 Cursor，当咱们应用 SQLiteDatabase.query() 办法时，会失去一个 Cursor 对象，Cursor 指向的就是每一条数据。它提供了很多无关查问的办法，具体方法如下：

这个能够参考在代码示例中 flushStuData() 办法中的应用：

private void flushStuData() {List<StudentInfo> stuList = new ArrayList<>();
    // 参数解释：表名、要查问的字段、列条件、列条件参数、GroupBy、having、orderBy
    Cursor cursor = sqLiteDatabase.query("stu_info", null, null, null, null, null, null);
    if(cursor.moveToFirst()){
        do{int id = cursor.getInt(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            String sex = cursor.getString(3);
            stuList.add(new StudentInfo(id, name, age, sex));
        } while (cursor.moveToNext());
    }
    cursor.close();
    lvData.setAdapter(new StuInfoAdapter(this, stuList));
}

通过 API 来操作数据库

SQLiteDatabase 也提供了 insert()、delete()、update()、query() 办法专门用于插入、删除、更新和查问，通过这种 API 的操作形式就须要编写 SQL 语句了，只须要传入对应的参数，即可实现 CRUD 操作。还是通过下面的例子，改变的中央无非就是操作数据的局部而已：

// ...

// 查问
private void flushStuData() {List<StudentInfo> stuList = new ArrayList<>();
    // 参数解释：表名、要查问的字段、列条件、列条件参数、GroupBy、having、orderBy
    Cursor cursor = sqLiteDatabase.query("stu_info", null, null, null, null, null, null);
    if(cursor.moveToFirst()){
        do{int id = cursor.getInt(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            String sex = cursor.getString(3);
            stuList.add(new StudentInfo(id, name, age, sex));
        } while (cursor.moveToNext());
    }
    cursor.close();
    lvData.setAdapter(new StuInfoAdapter(this, stuList));
}


public void operatorData(View view) {int viewId = view.getId();
    switch (viewId) {
        // 增加
        case R.id.btn_add:
            // 参数解释：操作表的名称、能够为空的列、参数
            ContentValues values = new ContentValues();
            // Key - value
            values.put("name", etName.getText().toString());
            values.put("age", Integer.parseInt(etAge.getText().toString()));
            values.put("gender", sex);
            // 插入胜利，返回数据的 ID
            long infoId = sqLiteDatabase.insert("stu_info", null, values);
            // 刷新数据展现
            flushStuData();
            Toast.makeText(MainActivity.this, "增加胜利，Id =" + infoId, Toast.LENGTH_SHORT).show();
            break;
        // 更新
        case R.id.btn_update:
            String idStr = etId.getText().toString();
            ContentValues updateValues = new ContentValues();
            // Key - value
            updateValues.put("name", etName.getText().toString());
            updateValues.put("age", Integer.parseInt(etAge.getText().toString()));
            updateValues.put("gender", sex);
            int info = sqLiteDatabase.update("stu_info", updateValues, "id=?", new String[]{idStr});
            // 刷新数据展现
            flushStuData();
            Toast.makeText(MainActivity.this, "更新胜利，影响行数：" + info, Toast.LENGTH_SHORT).show();
            break;
        // 删除
        case R.id.btn_delete:
            String deleteIdStr = etId.getText().toString();
            int delete = sqLiteDatabase.delete("stu_info", "id=?", new String[]{deleteIdStr});
            // 刷新数据展现
            flushStuData();
            Toast.makeText(MainActivity.this, "删除胜利，影响行数：" + delete, Toast.LENGTH_SHORT).show();
            break;
    }
}

// ...

应用 LitePal 操作 SQLite

下面应用 SQLiteDatabase 来操作 SQLite 数据库的办法，应用起来真的很不不便，像我这种习惯应用 ORM 框架的人来说，SQLiteDatabase 的操作形式几乎太过于简单，所以当初来看看 Litepal 这款开源框架吧，应用完过后本人也来尝试造一轮子，能够参考：https://github.com/huyongli/T…

环境搭建

首先引入依赖：

implementation 'org.litepal.android:core:1.4.1'

接下来须要配置 litepal.xml 文件。右击 app/src/main 目录 —>New—>Directory，创立一个 assets 目录，在 assets 目录下新建 litepal.xml 文件：

<?xml version="1.0" encoding="utf-8" ?>
<litepal>
    <!-- 指定数据库名称 -->
    <dbname value="BookStore"/>
    <!-- 指定数据库版本号 -->
    <version value="1"/>
    <!-- 指定映射模型 -->
    <list></list>
</litepal>

最初还须要批改一下 AndroidManifest.xml 中的代码：

<!-- 要害就这一句：android:name="org.litepal.LitePalApplication" -->
<application
    android:name="org.litepal.LitePalApplication"
    android:allowBackup="true"
    android:icon="@mipmap/ic_launcher"
    android:label="@string/app_name"
    android:roundIcon="@mipmap/ic_launcher_round"
    android:supportsRtl="true"
    android:theme="@style/AppTheme">
    <activity android:name=".MainActivity">
        <intent-filter>
            <action android:name="android.intent.action.MAIN" />
            <category android:name="android.intent.category.LAUNCHER" />
        </intent-filter>
    </activity>
</application>

当初 Litepal 的配置工作曾经做完了，让咱们开始正式应用它吧！

建库建表

当初开始申明一个 JavaBean，也就是咱们要存储的数据：

public class Book {
    private int id;
    private String name;
    private String author;

    public Book(){}

    public Book(int id, String author, String name) {
        this.id = id;
        this.author = author;
        this.name = name;
    }
    // Getter and Setter ...
}

并且在配置文件中配置它的映射模型：

<?xml version="1.0" encoding="utf-8" ?>
<litepal>
    <!-- 指定数据库名称 -->
    <dbname value="BookStore"/>
    <!-- 指定数据库版本号 -->
    <version value="1"/>
    <!-- 指定映射模型 -->
    <list>
        <mapping class="cn.tim.litepal_demo.Book"/>
    </list>
</litepal>

在 Activity 启动时创立数据库：

// 创立数据库
SQLiteDatabase database = LitePal.getDatabase();

尽管有三张表，其中 android_matedata 表依然不必管，table_schema 表是 litepal 外部应用的，也能够间接漠视，Book 表就是依据配置的 Book 类主动生成的表，是不是很不便？

数据库降级

而且 Litepal 很好的解决了数据库降级问题，应用 SQLiteOpenHelper 来降级数据库的形式：降级数据库的时候咱们须要先把之前的表 drop 掉，而后再从新创立才行。这其实是一个十分重大的问题，因为这样会造成数据失落，每当降级一次数据库，之前表中的数据就全没了。尽管能够通过简单的逻辑管制来防止这种状况，然而保护老本很高。而有了 LitePal, 这些就都不再是问题了，应用 LitePal 来降级数据库十分非常简单，你齐全不必思考任何的逻辑，只须要改你想改的任何内容，而后将版本号加 1 就行了。

比方，将图书表的中再增加一个价格的字段，再新建一张分类表：

public class Book {
    private int id;
    private String name;
    private String author;
    private int price;
    
    // ...
}

public class Category {
    private int id;
    private String name;
    private long count;
    
    // ...
}

同样须要配置映射模型：

<?xml version="1.0" encoding="utf-8" ?>
<litepal>
    <!-- 指定数据库名称 -->
    <dbname value="BookStore"/>
    <!-- 指定数据库版本号：此时数据库版本应该为 2 -->
    <version value="2"/>
    <!-- 指定映射模型 -->
    <list>
        <mapping class="cn.tim.litepal_demo.Book"/>
        <mapping class="cn.tim.litepal_demo.Category"/>
    </list>
</litepal>

由此可见，book 表中新增了一个 price 列，并且新创建了 category 表。

CRUD 操作

上面来看看应用 Litepal 来对数据进行 CRUD 是如许不便吧：

首先须要让数据模型对象，也就是定义的 Javabean 来继承 DataSupport

public class Book extends DataSupport {...}

上面间接通过查看 log 日志的形式来验证 LitePal 框架的 CRUD：

public class MainActivity extends AppCompatActivity {
    private static final String TAG = "MainActivity";
    @Override
    protected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // 创立数据库
        SQLiteDatabase database = LitePal.getDatabase();

        // 增加数据
        Book book = new Book("Think In Java", "Tim", 58);
        boolean saveRet = book.save();
        Log.i(TAG, "onCreate: saveRet =" + saveRet);
        new Book("Think In C/C++", "Tom", 38).save();
        Log.i(TAG, "onCreate: 增加数据胜利");

        // 查问数据
        List<Book> bookList = DataSupport.findAll(Book.class);
        Book[] books = new Book[bookList.size()];
        bookList.toArray(books);
        Log.i(TAG, "onCreate: books =" + Arrays.toString(books));

        // 删除数据
        int delete = DataSupport.delete(Book.class, books[0].getId());
        Log.i(TAG, "onCreate: 删除数据胜利，delete =" + delete);
        
        // 查问数据
        bookList = DataSupport.findAll(Book.class);
        books = new Book[bookList.size()];
        bookList.toArray(books);
        Log.i(TAG, "onCreate: books =" + Arrays.toString(books));

        // 批改数据
        Book cppBook = new Book("Think In C/C++", "Tom", 28);
        int update = cppBook.update(2);
        Log.i(TAG, "onCreate: 批改数据胜利，update =" + update);

        // 查问数据
        bookList = DataSupport.findAll(Book.class);
        books = new Book[bookList.size()];
        bookList.toArray(books);
        Log.i(TAG, "onCreate: books =" + Arrays.toString(books));
    }
}

下面演示了具体的操作，其实还有很多高级查询方法，这里不再赘述，如果用到能够参考作者的博客《Android 数据库高手秘籍》。

博客原文：https://zouchanglin.cn/2020/1…