Android Studio 数据库Sqlite3 安卓工具类（简便开发）

原创于 2024-09-12 22:30:00 发布 · 2k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #android #android studio

版本：Android Studio 2024.1.1

SDK：34

一、数据库查看方式

使用App Inspection工具，此工具包括网络请求、数据库访问等功能，可以实时监控应用的行为。

如图所示：

二、封装工具类

源码：

DatabaseHelper.java

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;
    }

}

实体类定义，查询使用，只用增、删、改不用添加。

UserBean .java

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 +
                '}';
    }
}

三、使用Sqlite工具类（直接调用）

1.增

DatabaseHelper.getInstance(getApplicationContext()).insertUser("zs","1",18,12,true);

2.删

DatabaseHelper.getInstance(getApplicationContext()).deleteUser(1);

3.改

DatabaseHelper.getInstance(getApplicationContext()).updateUser(4,"zs44","1",18,12,true);

4.查

//查询所有用户

List<UserBean> userList = new ArrayList<>();
userList = DatabaseHelper.getInstance(getApplicationContext()).getAllUsers();
for(UserBean cell : userList){
Log.d("test",cell.toString());
}

//查询指定用户

UserBean user;
user = DatabaseHelper.getInstance(getApplicationContext()).getUserById(1);
Log.d("test",user.toString());