SQLite实例――dummynote(记事本)

package com.example.dummynote; import android.os.Bundle; import android.support.v4.widget.SimpleCursorAdapter; import android.util.Log; import android.view.ContextMenu; import android.view.ContextMenu.ContextMenuInfo; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.R.bool; import android.app.ListActivity; import android.content.Intent; import android.database.Cursor; import android.widget.AdapterView; import android.widget.ArrayAdapter; import android.widget.ListAdapter; import android.widget.ListView; public class DummyNote extends ListActivity { private static final String TAG = "notes"; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); getListView().setEmptyView(findViewById(R.id.empty));//设置列表为空时的显示内容为 empty文本框 registerForContextMenu(getListView()); //调用 registerForContextMenu() 方法，为视图注册上下文菜单。 setAdapter(); } /* private String[] note_array={ "hangsome", "lushichuanshuo", "android", "library" }; */ private NotesDbAdapter mDbHelper ; private Cursor mNoteCursor; private void setAdapter(){ mDbHelper = new NotesDbAdapter(this); mDbHelper.open(); fillData(); } //fillData()刷新数据 private void fillData(){ mNoteCursor = mDbHelper.getall(); startManagingCursor(mNoteCursor); String[] from = new String[]{NotesDbAdapter.NOTE}; int[] to = new int[]{android.R.id.text1};//android.R.id.text1是Android 框架里面的TextView的1个标识符 //Now create a simple cursor adapter SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, mNoteCursor, from, to); setListAdapter(adapter); } //Add an entity private int mNoteNumber = 1; protected static final int MENU_INSERT = Menu.FIRST; protected static final int MENU_DELETE = Menu.FIRST+1; @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. super.onCreateOptionsMenu(menu); menu.add(0, MENU_INSERT, 0, "新增记事"); menu.add(0, MENU_DELETE, 0, "删除记事"); return super.onCreateOptionsMenu(menu); } public boolean onOptionsItemSelected(MenuItem item){ switch(item.getItemId()){ case MENU_INSERT: String noteName = "Note "+ mNoteNumber++; mDbHelper.create(noteName); fillData(); return true; case MENU_DELETE: mDbHelper.delete(getListView().getSelectedItemId()); fillData(); return true; } return super.onOptionsItemSelected(item); } //NoteEdit Activity的跳转设置 private static final int ACTIVITY_EDIT = 0x1001; @Override protected void onListItemClick(ListView l, View v, int position, long id) { super.onListItemClick(l, v, position, id); Intent intent = new Intent(this,NoteEdit.class); intent.putExtra(NotesDbAdapter.ROWID, id);//id代表绑定到这个“ListView”项目的“row ID” startActivityForResult(intent, ACTIVITY_EDIT); } //startActivityForResult()和onActivityResult()方法是配套使用。 //前者负责调用其他Activity //猴子负责处理从其他Activity返回的消息 @Override protected void onActivityResult(int requestCode, int resultCode, Intent intent) { // TODO Auto-generated method stub super.onActivityResult(requestCode, resultCode, intent); fillData(); } //长按删除 @Override public void onCreateContextMenu(ContextMenu menu, View v, ContextMenuInfo menuInfo) { menu.add(0, MENU_DELETE, 0, "删除记事"); menu.setHeaderTitle("要如何处理这笔记录?"); super.onCreateContextMenu(menu, v, menuInfo); } @Override public boolean onContextItemSelected(MenuItem item) { AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo(); switch(item.getItemId()){ case MENU_DELETE: mDbHelper.delete(info.id); fillData(); break; } return super.onContextItemSelected(item); } }

package com.example.dummynote; import java.util.Date; import android.content.ContentValues; import android.content.Context; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.*; import android.util.Log; public class NotesDbAdapter { private static final String TAG = "notes"; private static final String DATABASE_NAME = "notes.db"; private static final int DATABASE_VERSION = 1 ; private static final String DATABASE_TABLE = "notes"; private static final String DATABASE_CREATE = "create table notes(" +"_id INTEGER PRIMARY KEY," +"note TEXT NOT NULL," +"created INTEGER" +");"; private static class DatabaseHelper extends SQLiteOpenHelper{ public DatabaseHelper(Context context) { super(context, DATABASE_NAME,null,DATABASE_VERSION); // TODO Auto-generated constructor stub } @Override//创建数据表 public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub try { db.execSQL(DATABASE_CREATE); Log.d(TAG, "onCreate !"); } catch (Exception e) { Log.d(TAG, e.toString()); } } @Override//更新数据表 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { try { // TODO Auto-generated method stub db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE); onCreate(db); Log.d(TAG, "inUpgrade!"); } catch (Exception e) { Log.d(TAG, "onUpgrade failure!"); } } } private Context mCtx = null; //抽象界面 private DatabaseHelper dbHelper; //数据库工具类 private SQLiteDatabase db; //数据库类 /** COnstructor**/ public NotesDbAdapter(Context ctx){ this.mCtx=ctx; } public NotesDbAdapter open () throws SQLException{ dbHelper = new DatabaseHelper(mCtx); db = dbHelper.getWritableDatabase();//数据库不存在就创造1个，若存在就根据版本库来决定是不是更新数据库 return this; } public void close(){ dbHelper.close(); } public static final String ROWID = "_id"; public static final String NOTE = "note"; public static final String CREATED = "created"; //query single entry public Cursor get(long rowId)throws SQLException { Cursor mCursor = db.query(DATABASE_TABLE,//Which table to select new String[]{ROWID,NOTE,CREATED},//Which columns to return ROWID+"="+rowId, //Where clause null, //Where arguments null, //Group By clause null, //Having clause null //Order-by clause ); if(mCursor!=null){ mCursor.moveToFirst();//指针移到1开始 } return mCursor; } //query single entry public Cursor getall() { return db.query(DATABASE_TABLE,//Which table to select new String[]{ROWID,NOTE,CREATED},//Which columns to return null, //Where clause null, //Where arguments null, //Group By clause null, //Having clause null //Order-by clause ); } //add an entity public long create(String Note){ Date now = new Date(); ContentValues args = new ContentValues(); args.put(NOTE, Note); args.put(CREATED, now.getDate()); return db.insert(DATABASE_TABLE, null, args); } //remove an entity public boolean delete(long rowId){ return db.delete(DATABASE_TABLE,ROWID+"="+rowId,null)>0;//delete失败返回0 } //update an entity public boolean update(long rowId,String note){ ContentValues args = new ContentValues(); args.put(NOTE, note); return db.update(DATABASE_TABLE, args, ROWID+"="+rowId, null)>0; } }

package com.example.dummynote; import android.app.Activity; import android.database.Cursor; import android.os.Bundle; import android.view.View; import android.widget.Button; import android.widget.EditText; public class NoteEdit extends Activity{ private NotesDbAdapter mDbHelper; @Override protected void onCreate(Bundle SavedInstanceState){ super.onCreate(SavedInstanceState); mDbHelper = new NotesDbAdapter(this); mDbHelper.open(); setContentView(R.layout.note_edit); findViews(); showViews(SavedInstanceState); } private EditText field_note; private Button button_confirm; private void findViews() { // TODO Auto-generated method stub field_note = (EditText) findViewById(R.id.note); button_confirm = (Button) findViewById(R.id.confirm); } private Long mRowId; private void showViews(Bundle savedInstanceState) { mRowId = savedInstanceState != null? savedInstanceState.getLong(NotesDbAdapter.ROWID) : null; /*saveInstanceState 这个“bundle”数据容器中，取出Activity上1次处于“stop”状态是，键值为“_id”(NotesDbAdapter.ROWID)的内容值。如果这个ACtivity是 全新打开的或是之前的行程已被回收了，那末“mRouId”的值被设为“null”*/ if (mRowId == null) { Bundle extras = getIntent().getExtras(); mRowId = extras != null ? extras.getLong(NotesDbAdapter.ROWID) : null; } populateFields();//根据RowId查询记录，并显示在EditText中 button_confirm.setOnClickListener(new View.OnClickListener(){ @Override public void onClick(View v) { mDbHelper.update(mRowId, field_note.getText().toString()); setResult(RESULT_OK); finish(); } }); } private void populateFields() { if (mRowId != null){ Cursor note = mDbHelper.get(mRowId); startManagingCursor(note); field_note.setText(note.getString(note.getColumnIndexOrThrow(NotesDbAdapter.NOTE))); } } }