（Android）创建数据库与完成数据库的增删查改

1.Person .java

package com.example.domain;

public class Person {
private Integer id;
private String name;
private String phone;

public Person(){};


public Person(String name,String phone) {
super();
this.name = name;
this.phone = phone;

}




public Person(Integer id, String name, String phone) {
super();
this.id = id;
this.name = name;
this.phone = phone;
}
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;
}

}

2.DBOpenHelper .java//完成数据库的创建或更新

package com.example.service;


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class DBOpenHelper extends SQLiteOpenHelper{


public DBOpenHelper(Context context) {
super(context, "itcast.db", null, 2);
// TODO Auto-generated constructor stub
}


@Override
public void onCreate(SQLiteDatabase db) {//是在数据库第一次创建的时候被调用的
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20))");
}


@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// TODO Auto-generated method stub
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
}


}

3.PersonService .java//实现增删查改

package com.example.service;


import java.util.ArrayList;
import java.util.List;


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


import com.example.domain.Person;


public class PersonService {
private DBOpenHelper dbOpenHelper;

public PersonService(Context context) {
super();
this.dbOpenHelper = new DBOpenHelper(context);
}
public void save(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person(name,phone) values(?,?)",new Object[]{person.getName(),person.getPhone()});
}
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?",new Object[]{id});
}
public void update(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=?,phone=? where personid=?",new Object[]{
person.getName(),person.getPhone(),person.getId()});
}
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"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone);
}
cursor.close();
return null;
}
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"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid,name,phone));
}
cursor.close();
return persons;

}
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;

}
}

4.PersonServiceTest .java//对数据库的创建及数据的增删查改进行测试

package com.example.test;


import java.util.List;


import com.example.domain.Person;
import com.example.service.DBOpenHelper;
import com.example.service.PersonService;


import android.test.AndroidTestCase;
import android.util.Log;


public class PersonServiceTest extends AndroidTestCase{
public void testCreateDB() throws Exception {
DBOpenHelper dbOpenHelp = new DBOpenHelper(getContext());
dbOpenHelp.getWritableDatabase();
}
public void testSave() throws Exception {
PersonService service = new PersonService(this.getContext());
Person person = new Person("zhabg","125467568");
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+"");
}
}

5.配置文件AndroidManifest.xml//添加测试权限(JUnit)

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.sqlite"
    android:versionCode="1"
    android:versionName="1.0" >


    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="18" />


    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <uses-library android:name="android.test.runner" />
        <activity
            android:name="com.example.sqlite.MainActivity"
            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>

<instrumentation 
   android:name="android.test.InstrumentationTestRunner"
   android:label="Test for my app"
   android:targetPackage="com.example.sqlite"></instrumentation>

</manifest>