How to check data in SQLite if already exist update or else insert in android

Question

I want to check the data in SQLite if already exist can update or else insert.I am checking code like this what i mentioned below.

Code:

public long  addmenus(String navigationdrawer,String optionname)
    {
        SQLiteDatabase menus=this.getWritableDatabase();
        try {

        ContentValues values=new ContentValues();
        values.put(HEADER_NAME,navigationdrawer);
        values.put(CHILD_NAME,optionname);

       // menus.insert(TABLE_NAME,null,values);
           // String owner=optionname;

            Cursor cursor = menus.rawQuery("select * from TABLE_NAME where CHILD_NAME ='"+ optionname +"'", null);

        if(cursor.getCount()<1)
        {
            //execute insert query here
            long rows = menus.insert(TABLE_NAME, null, values);
            return rows;
            // return rows inserted.
        }
        else
        {
            //Perform the update query
            String strFilter = "CHILD_NAME" + optionname;
            long updaterow=menus.update(TABLE_NAME,values,strFilter,null);
            return updaterow;
            // return rows updated.
        }

       // menus.close();

    } catch (Exception e) {
        return -1;
    }

        finally {
            if (menus != null)
                menus.close();
        }
    }

My activity:

I converted whole json data into string object then insert into SQLite.

  String productpage=jsonObject.toString();
     db.addmenus(productpage,"Navigationmenus");

But It doesn't work.It couldn't insert into sqlite.

Anyone solve this problem Glad to appreciate. Thanks in advance

What error are you getting? Also it looks like you're using `TABLE_NAME` and `CHILD_NAME` as strings instead of the constants they seem to be. — Nathanael, Apr 28 '16 at 05:20
Table name is my table name and have two column child name and header namer. i have to convert whole json data in to string the insert into column headername and i will give one option name eg. menu for one json then second one product option for another json — Nivethitha, Apr 28 '16 at 05:24
You are storing JSON into Sqlite? You might want to look at using Realm as your database instead — OneCricketeer, Apr 28 '16 at 05:38

score 1 · Answer 1 · answered Apr 28 '16 at 05:04

1

You can user insertWithOnConflict() like this

db.insertWithOnConflict(TABLE, null, yourContentValues, SQLiteDatabase.CONFLICT_REPLACE);

answered Apr 28 '16 at 05:04

N J

27,217
13
76
96

i want to check with where conditon option name if already or not.how could possible insertwithonconflict – Nivethitha Apr 28 '16 at 05:07
name is user primary key ? – N J Apr 28 '16 at 05:10
1

It doesn't work for me it can inserted again and again same json data – Nivethitha Apr 28 '16 at 05:19

score 0 · Answer 2 · answered Apr 28 '16 at 05:36

0

you should use replace into

REPLACE INTO table(...) VALUES(...);

answered Apr 28 '16 at 05:36

Mark Shen

346
1
5

I don't want to replace the table.i just want to update the data if data already exist in table using where condtion name= option name – Nivethitha Apr 28 '16 at 05:39
"REPLACE INTO" does not replace the table.it just inserts or updates the data – Mark Shen Apr 28 '16 at 05:45
can u tell REPLACE INTO query properly – Nivethitha Apr 28 '16 at 05:50
rawQuery("replace into TABLE_NAME (CHILD_NAME) values("+optionname+");") – Mark Shen Apr 28 '16 at 06:07

Attaullah · Answer 3 · 2016-04-28T10:03:17.820

Question is not much clear but, i think you want to check either data/record is inserted in SQLite or not. you will need to define some extra variable long rowInserted insert() method returns the row ID of the newly inserted row, or -1 when an error occurred.

menus.insert(TABLE_NAME, null, values);

long rowInserted = db.insert(AddNewPhysicalPerson, null, newValues);

if(rowInserted != -1)
    Toast.makeText(myContext, "New row added :" + rowInserted, Toast.LENGTH_SHORT).show();
else
    Toast.makeText(myContext, "Something wrong", Toast.LENGTH_SHORT).show();

Updated check either data is in table or column? for this you use this code

public boolean Exists(String id){
    Cursor res = getAllData();
    int count=0;
    while (res.moveToNext()){
        String email =res.getString(3);
        if(email.equals(id)){
            count++;
        }
    }
    if(count==0){
        return false;
    } else{
        return true;
    }
}

Second you asking about json first store all data in any List run time and get string from it then you are able to store in SQlite

try {
        items = jsonObject.getJSONArray("myjsonattribute");
        List<MyAnySetterGetter> mList = new ArrayList<MyAnySetterGetter>();
        for (int i = 0; i < items.length(); i++) {
             JSONObject c = items.getJSONObject(i);
             String mfilename = c.getString("myjsonattribute2");
             mList.add(mfilename);
            }
    } catch (JSONException e) {
      //e.printStackTrace();
    }

then use above list to insert data from list to SQLite like

String str1 = mList.get(position).getMYITEM1();
String str2 = mList.get(position).getMYITEM2();

insert str1 and str2 in SQLite hope you will get idea.

As u mentioned above new row inserted in to sqlite.But i want just check the data using where condition name=optionname.if that optionname corresponding column empty can insert otherwise update — Nivethitha, Apr 28 '16 at 06:04
OK get data from database and then check while object return null or not, if null then update data — Attaullah, Apr 28 '16 at 06:08
Exist function if worked well, if where condition using checked column exist or not that can checked well if exist goes to update otherwise insert function. — Nivethitha, Apr 28 '16 at 09:00

score 0 · Accepted Answer · edited May 23 '17 at 11:46

0

You can use refer this Link. That link explains how to find the email address available in a table or not, you can change the column name, table and pass the values according. In your scenario you want to check the whether the name exists already or not, so you must pass which name you want to find. If the name is there then this method will return true or false. You can validate whether you had to insert or update according the response.i.e., false means you had to insert, otherwise if it is true means then you had to update.

edited May 23 '17 at 11:46

Community

1
1

answered Apr 28 '16 at 05:55

Jeevanandhan

1,073
10
18

I used data exist function that is worked well.Thanks a lot jeeva – Nivethitha Apr 28 '16 at 08:58

score 0 · Answer 5 · answered Mar 26 '17 at 12:04

0

you should

set key for the table, then
insert(if the key existed it will not insert anymore), then
update all row.

answered Mar 26 '17 at 12:04

HoangYell

4,100
37
31

How to check data in SQLite if already exist update or else insert in android

5 Answers5