Android学习笔记--读写SQLite

步聚一：建一个继承了SQLiteOpenHelper的类，代码如下：

package com.example.sqliteoper.SQLiteDbHelper;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper extends SQLiteOpenHelper {
	private static final String tag="SQLiteOpenHelper";
	public DbHelper(Context context) {
		super(context, "mysqlite.db", null, 3);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql="create table Person(personId integer primary key autoincrement,PersonName varchar(20))";
		db.execSQL(sql);
		db.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String esql="select count(*) from sqlite_master where tbl_name='Person' and type='table' and sql like '%PersonPhone%'";
		Cursor cursor=db.rawQuery(esql, null);
		cursor.moveToFirst();
		int cnt=cursor.getInt(0);
		if(cnt==0){
		String sql="alter table Person add PersonPhone varchar(20)";
		db.execSQL(sql);
		Log.i(tag, "修改了数据库结构");
		}
		db.close();
	}

}

步骤二：创建Model类

package com.example.sqliteoper.Model;

public class Person {
	private int PersonId;
	private String PersonName;
	private String PersonPhone;
	public int getPersonId() {
		return PersonId;
	}
	public Person() {}
	public Person(int personId, String personName, String personPhone) {
		PersonId = personId;
		PersonName = personName;
		PersonPhone = personPhone;
	}
	@Override
	public String toString() {
		return "Person [PersonId=" + PersonId + ", PersonName=" + PersonName
				+ ", PersonPhone=" + PersonPhone + "]";
	}
	public void setPersonId(int personId) {
		PersonId = personId;
	}
	public String getPersonName() {
		return PersonName;
	}
	public void setPersonName(String personName) {
		PersonName = personName;
	}
	public String getPersonPhone() {
		return PersonPhone;
	}
	public void setPersonPhone(String personPhone) {
		PersonPhone = personPhone;
	}
	public Person(String personName, String personPhone) {
		PersonName = personName;
		PersonPhone = personPhone;
	}
	
}

步骤三：创建db操作类，PersonServer是用的sql来操作的，个人感觉这种方式灵活性高，也可以用db自带的增删查改方法，如后面的OtherServer

package com.example.sqliteoper.Service;

import com.example.sqliteoper.Model.Person;
import com.example.sqliteoper.SQLiteDbHelper.DbHelper;

import java.util.*;
import android.content.*;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class PersonService {
	private Context context;
	
	public PersonService(Context context) {
		this.context = context;
	}

	public void Insert(Person p){
		DbHelper helper=new DbHelper(context);
		String sql="insert into Person (PersonName,PersonPhone) values (?,?)";
		SQLiteDatabase db=helper.getWritableDatabase();
		db.execSQL(sql, new Object[]{p.getPersonName(),p.getPersonPhone()});
		db.close();
	}
	
	public void Update(Person p){
		DbHelper helper=new DbHelper(context);
		String sql="update Person set PersonName=?,PersonPhone=? where PersonId=?";
		SQLiteDatabase db=helper.getWritableDatabase();
		db.execSQL(sql, new Object[]{p.getPersonName(),p.getPersonPhone(),p.getPersonId()});
		db.close();
	}
	
	public void Delete(int personId){
		DbHelper helper=new DbHelper(context);
		String sql="delete from  Person  where PersonId=?";
		SQLiteDatabase db=helper.getWritableDatabase();
		db.execSQL(sql, new Object[]{personId});
		db.close();
	}
	
	public Person GetPerson(int personId){
		DbHelper helper=new DbHelper(context);
		String sql="select * from  Person  where PersonId=?";
		SQLiteDatabase db=helper.getReadableDatabase();
		 Cursor cursor=db.rawQuery(sql, new String[]{String.valueOf(personId)});
		if(cursor.isFirst())
		{
			Person p=new Person();
			p.setPersonName(cursor.getString(cursor.getColumnIndex("PersonName")));
			p.setPersonPhone(cursor.getString(cursor.getColumnIndex("PersonPhone")));
			p.setPersonId(cursor.getInt(cursor.getColumnIndex("personId")));
			return p;
		}
		db.close();
		return null;
	}
	
	/*
	 * 取分页数据
	 * @param strWhere:筛选条件
	 * @param orderBy:排序方式，以order by 开头
	 * @param pageIndex:当前页码
	 * @param pageSize:每页记录数
	 */
	public List<Person> GetPersonList(String strWhere,String orderBy,int pageIndex,int pageSize){
		DbHelper helper=new DbHelper(context);
		int start=(pageIndex-1)*pageSize;
		String sql="select * from Person where 1=1 "+strWhere+orderBy+" limit ?,?";
		SQLiteDatabase db=helper.getReadableDatabase();
		Cursor cursor=db.rawQuery(sql, new String[]{String.valueOf(start),String.valueOf(pageSize)});
		List<Person> list=new ArrayList<Person>();
		while(cursor.moveToNext()){
			Person p=new Person();
			p.setPersonName(cursor.getString(cursor.getColumnIndex("PersonName")));
			p.setPersonPhone(cursor.getString(cursor.getColumnIndex("PersonPhone")));
			p.setPersonId(cursor.getInt(cursor.getColumnIndex("personId")));
			list.add(p);
		}
		db.close();
		return list;
	}
	
	public int GetRowCount(String strWhere)
	{
		DbHelper helper=new DbHelper(context);
		String sql="select count(*) from Person where 1=1 "+strWhere;
		SQLiteDatabase db=helper.getReadableDatabase();
		Cursor cursor=db.rawQuery(sql, null);
		cursor.moveToFirst();
		int cnt= cursor.getInt(0);
		db.close();
		return cnt;
	}
}

package com.example.sqliteoper.Service;
import java.util.*;

import com.example.sqliteoper.Model.Person;
import com.example.sqliteoper.SQLiteDbHelper.DbHelper;

import android.content.*;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class OtherService {
private Context context;
	
	public OtherService(Context context) {
		this.context = context;
	}

	public void Insert(Person p){
		DbHelper helper=new DbHelper(context);
		SQLiteDatabase db=helper.getWritableDatabase();
		ContentValues cvs=new ContentValues();
		cvs.put("PersonName", p.getPersonName());
		cvs.put("PersonPhone", p.getPersonPhone());
		db.insert("Person", null, cvs);
		db.close();
	}
	
	public void Update(Person p){
		DbHelper helper=new DbHelper(context);
		SQLiteDatabase db=helper.getWritableDatabase();
		ContentValues cvs=new ContentValues();
		cvs.put("PersonName", p.getPersonName());
		cvs.put("PersonPhone", p.getPersonPhone());
		db.update("Person", cvs, "PersonId=?", new String[]{String.valueOf(p.getPersonId())});
		db.close();
	}
	
	public void Delete(int personId){
		DbHelper helper=new DbHelper(context);
		SQLiteDatabase db=helper.getWritableDatabase();
		db.delete("Person","PersonId=?", new String[]{String.valueOf(personId)});
		db.close();
	}
	
	public Person GetPerson(int personId){
		DbHelper helper=new DbHelper(context);
		SQLiteDatabase db=helper.getReadableDatabase();
		 Cursor cursor=db.query("Person", null, "PersonId=?", new String[]{String.valueOf(personId)}, null, null, null);
		if(cursor.moveToFirst())
		{
			Person p=new Person();
			p.setPersonName(cursor.getString(cursor.getColumnIndex("PersonName")));
			p.setPersonPhone(cursor.getString(cursor.getColumnIndex("PersonPhone")));
			p.setPersonId(cursor.getInt(cursor.getColumnIndex("personId")));
			return p;
		}
		db.close();
		return null;
	}
	
	/*
	 * 取分页数据
	 * @param strWhere:筛选条件
	 * @param orderBy:排序方式，不要order by
	 * @param pageIndex:当前页码
	 * @param pageSize:每页记录数
	 */
	public List<Person> GetPersonList(String strWhere,String orderBy,int pageIndex,int pageSize){
		DbHelper helper=new DbHelper(context);
		int start=(pageIndex-1)*pageSize;
		SQLiteDatabase db=helper.getReadableDatabase();
		Cursor cursor=db.query("Person", null, strWhere, null,null , null, orderBy, start+","+pageSize);
		List<Person> list=new ArrayList<Person>();
		while(cursor.moveToNext()){
			Person p=new Person();
			p.setPersonName(cursor.getString(cursor.getColumnIndex("PersonName")));
			p.setPersonPhone(cursor.getString(cursor.getColumnIndex("PersonPhone")));
			p.setPersonId(cursor.getInt(cursor.getColumnIndex("personId")));
			list.add(p);
		}
		db.close();
		return list;
	}
	
	public int GetRowCount(String strWhere)
	{
		DbHelper helper=new DbHelper(context);
		SQLiteDatabase db=helper.getReadableDatabase();
		Cursor cursor=db.query("Person", new String[]{"count(*)"}, strWhere, null, null, null, null);
		cursor.moveToFirst();
		int cnt= cursor.getInt(0);
		db.close();
		return cnt;
	}
}

再后面就是测试和调用了，不再贴代码