今天用到了android中滑动SQlit数据库分页加载技术，写了个测试工程，将代码贴出来和大家交流一下：
MainActivity
package com.example.testscrollsqlite;
import java.util.ArrayList;
import android.app.ActionBar.LayoutParams;
import android.app.Activity;
import android.os.Bundle;
import android.view.Gravity;
import android.view.Menu;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AbsListView;
import android.widget.AbsListView.OnScrollListener;
import android.widget.Adapter;
import android.widget.BaseAdapter;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.TextView;
public class MainActivity extends Activity implements OnScrollListener{
private TextView loadInfo;
private ListView listView;
private LinearLayout loadLayout;
private ArrayList<String> items;
private DatabaseService service;
private int currentPage = 1; //默认在第一页
private static final int lineSize = 7; //每次显示数
private int allRecorders = 0; //全部记录数
private int pageSize = 1; //默认共一页
private int lastItem;
private Aleph0 baseAdapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
listView = (ListView) findViewById(R.id.listview);
//创建一个角标线性布局用来显示"正在加载"
loadLayout = new LinearLayout(this);
loadLayout.setGravity(Gravity.CENTER);
//定义一个文本显示“正在加载”
loadInfo = new TextView(this);
loadInfo.setText("正在加载...");
loadInfo.setGravity(Gravity.CENTER);
//增加组件
loadLayout.addView(loadInfo, new LayoutParams(
LinearLayout.LayoutParams.MATCH_PARENT, LinearLayout.LayoutParams.WRAP_CONTENT));
//增加到listView底部
listView.addFooterView(loadLayout);
listView.setOnScrollListener(this);
showAllData();
}
/**
* 读取全部数据
*/
public void showAllData(){
service = new DatabaseService(this);
allRecorders = service.getCount();
//计算总页数
pageSize = (allRecorders + lineSize -1) / lineSize;
System.out.println("allRecorders = " + allRecorders);
System.out.println("pageSize = " + pageSize);
items = service.getAllItems(currentPage, lineSize);
for(int i=0; i<items.size(); i++){
System.out.println(items.get(i));
}
baseAdapter = new Aleph0();
listView.setAdapter(baseAdapter);
}
@Override
public void onScroll(AbsListView view, int firstVisible, int visibleCount,
int totalCount) {
lastItem = firstVisible + visibleCount - 1; //统计是否到最后
}
@Override
public void onScrollStateChanged(AbsListView view, int scorllState) {
System.out.println("进入滚动界面了");
//是否到最底部并且数据没读完
if(lastItem == baseAdapter.getCount()
&& currentPage < pageSize //不再滚动
&& scorllState == OnScrollListener.SCROLL_STATE_IDLE){
currentPage ++;
//设置显示位置
listView.setSelection(lastItem);
//增加数据
appendDate();
}
}
/**
* 增加数据
*/
private void appendDate(){
ArrayList<String> additems = service.getAllItems(currentPage, lineSize);
baseAdapter.setCount(baseAdapter.getCount() + additems.size());
//判断，如果到了最末尾则去掉“正在加载”
if(allRecorders == baseAdapter.getCount()){
listView.removeFooterView(loadLayout);
}
items.addAll(additems);
//通知记录改变
baseAdapter.notifyDataSetChanged();
}
class Aleph0 extends BaseAdapter {
int count = lineSize; /* starting amount */
public int getCount() {
return count;
}
public void setCount(int count){
this.count = count;
}
public Object getItem(int pos) {
return pos;
}
public long getItemId(int pos) {
return pos;
}
public View getView(int pos, View v, ViewGroup p) {
TextView view = new TextView(MainActivity.this);
view.setTextSize(60);
if(items != null){
view.setText(items.get(pos));
}else{
view.setText(pos);
}
return view;
}
}
}
MyDBOpenHelper
package com.example.testscrollsqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
public class MyDBOpenHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "merit.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "database";
public static final String FIELD_TITLE = "title";
/**
*
* @param context
* 上下文
* @param name
* 数据库的名字
* @param factory
* 结果集游标工厂(一般使用默认)
* @param version
* 数据库的版本号（必须version>=1）
*/
public MyDBOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**
* 在MyDBOpenHelper第一次new出来时，系统会调用onCreate方法
*/
@Override
public void onCreate(SQLiteDatabase db) {
System.out.println("我被调用了");
String sql = "Create table " + TABLE_NAME + "(" + BaseColumns._ID
+ " integer primary key autoincrement," + FIELD_TITLE
+ " text )";
db.execSQL(sql);
initDatabase(db);
}
// 向数据库的表中插入一些数据。
private void initDatabase(SQLiteDatabase db) {
ContentValues cv = new ContentValues();
cv.put("title", "cctv1 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv2 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv3 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv4 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv5 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv6 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv7 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv8 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv9 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("title", "cctv10 news");
db.insert(TABLE_NAME, null, cv);
cv.clear();
cv.put("news_title", "guangshui tv");
db.insert(TABLE_NAME, null, cv);
}
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
// TODO Auto-generated method stub
}
}
DatabaseService
package com.example.testscrollsqlite;
import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class DatabaseService {
private Context mContext;
private MyDBOpenHelper dbHelper;
public DatabaseService(Context context) {
// TODO Auto-generated constructor stub
mContext = context;
dbHelper = new MyDBOpenHelper(mContext);
}
// 添加
public void insert(String title) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String sql = "insert into database(title) values(?)";
db.execSQL(sql, new String[] { title });
}
// 删除
public void delete(String title) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String sql = "delete from database where title = ?";
db.execSQL(sql, new String[] { title });
}
// 查找
public ArrayList<String> find(int id) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String sql = "select * from database where _id = ? ";
Cursor c = db.rawQuery(sql, new String[] { String.valueOf(id) });
ArrayList<String> titles = new ArrayList<String>();
if (c.moveToNext()) {
String title = c.getString(c
.getColumnIndexOrThrow(MyDBOpenHelper.FIELD_TITLE));
titles.add(title);
return titles;
}
// 不用忘记关闭Cursor。
c.close();
return null;
}
// 更新
public void upDate(int id, String title) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String sql = "update database set title =? where _id = ?";
db.execSQL(sql,
new String[] { String.valueOf(title), String.valueOf(id) });
}
// 查询记录的总数
public int getCount() {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String sql = "select count(*) from database";
Cursor c = db.rawQuery(sql, null);
c.moveToFirst();
int length = c.getInt(0);
c.close();
return length;
}
/**
* 分页查询
*
* @param currentPage 当前页
* @param pageSize 每页显示的记录
* @return 当前页的记录
*/
public ArrayList<String> getAllItems(int currentPage, int pageSize) {
int firstResult = (currentPage - 1) * pageSize;
int maxResult = currentPage * pageSize;
SQLiteDatabase db = dbHelper.getWritableDatabase();
String sql = "select * from database limit ?,?";
Cursor mCursor = db.rawQuery(
sql,
new String[] { String.valueOf(firstResult),
String.valueOf(maxResult) });
ArrayList<String> items = new ArrayList<String>();
int columnCount = mCursor.getColumnCount();
while (mCursor.moveToNext()) {
String item = mCursor.getString(mCursor
.getColumnIndexOrThrow(MyDBOpenHelper.FIELD_TITLE));
items.add(item);
}
//不要关闭数据库
return items;
}
}
activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<ListView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/listview">
</ListView>
</LinearLayout>