Android之怎麼使用SQLite資料庫(增、刪、改、查、分頁等)以及ListView顯示資料

由於剛接觸android開發，故此想把學到的基礎知識記錄一下，以備查詢，故此寫的比較囉嗦：

步驟如下：

一、介紹：

此文主要是介紹怎麼使用android自帶的資料庫SQLite，以及把後臺的資料用ListView控制元件顯示

二、新建一個android工程——DBSQLiteOperate

工程目錄：

三、清單列表AndroidManifest.xml的配置為：

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.dboperate"
    android:versionCode="1"
    android:versionName="1.0" >

<uses-sdk android:minSdkVersion="8" />

</manifest>

四、main.xml配置清單：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
    <LinearLayout android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >

</LinearLayout>
    <ListView
        android:id="@+id/listView"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent" >
    </ListView>

</LinearLayout>

五、item.xml配置清單：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="horizontal" >

</LinearLayout>

六、string.xml配置清單：

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="hello">Hello World, DBSQLiteOperateActivity!</string>
    <string name="app_name">ExampleDBSQLiteOperate8</string>
    <string name="name">姓名</string>
    <string name="phone">電話</string>
    <string name="amount">存款</string>

</resources>

七、DBSQLiteOperateActivity.java Activity類的原始碼：

package com.example.dboperate;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.example.adapter.PersonAdapter;
import com.example.domain.Person;
import com.example.service.PersonService;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;

public class DBSQLiteOperateActivity extends Activity {

ListView listView;
PersonService personService;
OnItemClickListener listViewListener;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        listViewListener = new OnItemClickListener(){

   @Override
   public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
    //得到listView控制元件
    ListView listView = (ListView)parent;

    //1、如果使用自定義介面卡，返回的是Person物件
    //得到該條目資料
//    Person person = (Person)listView.getItemAtPosition(position);
//    //廣播出去
//    Toast.makeText(getApplicationContext(), person.toString(), Toast.LENGTH_LONG).show();

    //2、如果使用showList2()方法中的介面卡時，則取得的值是不一樣的,返回的是cursor
//    Cursor cursor = (Cursor)listView.getItemAtPosition(position);
//    int personid = cursor.getInt(cursor.getColumnIndex("_id"));
//    Toast.makeText(getApplicationContext(), personid+"", Toast.LENGTH_LONG).show();

    //3、如果使用showList()方法中的介面卡時，則取得的值是不一樣的,返回的是map
    @SuppressWarnings("unchecked")
    Map<String,Object> map = (Map)listView.getItemAtPosition(position);
    String name = map.get("name").toString();
    String personid = map.get("personid").toString();
    Toast.makeText(getApplicationContext(), personid +"-"+ name, Toast.LENGTH_LONG).show();

   }

        };

        listView = (ListView) this.findViewById(R.id.listView);
        listView.setOnItemClickListener(listViewListener);
        personService = new PersonService(this);
        showList();
    }
private void showList() {
  List<Person> persons = personService.getScrollData(0, 50);
  List<HashMap<String,Object>> data = new ArrayList<HashMap<String,Object>>();
  for(Person person : persons){
   HashMap<String,Object> item = new HashMap<String,Object>();
   item.put("name", person.getName());
   item.put("phone", person.getPhone());
   item.put("amount", person.getAmount());
   item.put("personid", person.getId());
   data.add(item);
  }
  SimpleAdapter adapter = new SimpleAdapter(this,data,R.layout.item, new String[]{"name","phone","amount"}, new int[]{R.id.name,R.id.phone,R.id.amount});
     listView.setAdapter(adapter);
}

public void showList2(){
  Cursor cursor = personService.getCursorScrollData(0, 50);
  //該介面卡要求返回的結果集cursor必須包含_id欄位，所以需要對取得結果集進行處理
  SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,R.layout.item,cursor,new String[]{"name","phone","amount"}, new int[]{R.id.name,R.id.phone,R.id.amount} );
  listView.setAdapter(adapter);
}
/**
* 自定義介面卡
*/
public void showList3(){
  List<Person> persons = personService.getScrollData(0, 50);
  /**
   * 第一個引數：上下文context，第二個引數：要顯示的資料，第三個引數：繫結的條目介面
   */
  PersonAdapter adapter = new PersonAdapter(this, persons, R.layout.item);
  listView.setAdapter(adapter);
}
}

八、person.java 實體類原始碼：

package com.example.domain;

public class Person {

private Integer id;
private String name;
private String phone;
private Integer amount;
public Integer getId() {
  return id;
}
public void setId(Integer id) {
  this.id = id;
}
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;
}

public Person(String name, String phone) {
  this.name = name;
  this.phone = phone;
}
public Person(Integer id, String name, String phone,Integer amount) {
  super();
  this.id = id;
  this.name = name;
  this.phone = phone;
  this.amount = amount;
}
public Person() {
  super();
}
public Integer getAmount() {
  return amount;
}
public void setAmount(Integer amount) {
  this.amount = amount;
}
@Override
public String toString() {
  return "Person [id=" + id + ", name=" + name + ", phone=" + phone
    + ", amount=" + amount + "]";
}

}

九、DBOperateHelper.java 業務類原始碼：

package com.example.service;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DBOperateHelper extends SQLiteOpenHelper {

public DBOperateHelper(Context context) {//預設建立的資料庫檔案儲存在<包名>/database/
//第一個引數是上下文，第二個引數是資料庫名稱，第三個是遊標工廠為null時使用資料庫預設的遊標工廠,第四個是資料庫版本號但是不能為0，一般大於0
super(context, "smallpig", null, 4);
}

/**
* 資料庫每一次被建立時被呼叫
*/
@Override
public void onCreate(SQLiteDatabase sqldb) {
  sqldb.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) {
  //db.execSQL("alter table person add phone varchar(12) null");\
  db.execSQL("alter table person add amount Integer null");
}

}

十、PersonService.java 業務類原始碼：

package com.example.service;

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

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

import com.example.domain.Person;

public class PersonService {

private DBOperateHelper dbOperateHelper;

public PersonService(Context context) {
  this.dbOperateHelper = new DBOperateHelper(context);
}
    /**
     * 儲存記錄
     * @param person
     */
public void save(Person person){
  //得到資料庫例項，裡面封裝了資料庫操作方法
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
     //sqldb.execSQL("insert into person(name,phone) values('"+person.getName()+"','"+person.getPhone()+"')");
  //利用佔位符可以避免注入，但是注意陣列參與與佔位符對應的欄位要一一對應
  sqldb.execSQL("insert into person(name,phone,amount) values(?,?,?)",new Object[]{person.getName(),person.getPhone(),person.getAmount()});
     //關閉資料庫
  sqldb.close();
}

/**
* 刪除記錄
* @param id
*/
public void delete(Integer id){
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
  sqldb.execSQL("delete from person where personid=?",new Object[]{id});
  sqldb.close();
}

/**
* 更新記錄
* @param person
*/
public void update(Person person){
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
  sqldb.execSQL("update person set name=?,phone=?,amount=? where personid=?",new Object[]{person.getName(),person.getPhone(),person.getAmount(),person.getId()});
  sqldb.close();
}

/**
* 通過ID查詢記錄
* @param id
* @return
*/
public Person find(Integer id){
  /**
   * getWritableDatabase 與 getReadableDatabase 的區別：
   * getReadableDatabase會先返回getWritableDatabase(可寫)，如果呼叫getWritableDatabase失敗
   * 則才會呼叫getReadableDatabase後續方法，使資料庫只讀
   * 當寫入的資料超過資料庫大小則呼叫getWritableDatabase會失敗
   * 所以只讀時則可以使用此方法，其它情況(只要不是超過資料庫大小)也可以使用此方法
   */
  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
  Cursor cursor = sqldb.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});
  int personid;
  String name;
  String phone;
  int amount;
  Person person = null;
  if(cursor.moveToFirst()){
   personid = cursor.getInt(cursor.getColumnIndex("personid"));
   name = cursor.getString(cursor.getColumnIndex("name"));
   phone = cursor.getString(cursor.getColumnIndex("phone"));
   amount = cursor.getInt(cursor.getColumnIndex("amount"));
   person = new Person(personid,name,phone,amount);
  }
  cursor.close();
  return person;
}

/**
* 返回指定長度記錄，limit 3,5,適用於分頁
* @param offset 起始
* @param maxResult 長度
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){

  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
  Cursor cursor = sqldb.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
  int personid;
  String name;
  String phone;
  int amount;
  Person person = null;
  List<Person> persons = new ArrayList<Person>();
  while(cursor.moveToNext()){
   personid = cursor.getInt(cursor.getColumnIndex("personid"));
   name = cursor.getString(cursor.getColumnIndex("name"));
   phone = cursor.getString(cursor.getColumnIndex("phone"));
   amount = cursor.getInt(cursor.getColumnIndex("amount"));
   person = new Person(personid,name,phone,amount);
   persons.add(person);
  }
  cursor.close();
  return persons;
}

/**
* 返回cursor
* @param offset 起始
* @param maxResult 長度
* @return
*/
public Cursor getCursorScrollData(int offset,int maxResult){

  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
  Cursor cursor = sqldb.rawQuery("select personid as _id,name,phone,amount from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});

  return cursor;
}

/**
* 返回總記錄數
* @return
*/
public long getCount(){
  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();

  Cursor cursor = sqldb.rawQuery("select count(*) from person", null);
  //該查詢語句值返回一條語句
  cursor.moveToFirst();
  long result = cursor.getLong(0);
  cursor.close();
  return result;
}

public void payment(){
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
  sqldb.beginTransaction();//開啟事務
  try{
   sqldb.execSQL("update person set amount = amount -10 where personid=1");
   sqldb.execSQL("update person set amount = amount + 10 where personid=2");
   sqldb.setTransactionSuccessful();//設定事務標誌位true
  } finally {
   //結束事務：有兩種情況：commit\rollback，事務提交或者回滾是由事務的標識決定的
   //事務為ture則提交，事務為flase則回滾，預設為false
   sqldb.endTransaction();
  }
}
}

十一、OtherPersonService.java 業務類原始碼：

package com.example.service;

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

import com.example.domain.Person;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class OtherPersonService {

private DBOperateHelper dbOperateHelper;

public OtherPersonService(Context context) {
  this.dbOperateHelper = new DBOperateHelper(context);
}
    /**
     * 儲存記錄
     * @param person
     */
public void save(Person person){
  //得到資料庫例項，裡面封裝了資料庫操作方法
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
     //sqldb.execSQL("insert into person(name,phone) values('"+person.getName()+"','"+person.getPhone()+"')");
  //利用佔位符可以避免注入，但是注意陣列參與與佔位符對應的欄位要一一對應
  //sqldb.execSQL("insert into person(name,phone) values(?,?)",new Object[]{person.getName(),person.getPhone()});

  ContentValues values = new ContentValues();
  values.put("name", person.getName());
  values.put("phone", person.getPhone());
  values.put("amount", person.getAmount());
  //第一個引數是表名，第三個為欄位值集合，第二個引數是空值欄位，當第三個欄位值集合為空時，系統會自動插入一條第二個引數為空的sql語句
  //否則當第三個引數為空時，如果第二個引數也為空，那麼插入表就會找不到插入的欄位資訊，會報錯
  sqldb.insert("person", "name", values );
  //關閉資料庫
  sqldb.close();
}

/**
* 刪除記錄
* @param id
*/
public void delete(Integer id){
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
  //sqldb.execSQL("delete from person where personid=?",new Object[]{id});
  //第一個引數是表名，第二個是where後面的條件用佔位符表示，第三個對應占位符為引數值
  sqldb.delete("person", "personid=?", new String[]{Integer.toString(id)});
  sqldb.close();
}

/**
* 更新記錄
* @param person
*/
public void update(Person person){
  SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
  //sqldb.execSQL("update person set name=?,phone=? where personid=?",new Object[]{person.getName(),person.getPhone(),person.getId()});
  //第一個引數為表名，第二個是一個更新值集合，採用鍵值對的形式，每個更新的欄位對應更新值
  //第三個引數是where後面條件欄位用佔位符標識，第四個引數是對應where佔位符的值
  ContentValues values = new ContentValues();
  values.put("name", person.getName());
  values.put("phone", person.getPhone());
  values.put("amount", person.getAmount());
  sqldb.update("person", values , "personid=?", new String[]{person.getId().toString()});
  sqldb.close();
}

/**
* 通過ID查詢記錄
* @param id
* @return
*/
public Person find(Integer id){
  /**
   * getWritableDatabase 與 getReadableDatabase 的區別：
   * getReadableDatabase會先返回getWritableDatabase(可寫)，如果呼叫getWritableDatabase失敗
   * 則才會呼叫getReadableDatabase後續方法，使資料庫只讀
   * 當寫入的資料超過資料庫大小則呼叫getWritableDatabase會失敗
   * 所以只讀時則可以使用此方法，其它情況(只要不是超過資料庫大小)也可以使用此方法
   */
  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
  //Cursor cursor = sqldb.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});
  //第一個引數是表名；第二個引數是查詢顯示的欄位，null時預設查詢顯示所有欄位；
  //第三個引數是where查詢條件佔位符；第四個是佔位符對應的值；
  //第五個引數是group by條件；第六個是having條件；第七個是order by條件
  Cursor cursor = sqldb.query("person", null, "personid=?", new String[]{id.toString()}, null, null, null);
  int personid;
  String name;
  String phone;
  int amount;
  Person person = null;
  if(cursor.moveToFirst()){
   personid = cursor.getInt(cursor.getColumnIndex("personid"));
   name = cursor.getString(cursor.getColumnIndex("name"));
   phone = cursor.getString(cursor.getColumnIndex("phone"));
   amount = cursor.getInt(cursor.getColumnIndex("amount"));
   person = new Person(personid,name,phone,amount);
  }
  cursor.close();
  return person;
}

/**
* 返回指定長度記錄，limit 3,5,適用於分頁
* @param offset 起始
* @param maxResult 長度
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){

  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
  //Cursor cursor = sqldb.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
  //第一個引數是表名；第二個引數是查詢顯示的欄位，null時預設查詢顯示所有欄位；
  //第三個引數是where查詢條件佔位符；第四個是佔位符對應的值；
  //第五個引數是group by條件；第六個是having條件；第七個是order by條件
  //第八個引數是limit ?,? 條件
  Cursor cursor = sqldb.query("person", null, null, null, null, null, "personid",offset+","+maxResult);

  int personid;
  String name;
  String phone;
  int amount;
  Person person = null;
  List<Person> persons = new ArrayList<Person>();
  while(cursor.moveToNext()){
   personid = cursor.getInt(cursor.getColumnIndex("personid"));
   name = cursor.getString(cursor.getColumnIndex("name"));
   phone = cursor.getString(cursor.getColumnIndex("phone"));
   amount = cursor.getInt(cursor.getColumnIndex("amount"));
   person = new Person(personid,name,phone,amount);
   persons.add(person);
  }
  cursor.close();
  return persons;
}

/**
* 返回總記錄數
* @return
*/
public long getCount(){
  SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
  //Cursor cursor = sqldb.rawQuery("select count(*) from person", null);
  //第一個引數是表名；第二個引數是查詢顯示的欄位，null時預設查詢顯示所有欄位；
  //第三個引數是where查詢條件佔位符；第四個是佔位符對應的值；
  //第五個引數是group by條件；第六個是having條件；第七個是order by條件
  Cursor cursor = sqldb.query("person", new String[]{"count(*)"}, null, null, null, null, null);
  //該查詢語句值返回一條語句
  cursor.moveToFirst();
  long result = cursor.getLong(0);
  cursor.close();
  return result;
}

}

十二、PersonServiceTest.java 單元測試類原始碼：

package com.example.test;

import java.util.List;

import com.example.domain.Person;
import com.example.service.DBOperateHelper;
import com.example.service.PersonService;import android.test.AndroidTestCase;
import android.util.Log;

public class PersonServiceTest extends AndroidTestCase {

public void testCreateDB() throws Exception{
  DBOperateHelper dbHelper = new DBOperateHelper(getContext());
  dbHelper.getWritableDatabase();
}
public void testSave() throws Exception{
  PersonService ps = new PersonService(getContext());
  for(int i=1;i<=100;i++){
   Person person = new Person();
   person.setName("我是"+i);
   person.setPhone(String.valueOf(Long.parseLong("18888888800")+i));
   ps.save(person);
   Log.i("PersonService",person.toString());
  }
}
public void testDelete() throws Exception{
  PersonService ps = new PersonService(getContext());
  ps.delete(10);
}
public void testUpdate() throws Exception{
  PersonService ps = new PersonService(getContext());
  ps.update(new Person(1,"xiaopang","18887654321",0));
}
public void testFind() throws Exception{
  PersonService ps = new PersonService(getContext());
  Person person = ps.find(1);
  Log.i("PersonService", person.toString());
}
public void testGetScrollData() throws Exception{
  PersonService ps = new PersonService(getContext());
  List<Person> persons = ps.getScrollData(3, 5);
  for(Person person:persons){
   Log.i("PersonService",person.toString());
  }
}

public void testGetCount() throws Exception{
  PersonService ps = new PersonService(getContext());
  Long count = ps.getCount();
  Log.i("PersonService",count.toString());
}
public void testUpdateAmount() throws Exception{
  PersonService ps = new PersonService(getContext());
  Person person1 = ps.find(1);
  Person person2 = ps.find(2);
  person1.setAmount(100);
  person2.setAmount(100);
  ps.update(person1);
  ps.update(person2);
}
public void testPayment() throws Exception{
  PersonService ps = new PersonService(getContext());
  ps.payment();
}
}

十三、OtherPersonServiceTest 單元測試類原始碼：

package com.example.test;

import java.util.List;

import com.example.domain.Person;
import com.example.service.DBOperateHelper;
import com.example.service.OtherPersonService;

import android.test.AndroidTestCase;
import android.util.Log;

public class OtherPersonServiceTest extends AndroidTestCase {
public void testCreateDB() throws Exception{
  DBOperateHelper dbHelper = new DBOperateHelper(getContext());
  dbHelper.getWritableDatabase();
}
public void testSave() throws Exception{
  OtherPersonService ps = new OtherPersonService(getContext());
  for(int i=1;i<=100;i++){
   Person person = new Person();
   person.setName("我是"+i);
   person.setPhone(String.valueOf(Long.parseLong("18888888800")+i));
   ps.save(person);
   Log.i("PersonService",person.toString());
  }
}
public void testDelete() throws Exception{
  OtherPersonService ps = new OtherPersonService(getContext());
  ps.delete(10);
}
public void testUpdate() throws Exception{
  OtherPersonService ps = new OtherPersonService(getContext());
  ps.update(new Person(1,"xiaopang","18887654321",0));
}
public void testFind() throws Exception{
  OtherPersonService ps = new OtherPersonService(getContext());
  Person person = ps.find(1);
  Log.i("PersonService", person.toString());
}
public void testGetScrollData() throws Exception{
  OtherPersonService ps = new OtherPersonService(getContext());
  List<Person> persons = ps.getScrollData(3, 5);
  for(Person person:persons){
   Log.i("PersonService",person.toString());
  }
}
public void testGetCount() throws Exception{
  OtherPersonService ps = new OtherPersonService(getContext());
  Long count = ps.getCount();
  Log.i("PersonService",count.toString());
}
}
十四、注意事項以及相關知識點：

1、掌握SQLite資料庫如何建立資料庫、建立表、維護欄位等操作

繼承SQLiteOpenHelper類，建構函式呼叫父類建構函式建立資料庫，利用onCreate建立表，利用onUpgrade更新表欄位資訊

2、掌握SQLite資料庫如何增、刪、改、查以及分頁

取得SQLiteDatabase的例項，然後呼叫該例項的方法可以完成上述操作

SQLiteDataBase提供兩種操作上述功能的方式：一是直接呼叫execSQL書寫sql語句，另一種是通過insert、update、delete、query等方法來傳值來拼接sql，前一種適合熟練掌握sql 語句的

3、對需要資料同步的處理請新增事務處理，熟悉事務的處理方式

4、瞭解各個方法引數的意義以及傳值

5、掌握ListView顯示後臺資料的使用方法

SimpleAdapter、SimpleCursorAdapter以及自定義介面卡的使用，以及OnItemClickListener取值時各個介面卡返回值的區別以及取值方法

6、多學、多記、多練、多思，加油！

Android之怎麼使用SQLite資料庫(增、刪、改、查、分頁等)以及ListView顯示資料

相關文章