本内容基于《Beginning Android 4 Application Development》Wei-Meng Lee中文版6.3节(P264) 的 创建和使用数据库修改而成
原文为对表contact的修改,这里我将其改为菜谱的实现
原表为:
_id | name | |
_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