I have SQLite DB which there I am saving data and I want for each new entry to check in DB if already exists this record if yes show me a toast message if not insert the record in DB.
I want to check if the search url and native url exists or not in the DB.
Below is the code.
public class BookmarkDB extends SQLiteOpenHelper {
public static final String DBNAME = "bookmarks.db"; // The name of the database file
public static final int DBVERSION = 1; // The Database version
public static final String TBL_BOOKMARK = "bookmark";
public static final String COL_ID = BaseColumns._ID; // equates to _id
public static final String COl_NAME = "name";
public static final String COl_HIDDEN = "hidden";
public static final String COL_ICON = "icon";
public static final String COL_NATIVEURL = "nativeurl";
public static final String COL_SEARCHURL = "searchurl";
SQLiteDatabase mDB;
Context mContext;
public BookmarkDB(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
// The SQL to be used to create the table
String crt_bookmark_tbl_sql = "CREATE TABLE IF NOT EXISTS " + TBL_BOOKMARK + "(" +
COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COl_NAME + " TEXT, " +
COl_HIDDEN + " INTEGER, " +
COL_ICON + " TEXT, " +
COL_NATIVEURL + " TEXT," +
COL_SEARCHURL + " TEXT" +
")";
db.execSQL(crt_bookmark_tbl_sql); // CREATE THE TABLE
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP IF TABLE EXISTS " + DBNAME);
onCreate(db);
}
public long addBookmark(long id, String name, boolean hidden, String icon, String nativeurl, String searchurl) {
ContentValues cv = new ContentValues();
cv.put(COl_HIDDEN,hidden);
cv.put(COl_NAME,name);
cv.put(COL_ICON,icon);
cv.put(COL_NATIVEURL,nativeurl);
cv.put(COL_SEARCHURL,searchurl);
return mDB.insert(TBL_BOOKMARK, null, cv);
// uses the convenience insert method that builds the SQL
}
public ArrayList<Bookmark> getAllBookmarks() {
ArrayList<Bookmark> rv = new ArrayList<>();
Cursor csr = mDB.query(TBL_BOOKMARK,null,null,null,null,null, null);
while (csr.moveToNext()) {
Bookmark b = new Bookmark();
b.setId(csr.getString(csr.getColumnIndex(COL_ID)));
int Icon = csr.getInt(csr.getColumnIndex(COL_ICON));
String name = csr.getString(csr.getColumnIndex(COl_NAME));
String searchUrl = csr.getString(csr.getColumnIndex(COL_SEARCHURL));
b.setIcon(Icon);
b.setName(name);
b.setSearchUrl(searchUrl);
b.setViewType(csr.getInt(csr.getColumnIndex(COl_NAME)));
b.setNativeUrl(csr.getString(csr.getColumnIndex(COL_NATIVEURL)));
rv.add(b);
}
return rv;
}
public void deleteBookmark(int id, String name){
SQLiteDatabase db = this.getWritableDatabase();
String query = "DELETE FROM " + TBL_BOOKMARK + " WHERE "
+ COL_ID + " = '" + id + "'" +
" AND " + COl_NAME + " = '" + name + "'";
Log.d("Deleted", "Item" + query);
db.execSQL(query);
}
}
Code for saving new record
public class ActivityChangeBookmark extends AppCompatActivity {
private ArrayList<String> listItems = new ArrayList<String>();
private RecyclerView recyclerView;
Button saveBookmark, cancelBookmark;
TextView name, url;
ImageView icon, mIcon;
EditText mName, mUrl, mID;
public static final String Save_Bookmark = "Save_Bookmark";
static final String xmlFileName = "bookmarks.xml";
String Url, Name;
Integer Id;
MyAdapter myAdapter;
BookmarkDB bookmarkDB;
ButtonRobotoMedium removeBookmark, sendToHomeScreen;
ArrayList<Bookmark> arrayList = new ArrayList<>();
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_change_bookmark);
saveBookmark = findViewById(R.id.btnSave);
name = findViewById(R.id.tvNameEdit);
url = findViewById(R.id.tvURLEdit);
recyclerView = findViewById(R.id.myRecyclerView);
icon = findViewById(R.id.ivFavIcon);
mName = findViewById(R.id.etNameEdit);
mUrl = findViewById(R.id.etURLEdit);
cancelBookmark = findViewById(R.id.btnCancel);
removeBookmark = findViewById(R.id.btnRemove);
sendToHomeScreen = findViewById(R.id.btnAddIconToDeviceScreen);
name.setVisibility(View.GONE);
url.setVisibility(View.GONE);
bookmarkDB = new BookmarkDB(getApplicationContext());
Intent intent = getIntent();
myAdapter = new MyAdapter(getApplicationContext(), arrayList);
Bundle extras = intent.getExtras();
if (extras != null) {
Url = intent.getExtras().getString("Url");
Name = intent.getExtras().getString("Name");
Id = intent.getExtras().getInt("ID");
Bitmap bitmap = intent.getExtras().getParcelable("Image");
mName.setText(Name);
mUrl.setText(Url);
Log.d("Id", "TakenId" + Id + Name + Url);
icon.setImageBitmap(bitmap);
removeBookmark.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Log.d("DeletedItem", "Test" + Id + Name);
bookmarkDB.deleteBookmark(Id, Name);
myAdapter.notifyDataSetChanged();
finish();
}
});
}
else {
removeBookmark.setVisibility(View.GONE);
sendToHomeScreen.setVisibility(View.GONE);
icon.setImageResource(R.drawable.user_bookmark);
mName.setText("");
mUrl.setText("");
}
saveBookmark.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
saveData();
myAdapter.notifyDataSetChanged();
finish();
}
});
cancelBookmark.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
myAdapter.notifyDataSetChanged();
finish();
}
});
}
public void saveData() {
Random r = new Random();
int low = 14;
int high = 100;
int result = r.nextInt(high-low) + low;
bookmarkDB.addBookmark(result, mName.getText().toString(), false, "", mUrl.getText().toString(), "http://" + mUrl.getText().toString());
}
}
The simplest way would be to utilise UNIQUE thus making the column/composite columns which will result in a constraint conflict and thus not allow the row to be inserted.
What is not abundantly clear is what columns you are referring to to. The title appears to indicate that it is the name column, in which case you could change COl_NAME + " TEXT, " +, to be COl_NAME + " TEXT UNIQUE, " +.
The text how states I wan't to check if the search url and native url exists or not in the DB. This implies that it is only if a combination of both exist that you don't want to add the row, but that a combination where only one of the columns already exists should be added.
Assuming this then you could add a composite UNIQUE constraint in which case, you could add the line
", UNIQUE(" + COL_NATIVEURL + "," + COL_SEARCHURL + ")" +
This would be added after the line COL_SEARCHURL + " TEXT" +
If wanted the row to not be added if either existed then you could add UNIQUE to the column definitions by using :-
COL_NATIVEURL + " TEXT UNIQUE," +
COL_SEARCHURL + " TEXT UNIQUE" +
If the above does not suit and you want to check a column then you could base that check upon something like the following method in the BookmarkDB class:-
public boolean ifNativeUrlExists(String nativeUrl) {
boolean rv = false;
String whereclause = COL_NATIVEURL + "=?";
String[] whereargs = new String[]{nativeUrl};
Cursor csr = mDB.query(TBL_BOOKMARK,null,whereclause,whereargs,null,null, null)
if (csr.getCount() > 0) {
rv = true;
}
csr.close();
return rv;
}
You could utilise this using something like :-
public void saveData() {
Random r = new Random();
int low = 14;
int high = 100;
int result = r.nextInt(high-low) + low;
if (!bookmarkDB.ifNativeUrlExists(mUrl.getText().toString())) {
bookmarkDB.addBookmark(result, mName.getText().toString(), false, "", mUrl.getText().toString(), "http://" + mUrl.getText().toString());
} else {
..... code to indicate not added
}
}