数据库SQLite
数据库管理器 (SQLiteDatabase)
SQLiteDatabase是SQLite的数据库管理类,它提供若干操作数据表的API,常用的方法有3类:
1.管理类,用于数据库层面的操作
方法 | 说明 |
---|
openDatabase | 打开指定路径的数据库 |
isOpen | 判断数据库是否已打开 |
close | 关闭数据库 |
getVersion | 获取数据库的版本号 |
setVersion | 设置数据库的版本号 |
public void onClick(View view) {
String db = getFilesDir() + "/test.db";
SQLiteDatabase sqLiteDatabase = openOrCreateDatabase(db, Context.MODE_PRIVATE, null);
if (sqLiteDatabase != null) {
Log.d("message", "创建成功");
}
boolean result = deleteDatabase(db);
if (result) {
Log.d("message", "删除成功");
}
else{
Log.d("message", "删除失败");
}
}
2.事务类,用于事务层面的操作
方法 | 说明 |
---|
beginTransaction | 开始事务 |
setTransactionSuccessful | 设置事务的成功标志 |
endTransaction | 结束事务 |
public long update(User user){
ContentValues values = new ContentValues();
values.put("name", user.getName());
values.put("age", user.getAge());
try {
writeDB.beginTransaction();
writeDB.update("User",values, "name=?", new String[]{user.getName()});
writeDB.setTransactionSuccessful();
}catch (Exception e){
e.printStackTrace();
}
finally {
writeDB.endTransaction();
}
return 1;
}
3.数据处理类,用于数据表层面的操作
方法 | 说明 |
---|
execSQL | 执行拼接好的SQL控制语句 |
insert | 插入记录 |
delete | 删除符合条件的记录 |
update | 更新符合条件的记录 |
query | 执行查询操作,返回结果集游标 |
rawQuery | 执行拼接好的SQL查询语句,返回结果集游标 |
数据库帮助器 (SQLiteOpenHelper)
SQLiteOpenHelper是Android提供的数据库辅助工具,用于指导开发者进行SQLite的合理使用
使用SQLiteOpenHelper,必须继承SQLiteOpenHelper的数据库操作类,并重写onCreate和 onUpgrade两个方法,需要注意的是读,写DB是两个不同的方法,需要声明两个SQLiteDatabase对象,然后在添加增,删,改,查的方法
package com.example.myapplication;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
import java.util.ArrayList;
import java.util.List;
public class UserDBHelper extends SQLiteOpenHelper {
private SQLiteDatabase readDB = null;
private SQLiteDatabase writeDB = null;
public UserDBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
public SQLiteDatabase getReadDB(Context context) {
UserDBHelper userDBHelper = new UserDBHelper(context, "TestDB", null,1);
readDB = userDBHelper.getReadableDatabase();
return readDB;
}
public SQLiteDatabase getWriteDB(Context context) {
UserDBHelper userDBHelper = new UserDBHelper(context, "TestDB", null,1);
writeDB = userDBHelper.getWritableDatabase();
return writeDB;
}
public void close(SQLiteDatabase db) {
if (db !=null && db.isOpen()) {
db.close();
}
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String sql = "CREATE TABLE IF NOT EXISTS user (" +
"id INTEGER," +
" name VARCHAR NOT NULL," +
" age VARCHAR NOT NULL" +
" );";
sqLiteDatabase.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
String sql = "ALTER TABLE User ADD COLUMN class VARCHAR;";
sqLiteDatabase.execSQL(sql);
}
public long insert(User user){
ContentValues values = new ContentValues();
values.put("name", user.getName());
values.put("age", user.getAge());
long rowId = writeDB.insert("User", null, values);
return rowId;
}
public long delete(User user){
return writeDB.delete("User", "name=? and age=?", new String[]{user.getName(), user.getAge()});
}
public long update(User user){
ContentValues values = new ContentValues();
values.put("name", user.getName());
values.put("age", user.getAge());
return writeDB.update("User",values, "name=?", new String[]{user.getName()});
}
public List<User> queryAll(){
List<User> list = new ArrayList<User>();
Cursor cursor = readDB.query("User",null,null,null,null,null,null);
while (cursor.moveToNext()){
User user = new User();
user.setId(cursor.getInt(0));
user.setName(cursor.getString(1));
user.setAge(cursor.getString(2));
list.add(user);
}
return list;
}
public List<User> queryByName(String name){
List<User> list = new ArrayList<User>();
Cursor cursor = readDB.query("User",null,"name=?", new String[]{name},null,null,null);
while (cursor.moveToNext()){
User user = new User();
user.setId(cursor.getInt(0));
user.setName(cursor.getString(1));
user.setAge(cursor.getString(2));
list.add(user);
}
return list;
}
}