Android Studio 数据库Sqlite3 安卓工具类（简便开发） - ToB企服应用市场:ToB评测及商务社交产业平台

package com.example.mytest; //注意：替换成自己的包名
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import androidx.annotation.Nullable;
import java.util.List;
import java.util.ArrayList;
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = "DatabaseHelper";
//数据库名和版本号
private static final String DATABASE_NAME = "test.db";
private static final int DATABASE_VERSION = 1;
//单例对象
private static DatabaseHelper instance;
private DatabaseHelper(@Nullable Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
Log.d(TAG,"DatabaseHelper");
}
//单例模式获取实例
public static synchronized DatabaseHelper getInstance(Context context){
if(instance == null){
instance = new DatabaseHelper(context.getApplicationContext());
Log.d(TAG,"getInstance");
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String sql = "CREATE TABLE user (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"username TEXT NOT NULL," +
"password TEXT NOT NULL," +
"age INTEGER," +
"height REAL," +
"is_active BOOLEAN NOT NULL DEFAULT 1," +
"created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
");";
Log.d(TAG,sql);
sqLiteDatabase.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
//增加数据,返回值为-1代表插入失败
public long insertUser(String username, String password, int age, double height, boolean isActive) {
SQLiteDatabase db = instance.getWritableDatabase();
long ret = 0;
try {
// 组装数据插入
ContentValues values = new ContentValues();
values.put("username", username);
values.put("password", password);
values.put("age", age);
values.put("height", height);
values.put("is_active", isActive ? 1 : 0);
ret = db.insert("user", null, values);
} catch (SQLiteException e) {
// 处理数据库操作失败的情况
Log.e(TAG, "Failed to insertUser : " , e);
} finally {
//关闭数据库, 注意：使用App Inspection调试时不执行db.close();,否则无法调试
db.close();
}
return ret;
}
//删除数据
public void deleteUser(long id) {
SQLiteDatabase db = instance.getWritableDatabase();
try {
// 使用参数化查询防止 SQL 注入
String selection = "id = ?";
String[] selectionArgs = { String.valueOf(id) };
db.delete("user", selection, selectionArgs);
} catch (SQLiteException e) {
// 处理数据库操作失败的情况
Log.e(TAG, "Failed to delete user with ID: " + id, e);
} finally {
//关闭数据库, 注意：使用App Inspection调试时不执行db.close();,否则无法调试
db.close();
}
}
//修改数据，返回值 <0 代表失败
public int updateUser(long id, String username, String password, int age, double height, boolean isActive) {
SQLiteDatabase db = instance.getWritableDatabase();
int ret = 0;
try {
// 组装数据插入
ContentValues values = new ContentValues();
values.put("username", username);
values.put("password", password);
values.put("age", age);
values.put("height", height);
values.put("is_active", isActive ? 1 : 0);
String selection = "id LIKE ?";
String[] selectionArgs = { String.valueOf(id) };
ret = db.update("user", values, selection, selectionArgs);
} catch (SQLiteException e) {
// 处理数据库操作失败的情况
Log.e(TAG, "Failed to insertUser : " , e);
} finally {
//关闭数据库, 注意：使用App Inspection调试时不执行,否则看不到实时数据
db.close();
}
return ret;
}
//查询数据，所有
public List<UserBean> getAllUsers() {
SQLiteDatabase db = instance.getReadableDatabase();
List<UserBean> userList = new ArrayList<>();
// 执行查询
Cursor cursor = db.query("user", null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
long id = cursor.getLong(cursor.getColumnIndex("id"));
String username = cursor.getString(cursor.getColumnIndex("username"));
String password = cursor.getString(cursor.getColumnIndex("password"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
float height = cursor.getFloat(cursor.getColumnIndex("height"));
boolean isActive = cursor.getInt(cursor.getColumnIndex("is_active")) == 1;
long createdTime = cursor.getLong(cursor.getColumnIndex("created_time"));
UserBean user = new UserBean(id, username, password, age, height, isActive, createdTime);
userList.add(user);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return userList;
}
//查询数据,指定
public UserBean getUserById(long userId) {
SQLiteDatabase db = instance.getReadableDatabase();
// 定义查询条件
String selection = "id = ?";
String[] selectionArgs = { String.valueOf(userId) };
//执行查询
Cursor cursor = db.query("user", null, selection, selectionArgs, null, null, null);
UserBean user = null;
if (cursor.moveToFirst()) {
long id = cursor.getLong(cursor.getColumnIndex("id"));
String username = cursor.getString(cursor.getColumnIndex("username"));
String password = cursor.getString(cursor.getColumnIndex("password"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
float height = cursor.getFloat(cursor.getColumnIndex("height"));
boolean isActive = cursor.getInt(cursor.getColumnIndex("is_active")) == 1;
long createdTime = cursor.getLong(cursor.getColumnIndex("created_time"));
user = new UserBean(id, username, password, age, height, isActive, createdTime);
}
cursor.close();
db.close();
return user;
}
}

复制代码

package com.example.mytest; //替换成自己包名
public class UserBean {
private long id;
private String username;
private String password;
private int age;
private float height;
private boolean isActive;
private long createdTime;
// 构造函数
public UserBean(long id, String username, String password, int age, float height, boolean isActive, long createdTime) {
this.id = id;
this.username = username;
this.password = password;
this.age = age;
this.height = height;
this.isActive = isActive;
this.createdTime = createdTime;
}
// Getter 和 Setter 方法
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public float getHeight() {
return height;
}
public void setHeight(float height) {
this.height = height;
}
public boolean isActive() {
return isActive;
}
public void setActive(boolean active) {
isActive = active;
}
public long getCreatedTime() {
return createdTime;
}
public void setCreatedTime(long createdTime) {
this.createdTime = createdTime;
}
// toString 方法
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", age=" + age +
", height=" + height +
", isActive=" + isActive +
", createdTime=" + createdTime +
'}';
}
}

复制代码