首页 > 代码库 > Sqlite 数据库分页查询(ListView分页显示数据)

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();    }  }}

贴上效果图:

技术分享

Sqlite 数据库分页查询(ListView分页显示数据)