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

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

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

原表为：

_id	name	email

将其修改为：

_id	name	ingredient
1	泡芙	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);		
	}
	
	// 通过特定名字，找到该行ID
	public 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", "weimenglee@learn2develop.net");
        id = db.insertContact("Mary Jackson", "mary@jackson.com");
        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", "weimenglee@gmail.com"))
            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 stub

		autoString = 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 stub
		list.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();
    }

	@Override
	public 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);
	}

	@Override
	public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public 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);
	}
	@Override
	public boolean onPrepareOptionsMenu(Menu menu) 
	{
		// TODO Auto-generated method stub
		return 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;
	
	@Override
	public 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