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類

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

 

Android SQL語句實現資料庫的增刪改查