一,建立一个名为mySQLite1.db的数据库,及一张名称为Person的表。
package myAndroid.database.SQLite; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DatabaseHelper extends SQLiteOpenHelper { //数据库名称 private static String DATABASE_NAME="mySQLite1.db"; //数据库版本 private static int DATABASE_VERSION =1; //表名称 private static String TABLE_NAME="person"; //创建表 private String sql="create table "+TABLE_NAME+" (id integer primary key autoincrement ,name varchar(50),age integer )"; //构造函数,将数据库的名称和版本传入 public DatabaseHelper(Context context){ super(context, DATABASE_NAME,null,DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase sqlDb) { sqlDb.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // db.execSQL("alter table person add sex varchar(2)"); } }
如果表结构发生了改变,例如向表person中增加一个字段sex
则应在onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion ) 方法中加入代码:db.execSQL("alter table person add sex varchar(2)");
并将数据库版本DATABASE_VERSION = 2;
二,对数据库进行增加、删除、修改、查询、分页,及表中的记录数
创建一个名为PersonService的类及一个JavaBean→Person.java。
通过建立PersonService类的构造函数public PersonService(Context context) ,用于取得应用环境的全局变量context
利用getWritableDatabase()或getReadableDatabase()方法来实例化SQLiteDatabase类的对象,再调用execSQL()或rawQuery()方法来操作数据库。
package myAndroid.database.service; import java.util.ArrayList; import java.util.List; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import myAndroid.database.SQLite.DatabaseHelper; public class PersonService { // 声明DatabaseHelper类的对象作为成员变量 private DatabaseHelper databaseHelper; /** * 声明PersonService类的构造方法,用于实例化对象databaseHelper * * @param context * →得到应用环境的全局信息 */ public PersonService(Context context) { this.databaseHelper = new DatabaseHelper(context); } /** * 向表中插入一条数据 * * @param person * @throws Exception */ public void insert(Person person) throws Exception { // 调用getWritableDatabase()方法对SQLiteDatabase类的对象sqlDatabase进行实例化 SQLiteDatabase sqlDatabase = databaseHelper.getWritableDatabase(); // 用占位符?来接收name和age的内容 sqlDatabase.execSQL("insert into person (name,age) values(?,?)", new Object[] { person.getName().trim(), person.getAge() }); } /** * 删除一条数据 * * @param id * @throws Exception */ public void delete(Integer id) throws Exception { // 调用getWritableDatabase()方法对SQLiteDatabase类的对象sqlDatabase进行实例化 SQLiteDatabase sqlDatabase = databaseHelper.getWritableDatabase(); sqlDatabase.execSQL("delete from person where id=?", new String[] { id.toString() }); } /** * 更新一条数据 * * @param person * @throws Exception */ public void update(Person person) throws Exception { // 调用getWritableDatabase()方法对SQLiteDatabase类的对象sqlDatabase进行实例化 SQLiteDatabase sqlDatabase = databaseHelper.getWritableDatabase(); sqlDatabase.execSQL("update person set name=? where id=?", new Object[] { person.getName().trim(), person.getId() }); } /** * 查询一条数据 * * @param id * @return * @throws Exception */ public Person query(Integer id) throws Exception { // 调用getWritableDatabase()方法对SQLiteDatabase类的对象sqlDatabase进行实例化 SQLiteDatabase sqlDatabase = databaseHelper.getReadableDatabase(); Cursor cursor = sqlDatabase.rawQuery("select * from person 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); } return null; } /** * 对查询到的结果进行分页 * * @return persons */ public List<Person> getScrollData(Integer offset, Integer maxResult) { List<Person> persons = new ArrayList<Person>(); // 调用getWritableDatabase()方法对SQLiteDatabase类的对象sqlDatabase进行实例化 SQLiteDatabase sqlDatabase = databaseHelper.getReadableDatabase(); Cursor cursor = sqlDatabase.rawQuery("select * from person limit ?,?", new String[] { offset.toString(), maxResult.toString() }); while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); //将查询到的结果加入到persons集合中 persons.add(new Person(id, name, age)); } cursor.close(); return persons; } public int getCount() { // 调用getWritableDatabase()方法对SQLiteDatabase类的对象sqlDatabase进行实例化 SQLiteDatabase sqlDatabase = databaseHelper.getReadableDatabase(); Cursor cursor = sqlDatabase.rawQuery("select count(*) from person",null); //因为查找的结果有且只有一条,所以直接将游标的指向为first即可 cursor.moveToFirst(); //返回结果 return cursor.getInt(0); } }
Person.java
package myAndroid.database.service; public class Person { private int id; private String name; private int age; public Person(int id,String name,int age){ this.id=id; this.name=name; this.age=age; } public Person(String name,int age){ this.name=name; this.age=age; } public int getId() { return id; } public void setId(int id) { this.id = id; } 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; } public String toString(){ return "Person [id="+id+",name="+name+",age="+age+"]"; } }
三、构建Android JUnit 测试环境
在功能清单文件中添加以下代码:
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="myAndroid.database.SQLite" >
</instrumentation>
<uses-library android:name="android.test.runner"/>
注:以上代码的包应该和你的Acitivity所在的包名称一致。
<uses-sdk android:minSdkVersion="10" />
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="myAndroid.database.SQLite" >
</instrumentation>
<application
android:icon="@drawable/ic_launcher"
android:label="@string/app_name" >
<uses-library android:name="android.test.runner"/>
<activity
android:name=".SQLite_dbActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
四、添加测试代码。
package myAndroid.database.SQLiteTest; import java.util.List; import myAndroid.database.SQLite.DatabaseHelper; import myAndroid.database.service.Person; import myAndroid.database.service.PersonService; import android.test.AndroidTestCase; import android.util.Log; public class SQLiteTest extends AndroidTestCase { private static final String TAG = "SQLiteTest"; public void testCreateDatabase() throws Exception{ DatabaseHelper dbHelper = new DatabaseHelper(getContext()); // 创建数据库 dbHelper.getWritableDatabase(); } public void testInsert() throws Exception{ PersonService personService = new PersonService(this.getContext()); for(int i=0;i<5;i++){ personService.insert(new Person("小王"+i,18+i)); } } public void testDelete() throws Exception{ PersonService personService = new PersonService(this.getContext()); personService.delete(4); } public void testUpdate() throws Exception{ PersonService personService = new PersonService(this.getContext()); //查找id为1的一条数据 Person person = personService.query(1); person.setName("田中"); personService.update(person); } public void testQuery() throws Exception{ PersonService personService = new PersonService(this.getContext()); Person person = personService.query(1); Log.i(TAG, person.toString()); } public void testGetScrollData() throws Exception{ PersonService personService = new PersonService(this.getContext()); //从第一行开始,以三行的形式显示 List<Person> persons= personService.getScrollData(0, 3); //foreach语句将结果迭代输出 for(Person per :persons){ Log.i(TAG, per.toString()); } } public void testGetCount() throws Exception{ PersonService personService = new PersonService(this.getContext()); //将结果保存到一个临时变量count中 Integer count = personService.getCount(); //将结果打印出来 Log.i(TAG, count.toString()); } }