SQLite进阶：Android上的SQLite常用操作
来源：互联网 发布：加华伟业资本 知乎 编辑：程序博客网 时间：2024/06/05 09:44
昨天简单地做一了一个列表展示，现在将增加/修改/删除方法都给加上了，本次增加的内容比较多，比如常见按钮的事件，列表长按事件，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);}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stub}@Overridepublic 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(){ @Overridepublic 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")@Overridepublic 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() {@Overridepublic 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);// 启动新的ActivityAddAppInfoActivity.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() {@Overridepublic 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);// 启动新的ActivityEditAppInfoActivity.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变量就不贴了，看看效果先，主界面：
添加，点击主界面添加按钮进入添加页面：
编辑，点击列表直接跳转到编辑页面：
删除，长按类别项出现删除操作：
- SQLite进阶：Android上的SQLite常用操作
- Android sqlite常用操作
- Android Sqlite数据库常用操作
- android Sqlite数据库的操作
- android sqlite数据库的操作
- android的sqlite数据库操作
- android中SQLite的操作
- Android的SQLite基本操作
- Android SQLite的基本操作
- Android 的 Sqlite基本操作
- Android 的 Sqlite基本操作
- 查看android sqlite数据库常用操作
- Android开发-SQLite数据库-常用操作
- 【SQLite】常用操作SQL及压缩SQLite的实例代码
- mini2440上SQLite操作
- mini2440上SQLite操作
- SQLite常用基本操作
- sqlite常用操作
- vb学习之菜鸟方案
- Android入门：使用Android GPS实现简单的定位
- Qt中导出.txt, .xls, .xlsx, .pdf文件小结
- C#例子程序
- c#例子程序
- SQLite进阶：Android上的SQLite常用操作
- 生活场景与UML
- SQL不同服务器数据库之间的数据操作整理
- Android简单的SQLite操作及ListView展示数据
- Java虚拟机深入学习之一: Java虚拟机的内存区域
- 边界测试
- OPC客户端读取Server数据
- JQuery树插件JSTree的使用
- ubuntu下SVN服务器安装配置
