19. SQLite 数据库操作

1. 创建实体类Person

public class Person { 

 public int personId;

 public String name; 

 public String sex;

 public int age; 

 public String phone; 

 public Person() { } 

 public Person(String name, String sex, int age) { 

 this.name = name; 

 this.sex = sex;

 this.age = age; 

 }

 

public Person(String name, String sex, int age, String phone) { 

 this(name, sex, age);

 this.phone = phone;

 } 

 public Person(int personId, String name, String sex, int age, String phone) { 

 this(name, sex, age, phone); 

 this.personId = personId;

 }

 }

2. 书写DBOpenHelper类继承SQLiteOpenHelper

public class DBOpenHelper extends SQLiteOpenHelper { 

 public DBOpenHelper(Context context) { 

 super(context, "myDB.db", null, 2); 

//保存在<包>/data/data/databases 

 } 

 @Override 

 public void onCreate(SQLiteDatabase sqLiteDatabase) { 

//数据库每一次调用时创建 

 sqLiteDatabase.execSQL("create table person(" + "personid integer primary key autoincrement, name varchar(20), " + "sex varchar(10), age integer)"); 

 } 

 @Override 

 public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {

 //数据库版本改变时执行 

 sqLiteDatabase.execSQL("alter table person add phone varchar(12) null");

 } 

}

3. 书写服务类完成增删改查

public class PersonService { 

 private DBOpenHelper dbOpenHelper; 

 public PersonService(Context context) { 

 this.dbOpenHelper = new DBOpenHelper(context); 

 } 

 public void save(Person person) { 

 SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 

 ContentValues values = new ContentValues(); 

 values.put("name", person.name); 

 values.put("sex", person.sex); 

 values.put("age", person.age);

 values.put("phone", person.phone); 

 db.insert("person", null, values);

 } 

 public void delete(int personId) { 

 SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 

 db.delete("person", "personid=?", new String[]{String.valueOf(personId)});

 } 

 public void update(Person person) { 

 SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 

 ContentValues values = new ContentValues(); 

 values.put("name", person.name); 

 values.put("sex", person.sex); 

 values.put("age", person.age);

 values.put("phone", person.phone); 

 db.update("person", values, "personid=?", new String[]{String.valueOf(person.pers onId)}); 

 } 

 public Person find(int personId) { 

 SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

 Cursor cursor = db.query("person", null, "personid=?", new String[]{String.valueOf( personId)}, null, null, null); 

 Person person = null; 

 while(cursor.moveToNext()) { 

 int id = cursor.getInt(cursor.getColumnIndex("personid")); 

 String name = cursor.getString(cursor.getColumnIndex("name")); 

 String sex = cursor.getString(cursor.getColumnIndex("sex")); 

 int age = cursor.getInt(cursor.getColumnIndex("age")); 

 String phone = cursor.getString(cursor.getColumnIndex("phone")); 

 person = new Person(id, name, sex, age, phone); 

 } 

 return person; 

 } 

 public List<Person> getScrollData(int offset, int maxResult) {

 List<Person> persons = new ArrayList<Person>(); 

 SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 

 Cursor cursor = db.query("person", null, null, null, null, null, "personid asc", offset + ", " + maxResult); 

 while(cursor.moveToNext()) { 

 int id = cursor.getInt(cursor.getColumnIndex("personid")); 

 String name = cursor.getString(cursor.getColumnIndex("name")); 

 String sex = cursor.getString(cursor.getColumnIndex("sex")); 

 int age = cursor.getInt(cursor.getColumnIndex("age")); 

 String phone = cursor.getString(cursor.getColumnIndex("phone"));

 persons.add(new Person(id, name, sex, age, phone));

 } 

 return persons;

 } 

 public long getCount() { 

 SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

 Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null);  cursor.moveToFirst(); 

 long result = cursor.getLong(0); 

 return result; 

 } 

 }