Sqlite 数据库分页查询(ListView分页显示数据)
下面介绍一下我的这个demo。
流程简述：
我在raw文件夹下面放了名称为city的数据库，里面包含全国2330个城市，以及所属省，拼音简写等信息。
首先 在进入MainActivity的时候，创建数据库并读入sd卡文件中data/data/databases/city。
然后 我再开启子线程去读取前50条数据，显示在ListView中。
当用户浏览数据， 前50条不够时，他会滑动ListView以查看更多数据，此时，listview的数据源会递增，50 ，100，150，。。。。
以50为增量不断增加.....
这样避免了因一次性加载数据造成ANR，也给用户比较好的体验。
工程目录结构：
cls_city是城市信息类，Common是工具类，ViewHolder是ListView缓存机制帮助类，DataBaseHelper 数据库操作工具类/.....
package com.example.sqlitepagetest; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.List; import android.database.Cursor; import android.util.Log; /** * <p> * </p> * 下午9:12:42 * * @auther dalvikCoder */ public class cls_city { public String _id = ""; public String province = ""; public String name = ""; /** * * select _id,province ,name from city order by _id limit perItemNum Offset * currentPage*perItemNum ;--currentPage 从零开始 * <p> * 根据条目数量查询 * </p> * * @param dbh * @param num * num[1]-->每一页显示的条目数量 num[0]--->从第几条开始 * @return List<cls_city> */ public static List<cls_city> getCityList(DatabaseHelper dbh, int num[]) { String sql = "select _id,province ,name from city order by _id limit " + num[0] + "," + num[1]; Log.e("select city's sql --------------", sql); Cursor cursor = dbh.rawQuery(sql); List<cls_city> list = new ArrayList<cls_city>(); cls_city cls = null; while (cursor.moveToNext()) { cls = new cls_city(); setClassValueBycursor(cls, cursor); list.add(cls); } return list; } /** * <p> * 利用反射机制给对象赋值 * </p> * @param obj * @param cursor void */ public static void setClassValueBycursor(Object obj, Cursor cursor) { int ColCount = cursor.getColumnCount(); int i = 0; for (i = 0; i < ColCount; i++) { String ColName = cursor.getColumnName(i); try { Field f = obj.getClass().getField(ColName); String ret = cursor.getString(i); if (f == null) continue; if (ret == null) ret = ""; f.set(obj, ret); } catch (SecurityException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (NoSuchFieldException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IllegalArgumentException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
package com.example.sqlitepagetest; import android.util.SparseArray; import android.view.View; /** * <p> * ListView缓存的写法比较多种，下面也是其中一中， * </p> * 下午9:45:29 * * @auther dalvikCoder */ public class ViewHolder { // I added a generic return type to reduce the casting noise in client code @SuppressWarnings("unchecked") public static <T extends View> T get(View view, int id) { SparseArray<View> viewHolder = (SparseArray<View>) view.getTag(); if (viewHolder == null) { viewHolder = new SparseArray<View>(); view.setTag(viewHolder); } View childView = viewHolder.get(id); if (childView == null) { childView = view.findViewById(id); viewHolder.put(id, childView); } return (T) childView; } }
在使用了ViewHolder之后，自定义适配器里面的代码看起来好多了。
@Override public View getView(int position, View convertView, ViewGroup parent) { if (convertView == null) { convertView = inflater.inflate(R.layout.city_lv_item, null); } TextView cityId = ViewHolder.get(convertView, R.id.cityidtxt); TextView provincetxt = ViewHolder.get(convertView, R.id.provincetxt); TextView cityName = ViewHolder.get(convertView, R.id.nametxt); cls_city city = cityList.get(position); cityId.setText(city._id); provincetxt.setText(city.province); cityName.setText(city.name); return convertView; }
下面是总的MainActivity类，里面的注释比较详细，也比较简单。
当然如果有兴趣你可以给ListView添加尾部视图，比如加个状态文字或者加个加个进度条，不过像本地数据，好像用不了多少时间
package com.example.sqlitepagetest; import java.util.ArrayList; import java.util.List; import android.app.Activity; import android.os.Bundle; import android.os.Handler; import android.os.Message; import android.view.Menu; import android.widget.AbsListView; import android.widget.AbsListView.OnScrollListener; import android.widget.ListView; public class MainActivity extends Activity { private ListView cityListView; private List<cls_city> cityList; private CityAdapter cityAdapter; private MyThread thread = null; /** * 每页有数据条数 这个数量可以根据需要更改，而不需在程序中更改具体数值 * **/ private int perPageItemNum = 100; /** 当前是第几页 0表示第一页 **/ private int currentPage = 0; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); setUpView(); } private void setUpView() { cityList = new ArrayList<cls_city>(); try { Common.loadCityDatabase(this); } catch (Exception e) { e.printStackTrace(); } Common.dbh = new DatabaseHelper(this, "city"); cityListView = (ListView) findViewById(R.id.citylistview); cityAdapter = new CityAdapter(this, cityList); cityListView.setAdapter(cityAdapter); cityListView.setOnScrollListener(new OnScrollListener() { @Override public void onScrollStateChanged(AbsListView view, int scrollState) { if (view.getLastVisiblePosition() == view.getCount() - 1 && scrollState == OnScrollListener.SCROLL_STATE_IDLE) { if (thread != null && !thread.isInterrupted()) { thread.interrupt(); thread = null; } currentPage++; cityListView.setSelection(view.getLastVisiblePosition());// 设置显示位置，这句只是让Listview停留在最后末尾的显示而已，加不加影响不大 thread = new MyThread(); thread.start(); } } @Override public void onScroll(AbsListView view, int firstVisibleItem, int visibleItemCount, int totalItemCount) { } }); if (thread == null) { thread = new MyThread(); thread.start(); } } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.main, menu); return true; } class MyThread extends Thread { @Override public void run() { int num[] = new int[2]; num[0] = currentPage * perPageItemNum;// 0*50 1*50 2*50 num[1] = perPageItemNum; List<cls_city> dataList = cls_city.getCityList(Common.dbh, num); // try { // Thread.sleep(1000); // } catch (InterruptedException e) { // e.printStackTrace(); // } Message msg = new Message(); msg.what = 1; msg.obj = dataList; mHandler.sendMessage(msg); } } private Handler mHandler = new Handler() { @Override public void handleMessage(Message msg) { super.handleMessage(msg); List<cls_city> dataList = (List<cls_city>) msg.obj; if (!dataList.isEmpty()) { cityAdapter.refresh(dataList); } } }; }
Common类
package com.example.sqlitepagetest; import java.io.File; import java.io.FileOutputStream; import java.io.InputStream; import android.content.Context; /** * <p> * </p> * 下午9:02:19 * * @auther dalvikCoder */ public class Common { public static DatabaseHelper dbh = null; public static void loadCityDatabase(Context context) throws Exception { // ------------创建路径 String path = "/data/data/" + context.getPackageName() + "/databases"; File file = new File(path); if (!file.exists()) { file.mkdirs(); } // --------------该路径下创建数据库文件 File f = new File(path, "city"); if (!f.exists()) { InputStream is = context.getResources().openRawResource(R.raw.city); FileOutputStream out = new FileOutputStream(path + "/city"); byte buffer[] = new byte[2 * 1024]; int len = 0; while ((len = is.read(buffer)) > 0) { out.write(buffer, 0, len); } out.close(); is.close(); } } }
贴上效果图:
