实现“android sqlite工具”教程
一、整体流程
下面是实现“android sqlite工具”的整体流程步骤：
gantt
title 实现“android sqlite工具”流程
section 创建数据库
设计数据库表结构: done, 2022-01-01, 1d
创建数据库帮助类: done, 2022-01-02, 1d
section 实现CRUD操作
实现插入数据操作: done, 2022-01-03, 1d
实现查询数据操作: done, 2022-01-04, 1d
实现更新数据操作: done, 2022-01-05, 1d
实现删除数据操作: done, 2022-01-06, 1d
二、具体步骤及代码
1. 创建数据库
首先，我们需要设计数据库表结构，然后创建数据库帮助类。
设计数据库表结构
```java
/**
* 数据库表的创建和升级
*/
public class DbHelper extends SQLiteOpenHelper {
// 数据库名
private static final String DB_NAME = "mydb.db";
// 数据库版本号
private static final int DB_VERSION = 1;
// 表名
private static final String TABLE_NAME = "my_table";
// 列名
private static final String COLUMN_ID = "id";
private static final String COLUMN_NAME = "name";
private static final String COLUMN_AGE = "age";
// 创建表的sql语句
private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_NAME + " TEXT,"
+ COLUMN_AGE + " INTEGER)";
public DbHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 数据库升级时的操作
}
}
### 2. 实现CRUD操作
接下来，我们需要实现插入、查询、更新和删除数据的操作。
#### 实现插入数据操作
```markdown
```java
/**
* 插入数据
*/
public void insertData(String name, int age) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_AGE, age);
db.insert(TABLE_NAME, null, values);
db.close();
}
#### 实现查询数据操作
```markdown
```java
/**
* 查询数据
*/
public List<Person> queryData() {
List<Person> personList = new ArrayList<>();
String selectQuery = "SELECT * FROM " + TABLE_NAME;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
Person person = new Person();
person.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
person.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
person.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));
personList.add(person);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return personList;
}
#### 实现更新数据操作
```markdown
```java
/**
* 更新数据
*/
public int updateData(int id, String name, int age) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_AGE, age);
return db.update(TABLE_NAME, values, COLUMN_ID + " = ?",
new String[]{String.valueOf(id)});
}
#### 实现删除数据操作
```markdown
```java
/**
* 删除数据
*/
public void deleteData(int id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, COLUMN_ID + " = ?",
new String[]{String.valueOf(id)});
db.close();
}
## 三、类图
```mermaid
classDiagram
class DbHelper {
- DB_NAME: String
- DB_VERSION: int
- TABLE_NAME: String
- COLUMN_ID: String
- COLUMN_NAME: String
- COLUMN_AGE: String
- CREATE_TABLE: String
+ DbHelper(Context context)
+ onCreate(SQLiteDatabase db)
+ onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
}
class Person {
- id: int
- name: String
- age: int
+ getId(): int
+ setId(int id): void
+ getName(): String
+ setName(String name
