安卓应用开发：如何访问SQLite数据库? (安卓 访问sqlite数据库)
安卓开发中，SQLite是一种常用的数据库存储方式。SQLite是一种轻量级的嵌入式关系型数据库管理系统，它在安卓平台中可以快速、高效的进行数据的存储和管理，因此，在安卓应用开发中使用SQLite也成为了一种非常普遍的方式。
本文将介绍如何访问SQLite数据库，让你快速掌握在安卓开发中使用SQLite的技能。
一、安卓中SQLite数据库的使用
SQLite是一种轻量级的嵌入式数据库，在安卓中使用SQLite非常方便。需要注意的是，由于安卓应用开发通常是基于Java语言进行的，而SQLite是由C语言编写的，因此，需要使用Java语言中的一些特定的API接口来访问SQLite数据库。
1.1 安卓中SQLite数据库的API
在安卓中，可以使用以下API来访问SQLite数据库：
（1）android.database.sqlite.SQLiteDatabase类：该类提供了访问SQLite数据库的方法，包括查询、更新、插入和删除数据等基本操作。
（2）android.database.sqlite.SQLiteOpenHelper类：该类用于帮助创建和管理SQLite数据库。可以使用该类来创建和打开SQLite数据库，以及升级该数据库的版本。
1.2 在Android Studio中创建SQLite数据库
Android Studio是连接安卓开发和SQLite数据库的更佳工具。需要注意的是，在Android Studio中，我们可以通过三种方式来创建SQLite数据库：
1.2.1 基于SQLiteOpenHelper创建
SQLiteOpenHelper是Android提供的简化SQLite数据库使用的一个帮助类。我们可以继承SQLiteOpenHelper，在子类中重写onCreate()方法和onUpgrade()方法，来处理数据库的创建和升级操作。具体的操作如下：
（1）创建一个新的Java类，并继承SQLiteOpenHelper类。
（2）在子类中重写onCreate()方法，该方法用于在数据库之一次创建时执行，负责创建数据库的表和存储过程等。
（3）在子类中重写onUpgrade()方法，该方法用于在数据库版本升级时执行，通过该方法可以删除原有的数据表，并创建新的数据表以及存储过程等。
（4）在应用程序中实例化该类，并使用getReadableDatabase()方法或者getWritableDatabase()方法来获取一个用于访问数据库的SQLiteDatabase对象。
示例代码：
public class MyDatabaseHelper extends SQLiteOpenHelper {
public static final String CREATE_BOOK = “create table Book (“
+ “id integer primary key autoincrement, “
+ “author text, “
+ “price float, “
+ “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 succeeded”, Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(“drop table if exists Book”);
onCreate(db);
}
}
1.2.2 基于SQLite语句创建
使用Android Studio的SqliteDatabase类，我们可以通过执行SQL语句来创建SQLite数据库和数据表，示例代码如下：
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = “test.db”;
private static final String TABLE_NAME = “users”;
private static final String COLUMN_ID = “id”;
private static final String COLUMN_NAME = “name”;
private static final String COLUMN_EML = “eml”;
private static final String COLUMN_PASSWORD = “password”;
private static final String CREATE_USERS_QUERY = “CREATE TABLE “+TABLE_NAME +” (“+
COLUMN_ID +” INTEGER PRIMARY KEY AUTOINCREMENT,”+
COLUMN_NAME+” TEXT,”+
COLUMN_EML+” TEXT,”+
COLUMN_PASSWORD+” TEXT)”;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(CREATE_USERS_QUERY);
}
@Override
public void onUpgrade(SQLiteDatabase database, int i, int i1) {
database.execSQL(“DROP TABLE IF EXISTS “+TABLE_NAME );
onCreate(database);
}
}
1.2.3 使用ORM创建
ORM(Object-Relational Mapping)是一种将对象模型与关系型数据库模型进行映射的技术。在Android Studio中，可以使用ORM库来简化SQLite数据库的操作。一些常见的ORM库有：GreenDAO和OrmLite等。这里以GreenDAO为例，来介绍如何使用ORM来创建SQLite数据库：
（1）在build.gradle文件中添加GreenDAO的依赖：
dependencies {
implementation ‘org.greenrobot:greendao:3.2.2’
}
（2）在Java文件中，定义数据模型和数据操作类：
@Entity
public class User {
@Id(autoincrement = true)
private Long id;
private String name;
private String eml;
private String password;
// getter和setter方法
}
public class UserDao {
private final DaoSession daoSession;
private final UserDao userDao;
public UserDao(DaoSession daoSession) {
this.daoSession = daoSession;
userDao = daoSession.getUserDao();
}
public void insertUser(User user) {
userDao.insert(user);
}
public void deleteUser(User user) {
userDao.delete(user);
}
public User findUserById(Long id) {
return userDao.load(id);
}
public List getAllUsers() {
return userDao.loadAll();
}
}
（3）在Application中初始化GreenDAO：
public class App extends Application {
private DaoMaster.DevOpenHelper mHelper;
private SQLiteDatabase db;
private DaoMaster mDaoMaster;
private DaoSession mDaoSession;
@Override
public void onCreate() {
super.onCreate();
setDatabase();
}
private void setDatabase(){
mHelper = new DaoMaster.DevOpenHelper(this, “mydb”, null);
db = mHelper.getWritableDatabase();
mDaoMaster = new DaoMaster(db);
mDaoSession = mDaoMaster.newSession();
}
public DaoSession getDaoSession() {
return mDaoSession;
}
public DaoMaster getDaoMaster() {
return mDaoMaster;
}
public SQLiteDatabase getDb() {
return db;
}
}
二、安卓中SQLite数据库的操作
在Android Studio中创建和连接SQLite数据库以后，我们可以使用以下几种方式来访问数据库：
2.1 插入数据
使用SQLiteDatabase类的insert()方法，可以向数据表中添加数据，例如：
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(“name”, “The Da Vinci Code”);
values.put(“author”, “Dan Brown”);
values.put(“pages”, 454);
values.put(“price”, 16.96);
db.insert(“Book”, null, values);
2.2 更新数据
使用SQLiteDatabase类的update()方法，可以更新数据表中的数据，例如：
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(“price”, 10.99);
db.update(“Book”, values, “name = ?”, new String[] { “The Da Vinci Code” });
2.3 查询数据
使用SQLiteDatabase类的query()方法，可以查询数据表中的数据，例如：
SQLiteDatabase db = dbHelper.getWritableDatabase();
Cursor cursor = db.query(“Book”, null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
String name = cursor.getString(cursor.getColumnIndex(“name”));
String author = cursor.getString(cursor.getColumnIndex(“author”));
int pages = cursor.getInt(cursor.getColumnIndex(“pages”));
double price = cursor.getDouble(cursor.getColumnIndex(“price”));
} while (cursor.moveToNext());
}
cursor.close();
2.4 删除数据
使用SQLiteDatabase类的delete()方法，可以从数据表中删除数据，例如：
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete(“Book”, “pages > ?”, new String[] { “500” });
三、安卓SQLite数据库的一个使用案例
下面是一个简单的案例，说明如何使用安卓中的SQLite数据库实现简单的数据存储。
该案例的功能：实现简单的分类记账，记录用户的收入、支出及余额情况，并在图表中显示出这些数据。
3.1 创建数据库和表结构
我们需要创建两个表来表示流水账单记录和账单分类，具体的SQL代码如下：
CREATE TABLE t_bill_category (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT NOT NULL,
type TEXT NOT NULL
);
CREATE TABLE t_record_income (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
amount REAL NOT NULL,
time INTEGER NOT NULL,
category_id INTEGER NOT NULL,
FOREIGN KEY (category_id) REFERENCES t_bill_category (id)
);
CREATE TABLE t_record_expense (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
amount REAL NOT NULL,
time INTEGER NOT NULL,
category_id INTEGER NOT NULL,
FOREIGN KEY (category_id) REFERENCES t_bill_category (id)
);
3.2 创建Java类
我们需要创建三个Java类来实现相关功能：一个是AccountingDatabaseHelper类，用于创建和维护SQLite数据库；一个是Category类，用于表示账单类别；一个是Record类，用于表示收支流水记录。
AccountingDatabaseHelper类代码：
public class AccountingDatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = “accounting.db”;
public static final int DATABASE_VERSION = 1;
private static final String CREATE_CATEGORY_TABLE_SQL = “CREATE TABLE IF NOT EXISTS t_bill_category (” +
“id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” +
“name TEXT NOT NULL,” +
“type TEXT NOT NULL);”;
private static final String CREATE_INCOME_RECORD_TABLE_SQL = “CREATE TABLE IF NOT EXISTS t_record_income (” +
“id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” +
“amount REAL NOT NULL,” +
“time INTEGER NOT NULL,” +
“category_id INTEGER NOT NULL,” +
“FOREIGN KEY (category_id) REFERENCES t_bill_category (id));”;
private static final String CREATE_EXPENSE_RECORD_TABLE_SQL = “CREATE TABLE IF NOT EXISTS t_record_expense (” +
“id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” +
“amount REAL NOT NULL,” +
“time INTEGER NOT NULL,” +
“category_id INTEGER NOT NULL,” +
“FOREIGN KEY (category_id) REFERENCES t_bill_category (id));”;
private static final String[] INSERT_CATEGORY_DATA_SQL =
{“INSERT INTO t_bill_category (id,name,type) values (1,’餐饮’,’EXPENSE’)”,
“INSERT INTO t_bill_category (id,name,type) values (2,’交通’,’EXPENSE’)”,
“INSERT INTO t_bill_category (id,name,type) values (3,’工资’,’INCOME’)”,
“INSERT INTO t_bill_category (id,name,type) values (4,’津贴’,’INCOME’)”};
AccountingDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_CATEGORY_TABLE_SQL);
db.execSQL(CREATE_INCOME_RECORD_TABLE_SQL);
db.execSQL(CREATE_EXPENSE_RECORD_TABLE_SQL);
for (String sql : INSERT_CATEGORY_DATA_SQL) {
db.execSQL(sql);
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
Category类代码：
@Entity
public class Category {
@Id(autoincrement = true)
private Long id;
private String name;
private String type;
public Category() {
}
public Category(String name, String type) {
this.name = name;
this.type = type;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
}
Record类代码：
public class Record {
private Long id;
private double amount;
private long time;
private Category category;
public Record(double amount, long time, Category category) {
this.amount = amount;
this.time = time;
this.category = category;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public double getAmount() {
return amount;
}
public void setAmount(double amount) {
this.amount = amount;
}
public long getTime() {
return time;
}
public void setTime(long time) {
this.time = time;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
}
3.3 实现插入、查询和删除功能
在主Activity中，定义以下方法来实现插入、查询和删除等操作：
public Long saveRecord(Record record) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(“amount”, record.getAmount());
values.put(“time”, record.getTime());
values.put(“category_id”, record.getCategory().getId());
long id = db.insert(record.getCategory().getType().equalsIgnoreCase(“INCOME”) ? “t_record_income” : “t_record_expense”, null, values);
db.close();
return id;
}
public void deleteRecord(long id,String categoryType) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete(categoryType.equalsIgnoreCase(“INCOME”) ? “t_record_income” : “t_record_expense”, “id=?”, new String[]{String.valueOf(id)});
db.close();
}
public List getAllRecords(String categoryType) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.query(categoryType.equalsIgnoreCase(“INCOME”) ? “t_record_income” : “t_record_expense”, null, null, null, null, null, “time DESC”);
ArrayList records = new ArrayList();
while (cursor.moveToNext()) {
Long id = cursor.getLong(cursor.getColumnIndex(“id”));
double amount = cursor.getDouble(cursor.getColumnIndex(“amount”));
long time = cursor.getLong(cursor.getColumnIndex(“time”));
Long categoryId = cursor.getLong(cursor.getColumnIndex(“category_id”));
Category category = null;
Cursor categoryCursor = db.query(“t_bill_category”, null, “id=?”, new String[]{String.valueOf(categoryId)}, null, null, null);
if (categoryCursor.moveToFirst()) {
String categoryName = categoryCursor.getString(categoryCursor.getColumnIndex(“name”));
String categoryDbType = categoryCursor.getString(categoryCursor.getColumnIndex(“type”));
category = new Category(categoryName, categoryDbType);
category.setId(categoryId);
}
Record record = new Record(amount, time, category);
record.setId(id);
records.add(record);
categoryCursor.close();
}
cursor.close();
db.close();
return records;
}
3.4 实现图表功能
我们通过使用MPAndroidChart库来实现将收支记录数据在图表中展示的功能，总代码参考如下：
public class MnActivity extends AppCompatActivity {
private LinearLayout mLayoutExpense;
private LinearLayout mLayoutIncome;
private TextView mTextTotalExpense;
private TextView mTextTotalIncome;
private TextView mTextTotal;
private LineChart mChart;
private AccountingDatabaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_mn);
// 初始化UI组件
initView();
dbHelper = new AccountingDatabaseHelper(this);
// 初始化数据
initData();
// 初始化图表
initChart();
}
private void initView() {
mLayoutExpense = findViewById(R.id.ll_expense);
mLayoutIncome = findViewById(R.id.ll_income);
mTextTotalExpense = findViewById(R.id.tv_total_expense);
mTextTotalIncome = findViewById(R.id.tv_total_income);
mTextTotal = findViewById(R.id.tv_total);
mChart = findViewById(R.id.chart);
}
private void initData() {
double totalExpense = 0;
double totalIncome = 0;
// TODO: 从数据库中查询多个月份的收支记录数据
List expenseRecords = getAllRecords(“EXPENSE”);
for (int i = 0; i
Record record = expenseRecords.get(i);
addRecordView(record, mLayoutExpense);
totalExpense += record.getAmount();
}
List incomeRecords = getAllRecords(“INCOME”);
for (int i = 0; i
Record record = incomeRecords.get(i);
addRecordView(record, mLayoutIncome);
totalIncome += record.getAmount();
}
mTextTotalExpense.setText(String.format(Locale.getDefault(), “%.2f”, totalExpense));
mTextTotalIncome.setText(String.format(Locale.getDefault(), “%.2f”, totalIncome));
mTextTotal.setText(String.format(Locale.getDefault(), “%.2f”, totalIncome – totalExpense));
}
private void addRecordView(Record record, LinearLayout layout) {
View recordView = LayoutInflater.from(this).inflate(R.layout.layout_record_item, null);
TextView textView1 = recordView.findViewById(R.id.tv_item_name);
TextView textView2 = recordView.findViewById(R.id.tv_item_amount);
textView1.setText(record.getCategory().getName());
textView2.setText(String.format(Locale.getDefault(), “%.2f”, record.getAmount()));
layout.addView(recordView);
}
private void initChart() {
