还是那句话,经常重复的代码要总结起来
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 + "]"; } }
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=?",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,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; } }