android——查询所有，查询个数、查询分页

1、PersonDao

完整版的PersonDao的代码如下：

package com.njupt.sqllist;

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

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

public class PersonDao {

	DBOpenHelper helper ;
	
	public PersonDao(Context context){
		helper = new DBOpenHelper(context);
	}
	
	public void insert(Person p){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("insert into person(name,balance) values(?,?)", new Object[]{ p.getName(),p.getBalance()});
	    db.close();
	}
	
	public void delete(int id){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("delete from person where id = ?", new Object[]{id});
		db.close();
	}
	
	public void update(Person p){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update person set name = ? , balance = ? where id = ? ", new Object[]{p.getName(),p.getBalance(),p.getId()});
	}
	
	
	public Person query(int id){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("select name , balance from person where id = ?", new String[]{id + ""});
	    Person p = null ;
		if(c.moveToNext()){
			String name = c.getString(c.getColumnIndex("name"));
			int balance = c.getInt(1);
			
			p = new Person(id,name,balance);
		}
		
		return p;
	}
	
	public List<Person> queryAll(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("select * from person", null);
		List<Person> persons = new ArrayList<Person>();
		
		while(c.moveToNext()){
			Person p = new Person(c.getInt(0),c.getString(1),c.getInt(2));
			
			persons.add(p);
		}
		
		
		return persons;
	}
	
	
	public int queryCount(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("select count(*) from person", null);
		
		 c.moveToNext();
		 
		 int count = c.getInt(0);
		 
		return count;
	}
	
	public List<Person> queryPage(int pageNum , int pageSize){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("select * from person limit ?,?", new String[]{(pageNum - 1)*pageSize + "",pageSize + ""  });
		
		List<Person> persons = new ArrayList<Person>();
		
		while(c.moveToNext()){
			Person p = new Person(c.getInt(0),c.getString(1),c.getInt(2));
			
			persons.add(p);
		}
		
		return persons;
	}
	
	
}

2、DBTest

以下附上完整版的测试代码：

package com.njupt.sqllist;


import java.util.List;
import java.util.Random;

import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase{

	public void test1(){
		DBOpenHelper helper = new DBOpenHelper(getContext());
		helper.getWritableDatabase();
		
	}
	
	public void testInsert(){
		PersonDao dao = new PersonDao(getContext());
	    Random r = new Random(40000);
		
	    for(int i = 0 ; i < 100 ; ++i ){
			Person p = new Person(0,"Test"+ i ,r.nextInt());
			dao.insert(p);
		}
		
	}
	
	
	public void testDelete(){
		PersonDao dao = new PersonDao(getContext());
	    
		dao.delete(1);
	}
	
	public void testUpdate(){
		PersonDao dao = new PersonDao(getContext());
		
		Person p = new Person(2,"刘亦菲",45000);
		dao.update(p);
	}
	
	
	public void testQuery(){
		PersonDao dao = new PersonDao(getContext());
		
		Person p = dao.query(3);
		
		System.out.println(p);
	}
	
	public void testQueryAll(){
		PersonDao dao = new PersonDao(getContext());
		
		List<Person> persons = dao.queryAll();
	    
		for(Person p : persons){
			System.out.println(p);
		}
	}
	
	
	public void testQueryCount(){
		PersonDao dao = new PersonDao(getContext());
		
		int count = dao.queryCount();
		
		System.out.println(count);
	}
	
	public void testQueryPage(){
		PersonDao dao = new PersonDao(getContext());
		
		List<Person> persons = dao.queryPage(2, 10);
		
		for(Person p : persons){
			System.out.println(p);
		}
	}
}

===============================================

分页的结果如下所示：

查询总数：