Sqlite3 增删改查操作实例

(1) 在android中使用sqlite数据库,首先需要了解SQLiteOpenHerper这个类, 是用来实现数据库初始化的一个类,我们需要继承这个类,初始化我们的数据库:

DBOpenHelper.java

package com.xiaoming.domain;

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

public class DBOpenHelper extends SQLiteOpenHelper{

	public DBOpenHelper(Context context){
		super(context, "sqlite.db", null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table persons (_id integer primary key autoincrement," +
				"name varchar null," +
				"age int null);" );
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

}

(2)然后我们需要一个业务类,来操作我们的数据库中的内容. 而且可能有很多种业务类, 对数据库中不同的表进行操作,我们这里写一个PersonService业务类, 其所用到的数据类是Person:

Person:

package com.xiaoming.domain;

public class Person {
	
	private String  name;
	private Integer age;
	private Integer personid;
	
	
	public Person() {
	    name= null;
		age      = null;
		personid = null;
	}
	
	
	public Person(Integer personid,String name, int age) {
		this.personid = personid;
		this.name = name;
		this.age = age;
	}
	
	public Person(String name, int age) {
		this.personid = null;
		this.name = name;
		this.age = age;
	}
	
	public Integer getPersonid() {
		return personid;
	}


	public void setPersonid(Integer personid) {
		this.personid = personid;
	}


	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
}

PersonService:

package com.xiaoming.service;

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

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

import com.xiaoming.domain.DBOpenHelper;
import com.xiaoming.domain.Person;


public class PersonService {

	Context      context = null;
	DBOpenHelper dbOpenHelper = null;
	

	public PersonService(Context context) {
		this.context = context;
		dbOpenHelper = new DBOpenHelper( context );
	}
	
	/**
	 * 添加记录
	 * @param p
	 */
	public void insert(Person p)
	{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("insert into persons(name,age) values(?,?);",
				new Object[]{p.getName(),p.getAge()});
	}
	
	/**
	 * 删除记录
	 * @param id
	 */
	public void delete(Integer id)
	{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from persons where _id=?;",
				new Object[]{id});
	}
	
	
	/**
	 * 更新记录
	 * @param p
	 */
	public void update(Person p)
	{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("update persons set name=?,age=? where _id=?",
				new Object[]{p.getName(),p.getAge(),p.getPersonid()} );
		
	}
	
	
	/**
	 * 查找记录
	 * @param id
	 * @return
	 */
	public Person find(Integer id)
	{
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from persons where _id=?", new String[]{id.toString()}) ;
		if( cursor.moveToFirst() )
		{
			int personid = cursor.getInt(cursor.getColumnIndex("_id"));
			String name  = cursor.getString(cursor.getColumnIndex("name"));
			int age      = cursor.getInt(cursor.getColumnIndex("age"));
			return new Person(personid,name,age);
		}
		cursor.close();
		return null;
	}
	
	/**
	 * 分页查找记录
	 * @param offset
	 * @param maxResult
	 * @return
	 */
	public List<Person> getScrollData(int offset, int maxResult)
	{
		List<Person> personlist = new ArrayList<Person>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from persons order by _id asc limit ?,? ",
				new String[]{String.valueOf(offset), String.valueOf(maxResult)}) ;
		while( cursor.moveToNext() )
		{
			int personid = cursor.getInt(cursor.getColumnIndex("_id"));
			String name  = cursor.getString(cursor.getColumnIndex("name"));
			int age      = cursor.getInt(cursor.getColumnIndex("age"));
			personlist.add(new Person(personid,name,age) );
		}
		cursor.close();
		return personlist;
	}
	
	/**
	 * 得到记录数
	 * @return
	 */
	public long getCount()
	{
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from persons",null );
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		return result;
	}
	
}

(3)然后需要写一个测试类PersonServiceTest:

package com.xiaoming.test;

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

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

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

public class PersonServiceTest extends AndroidTestCase {

	private static final String TAG = "PersonServiceTest";
	public void testInsert()
	{
		PersonService pService = new PersonService(getContext());
		Person p = new Person("王强",40);
		Person p1 = new Person("小花",40);
		Person p2 = new Person("小狗",40);
		Person p3 = new Person("小猫",40);
		Person p4 = new Person("自傲做",40);
		Person p5 = new Person("我晕哦",40);
		Person p6 = new Person("么得",40);
		Person p7 = new Person("张建",40);
		pService.insert(p1);
		pService.insert(p2);
		pService.insert(p3);
		pService.insert(p4);
		pService.insert(p5);
		pService.insert(p6);
		pService.insert(p7);
	}
	
	public void testdelete()
	{
		PersonService pService = new PersonService(getContext());
		pService.delete(1);
	}
	
	public void testUpdate()
	{
		PersonService pService = new PersonService(getContext());
		Person p = pService.find(5);
		if( p ==  null )
		{
			Log.i(TAG,"id="+p.getPersonid()+"的人没有找到");
			return ;
		}
		p.setName("哈哈哈哈");
		pService.update(p);
	}
	
	public void testFind()
	{
		PersonService pService = new PersonService(getContext());
		Person p = pService.find(2);
		Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());
	}
	
	public void testGetScrollData()
	{
		PersonService pService = new PersonService(getContext());
		ArrayList<Person> pList = (ArrayList<Person>) pService.getScrollData(1, 5);
		for(Person p:pList)
		{
			Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());
		}
	}
	
	public void testGetCount()
	{
		PersonService pService = new PersonService(getContext());
		long  count = pService.getCount();
		Log.i(TAG,""+count);
	}
}