Android应用开发之SQLite数据库2

SQLite数据库中另一种添删改查操作

OtherPersonService

package cn.class3g.service;
 
 
import cn.class3g.domain.Person;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
 
public class OtherPersonService {
 
       private DatabaseHelper dbHelper;
      
       public OtherPersonService(Context context) {    
              dbHelper = new DatabaseHelper(context);
       }
      
       public void save(Person person) {
              SQLiteDatabase db = dbHelper.getWritableDatabase();
             
              ContentValues values = new ContentValues();
              values.put("name", person.getName());
              values.put("age", person.getAge());
             
              db.insert("person", "name", values); //第二个参数name的作用体现在第三个参数values为空时
       }
 
       public void update(Person person, int id) {
              SQLiteDatabase db = dbHelper.getWritableDatabase();
             
              ContentValues values = new ContentValues();
              values.put("name", person.getName());
              values.put("age", person.getAge());
             
              db.update("person", values, "personid=?", new String[]{String.valueOf(id)});
       }
 
       public Person find(int id) {
              SQLiteDatabase db = dbHelper.getReadableDatabase();
 
              Cursor cursor = db.query("person", new String[]{"personid","name","age"},
                            "personid=?",new String[]{String.valueOf(id)},
                            null,null, null);
                           
              if(cursor.moveToNext()){
                     Person person = new Person();
                    
                     person.setId(cursor.getInt(0));
                     person.setName(cursor.getString(1));
                     person.setAge(cursor.getInt(2));
                    
                     return person;
              }
              return null;
       }
      
       public void delete(int id){
              SQLiteDatabase db = dbHelper.getWritableDatabase();
              db.delete("person", "personid=?", new String[]{String.valueOf(id)});            
       }
}

测试类OtherPersonServiceTest

package cn.class3g.db;
 
import android.test.AndroidTestCase;
import android.util.Log;
import cn.class3g.domain.Person;
import cn.class3g.service.OtherPersonService;
import cn.class3g.service.PersonService;
 
public class OtherPersonServiceTest extends AndroidTestCase {
 
       public void testSave() throws Throwable {
              OtherPersonService service = new OtherPersonService(this.getContext());
 
              Person person = new Person();
              person.setName("wusong");
              person.setAge(2000);
              service.save(person);
 
              Person person2 = new Person();
              person2.setName("Tangbohu");
              service.save(person2);
 
              Person person3 = new Person();
              person3.setName("Qiuxiang");
              service.save(person3);
 
              Person person4 = new Person();
              person4.setName("Furong");
              service.save(person4);
       }
      
       public void testUpdate() throws Throwable{
              OtherPersonService ps  = new OtherPersonService(this.getContext());
              Person person = new Person("Jiangzhongzheng", 122);
              ps.update(person, 5);//需要实现查看数据库中Ton的id值
       }
      
       public void testFind() throws Throwable{
              OtherPersonService ps  = new OtherPersonService(this.getContext());
              Person person = ps.find(3);//测之前先注意被测记录id是否存在
              Log.i("TAG",person.toString());
       }
      
       public void testDelete() throws Throwable{
              OtherPersonService ps  = new OtherPersonService(this.getContext());
              ps.delete(1);           //测之前先注意被测记录id是否存在
       }    
}

使用列表显示数据库数据

资源

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">Android_db_test</string>
    <string name="id_text">编号</string>
    <string name="name_text">姓名</string>
    <string name="age_text">年龄</string>
</resources>

布局

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
 
    <RelativeLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >
 
        <TextView
            android:id="@+id/idTitle"
            android:layout_width="80dp"
            android:layout_height="wrap_content"
            android:text="@string/id_text" />
 
        <TextView
            android:id="@+id/nameTitle"
            android:layout_width="200dp"
            android:layout_height="wrap_content"
            android:layout_toRightOf="@id/idTitle"
            android:layout_alignTop="@id/idTitle"
            android:gravity="center"
            android:text="@string/name_text" />
 
        <TextView
            android:id="@+id/ageTitle"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_toRightOf="@id/nameTitle"
            android:layout_alignTop="@id/nameTitle"
            android:text="@string/age_text" />
    </RelativeLayout>
    <ListView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:id="@+id/personList"
        />
</LinearLayout>

ListView布局

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content" >
 
    <TextView
        android:id="@+id/id"
        android:layout_width="80dp"
        android:layout_height="wrap_content" />
 
    <TextView
        android:id="@+id/name"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:layout_alignTop="@id/id"
        android:layout_toRightOf="@id/id"
        android:gravity="center" />
 
    <TextView
        android:id="@+id/age"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignTop="@id/name"
        android:layout_toRightOf="@id/name"
        android:gravity="left" />
 
</RelativeLayout>

Activity

package cn.class3g.db;
 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
 
import cn.class3g.domain.Person;
import cn.class3g.service.PersonService;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
 
public class MainActivity extends Activity {
 
       private ListView listView;
       private PersonService service;
 
       protected void onCreate(Bundle savedInstanceState) {
              super.onCreate(savedInstanceState);
              this.setContentView(R.layout.main);
 
              listView = (ListView) this.findViewById(R.id.personList);
 
              service = new PersonService(this);
 
              /*
               * //使用SimpleAdapter List<Person> personList = service.getScrollData(3,
               * 5); List<HashMap<String,String>> data = new
               * ArrayList<HashMap<String,String>>();
               *
               * for(Person p : personList){ HashMap<String,String> map = new
               * HashMap<String,String>(); map.put("personid",
               * String.valueOf(p.getId())); map.put("name", p.getName());
               * map.put("age", String.valueOf(p.getAge()));
               *
               * data.add(map); }
               *
               * SimpleAdapter adapter = new SimpleAdapter(this, data,
               * R.layout.person_item, new String[]{"personid","name","age"}, new
               * int[]{R.id.id, R.id.name, R.id.age});
               */
 
              // 使用SimpleCursorAdapter
              Cursor cursor = service.getCursorScrollData(3, 5);
              //注意此时绑定的cursor中的personid as _id，不能用personid
              SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,
                            R.layout.person_item, cursor,
                            new String[] { "_id", "name","age" }, 
                            new int[] { R.id.id, R.id.name, R.id.age });
 
              listView.setAdapter(adapter);
       }
}