java - SQLiteDatabase 仅在不存在时才添加

Question

我正在实现“添加到 Collection 夹”功能。用户应该能够将数据添加到数据库的位置。

DatabaseHandler 正在运行，我已经创建了一个类来处理 get/set 方法。

我可以轻松地将数据添加到数据库中，但我不太清楚如何检查特定 ID 是否已经存在，从而不向数据库中添加重复项。

这是我的数据库处理程序:

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "favoriteMovies";

// DataFavorites table name
private static final String TABLE_NAME = "movies";

// DataFavorites Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_URL = "url";
private static final String KEY_RELEASEDATE = "releaseDate";


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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_DATAFAVORITES_TABLE = "CREATE TABLE " + TABLE_NAME + "("
            + KEY_ID + "  INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT,"
            + KEY_URL + " TEXT,"+ KEY_RELEASEDATE + " TEXT" + ")";
    db.execSQL(CREATE_DATAFAVORITES_TABLE);



}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

    // Create tables again
    onCreate(db);

}


// Adding new movie
public void addMovie(DataFavorites movie) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_ID, movie.getId()); // movie id
    values.put(KEY_TITLE, movie.getTitle()); // movie title
    values.put(KEY_URL, movie.getUrl()); // movie url (poster)
    values.put(KEY_RELEASEDATE, movie.getReleaseDate()); // movie url (poster)

    // Inserting Row
    db.insert(TABLE_NAME, null, values);
    db.close(); // Closing database connection

}

// Getting single movie
public DataFavorites getMovie(int id) {

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_NAME, new String[] { KEY_ID,
                    KEY_TITLE, KEY_URL, KEY_RELEASEDATE }, KEY_ID + "=?",
            new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    DataFavorites movie = new DataFavorites(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1), cursor.getString(2), cursor.getString(3));
    // return movie
    return movie;

}

// Getting All movie
public List<DataFavorites> getAllMovie() {

    List<DataFavorites> movieList = new ArrayList<DataFavorites>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_NAME;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            DataFavorites dataFavorites = new DataFavorites();

            dataFavorites.setId(Integer.parseInt(cursor.getString(0)));
            dataFavorites.setTitle(cursor.getString(1));
            dataFavorites.setUrl(cursor.getString(2));
            dataFavorites.setReleaseDate(cursor.getString(3));

            // Adding movie to list
            movieList.add(dataFavorites);
        } while (cursor.moveToNext());
    }

    // return movie list
    return movieList;

}

// Getting movie Count
public int getMoviesCount(){

    String countQuery = "SELECT  * FROM " + TABLE_NAME;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();

}

// Updating single movie
public int updateMovie(DataFavorites movie) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_ID, movie.getId());
    values.put(KEY_TITLE, movie.getTitle());
    values.put(KEY_URL, movie.getUrl());
    values.put(KEY_RELEASEDATE, movie.getReleaseDate());

    // updating row
    return db.update(TABLE_NAME, values, KEY_ID + " = ?",
            new String[] { String.valueOf(movie.getId()) });

}

// Deleting single movie
public void deleteMovie(DataFavorites movie) {

    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME, KEY_ID + " = ?",
            new String[] { String.valueOf(movie.getId()) });
    db.close();

}

如果 id 不存在，我想在这里添加数据:

public void AddToFavorites(MenuItem item) {

    DatabaseHandler db = new DatabaseHandler(this);

    /* if ( id is not there ){

    // Inserting movie
    Log.d("Insert: ", "Inserting movie..");
    db.addMovie(new DataFavorites(Integer.parseInt(Id), Title, Poster, Release));
} */

}

Answer 1

向表列添加一个UNIQUE 索引以防止重复条目。另请注意，如果插入重复项，它会抛出异常，所以不要忘记处理它