android数据存储之SQLite

我们先来看下用SQLite做的一个分页示例（这个代码也是网上来的，做了下修改，如有侵权，请告之）：

布局：

整体布局：

<?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="60px"
	   	 android:layout_height="wrap_content"
	   	 android:id="@+id/personIdTitle"
	   	 android:gravity="left"
	   	 android:text="编号"
	   	 android:textSize="20px"
	   	/>
	   
	   <TextView
	   	 android:layout_width="150px"
	   	 android:layout_height="wrap_content"
	   	 android:layout_toRightOf="@id/personIdTitle"
	   	 android:layout_alignTop="@id/personIdTitle"
	   	 android:gravity="center_horizontal"
	   	 android:id="@+id/personNameTitle"
	   	 android:text="名称"
	   	 android:textSize="20px"
	   	/>
	   	
	   <TextView
	   	 android:layout_width="60px"
	   	 android:layout_height="wrap_content"
	   	 android:layout_toRightOf="@id/personNameTitle"
	   	 android:layout_alignTop="@id/personNameTitle"
	   	 android:gravity="right"
	   	 android:id="@+id/personAgeTitle"
	   	 android:text="年龄"
	   	 android:textSize="20px"
	   	/>	
	</RelativeLayout>
	<ListView  
	    android:layout_width="fill_parent" 
	    android:layout_height="wrap_content" 
	    android:layout_weight="1"
	    android:id="@+id/listView"
	    />
	<RelativeLayout
		  xmlns:android="http://schemas.android.com/apk/res/android"
	      android:layout_width="fill_parent"
	      android:layout_height="wrap_content">
	      <Button
	        android:layout_width="wrap_content" 
	   		android:layout_height="wrap_content" 
	   		android:layout_marginLeft="20px" 
	   		android:text="首页"
	    	android:id="@+id/first"
	    	/>
	      <Button
	        android:layout_width="wrap_content" 
	   		android:layout_height="wrap_content"
	   		android:layout_alignTop="@id/first"
	   		android:layout_toRightOf="@id/first"
	   		android:layout_marginLeft="5px"
	   		android:text="上一页"
	    	android:id="@+id/last"
	    	/>
	      <Button
	        android:layout_width="wrap_content" 
	   		android:layout_height="wrap_content"
	   		android:layout_alignTop="@id/last"
	   		android:layout_toRightOf="@id/last"
	   		android:layout_marginLeft="5px" 
	   		android:text="下一页"
	        android:id="@+id/next"
	    	/>
	      <Button
	        android:layout_width="wrap_content" 
	   		android:layout_height="wrap_content" 
	   		android:layout_alignTop="@id/next"
	   		android:layout_toRightOf="@id/next"
	   		android:layout_marginLeft="5px" 
	   		android:text="最后一页"
	    	android:id="@+id/end"
	    	/>
	      <TextView
	        android:layout_width="wrap_content" 
	   		android:layout_height="wrap_content" 
	   		android:layout_alignBottom="@id/end"
	   		android:layout_toRightOf="@id/end"
	   		android:layout_marginLeft="5px" 
	    	android:id="@+id/pageInfo"
	    	/>
		  </RelativeLayout>  
</LinearLayout>

list的item布局：

<?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="60px"
   	 android:layout_height="wrap_content"
   	 android:id="@+id/personId"
   	 android:gravity="left"
   	 android:textSize="20px"
   	/>
   
   <TextView
   	 android:layout_width="150px"
   	 android:layout_height="wrap_content"
   	 android:layout_toRightOf="@id/personId"
   	 android:layout_alignTop="@id/personId"
   	 android:gravity="center_horizontal"
   	 android:id="@+id/personName"
   	 android:textSize="20px"
   	/>
   	
   <TextView
   	 android:layout_width="60px"
   	 android:layout_height="wrap_content"
   	 android:layout_toRightOf="@id/personName"
   	 android:layout_alignTop="@id/personName"
   	 android:gravity="right"
   	 android:id="@+id/personAge"
   	 android:textSize="20px"
   	/>	
</RelativeLayout>

定义一个对象类：

package com.android.SQLiteDemo;

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

定义SQLiteOpenHelper：

package com.android.SQLiteDemo;

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

public class DBHelper extends SQLiteOpenHelper {
	
	public DBHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}
	
	public DBHelper(Context context, String name, int version) {
		this(context, name, null, version);
	}
	
	@Override
	public void onCreate(SQLiteDatabase sld) {
		sld.execSQL("create table person(id integer primary key autoincrement, name varchar(20), age integer)");
		System.out.println("database create ...");
	}

	@Override
	public void onUpgrade(SQLiteDatabase sld, int arg1, int arg2) {
		sld.execSQL("drop table if exists person");
		System.out.println("database upgrade ...");
		onCreate(sld);
	}

}

定义SQlite操作类：

package com.android.SQLiteDemo;

import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class PersonService {
	private static DBHelper db;
	public PersonService(Context context, String name, CursorFactory factory, int version) {
		db = new DBHelper(context, name, factory, version);
	}
	
	public void save(Person person) {
		SQLiteDatabase data = db.getWritableDatabase();
		data.execSQL("insert into person(name, age) values(?,?)", new Object[]{person.getName(), person.getAge()});
		data.close();
	}
	
	@SuppressWarnings("unused")
	public void delete(Integer ... ids) {
		if (ids.length > 0) {
			StringBuffer sb = new StringBuffer();
			for (Integer id : ids) {
				sb.append("?").append(",");
				sb.deleteCharAt(ids.length);
			}
			SQLiteDatabase data = db.getWritableDatabase();
			data.execSQL("delete from person where id in(" + sb.toString() + ")", (Object[])ids);
			data.close();
		}
		
	}
	
	public void update(Person person) {
		SQLiteDatabase data = db.getWritableDatabase();
		data.execSQL("update person set name=?, age=? where id=?", new Object[]{person.getName(), person.getAge(), person.getId()});
		data.close();
	}
	
	public Person find(Integer id) {
		SQLiteDatabase data = db.getReadableDatabase();
		Cursor cursor = data.rawQuery("select * from person where id=?",  new String[]{String.valueOf(id)});
		if (cursor.moveToNext()) {
			Person mPerson = new Person(cursor.getInt(0), cursor.getString(1), cursor.getShort(2));
			cursor.close();
			data.close();
			return mPerson;
		}
		cursor.close();
		data.close();
		return null;
	}
	
	public static List<Person> queryList(int startIndex, int endIndex) {
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase data = db.getReadableDatabase();
		Cursor cursor = data.rawQuery("select * from person limit ?,?", new String[]{String.valueOf(startIndex), String.valueOf(endIndex)});
		while (cursor.moveToNext()) {
			persons.add(new Person(cursor.getInt(0), cursor.getString(1), cursor.getShort(2)));
		}
		cursor.close();
		data.close();
		return persons;
	}
	
	public static long getCount() {
		SQLiteDatabase data = db.getReadableDatabase();
		Cursor cursor = data.rawQuery("select count(*) from person", null);
		if (cursor.moveToNext()) {
			long mCount = cursor.getLong(0);
			cursor.close();
			data.close();
			return mCount;
		}
		cursor.close();
		data.close();
		return 0;
	}
	
}

定义分页工具类：

package com.android.SQLiteDemo;

import java.util.List;

import android.util.Log;

public class PageUtil {
	public static int pagesize = 12;
	private static int actualpage = 0;
	public static int count = new Long(PersonService.getCount()).intValue();
	public static int pagecount = (count % pagesize == 0)?(count / pagesize):(count / pagesize + 1);
	
	public static int getCurrentPage() {
		return actualpage + 1;
	}
	
	public static List<Person> getFirst() {
		actualpage = 0;
		Log.e("PageUtil", "getFirst actualpage = "+actualpage+ " pageCount="+pagecount+" count="+count);
		return PersonService.queryList(0, pagesize);
	}
	
	public static List<Person> getEnd() {
		actualpage = pagecount - 1;
		Log.e("PageUtil", "getEnd actualpage = "+actualpage+ " pageCount="+pagecount+" count="+count);
		return PersonService.queryList(actualpage * pagesize, pagesize);
	}
	
	public static List<Person> getLast() {
		Log.e("PageUtil", "getLast actualpage = "+actualpage+ " pageCount="+pagecount+" count="+count);
		if (actualpage == 0) {
			return getFirst();
		} else {
			actualpage--; 
			return PersonService.queryList(actualpage * pagesize, pagesize);
		}
	}
	
	public static List<Person> getNext() {
		Log.e("PageUtil", "getNext actualpage = "+actualpage+ " pageCount="+pagecount+" count="+count);
		
		if (actualpage == pagecount - 1) {
			return getEnd();
		} else {
			actualpage++;
			return PersonService.queryList(actualpage * pagesize, pagesize);
		}
	}
}

定义主activity：

package com.android.SQLiteDemo;


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




import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;


public class SQLiteDemoActivity extends Activity {
private ListView listView;
private PersonService personService;
List<Person> personList = null;
private Map<String, String> map;
private List<Map<String, String>> dataList;
private Button first;
private Button last;
private Button next;
private Button end;
private TextView pageInfo;
    @SuppressWarnings("static-access")
@Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        listView = (ListView) findViewById(R.id.listView);
        personService = new PersonService(this, "androidla", null, 2);


        if (personService.getCount() == 0) {
        for (int i = 0; i < 100; i++) {
        Person p = new Person(i, Integer.toString(i), Short.valueOf(Integer.toString(i)));
        personService.save(p);
        }
        }
        
        dataList = new ArrayList<Map<String, String>>();
        personList = PageUtil.getFirst();
        for (Person person : personList) {
        map = new HashMap<String, String>();
        map.put("id", person.getId().toString());
        map.put("name", person.getName());
        map.put("age", person.getAge().toString());
        dataList.add(map);
        }
        SimpleAdapter adapter = new SimpleAdapter(SQLiteDemoActivity.this, dataList, R.layout.person, new String[]{"id", "name", "age"}, new int[]{R.id.personId, R.id.personName, R.id.personAge});
        listView.setAdapter(adapter);
        listView.setOnItemClickListener(onItemClickListener);
       
        first = (Button) findViewById(R.id.first);
        first.setOnClickListener(onClickListener);
        last = (Button) findViewById(R.id.last);
        last.setOnClickListener(onClickListener);
        last.setEnabled(false);
        next = (Button) findViewById(R.id.next);
        next.setOnClickListener(onClickListener);
        end = (Button) findViewById(R.id.end);
        end.setOnClickListener(onClickListener);
        pageInfo = (TextView) findViewById(R.id.pageInfo);
        pageInfo.setText("(" + 1 + "/" + PageUtil.pagecount + ")");
    }
    
    private OnItemClickListener onItemClickListener = new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> aView, View view, int arg2, long arg3) {
ListView listView = (ListView) aView;
Map<String, String> map = (Map<String, String>) listView.getItemAtPosition(arg2);
System.out.println();
Toast.makeText(SQLiteDemoActivity.this, map.get("id") + ":" + map.get("name") + ":" + map.get("age"), Toast.LENGTH_SHORT).show();
}
};

private OnClickListener onClickListener = new OnClickListener() {
@Override
public void onClick(View v) {
Button button = (Button) v;
dataList = new ArrayList<Map<String, String>>();
listView.setAdapter(null);
List<Person> personList = null;

if (PageUtil.pagecount == 0) {
Toast.makeText(SQLiteDemoActivity.this, "没有数据", Toast.LENGTH_LONG).show();
return;
}

switch (button.getId()) {
case R.id.first:
personList = PageUtil.getFirst();
break;
case R.id.last:
personList = PageUtil.getLast();
break;
case R.id.next:
personList = PageUtil.getNext();
break;
case R.id.end:
personList = PageUtil.getEnd();
break;
}
int currentPage = PageUtil.getCurrentPage();
pageInfo.setText("(" + Integer.toString(currentPage) + "/" + PageUtil.pagecount + ")");
if (currentPage == 1 && PageUtil.pagecount != 0) {
last.setEnabled(false);
next.setEnabled(true);
} else if (currentPage == PageUtil.pagecount && PageUtil.pagecount != 0) {
next.setEnabled(false);
last.setEnabled(true);
} else if (PageUtil.pagecount == 0) {
first.setEnabled(false);
last.setEnabled(false);
next.setEnabled(false);
end.setEnabled(false);
} else {
last.setEnabled(true);
next.setEnabled(true);
}
       for (Person person : personList) {
        map = new HashMap<String, String>();
        map.put("id", person.getId().toString());
        map.put("name", person.getName());
        map.put("age", person.getAge().toString());
        dataList.add(map);
       }
       SimpleAdapter adapter = new SimpleAdapter(SQLiteDemoActivity.this, dataList, R.layout.person, new String[]{"id", "name", "age"}, new int[]{R.id.personId, R.id.personName, R.id.personAge});
       listView.setAdapter(adapter);
       listView.setOnItemClickListener(onItemClickListener);
}
};
}

分页的具体操作方法很多，这个只是其中一种，可以参考下。

这里有一个点要注意，数据库操作最好放在独立线程中，这个实例没有，主要就是练习下分页。