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}@Overridepublic void onCreate(SQLiteDatabase db) {String sql="create table Person(personId integer primary key autoincrement,PersonName varchar(20))";db.execSQL(sql);db.close();}@Overridepublic 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;}@Overridepublic 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;}}

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