android 的sqlite数据库,Android下利用SQLite数据库实现增删改查

1： 首先介绍如何利用adb查看数据库

1： adb shell

2： cd /data/data/包名/databases

3:  sqlite3 数据库

4   接下来就可以进行数据库的sql语法的使用了

bean对象：

public classPerson {private intid;privateString name;privateString number;

}

View Code

数据库的创建以及表的创建：

package com.example.db;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;public classBaseDao extends SQLiteOpenHelper{/**

* 数据库的构造方法， 用来定义数据库的名称， 数据库查询结果集， 数据库的版本号

**/

publicBaseDao(Context context) {

super(context,"person.db", null, 1);

}/**

*数据库别创建的时候调用的方法

**/@Overridepublic voidonCreate(SQLiteDatabase db) {//初始化数据库的表结构

db.execSQL("create table person (id integer primary key autoincrement, name varchar(20), number varchar(20))");

}

@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, intnewVersion) {//TODO Auto-generated method stub

}

}

View Code

利用纯SQL语句实现增删改查

package com.example.dao;

import java.util.ArrayList;

import java.util.List;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import com.example.bean.Person;

import com.example.db.BaseDao;public classPersonDaoSQL {private BaseDao helper; //helper

/**

* 在构造方法中实例化helper

* @param context*/

publicPersonDaoSQL(Context context) {

helper= newBaseDao(context);

}/**

* 增

* @param person*/

public voidadd(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();

db.execSQL("insert into person (name, number) values(?, ?)", newString[] {person.getName(), person.getNumber()});

db.close();

}/**

* 按姓名查

* @param person

* @return*/

publicboolean find(Person person) {

SQLiteDatabase db=helper.getReadableDatabase();

Cursor cursor= db.rawQuery("select * from person where name = ?", newString[] {person.getName()});

boolean result=cursor.moveToNext();

cursor.close();

db.close();returnresult;

}/**

* 删除一条记录*/

public voiddelete(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();

db.execSQL("delete from person where name = ?", newString[]{person.getName()});

db.close();

}/**

* 更新*/

public voidupdate(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();

db.execSQL("update person set number = ? where name = ?", newString[]{person.getNumber(),person.getName()});

db.close();

}public ListfindAll() {

SQLiteDatabase db=helper.getReadableDatabase();

Cursor cursor= db.rawQuery("select id, name, number from person", null);

List persons = new ArrayList();

Person person= null;while(cursor.moveToNext()) {int id = cursor.getInt(cursor.getColumnIndex("id"));

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

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

person= newPerson(id, name, number);

persons.add(person);

}

cursor.close();

db.close();returnpersons;

}

}

View Code

利用API实现增删改查：

package com.example.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.example.bean.Person;

import com.example.db.BaseDao;public classPersonDaoAPI {private BaseDao helper; //helper

/**

* 在构造方法中实例化helper

* @param context*/

publicPersonDaoAPI(Context context) {

helper= newBaseDao(context);

}public longadd(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();

ContentValues values= newContentValues();

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

values.put("number", person.getNumber());long result = db.insert("person", null, values);

db.close();returnresult;

}public intdelete(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();int result = db.delete("person", "name = ?", newString[] {person.getName()});

db.close();returnresult;

}public intupdate(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();

ContentValues values= newContentValues();

values.put("number", person.getNumber());int result = db.update("person", values, "name=?", newString[] {person.getName()});

db.close();returnresult;

}publicboolean find(Person person) {

SQLiteDatabase db=helper.getWritableDatabase();

Cursor cursor= db.query("person", null, "name = ?", new String[]{person.getName()}, null, null, null);

boolean result=cursor.moveToNext();

db.close();returnresult;

}public ListfindAll() {

SQLiteDatabase db=helper.getWritableDatabase();

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

List persons = new ArrayList();

Person person= null;while(cursor.moveToNext()) {int id = cursor.getInt(cursor.getColumnIndex("id"));

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

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

person= newPerson(id, name, number);

persons.add(person);

}

db.close();returnpersons;

}

}

View Code

利用Junit进行测试，

注意一定要在AndroidManifest.xml中添加：

android:name="android.test.InstrumentationTestRunner"

android:targetPackage="com.example.db" />

和

package com.example.test;

import java.util.List;

import android.test.AndroidTestCase;

import com.example.bean.Person;

import com.example.dao.PersonDaoAPI;

import com.example.dao.PersonDaoSQL;

import com.example.db.BaseDao;public classTestPersonDB extends AndroidTestCase {/**

* 测试数据库的创建

* @throws Exception*/

public voidtestCreateDB() throws Exception{

BaseDao helper= newBaseDao(getContext());

helper.getReadableDatabase();

}/**

* 测试添加*/

public voidtestAdd() {//PersonDaoSQL dao = new PersonDaoSQL(getContext());

PersonDaoAPI dao = newPersonDaoAPI(getContext());for (int i = 0; i < 10 ; ++i) {

Person person= new Person("lisi" + i + 1, "1347515770" +i);

dao.add(person);

}

}/**

* 测试修改*/

public voidtestUpdate() {//PersonDaoSQL dao = new PersonDaoSQL(getContext());

PersonDaoAPI dao = newPersonDaoAPI(getContext());

Person person= new Person("lisi" + 11, "1347515770" + "x");

dao.update(person);

}/**

* 测试单个查询查询*/

public voidtestFind() {//PersonDaoSQL dao = new PersonDaoSQL(getContext());

PersonDaoAPI dao = newPersonDaoAPI(getContext());

Person person= new Person("lisi" + 21, "1347515770" + "x");

boolean result=dao.find(person);

assertEquals(true, result);

}/**

* 查询所有*/

public voidtestFindAll() {//PersonDaoSQL dao = new PersonDaoSQL(getContext());

PersonDaoAPI dao = newPersonDaoAPI(getContext());

List persons =dao.findAll();for(Person person : persons) {

System.out.println(person.getName());

}

}/**

* 删除*/

public voidtestDelete() {

PersonDaoSQL dao= newPersonDaoSQL(getContext());

Person person= new Person("lisi" + 21, "1347515770" + "x");

dao.delete(person);

}

}

View Code