SQLite数据库存储

SQLite是一款安卓内置的轻量级关系型数据库，非常适合在移动设备上使用

Android内置SQLiteOpenHelper抽象帮助类，借助整个类我们可以对数据库进行创建升级，想要使用时我们要自己创建类去继承它

此时需要重写两个方法onCreate和onUpgrade()

我们创建我们的数据库类继承SQLiteOpenHelper，手动写建表语句

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class MyDatabaseHelper extends SQLiteOpenHelper {
    public static final String CREATE_BOOK = "Create table Book("
            + "id integer primary key autoincrement,"
            + "author text,"
            + "price real,"
            + "pages integer,"
            + "name text)";

    private Context mContext;
    //
    public MyDatabaseHelper(Context context,String name,SQLiteDatabase.CursorFactory factory,
                            int version){
        super(context,name,factory,version);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        Toast.makeText(mContext,"Create successed",Toast.LENGTH_SHORT).show();
    }

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

    }
}

创建布局

    <Button
        android:id="@+id/create_datebase"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Create DateBase"
        tools:ignore="MissingConstraints" />

在MainActivity中创建

public class MainActivity extends AppCompatActivity {
    private MyDatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new MyDatabaseHelper(this,"BookStore.dp",null,1);
        Button createDateBase = findViewById(R.id.create_datebase);
        createDateBase.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });
    }
}

启动后点击按钮创建，创建成功，但是此时需要下载一个插件Database Navigator

我们点击右下角Explorer打开databases

 点开其中找到与包名对应的databases文件，其下

 

 我们右键BookStore.dp文件，点击save as保存至电脑端目录，再在左侧栏打开DB Browser

这样就可以查看数据库中内容了

连接SQLite，修改文件路径

 连接成功

 但是我们此时还有一个onUpgrade方法

想重新创建一个已存在表时，我们不希望通过先删除该表后创建这种繁琐的过程，此时我们可以使用onUpgrade方法

那么如何调用这个方法呢，sqlite中有四个参数，其中最后一个版本号version，当我们传入一个比1大的数字，那么就会自动调用该方法了

import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    private MyDatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new MyDatabaseHelper(this,"BookStore.dp",null,4);
        Button createDateBase = findViewById(R.id.create_datebase);
        createDateBase.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
                Toast.makeText(MainActivity.this,"666",Toast.LENGTH_SHORT).show();
            }
        });
    }
}

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class MyDatabaseHelper extends SQLiteOpenHelper {
    public static final String CREATE_BOOK = "Create table Book("
            + "id integer primary key autoincrement,"
            + "author text,"
            + "price real,"
            + "pages integer,"
            + "name text)";

    public static final String CREATE_CATEGORY = "Create table Category("
            + "id integer primary key autoincrement,"
            + "category_name text,"
            + "category_code integer)";

    private Context mContext;

    public MyDatabaseHelper(Context context,String name,SQLiteDatabase.CursorFactory factory,
                            int version){
        super(context,name,factory,version);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext,"Create successed",Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists Book");
        db.execSQL("drop table if exists Category");
        onCreate(db);
    }
}

注：生成了新的数据库文件之后记得回来覆盖之前的文件

---

数据的CRUD

添加数据

我们之前调用SQLiteOpenHelper的getReadableDatabase()或getWritableDatabase()方法可以用于创建和升级数据库，且这两个方法都会返回一个SQLiteDatabase的对象，借助这个对象就可以进行增删改查

        Button addData = findViewById(R.id.add_data);
        addData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                //开始组装第一条数据
                values.put("name","Just Live");
                values.put("author","wal");
                values.put("pages",200);
                values.put("price",9.9);
                //输入第一条数据
                db.insert("Book",null,values);

                //组装第二条书v就
                values.put("name","Don't fear");
                values.put("author","yourself");
                values.put("pages","800");
                values.put("price",12.8);
                //输入第二条数据
                db.insert("Book",null,values);
            }
        });

此时数据已经插入数据库中了，但是DB Browser展示的表中并不会显示，这并不影响结果使用，但如果你想要再这个展示的视图表中看到，需要重新更换文件

注：替换文件时记得断开sqlite与旧文件的连接，否则会报错，因为该旧文件正在被占用

在这里断开连接

第二种插入，使用动态的sql语句，？是占位符，会一一对应下方函数数组内的元素

        Button addData = findViewById(R.id.add_data);
        addData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String ibook = "insert into Book(id,name) values(null,?) ";
                sqLiteDatabase.execSQL(ibook,new String[]{"bb"});
            }
        });

删除数据

删除数据

        Button delete = findViewById(R.id.delete_data);
        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                db.delete("Book","pages = ? ",new String[]{"200"});
            }
        });

 成功删除数据

 

 修改数据

        Button update = findViewById(R.id.update_data);
        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("price",10.99);
                //问号是一个占位符
                db.update("Book",values,"name = ?",new String[]{"Just do it"});
            }
        });

查询数据

        Button query = findViewById(R.id.query_data);
        query.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                //查询Book表中所有数据
                Cursor cursor = db.query("Book",null,null,null,null,null,null);
                if (cursor.moveToFirst()){
                    do {
                        //遍历Cursor对象，取出数据并打印
                        String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                        String author = cursor.getString(cursor.getColumnIndexOrThrow("author"));
                        Log.d("MainActivity","name is " + name);
                        Log.d("MainActivity","author is " + author);
                    }while (cursor.moveToNext());
                }
                cursor.close();
            }
        });