Android Sqlite

什么是Sqlite

小型的、可嵌入、开源的关系型数据库，效率高，无数据类型，支持事务操作，程序驱动。

跨平台的磁盘文件 代码量少 api简单易用 1. sqlite数据库支持的数据类型

Integer、varchar(10)、float、double、char(10)、text

2. sql语句回顾 2.1. 创建表

create table 表名(字段名称 数据类型 约束, 字段类型，数据类型 约束...)
create table person(_id Integer primary key, name varchar(10), age Integer not null)

2.2 删除表

drop table 表名
drop table person

2.3 插入数据

insert into 表名[字段，字段] values(值1，值2）
insert into person(_id, age) values(1, 20)
insert into values(2, "zs", 30)

2.4 修改数据

update 表名 set 字段=新值 where 修改的条件
update person set name="ls", age = 20, where _id = 1

2.5 删除数据

delete from 表名 where 删除的条件
delete from person from where _id = 2

2.6 查询语句

select 字段名 from 表名 where 查询条件 group by 分组的字段 having 筛选条件 order by 排序字段
select * from person;
select _id, name from person
select * from person where _id = 1
select * from person where _id  1
select * from person where _id = 1 and age > 18
select * from person where name like "%小%"
select * from person where name like "_小%"
select * from person where name is null 
select * from person where name age between 10 and 20
select * from person where age > 18 order by _id

Sqlite 数据库创建 SQLiteOpenHelper: 帮助类 onCreate(): 创建方法 onUpdate(): 数据库升级方法 onOpen(): 打开数据库方法 1. 主界面

2. 实现MySqliteHelper并继承自SQLiteOpenHelper

public class MySqlitHelper extends SQLiteOpenHelper {
    
    public MySqlitHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }
    public MySqlitHelper(Context context) {
        super(context, Constant.DATABASE_NAME, null, Constant.DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i("tag", "--------onCreate--------");
        String sql = "create table " + Constant.TABLE_NAME + "(" + Constant._ID +
                " Integer primary key, " + Constant.NAME + " varchar(10), " + Constant.AGE +" Integer)";
        db.execSQL(sql);
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    }
    
    @Override
    public void onOpen(SQLiteDatabase db) {
        Log.i("tag:", "-----------onOpen-------------");
        super.onOpen(db);
    }
}

3. 定义DbManager类来管理数据库

public class DbManager {
    private static MySqlitHelper helper;
    public static MySqlitHelper getInstance(Context context) {
        if (helper == null) {
            helper = new MySqlitHelper(context);
        }
        return helper;
    }
    
    public static void execSQL(SQLiteDatabase db, String sql) {
        if (db != null) {
            if (sql != null && !"".equals(sql)) {
                db.execSQL(sql);
            }
        }
    }
}

4. 定义常量类来存储项目相关的常量

public class Constant {
    public static final String DATABASE_NAME = "info.db";
    public static final int DATABASE_VERSION = 1;
    public static final String TABLE_NAME = "person";
    public static final String _ID = "_id";
    public static final String NAME = "name";
    public static final String AGE = "age";
}

5. 在MainActivity中实现相关逻辑

package com.example.sqlitedemo;
import androidx.appcompat.app.AppCompatActivity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity {
    private MySqlitHelper helper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        helper = DbManager.getInstance(this);
    }
    
    public void createDb(View view) {
        
        SQLiteDatabase db = helper.getWritableDatabase();
    }
    public void click(View view) {
        switch (view.getId()) {
            case R.id.btn_insert:
                SQLiteDatabase db = helper.getWritableDatabase();
                String sql = "insert into "+Constant.TABLE_NAME+" values(1, 'zhangsan', 20)";
                DbManager.execSQL(db, sql);
                String sql2 = "inert into "+Constant.TABLE_NAME+" values(2, 'lisi', 25)";
                DbManager.execSQL(db, sql2);
                db.close();
                break;
            case R.id.btn_update:
                db = helper.getWritableDatabase();
                String updateSql = "update person set name = 'xiaoming' where _id = 1";
                DbManager.execSQL(db, updateSql);
                db.close();
                break;
            case R.id.btn_delete:
                db = helper.getWritableDatabase();
                String delSql = "delete from person where _id = 2";
                DbManager.execSQL(db, delSql);
                db.close();
                break;
        }
    }
    public void onClick(View view) {
        SQLiteDatabase db = helper.getWritableDatabase();
        switch (view.getId()) {
            case R.id.btn_insertApi:
                
                ContentValues values = new ContentValues();
                values.put(Constant._ID, 3);
                values.put(Constant.NAME, "张三");
                values.put(Constant.AGE, 30);
                long result = db.insert(Constant.TABLE_NAME, null, values);
                if (result > 0) {
                    Toast.makeText(this, "插入数据成功", Toast.LENGTH_SHORT).show();
                } else {
                    Toast.makeText(this, "插入数据失败", Toast.LENGTH_SHORT).show();
                }
            case R.id.btn_updateApi:
                
                ContentValues cv = new ContentValues();
                cv.put(Constant.NAME, "小木"); // 需要修改的字段名称，修改后的字段值
                int count = db.update(Constant.TABLE_NAME, cv, "_id=?", new String[]{"3"});
        }
    }
}

作者：Sun_lulu