Android中SQLite的简单crud操作

  在Android中也需要存储数据，5种存储方式中就有数据库的存储，android和ios都使用了SQLite这款嵌入式小巧型数据库，只消耗几十K内存，却可以完成关系型数据库的

  大部分功能。它的特点有很多，可以百度一下，更加全面了解SQLite这款数据库。

  下面就是完成简单的crud操作：

   建立如图所示的目录结构：

  

   这个PersonProvider可以不用建，因为这是我写Content Provider时用到的类：

   首先需要一个继承了SQLiteOpenHelper 的类：

  

package com.database.server;

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

public class SQLiteHelper extends SQLiteOpenHelper {

	public SQLiteHelper(Context context) {
		// 第一个参数为上下文内容，第二个需要创建的数据库名，第三个为版本号（可随意定）
		super(context, "text.db", null, 1);
	}

	// 数据库第一次使用时调用该方法，是否是第一次，具体看上面的版本号。
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE person(userid integer primary key autoincrement,name varchar(20))");
	}

	// 当版本号改变时，调用该方法。
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("ALTER TABLE person ADD password integer");
	}
}
这里类似于JDBC中的连接操作了，下面就是CRUD操作，我封装了一个来处理：
 
package com.database.server;

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

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

import com.database.domain.Person;

public class PersonService {

	private SQLiteHelper sqlite = null;

	public PersonService(Context context) {

		this.sqlite = new SQLiteHelper(context);
	}

	// 保存
	public void save(Person person) {
		SQLiteDatabase db = sqlite.getWritableDatabase();
		db.execSQL("insert into person (name,password) values(?,?)",
				new Object[] { person.getName(), person.getPassword() });
	}

	// 删除
	public void delete(Integer id) {
		SQLiteDatabase db = sqlite.getWritableDatabase();
		db.execSQL("delete from person where userid=?", new Object[] { id });
	}

	// 更新
	public void update(Person person) {
		SQLiteDatabase db = sqlite.getWritableDatabase();
		db.execSQL(
				"update person set name = ?,password=? where userid=?",
				new Object[] { person.getName(), person.getPassword(),
						person.getUserid() });
	}

	// 查找
	public Person find(Integer id) {
		SQLiteDatabase db = sqlite.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from person where userid=?",
				new String[] { id.toString() }); // 该方法会返回一个游标类型
		if (cursor.moveToFirst()) {
			int userid = cursor.getInt(cursor.getColumnIndex("userid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String password = cursor.getString(cursor
					.getColumnIndex("password"));
			return new Person(userid, name, password);
		}
		cursor.close();
		return null;
	}

	// 分页查找
	public List<Person> getScrollDate(int offset, int maxResult) {
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase db = sqlite.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from person order by userid asc limit ? , ?", // 分页语句
				new String[] { String.valueOf(offset),
						String.valueOf(maxResult) });
		while (cursor.moveToNext()) {
			int userid = cursor.getInt(cursor.getColumnIndex("userid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String password = cursor.getString(cursor
					.getColumnIndex("password"));
			persons.add(new Person(userid, name, password));
		}
		cursor.close();
		return persons;
	}

	// 获取数据总数
	public long getCount() {
		SQLiteDatabase db = sqlite.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from person", null); // 返回值最小为1
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}

	// 事务
	public void payment() {
		SQLiteDatabase db = sqlite.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL("update person set amount = amount-10 where userid=22");
			db.execSQL("update person set amount = amount+10 where userid=23");
			db.setTransactionSuccessful(); // 设置事务的标志为true
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
	}
}


这个amount字段是我来ADD的进去的，具体就是修改版本号，然后使用相同的方法，加了进去。目的是为了使用事务时测试用的的一个字段。

Person 这个JavaBean：

package com.database.domain;

public class Person {
	private Integer userid;
	private String name;
	private String password;
	private Integer amount;

	public Person() {

	}
	public Person(Integer userid, String name, String password) {
		this.userid = userid;
		this.name = name;
		this.password = password;
	}
	public Person(Integer userid, String name, String password, Integer amount) {
		this.userid = userid;
		this.name = name;
		this.password = password;
		this.amount = amount;
	}
	public Integer getAmount() {
		return amount;
	}

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

	public Person(String name, String password) {
		this.name = name;
		this.password = password;
	}

	public Integer getUserid() {
		return userid;
	}

	public void setUserid(Integer userid) {
		this.userid = userid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString() {
		return "Person [userid=" + userid + ", name=" + name + ", password="
				+ password + "]";
	}

}
最后是测试类：

package com.database.test;

import java.util.List;

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

import com.database.domain.Person;
import com.database.server.PersonService;
import com.database.server.SQLiteHelper;

public class TestPersonService extends AndroidTestCase {

	private final static String TAG = "TestPersonService";

	private PersonService service;

	public void testCreateBD() throws Exception {
		SQLiteHelper sqlite = new SQLiteHelper(getContext());
		sqlite.getWritableDatabase();
	}

	@Override
	protected void setUp() throws Exception {
		service = new PersonService(this.getContext());
	}

	public void testSave() throws Exception {
		service.save(new Person("zhangsan", "123456"));
	}

	public void testFind() throws Exception {
		Person person = service.find(1);
		Log.i(TAG, person.toString());
	}

	public void testUpdate() throws Exception {
		Person person = service.find(1);
		person.setName("lisi");
		service.update(person);
	}

	public void testCount() throws Exception {
		long result = service.getCount();
		Log.i(TAG, result + "");
	}

	public void testScrollData() throws Exception {
		List<Person> persons = service.getScrollDate(20, 1);
		for (Person person : persons) {
			Log.i(TAG, person.toString());
		}
	}

	public void testDelete() throws Exception {
		service.delete(20);
	}

	public void testPayment() throws Exception {
		service.payment();
	}
}

创建的数据在/data/data/[Package_Name]/databases/下的text.db，可以导出桌面上用SQLite expert Professional这款软件来查看。
输出的数据可以在LogCat上查看，记得加下过滤条件，这个更加方便查看