Android本地数据库增删改查 菜单实现

本内容基于《Beginning Android 4 Application Development》Wei-Meng Lee中文版6.3节(P264) 的 创建和使用数据库修改而成

原文为对表contact的修改，这里我将其改为菜谱的实现

原表为：

_idnameemail      将其修改为：
_idnameingredient1泡芙1. 材料：低粉、鸡蛋...2蛋挞1. 材料：精粉、黄油...3双皮奶1. 材料：纯牛奶、白...

使用DB Browser for SQLite 创建该表，SQL语句为：

CREATE TABLE `contacts` (    `_id`    INTEGER,    `name`    TEXT,    `ingredient`    TEXT,    PRIMARY KEY(_id));

注：如果您使用多表查询，可以做相应的修改，这里只涉及对一个表的修改，多表操作可能需要参考其他一些内容。

开发软件是直接下载的 adt-bundle-windows-x86_64-20131030，可以直接下载解压使用。

代码结构如下：

manifest清单文件：

<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"    package="net.learn2develop.Databases"    android:versionCode="1"    android:versionName="1.0" >    <uses-sdk android:minSdkVersion="10" />    <application        android:icon="@drawable/ic_launcher"        android:label="@string/app_name" >        <activity            android:label="@string/app_name"            android:name=".DatabasesActivity" >            <intent-filter >                <action android:name="android.intent.action.MAIN" />                <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>        <activity            android:label="@string/app_name"            android:name=".AddItemActivity" >                    </activity>    </application></manifest>

两个xml文件：

1. main.xml文件(字符串应当写到res目录下的strings.xml文件中，这里没有写，为了以后的全球化，您可以将其写入strings.xml文件中)：

<?xml version="1.0" encoding="utf-8"?><RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:id="@+id/RelativeLayout1"    android:layout_width="fill_parent"    android:layout_height="fill_parent"    android:orientation="vertical" >    <AutoCompleteTextView        android:id="@+id/autoCompleteTextView1"        android:layout_width="253dp"        android:layout_height="wrap_content"        android:layout_alignParentLeft="true"        android:layout_alignParentTop="true"        android:ems="10"        android:text="输入甜品名称" >        <requestFocus />    </AutoCompleteTextView>    <Button        android:id="@+id/button1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBaseline="@+id/autoCompleteTextView1"        android:layout_alignBottom="@+id/autoCompleteTextView1"        android:layout_alignParentRight="true"        android:text="搜索" />    <EditText        android:id="@+id/editText1"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_alignParentBottom="true"        android:layout_alignParentLeft="true"        android:layout_alignParentRight="true"        android:layout_below="@+id/button1"        android:ems="10"        android:inputType="textMultiLine" /></RelativeLayout>

2. 点击菜单选项“新增”之后，在新的页面中分别在editTxt_Title中填写菜品名(name)， 在editTxt_Content填写菜品配方(ingredient)。

<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:orientation="vertical" >    <EditText        android:id="@+id/editTxt_Title"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:ems="10"        android:inputType="text" />    <EditText        android:id="@+id/editTxt_Content"        android:layout_width="match_parent"        android:layout_height="match_parent"        android:ems="10"        android:inputType="textMultiLine" >        <requestFocus />    </EditText></LinearLayout>

三个.java文件

DBAdapter.java

package net.learn2develop.Databases;/** * @author 作者1Wei-Meng Lee * @version 创建时间：不详 * * @author 作者2：宋倩楠  &  blog.csdn.net/haimian520 * @version 创建时间：2015年11月18日  * */ import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;public class DBAdapter {    static final String KEY_ROWID = "_id";    static final String KEY_NAME = "name";    static final String KEY_INGREDIENT = "ingredient";    static final String TAG = "DBAdapter";    static final String DATABASE_NAME = "MyDB";    static final String DATABASE_TABLE = "contacts";    static final int DATABASE_VERSION = 2;    static final String DATABASE_CREATE =        "create table contacts (_id integer primary key autoincrement, "        + "name text not null, ingredient text not null);";    final Context context;    DatabaseHelper DBHelper;    SQLiteDatabase db;        public DBAdapter(Context ctx)    {        this.context = ctx;        DBHelper = new DatabaseHelper(context);    }    private static class DatabaseHelper extends SQLiteOpenHelper    {        DatabaseHelper(Context context)        {            super(context, DATABASE_NAME, null, DATABASE_VERSION);        }        @Override        public void onCreate(SQLiteDatabase db)        {            try {                db.execSQL(DATABASE_CREATE);            } catch (SQLException e) {                e.printStackTrace();            }        }        @Override        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)        {            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "                    + newVersion + ", which will destroy all old data");            db.execSQL("DROP TABLE IF EXISTS contacts");            onCreate(db);        }    }    //---opens the database---    public DBAdapter open() throws SQLException     {        db = DBHelper.getWritableDatabase();        return this;    }    //---closes the database---    public void close()     {        DBHelper.close();    }    //---insert a contact into the database---    public long insertContact(String name, String ingredient)     {        ContentValues initialValues = new ContentValues();        initialValues.put(KEY_NAME, name);        initialValues.put(KEY_INGREDIENT, ingredient);        return db.insert(DATABASE_TABLE, null, initialValues);    }    //---deletes a particular contact---    public boolean deleteContact(long rowId)     {        return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;    }    //---deletes a particular contact---    public boolean deleteContact(String str)     {        return db.delete(DATABASE_TABLE, KEY_NAME +"='" + str+"'", null) > 0;    }        //---retrieves all the contacts---    public Cursor getAllContacts()    {        return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,                KEY_INGREDIENT}, null, null, null, null, null);    }    //---retrieves a particular contact---    public Cursor getContact(long rowId) throws SQLException     {        Cursor mCursor =                db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,                KEY_NAME, KEY_INGREDIENT}, KEY_ROWID + "=" + rowId, null,                null, null, null, null);        if (mCursor != null) {            mCursor.moveToFirst();        }        return mCursor;    }    //---updates a contact---    public boolean updateContact(long rowId, String name, String ingredient)     {        ContentValues args = new ContentValues();        args.put(KEY_NAME, name);        args.put(KEY_INGREDIENT, ingredient);//        return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;        return db.update(DATABASE_TABLE, args, KEY_NAME + "='" + name+"'", null) > 0;    }    public Cursor getContact(String string)throws SQLException  {// select * from contacts where name="蛋挞"// TODO Auto-generated method stub Cursor mCursor =                db.query(true, DATABASE_TABLE, null, KEY_NAME + "='" + string+"'", null,                null, null, null, null);        if (mCursor != null) {            mCursor.moveToFirst();        }return mCursor;}public Cursor getFoodName() throws SQLException{return db.query(DATABASE_TABLE, new String[] {KEY_NAME}, null, null, null, null, null);}// 通过特定名字，找到该行IDpublic Cursor getRowID(String name) throws SQLException{return db.query(DATABASE_TABLE, new String[] {KEY_NAME}, null, null, null, null, null);}}

DatabasesActivity.java

package net.learn2develop.Databases;/** * @author 作者1Wei-Meng Lee * @version 创建时间：不详 * * @author 作者2：宋倩楠  &  blog.csdn.net/haimian520 * @version 创建时间：2015年11月18日  * */ import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.lang.reflect.Method;import java.util.ArrayList;import android.app.Activity;import android.app.AlertDialog;import android.app.AlertDialog.Builder;import android.content.DialogInterface;import android.content.Intent;import android.database.Cursor;import android.os.Bundle;import android.util.Log;import android.view.Menu;import android.view.MenuItem;import android.view.View;import android.view.View.OnClickListener;import android.widget.AdapterView;import android.widget.AdapterView.OnItemClickListener;import android.widget.ArrayAdapter;import android.widget.AutoCompleteTextView;import android.widget.Button;import android.widget.DialerFilter;import android.widget.EditText;import android.widget.Toast; public class DatabasesActivity extends Activity implements OnClickListener,OnItemClickListener{    /** Called when the activity is first created. */Button btn1, btn2, btn3;AutoCompleteTextView autoCTV;EditText editTxt;static String[] autoString ;ArrayAdapter<String> arrAdapter;static ArrayList<String> list;private static final int ITEM1 = Menu.FIRST;private static final int ITEM2 = Menu.FIRST+1;private static final int ITEM3 = Menu.FIRST+2;    @Override    public void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.main);        btn1 = (Button) findViewById(R.id.button1);//btn2 = (Button) findViewById(R.id.button2);//btn3 = (Button) findViewById(R.id.button3);//btn1.setOnClickListener(this);//btn2.setOnClickListener(this);//btn3.setOnClickListener(this);//                autoCTV = (AutoCompleteTextView)findViewById(R.id.autoCompleteTextView1);        editTxt = (EditText)findViewById(R.id.editText1);        DBAdapter db = new DBAdapter(this);        /*        //---add a contact---        db.open();        long id = db.insertContact("Wei-Meng Lee", "[e-mail]");        id = db.insertContact("Mary Jackson", "[e-mail]");        db.close();        */                /*        //--get all contacts---        db.open();        Cursor c = db.getAllContacts();        if (c.moveToFirst())        {            do {                DisplayContact(c);            } while (c.moveToNext());        }        db.close();        */                /*        //---get a contact---        db.open();        Cursor c = db.getContact(2);        if (c.moveToFirst())                    DisplayContact(c);        else            Toast.makeText(this, "No contact found", Toast.LENGTH_LONG).show();        db.close();             */                /*        //---update contact---        db.open();        if (db.updateContact(1, "Wei-Meng Lee", "[e-mail]"))            Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG).show();        else            Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG).show();        db.close();        */                /*        //---delete a contact---        db.open();        if (db.deleteContact(1))            Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG).show();        else            Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG).show();        db.close();        */                try {            String destPath = "/data/data/" + getPackageName() +                "/databases";            File f = new File(destPath);            if (!f.exists()) {                        f.mkdirs();                f.createNewFile();                        //---copy the db from the assets folder into             // the databases folder---                CopyDB(getBaseContext().getAssets().open("mydb"),                    new FileOutputStream(destPath + "/MyDB"));            }        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }               //---get all contacts---        db.open();        //        Cursor c = db.getAllContacts();//        if (c.moveToFirst())//        {//            do {//              //  DisplayContact(c);//            } while (c.moveToNext());//        }        list = new ArrayList<String>();        Cursor c = db.getFoodName();        if(c.moveToFirst()){        do{        //DisplayFoodName(c);        BindFoodName(c);        }while(c.moveToNext());                }                db.close();                // Init autoString        InitAutoString();                              arrAdapter = new ArrayAdapter<String>(this,android.R.layout.simple_dropdown_item_1line ,autoString);      autoCTV.setAdapter(arrAdapter);//      autoCTV.setOnItemClickListener(new OnItemClickListener() {////@Override//public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,//long arg3) {//// TODO Auto-generated method stub////}//      //      });    }    private void InitAutoString() {// TODO Auto-generated method stubautoString = new String[list.size()];        for(int i=0; i< list.size();++i){        autoString[i] = list.get(i);        }}private void BindFoodName(Cursor c) {// TODO Auto-generated method stublist.add(c.getString(0));}//    private void DisplayFoodName(Cursor c) {//// TODO Auto-generated method stub////    Toast.makeText(this,////                "Name: " + c.getString(0) + "\n",////                Toast.LENGTH_LONG).show();//    list.add(c.getString(0));//}public void CopyDB(InputStream inputStream,     OutputStream outputStream) throws IOException {        //---copy 1K bytes at a time---        byte[] buffer = new byte[1024];        int length;        while ((length = inputStream.read(buffer)) > 0) {            outputStream.write(buffer, 0, length);        }        inputStream.close();        outputStream.close();    }    public void DisplayContact(Cursor c)    {        Toast.makeText(this,                "id: " + c.getString(0) + "\n" +                "Name: " + c.getString(1) + "\n" +                "ingredient:  " + c.getString(2),                Toast.LENGTH_LONG).show();    }@Overridepublic void onClick(View v) {switch(v.getId()){//case R.id.button1://DBAdapter db = new DBAdapter(this);//db.open();////Cursor c = db.getAllContacts();//if(c.moveToFirst()){//do{// DisplayContact(c);//}while(c.moveToNext());//}//db.close();//break;////case R.id.button2://DBAdapter db1 = new DBAdapter(this);//db1.open();////Cursor c1 = db1.getContact(1);  // 查找返回//// 假设返回多项(0,1,2...)，使用c.moveToFirst()来获得第一项，如果没有获得任何数据//// 的使用c.moveToFirst()则为假//if(c1.moveToFirst()){//DisplayContact(c1);//}else{//Toast.makeText(this, "No item found", Toast.LENGTH_SHORT).show();//}//db1.close();////break;//case R.id.button1:String str = autoCTV.getEditableText().toString();DBAdapter db2 = new DBAdapter(this);db2.open();Cursor c2 = db2.getContact(str);  // 查找返回// 假设返回多项(0,1,2...)，使用c.moveToFirst()来获得第一项，如果没有获得任何数据// 的使用c.moveToFirst()则为假if(c2.moveToFirst()){//DisplayContact(c2);ShowInEditText(c2);}else{Toast.makeText(this, "No item found", Toast.LENGTH_SHORT).show();}db2.close();break;}// TODO Auto-generated method stub}private void ShowInEditText(Cursor c) {// TODO Auto-generated method stub    String str =  c.getString(1) + "\n" +    c.getString(2)+"\n";        editTxt.setText(str);}@Overridepublic void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {// TODO Auto-generated method stub}@Overridepublic boolean onCreateOptionsMenu(Menu menu){//运行时，参数Menu其实就是MenuBuilder对象Log.d("MainActivity", "menu--->" + menu);/*利用反射机制调用MenuBuilder的setOptionalIconsVisible方法设置mOptionalIconsVisible为true， * 给菜单设置图标时才可见 */setIconEnable(menu, true); menu.add(0, ITEM1, 0, R.string.AddItem);//item1.setIcon(R.drawable.camera);// menu.add(0, ITEM2, 0, R.string.DeleteItem);//item2.setIcon(R.drawable.dial); menu.add(0, ITEM3, 0, R.string.SaveItem);//item3.setIcon(R.drawable.sms);return super.onCreateOptionsMenu(menu);}@Overridepublic boolean onPrepareOptionsMenu(Menu menu) {// TODO Auto-generated method stubreturn super.onPrepareOptionsMenu(menu);}    //enable为true时，菜单添加图标有效，enable为false时无效。4.0系统默认无效    private void setIconEnable(Menu menu, boolean enable)    {    try     {Class<?> clazz = Class.forName("com.android.internal.view.menu.MenuBuilder");Method m = clazz.getDeclaredMethod("setOptionalIconsVisible", boolean.class);m.setAccessible(true);//MenuBuilder实现Menu接口，创建菜单时，传进来的menu其实就是MenuBuilder对象(java的多态特征)m.invoke(menu, enable);    } catch (Exception e) {e.printStackTrace();}    }        @Override      public boolean onOptionsItemSelected(MenuItem item) {          switch(item.getItemId()){  //item.getTitle() 1.7+ version        // http://bgwan.blog.163.com/blog/static/23930101620153113355297/        // http://blog.csdn.net/worker90/article/details/6786592            case ITEM1:                  Toast.makeText(this,"新增一项", Toast.LENGTH_LONG).show();                  Intent intent = new Intent(this,AddItemActivity.class);                startActivity(intent);                break;              case ITEM2:  //            Toast.makeText(this,"删除当前打开项", Toast.LENGTH_LONG).show();//            AlertDialog.Builder builder = new Builder(this);            String tempStr = autoCTV.getEditableText().toString();                        DBAdapter dba = new DBAdapter(this);            dba.open();            if(dba.deleteContact(tempStr)){            Toast.makeText(this, "Delete"+tempStr+"Succeed", Toast.LENGTH_LONG).show();            }else{            Toast.makeText(this, "Delete"+tempStr+"failed", Toast.LENGTH_LONG).show();            }            dba.close();// //            builder.setMessage("确定删除菜单项"+tempStr+"吗？");//            builder.setTitle("提示");//            builder.setPositiveButton("确认", new DialogInterface.OnClickListener() {////@Override//public void onClick(DialogInterface arg0, int arg1) {//// TODO Auto-generated method stub//// //        //}//// //});                            break;              case ITEM3:              Toast.makeText(this,"保存修改", Toast.LENGTH_LONG).show();              String name = autoCTV.getEditableText().toString();            String ingredient = editTxt.getText().toString();            DBAdapter dbAdapter = new DBAdapter(this);            dbAdapter.open();            // 获取改名的 rowID            Cursor cursor =  dbAdapter.getContact(name);            cursor.moveToFirst();            int Row_ID =Integer.parseInt(cursor.getString(0));                         if(dbAdapter.updateContact(Row_ID, name, ingredient)){            Toast.makeText(this, "Update Succeed!", Toast.LENGTH_LONG).show();            }else{            Toast.makeText(this, "Update Failed!", Toast.LENGTH_LONG).show();            }            break;          }                    return super.onOptionsItemSelected(item);      }    }

AddItemActivity.java

package net.learn2develop.Databases;/** * @author 作者1：宋倩楠  &  blog.csdn.net/haimian520 * @version 创建时间：2015年11月18日  * */ import android.app.Activity;import android.database.Cursor;import android.os.Bundle;import android.view.Menu;import android.view.MenuItem;import android.widget.EditText;import android.widget.Toast;public class AddItemActivity extends Activity{private static final int SaveItem = Menu.FIRST;private static final int CancelItem = Menu.FIRST+1;EditText edit_Name;EditText edit_Ingredient;@Overridepublic void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.addnewitem);edit_Name = (EditText)findViewById(R.id.editTxt_Title);edit_Ingredient = (EditText)findViewById(R.id.editTxt_Content);}public boolean onCreateOptionsMenu(Menu menu) {menu.add(0, SaveItem, 0, "保存");menu.add(0, CancelItem, 0, "舍弃");return true;}public boolean onOptionsItemSelected(MenuItem item) {switch (item.getItemId()) {case SaveItem://setTitle("单击了菜单子项1");String name = "";name = edit_Name.getText().toString();String ingredient = edit_Ingredient.getText().toString();if(name.isEmpty()||ingredient.isEmpty()){Toast.makeText(this, "请输入 名称 和 做法", Toast.LENGTH_LONG).show();}else{DBAdapter db = new DBAdapter(this);db.open();Cursor c = db.getContact(name);if(!c.moveToFirst()){db.insertContact(name, ingredient);db.close();}else{Toast.makeText(this, "同名菜已经存在，请尝试使用其他名称", Toast.LENGTH_LONG).show();}}break;case CancelItem://setTitle("单击了菜单子项2");Toast.makeText(this, "直接按返回键就可以了", Toast.LENGTH_LONG).show();break;}return true;}}

上述的代码中还有一些不完善，比如：

在新增菜品名之后，AutoCompleteTextView中并不能予以及时的显示，而是在重启app之后，才能显示，这里只需要在新增项目之后并点击保存之后(B Activity)，重新运行父Activity（A Activity）。需要处理一些特别情况，如进入B之前，将A已经关闭，而点的是返回键，那么需要重写返回按键事件。这样就可以将菜品名称不能予以显示的缺点给弥补了。

这是一个完整的Android本地(离线)数据库的增删改查操作，基于Wei-Meng Lee的代码修改而成，原先数据库被修改，以适应当前项目──菜单。本代码由本人和可视化研究所成员宋倩楠共同完成，是Android课程的一个小作业。为了您和他人着想请不要将代码复制粘贴，只供参考。在这里我给源代码压缩包下载设置了分数，当然这不是我的目的，这样做只是杜绝一部分。。。，当然本文中的所有代码和压缩包中的代码完全相同。完整项目代码压缩包下载地址 http://download.csdn.net/detail/haimian520/9285205