SQLite进阶：Android上的SQLite常用操作

xuyaxiu

　　昨天简单地做一了一个列表展示，现在将增加/修改/删除方法都给加上了，本次增加的内容比较多，比如常见按钮的事件，列表长按事件，Activity之间的跳转及传递参数，按键事件的监听等，希望对初学的朋友有些帮助，同时欢迎老鸟给我做Code review，需要改进的地方，还请指出，谢谢。

　　下面逐个介绍一个代码，数据库辅助类：DBOpenHelper

package com.van.sqlite.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* 数据库辅助类。
* @author Van
*
*/
public class DBOpenHelper extends SQLiteOpenHelper{
/**数据库名称*/
private static final String DATABASE_NAME="MyAppDB";
/**数据库版本*/
private static final int DATABASE_VERSION=1;
/** 创建数据表语句*/
private static final String DDL_CREATE_TABLE_APPINFO="CREATE TABLE IF NOT EXISTS AppInfo (appId integer primary key autoincrement,appName text,appDescription text, remark text)";
/**
* 实例化数据库连接.
* @param context
*/
public DBOpenHelper(Context context){
super(context, DATABASE_NAME, null, DATABASE_VERSION);
/**初始化数据表*/
this.getWritableDatabase().execSQL(DDL_CREATE_TABLE_APPINFO);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
　　针对AppInfo数据表的辅助类，内含增删查改方法：AppInfoHelper

package com.van.sqlite.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class AppInfoHelper {

private DBOpenHelper helper;  
/**
* 构造
* @param context
*/
public AppInfoHelper(Context context){
this.helper=new DBOpenHelper(context);
}

/**
* 查询
* @param tableName
* @return
*/
public Cursor select(String sql) {  
SQLiteDatabase db=helper.getWritableDatabase();
try{
Cursor cursor=db.rawQuery(sql,null);  
cursor.moveToFirst();
return cursor;
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(db.isOpen()){
db.close();
}
}
return null;
}  



/**
* 新增方法.
* @param appName
* @param appDescription
* @param remark
*/
public void addAppInfo(String appName,String appDescription,String remark){

ContentValues values=new ContentValues();
values.put("appName", appName);  
values.put("appDescription", appDescription);  
values.put("remark", remark);  
SQLiteDatabase db=helper.getWritableDatabase();
try{
db.insert("AppInfo", null, values);  
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(db.isOpen()){
db.close();
}
}
}
/**
* 编辑应用信息.
* @param appId
* @param appName
* @param appDescription
* @param remark
*/
public void editAppInfo(int appId,String appName,String appDescription,String remark){
ContentValues values=new ContentValues();
values.put("appName", appName);  
values.put("appDescription", appDescription);  
values.put("remark", remark);  
SQLiteDatabase db=helper.getWritableDatabase();
//条件
String[] whereValue ={ Integer.toString(appId) };
try{
db.update("AppInfo", values, "appId=?", whereValue);  
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(db.isOpen()){
db.close();
}
}
}

/**
* 删除应用信息.
* @param appId
*/
public void deleteAppInfo(int appId){
SQLiteDatabase db=helper.getWritableDatabase();
//条件
String[] whereValue ={ Integer.toString(appId) };
try{
db.delete("AppInfo", "appId=?", whereValue);
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(db.isOpen()){
db.close();
}
}
}




}

　　


　　AppInfo数据适配器，用于列表显示的适配器：AppInfoListAdapter

package com.van.sqlite.adapter;
import java.util.ArrayList;
import java.util.HashMap;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.SimpleAdapter;
import com.van.sqlite.activity.R;
import com.van.sqlite.db.DBOpenHelper;
public class AppInfoListAdapter {

private Context context;

public AppInfoListAdapter(Context context){
this.context=context;
}
/**
* 查询AppInfo返回Map集合
* @return
*/  
private ArrayList<HashMap<String, Object>> fillList(){  
//生成动态数组，并且转载数据   
ArrayList<HashMap<String, Object>> dataList = new ArrayList<HashMap<String, Object>>();   
DBOpenHelper helper=new DBOpenHelper(context);  
SQLiteDatabase db=helper.getReadableDatabase();  
try{  
Cursor cursor=db.rawQuery("SELECT * FROM AppInfo", null);   
cursor.moveToFirst();  
if(cursor.moveToFirst()) {   
while (!cursor.isAfterLast()) {
Integer appId = cursor.getInt(cursor.getColumnIndex("appId"));   
String appName = cursor.getString(cursor.getColumnIndex("appName"));   
String appDescription = cursor.getString(cursor.getColumnIndex("appDescription"));   
HashMap<String, Object> map = new HashMap<String, Object>();   
map.put("appId",appId);   
map.put("appName", appName);   
map.put("appDescription", appDescription);   
dataList.add(map);   
cursor.moveToNext();
}
}   
}catch(Exception ex){  
ex.printStackTrace();  
}finally{  
if(db.isOpen()){  
db.close();  
}  
}  
return dataList;  
}  

/**
* 填充数据，取得数据适配器.
* @param listData
* @return
*/  
public SimpleAdapter getAdapter(Context context){  
//生成适配器，数组===》ListItem   
SimpleAdapter adapter = new SimpleAdapter(context,  
fillList(),//数据来源     
R.layout.list_item,//ListItem的XML实现   
//动态数组与ListItem对应的子项            
new String[] {"appName", "appDescription"},     
//ListItem的XML文件里面的两个TextView ID   
new int[] {R.id.textView_appName,R.id.textView_appDescription});   
return adapter;   
}  



}

　　3个Activity，主界面显示/增加/修改
　　SQLiteDemoActivity

package com.van.sqlite.activity;  
import java.util.HashMap;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.AlertDialog.Builder;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.KeyEvent;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.View.OnCreateContextMenuListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;
import com.van.sqlite.adapter.AppInfoListAdapter;
import com.van.sqlite.db.AppInfoHelper;
public class SQLiteDemoActivity extends Activity {  

private ListView listView;
private AppInfoListAdapter adapter;

@Override  
public void onCreate(Bundle savedInstanceState) {  
super.onCreate(savedInstanceState);  
setContentView(R.layout.main);  
listView=(ListView)findViewById(R.id.listView_appList);  
//获取数据适配器
adapter=new AppInfoListAdapter(this);
//添加并且显示   
listView.setAdapter(adapter.getAdapter(this));   

//跳转到新增窗口
Button addButton=(Button)findViewById(R.id.button_to_add);
addButton.setOnClickListener(new OnClickListener(){
@Override
public void onClick(View arg0) {
//打开新的窗口
Intent intent=new Intent();
intent.setClass(SQLiteDemoActivity.this, AddAppInfoActivity.class);
startActivity(intent);
//结束当前
SQLiteDemoActivity.this.finish();
}
});

//数据列表点击操作
listView.setOnItemClickListener(new OnItemClickListener() {
@SuppressWarnings("unchecked")
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int index,long arg3) {
ListView listView = (ListView)adapterView;
HashMap<String, Object> map = (HashMap<String, Object>) listView.getItemAtPosition(index);
//打开新的窗口
Intent intent=new Intent();
intent.setClass(SQLiteDemoActivity.this, EditAppInfoActivity.class);
//将appId传入编辑窗口
intent.putExtra("appId", Integer.parseInt(map.get("appId").toString()));
startActivity(intent);
//结束当前
SQLiteDemoActivity.this.finish();
}
});

//添加长按点击   
listView.setOnCreateContextMenuListener(new OnCreateContextMenuListener() {   
@Override  
public void onCreateContextMenu(ContextMenu menu, View v,ContextMenuInfo menuInfo) {
menu.setHeaderTitle("列表操作");   
menu.add(0, 0, 0, "删除应用信息");   
}
});  

}  

@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if(keyCode == KeyEvent.KEYCODE_BACK){//返回按钮
AlertDialog.Builder builder = new Builder(SQLiteDemoActivity.this);
builder.setMessage("确实要退出程序吗?");
builder.setTitle("提示");
builder.setPositiveButton("确认",
new android.content.DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
SQLiteDemoActivity.this.finish();
}
});
builder.setNegativeButton("取消",
new android.content.DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
}
});
builder.create().show();
return true;
}
return false;
}

/**
* 删除应用.
* @param appId
*/
public void delete(int appId){
AppInfoHelper appInfoHelper=new AppInfoHelper(this);
appInfoHelper.deleteAppInfo(appId);
Toast.makeText(SQLiteDemoActivity.this, "应用删除成功！", Toast.LENGTH_SHORT).show();  
//添加并且显示   
listView.setAdapter(adapter.getAdapter(this));   
}

//长按菜单响应函数   
@SuppressWarnings("unchecked")
@Override  
public boolean onContextItemSelected(MenuItem item) {   
AdapterView.AdapterContextMenuInfo m = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo();
HashMap<String, Object> map = (HashMap<String, Object>) listView.getItemAtPosition(m.position);
int appId=Integer.parseInt(map.get("appId").toString());
//调用方法，删除
delete(appId);
return super.onContextItemSelected(item);   
}   

}  
　　AddAppInfoActivity

package com.van.sqlite.activity;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.KeyEvent;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.van.sqlite.db.AppInfoHelper;
public class AddAppInfoActivity extends Activity{

@Override  
public void onCreate(Bundle savedInstanceState) {  
super.onCreate(savedInstanceState);  
setContentView(R.layout.add);  
Button saveButton=(Button)findViewById(R.id.button_save);
saveButton.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
add();//添加数据
}
});
}  
/**
* 添加数据方法.
*/
public void add(){
EditText et_appName=(EditText)findViewById(R.id.editText_appName);
EditText et_appDescription=(EditText)findViewById(R.id.editText_appDescription);
EditText et_remark=(EditText)findViewById(R.id.editText_remark);
String appName=et_appName.getText().toString();  
String appDescription=et_appDescription.getText().toString();  
String remark=et_remark.getText().toString();  
AppInfoHelper appInfoHelper=new AppInfoHelper(this);
appInfoHelper.addAppInfo(appName, appDescription, remark);
Toast.makeText(AddAppInfoActivity.this, "应用信息添加成功！", Toast.LENGTH_SHORT).show();  
Intent intent = new Intent(AddAppInfoActivity.this,SQLiteDemoActivity.class);
startActivity(intent);
AddAppInfoActivity.this.finish();
}

@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if(keyCode == KeyEvent.KEYCODE_BACK){//返回按钮
Intent intent = new Intent();
intent.setClass(AddAppInfoActivity.this, SQLiteDemoActivity.class);
startActivity(intent);// 启动新的Activity
AddAppInfoActivity.this.finish();// 结束就的Activity
}
return false;
}


}

　　


　　EditAppInfoActivity

package com.van.sqlite.activity;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.KeyEvent;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.van.sqlite.db.AppInfoHelper;
public class EditAppInfoActivity extends Activity{

private int paramAppId;
@Override  
public void onCreate(Bundle savedInstanceState) {  
super.onCreate(savedInstanceState);  
setContentView(R.layout.edit);  
//绑定数据
bindValue();
//绑定事件
Button editButton=(Button)findViewById(R.id.button_edit_save);
editButton.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
edit();
}
});
}
/**
* 查询赋值.
*/
public void bindValue(){
//取得编号
paramAppId= this.getIntent().getExtras().getInt("appId");
//查询数据
AppInfoHelper appInfoHelper=new AppInfoHelper(this);
Cursor cursor=appInfoHelper.select("SELECT * FROM AppInfo WHERE appId="+paramAppId);   

if(cursor.moveToFirst()) {   
String appName = cursor.getString(cursor.getColumnIndex("appName"));   
String appDescription = cursor.getString(cursor.getColumnIndex("appDescription"));  
String remark = cursor.getString(cursor.getColumnIndex("remark"));  
EditText et_appName=(EditText)findViewById(R.id.editText_edit_appName);
EditText et_appDescription=(EditText)findViewById(R.id.editText_edit_appDescription);
EditText et_remark=(EditText)findViewById(R.id.editText_edit_remark);
et_appName.setText(appName);
et_appDescription.setText(appDescription);
et_remark.setText(remark);
}   
}
/**
* 编辑应用。
*/
private void edit(){
EditText et_appName=(EditText)findViewById(R.id.editText_edit_appName);
EditText et_appDescription=(EditText)findViewById(R.id.editText_edit_appDescription);
EditText et_remark=(EditText)findViewById(R.id.editText_edit_remark);

String appName=et_appName.getText().toString();  
String appDescription=et_appDescription.getText().toString();  
String remark=et_remark.getText().toString();  
AppInfoHelper appInfoHelper=new AppInfoHelper(this);
appInfoHelper.editAppInfo(paramAppId, appName, appDescription, remark);
Toast.makeText(EditAppInfoActivity.this, "应用信息编辑成功！", Toast.LENGTH_SHORT).show();  
Intent intent = new Intent(EditAppInfoActivity.this,SQLiteDemoActivity.class);
startActivity(intent);
EditAppInfoActivity.this.finish();
}
@Override
public boolean onKeyDown(int keyCode, KeyEvent event) {
if(keyCode == KeyEvent.KEYCODE_BACK){//返回按钮
Intent intent = new Intent();
intent.setClass(EditAppInfoActivity.this, SQLiteDemoActivity.class);
startActivity(intent);// 启动新的Activity
EditAppInfoActivity.this.finish();// 结束就的Activity
}
return false;
}

}

　　几个布局文件：
main.xml

<?xml version="1.0" encoding="utf-8"?>  
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
android:layout_width="fill_parent"  
android:layout_height="fill_parent"  
android:orientation="vertical" >

<Button
android:id="@+id/button_to_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/add" />


<ListView
android:id="@+id/listView_appList"
android:layout_width="match_parent"
android:layout_height="match_parent" >
</ListView>
</LinearLayout>  
　　


　　list_item.xml

<?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" >  
<TextView  
android:id="@+id/textView_appName"  
android:layout_width="wrap_content"  
android:layout_height="wrap_content"  
android:textAppearance="?android:attr/textAppearanceMedium" />  
<TextView  
android:id="@+id/textView_appDescription"  
android:layout_width="wrap_content"  
android:layout_height="wrap_content"  
android:textAppearance="?android:attr/textAppearanceSmall" />
</LinearLayout>
　　add.xml

<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent" >
<TextView
android:id="@+id/textView_appName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/appName"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText_appName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="textPostalAddress" />
<TextView
android:id="@+id/textView_appDescription"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/appDescription"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText_appDescription"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="text" />
<TextView
android:id="@+id/textView_remark"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/remark"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText_remark"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="text"/>
<Button
android:id="@+id/button_save"
android:layout_width="100dp"
android:layout_marginTop="15dp"
android:layout_height="wrap_content"
android:text="@string/save" />
</TableLayout>
　　edit.xml

<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent" >
<TextView
android:id="@+id/textView_edit_appName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/appName"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText_edit_appName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="textPostalAddress" />
<TextView
android:id="@+id/textView_edit_appDescription"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/appDescription"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText_edit_appDescription"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="text" />
<TextView
android:id="@+id/textView_edit_remark"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/remark"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText_edit_remark"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="text"/>
<Button
android:id="@+id/button_edit_save"
android:layout_width="100dp"
android:layout_marginTop="15dp"
android:layout_height="wrap_content"
android:text="@string/edit" />
</TableLayout>
　　神马String变量就不贴了，看看效果先，主界面：

　　添加，点击主界面添加按钮进入添加页面：

　　编辑，点击列表直接跳转到编辑页面：

　　删除，长按类别项出现删除操作：