还是那句话,经常重复的代码要总结起来
1.首先建一个person实例:
Person.java
package morgen.domain; public class Person { private Integer id; private String name; private String phone; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public Person(Integer id, String name, String phone) { this.id = id; this.name = name; this.phone = phone; } public Person(String name, String phone) { this.name = name; this.phone = phone; } @Override public String toString() { return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]"; } }
2.接着创建数据库文件和表
DBOpenHelper.java
package morgen.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DBOpenHelper extends SQLiteOpenHelper { public DBOpenHelper(Context context) { super(context, "morgen.db", null, 2); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE person(id integer primary key autoincrement, name varchar(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL"); } }
3.对数据库进行操作
PersonService.java
package morgen.service; import java.util.ArrayList; import java.util.List; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import morgen.db.DBOpenHelper; import morgen.domain.Person; public class PersonService { private DBOpenHelper dbOpenHelper; public PersonService(Context context){ dbOpenHelper = new DBOpenHelper(context); } /** * 保存数据 * @param person */ public void save(Person person){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("insert into person(name,phone) values(?,?)", new Object[]{person.getName(), person.getPhone()}); //db.close(); } /** * 删除记录 * @param id 记录ID */ public void delete(Integer id){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("delete from person where id=?", new Object[]{id}); } /** * 更新记录 * @param person */ public void update(Person person){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("update person set name=?,phone=? where id=?", new Object[]{person.getName(), person.getPhone(), person.getId()}); } /** * 获取记录 * @param id 记录id * @return */ public Person find(Integer id){ SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from person where id=?", new String[]{id.toString()}); if(cursor.moveToFirst()){ String phone = cursor.getString(cursor.getColumnIndex("phone")); String name = cursor.getString(cursor.getColumnIndex("name")); cursor.close(); return new Person(id, name, phone); } return null; } /** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取的记录数 * @return */ public List<Person> getScrollData(int offset, int maxResult){ List<Person> persons = new ArrayList<Person>(); SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from person order by id asc limit ?,?", new String[]{String.valueOf(offset), String.valueOf(maxResult)}); while(cursor.moveToNext()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String phone = cursor.getString(cursor.getColumnIndex("phone")); String name = cursor.getString(cursor.getColumnIndex("name")); persons.add(new Person(id, name, phone)); } cursor.close(); return persons; } /** * 获取记录总数 * @return */ public long getCount(){ SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select count(*) from person", null); cursor.moveToFirst(); long count = cursor.getLong(0); cursor.close(); return count; } }