关注我
一，建立一个名为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()); } }