java sqlite dbhelper,Android 之 SQLite 数据库 通讯录应用程序

新建一个 SQLLiteApp Android 工程

数据库文件 存放位置如图：

下面我们一步一步实现

1. 新建电话类 com.models.Telephone

package com.models;

public class Telephone {

private int id;

private String name;

private String telephone;

public Telephone(String name, String telephone) {

super();

this.name = name;

this.telephone = telephone;

}

public Telephone(int id, String name, String telephone) {

super();

this.id = id;

this.name = name;

this.telephone = telephone;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getTelephone() {

return telephone;

}

public void setTelephone(String telephone) {

this.telephone = telephone;

}

public String toString() {

return this.name + " - " + this.telephone;

}

}

2. 新建DBHelper 数据访问帮助类 com.db.DBHelper

package com.db;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

// DDMS /data/data/包名/databases/telephone.db

private static String name = "telephone.db";// 数据库文件的名字

private static int version = 2;// 版本号

public DBHelper(Context context) {

super(context, name, null, version);

}

// 第一次操作数据库的时候 新建表 只调用一次

@Override

public void onCreate(SQLiteDatabase db) {

String sql = "create table telephone(_id integer primary key autoincrement,name varchar,telephone varchar)";

// 执行 sql 语句

db.execSQL(sql);

}

// 当 版本号 version 改变 才调用

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

String sql = "create table user(username varchar,pwd varchar)";

db.execSQL(sql);

}

}

3. 新建数据访问接口 com.dao.TelephoneDao

package com.dao;

import java.util.List;

import com.models.Telephone;

public interface TelephoneDao {

// 添加联系人

public boolean add(Telephone item);

// 修改联系人

public boolean update(Telephone item);

// 查找所有的联系人

public List find();

// 删除联系人

public boolean deleteById(int id);

// 删除选择的多个联系人

public boolean deleteByIds(String ids);

}

4. 新建数据访问实现类 com.dao.TelephoneDaoImpl

package com.dao;

import java.util.ArrayList;

import java.util.List;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import com.db.DBHelper;

import com.models.Telephone;

public class TelephoneDaoImpl implements TelephoneDao {

private DBHelper dbHelper;

public TelephoneDaoImpl(Context context) {

dbHelper = new DBHelper(context);

}

public boolean add(Telephone item) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

String sql = "insert into telephone(name,telephone)values('" + item.getName() + "','" + item.getTelephone() + "')";

// 执行sql 语句

db.execSQL(sql);

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

public boolean update(Telephone item) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

String sql = "update telephone set name='" + item.getName() + "',telephone='" + item.getTelephone() + "' where _id=" + item.getId();

// 执行sql 语句

db.execSQL(sql);

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

public List find() {

SQLiteDatabase db = dbHelper.getWritableDatabase();

List telephoneList = new ArrayList();

try {

String sql = "select * from telephone";

// 执行sql 语句

Cursor cursor = db.rawQuery(sql, null);

// 循环取数据

while (cursor.moveToNext()) {

int id = cursor.getInt(cursor.getColumnIndex("_id"));

String name = cursor.getString(cursor.getColumnIndex("name"));

String telephone = cursor.getString(cursor.getColumnIndex("telephone"));

// 数据添加到集合

telephoneList.add(new Telephone(id, name, telephone));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return telephoneList;

}

public boolean deleteById(int id) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

String sql = "delete from telephone where _id=" + id;

// 执行sql 语句

db.execSQL(sql);

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

public boolean deleteByIds(String ids) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

String sql = "delete from telephone where _id in (" + ids + ")";

db.execSQL(sql);

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

}

5. 新建布局文件  res -> layout -> sqlite.xml

xmlns:tools="http://schemas.android.com/tools"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:orientation="vertical">

android:id="@+id/btnAdd"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="添加" />

android:id="@+id/btnUpdate"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="修改" />

android:id="@+id/btnFind"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="查询" />

android:id="@+id/btnDelete"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="删除" />

6. 新建 界面类 com.test.SqliteActivity

package com.test;

import java.util.List;

import android.app.Activity;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.Toast;

import com.dao.TelephoneDao;

import com.dao.TelephoneDaoImpl;

import com.example.sqlliteapp.R;

import com.models.Telephone;

public class SqliteActivity extends Activity {

private Button btnAdd;

private Button btnUpdate;

private Button btnFind;

private Button btnDelete;

@Override

protected void onCreate(Bundle savedInstanceState) {

// TODO Auto-generated method stub

super.onCreate(savedInstanceState);

setContentView(R.layout.sqlite);

btnAdd = (Button) findViewById(R.id.btnAdd);

btnUpdate = (Button) findViewById(R.id.btnUpdate);

btnFind = (Button) findViewById(R.id.btnFind);

btnDelete = (Button) findViewById(R.id.btnDelete);

btnAdd.setOnClickListener(listener);

btnUpdate.setOnClickListener(listener);

btnFind.setOnClickListener(listener);

btnDelete.setOnClickListener(listener);

}

private OnClickListener listener = new OnClickListener() {

public void onClick(View v) {

if (v.getId() == R.id.btnAdd) {

// 添加练习人

TelephoneDao telephoneDao = new TelephoneDaoImpl(SqliteActivity.this);

if (telephoneDao.add(new Telephone("果宏伟", "13244444"))) {

Toast.makeText(SqliteActivity.this, "添加成功", Toast.LENGTH_SHORT).show();

}

}

if (v.getId() == R.id.btnUpdate) {

// 修改练习人

TelephoneDao telephoneDao = new TelephoneDaoImpl(SqliteActivity.this);

if (telephoneDao.update(new Telephone(1, "李密", "132555555"))) {

Toast.makeText(SqliteActivity.this, "修改成功", Toast.LENGTH_SHORT).show();

}

}

if (v.getId() == R.id.btnFind) {

// 查找练习人

TelephoneDao telephoneDao = new TelephoneDaoImpl(SqliteActivity.this);

List telephoneList = telephoneDao.find();

StringBuilder sb = new StringBuilder();

for (int i = 0; i < telephoneList.size(); i++) {

Telephone item = telephoneList.get(i);

sb.append(item.getName() + "=" + item.getTelephone() + "\n");

}

Toast.makeText(SqliteActivity.this, sb.toString(), Toast.LENGTH_SHORT).show();

}

if (v.getId() == R.id.btnDelete) {

// 删除练习人

TelephoneDao telephoneDao = new TelephoneDaoImpl(SqliteActivity.this);

if (telephoneDao.deleteById(1)) {

Toast.makeText(SqliteActivity.this, "删除成功", Toast.LENGTH_SHORT).show();

}

}

}

};

}

7. AndroidManifest.xml 全局配置文件 配置启动界面 com.test.SqliteActivity

package="com.example.listviewapp"

android:versionCode="1"

android:versionName="1.0" >

android:minSdkVersion="7"

android:targetSdkVersion="7" />

android:allowBackup="true"

android:icon="@drawable/ic_launcher"

android:label="@string/app_name"

android:theme="@style/AppTheme" >

android:name="com.test.SqliteActivity"

android:label="@string/app_name" >

8. 在 SQLLiteApp 右键 -> Run As -> Android Application 发布到模拟器运行

补充：

对于 SQLLite 的操作另外一种方式 ContentValues  可以用下面的数据访问类 替换上面的 com.dao.TelephoneDaoImpl

package com.dao;

import java.util.ArrayList;

import java.util.List;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import com.db.DBHelper;

import com.models.Telephone;

public class TelephoneDaoImpl2 implements TelephoneDao {

private DBHelper dbHelper;

public TelephoneDaoImpl2(Context context) {

dbHelper = new DBHelper(context);

}

public boolean add(Telephone item) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

// 插入

ContentValues values = new ContentValues();

values.put("name", item.getName());

values.put("telephone", item.getTelephone());

db.insert("telephone", null, values);

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

public boolean update(Telephone item) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

// 修改

ContentValues values = new ContentValues();

values.put("_id", item.getId());

values.put("name", item.getName());

values.put("telephone", item.getTelephone());

// update set name='' where _id=?

db.update("telephone", values, "_id=?", new String[] { String.valueOf(item.getId()) });

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

public List find() {

SQLiteDatabase db = dbHelper.getWritableDatabase();

List telephoneList = new ArrayList();

try {

// select name,telephone from telephone where _id=? orderBy _id desc

// groupby _id having _id>2

// columns = new String[]{"name","telephone"}

// selection = _id=?

// selectionArgs =new String[]{}

Cursor cursor = db.query("telephone", null, null, null, null, null, null);

// 循环取数据

while (cursor.moveToNext()) {

int id = cursor.getInt(cursor.getColumnIndex("_id"));

String name = cursor.getString(cursor.getColumnIndex("name"));

String telephone = cursor.getString(cursor.getColumnIndex("telephone"));

// 数据添加到集合

telephoneList.add(new Telephone(id, name, telephone));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return telephoneList;

}

public boolean deleteById(int id) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

// 删除

// delete from telephone where _id=?

db.delete("telephone", "_id=?", new String[] { String.valueOf(id) });

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

public boolean addAll(List telephoneList) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

// 开始事物

db.beginTransaction();

for (int i = 0; i < telephoneList.size(); i++) {

Telephone item = telephoneList.get(i);

ContentValues values = new ContentValues();

values.put("name", item.getName());

values.put("telephone", item.getTelephone());

db.insert("telephone", null, values);

}

// 事物提交

db.setTransactionSuccessful();

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.endTransaction();

db.close();

}

return false;

}

public boolean deleteByIds(String ids) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

try {

String sql = "delete from telephone where _id in (" + ids + ")";

db.execSQL(sql);

return true;

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return false;

}

}

在上面的基础上我们实现 通讯录应用程序

点击 右边控制面板 menu 键弹出 添加联系人 和 删除联系人 菜单

点击 添加联系人

点击 删除联系人

1.建立 2 个布局文件 通讯录列表 res -> layout -> telephone.xml  通讯录添加联系人 res -> layout -> telephone_add.xml

telephone.xml

xmlns:tools="http://schemas.android.com/tools"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:orientation="vertical">

android:id="@+id/listView"

android:layout_width="fill_parent"

android:layout_height="wrap_content"

/>

telephone_add.xml

xmlns:tools="http://schemas.android.com/tools"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:orientation="vertical" >

android:id="@+id/txtName"

android:layout_width="200dp"

android:layout_height="wrap_content"

android:hint="请输入名称"

>

android:id="@+id/txtTelephone"

android:layout_width="200dp"

android:layout_height="wrap_content"

android:hint="请输入电话号码"

/>

android:id="@+id/btnOk"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="确定" />

2.建立通讯录 界面类 com.test.TelephoneActivity

package com.test;

import java.util.ArrayList;

import java.util.List;

import android.app.Activity;

import android.app.AlertDialog;

import android.content.DialogInterface;

import android.os.Bundle;

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.OnItemLongClickListener;

import android.widget.ArrayAdapter;

import android.widget.Button;

import android.widget.CheckedTextView;

import android.widget.EditText;

import android.widget.ListView;

import android.widget.Toast;

import com.dao.TelephoneDao;

import com.dao.TelephoneDaoImpl;

import com.example.sqlliteapp.R;

import com.models.Telephone;

public class TelephoneActivity extends Activity {

private ListView listView;

private List telephoneList = new ArrayList();

private final int ADD_MENU = 1;

private final int DELETE_MENU = 2;

private AlertDialog dialog;

@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.telephone);

listView = (ListView) findViewById(R.id.listView);

fillData();

}

// 填充数据

private void fillData() {

TelephoneDao telephoneDao = new TelephoneDaoImpl(this);

telephoneList = telephoneDao.find();

// 填充数据

ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_multiple_choice, telephoneList);

listView.setAdapter(adapter);

listView.setChoiceMode(ListView.CHOICE_MODE_MULTIPLE);

// 添加长按事件

listView.setOnItemLongClickListener(itemLongListener);

}

private OnItemLongClickListener itemLongListener = new OnItemLongClickListener() {

public boolean onItemLongClick(AdapterView> parent, View view, final int position, long id) {

AlertDialog.Builder builder = new AlertDialog.Builder(TelephoneActivity.this);

// 设置删除 和修改 选项

String[] items = { "删除", "修改" };

builder.setItems(items, new DialogInterface.OnClickListener() {

public void onClick(DialogInterface dialog, int which) {

if (which == 0)// 删除

{

delete(position);

}

if (which == 1)// 修改

{

update(position);

}

}

});

dialog = builder.create();

dialog.show();

return false;

}

};

// 删除数据

private void delete(int position) {

Telephone item = telephoneList.get(position);

TelephoneDao telephoneDao = new TelephoneDaoImpl(this);

if (telephoneDao.deleteById(item.getId())) {

Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();

fillData();// 刷新界面

}

}

// 修改数据

private void update(int position) {

final Telephone item = telephoneList.get(position);

AlertDialog.Builder builder = new AlertDialog.Builder(this);

// 将 res->layout->telephone_add.xml 转换成view

View view = View.inflate(this, R.layout.telephone_add, null);

// 获得引用

final EditText txtName = (EditText) view.findViewById(R.id.txtName);

final EditText txtTelephone = (EditText) view.findViewById(R.id.txtTelephone);

// 弹出之前存入数据到文本框

txtName.setText(item.getName());

txtTelephone.setText(item.getTelephone());

Button btnOk = (Button) view.findViewById(R.id.btnOk);

btnOk.setOnClickListener(new OnClickListener() {

public void onClick(View v) {

// 修改练习人

TelephoneDao telephoneDao = new TelephoneDaoImpl(TelephoneActivity.this);

if (telephoneDao.update(new Telephone(item.getId(), txtName.getText().toString(), txtTelephone.getText().toString()))) {

Toast.makeText(TelephoneActivity.this, "修改成功", Toast.LENGTH_SHORT).show();

// 刷新界面

fillData();

dialog.dismiss();

}

}

});

builder.setView(view);

dialog = builder.create();

dialog.show();

}

@Override

public boolean onCreateOptionsMenu(Menu menu) {

menu.add(0, ADD_MENU, 0, "添加联系人");

menu.add(0, DELETE_MENU, 0, "删除选择联系人");

return super.onCreateOptionsMenu(menu);

}

@Override

public boolean onOptionsItemSelected(MenuItem item) {

if (item.getItemId() == ADD_MENU) {

add();

}

if (item.getItemId() == DELETE_MENU) {

// delete from telephone where _id in(1,2,3,4)

// 循环所有listView

StringBuilder sb = new StringBuilder();

for (int i = 0; i < listView.getChildCount(); i++) {

Telephone telephone = telephoneList.get(i);

CheckedTextView ctv = (CheckedTextView) listView.getChildAt(i);

if (ctv.isChecked()) {

sb.append(telephone.getId() + ",");

}

}

// 3,4,5,

String ids = sb.toString();

// 切除最后的 ,

ids = ids.substring(0, ids.lastIndexOf(","));

// 删除

TelephoneDao telephoneDao = new TelephoneDaoImpl(this);

if (telephoneDao.deleteByIds(ids)) {

Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();

// 刷新界面

fillData();

}

}

return super.onOptionsItemSelected(item);

}

private void add() {

// 弹出添加联系人的对话框

AlertDialog.Builder builder = new AlertDialog.Builder(this);

// 将 res->layout->telephone_add.xml 转换成view

View view = View.inflate(this, R.layout.telephone_add, null);

// 获得引用

final EditText txtName = (EditText) view.findViewById(R.id.txtName);

final EditText txtTelephone = (EditText) view.findViewById(R.id.txtTelephone);

Button btnOk = (Button) view.findViewById(R.id.btnOk);

btnOk.setOnClickListener(new OnClickListener() {

public void onClick(View v) {

// 将 文本框里面的内容存入数据库

TelephoneDao telephoneDao = new TelephoneDaoImpl(TelephoneActivity.this);

if (telephoneDao.add(new Telephone(txtName.getText().toString(), txtTelephone.getText().toString()))) {

Toast.makeText(TelephoneActivity.this, "添加成功", Toast.LENGTH_SHORT).show();

// 关闭对话框

dialog.dismiss();

// 刷新界面

fillData();

}

}

});

// 替换布局

builder.setView(view);

dialog = builder.create();

dialog.show();

}

}

3. AndroidManifest.xml 全局配置文件 配置启动界面 com.test.TelephoneActivity

package="com.example.sqlliteapp"

android:versionCode="1"

android:versionName="1.0" >

android:minSdkVersion="7"

android:targetSdkVersion="7" />

android:allowBackup="true"

android:icon="@drawable/ic_launcher"

android:label="@string/app_name"

android:theme="@style/AppTheme" >

android:name="com.test.TelephoneActivity"

android:label="@string/app_name" >

3. 在 SQLLiteApp 右键 -> Run As -> Android Application 发布到模拟器运行