面试题：SQLite在Seinfeld应用中如何实现数据的增删改查

Question

假设Seinfeld应用中有一个存储角色信息的表，表结构包含角色名、性别、出场次数等字段。请分别写出使用SQLiteOpenHelper及SQL语句实现向该表插入一条新角色信息、删除指定角色信息、修改某角色出场次数、查询所有男性角色信息的代码片段（用Java语言实现）。

Answer 1

1. 插入新角色信息

使用SQLiteOpenHelper

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SeinfeldDBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "seinfeld.db";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "characters";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_GENDER = "gender";
    private static final String COLUMN_APPEARANCE_COUNT = "appearance_count";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (" +
                COLUMN_NAME + " TEXT PRIMARY KEY, " +
                COLUMN_GENDER + " TEXT, " +
                COLUMN_APPEARANCE_COUNT + " INTEGER)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public long insertCharacter(String name, String gender, int appearanceCount) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_NAME, name);
        values.put(COLUMN_GENDER, gender);
        values.put(COLUMN_APPEARANCE_COUNT, appearanceCount);
        return db.insert(TABLE_NAME, null, values);
    }
}

使用SQL语句

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SeinfeldDBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "seinfeld.db";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "characters";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_GENDER = "gender";
    private static final String COLUMN_APPEARANCE_COUNT = "appearance_count";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (" +
                COLUMN_NAME + " TEXT PRIMARY KEY, " +
                COLUMN_GENDER + " TEXT, " +
                COLUMN_APPEARANCE_COUNT + " INTEGER)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public void insertCharacterUsingSQL(String name, String gender, int appearanceCount) {
        SQLiteDatabase db = this.getWritableDatabase();
        String insertQuery = "INSERT INTO " + TABLE_NAME + " (" + COLUMN_NAME + ", " + COLUMN_GENDER + ", " + COLUMN_APPEARANCE_COUNT + ") VALUES ('" + name + "', '" + gender + "', " + appearanceCount + ")";
        db.execSQL(insertQuery);
    }
}

2. 删除指定角色信息

使用SQLiteOpenHelper

public int deleteCharacter(String name) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(TABLE_NAME, COLUMN_NAME + " =?", new String[]{name});
}

使用SQL语句

public void deleteCharacterUsingSQL(String name) {
    SQLiteDatabase db = this.getWritableDatabase();
    String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_NAME + " = '" + name + "'";
    db.execSQL(deleteQuery);
}

3. 修改某角色出场次数

使用SQLiteOpenHelper

public int updateAppearanceCount(String name, int newAppearanceCount) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(COLUMN_APPEARANCE_COUNT, newAppearanceCount);
    return db.update(TABLE_NAME, values, COLUMN_NAME + " =?", new String[]{name});
}

使用SQL语句

public void updateAppearanceCountUsingSQL(String name, int newAppearanceCount) {
    SQLiteDatabase db = this.getWritableDatabase();
    String updateQuery = "UPDATE " + TABLE_NAME + " SET " + COLUMN_APPEARANCE_COUNT + " = " + newAppearanceCount + " WHERE " + COLUMN_NAME + " = '" + name + "'";
    db.execSQL(updateQuery);
}

4. 查询所有男性角色信息

使用SQLiteOpenHelper

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

public class SeinfeldDBHelper extends SQLiteOpenHelper {
    //... 前面的代码不变

    public List<String> getMaleCharacters() {
        List<String> maleCharacters = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        String selectQuery = "SELECT " + COLUMN_NAME + " FROM " + TABLE_NAME + " WHERE " + COLUMN_GENDER + " = 'Male'";
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                maleCharacters.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return maleCharacters;
    }
}

使用SQL语句

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

public class SeinfeldDBHelper extends SQLiteOpenHelper {
    //... 前面的代码不变

    public List<String> getMaleCharactersUsingSQL() {
        List<String> maleCharacters = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        String selectQuery = "SELECT " + COLUMN_NAME + " FROM " + TABLE_NAME + " WHERE " + COLUMN_GENDER + " = 'Male'";
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                maleCharacters.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return maleCharacters;
    }
}