Android之SQLite数据库

SQLite简介

Android提供了SQLiteDatabase代表一个数据库（底层就是一个数据库文件），一旦应用程序获得了代表指定数据库SQLiteDatabase对象，接下来就可通过SQLiteDatabase对象来管理，操作数据库了。

使用SQLiteOpenHelper抽象类建立数据库

抽象类SQLiteOpenHelper用来对数据库进行版本管理，不是必须使用的。

为了实现对数据库版本进行管理， SQLiteOpenHelper 类提供了两个重要的方法 ， 分别onCreate(SQLiteDatabasedb) 和 onUpgrade(SQLiteDatabase db, int oldVersion, intnewVersion)用于初次使用软件时生成数据库表，后者用于升级软件时更新数据库表结构。

1、  建立数据库类DatabaseHelper

public class DatabaseHelper extends SQLiteOpenHelper {

    static String dbName = "myAndroid_db.db";

    static int version=1;

 

    public DatabaseHelper(Context context) {

       super(context, dbName, null,version);   

    }

    //第一次使用的时候会被调用，用来建库

    public void onCreate(SQLiteDatabase db) {

    Stringsql = "create table person11(personidinteger primary key

autoincrement, name varchar(20),age integer)";

       db.execSQL(sql);

    }

 

    public void onUpgrade(SQLiteDatabase db, int oldVersion,

int newVersion) {

       String sql = "droptable if exists person";

       onCreate(db);

    }

}

2、  编写测试类进行测试

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

//  String sql = "drop table if exists person";

//  Log.i("TAG","我被删除了");

//  onCreate(db);

      

    String sql = "alter tableperson add phone char(20) null";

    db.execSQL(sql);

}

CRUD

建立PersonService业务类

package cn.class3g.service;

…

public class PersonService {

 

    private DatabaseHelper dbHelper;

    private Context context;

 

    public PersonService(Context context) {

       this.context = context;

       dbHelper = newDatabaseHelper(context);

    }

 

    public void save(Person person) {

       SQLiteDatabasedb = dbHelper.getWritableDatabase();

       // String sql = "insert into person(name,age)values('Tom',21)";

       // db.execSQL(sql);

 

       // 防止用户输入数据错误，如:name="T'om"

       String sql = "insert into person(name,age) values(?,?)";

       db.execSQL(sql,new Object[] { person.getName(), person.getAge() });

    }

 

    public void update(Person person, int id) {

       SQLiteDatabasedb = dbHelper.getWritableDatabase();

       String sql = "update person set name=?,age=? wherepersonid=?";

       db.execSQL(sql,new Object[] { person.getName(), person.getAge(), id });

    }

 

    public Person find(int id) {

       SQLiteDatabasedb = dbHelper.getReadableDatabase();

       String sql = "select * from person where personid=?";

       Cursor cursor= db.rawQuery(sql, new String[] { String.valueOf(id) });

 

       if(cursor.moveToNext()) {

           Person person = new Person();

           person.setName(cursor.getString(cursor.getColumnIndex("name")));

           person.setId(cursor.getInt(0));

           person.setAge(cursor.getInt(2));

 

           cursor.close();// 关闭游标

           return person;

       }

       return null;

    }

 

    public void delete(int id) {

       SQLiteDatabasedb = dbHelper.getReadableDatabase();

       Stringsql = "delete fromperson where personid=?";

       db.execSQL(sql, new Object[] { id });

    }

 

    public List<Person> getScrollData(int startIdx,int count) {

 

       SQLiteDatabasedb = dbHelper.getReadableDatabase();

       String sql = "select * from person limit ?,?";

       Cursor cursor = db.rawQuery(sql,

                     new String[] { String.valueOf(startIdx),

                                   String.valueOf(count) });

 

       List<Person> list = new ArrayList<Person>();

      

       while(cursor.moveToNext()){

           Person p = new Person();

           p.setId(cursor.getInt(0));

           p.setName(cursor.getString(1));

           p.setAge(cursor.getInt(2));

          

           list.add(p);

       }     

       cursor.close();

       return list;

    }

    public long getRecordsCount(){

       SQLiteDatabasedb = dbHelper.getReadableDatabase();

       String sql = "select count(*) from person";

       Cursor cursor = db.rawQuery(sql, null);

       cursor.moveToFirst();

       long count =cursor.getInt(0);

       cursor.close();

       return count;

    }

}

在测试类cn.class3g.db.PersonServiceTest中添加对应测试方法

package cn.class3g.db;

…

public class PersonServiceTest extends AndroidTestCase {

   

    public void testSave() throws Throwable{

       PersonServiceservice = new PersonService(this.getContext());

      

       Person person= new Person();

       person.setName("zhangxiaoxiao");

       service.save(person);

      

       Personperson2 = new Person();

       person2.setName("laobi");

       service.save(person2);

      

       Personperson3 = new Person();

       person3.setName("lili");

       service.save(person3);

      

       Personperson4 = new Person();

       person4.setName("zhaoxiaogang");

       service.save(person4);     

    }

    public void testUpdate() throws Throwable{

       PersonServiceps  = new PersonService(this.getContext());

       Person person= new Person("Ton", 122);

       ps.update(person,2);//需要实现查看数据库中Ton的id值

    }

    public void testFind() throws Throwable{

       PersonServiceps  = new PersonService(this.getContext());

       Person person= ps.find(2);

       Log.i("TAG",person.toString());

    }  

    public void testDelete() throws Throwable{

       PersonServiceps  = new PersonService(this.getContext());

       ps.delete(2);    

    }  

    public void testScroll() throws Throwable{

       PersonServiceservice = new PersonService(this.getContext());

       List<Person> personList =service.getScrollData(3, 2);

      

       Log.i("TAG",personList.toString());      

    }  

    public void testCount() throws Throwable{

       PersonServiceservice = new PersonService(this.getContext());

       long count =service.getRecordsCount();

       Log.i("TAG", String.valueOf(count));

    }

}