用SQLiteDatabase类的execSQL和rawQuery方法操作数据库

mypanlong

于 2015-02-15 20:42:19 发布

阅读量5.1k

点赞数 1

分类专栏： sqlite数据库

sqlite数据库专栏收录该内容

9 篇文章 1 订阅

订阅专栏

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

复制代码

mypanlong

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
用SQLiteDatabase类的execSQL和rawQuery方法操作数据库

一，建立一个名为mySQLite1.db的数据库，及一张名称为Person的表。package myAndroid.database.SQLite;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQ
复制链接

扫一扫

专栏目录