Sqlite3 增刪改查操作實例

jopen 9年前發布 | 38K 次閱讀 SQLite3 SQLite 數據庫服務器

1) 在android中使用sqlite數據庫,首先需要了解SQLiteOpenHerper這個類, 是用來實現數據庫初始化的一個類,我們需要繼承這個類,初始化我們的數據庫:

DBOpenHelper.java

import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; public class DBOpenHelper extends SQLiteOpenHelper{ public DBOpenHelper(Context context){ super(context, "sqlite.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table persons (_id integer primary key autoincrement," + "name varchar null," + "age int null);" ); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } }

(2)然后我們需要一個業務類,來操作我們的數據庫中的內容. 而且可能有很多種業務類, 對數據庫中不同的表進行操作,我們這里寫一個PersonService業務類, 其所用到的數據類是Person:

Person:

public class Person {  

    private String  name;  
    private Integer age;  
    private Integer personid;  


    public Person() {  
        name= null;  
        age      = null;  
        personid = null;  
    }  


    public Person(Integer personid,String name, int age) {  
        this.personid = personid;  
        this.name = name;  
        this.age = age;  
    }  

    public Person(String name, int age) {  
        this.personid = null;  
        this.name = name;  
        this.age = age;  
    }  

    public Integer getPersonid() {  
        return personid;  
    }  


    public void setPersonid(Integer personid) {  
        this.personid = personid;  
    }  


    public String getName() {  
        return name;  
    }  
    public void setName(String name) {  
        this.name = name;  
    }  
    public int getAge() {  
        return age;  
    }  
    public void setAge(int age) {  
        this.age = age;  
    }  

}

PersonService:

import java.util.ArrayList;  
import java.util.List;  

import android.content.Context;  
import android.database.Cursor;  
import android.database.sqlite.SQLiteDatabase;  

import com.xiaoming.domain.DBOpenHelper;  
import com.xiaoming.domain.Person;  


public class PersonService {  

    Context      context = null;  
    DBOpenHelper dbOpenHelper = null;  


    public PersonService(Context context) {  
        this.context = context;  
        dbOpenHelper = new DBOpenHelper( context );  
    }  

    /** 
     * 添加記錄 
     * @param p 
     */  
    public void insert(Person p)  
    {  
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
        db.execSQL("insert into persons(name,age) values(?,?);",  
                new Object[]{p.getName(),p.getAge()});  
    }  

    /** 
     * 刪除記錄 
     * @param id 
     */  
    public void delete(Integer id)  
    {  
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
        db.execSQL("delete from persons where _id=?;",  
                new Object[]{id});  
    }  


    /** 
     * 更新記錄 
     * @param p 
     */  
    public void update(Person p)  
    {  
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
        db.execSQL("update persons set name=?,age=? where _id=?",  
                new Object[]{p.getName(),p.getAge(),p.getPersonid()} );  

    }  


    /** 
     * 查找記錄 
     * @param id 
     * @return 
     */  
    public Person find(Integer id)  
    {  
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();  
        Cursor cursor = db.rawQuery("select * from persons where _id=?", new String[]{id.toString()}) ;  
        if( cursor.moveToFirst() )  
        {  
            int personid = cursor.getInt(cursor.getColumnIndex("_id"));  
            String name  = cursor.getString(cursor.getColumnIndex("name"));  
            int age      = cursor.getInt(cursor.getColumnIndex("age"));  
            return new Person(personid,name,age);  
        }  
        cursor.close();  
        return null;  
    }  

    /** 
     * 分頁查找記錄 
     * @param offset 
     * @param maxResult 
     * @return 
     */  
    public List<Person> getScrollData(int offset, int maxResult)  
    {  
        List<Person> personlist = new ArrayList<Person>();  
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();  
        Cursor cursor = db.rawQuery("select * from persons order by _id asc limit ?,? ",  
                new String[]{String.valueOf(offset), String.valueOf(maxResult)}) ;  
        while( cursor.moveToNext() )  
        {  
            int personid = cursor.getInt(cursor.getColumnIndex("_id"));  
            String name  = cursor.getString(cursor.getColumnIndex("name"));  
            int age      = cursor.getInt(cursor.getColumnIndex("age"));  
            personlist.add(new Person(personid,name,age) );  
        }  
        cursor.close();  
        return personlist;  
    }  

    /** 
     * 得到記錄數 
     * @return 
     */  
    public long getCount()  
    {  
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();  
        Cursor cursor = db.rawQuery("select count(*) from persons",null );  
        cursor.moveToFirst();  
        long result = cursor.getLong(0);  
        return result;  
    }  

}

(3)然后需要寫一個測試類PersonServiceTest:

import java.util.ArrayList; import java.util.List; import com.xiaoming.domain.DBOpenHelper; import com.xiaoming.domain.Person; import com.xiaoming.service.PersonService; import android.test.AndroidTestCase; import android.util.Log; public class PersonServiceTest extends AndroidTestCase { private static final String TAG = "PersonServiceTest"; public void testInsert() { PersonService pService = new PersonService(getContext()); Person p = new Person("王強",40); Person p1 = new Person("小花",40); Person p2 = new Person("小狗",40); Person p3 = new Person("小貓",40); Person p4 = new Person("自傲做",40); Person p5 = new Person("我暈哦",40); Person p6 = new Person("么得",40); Person p7 = new Person("張建",40); pService.insert(p1); pService.insert(p2); pService.insert(p3); pService.insert(p4); pService.insert(p5); pService.insert(p6); pService.insert(p7); } public void testdelete() { PersonService pService = new PersonService(getContext()); pService.delete(1); } public void testUpdate() { PersonService pService = new PersonService(getContext()); Person p = pService.find(5); if( p == null ) { Log.i(TAG,"id="+p.getPersonid()+"的人沒有找到"); return ; } p.setName("哈哈哈哈"); pService.update(p); } public void testFind() { PersonService pService = new PersonService(getContext()); Person p = pService.find(2); Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge()); } public void testGetScrollData() { PersonService pService = new PersonService(getContext()); ArrayList<Person> pList = (ArrayList<Person>) pService.getScrollData(1, 5); for(Person p:pList) { Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge()); } } public void testGetCount() { PersonService pService = new PersonService(getContext()); long count = pService.getCount(); Log.i(TAG,""+count); } }

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1420634263453.html

SQLite3 SQLite 數據庫服務器

Sqlite3 增刪改查操作實例

相關經驗

相關資訊

相關文檔

目錄