Sqlite3 增删改查操作实例

jopen 5年前

1) 在android中使用sqlite数据库,首先需要了解SQLiteOpenHerper这个类, 是用来实现数据库初始化的一个类,我们需要继承这个类,初始化我们的数据库:


DBOpenHelper.java

import android.content.Context;    import android.database.sqlite.SQLiteDatabase;    import android.database.sqlite.SQLiteDatabase.CursorFactory;    import android.database.sqlite.SQLiteOpenHelper;        public class DBOpenHelper extends SQLiteOpenHelper{            public DBOpenHelper(Context context){            super(context, "sqlite.db", null, 1);        }            @Override        public void onCreate(SQLiteDatabase db) {            db.execSQL("create table persons (_id integer primary key autoincrement," +                    "name varchar null," +                    "age int null);" );        }            @Override        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            // TODO Auto-generated method stub                    }        }  

</div> </div>


(2)然后我们需要一个业务类,来操作我们的数据库中的内容. 而且可能有很多种业务类, 对数据库中不同的表进行操作,我们这里写一个PersonService业务类, 其所用到的数据类是Person:

Person:

 
public class Person {                private String  name;        private Integer age;        private Integer personid;                        public Person() {            name= null;            age      = null;            personid = null;        }                        public Person(Integer personid,String name, int age) {            this.personid = personid;            this.name = name;            this.age = age;        }                public Person(String name, int age) {            this.personid = null;            this.name = name;            this.age = age;        }                public Integer getPersonid() {            return personid;        }                public void setPersonid(Integer personid) {            this.personid = personid;        }                public String getName() {            return name;        }        public void setName(String name) {            this.name = name;        }        public int getAge() {            return age;        }        public void setAge(int age) {            this.age = age;        }            }  


PersonService:
import java.util.ArrayList;    import java.util.List;        import android.content.Context;    import android.database.Cursor;    import android.database.sqlite.SQLiteDatabase;        import com.xiaoming.domain.DBOpenHelper;    import com.xiaoming.domain.Person;            public class PersonService {            Context      context = null;        DBOpenHelper dbOpenHelper = null;                    public PersonService(Context context) {            this.context = context;            dbOpenHelper = new DBOpenHelper( context );        }                /**        * 添加记录        * @param p        */        public void insert(Person p)        {            SQLiteDatabase db = dbOpenHelper.getWritableDatabase();            db.execSQL("insert into persons(name,age) values(?,?);",                    new Object[]{p.getName(),p.getAge()});        }                /**        * 删除记录        * @param id        */        public void delete(Integer id)        {            SQLiteDatabase db = dbOpenHelper.getWritableDatabase();            db.execSQL("delete from persons where _id=?;",                    new Object[]{id});        }                        /**        * 更新记录        * @param p        */        public void update(Person p)        {            SQLiteDatabase db = dbOpenHelper.getWritableDatabase();            db.execSQL("update persons set name=?,age=? where _id=?",                    new Object[]{p.getName(),p.getAge(),p.getPersonid()} );                    }                        /**        * 查找记录        * @param id        * @return        */        public Person find(Integer id)        {            SQLiteDatabase db = dbOpenHelper.getReadableDatabase();            Cursor cursor = db.rawQuery("select * from persons where _id=?", new String[]{id.toString()}) ;            if( cursor.moveToFirst() )            {                int personid = cursor.getInt(cursor.getColumnIndex("_id"));                String name  = cursor.getString(cursor.getColumnIndex("name"));                int age      = cursor.getInt(cursor.getColumnIndex("age"));                return new Person(personid,name,age);            }            cursor.close();            return null;        }                /**        * 分页查找记录        * @param offset        * @param maxResult        * @return        */        public List<Person> getScrollData(int offset, int maxResult)        {            List<Person> personlist = new ArrayList<Person>();            SQLiteDatabase db = dbOpenHelper.getReadableDatabase();            Cursor cursor = db.rawQuery("select * from persons order by _id asc limit ?,? ",                    new String[]{String.valueOf(offset), String.valueOf(maxResult)}) ;            while( cursor.moveToNext() )            {                int personid = cursor.getInt(cursor.getColumnIndex("_id"));                String name  = cursor.getString(cursor.getColumnIndex("name"));                int age      = cursor.getInt(cursor.getColumnIndex("age"));                personlist.add(new Person(personid,name,age) );            }            cursor.close();            return personlist;        }                /**        * 得到记录数        * @return        */        public long getCount()        {            SQLiteDatabase db = dbOpenHelper.getReadableDatabase();            Cursor cursor = db.rawQuery("select count(*) from persons",null );            cursor.moveToFirst();            long result = cursor.getLong(0);            return result;        }            }  



(3)然后需要写一个测试类PersonServiceTest:

       import java.util.ArrayList;    import java.util.List;        import com.xiaoming.domain.DBOpenHelper;    import com.xiaoming.domain.Person;    import com.xiaoming.service.PersonService;        import android.test.AndroidTestCase;    import android.util.Log;        public class PersonServiceTest extends AndroidTestCase {            private static final String TAG = "PersonServiceTest";        public void testInsert()        {            PersonService pService = new PersonService(getContext());            Person p = new Person("王强",40);            Person p1 = new Person("小花",40);            Person p2 = new Person("小狗",40);            Person p3 = new Person("小猫",40);            Person p4 = new Person("自傲做",40);            Person p5 = new Person("我晕哦",40);            Person p6 = new Person("么得",40);            Person p7 = new Person("张建",40);            pService.insert(p1);            pService.insert(p2);            pService.insert(p3);            pService.insert(p4);            pService.insert(p5);            pService.insert(p6);            pService.insert(p7);        }                public void testdelete()        {            PersonService pService = new PersonService(getContext());            pService.delete(1);        }                public void testUpdate()        {            PersonService pService = new PersonService(getContext());            Person p = pService.find(5);            if( p ==  null )            {                Log.i(TAG,"id="+p.getPersonid()+"的人没有找到");                return ;            }            p.setName("哈哈哈哈");            pService.update(p);        }                public void testFind()        {            PersonService pService = new PersonService(getContext());            Person p = pService.find(2);            Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());        }                public void testGetScrollData()        {            PersonService pService = new PersonService(getContext());            ArrayList<Person> pList = (ArrayList<Person>) pService.getScrollData(1, 5);            for(Person p:pList)            {                Log.i(TAG,"_id="+p.getPersonid()+", name="+p.getName()+", age="+p.getAge());            }        }                public void testGetCount()        {            PersonService pService = new PersonService(getContext());            long  count = pService.getCount();            Log.i(TAG,""+count);        }    }  
 
</div> </div>