安卓增删改查用sql语句号码_Android SQL语句实现数据库的增删改查

本文介绍android中的数据库的增删改查

复习sql语法：

* 增

insert into info (name,phone) values ('wuyudong','111')

* 删

delete from person where name = 'wuyudong'

* 改

update person set number='119' where name='wuyudong'

* 查

select * from person

select * from person where name='wuyudong'

数据库文件是在 /data/data/包名/databases/xxx.db

下面使用代码完成相关的操作

首先定义一个Person类

packagecom.wuyudong.db.domain;

public classPerson {

private intid;

privateString name;

privateString number;

publicPerson() {

}

public Person(intid, String name, String number) {

this.id =id;

this.name =name;

this.number =number;

}

public intgetId() {

returnid;

}

public void setId(intid) {

this.id =id;

}

publicString getName() {

returnname;

}

public voidsetName(String name) {

this.name =name;

}

publicString getNumber() {

returnnumber;

}

public voidsetNumber(String number) {

this.number =number;

}

}

接着实现相关操作的代码：

packagecom.wuyudong.db.dao;

importjava.util.ArrayList;

importjava.util.List;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importcom.wuyudong.db.PersonSQLiteOpenHelper;

importcom.wuyudong.db.domain.Person;

public classPersonDao {

privatePersonSQLiteOpenHelper helper;

publicPersonDao(Context context) {

helper = newPersonSQLiteOpenHelper(context);

}

/*** 添加一条记录到数据库

*

* @paramname

* 姓名

* @paramnumber

* 电话

*/

public voidadd(String name, String number) {

SQLiteDatabase db =helper.getWritableDatabase();

db.execSQL("insert into person (name,number) values (?,?)",

newObject[] { name, number });

db.close();

}

/*** 查询记录是否存在

*

* @paramname

* 姓名 return true存在， false不存在

*/

public booleanfind(String name) {

SQLiteDatabase db =helper.getReadableDatabase();

Cursor cursor = db.rawQuery("select * from person where name=?",

newString[] { name });

boolean result =cursor.moveToNext();

cursor.close();

db.close();

returnresult;

}

/***

* @paramname

* 要修改的人的姓名

* @paramnewnumber

* 新的号码

*/

public voidupdate(String name, String newnumber) {

SQLiteDatabase db =helper.getReadableDatabase();

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

db.close();

}

/*** 删除一条记录

* @paramname

*/

public voiddelete(String name) {

SQLiteDatabase db =helper.getReadableDatabase();

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

db.close();

}

/*** 返回全部的数据库信息

* @return

*/

public ListfindAll() {

List persons = new ArrayList();

SQLiteDatabase db =helper.getReadableDatabase();

Cursor cursor = db.rawQuery("select * from 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 person = newPerson(id, name, number);

persons.add(person);

}

cursor.close();

db.close();

returnpersons;

}

}

完整的测试代码如下：

packagecom.wuyudong.db.test;

importjava.util.List;

importcom.wuyudong.db.PersonSQLiteOpenHelper;

importcom.wuyudong.db.dao.PersonDao;

importcom.wuyudong.db.domain.Person;

importandroid.database.sqlite.SQLiteDatabase;

importandroid.test.AndroidTestCase;

public class TestPersonDB extendsAndroidTestCase {

public void testCreateDB() throwsException {

PersonSQLiteOpenHelper helper = newPersonSQLiteOpenHelper(getContext());

SQLiteDatabase db =helper.getWritableDatabase();

}

public void testAdd() throwsException {

PersonDao dao = newPersonDao(getContext());

dao.add("wuyudong", "666");

}

public void testFind() throwsException {

PersonDao dao = newPersonDao(getContext());

boolean result = dao.find("zhangsan");

assertEquals(true, result);

}

public void testUpdate() throwsException {

PersonDao dao = newPersonDao(getContext());

dao.update("zhangsan", "655");

}

public void testDelete() throwsException {

PersonDao dao = newPersonDao(getContext());

dao.delete("zhangsan");

}

public void testFindall() throwsException {

PersonDao dao = newPersonDao(getContext());

List persons =dao.findAll();

for(Person person : persons) {

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

}

}

}