http://www.cnblogs.com/linjiqin/archive/2011/05/27/2059937.html
package com.ljq.domain;public class Person {private Integer id;private String name;private String phone;public Person() {super();}public Person(String name, String phone) {super();this.name &＃61; name;this.phone &＃61; phone;}public Person(Integer id, String name, String phone) {super();this.id &＃61; id;this.name &＃61; name;this.phone &＃61; phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id &＃61; id;}public String getName() {return name;}public void setName(String name) {this.name &＃61; name;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone &＃61; phone;}}
package com.ljq.db;import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {// 类没有实例化,是不能用作父类构造器的参数,必须声明为静态
private static final String DBNAME &＃61; "ljq.db";private static final int VERSION &＃61; 1;// 第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,// 设置为null,代表使用系统默认的工厂类
public DBOpenHelper(Context context) {super(context, DBNAME, null, VERSION);}&＃64;Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR(20), PHONE VARCHAR(20))");}&＃64;Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 注&＃xff1a;生产环境上不能做删除操作
db.execSQL("DROP TABLE IF EXISTS PERSON"); onCreate(db);}
}
package com.ljq.db;import java.util.ArrayList;
import java.util.List;import android.content.Context;
import android.database.Cursor;import com.ljq.domain.Person;public class PersonService {private DBOpenHelper dbOpenHelper &＃61; null;/*** 构造函数* * 调用getWritableDatabase()或getReadableDatabase()方法后&＃xff0c;会缓存SQLiteDatabase实例&＃xff1b;* 因为这里是手机应用程序&＃xff0c;一般只有一个用户访问数据库&＃xff0c;所以建议不关闭数据库&＃xff0c;保持连接状态。* getWritableDatabase()&＃xff0c;getReadableDatabase的区别是当数据库写满时&＃xff0c;调用前者会报错&＃xff0c;调用后者不会&＃xff0c;* 所以如果不是更新数据库的话&＃xff0c;最好调用后者来获得数据库连接。* * 对于熟悉SQL语句的程序员最好使用exeSQL(),rawQuery(),因为比较直观明了* * &＃64;param context*/ public PersonService(Context context){dbOpenHelper &＃61; new DBOpenHelper(context);}public void save(Person person){dbOpenHelper.getWritableDatabase().execSQL("insert into person(name, phone) values (?, ?)", new Object[]{person.getName(), person.getPhone()});}public void update(Person person){dbOpenHelper.getWritableDatabase().execSQL("update person set name&＃61;?, phone&＃61;? where id&＃61;?", new Object[]{person.getName(), person.getPhone(), person.getId()});}public void delete(Integer... ids){if(ids.length>0){StringBuffer sb &＃61; new StringBuffer();for(Integer id : ids){sb.append("?").append(",");}sb.deleteCharAt(sb.length() - 1);dbOpenHelper.getWritableDatabase().execSQL("delete from person where id in ("&＃43;sb&＃43;")", (Object[])ids);}}public Person find(Integer id){Cursor cursor &＃61; dbOpenHelper.getReadableDatabase().rawQuery("select id, name, phone from person where id&＃61;?", new String[]{String.valueOf(id)});if(cursor.moveToNext()){int personid &＃61; cursor.getInt(0);String name &＃61; cursor.getString(1);String phone &＃61; cursor.getString(2);return new Person(personid, name, phone);}return null;}public long getCount(){Cursor cursor &＃61; dbOpenHelper.getReadableDatabase().query("person", new String[]{"count(*)"}, null,null,null,null,null);if(cursor.moveToNext()){return cursor.getLong(0);}return 0;}/*** 分页* * &＃64;param startResult 偏移量&＃xff0c;默认从0开始* &＃64;param maxResult 每页显示的条数* &＃64;return*/public List<Person> getScrollData(int startResult, int maxResult){List<Person> persons &＃61; new ArrayList<Person>();//Cursor cursor &＃61; dbOpenHelper.getReadableDatabase().query("person", new String[]{"id, name, phone"}, // "name like ?", new String[]{"%ljq%"}, null, null, "id desc", "1,2");
Cursor cursor &＃61; dbOpenHelper.getReadableDatabase().rawQuery("select * from person limit ?,?", new String[]{String.valueOf(startResult), String.valueOf(maxResult)});while(cursor.moveToNext()) {int personid &＃61; cursor.getInt(0); String name &＃61; cursor.getString(1);String phone &＃61; cursor.getString(2);persons.add(new Person(personid, name, phone));}return persons;}}
package com.ljq.test;import java.util.List;import com.ljq.db.PersonService;
import com.ljq.domain.Person;import android.test.AndroidTestCase;
import android.util.Log;public class PersonServiceTest extends AndroidTestCase{private final String TAG &＃61; "PersonServiceTest";public void testSave() throws Exception{PersonService personService &＃61; new PersonService(this.getContext());personService.save(new Person("zhangsan1", "059188893343"));personService.save(new Person("zhangsan2", "059188893343"));personService.save(new Person("zhangsan3", "059188893343"));personService.save(new Person("zhangsan4", "059188893343"));personService.save(new Person("zhangsan5", "059188893343"));}public void testUpdate() throws Exception{PersonService personService &＃61; new PersonService(this.getContext());Person person &＃61; personService.find(1);person.setName("linjiqin");personService.update(person);}public void testFind() throws Exception{PersonService personService &＃61; new PersonService(this.getContext());Person person &＃61; personService.find(1);Log.i(TAG, person.getName());}public void testList() throws Exception{PersonService personService &＃61; new PersonService(this.getContext());List<Person> persons &＃61; personService.getScrollData(0, 10);for(Person person : persons){Log.i(TAG, person.getId() &＃43; " : " &＃43; person.getName());}}public void testCount() :/span>throws Exception{PersonService personService &＃61; new PersonService(this.getContext());Log.i(TAG, String.valueOf(personService.getCount()));}public void testDelete() throws Exception{PersonService personService &＃61; new PersonService(this.getContext());personService.delete(1);}public void testDeleteMore() throws Exception{PersonService personService &＃61; new PersonService(this.getContext());personService.delete(new Integer[]{2, 5, 6});}
}
京公网安备 11010802041100号