一,建立一个名为mySQLite1.db的数据库,及一张名称为Person的表。
复制代码
如果表结构发生了改变,例如向表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()方法来操作数据库。
复制代码
Person.java
复制代码
三、构建Android JUnit 测试环境
在功能清单文件中添加以下代码:
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="myAndroid.database.SQLite" >
</instrumentation>
<uses-library android:name="android.test.runner"/>
注:以上代码的包应该和你的Acitivity所在的包名称一致。
复制代码
四、添加测试代码。
复制代码
- 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)");
- }
- }
则应在 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());
- }
-
- }