如果记录存在，如何插入sqlite表? [英] How to update sqlite table if records exist else insert?
本文介绍了如果记录存在，如何插入sqlite表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这可能是重复的问题，但是由于sql和android的新功能而感到困惑，如果表中的值已经存在，则服务器响应会需要将其保存在sqlite db中更新；否则，请插入带有任务ID的il check，这是唯一的do'我不知道该怎么做，我已经搜索过并做了很多模式，但是这些都不起作用，请您帮帮我！！在这里，让我发布我的代码:
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:
这是模型类别:
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(){
}
}
这是我的数据库查询:
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();
}
}
像这样，如果要更新或插入，请检查表中是否已存在任何任务ID:
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);
}
}
推荐答案
update()函数返回受影响的行数.因此，如果没有，则您必须改为插入:
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);
}
}
这篇关于如果记录存在，如何插入sqlite表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
