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());        }    }}