首页 > 代码库 > Android开发系列(九):创建数据库以及完成简单的CRUD操作
Android开发系列(九):创建数据库以及完成简单的CRUD操作
本篇博文主要实现简单的创建数据库以及实现CRUD操作。
首先,我们建立一个Android Project,命名为db
一、完成数据库的创建操作:
用SQLiteOpenHelper类中的getWritableDatabase()和getReadableDatabase()都可以获取一个操作数据库的SQLiteDatabase实例,其中getReadableDatabase()方法中会调用getWritableDatabase()方法。
区别:其中,getWritableDatabase() 方法以读写方式打开数据库,一旦数据库的磁盘空间满了,数据库就只能读而不能写,倘若使用的是getWritableDatabase() 方法就会出错。getReadableDatabase()方法则是先以读写方式打开数据库,如果数据库的磁盘空间满了,就会打开失败,当打开失败后会继续尝试以只读方式打开数据库;如果此时数据库空间出现了空余,则会成功获取一个操作数据库的SQLiteDatabase对象。
我们首先建立一个类(继承SQLiteOpenHelper)DBOpenHelper.java:
package cn.itcast.service; 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 { //构造函数,调用父类的SQLiteOpenHelper的构造函数 public DBOpenHelper(Context context) { super(context, "itcast.db", null, 1); //<包>/databases/ } @Override public void onCreate(SQLiteDatabase db) {//是在数据库每一次被创建的时候调用的 db.execSQL("CREATE TABLE person(personid integer primary key autoincrement, name varchar(20), phone VARCHAR(12) NULL)"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//在数据库的版本发生变化时会被调用 db.execSQL("ALTER TABLE person ADD amount integer"); } }
onCreate()方法在初次生成数据库时才会被调用,在onCreate()方法里可以生成数据库表结构及添加一些应用使用到的初始化数据。
onUpgrade()方法在数据库的版本发生变化时会被调用,一般在软件升级时才需改变版本号,而数据库的版本是由程序员控制的。
然后,我们建立一个测试类,来实现调用DBOpenHelper中的方法来创建数据库DBTest.java:
package cn.itcast.test; public class DBTest extends AndroidTestCase { public void testCreateDB() throws Exception{ DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext()); dbOpenHelper.getWritableDatabase(); } }关于测试需要在AndroidManifest.xml中配置几条语句,这点不要忘了。
运行测试发现在File Explorer视图中的/data/data/cn.itcast.db/databases目录下有个itcast.db的文件,就是我们创建的数据库了。
我们用Person来做例子
首先,用建立一个javabean:Person.java
package cn.itcast.domain; public class Person { private Integer id; private String name; private String phone; private Integer amount; public Person(){} public Person(String name, String phone, Integer amount) { this.name = name; this.phone = phone; this.amount = amount; } public Person(Integer id, String name, String phone, Integer amount) { this.id = id; this.name = name; this.phone = phone; this.amount = amount; } public Integer getAmount() { return amount; } public void setAmount(Integer amount) { this.amount = amount; } 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; } @Override public String toString() { return "Person [id=" + id + ", name=" + name + ", phone=" + phone + ", amount=" + amount + "]"; } }然后,我们需要编写业务层的类,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); } /** * 添加记录 * @param person */ public void save(Person person){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("insert into person(name, phone, amount) values(?,?,?)", new Object[]{person.getName(), person.getPhone(), person.getAmount()}); } /** * 删除记录 * @param id 记录ID */ public void delete(Integer id){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("delete from person where personid=?", new Object[]{id}); } /** * 更新记录 * @param person */ public void update(Person person){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("update person set name=?,phone=?,amount=? where personid=?", new Object[]{person.getName(), person.getPhone(), person.getAmount(), person.getId()}); } /** * 查询记录 * @param id 记录ID * @return */ 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")); int amount = cursor.getInt(cursor.getColumnIndex("amount")); String name = cursor.getString(cursor.getColumnIndex("name")); String phone = cursor.getString(cursor.getColumnIndex("phone")); return new Person(personid, name, phone, amount); } cursor.close(); return null; } /** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */ public List<Person> getScrollData(int offset, int maxResult){ List<Person> persons = new ArrayList<Person>(); SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset), String.valueOf(maxResult)}); while(cursor.moveToNext()){ int personid = cursor.getInt(cursor.getColumnIndex("personid")); int amount = cursor.getInt(cursor.getColumnIndex("amount")); String name = cursor.getString(cursor.getColumnIndex("name")); String phone = cursor.getString(cursor.getColumnIndex("phone")); persons.add(new Person(personid, name, phone, amount)); } cursor.close(); return persons; } /** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */ public Cursor getCursorScrollData(int offset, int maxResult){ SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select personid as _id,name,phone,amount from person order by personid asc limit ?,?", new String[]{String.valueOf(offset), String.valueOf(maxResult)}); return cursor; } /** * 获取记录总数 * @return */ public long getCount(){ SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select count(*) from person", null); cursor.moveToFirst(); long result = cursor.getLong(0); cursor.close(); return result; } }然后,我们就需要执行测试了PersonServiceTest.java:
package cn.itcast.test; 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 Exception{ DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext()); dbOpenHelper.getWritableDatabase(); } public void testSave() throws Exception{ PersonService service = new PersonService(this.getContext()); for(int i = 0 ; i < 20 ; i++){ Person person = new Person("zhangxx"+ i, "136765765"+ i, 100); service.save(person); } } public void testDelete() throws Exception{ PersonService service = new PersonService(this.getContext()); service.delete(21); } public void testUpdate() throws Exception{ PersonService service = new PersonService(this.getContext()); Person person = service.find(1); person.setName("zhangxiaoxiao"); service.update(person); } public void testFind() throws Exception{ PersonService service = new PersonService(this.getContext()); Person person = service.find(1); Log.i(TAG, person.toString()); } public void testScrollData() throws Exception{ PersonService service = new PersonService(this.getContext()); List<Person> persons = service.getScrollData(0, 5); for(Person person : persons){ Log.i(TAG, person.toString()); } } public void testCount() throws Exception{ PersonService service = new PersonService(this.getContext()); long result = service.getCount(); Log.i(TAG, result+""); } public void testUpdateAmount() throws Exception{ PersonService service = new PersonService(this.getContext()); Person person1 = service.find(1); Person person2 = service.find(2); person1.setAmount(100); person2.setAmount(50); service.update(person1); service.update(person2); } }可以在找到itcast.db之后,通过下载:SQLite Explorer来打开查看
Android开发系列(九):创建数据库以及完成简单的CRUD操作