android sqlite SQLiteDatabase 操作大全 不看后悔！必收藏！看后精通SQLITE (第三部分，完整代码)

让我们来建一个整体的实例

Person实体

    package com.jbridge.domain;  
      
    import android.R.string;  
      
    public class Person {  
        private Integer id;  
        private String name;  
        private Short age;  
      
        public Person(String name, Short age) {  
            this.name = name;  
            this.age = age;  
        }  
      
        public Person(Integer id, String name, Short age) {  
            super();  
            this.id = id;  
            this.name = name;  
            this.age = age;  
        }  
      
        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 Short getAge() {  
            return age;  
        }  
      
        public void setAge(Short age) {  
            this.age = age;  
        }  
      
        @Override  
        public String toString() {  
            return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";  
        }  
      
    }  

2.编写DataBaseOpenHelper类

      DataBaseOpenHelper继承自SQLiteOpenHelper类。我们需要创建数据表，必须重写onCreate（更新时重写onUpgrade方法）方法，在这个方法中创建数据表。

    package com.jbridge.service;  
      
    import android.content.Context;  
    import android.database.sqlite.SQLiteDatabase;  
    import android.database.sqlite.SQLiteDatabase.CursorFactory;  
    import android.database.sqlite.SQLiteOpenHelper;  
      
    public class DataBaseOpenHelper extends SQLiteOpenHelper {  
        // 类没有实例化,是不能用作父类构造器的参数,必须声明为静态  
        private static String dbname = "zyj";  
        private static int version = 1;  
      
        public DataBaseOpenHelper(Context context) {  
            // 第一个参数是应用的上下文  
            // 第二个参数是应用的数据库名字  
            // 第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类  
            // 第四个参数是数据库版本，必须是大于0的int（即非负数）  
            super(context, dbname, null, version);  
            // TODO Auto-generated constructor stub  
        }  
      
        public DataBaseOpenHelper(Context context, String name,  
                CursorFactory factory, int version) {  
            super(context, name, factory, version);  
            // TODO Auto-generated constructor stub  
        }  
      
        @Override  
        public void onCreate(SQLiteDatabase db) {  
            db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");  
        }  
      
        // onUpgrade()方法在数据库版本每次发生变化时都会把用户手机上的数据库表删除，然后再重新创建。  
        // 一般在实际项目中是不能这样做的，正确的做法是在更新数据库表结构时，还要考虑用户存放于数据库中的数据不会丢失,从版本几更新到版本几。  
        @Override  
        public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {  
            db.execSQL("DROP TABLE IF EXISTS person");  
            onCreate(db);  
        }  
      
    }  

3.编写PersonService类     PersonService类主要实现对业务逻辑和数据库的操作。

    package com.jbridge.service;  
      
    import java.util.ArrayList;  
    import java.util.Currency;  
    import java.util.List;  
      
    import android.content.Context;  
    import android.database.Cursor;  
    import android.database.sqlite.SQLiteDatabase;  
      
    import com.jbridge.domain.Person;  
      
    public class PersonService {  
      
        private DataBaseOpenHelper dbOpenHelper;  
      
        // private Context context;  
      
        public PersonService(Context context) {  
            // this.context = context;  
            dbOpenHelper = new DataBaseOpenHelper(context);  
        }  
      
        public void save(Person person) {  
            SQLiteDatabase database = dbOpenHelper.getWritableDatabase();  
            database.beginTransaction();  
            database.execSQL("insert into person(name,age)values(?,?)",  
                    new Object[] { person.getName(), person.getAge() });  
            // database.close();可以不关闭数据库，他里面会缓存一个数据库对象，如果以后还要用就直接用这个缓存的数据库对象。但通过  
            // context.openOrCreateDatabase(arg0, arg1, arg2)打开的数据库必须得关闭  
            database.setTransactionSuccessful();  
            database.endTransaction();  
      
        }  
      
        public void update(Person person) {  
            SQLiteDatabase database = dbOpenHelper.getWritableDatabase();  
            database.execSQL(  
                    "update person set name=?,age=? where personid=?",  
                    new Object[] { person.getName(), person.getAge(),  
                            person.getId() });  
        }  
      
        public Person find(Integer id) {  
            SQLiteDatabase database = dbOpenHelper.getReadableDatabase();  
            Cursor cursor = database.rawQuery(  
                    "select * from person where personid=?",  
                    new String[] { String.valueOf(id) });  
            if (cursor.moveToNext()) {  
                return new Person(cursor.getInt(0), cursor.getString(1),  
                        cursor.getShort(2));  
            }  
            return null;  
        }  
      
        public void delete(Integer... ids) {  
            if (ids.length > 0) {  
                StringBuffer sb = new StringBuffer();  
                for (Integer id : ids) {  
                    sb.append('?').append(',');  
                }  
                sb.deleteCharAt(sb.length() - 1);  
                SQLiteDatabase database = dbOpenHelper.getWritableDatabase();  
                database.execSQL(  
                        "delete from person where personid in(" + sb.toString()  
                                + ")", ids);  
            }  
        }  
      
        public List<Person> getScrollData(int startResult, int maxResult) {  
            List<Person> persons = new ArrayList<Person>();  
            SQLiteDatabase database = dbOpenHelper.getReadableDatabase();  
            Cursor cursor = database.rawQuery(  
                    "select * from person limit ?,?",  
                    new String[] { String.valueOf(startResult),  
                            String.valueOf(maxResult) });  
            while (cursor.moveToNext()) {  
                persons.add(new Person(cursor.getInt(0), cursor.getString(1),  
                        cursor.getShort(2)));  
            }  
            return persons;  
        }  
      
        // 获取分页数据，提供给SimpleCursorAdapter使用。  
        public Cursor getRawScrollData(int startResult, int maxResult) {  
            List<Person> persons = new ArrayList<Person>();  
            SQLiteDatabase database = dbOpenHelper.getReadableDatabase();  
            return database.rawQuery(  
                    "select personid as _id ,name,age from person limit ?,?",  
                    new String[] { String.valueOf(startResult),  
                            String.valueOf(maxResult) });  
      
        }  
      
        public long getCount() {  
            SQLiteDatabase database = dbOpenHelper.getReadableDatabase();  
            Cursor cursor = database.rawQuery("select count(*) from person", null);  
            if (cursor.moveToNext()) {  
                return cursor.getLong(0);  
            }  
            return 0;  
        }  
      
    }  

4.编写OtherPersonService类，使用 insert()、delete()、update()和query()方法实现的业务类

package com.jbridge.service;

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

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.jbridge.domain.Person;

public class OtherPersonService {

	private DataBaseOpenHelper dbOpenHelper;

	// private Context context;

	public OtherPersonService(Context context) {
		// this.context = context;
		dbOpenHelper = new DataBaseOpenHelper(context);
	}

	public void save(Person person) {
		SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put("name", person.getName());
		contentValues.put("age", person.getAge());
		database.insert("person", null, contentValues);
	}

	public void update(Person person) {
		SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put("name", person.getName());
		contentValues.put("age", person.getAge());
		database.update("person", null, "personid=?",
				new String[] { String.valueOf(person.getId()) });
	}

	public Person find(Integer id) {
		SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
		Cursor cursor = database.query("person", new String[] { "personid",
				"name", "age" }, "personid=?",
				new String[] { String.valueOf(id) }, null, null, null);
		if (cursor.moveToNext()) {
			return new Person(cursor.getInt(0), cursor.getString(1),
					cursor.getShort(2));
		}
		return null;
	}

	public void delete(Integer... ids) {
		if (ids.length > 0) {
			StringBuffer sb = new StringBuffer();
			String[] strIds = new String[ids.length];
			// for (Integer id : ids) {
			// sb.append('?').append(',');
			// }
			for (int i = 0; i < strIds.length; i++) {
				sb.append('?').append(',');
				strIds[i] = String.valueOf(ids[i]);
			}
			sb.deleteCharAt(sb.length() - 1);
			SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
			database.delete("person", "personid in(" + sb.toString() + ")",
					strIds);
		}
	}

	public List<Person> getScrollData(int startResult, int maxResult) {
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
		Cursor cursor = database.query("person", new String[] { "personid",
				"name", "age" }, null, null, null, null, "personid desc",
				startResult + "," + maxResult);
		while (cursor.moveToNext()) {
			persons.add(new Person(cursor.getInt(0), cursor.getString(1),
					cursor.getShort(2)));
		}
		return persons;
	}

	public long getCount() {
		SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
		Cursor cursor = database.query("person", new String[] { "count(*)" },
				null, null, null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getLong(0);
		}
		return 0;
	}

}

5.编写测试类  编写一个针对PersonService的测试类，测试PersonService类中的各个方法是否正确。

    package com.jbridge.db;  
      
    import java.util.List;  
      
    import com.jbridge.domain.Person;  
    import com.jbridge.service.OtherPersonService;  
    import com.jbridge.service.PersonService;  
      
    import android.test.AndroidTestCase;  
    import android.util.Log;  
      
    public class PersonServiceTest extends AndroidTestCase {  
        private static String TAG = "PersonServiceTest";  
      
        // OtherPersonService personService = new  
        // OtherPersonService(this.getContext());  
        // //不可以这么写，因为Android把context环境变量是在PersonServiceTest实例化后给他的  
      
        public void testSave() throws Exception {  
            PersonService personService = new PersonService(this.getContext());  
            // personService.save(new Person("老猪", (short) 11));  
            for (int i = 0; i < 10; i++) {  
                personService.save(new Person("你" + i, (short) (i + 10)));  
            }  
      
        }  
      
        public void testFind() throws Exception {  
            PersonService personService = new PersonService(this.getContext());  
            Person person = personService.find(1);  
            Log.i(TAG, person.toString());  
        }  
      
        public void testUpdate() throws Exception {  
            PersonService personService = new PersonService(this.getContext());  
            Person person = personService.find(1);  
            person.setName("lv");  
            personService.update(person);  
        }  
      
        public void testDelete() throws Exception {  
            PersonService personService = new PersonService(this.getContext());  
            personService.delete(1, 2, 3);  
        }  
      
        public void testGetCount() throws Exception {  
            PersonService personService = new PersonService(this.getContext());  
            Log.i(TAG, String.valueOf(personService.getCount()));  
        }  
      
        public void testGetScrollData() throws Exception {  
            PersonService personService = new PersonService(this.getContext());  
            List<Person> persons = personService.getScrollData(0, 3);  
            for (Person person : persons) {  
                Log.i(TAG, person.toString());  
            }  
        }  
    }  

启用测试功能，不要忘记在AndroidManifest.xml文件中加入测试环境。为application元素添加一个子元素：<uses-library android:name="android.test.runner"/>，为application元素添加一个兄弟元素：<instrumentation android:name="android.test.InstrumentationTestRunner"     android:targetPackage="com.jbridge.db" android:label="Tests for My App" />。

 

       SQLite数据库以单个文件存储，就像微软的Access数据库。有一个查看SQLite数据库文件的工具——SQLite Developer，我们可以使用它来查看数据库。Android将创建的数据库存放在”/data/data/ com.jbridge.db/databases/person”，我们将它导出然后使用SQLite Developer打开。

 

  6.分页显示数据

       我们在ContactsService类中，提供了一个获取分页数据的方法。我们将调用它获取的数据，使用ListView组件显示出来。

       编辑mail.xml：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
    <RelativeLayout
  xmlns:android="http://schemas.android.com/apk/res/android"
  android:layout_width="fill_parent"
  android:layout_height="wrap_content">
  <TextView
  android:layout_width="40px"
  android:layout_height="wrap_content"
  android:textSize="20px"
  android:id="@+id/personidtitle"
  android:text="编号"
  />
   <TextView
  android:layout_width="200px"
  android:layout_height="wrap_content"
   android:textSize="20px"
  android:layout_toRightOf="@id/personidtitle"
   android:layout_alignTop="@id/personidtitle"
   android:gravity="center_horizontal"
   android:id="@+id/nametitle"
   android:text="姓名"
  />
   <TextView
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
   android:textSize="20px"
  android:layout_toRightOf="@id/nametitle"
   android:layout_alignTop="@id/nametitle"
   android:id="@+id/agetitle"
   android:text="年龄"
  />
</RelativeLayout>
<ListView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:id="@+id/listView"
    />
</LinearLayout>

在mail.xml所在目录里添加一个personitem.xml：

<?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:layout_width="40px"
  android:layout_height="wrap_content"
  android:textSize="20px"
  android:id="@+id/personid"
  />
   <TextView
  android:layout_width="200px"
  android:layout_height="wrap_content"
   android:textSize="20px"
  android:layout_toRightOf="@id/personid"
   android:layout_alignTop="@id/personid"
   android:gravity="center_horizontal"
   android:id="@+id/name"
  />
   <TextView
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
   android:textSize="20px"
  android:layout_toRightOf="@id/name"
   android:layout_alignTop="@id/name"
   android:id="@+id/age"
  />
</RelativeLayout>

编辑 DBActivity 类：

package com.jbridge.db;

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

import com.jbridge.domain.Person;
import com.jbridge.service.PersonService;

import android.R.string;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.provider.LiveFolders;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;

public class DBActivity extends Activity {
	/** Called when the activity is first created. */
	private static final String TAG = "DBActivity";
/*实现方法一
	 @Override
	 public void onCreate(Bundle savedInstanceState) {
	 super.onCreate(savedInstanceState);
	 setContentView(R.layout.main);
	 PersonService personService=new PersonService(this);
	 ListView listView = (ListView) this.findViewById(R.id.listView);
	
	 List<HashMap<String, String>> data = new ArrayList<HashMap<String,
	 String>>();
	 // HashMap<String, String> title = new HashMap<String, String>();
	 // title.put("personid", "编号");
	 // title.put("name", "姓名");
	 // title.put("age", "年龄");
	 // data.add(title);
	
	 List<Person> persons= personService.getScrollData(0, 10);
	 for (Person person : persons) {
	 HashMap<String, String> p = new HashMap<String, String>();
	 p.put("personid", String.valueOf(person.getId()));
	 p.put("name", person.getName());
	 p.put("age",String.valueOf(person.getAge()));
	 data.add(p);
	 }
	
	 // 适配器有：
	 // ArrayAdapter<T>
	 // simpAdapter
	 // SimpleCursorAdapter
	 SimpleAdapter adapter = new SimpleAdapter(DBActivity.this, data,
	 R.layout.personitem,
	 new String[] { "personid", "name", "age" },
	 new int[] {R.id.personid, R.id.name, R.id.age });
	 listView.setAdapter(adapter);
	
	 listView.setOnItemClickListener(new AdapterView.OnItemClickListener(){
	 @Override
	 // parent即为你点击的listView
	 // view为listview的外面布局
	 public void onItemClick(AdapterView<?> parent, View view, int position,
	 long id) {
	 ListView listView= (ListView) parent;
	 HashMap<String, String> itemdata= (HashMap<String, String>)
	 listView.getItemAtPosition(position);
	 String personid=itemdata.get("personid");
	 String name=itemdata.get("name");
	 String age=itemdata.get("age");
	 Log.i(TAG,view.getClass().getName());
	 Log.i(TAG, "personid: "+personid+ "   name: "+name+"   age:   "+age);
	 Log.i(TAG," position==id:"+ (position==id));
	Toast.makeText(DBActivity.this, name, Toast.LENGTH_LONG).show();
	 }
	
	 });
	
	 }
	
*/
	
//	实现方法二（游标）
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		PersonService personService = new PersonService(this);
		ListView listView = (ListView) this.findViewById(R.id.listView);

		List<HashMap<String, String>> data = new ArrayList<HashMap<String, String>>();
		// HashMap<String, String> title = new HashMap<String, String>();
		// title.put("personid", "编号");
		// title.put("name", "姓名");
		// title.put("age", "年龄");
		// data.add(title);

		// 适配器有：
		// ArrayAdapter<T>
		// simpAdapter
		// SimpleCursorAdapter
		Cursor cursor = personService.getRawScrollData(0, 10);
		SimpleCursorAdapter adapter = new SimpleCursorAdapter(DBActivity.this,
				R.layout.personitem, cursor, new String[] { "_id", "name",
						"age" },
				new int[] { R.id.personid, R.id.name, R.id.age });
		listView.setAdapter(adapter);

		listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

			@Override
			// parent即为你点击的listView
			// view为listview的外面布局
			public void onItemClick(AdapterView<?> parent, View view,
					int position, long id) {
				ListView listView = (ListView) parent;
				Cursor cursor = (Cursor) listView.getItemAtPosition(position);
				String personid = String.valueOf(cursor.getInt(0));
				String name = String.valueOf(cursor.getString(1));
				String age = String.valueOf(cursor.getShort(2));
				Log.i(TAG, view.getClass().getName());
				Log.i(TAG, "personid: " + personid + "   name: " + name
						+ "   age:   " + age);
				Log.i(TAG, " position==id:" + (position == id));
				Toast.makeText(DBActivity.this, name, Toast.LENGTH_LONG).show();
			}

		});

	}
}