在Android应用中使用SQLite数据库（传智播客视频笔记）

在Android应用中使用SQLite数据库

应用生成的数据库文件保存为/data/data/应用包名/databases/name.db，生成的文件可用SQLite Expert打开

（name是自定义的，例如，我的应用是保存在：/data/data/com.sinaapp.sql/databases/ssun.db中）

SQLiteActivity.java源码：

package com.sinaapp.sql;

import com.sinaapp.sql.R;

import android.app.Activity;
import android.os.Bundle;

public class SQLiteActivity extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
    }
}

Pseron.java源码：

package com.sinaapp.domain;

public class Person {
	private String name;
	private Integer id;
	private String phone;
	private Integer amount; 
	
	public Person() {}

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

	public Integer getAmount() {
		return amount;
	}

	public void setAmount(Integer amount) {
		this.amount = amount;
	}

	public String getName() {
		return name; 
	}

	public void setName(String name) {
		this.name = name;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
}

DBOpenHelper.java源码：

package com.sinaapp.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, "ssun.db", null, 3);//数据库版本号改变，将会执行onUpgrade方法
	}

	@Override
	public void onCreate(SQLiteDatabase db) {//第一次使用时执行的方法
		// TODO Auto-generated method stub
		db.execSQL("create table person(id integer primary key autoincrement,name varchar(20),phone varchar(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("alter table person add amount int(10)");
	}

}

PersonService.java源码：

package com.sinaapp.service;

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

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

import com.sinaapp.domain.Person;

public class PersonService {
	private DBOpenHelper helper;
	
	public PersonService(Context context){
		this.helper = new DBOpenHelper(context);
	}
	
	public void payment(){
		SQLiteDatabase  db = helper.getWritableDatabase();
		try{
			db.beginTransaction();
			db.execSQL("update person set amount = amount - 10 where id = 3");
			db.execSQL("update person set amount = amount + 10 where id = 4");
			db.setTransactionSuccessful();
		}finally{
			db.endTransaction();
		}
	}
	
	/**
	 * 添加记录
	 * @param person
	 */
	public void add(Person person){
		SQLiteDatabase  db = helper.getWritableDatabase();
		db.execSQL("insert into person(name,phone,amount) values(?,?,?) ",
				new Object[]{person.getName(),person.getPhone(),person.getAmount()});
	}
	
	/**
	 * 删除记录
	 * @param integer 将要删除的记录的ID号
	 */
	public void delete(Integer integer){
		SQLiteDatabase  db = helper.getWritableDatabase();
		db.execSQL("delete from person where id = ?",new Object[]{integer});
	}
	
	/**
	 * 更新记录
	 * @param person 更新的记录
	 */
	public void update(Person person){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update person set name=?, phone=?, amount=? where id = ?",
				new Object[]{person.getName(),person.getPhone(),person.getAmount(),person.getId()});
	}
	
	/**
	 * 查找记录
	 * @param integer
	 * @return
	 */
	public Person find(Integer integer){
		Person person = null;
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor =  db.rawQuery("select * from person where id = ?",
										new String[]{integer.toString()});
		if(cursor.moveToFirst()){
			int id = cursor.getInt(cursor.getColumnIndex("id"));	
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			person = new Person(name,id,phone,amount); 
		}
		return person;
	}
	
	/**
	 * 分页显示记录
	 * @param start 开始记录号
	 * @param length 记录长度
	 * @return
	 */
	public List<Person> getPersons(Integer start, Integer length){
		List<Person> persons = new ArrayList<Person>();
		Person person = null;
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor =  db.rawQuery("select * from person order by id limit ?, ?",
										new String[]{start.toString(),length.toString()});
		while(cursor.moveToNext()){
			int id = cursor.getInt(cursor.getColumnIndex("id"));	
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			person = new Person(name,id,phone,amount); 
			persons.add(person);
			person = null;
		}
		cursor.close();
		return persons;
	}
	
	/**
	 * 得到记录数
	 * @return
	 */
	public Integer getCounts(){
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor =  db.rawQuery("select count(*) from person",null);
		cursor.moveToFirst();
		Integer counts = cursor.getInt(0);
		return counts;
	}
}

单元测试TestService.java源码：

package com.sinaapp.test;

import java.util.List;

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

import com.sinaapp.domain.Person;
import com.sinaapp.service.DBOpenHelper;
import com.sinaapp.service.PersonService;

public class TestService extends AndroidTestCase {
	public void testCreateDB() {
		DBOpenHelper helper = new DBOpenHelper(this.getContext());
		helper.getWritableDatabase();
	}
	
	public void testAdd(){
		PersonService service = new PersonService(this.getContext());
		service.add(new Person("luolin","12345",100));
	}
	
	public void testUpdate(){
		PersonService service = new PersonService(this.getContext());
		service.update(new Person("zhang",4,"2222222",200));
	}
	
	public void testFind(){
		PersonService service = new PersonService(this.getContext());
		Person person = service.find(1);
		Log.i("TestService", person.toString());
	}
	
	public void testDel(){
		PersonService service = new PersonService(this.getContext());
		service.delete(2);
	}
	
	public void testGetCounts(){
		PersonService service = new PersonService(this.getContext());
		Integer i = service.getCounts();
		Log.i("TestService", i.toString());
	}
	
	public void testGetPersons(){
		PersonService service = new PersonService(this.getContext());
		List<Person> persons = service.getPersons(0, 4);
		for(Person p : persons){
			Log.i("TestService", p.toString());
		}
	}
	
	public void testPayment(){
		PersonService service = new PersonService(this.getContext());
		service.payment();
	}
}

AndroidManifest.xml

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

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

    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:name=".SQLiteActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <uses-library android:name="android.test.runner"/>
    </application>
    <instrumentation android:name="android.test.InstrumentationTestRunner"
        			 android:targetPackage="com.sinaapp.sql" />

</manifest>