黎活明8天快速掌握android視頻教程--17_建立資料庫與完成資料添刪改查

標籤：getc   set   package   ons   save   super   end   建立   one   

1、我們首先來看下整個項目

項目也是採用mvc的架構

 

package dB;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.widget.Toast;public class DbOpenHelper extends SQLiteOpenHelper{    public DbOpenHelper(Context context) {        super(context, "wy.db", null, 1);    }    @Override    public void onCreate(SQLiteDatabase db)    {     db.execSQL("CREATE TABLE person(personid integer primary key autoincrement, name varchar(20), phone VARCHAR(12) NULL)");    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    }}

動作表的介面類：

package service;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import java.util.ArrayList;import java.util.List;import dB.DbOpenHelper;import domain.Person;public class PersonService {private DbOpenHelper dbOpenHelper;    public PersonService(Context context) {        this.dbOpenHelper = new DbOpenHelper(context);    }    public void save(Person person){        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();        db.execSQL("insert into person(name, phone) values(?,?)",                new Object[]{person.getName(), person.getPhone()});    }    /**     * 刪除記錄     * @param name 記錄ID     */    public void delete(String name,String phone){        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();        db.execSQL("delete from person where name=? and phone=?", new Object[]{name,phone});    }    /**     * 更新記錄     * @param person     */    public void update(Person person,String name,String phone){        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();        db.execSQL("update person set name=?,phone=? where name=? and phone=?",                new Object[]{person.getName(), person.getPhone(),name,phone});    }    /**     * 查詢記錄     * @param name 記錄ID     * @return     */    public Person find(String name,String phone){        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();        Cursor cursor = db.rawQuery("select * from person where name=? and phone = ?", new String[]{name,phone});        if(cursor.moveToNext()){            int personid = cursor.getInt(cursor.getColumnIndex("personid"));            String name1 = cursor.getString(cursor.getColumnIndex("name"));            String phone1 = cursor.getString(cursor.getColumnIndex("phone"));            return new Person( name1, phone1);        }        cursor.close();        return null;    }    /**     * 分頁擷取記錄     * @param offset 跳過前面多少條記錄     * @param maxResult 每頁擷取多少條記錄     * @return     */    public List<Person> getScrollData(int offset, int maxResult){        List<Person> persons = new ArrayList<Person>();        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();        Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?",                new String[]{String.valueOf(offset), String.valueOf(maxResult)});        while(cursor.moveToNext()){            int personid = cursor.getInt(cursor.getColumnIndex("personid"));            /*這裡也可以寫成            *   String name = cursor.getString(1);            String phone = cursor.getString(2);             預設的表內建的id欄位為0 ，name為第一個欄位所有為1 ，phone為第二個欄位為2*/            String name = cursor.getString(cursor.getColumnIndex("name"));            String phone = cursor.getString(cursor.getColumnIndex("phone"));            persons.add(new Person( name, phone));        }        cursor.close();        return persons;    }    /**     * 擷取記錄總數     * @return     */    public long getCount(){        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();        Cursor cursor = db.rawQuery("select count(*) from person", null);        cursor.moveToFirst();        long result = cursor.getLong(0);//統計之後只有一個預設的欄位，所以為0        cursor.close();        return result;    }}

Person類對象是：

/** * 檔案名稱：Person.java * 著作權:著作權 (C) 中國電科30所三部 * 描述: * 修改人: wei.yuan * 修改時間:2015/1/9 * 修改內容:新增 */package domain;/** * 項目名稱：SQLLite1 * 類描述： * 建立人：wei.yuan * 建立時間：2015/1/9 11:07 * 修改人：wei.yuan * 修改時間：2015/1/9 11:07 * 修改備忘： * 著作權:著作權 (C) 中國電科30所三部 */public class Person{    private String name;    private String phone;    @Override    public String toString() {        return "Person{" +                "name=‘" + name + ‘\‘‘ +                ", phone=‘" + phone + ‘\‘‘ +                ‘}‘;    }    public Person(String name, String phone) {        this.name = name;        this.phone = phone;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getPhone() {        return phone;    }    public void setPhone(String phone) {        this.phone = phone;    }}

我們可以編寫一個測試載入器類來對上面的功能進行測試：

Android的測試類別必須繼承AndroidTestCase，該類的方法必須以test開頭

package testSQLLite;import android.test.AndroidTestCase;import android.util.Log;import android.widget.Toast;import java.util.List;import dB.DbOpenHelper;import domain.Person;import service.PersonService;public class TestSQLLite extends AndroidTestCase {      final  static String  TAG ="weiyuan";    public  void testCreateDb()    {        DbOpenHelper dbOpenHelper = new DbOpenHelper(getContext());        dbOpenHelper.getWritableDatabase();        Log.i(TAG,"資料庫建立成功");    }    public void testSave() throws Exception{        PersonService service = new PersonService(this.getContext());        for(int i = 0;i<20;i++)        {            service.save(new Person("weiyuan"+i,"12345"+i));        }        Log.i(TAG,"資料儲存成功");    }      /*主要尋找的是姓名和電話一起尋找，只要滿足了姓名和電話，才正確*/    public void testFind() throws Exception{        PersonService service = new PersonService(this.getContext());        Person person = service.find("chendong","456789");        Log.i(TAG, person.toString());        Log.i(TAG,"資料尋找成功");    }    /*刪除某個記錄*/    public void testdelete() throws Exception{        PersonService service = new PersonService(this.getContext());        service.delete("weiyuan1","123451");        Log.i(TAG,"資料刪除成功");    }    /*給新某個記錄*/    public void testupdate() throws Exception{        PersonService service = new PersonService(this.getContext());        service.update(new Person("chendong","456789"),"weiyuan2","123452");        Log.i(TAG, "資料修改成功");    }        /*獲得分頁的資料*/    public void testScrollData() throws Exception{        PersonService service = new PersonService(this.getContext());        List<Person> persons = service.getScrollData(0, 5);        for(Person person : persons){            Log.i(TAG, person.toString());        }    }    public void testCount() throws Exception{        PersonService service = new PersonService(this.getContext());        long result = service.getCount();        Log.i(TAG, result+"");    }}

 

黎活明8天快速掌握android視頻教程--17_建立資料庫與完成資料添刪改查