十一、SQLite数据库增删改查操作案例

Person实体类

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 = name;        this.phone = phone;    }        public Person(Integer id, String name, String phone) {        super();        this.id = id;        this.name = name;        this.phone = 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;    }}

         

           

DBOpenHelper数据库关联类

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 = "ljq.db";    private static final int VERSION = 1;        // 第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,    // 设置为null,代表使用系统默认的工厂类    public DBOpenHelper(Context context) {        super(context, DBNAME, null, VERSION);    }    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL("CREATE TABLE PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR(20), PHONE VARCHAR(20))");    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        // 注：生产环境上不能做删除操作        db.execSQL("DROP TABLE IF EXISTS PERSON");         onCreate(db);    }}

            

               

PersonService业务类

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 = null;        /**     * 构造函数     *      * 调用getWritableDatabase()或getReadableDatabase()方法后，会缓存SQLiteDatabase实例；     * 因为这里是手机应用程序，一般只有一个用户访问数据库，所以建议不关闭数据库，保持连接状态。     * getWritableDatabase()，getReadableDatabase的区别是当数据库写满时，调用前者会报错，调用后者不会，     * 所以如果不是更新数据库的话，最好调用后者来获得数据库连接。     *      * 对于熟悉SQL语句的程序员最好使用exeSQL(),rawQuery(),因为比较直观明了     *      * @param context     */     public PersonService(Context context){        dbOpenHelper = 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=?, phone=? where id=?",                 new Object[]{person.getName(), person.getPhone(), person.getId()});    }        public void delete(Integer... ids){        if(ids.length>0){            StringBuffer sb = new StringBuffer();            for(Integer id : ids){                sb.append("?").append(",");            }            sb.deleteCharAt(sb.length() - 1);            dbOpenHelper.getWritableDatabase().execSQL("delete from person where id in ("+sb+")", (Object[])ids);        }    }        public Person find(Integer id){        Cursor cursor = dbOpenHelper.getReadableDatabase().rawQuery("select id, name, phone from person where id=?",                 new String[]{String.valueOf(id)});        if(cursor.moveToNext()){            int personid = cursor.getInt(0);            String name = cursor.getString(1);            String phone = cursor.getString(2);            return new Person(personid, name, phone);        }        return null;    }        public long getCount(){        Cursor cursor = dbOpenHelper.getReadableDatabase().query("person",                 new String[]{"count(*)"}, null,null,null,null,null);        if(cursor.moveToNext()){            return cursor.getLong(0);        }        return 0;    }        /**     * 分页     *      * @param startResult 偏移量，默认从0开始     * @param maxResult 每页显示的条数     * @return     */    public List<Person> getScrollData(int startResult, int maxResult){        List<Person> persons = new ArrayList<Person>();        //Cursor cursor = dbOpenHelper.getReadableDatabase().query("person", new String[]{"id, name, phone"},         //        "name like ?", new String[]{"%ljq%"}, null, null, "id desc", "1,2");        Cursor cursor = dbOpenHelper.getReadableDatabase().rawQuery("select * from person limit ?,?",                 new String[]{String.valueOf(startResult), String.valueOf(maxResult)});        while(cursor.moveToNext()) {            int personid = cursor.getInt(0);             String name = cursor.getString(1);            String phone = cursor.getString(2);            persons.add(new Person(personid, name, phone));        }        return persons;    }            }

             

                  

PersonServiceTest测试类

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 = "PersonServiceTest";        public void testSave() throws Exception{        PersonService personService = 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 = new PersonService(this.getContext());        Person person = personService.find(1);        person.setName("linjiqin");        personService.update(person);    }        public void testFind() throws Exception{        PersonService personService = new PersonService(this.getContext());        Person person = personService.find(1);        Log.i(TAG, person.getName());    }        public void testList() throws Exception{        PersonService personService = new PersonService(this.getContext());        List<Person> persons = personService.getScrollData(0, 10);        for(Person person : persons){            Log.i(TAG, person.getId() + " : " + person.getName());        }    }        public void testCount() throws Exception{        PersonService personService = new PersonService(this.getContext());        Log.i(TAG, String.valueOf(personService.getCount()));    }        public void testDelete() throws Exception{        PersonService personService = new PersonService(this.getContext());        personService.delete(1);    }        public void testDeleteMore() throws Exception{        PersonService personService = new PersonService(this.getContext());        personService.delete(new Integer[]{2, 5, 6});    }}

                 

            

运行结果