安卓数据云端同步（一）生成操作Sqlite记录

同步思想：

先介绍客户端为最新数据的数据同步问题，这种机制常常应用在云笔记，随手记类似的app。

这里我建了一个listview，对listview上item的增删改，来代替实际项目中的情况。

1.对listview 和本地数据表datatable表(使用id作为数据唯一表示)映射，实现增删改。

2.每次将对本地数据表的操作记录在operation表中，在表中记录该操作的时间戳。

3.将operation 表中内容封装成json数据发送给服务器（从服务器获取上次同步时间戳，只提交在这次更新以后的操作）。

4.服务器更新完成，返回success,表示同步成功，在时候可以清空operation表

MainActivity.java

package com.example.sqllitcache;

import java.text.SimpleDateFormat;
import java.util.Date;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.DialogInterface;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemLongClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageButton;
import android.widget.ListView;
import android.widget.Toast;
public class MainActivity extends Activity {

	ListView lv;
	Button submit;
	EditText et;
	SQLiteDatabase dbwrite ;
	Dbhelper dbhelper;
	ArrayAdapter <String> adapter; 
	ImageButton upload;
	Cursor c;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        lv=(ListView) findViewById(R.id.listView1);
        submit=(Button) findViewById(R.id.button1);
        upload=(ImageButton) findViewById(R.id.imageButton1);
        et=(EditText) findViewById(R.id.autoCompleteTextView1);
        adapter=new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1);
        lv.setAdapter(adapter);
        dbhelper=new Dbhelper(this);
        dbwrite=dbhelper.getWritableDatabase();
        getData();
        
        //点击提交
        submit.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				String newData=et.getText().toString();
				addData(newData);
			}
		});
        
        //点击了同步
        upload.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				getOp();
			}
		});
        //长按列表项，修改数据
        lv.setOnItemLongClickListener(new OnItemLongClickListener() {
			@Override
			public boolean onItemLongClick(AdapterView<?> arg0, View arg1,
					final int position, long arg3) {
				// TODO Auto-generated method stub
				
				new AlertDialog.Builder(MainActivity.this).setPositiveButton("我确定修改",new DialogInterface.OnClickListener() {
					
					@Override
					public void onClick(DialogInterface dialog, int which) {
						// TODO Auto-generated method stub
						updateData(position);
					}
				}).show();
				return false;
			}
		});
        //短按列表项删除数据
        lv.setOnItemClickListener(new OnItemClickListener() {
			public void onItemClick(AdapterView<?> arg0, View arg1, int position,
					long arg3) {
				// TODO Auto-generated method stub
				Toast.makeText(MainActivity.this, position+"", 0).show();
				deleteData(position);
			}
		});
    }
    
    //添加数据的执行函数，添加到本地数据库，并提示更新listtview
    void addData(String newData)
    {
    	 
    	//添加到本地数据库
    	ContentValues content=new ContentValues();
    	String putdata=et.getText().toString();
        content.put("data", putdata);
        dbwrite.insert("datatable",null, content);
        //下面将操作保存到operation表
        getData();
        c.moveToLast();
        int id=c.getInt(c.getColumnIndex("_id"));
        ContentValues content2=new ContentValues();
        content2.put("data", putdata);
        content2.put("op", 0);
        content2.put("dataid",id );
        long time=System.currentTimeMillis();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date=new Date(time);
        String formattime=sdf.format(date);
        content2.put("timestamp", formattime);
        dbwrite.insert("operation", null, content2);
    }
    
    //删除一条数据
    void deleteData(int position)
    {
    	c.moveToPosition(position);
		int id=c.getInt(c.getColumnIndex("_id"));
		dbwrite.delete("datatable", "_id=?",  new String[]{id+""} );
		getData(); 
		//记录到operation表
		    ContentValues content2=new ContentValues();
	        content2.put("data", "");
	        content2.put("op", 1);
	        content2.put("dataid",id );
	        long time=System.currentTimeMillis();
	        SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	        Date date=new Date(time);
	        String formattime=sdf.format(date);
	        content2.put("timestamp", formattime);
	        dbwrite.insert("operation", null, content2);
		
		
    }
    //修改一条数据
    void updateData(int position)
    {
    	c.moveToPosition(position);
		ContentValues cv=new ContentValues();
		cv.put("data", "changed");
		int id=c.getInt(c.getColumnIndex("_id"));
		dbwrite.update("datatable", cv, "_id=?", new String [] {id+""});
        getData();
        
        ContentValues content2=new ContentValues();
        content2.put("data", "changed");
        content2.put("op", 2);
        content2.put("dataid",id );
        long time=System.currentTimeMillis();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date=new Date(time);
        String formattime=sdf.format(date);
        content2.put("timestamp", formattime);
        dbwrite.insert("operation", null, content2);
    }
    //获取数据
    void getData()
    {
    	c= dbwrite.query("datatable", null, null, null, null, null, null);
    	adapter.clear();
    	 //读取本地数据库 
        while(c.moveToNext())
        {
        	String data=c.getString(c.getColumnIndex("data"));
        	int id=c.getInt(c.getColumnIndex("_id"));
        	adapter.add(data+":"+id);
        	System.out.println(data+": "+id);
        }
        adapter.notifyDataSetChanged();
    }
    //从日志表获得操作记录
    void getOp()
    {
    	c= dbwrite.query("operation", null, null, null, null, null, null);
    	 while(c.moveToNext())
         {
         	String data=c.getString(c.getColumnIndex("data"));
         	int id=c.getInt(c.getColumnIndex("dataid"));
         	int op=c.getInt(c.getColumnIndex("op"));
         	String timestamp=c.getString(c.getColumnIndex("timestamp"));
         	Toast.makeText(MainActivity.this, id+": "+op+"  "+timestamp, 0).show();
         	System.out.println(id+": "+op+"  "+timestamp);
         }
    	 dbwrite.delete("operation", "1", null);
    }
}

Dbhelper.java

package com.example.sqllitcache;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class Dbhelper extends SQLiteOpenHelper {
	public Dbhelper(Context context ) {
		super(context, "mydb",null, 1);
		// TODO Auto-generated constructor stub
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE datatable ("+
		           "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+
		           "data String DEFAULT \"\")"  );
		//创建一个操作表作为日志，dataid为数据项id,opt=0,1,2,3为对数据的增删改查
		db.execSQL("CREATE TABLE operation ("+
		           "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+
				   "dataid INTEGER ,"+
		           "op INTEGER,"+
		           "data String DEFAULT \"\","+
				   "timestamp String"+")");
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}
}