7-1.Android SQLite 之 SQLiteDatabase 简单编码模板（SQLiteDatabase 使用、SQL 语句编写）

一、SQLiteDatabase

1. SQLite 是一种轻量级的数据库引擎，它非常适合在移动设备（例如，Android）上使用

2. SQLiteDatabase 允许应用程序与 SQLite 数据库进行交互，它提供了增删改查等一系列方法

---

二、SQLiteDatabase 简单编码

1、Application

• MyApplication.javas

package com.my.database.application;import android.app.Application;
import android.content.Context;public class MyApplication extends Application {public static final String TAG = MyApplication.class.getSimpleName();private static Context context;@Overridepublic void onCreate() {super.onCreate();context = this;}public static Context getContext() {return context;}
}

2、Note

• Note.java

package com.my.database.entity;public class Note {private int id;private String title;private String content;public Note(int id, String title, String content) {this.id = id;this.title = title;this.content = content;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}@Overridepublic String toString() {return "Note{" +"id=" + id +", title='" + title + '\'' +", content='" + content + '\'' +'}';}
}

3、Database

• MyDatabaseManager.java

package com.my.database.mydatabase;import android.annotation.SuppressLint;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;import com.my.database.application.MyApplication;
import com.my.database.entity.Note;import java.util.ArrayList;
import java.util.List;public class MyDatabaseManager {public static final String TAG = MyDatabaseManager.class.getSimpleName();private SQLiteDatabase sqLiteDatabase;private static MyDatabaseManager myDatabaseManager;private static final String DATABASE_NAME = "test.db";private static final String TABLE_NAME = "Note";private static final String COLUMN_ID = "id";private static final String COLUMN_TITLE = "title";private static final String COLUMN_CONTENT = "content";private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+ COLUMN_TITLE + " TEXT, "+ COLUMN_CONTENT + " TEXT);";private MyDatabaseManager() {sqLiteDatabase = SQLiteDatabase.openOrCreateDatabase(MyApplication.getContext().getDatabasePath(DATABASE_NAME).toString(), null);sqLiteDatabase.execSQL(CREATE_TABLE);}public static MyDatabaseManager getInstance() {if (myDatabaseManager == null) myDatabaseManager = new MyDatabaseManager();return myDatabaseManager;}public void insert(Note note) {sqLiteDatabase.execSQL("INSERT INTO " + TABLE_NAME + " (" + COLUMN_TITLE + ", " + COLUMN_CONTENT + ") VALUES (?, ?);",new Object[]{note.getTitle(), note.getContent()});}public void delete(int id) {sqLiteDatabase.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_ID + " = ?;", new Object[]{id});}public void update(Note note) {sqLiteDatabase.execSQL("UPDATE " + TABLE_NAME + " SET " + COLUMN_TITLE + " = ?, " + COLUMN_CONTENT + " = ? WHERE " + COLUMN_ID + " = ?;",new Object[]{note.getTitle(), note.getContent(), note.getId()});}@SuppressLint("Range")public List<Note> queryAll() {Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM " + TABLE_NAME + ";", null);if (cursor.moveToFirst()) {List<Note> notes = new ArrayList<>();do {Note note = new Note(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)),cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)),cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT)));notes.add(note);} while (cursor.moveToNext());return notes;}return null;}
}

4、Activity Layout

• activity_note.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"xmlns:app="http://schemas.android.com/apk/res-auto"xmlns:tools="http://schemas.android.com/tools"android:layout_width="match_parent"android:layout_height="match_parent"tools:context=".NoteActivity"><Buttonandroid:id="@+id/btn_insert"android:layout_width="wrap_content"android:layout_height="wrap_content"android:text="增"app:layout_constraintEnd_toEndOf="parent"app:layout_constraintStart_toStartOf="parent"app:layout_constraintTop_toTopOf="parent" /><Buttonandroid:id="@+id/btn_delete"android:layout_width="wrap_content"android:layout_height="wrap_content"android:text="删"app:layout_constraintEnd_toEndOf="parent"app:layout_constraintStart_toStartOf="parent"app:layout_constraintTop_toBottomOf="@+id/btn_insert" /><Buttonandroid:id="@+id/btn_update"android:layout_width="wrap_content"android:layout_height="wrap_content"android:text="改"app:layout_constraintEnd_toEndOf="parent"app:layout_constraintStart_toStartOf="parent"app:layout_constraintTop_toBottomOf="@+id/btn_delete" /><Buttonandroid:id="@+id/btn_query_all"android:layout_width="wrap_content"android:layout_height="wrap_content"android:text="查"app:layout_constraintEnd_toEndOf="parent"app:layout_constraintStart_toStartOf="parent"app:layout_constraintTop_toBottomOf="@+id/btn_update" />
</androidx.constraintlayout.widget.ConstraintLayout>

5、Activity Code

• NoteActivity.java

package com.my.database;import androidx.appcompat.app.AppCompatActivity;import android.os.Bundle;
import android.util.Log;
import android.widget.Button;import com.my.database.entity.Note;
import com.my.database.mydatabase.MyDatabaseManager;import java.util.List;public class NoteActivity extends AppCompatActivity {public static final String TAG = NoteActivity.class.getSimpleName();private MyDatabaseManager myDatabaseManager;private Button btnInsert;private Button btnDelete;private Button btnUpdate;private Button btnQueryAll;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_note);myDatabaseManager = MyDatabaseManager.getInstance();btnInsert = findViewById(R.id.btn_insert);btnDelete = findViewById(R.id.btn_delete);btnUpdate = findViewById(R.id.btn_update);btnQueryAll = findViewById(R.id.btn_query_all);btnInsert.setOnClickListener(v -> {Note note1 = new Note(0, "A", "AAA");Note note2 = new Note(0, "B", "BBB");myDatabaseManager.insert(note1);myDatabaseManager.insert(note2);Log.i(TAG, "------------------------------ insert ok");});btnDelete.setOnClickListener(v -> {myDatabaseManager.delete(1);myDatabaseManager.delete(2);Log.i(TAG, "------------------------------ delete ok");});btnUpdate.setOnClickListener(v -> {Note note1 = new Note(0, "A+", "AAA+");Note note2 = new Note(0, "B+", "BBB+");myDatabaseManager.update(note1);myDatabaseManager.update(note2);Log.i(TAG, "------------------------------ update ok");});btnQueryAll.setOnClickListener(v -> {List<Note> notes = myDatabaseManager.queryAll();if (notes == null) {Log.i(TAG, "------------------------------ queryAll - notes is null");return;}if (notes.size() == 0) {Log.i(TAG, "------------------------------ queryAll - notes is empty");return;}for (Note note : notes) Log.i(TAG, "------------------------------ queryAll - " + note);});}
}

Test

• 增 -> 改 -> 查 -> 删 -> 查，输出结果

I/NoteActivity: ------------------------------ insert ok
I/NoteActivity: ------------------------------ update ok
I/NoteActivity: ------------------------------ queryAll - Note{id=1, title='A', content='AAA'}
I/NoteActivity: ------------------------------ queryAll - Note{id=2, title='B', content='BBB'}
I/NoteActivity: ------------------------------ delete ok
I/NoteActivity: ------------------------------ queryAll - notes is null

---

三、SQLiteDatabase 简单编码案例解析

1、数据库结构

1. 数据库文件名为 test.db

private static final String DATABASE_NAME = "test.db";

2. 数据库中有 Note 表，它包含三个字段

private static final String TABLE_NAME = "Note";private static final String COLUMN_ID = "id";
private static final String COLUMN_TITLE = "title";
private static final String COLUMN_CONTENT = "content";

字段	类型	备注
id	整型	主键，自增
title	文本	-
content	文本	-

2、单例模式

1. MyDatabaseManager 的构造函数是私有的，防止外部代码直接创建实例对象

private MyDatabaseManager() {...
}

2. MyDatabaseManager 实例对象只能通过 getInstance 方法获取，该方法确保了整个应用程序中只有一个 MyDatabaseManager 实例对象

public static MyDatabaseManager getInstance() {if (myDatabaseManager == null) myDatabaseManager = new MyDatabaseManager();return myDatabaseManager;
}

3、数据库创建

1. SQLiteDatabase.openOrCreateDatabase 方法用于打开或创建一个数据库，如果数据库存在，就是打开，如果数据库不存在，就是创建

sqLiteDatabase = SQLiteDatabase.openOrCreateDatabase(MyApplication.getContext().getDatabasePath(DATABASE_NAME).toString(), null);

2. execSQL 方法用于执行 SQL 语句

sqLiteDatabase.execSQL(CREATE_TABLE);

4、数据库操作

（1）增 insert

• 接收一个 Note 对象并将其插入数据库

public void insert(Note note) {sqLiteDatabase.execSQL("INSERT INTO " + TABLE_NAME + " (" + COLUMN_TITLE + ", " + COLUMN_CONTENT + ") VALUES (?, ?);",new Object[]{note.getTitle(), note.getContent()});
}

（2）删 delete

• 接收一个 id 来删除对应的记录

public void delete(int id) {sqLiteDatabase.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_ID + " = ?;", new Object[]{id});
}

（3）改 update

• 接收一个 Note 对象并根据 id 更新对应的记录

public void update(Note note) {sqLiteDatabase.execSQL("UPDATE " + TABLE_NAME + " SET " + COLUMN_TITLE + " = ?, " + COLUMN_CONTENT + " = ? WHERE " + COLUMN_ID + " = ?;",new Object[]{note.getTitle(), note.getContent(), note.getId()});
}

（4）查 queryAll

• 查询返回所有的行

@SuppressLint("Range")
public List<Note> queryAll() {Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM " + TABLE_NAME + ";", null);if (cursor.moveToFirst()) {List<Note> notes = new ArrayList<>();do {Note note = new Note(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)),cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)),cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT)));notes.add(note);} while (cursor.moveToNext());cursor.close();return notes;}cursor.close();return null;
}

---

四、SQLiteDatabase 简单编码 SQL 语句

1、数据创建

CREATE TABLE IF NOT EXISTS Note (id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,content TEXT
);

// 写成一行CREATE TABLE IF NOT EXISTS Note (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, content TEXT);

2、数据库操作

（1）增 insert

INSERT INTO Note (title,content
) VALUES (【title】,【content】
);

// 写成一行INSERT INTO Note (title, content) VALUES (【title】, 【content】);

（2）删 delete

// 写成一行DELETE FROM Note WHERE id = 【id】;

（3）改 update

UPDATE Note SETtitle = 【title】,content = 【content】
WHERE id = 【id】;

// 写成一行UPDATE Note SET title = 【title】, content = 【content】 WHERE id = 【id】;

（4）查 queryAll

// 写成一行SELECT * FROM Note;

注意事项

1. SQL 语句最好先在其他编辑器中写好，如果直接在代码中编写，极易写错

2. SQL 语句屑好后，最好改写成一行，之后粘贴到代码中，然后调整结构，否则结构会混乱

// 这是没写成一行的 SQL 语句private static final  String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS Note (\n" +"    id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +"    title TEXT,\n" +"    content TEXT\n" +");";

// 这写成一行的 SQL 语句（粘贴到代码中，然后调整结构的）private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS Note (" +"id INTEGER PRIMARY KEY AUTOINCREMENT, " +"title TEXT, " +"content TEXT);";