一）开发前准备工作，下载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.大家可以下源代码看一下，不是很会写原理，呵呵，希望大家共同进步
? ?
?