Android 高手进阶教程：[12]SQLiteDatabase

1、新建一个Android工程，命名为SQLiteDatabaseDemo.

2、创建一个新的类BooksDB.java这个类要继承于android.database.sqlite.SQLiteOpenHelper抽象类，我们要实现其中两个方法：onCreate(),onUpdate.具体代码如下：

3、viewplaincopytoclipboar蟠校盯昂dprint?packagecom.android.tutor;importandroid.content.ContentValues;importandroid.content.Context;importandroid.database.Cursor;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;publicclassBooksDBextendsSQLiteOpenHelper{privatefinalstaticStringDATABASE_NAME="BOOKS.db";privatefinalstaticintDATABASE_VERSION=1;privatefinalstaticStringTABLE_NAME="books_table";publicfinalstaticStringBOOK_ID="book_id";publicfinalstaticStringBOOK_NAME="book_name";publicfinalstaticStringBOOK_AUTHOR="book_author";publicBooksDB(Contextcontext){//TODOAuto-generatedconstructorstubsuper(context,DATABASE_NAME,null,DATABASE_VERSION);}//创建table@OverridepublicvoidonCreate(SQLiteDatabasedb){Stringsql="CREATETABLE"+TABLE_NAME+"("+BOOK_ID+"INTEGERprimarykeyautoincrement,"+BOOK_NAME+"text,"+BOOK_AUTHOR+"text);";db.execSQL(sql);}@OverridepublicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){Stringsql="DROPTABLEIFEXISTS"+TABLE_NAME;db.execSQL(sql);onCreate(db);}publicCursorselect(){SQLiteDatabasedb=this.getReadableDatabase();Cursorcursor=db.query(TABLE_NAME,null,null,null,null,null,null);returncursor;}//增加操作publiclonginsert(Stringbookname,Stringauthor){SQLiteDatabasedb=this.getWritableDatabase();/*ContentValues*/ContentValuescv=newContentValues();cv.put(BOOK_NAME,bookname);cv.put(BOOK_AUTHOR,author);longrow=db.insert(TABLE_NAME,null,cv);returnrow;}//删除操作publicvoiddelete(intid){SQLiteDatabasedb=this.getWritableDatabase();Stringwhere=BOOK_ID+"=?";String[]whereValue={Integer.toString(id)};db.delete(TABLE_NAME,where,whereValue);}//修改操作publicvoidupdate(intid,Stringbookname,Stringauthor){SQLiteDatabasedb=this.getWritableDatabase();Stringwhere=BOOK_ID+"=?";String[]whereValue={Integer.toString(id)};ContentValuescv=newContentValues();cv.put(BOOK_NAME,bookname);cv.put(BOOK_AUTHOR,author);db.update(TABLE_NAME,cv,where,whereValue);}}packagecom.android.tutor;importandroid.content.ContentValues;importandroid.content.Context;importandroid.database.Cursor;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;publicclassBooksDBextendsSQLiteOpenHelper{privatefinalstaticStringDATABASE_NAME="BOOKS.db";privatefinalstaticintDATABASE_VERSION=1;privatefinalstaticStringTABLE_NAME="books_table";publicfinalstaticStringBOOK_ID="book_id";publicfinalstaticStringBOOK_NAME="book_name";publicfinalstaticStringBOOK_AUTHOR="book_author";publicBooksDB(Contextcontext){//TODOAuto-generatedconstructorstubsuper(context,DATABASE_NAME,null,DATABASE_VERSION);}//创建table@OverridepublicvoidonCreate(SQLiteDatabasedb){Stringsql="CREATETABLE"+TABLE_NAME+"("+BOOK_ID+"INTEGERprimarykeyautoincrement,"+BOOK_NAME+"text,"+BOOK_AUTHOR+"text);";db.execSQL(sql);}@OverridepublicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){Stringsql="DROPTABLEIFEXISTS"+TABLE_NAME;db.execSQL(sql);onCreate(db);}publicCursorselect(){SQLiteDatabasedb=this.getReadableDatabase();Cursorcursor=db.query(TABLE_NAME,null,null,null,null,null,null);returncursor;}//增加操作publiclonginsert(Stringbookname,Stringauthor){SQLiteDatabasedb=this.getWritableDatabase();/*ContentValues*/ContentValuescv=newContentValues();cv.put(BOOK_NAME,bookname);cv.put(BOOK_AUTHOR,author);longrow=db.insert(TABLE_NAME,null,cv);returnrow;}//删除操作publicvoiddelete(intid){SQLiteDatabasedb=this.getWritableDatabase();Stringwhere=BOOK_ID+"=?";String[]whereValue={Integer.toString(id)};db.delete(TABLE_NAME,where,whereValue);www.linuxidc.com}//修改操作publicvoidupdate(intid,Stringbookname,Stringauthor){SQLiteDatabasedb=this.getWritableDatabase();Stringwhere=BOOK_ID+"=?";String[]whereValue={Integer.toString(id)};ContentValuescv=newContentValues();cv.put(BOOK_NAME,bookname);cv.put(BOOK_AUTHOR,author);db.update(TABLE_NAME,cv,where,whereValue);}}

4、修改main.xml布局如下，由两个EditText和一个ListView组成，代码如下:viewplaincopytoclipboardprint?<?xmlversion="1.0"encoding="utf-8"?><LinearLayoutxmlns:android="http://schemas.android.com/apk/res/android"android:orientation="vertical"android:layout_width="fill_parent"android:layout_height="fill_parent"><EditTextandroid:id="@+id/bookname"android:layout_width="fill_parent"android:layout_height="wrap_content"></EditText><EditTextandroid:id="@+id/author"android:layout_width="fill_parent"android:layout_height="wrap_content"></EditText><ListViewandroid:id="@+id/bookslist"android:layout_width="fill_parent"android:layout_height="wrap_content"></ListView></LinearLayout><?xmlversion="1.0"encoding="utf-8"?><LinearLayoutxmlns:android="http://schemas.android.com/apk/res/android"android:orientation="vertical"android:layout_width="fill_parent"android:layout_height="fill_parent"><EditTextandroid:id="@+id/bookname"android:layout_width="fill_parent"android:layout_height="wrap_content"></EditText><EditTextandroid:id="@+id/author"android:layout_width="fill_parent"android:layout_height="wrap_content"></EditText><ListViewandroid:id="@+id/bookslist"android:layout_width="fill_parent"android:layout_height="wrap_content"></ListView></LinearLayout>bs+�n&�'+@&rt(Stringbookname,Stringauthor){SQLiteDatabasedb=this.getWritableDatabase();/*ContentValues*/ContentValuescv=newContentValues();cv.put(BOOK_NAME,bookname);cv.put(BOOK_AUTHOR,author);longrow=db.insert(TABLE_NAME,null,cv);returnrow;}//删除操作publicvoiddelete(intid){top:'�  0�@&yle="mso-spacerun:'yes'; color:rgb(0,0,0); font-size:14px; font-family:'宋体'; " >publicfinalstaticStringBOOK_ID="book_id";publicfinalstaticStringBOOK_NAME="book_name";publicfinalstaticStringBOOK_AUTHOR="book_author";

5、修改SQLiteDatabaseDemo.java代码如下:viewplaincopytoclipboardprint?packagecom.android.tutor;importandroid.app.Activity;importandroid.content.Context;importandroid.database.Cursor;importandroid.os.Bundle;importandroid.view.Menu;importandroid.view.MenuItem;importandroid.view.View;importandroid.view.ViewGroup;importandroid.widget.AdapterView;importandroid.widget.BaseAdapter;importandroid.widget.EditText;importandroid.widget.ListView;importandroid.widget.TextView;importandroid.widget.Toast;publicclassSQLiteDatabaseDemoextendsActivityimplementsAdapterView.OnItemClickListener{privateBooksDBmBooksDB;privateCursormCursor;privateEditTextBookName;privateEditTextBookAuthor;privateListViewBooksList;privateintBOOK_ID=0;protectedfinalstaticintMENU_ADD=Menu.FIRST;protectedfinalstaticintMENU_DELETE=Menu.FIRST+1;protectedfinalstaticintMENU_UPDATE=Menu.FIRST+2;publicvoidonCreate(BundlesavedInstanceState){super.onCreate(savedInstanceState);setContentView(R.layout.main);setUpViews();}publicvoidsetUpViews(){mBooksDB=newBooksDB(this);mCursor=mBooksDB.select();BookName=(EditText)findViewById(R.id.bookname);BookAuthor=(EditText)findViewById(R.id.author);BooksList=(ListView)findViewById(R.id.bookslist);BooksList.setAdapter(newBooksListAdapter(this,mCursor));BooksList.setOnItemClickListener(this);}@OverridepublicbooleanonCreateOptionsMenu(Menumenu){super.onCreateOptionsMenu(menu);menu.add(Menu.NONE,MENU_ADD,0,"ADD");menu.add(Menu.NONE,MENU_DELETE,0,"DELETE");menu.add(Menu.NONE,MENU_DELETE,0,"UPDATE");returntrue;}publicbooleanonOptionsItemSelected(MenuItemitem){super.onOptionsItemSelected(item);switch(item.getItemId()){caseMENU_ADD:add();break;caseMENU_DELETE:delete();break;caseMENU_UPDATE:update();break;}returntrue;}publicvoidadd(){Stringbookname=BookName.getText().toString();Stringauthor=BookAuthor.getText().toString();//书名和作者都不能为空，或者退出if(bookname.equals("")||author.equals("")){return;}mBooksDB.insert(bookname,author);mCursor.requery();BooksList.invalidateViews();BookName.setText("");BookAuthor.setText("");Toast.makeText(this,"AddSuccessed!",Toast.LENGTH_SHORT).show();}publicvoiddelete(){if(BOOK_ID==0){return;}mBooksDB.delete(BOOK_ID);mCursor.requery();BooksList.invalidateViews();BookName.setText("");BookAuthor.setText("");Toast.makeText(this,"DeleteSuccessed!",Toast.LENGTH_SHORT).show();}publicvoidupdate(){Stringbookname=BookName.getText().toString();Stringauthor=BookAuthor.getText().toString();//书名和作者都不能为空，或者退出if(bookname.equals("")||author.equals("")){return;}mBooksDB.update(BOOK_ID,bookname,author);mCursor.requery();BooksList.invalidateViews();BookName.setText("");BookAuthor.setText("");Toast.makeText(this,"UpdateSuccessed!",Toast.LENGTH_SHORT).show();}@OverridepublicvoidonItemClick(AdapterView<?>parent,Viewview,intposition,longid){mCursor.moveToPosition(position);BOOK_ID=mCursor.getInt(0);BookName.setText(mCursor.getString(1));BookAuthor.setText(mCursor.getString(2));}publicclassBooksListAdapterextendsBaseAdapter{privateContextmContext;privateCursormCursor;publicBooksListAdapter(Contextcontext,Cursorcursor){mContext=context;mCursor=cursor;}@OverridepublicintgetCount(){returnmCursor.getCount();}@OverridepublicObjectgetItem(intposition){returnnull;}"J�sa�'+@&s'; color:rgb(0,0,0); font-size:14px; font-family:'宋体'; " >BookName.setText("");BookAuthor.setText("");Toast.makeText(this,"DeleteSuccessed!",Toast.LENGTH_SHORT).show();}@OverridepubliclonggetItemId(intposition){return0;}@OverridepublicViewgetView(intposition,ViewconvertView,ViewGroupparent){TextViewmTextView=newTextView(mContext);mCursor.moveToPosition(position);mTextView.setText(mCursor.getString(1)+"___"+mCursor.getString(2));returnmTextView;}}}packagecom.android.tutor;importandroid.app.Activity;importandroid.content.Context;importandroid.database.Cursor;importandroid.os.Bundle;importandroid.view.Menu;importandroid.view.MenuItem;importandroid.view.View;importandroid.view.ViewGroup;importandroid.widget.AdapterView;importandroid.widget.BaseAdapter;importandroid.widget.EditText;importandroid.widget.ListView;importandroid.widget.TextView;importandroid.widget.Toast;publicclassSQLiteDatabaseDemoextendsActivityimplementsAdapterView.OnItemClickListener{privateBooksDBmBooksDB;privateCursormCursor;privateEditTextBookName;privateEditTextBookAuthor;privateListViewBooksList;privateintBOOK_ID=0;protectedfinalstaticintMENU_ADD=Menu.FIRST;protectedfinalstaticintMENU_DELETE=Menu.FIRST+1;protectedfinalstaticintMENU_UPDATE=Menu.FIRST+2;publicvoidonCreate(BundlesavedInstanceState){super.onCreate(savedInstanceState);setContentView(R.layout.main);setUpViews();}publicvoidsetUpViews(){mBooksDB=newBooksDB(this);mCursor=mBooksDB.select();BookName=(EditText)findViewById(R.id.bookname);BookAuthor=(EditText)findViewById(R.id.author);BooksList=(ListView)findViewById(R.id.bookslist);BooksList.setAdapter(newBooksListAdapter(this,mCursor));BooksList.setOnItemClickListener(this);}@OverridepublicbooleanonCreateOptionsMenu(Menumenu){super.onCreateOptionsMenu(menu);menu.add(Menu.NONE,MENU_ADD,0,"ADD");menu.add(Menu.NONE,MENU_DELETE,0,"DELETE");menu.add(Menu.NONE,MENU_DELETE,0,"UPDATE");returntrue;}publicbooleanonOptionsItemSelected(MenuItemitem){super.onOptionsItemSelected(item);switch(item.getItemId()){caseMENU_ADD:add();break;caseMENU_DELETE:delete();break;caseMENU_UPDATE:update();break;}returntrue;@OverridepublicvoidonItemClick(AdapterView<?>parent,Viewview,intposition,longid){mCursor.moveToPosition(position);BOOK_ID=mCursor.getInt(0);BookName.setText(mCursor.getString(1));BookAuthor.setText(mCursor.getString(2));}publicclassBooksListAdapterextendsBaseAdapter{privateContextmContext;privateCursormCursor;publicBooksListAdapter(Contextcontext,Cursorcursor){mContext=context;mCursor=cursor;}@OverridepublicintgetCount(){returnmCursor.getCount();}@OverridepublicObjectgetItem(intposition){returnnull;}@OverridepubliclonggetItemId(intposition){return0;}@OverridepublicViewgetView(intposition,ViewconvertView,ViewGroupparent){TextViewmTextView=newTextView(mContext);mCursor.moveToPosition(position);mTextView.setText(mCursor.getString(1)+"___"+mCursor.getString(2));returnmTextView;}}

6、运行程序效果如下:

7、查看我们所建的数据库。有两种方法：第一种用命令查看：adb shell lsdata/data/com.android.tutor/databases。

8、另一种方法是用DDMS查看，data/data下面对应的应用程序的包名下会有如在下数据库，如图所示：