Android SQLite查询执行方法及应用启动时默认插入数据实现问询

Hey there! Let's tackle your two Android SQLite needs step by step, building on your existing AddressBookDatabaseHelper class.

First, let's flesh out your helper class with the table creation logic, then add methods to run queries. We'll cover two common approaches: raw SQL queries and the structured query() method.

Step 1: Complete the Table Creation #

First, finish the onCreate method to define your contacts table (since this was missing in your code snippet):

class AddressBookDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "AddressBook.db";
    private static final int DATABASE_VERSION = 1;
    // Define table and column names for clarity
    private static final String TABLE_CONTACTS = "contacts";
    private static final String COL_ID = "id";
    private static final String COL_NAME = "name";
    private static final String COL_PHONE = "phone_number";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create the contacts table
        String CREATE_TABLE = "CREATE TABLE " + TABLE_CONTACTS + " (" +
                COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                COL_NAME + " TEXT NOT NULL," +
                COL_PHONE + " TEXT NOT NULL)";
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Handle schema upgrades (e.g., drop old table if needed)
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
        onCreate(db);
    }

Step 2: Add Query Methods #

Now add methods to fetch data from the table:

Option A: Raw SQL Query

Use this for custom, complex SQL statements:

// Fetch all contacts using rawQuery
    public List<String> getAllContactsRaw() {
        List<String> contacts = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        // Raw query string
        String query = "SELECT " + COL_NAME + ", " + COL_PHONE + " FROM " + TABLE_CONTACTS;
        Cursor cursor = db.rawQuery(query, null);

        // Iterate through results
        if (cursor.moveToFirst()) {
            do {
                String contactEntry = cursor.getString(cursor.getColumnIndex(COL_NAME)) + 
                                      " - " + cursor.getString(cursor.getColumnIndex(COL_PHONE));
                contacts.add(contactEntry);
            } while (cursor.moveToNext());
        }

        // Always close cursor and database to avoid leaks
        cursor.close();
        db.close();
        return contacts;
    }

Option B: Structured query() Method

Use this for cleaner, parameterized queries (great for avoiding SQL injection):

// Fetch all contacts using the built-in query() method
    public Cursor getAllContactsStructured() {
        SQLiteDatabase db = this.getReadableDatabase();
        // Parameters: table, columns to select, selection, selection args, group by, having, order by
        return db.query(TABLE_CONTACTS,
                new String[]{COL_ID, COL_NAME, COL_PHONE},
                null,
                null,
                null,
                null,
                COL_NAME + " ASC"); // Sort by name ascending
    }

There are two common approaches here, depending on whether you want the default record added once (on first database creation) or every time the app starts (if no data exists).

Option 1: Insert Once on Database Creation #

If you only need the default record added when the database is first created (e.g., initial placeholder data), add the insert logic directly to the onCreate method:

@Override
    public void onCreate(SQLiteDatabase db) {
        // Create table first
        String CREATE_TABLE = "CREATE TABLE " + TABLE_CONTACTS + " (" +
                COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                COL_NAME + " TEXT NOT NULL," +
                COL_PHONE + " TEXT NOT NULL)";
        db.execSQL(CREATE_TABLE);

        // Insert default record
        String INSERT_DEFAULT = "INSERT INTO " + TABLE_CONTACTS + 
                                " (" + COL_NAME + ", " + COL_PHONE + ") VALUES ('Default Contact', '123-456-7890')";
        db.execSQL(INSERT_DEFAULT);
    }

Note: onCreate only runs once, so this record won't be duplicated even if the app restarts.

Option 2: Insert on App Launch (If No Data Exists) #

If you want to ensure there's always at least one record (even if the user deletes all entries), add a check in your launch Activity (e.g., MainActivity):

First, add helper methods to your AddressBookDatabaseHelper:

// Check if the contacts table has any records
    public boolean hasContacts() {
        SQLiteDatabase db = this.getReadableDatabase();
        String countQuery = "SELECT COUNT(*) FROM " + TABLE_CONTACTS;
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        db.close();
        return count > 0;
    }

    // Insert a contact using ContentValues
    public void insertContact(String name, String phone) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_NAME, name);
        values.put(COL_PHONE, phone);
        
        db.insert(TABLE_CONTACTS, null, values);
        db.close();
    }

Then call these methods in your MainActivity's onCreate:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    AddressBookDatabaseHelper dbHelper = new AddressBookDatabaseHelper(this);
    
    // Insert default contact if table is empty
    if (!dbHelper.hasContacts()) {
        dbHelper.insertContact("Default Contact", "123-456-7890");
    }
}

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