android中SQLite資料庫的增刪改查

標籤：資料庫   測試   android   sqlite   安卓   

1.資料庫協助類PersonSQLiteOpenHelper

package com.wzw.sqllitedemo.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;/** *  * 資料庫協助類，用於建立管理資料庫 * @author Administrator * */public final class PersonSQLiteOpenHelper extends SQLiteOpenHelper {private String tag="PersonSQLiteOpenHelper";/** * 資料庫的建構函式 * @param context * @param name資料庫名稱 * @param factory遊標工廠 * @param version版本 */public PersonSQLiteOpenHelper(Context context) {super(context, "my.db", null, 1);}/** * 資料庫第一次建立時調用此方法 * 用於初始化表 */@Overridepublic void onCreate(SQLiteDatabase db) {//SQLiteDatabase對象已經串連資料庫，直接進行性資料庫操作String sql="create table person(_id integer primary key,name varchar(15),age integer);";db.execSQL(sql);//建立person表}/** * 資料庫的版本號碼更新時調用 * 用於更新資料庫的內容（刪除表，更新表，刪除表） */@Overridepublic void onUpgrade(SQLiteDatabase db, int  oldVersion, int newVersion) {}}

2.實體類person

package com.wzw.sqllitedemo.entities;public class Person {private int id;private int age;private String name;public int getId() {return id;}public void setId(int id) {this.id = id;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Person(int id, int age, String name) {super();this.id = id;this.age = age;this.name = name;}public Person() {super();// TODO Auto-generated constructor stub}@Overridepublic String toString() {return "Person [id=" + id + ", age=" + age + ", name=" + name + "]";}}

3.DAO資料庫操作類PersonDao

package com.wzw.sqllitedemo.dao;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.wzw.sqllitedemo.db.PersonSQLiteOpenHelper;import com.wzw.sqllitedemo.entities.Person;public class PersonDao {private PersonSQLiteOpenHelper mOpenHelper;/** *建構函式，誰調用需要傳遞一個context內容物件 * @param context */public PersonDao(Context context){mOpenHelper = new PersonSQLiteOpenHelper(context);}/** * 添加person表一條資料 * @param person */public void insert(Person person){SQLiteDatabase db=mOpenHelper.getWritableDatabase();if(db.isOpen()){//如果資料庫開啟，則進行插入操作//String sql="insert into person(name,age) values('李四',18);";db.execSQL("insert into person(name,age) values(?,?);", new Object[]{person.getName(),person.getAge()});db.close();}}public void delete(int id){SQLiteDatabase db=mOpenHelper.getWritableDatabase();//擷取可寫的資料庫物件if(db.isOpen()){//如果資料庫開啟，則進行插入操作db.execSQL("delete from person where _id=?", new Integer[]{id});db.close();}}public void update(int id,String name){SQLiteDatabase db=mOpenHelper.getWritableDatabase();if(db.isOpen()){//如果資料庫開啟，則進行插入操作db.execSQL("update person set name=? where _id=?", new Object[]{name,id});db.close();}}public List<Person> queryAll(){SQLiteDatabase db=mOpenHelper.getReadableDatabase();//獲得唯讀資料庫物件if(db.isOpen()){Cursor cursor=db.rawQuery("select _id,name,age from person", null);if(cursor.getCount()>0&&cursor!=null){List<Person> personList=new ArrayList<Person>();int id;String name;int age;while(cursor.moveToNext()){id=cursor.getInt(0);//取第0列的IDname=cursor.getString(1);//取第1列的nameage=cursor.getInt(2);//取第2列的agepersonList.add(new Person(id, age, name));}db.close();return personList;}db.close();}return null;}public Person queryItem(int id){SQLiteDatabase db=mOpenHelper.getReadableDatabase();if(db.isOpen()){Cursor cursor=db.rawQuery("select _id,name,age from person where id=?", new String[]{id+""});if(cursor!=null && cursor.moveToFirst()){int _id=cursor.getInt(0);String name=cursor.getString(1);int age=cursor.getInt(2);db.close();return new Person(_id, age, name);}db.close();}return null;}}

4.測試類別

package com.wzw.sqllitedemo.test;import java.util.List;import com.wzw.sqllitedemo.dao.PersonDao;import com.wzw.sqllitedemo.db.PersonSQLiteOpenHelper;import com.wzw.sqllitedemo.entities.Person;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;import android.util.Log;public class TestCase extends AndroidTestCase {private String tag="TestCase";public void test(){//資料庫什麼時候建立PersonSQLiteOpenHelper openHelp=new PersonSQLiteOpenHelper(getContext());//第一次串連資料庫的時建立資料庫，oncreate調用openHelp.getWritableDatabase();}public void testinsert(){PersonDao dao=new PersonDao(getContext());Person person=new Person(0,67,"zhang");dao.insert(person);}public void testDelete(){PersonDao dao=new PersonDao(getContext());dao.delete(1);}public void testUpdate(){PersonDao dao=new PersonDao(getContext());dao.update(2, "你妹");}public void testQueryAll(){PersonDao dao=new PersonDao(getContext());List<Person> personList=dao.queryAll();for (Person person : personList) {Log.i(tag,person.toString());}}public void testQueryItem(){PersonDao dao=new PersonDao(getContext());Person person=dao.queryItem(1);Log.i(tag, person.toString());}}

需要在AndroidManifest.xml檔案中聲明測試

<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"    package="com.wzw.sqllitedemo"    android:versionCode="1"    android:versionName="1.0" >    <span style="color:#ff6666;"><instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.wzw.sqllitedemo"></instrumentation></span>    <uses-sdk        android:minSdkVersion="8"        android:targetSdkVersion="17" />    <application        android:allowBackup="true"        android:icon="@drawable/ic_launcher"        android:label="@string/app_name"        android:theme="@style/AppTheme" >        <span style="color:#ff0000;"><uses-library android:name="android.test.runner"/></span>        <activity            android:name="com.wzw.sqllitedemo.MainActivity"            android:label="@string/app_name" >            <intent-filter>                <action android:name="android.intent.action.MAIN" />                <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>    </application></manifest>