How to update sqlite table if records exist else insert?

Question

This may be duplicate question but am confused as am new for sql and android am getting response from server need to save it in sqlite db if values in the table already exist update else insert il check with task id which is unique don't know how to do this i have searched and did lots of modes but none of that works can you please help me!! here let me post my code:

Here is model class:

import java.io.Serializable;

/**
 * Created by 4264 on 02-02-2016.
 */
public class Model_Task_List implements Serializable {


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int id;
    public String UserName;
    public String Subject;
    public String TaskStatus;

    public int getTaskID() {
        return TaskID;
    }

    public void setTaskID(int taskID) {
        TaskID = taskID;
    }

    public int TaskID;
    public static String KEY_table="task";
    public  static String KEY_id="id";
    public  static String KEY_username="name";
    public  static String KEY_subject="subject";
    public  static String KEY_task="status";
    public static String KEY_taskid="taskid";
    public static String KEY_owner="owner";

    public String getUserid() {
        return userid;
    }

    public void setUserid(String userid) {
        this.userid = userid;
    }

    public String userid;

    public String getSubject() {
        return Subject;
    }

    public void setSubject(String subject) {
        Subject = subject;
    }

    public String getTaskStatus() {
        return TaskStatus;
    }

    public void setTaskStatus(String taskStatus) {
        TaskStatus = taskStatus;
    }

    public String getUserName() {
        return UserName;
    }

    public void setUserName(String userName) {
        UserName = userName;
    }

    public Model_Task_List(String subject, String taskStatus, String userName) {
        Subject = subject;
        TaskStatus = taskStatus;
        UserName = userName;
    }
    public Model_Task_List(){

    }
}

Here is my db queries:

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

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


    public class Database_SF_APP  extends SQLiteOpenHelper {
    Context context;
    private static final int DATABASE_VERSION = 4;
    private static final String DATABASE_NAME = "crud.db";

    public Database_SF_APP(Context context ) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE_TASK = "CREATE TABLE " + Model_Task_List.KEY_table  + "("
                + Model_Task_List.KEY_id  + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
                + Model_Task_List.KEY_username + " TEXT, "
                + Model_Task_List.KEY_subject + " TEXT, "
                + Model_Task_List.KEY_task + " TEXT, "
                +Model_Task_List.KEY_taskid+ " INTEGER, "
                +Model_Task_List.KEY_owner+ " TEXT"
                + ");";

        db.execSQL(CREATE_TABLE_TASK);

    }

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

    }


    public void insert(Model_Task_List modelobj) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(Model_Task_List.KEY_username, modelobj.getUserName());
        values.put(Model_Task_List.KEY_subject,modelobj.getSubject());
        values.put(Model_Task_List.KEY_task, modelobj.getTaskStatus());
        values.put(Model_Task_List.KEY_taskid,modelobj.getTaskID());
        values.put(Model_Task_List.KEY_owner,modelobj.getUserid());
        db.insert(Model_Task_List.KEY_table, null, values);
        db.close();

        }

        public List<Model_Task_List> getTaskListById(String id) {
            SQLiteDatabase db = this.getReadableDatabase();
            String selectQuery =  " SELECT  " +
                    Model_Task_List.KEY_id + "," +
                    Model_Task_List.KEY_username + "," +
                    Model_Task_List.KEY_subject + "," +
                    Model_Task_List.KEY_task + "," +
                    Model_Task_List.KEY_taskid  + ","+
                    Model_Task_List.KEY_owner +
                    " FROM " + Model_Task_List.KEY_table + " WHERE "   +  Model_Task_List.KEY_owner + "= " + id +"";
            List<Model_Task_List>listobj=new ArrayList<Model_Task_List>();
            Cursor cursor = db.rawQuery(selectQuery, null);
            if (cursor.moveToFirst()) {
                while (cursor.moveToNext()) {
                    Model_Task_List modelobj=new Model_Task_List();
                    modelobj.setId(Integer.parseInt(cursor.getString(0)));
                    modelobj.setUserName(cursor.getString(1));
                    modelobj.setSubject(cursor.getString(2));
                    modelobj.setTaskStatus(cursor.getString(3));
                    modelobj.setTaskID(cursor.getShort(4));
                    modelobj.setUserid(cursor.getString(5));
                    listobj.add(modelobj);

                }
            }
            return listobj;

        }


    public List<Model_Task_List> getTaskList() {
        Model_Task_List model_task_list=new Model_Task_List();
        SQLiteDatabase db = this.getReadableDatabase();
        String selectQuery =  "SELECT  " +
                Model_Task_List.KEY_id + "," +
                Model_Task_List.KEY_username + "," +
                Model_Task_List.KEY_subject + "," +
                Model_Task_List.KEY_task +"," +
                Model_Task_List.KEY_taskid  +
                " FROM " + Model_Task_List.KEY_table;
            List<Model_Task_List>listobj=new ArrayList<Model_Task_List>();
            Cursor cursor = db.rawQuery(selectQuery, null);
            if (cursor.moveToFirst()) {
            while (cursor.moveToNext()) {
                Model_Task_List modelobj=new Model_Task_List();
                modelobj.setId(Integer.parseInt(cursor.getString(0)));
                modelobj.setUserName(cursor.getString(1));
                modelobj.setSubject(cursor.getString(2));
                modelobj.setTaskStatus(cursor.getString(3));
                modelobj.setTaskID(cursor.getShort(4));
                listobj.add(modelobj);

            }
        }
        return listobj;

    }
    public int getTaskCount() {
        int count=0;
        String countQuery = "SELECT  * FROM " + Model_Task_List.KEY_table;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        if(cursor != null && !cursor.isClosed()){
            count = cursor.getCount();
            cursor.close();
        }
        return count;

    }
    public void update(Model_Task_List student) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(Model_Task_List.KEY_username, student.getUserName());
        values.put(Model_Task_List.KEY_subject,student.getSubject());
        values.put(Model_Task_List.KEY_task, student.getTaskStatus());
        values.put(Model_Task_List.KEY_taskid,student.getTaskID());
        db.update(Model_Task_List.KEY_table, values, Model_Task_List.KEY_taskid + "= ?", new String[]{String.valueOf(student.getTaskID())});
        db.close();
    }

    }

Here is the code where is need to check where to update or insert :

import android.content.Context;
import android.content.SharedPreferences;
import android.net.ConnectivityManager;
import android.net.NetworkInfo;
import android.os.Bundle;
import android.support.v4.app.Fragment;
import android.support.v7.widget.DefaultItemAnimator;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Toast;

import com.android.volley.Request;
import com.android.volley.RequestQueue;
import com.android.volley.Response;
import com.android.volley.VolleyError;
import com.android.volley.toolbox.JsonObjectRequest;
import com.android.volley.toolbox.Volley;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

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

/**
 * Created by 4264 on 27-01-2016.
 */
public class Task extends Fragment  {
    private static final String MY_PREFERENCE_KEY = "yogan";
    private List<Model_Task_List> model_task_lists;

    //Creating Views
    String currentDateTimeString;
    Context context;
    SharedPreferences.Editor editor;
    private RecyclerView recyclerView;
    Task_List_Adapter taskadapter;
    private RecyclerView.LayoutManager layoutManager;
    SharedPreferences sharedPreferences;
    private RecyclerView.Adapter adapter;
    RequestQueue yog;
    String user_id;
    AppController app;
    RequestQueue queue;
    String Url;
    Task_DB task_db = null;
    Database_SF_APP database_sf_app;

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        View view = inflater.inflate(R.layout.fragment_task, container, false);
        recyclerView = (RecyclerView) view.findViewById(R.id.my_recycler_view);
        LinearLayoutManager layoutManager = new LinearLayoutManager(getContext());
        layoutManager.setOrientation(LinearLayoutManager.VERTICAL);
        recyclerView.setItemAnimator(new DefaultItemAnimator());
        recyclerView.setLayoutManager(layoutManager);
        if (model_task_lists == null) {
            model_task_lists = new ArrayList<Model_Task_List>();
        }
        database_sf_app = new Database_SF_APP(getActivity().getBaseContext());
        int count=database_sf_app.getTaskCount();
        sharedPreferences = getActivity().getSharedPreferences(LoginActivity.login, 0);
        user_id = sharedPreferences.getString("user_id", null);
        queue = Volley.newRequestQueue(getContext());
        String date="2016-02-09T19:59:27.9434387+05:30";
        Url = "http://xxx.xx.x.xx/xxx/GetActivitiesByUserID.svc/getlist/getTask/" + user_id ;
        ConnectivityManager cn = (ConnectivityManager) getActivity().getSystemService(Context.CONNECTIVITY_SERVICE);
        NetworkInfo nf = cn.getActiveNetworkInfo();
        if (nf != null && nf.isConnected()) {
            Toast.makeText(getActivity(), "Network Available", Toast.LENGTH_LONG).show();
            JsonObjectRequest jsonObjRequest = new JsonObjectRequest(Request.Method.GET, Url, new JSONObject(),
                    new Response.Listener<JSONObject>() {

                        @Override
                        public void onResponse(JSONObject response) {
                            String server_response = response.toString();
                            try {
                                JSONObject json_object = new JSONObject(server_response);
                                JSONArray json_array = new JSONArray(json_object.getString("GetTaskResult"));
                                for (int i = 0; i < json_array.length(); i++) {
                                    Model_Task_List modelobj = new Model_Task_List();
                                    JSONObject json_arrayJSONObject = json_array.getJSONObject(i);
                                    modelobj.setSubject(json_arrayJSONObject.getString("Subject"));
                                    modelobj.setTaskID(json_arrayJSONObject.getInt("TaskID"));

                                    modelobj.setUserName(json_arrayJSONObject.getString("DueDate"));
                                    modelobj.setTaskStatus(json_arrayJSONObject.getString("TaskStatus"));
                                    modelobj.setUserid(json_arrayJSONObject.getString("Owner"));
                                    model_task_lists.add(modelobj);
                                  ///Here i need to check 

                                    database_sf_app.update(modelobj);
                                    taskadapter = new Task_List_Adapter(model_task_lists, getContext());
                                    recyclerView.setAdapter(taskadapter);
                                }
                            } catch (JSONException e) {
                                e.printStackTrace();
                            }

                        }
                    },
                    new Response.ErrorListener() {
                        @Override
                        public void onErrorResponse(VolleyError error) {
                            Toast.makeText(getContext(), error.toString(), Toast.LENGTH_SHORT).show();
                            Log.e("error", error.toString());
                        }
                    });

            //Creating request queue

            queue.add(jsonObjRequest);

        }

        //sync operation
        //a union b
        //a server
        //b local storage

        //a only - insert local
        //b only - send to server
        //a = b do nothing

        //result
        //bind

        model_task_lists=database_sf_app.getTaskListById(user_id);
        Log.e("Test",model_task_lists.toString());
        taskadapter=new Task_List_Adapter(model_task_lists,getActivity());
        recyclerView.setAdapter(taskadapter);

        return view;
    }


    @Override
    public void onResume()
    {
            super.onResume();
    }


    @Override
    public void onStop() {

        super.onStop();
    }

}

Like This am checking whether is there any task id is already present in table if yes update or insert :

public void insertOrUpdate(Model_Task_List modelobj)
{
            String query=" SELECT  * FROM " + Model_Task_List.KEY_table+ " WHERE" +Model_Task_List.KEY_taskid+  " =" +modelobj.getTaskID();
            SQLiteDatabase db=this.getReadableDatabase();
            ContentValues values=new ContentValues();
            Cursor cursor=db.rawQuery(query, null);
            if(cursor.moveToFirst()){
                update(modelobj);
            }
             else
            {
                insert(modelobj);
            }
}

Possible duplicate of [On Duplicate Key not working in SQLite](http://stackoverflow.com/questions/23622504/on-duplicate-key-not-working-in-sqlite) — waki, Feb 10 '16 at 04:03

score 1 · Answer 1 · answered Feb 10 '16 at 09:42

The update() function returns how many rows were affected. So if there were none, you know that you must insert instead:

void updateIfExistsElseInsert(...) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = ...;
    values.put( /* all except the ID */ );
    int rows = db.update(KEY_TABLE, values, KEY_ID + " = " + id, null);
    if (rows == 0) {
        values.put(KEY_ID, id);
        db.insert(KEY_TABLE, null, values);
    }
}

use insertOrThrow, because it will throw exception. if any error occur during insertion of new record. — jessica, Mar 16 '17 at 12:49

score 0 · Answer 2 · answered Feb 10 '16 at 05:18

you must check if it is exist. Do it like this code:

 private Cursor selectOneItem(int id) {
    String strQuery = String.format(
            "SELECT * FROM %s WHERE %s = %s", Model_Task_List.KEY_table,
            Model_Task_List.KEY_id, id);
    Cursor cur = db.rawQuery(strQuery , null);
    return cur;
}

use above code to your insert function like below:

 public void insert(Model_Task_List modelobj) {
         Cursor cur = selectOneNews(modelobj.getTaskID());
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(Model_Task_List.KEY_username, modelobj.getUserName());
        values.put(Model_Task_List.KEY_subject,modelobj.getSubject());
        values.put(Model_Task_List.KEY_task, modelobj.getTaskStatus());
        values.put(Model_Task_List.KEY_taskid,modelobj.getTaskID());
        values.put(Model_Task_List.KEY_owner,modelobj.getUserid());
        if (cur == null || cur.getCount() == 0) {
                    db.insert(Model_Task_List.KEY_table, null, values);
                } else {
                    String[] args = {modelobj.getTaskID() + ""};
                    db.update(Model_Task_List.KEY_table, values,
                            Model_Task_List.KEY_id + "=?", args);
                }
        db.close();

    }

score -1 · Answer 3 · edited May 23 '17 at 11:46

replace is just like insert, it just checks if there is duplicate key and if it is it deletes the row, and inserts the new one, otherwise it just inserts

you can do this if there is for example unique index of (Model_Task_List.KEY_taskid) and if you type the following command

REPLACE INTO Model_Task_List.KEY_table(Model_Task_List.KEY_taskid,Model_Task_List.KEY_task,Model_Task_List.KEY_username,Model_Task_List.KEY_subject) VALUES ('111',3,50,90 )

and there already exists a row with Model_Task_List.KEY_taskid= '111' it will be replaced

CREATE UNIQUE INDEX idx_name_type ON Model_Task_List.KEY_table(Model_Task_List.KEY_taskid)

EDIT: a quick note - REPLACE always DELETES and then INSERTs, so it is never a very good idea to use it in heavy load because it needs exclusive lock when it deletes, and then when it inserts

some of the database engines have

INSERT ... ON DUPLICATE KEY UPDATE ...

Link

i dint mean any constraint need to check if value exist if not insert else update i dint impose any constraints — Yog, Feb 10 '16 at 04:27

How to update sqlite table if records exist else insert?

3 Answers3

Linked

Related