一）開發前準備工作，下載SQLite Expert工具（SQLite的可視化工具），大家可以去搜一下
? ? ? ?
二）工具準備好了，咱們開始開發吧
先貼一下項目的目錄結構：
?
1.寫一個獲得創建修改數據庫的工具類，這個類繼承自 SQLiteOpenHelper
?
package com.xiaobo.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DbOpenHelper extends SQLiteOpenHelper{
public DbOpenHelper(Context context) {// 初始化時把數據庫名和版本帶上
super(context, "itxiaobo.db", null, 3);
}
@Override
public void onCreate(SQLiteDatabase db) {// 數據庫第一次被創建的時候調用的
db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 數據庫版本更改后執行這個方法
db.execSQL("ALTER TABLE person ADD amount INTEGER NULL ");
}
}
?
?
2.然后再下一個測試類PersonServiceTest，測試一下是否成功創建的數據庫和表
? ?
package com.xiaobo.juit;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import com.xiaobo.domain.Person;
import com.xiaobo.service.DbOpenHelper;
import com.xiaobo.service.PersonService;
public class PersonServiceTest extends AndroidTestCase{
// 本測試類的專有TAG標簽，利于LogCat調試
private final static String TAG = "PersonServiceTest";
/**
* 測試創建或更改數據庫
* @throws Throwable
*/
public void testCreateDb() throws Throwable{
DbOpenHelper dbOpenHelper = new DbOpenHelper(getContext());
dbOpenHelper.getWritableDatabase();
}
}
?
? ?備注：寫單元測試時得在AndroidManifest.xml配置一下，看下圖
? ?
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.xiaobo.db"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="3"
android:targetSdkVersion="17" />
<instrumentation android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.xiaobo.db"
android:label="JUnit Test"/>
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<!-- 單元測試的library -->
<uses-library android:name="android.test.runner" />
<activity
android:name="com.xiaobo.db.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>
?
3.寫業務邏輯類PersonService，其中包含了增上改查，看代碼吧
??
package com.xiaobo.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.xiaobo.domain.Person;
public class PersonService {
private DbOpenHelper dbOpenHelper;
public PersonService(Context context) {
this.dbOpenHelper = new DbOpenHelper(context);
}
/**
* 添加記錄
* @param person
*/
public void save(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person(name, phone, amount) values(?,?)", new Object[]{person.getName(), person.getPhone(), person.getAmount()});
}
/**
* 根據id刪除記錄
* @param id
*/
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?", new Object[]{id});
}
/**
* 更新記錄
* @param person
*/
public void update(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=?, phone=?, amount=? where personid=?", new Object[]{person.getName(), person.getPhone(),person.getAmount(), person.getId()});
}
/**
* 根據id查找一條記錄
* @param id
* @return
*/
public Person find(Integer id){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person where personid=?", new String[]{id.toString()});
if(cursor.moveToFirst()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
return new Person(personid, name, phone, amount);
}
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(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
persons.add(new Person(personid, name, phone, amount));
}
cursor.close();
return persons;
}
/**
* 獲取記錄總數
* @return
*/
public long getCount(){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
long count = cursor.getLong(0);
return count;
}
/**
* 轉賬匯款事務
* @param fromId 匯款賬戶
* @param toId 接收賬戶
*/
public void payment(Integer fromId, Integer toId){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
db.beginTransaction(); // 開啟事務
try{
db.execSQL("update person set amount=amount-500 where personid=?", new Object[]{fromId});
db.execSQL("update person set amount=amount+500 where personid=?", new Object[]{toId});
// 結束事務有兩種情況，要么commit,要么rollback,是根據事務的標志決定的，默認為False，如果為True,就會提交
db.setTransactionSuccessful();
}finally{
db.endTransaction(); // 結束事務
}
}
}
?
4.在先前創建的PersonServiceTest類里面寫其他的測試方法，上代碼
??
package com.xiaobo.juit;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import com.xiaobo.domain.Person;
import com.xiaobo.service.DbOpenHelper;
import com.xiaobo.service.PersonService;
public class PersonServiceTest extends AndroidTestCase{
// 本測試類的專有TAG標簽，利于LogCat調試
private final static String TAG = "PersonServiceTest";
/**
* 測試創建或更改數據庫
* @throws Throwable
*/
public void testCreateDb() throws Throwable{
DbOpenHelper dbOpenHelper = new DbOpenHelper(getContext());
dbOpenHelper.getWritableDatabase();
}
/**
* 測試保存
* @throws Throwable
*/
public void testSave() throws Throwable{
PersonService personService = new PersonService(getContext());
personService.save(new Person(1, "yellow", "18706487378", 2000));
personService.save(new Person(2, "yuan", "18706697820", 3000));
personService.save(new Person(3, "zhen", "13658662813", 4000));
}
/**
* 測試根據id查找一條記錄
* @throws Throwable
*/
public void testFind() throws Throwable{
PersonService personService = new PersonService(getContext());
Person person = personService.find(1);
Log.i(TAG, person.toString());
}
/**
* 測試更新記錄
* @throws Throwable
*/
public void testUpdate() throws Throwable{
PersonService personService = new PersonService(getContext());
personService.update(new Person(2, "yellow", "18706487378", 5000));
personService.update(new Person(3, "yuan", "18706697820", 5000));
}
/**
* 測試刪除記錄
* @throws Throwable
*/
public void testDelete() throws Throwable{
PersonService personService = new PersonService(getContext());
personService.delete(1);
}
/**
* 測試獲得記錄總條數
* @throws Throwable
*/
public void testGetCount() throws Throwable{
PersonService personService = new PersonService(getContext());
Log.i(TAG, String.valueOf(personService.getCount()));
}
/**
* 測試分頁記錄
* @throws Throwable
*/
public void testGetScrollData() throws Throwable{
PersonService personService = new PersonService(getContext());
List<Person> persons = personService.getScrollData(0, 5);
for(Person person :persons){
Log.i(TAG, person.toString());
}
}
/**
* 測試轉賬匯款
* @throws Throwable
*/
public void testUpdateAmount() throws Throwable{
PersonService personService = new PersonService(getContext());
Person p01 = personService.find(2);
Person p02 = personService.find(3);
System.out.println(p01.toString());
System.out.println(p02.toString());
p01.setAmount(500);
p02.setAmount(600);
personService.update(p01);
personService.update(p02);
Log.i(TAG, p01.getAmount().toString());
Log.i(TAG, p02.getAmount().toString());
}
/**
* 測試轉賬匯款2
* @throws Throwable
*/
public void testPayment() throws Throwable{
PersonService personService = new PersonService(getContext());
Person p01 = personService.find(2);
Person p02 = personService.find(3);
// 打印轉賬之前賬戶信息
Log.i(TAG, p01.toString());
Log.i(TAG, p02.toString());
personService.payment(2, 3);
// 再次打印轉賬之后賬戶信息
Log.i(TAG, p01.toString());
Log.i(TAG, p02.toString());
}
}
?
5.大家可以下源代碼看一下，不是很會寫原理，呵呵，希望大家共同進步
? ?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
