Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Post an Article
Welcome To Ask or Share your Answers For Others

Categories

Android Studio Sqlite don't insert in database if value already exists

0 votes
128 views
in Technique[技术] by (71.8m points)

Android Studio Sqlite don't insert in database if value already exists

Maybe this question is duplicate but I cant find an answer that similar to my code.

I want to check database value before inserting.

If value is exist, I want to skip and continue to next insert.

Here is my insert

public void insertPlayer1Songlist() {

        createPlayer1("Song Title 1", "Artist 1", "Vol 1","","0","12341");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","","0","12342");
        createPlayer1("Song Title 3", "Artist 3", "Vol 3","","0","12343");
        createPlayer1("Song Title 4", "Artist 4", "Vol 4","","0","12344");
        createPlayer1("Song Title 5", "Artist 5", "Vol 5","","0","12345");
        createPlayer1("Song Title 6", "Artist 6", "Vol 6","","0","12346");
        createPlayer1("Song Title 7", "Artist 7", "Vol 7","","0","12347");
        createPlayer1("Song Title 8", "Artist 8", "Vol 8","","0","12348");
        createPlayer1("Song Title 9", "Artist 9", "Vol 9","","0","12349");
        createPlayer1("Song Title 10", "Artist 10", "Vol 10","","0","12310");
        createPlayer1("Song Title 11", "Artist 11", "Vol 11","","0","12311");
        createPlayer1("Song Title 12", "Artist 12", "Vol 12","","0","12312");

    }

and here is where I want to check if value is already in database..

public long createPlayer1(String title,
                              String artist, String volume,
                              String type, String favorite, String number) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_ARTIST, artist);
        initialValues.put(KEY_VOLUME, volume);
        initialValues.put(KEY_TYPE, type);
        initialValues.put(KEY_FAVORITE, favorite);
        initialValues.put(KEY_NUMBER, number);

        return mDb.insert(SQLITE_TABLE, null, initialValues);
    }

I want to check if number is already in database.

If yes I want to skip and continue to this code.

return mDb.insert(SQLITE_TABLE, null, initialValues);

full code..

package com.magicstarme.virtualsongbook;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;


/**
 * Created by Joe on 7/7/2016.
 */
public class FragmentOne_DbAdapter {

    public static final String KEY_ROWID = "_id";
    public static final String KEY_TITLE = "title";
    public static final String KEY_ARTIST = "artist";
    public static final String KEY_VOLUME = "volume";
    public static final String KEY_TYPE = "type";
    public static final String KEY_FAVORITE = "favorite";
    public static final String KEY_NUMBER = "number";

    private static final String TAG = "FragmentOne_DbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "Virtualsongbook";
    private static final String SQLITE_TABLE = "Player1";
    private static final int DATABASE_VERSION = 1;

    private final Context mCtx;

    private static final String DATABASE_CREATE =
            "CREATE TABLE if not exists " + SQLITE_TABLE + " (" +
                    KEY_ROWID + " integer PRIMARY KEY autoincrement," +
                    KEY_TITLE + "," +
                    KEY_ARTIST + "," +
                    KEY_VOLUME + "," +
                    KEY_TYPE + "," +
                    KEY_FAVORITE + "," +
                    KEY_NUMBER + ")";

    private static class DatabaseHelper extends SQLiteOpenHelper {

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


        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.w(TAG, DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
            onCreate(db);
        }
    }

    public FragmentOne_DbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public FragmentOne_DbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }

    private boolean isRecordExistInDatabase(String tableName, String field, String value) {
        Cursor c = mDb.rawQuery("SELECT * FROM " + tableName + " WHERE " + field + "=" + value, null);
        if (c.moveToFirst()) {
            //Record exist
            c.close();
            return true;
        }
        //Record available
        c.close();
        return false;
    }


    public long createPlayer1(String title,
                              String artist, String volume,
                              String type, String favorite, String number) {

        if(isRecordExistInDatabase(SQLITE_TABLE, KEY_NUMBER, number)){
            return 0;
        }

        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_ARTIST, artist);
        initialValues.put(KEY_VOLUME, volume);
        initialValues.put(KEY_TYPE, type);
        initialValues.put(KEY_FAVORITE, favorite);
        initialValues.put(KEY_NUMBER, number);

        return mDb.insert(SQLITE_TABLE, null, initialValues);
    }

    public boolean deleteAllPlayer1() {

        int doneDelete = 0;
        doneDelete = mDb.delete(SQLITE_TABLE, null , null);
        Log.w(TAG, Integer.toString(doneDelete));
        return doneDelete > 0;

    }

    public Cursor fetchPlayer1ByTitle(String titleText) throws SQLException {
        Log.w(TAG, titleText);
        Cursor mCursor = null;
        if (titleText == null  ||  titleText.length () == 0)  {
            mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
                            KEY_TITLE, KEY_ARTIST, KEY_VOLUME, KEY_TYPE, KEY_FAVORITE, KEY_NUMBER},
                    null, null, null, null, null);

        }
        else {
            mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
                            KEY_TITLE, KEY_ARTIST, KEY_VOLUME, KEY_TYPE, KEY_FAVORITE, KEY_NUMBER},
                    KEY_TITLE + " like '%" + titleText + "%'", null,
                    null, null, null, null);
        }
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    public Cursor fetchAllPlayer1() {

        Cursor mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
                        KEY_TITLE, KEY_ARTIST, KEY_VOLUME, KEY_TYPE, KEY_FAVORITE, KEY_NUMBER},
                null, null, null, null, null);

        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public boolean updateItemFavorite(long rowId, String favorite) {
        int doneUpdate = 0;
        ContentValues values = new ContentValues();
        values.put(KEY_FAVORITE, favorite);
        //doneUpdate =  mDb.update(SQLITE_TABLE, values, KEY_ROWID + " = ?", new String[] { String.valueOf(rowId) });
        doneUpdate =  mDb.update(SQLITE_TABLE, values, KEY_ROWID + "=" + rowId,null);
        Log.w(TAG, Integer.toString(doneUpdate));
        return doneUpdate > 0;
        //return mDb.update(SQLITE_TABLE, values, KEY_ROWID+"="+rowId,null);
    }

    public void insertPlayer1Songlist() {

        //TYPE
        //0 - NONE
        //1 - NEW

        createPlayer1("Song Title 1", "Artist 1", "Vol 1","0","0","12341");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","0","0","12342");
        createPlayer1("Song Title 3", "Artist 3", "Vol 3","0","0","12343");
        createPlayer1("Song Title 4", "Artist 4", "Vol 4","0","0","12344");
        createPlayer1("Song Title 5", "Artist 5", "Vol 5","0","0","12345");
        createPlayer1("Song Title 6", "Artist 6", "Vol 6","0","0","12346");
        createPlayer1("Song Title 7", "Artist 7", "Vol 7","0","0","12347");
        createPlayer1("Song Title 8", "Artist 8", "Vol 8","0","0","12348");
        createPlayer1("Song Title 9", "Artist 9", "Vol 9","0","0","12349");
        createPlayer1("Song Title 10", "Artist 10", "Vol 10","0","0","12310");
        createPlayer1("Song Title 11", "Artist 11", "Vol 11","0","0","12311");
        createPlayer1("Song Title 12", "Artist 12", "Vol 12","0","0","12312");

        /*createPlayer1("Song Title 1", "Artist 1", "Vol 1","0","0","12341");
        createPlayer1("Song Title 1", "Artist 1", "Vol 1","0","0","12341");
        createPlayer1("Song Title 1", "Artist 1", "Vol 1","0","0","12341");
        createPlayer1("Song Title 1", "Artist 1", "Vol 1","0","0","12341");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","0","0","12342");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","0","0","12342");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","0","0","12342");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","0","0","12342");
        createPlayer1("Song Title 2", "Artist 2", "Vol 2","0","0","12342");
        createPlayer1("Song Title 12", "Artist 12", "Vol 12","0","0","12312");
        createPlayer1("Song Title 12", "Artist 12", "Vol 12","0","0","12312");
        createPlayer1("Song Title 12", "Artist 12", "Vol 12","0","0","12312");
        createPlayer1("Song Title 12", "Artist 12", "Vol 12","0","0","10000");*/

    }
}

Please help me,

Thanks.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points) 
private boolean isRecordExistInDatabase(String tableName, String field, String value) {
    String query = "SELECT * FROM " + tableName + " WHERE " + field + " = '" + value + "'";
    Cursor c = db.rawQuery(query, null);
    if (c.moveToFirst()) {
        //Record exist
        c.close();
        return true;
    }
    //Record available
    c.close();
    return false;    
}


public long createPlayer1(...){
    // check if record exist in database by title number
    if(isRecordExistInDatabase(SQLITE_TABLE, KEY_NUMBER, number)){
        return 0;
    }
    // create record and insert to database normally
    ContentValues initialValues = new ContentValues();
    ...
    return mDb.insert(SQLITE_TABLE, null, initialValues);
}

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界，一个免费开放的程序员编程交流平台！开放，进步，分享！让技术改变生活，让极客改变未来！ Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

Just Browsing Browsing

1.4m articles

1.4m replys

5 comments

56.9k users

Most popular tags

Snow Theme by Q2A Market
Powered by Question2Answer
...