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