Android(安卓)SQLite数据库增删改查

DBOpenHelper.java

package cn.itcast.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {private static final String DATABASENAME = "itcast.db"; //数据库名称private static final int DATABASEVERSION = 2;//数据库版本public DBOpenHelper(Context context) {super(context, DATABASENAME, null, DATABASEVERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20), amount integer)");//执行有更改的sql语句}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("DROP TABLE IF EXISTS person");onCreate(db);}}

PersonService.java

package cn.itcast.service;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import cn.itcast.domain.Person;public class PersonService {private DBOpenHelper dbOpenHelper;public PersonService(Context context) {this.dbOpenHelper = new DBOpenHelper(context);}public void save(Person person){//如果要对数据进行更改，就调用此方法得到用于操作数据库的实例,该方法以读和写方式打开数据库SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("insert into person (name) values(?)", new Object[]{person.getName()});}public void update(Person person){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("update person set name=? where personid=?", new Object[]{person.getName(),person.getId()});}public void delete(Integer id){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("delete from person where personid=?", new Object[]{id.toString()});}public Person find(Integer id){//如果只对数据进行读取，建议使用此方法SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person where personid=?", new String[]{id.toString()});if(cursor.moveToFirst()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));return new Person(personid, name);}return null;}public List<Person> getScrollData(Integer offset, Integer maxResult){List<Person> persons = new ArrayList<Person>();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person limit ?,?",new String[]{offset.toString(), maxResult.toString()});while(cursor.moveToNext()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));Person person = new Person(personid, name);persons.add(person);}cursor.close();return persons;}public long getCount() {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select count(*) from person", null);cursor.moveToFirst();return cursor.getLong(0);}}

测试类：PersonServiceTest.java

package cn.itcast.db;import java.util.List;import cn.itcast.domain.Person;import cn.itcast.service.DBOpenHelper;import cn.itcast.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class PersonServiceTest extends AndroidTestCase {private static final String TAG = "PersonServiceTest";public void testCreateDB() throws Throwable{DBOpenHelper dbOpenHelper = new DBOpenHelper(this.getContext());dbOpenHelper.getWritableDatabase();//第一次调用该方法就会创建数据库}public void testSave() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = new Person();person.setName("xiaoxiao");personService.save(person);person = new Person();person.setName("zhangliming");personService.save(person);person = new Person();person.setName("libaobao");personService.save(person);person = new Person();person.setName("taobao");personService.save(person);}public void testUpate() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);person.setName("lili");personService.update(person);}public void testDelete() throws Throwable{PersonService personService = new PersonService(this.getContext());personService.delete(1);}public void testFind() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);Log.i(TAG, person.toString());}public void testGetScrollData() throws Throwable{PersonService personService = new PersonService(this.getContext());List<Person> persons = personService.getScrollData(0, 3);for(Person person : persons){Log.i(TAG, person.toString());}}public void testGetCount() throws Throwable{PersonService personService = new PersonService(this.getContext());Log.i(TAG, personService.getCount()+"");}}

更多相关文章

随机推荐