本文介绍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类
package com.wuyudong.db.domain;
public class Person {
private int id;
private String name;
private String number;
public Person() {
}
public Person(int id, String name, String number) {
this.id = id;
this.name = name;
this.number = number;
}
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 getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
}
接着实现相关操作的代码：
package com.wuyudong.db.dao; import java.util.ArrayList;
import java.util.List; import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase; import com.wuyudong.db.PersonSQLiteOpenHelper;
import com.wuyudong.db.domain.Person; public class PersonDao {
private PersonSQLiteOpenHelper helper; public PersonDao(Context context) {
helper = new PersonSQLiteOpenHelper(context);
} /**
* 添加一条记录到数据库
*
* @param name
* 姓名
* @param number
* 电话
*/
public void add(String name, String number) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("insert into person (name,number) values (?,?)",
new Object[] { name, number });
db.close();
} /**
* 查询记录是否存在
*
* @param name
* 姓名 return true存在， false不存在
*/
public boolean find(String name) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person where name=?",
new String[] { name });
boolean result = cursor.moveToNext();
cursor.close();
db.close();
return result;
} /**
*
* @param name
* 要修改的人的姓名
* @param newnumber
* 新的号码
*/
public void update(String name, String newnumber) {
SQLiteDatabase db = helper.getReadableDatabase();
db.execSQL("update person set number=? where name=?", new Object[]{newnumber,name});
db.close();
} /**
* 删除一条记录
* @param name
*/
public void delete(String name) {
SQLiteDatabase db = helper.getReadableDatabase();
db.execSQL("delete from person where name=?", new Object[]{name});
db.close();
} /**
* 返回全部的数据库信息
* @return
*/
public List<Person> findAll() {
List<Person> persons = new ArrayList<Person>();
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 = new Person(id, name, number);
persons.add(person);
}
cursor.close();
db.close();
return persons;
} }
完整的测试代码如下：
package com.wuyudong.db.test; import java.util.List; import com.wuyudong.db.PersonSQLiteOpenHelper;
import com.wuyudong.db.dao.PersonDao;
import com.wuyudong.db.domain.Person; import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase; public class TestPersonDB extends AndroidTestCase {
public void testCreateDB() throws Exception {
PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
SQLiteDatabase db = helper.getWritableDatabase();
} public void testAdd() throws Exception {
PersonDao dao = new PersonDao(getContext());
dao.add("wuyudong", "666");
} public void testFind() throws Exception {
PersonDao dao = new PersonDao(getContext());
boolean result = dao.find("zhangsan");
assertEquals(true, result);
} public void testUpdate() throws Exception {
PersonDao dao = new PersonDao(getContext());
dao.update("zhangsan", "655");
} public void testDelete() throws Exception {
PersonDao dao = new PersonDao(getContext());
dao.delete("zhangsan");
} public void testFindall() throws Exception {
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.findAll();
for (Person person : persons) {
System.out.println(person.toString());
}
}
}