SQLite多表创建与分表独立CRUD操作实现需求问询

这绝对是个明智的架构调整——把原本臃肿不堪的DatabaseHelper拆分，让每个表的操作职责单一，后续维护、扩展都会顺畅很多。下面是具体的实现步骤和代码示例：

我们要把DatabaseHelper的职责收窄，只负责数据库初始化、版本管理以及所有表的创建/升级，把所有CRUD操作全部剥离出去。

示例代码如下：

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "your_db_name.db";
    private static final int DATABASE_VERSION = 1;

    // 各表创建语句
    private static final String CREATE_TABLE_COMPANY = "CREATE TABLE Company (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "name TEXT NOT NULL," +
            "address TEXT)";

    private static final String CREATE_TABLE_CONTACT = "CREATE TABLE Contact (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "company_id INTEGER," +
            "name TEXT," +
            "phone TEXT," +
            "FOREIGN KEY(company_id) REFERENCES Company(id))";

    private static final String CREATE_TABLE_TODO = "CREATE TABLE ToDo (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "title TEXT NOT NULL," +
            "is_completed INTEGER DEFAULT 0)";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 一次性创建所有表
        db.execSQL(CREATE_TABLE_COMPANY);
        db.execSQL(CREATE_TABLE_CONTACT);
        db.execSQL(CREATE_TABLE_TODO);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 这里处理数据库版本升级逻辑，比如表结构修改
        // 示例：如果需要删除旧表重建（生产环境建议谨慎操作，最好做数据迁移）
        // db.execSQL("DROP TABLE IF EXISTS Company");
        // db.execSQL("DROP TABLE IF EXISTS Contact");
        // db.execSQL("DROP TABLE IF EXISTS ToDo");
        // onCreate(db);
    }
}

为每个表创建一个单独的类，比如CompanyDB，这个类只专注于Company表的所有CRUD和自定义查询，通过依赖DatabaseHelper来获取数据库实例。

示例代码：

public class CompanyDB {
    private DatabaseHelper dbHelper;

    public CompanyDB(Context context) {
        dbHelper = new DatabaseHelper(context);
    }

    // 插入一条Company数据
    public long insertCompany(String name, String address) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("address", address);
        long id = db.insert("Company", null, values);
        db.close();
        return id;
    }

    // 查询所有Company数据
    public List<Company> getAllCompanies() {
        List<Company> companyList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query("Company", null, null, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                Company company = new Company();
                company.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));
                company.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
                company.setAddress(cursor.getString(cursor.getColumnIndexOrThrow("address")));
                companyList.add(company);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return companyList;
    }

    // 更新Company数据
    public int updateCompany(int id, String name, String address) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("address", address);
        int rowsAffected = db.update("Company", values, "id=?", new String[]{String.valueOf(id)});
        db.close();
        return rowsAffected;
    }

    // 删除指定Company
    public int deleteCompany(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int rowsAffected = db.delete("Company", "id=?", new String[]{String.valueOf(id)});
        db.close();
        return rowsAffected;
    }

    // 自定义查询示例：根据名称搜索Company
    public List<Company> searchCompaniesByName(String keyword) {
        List<Company> companyList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String selection = "name LIKE ?";
        String[] selectionArgs = new String[]{"%" + keyword + "%"};
        Cursor cursor = db.query("Company", null, selection, selectionArgs, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                Company company = new Company();
                company.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));
                company.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
                company.setAddress(cursor.getString(cursor.getColumnIndexOrThrow("address")));
                companyList.add(company);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return companyList;
    }
}

// 对应的Company实体类（可选，但建议创建）
class Company {
    private int id;
    private String name;
    private String address;

    // Getters and Setters
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getAddress() { return address; }
    public void setAddress(String address) { this.address = address; }
}

完全照搬CompanyDB的模式即可：

• 创建ContactDB类，封装Contact表的所有CRUD和查询操作
• 创建ToDoDB类，封装ToDo表的所有CRUD和查询操作
  每个类只关注自身表的业务，互不干扰。

这样拆分后，你会获得几个明显的好处：

• 单一职责原则：每个类只做一件事，代码逻辑清晰，可读性强
• 易于维护：修改某张表的操作时，只需要改动对应的XXDB类，不会影响到其他表的代码，也不会污染DatabaseHelper
• 扩展性好：后续新增表时，只需要添加对应的XXDB类和表创建语句即可，无需修改原有代码

内容的提问来源于stack exchange，提问作者MohammadAli