Android增高第九篇之SQLite分页表格

Android提高第九篇之SQLite分页表格 .

本文来自http://blog.csdn.net/hellogv/ ，引用必须注明出处！

?????? 上次讲的Android上的SQLite分页读取，只用文本框显示数据而已，这次就讲得更加深入些，实现并封装一个SQL分页表格控件，不仅支持分页还是以表格的形式展示数据。先来看看本文程序运行的动画：

?????? 这个SQL分页表格控件主要分为“表格区”和“分页栏”这两部分，这两部分都是基于GridView实现的。网上介绍Android上实现表格的DEMO一般都用ListView。ListView与GridView对比，ListView最大的优势是格单元的大小可以自定义，可以某单元长某单元短，但是难于实现自适应数据表的结构；而GridView最大的优势就是自适应数据表的结构，但是格单元统一大小。。。对于数据表结构多变的情况，建议使用GridView实现表格。

本文实现的SQL分页表格控件有以下特点：

1.自适应数据表结构，但是格单元统一大小；

2.支持分页；

3.“表格区”有按键事件回调处理，“分页栏”有分页切换事件回调处理。

本文程序代码较多，可以到这里下载整个工程的源码：http://www.rayfile.com/files/72e78b68-f2e5-11df-8469-0015c55db73d/

items.xml的代码如下，它是“表格区”和“分页栏”的格单元实现：

<?xml version="1.0" encoding="utf-8"?><LinearLayout android:id="@+id/LinearLayout01"xmlns:android="http://schemas.android.com/apk/res/android"android:layout_width="fill_parent" android:background="#555555"android:layout_height="wrap_content"><TextView android:layout_below="@+id/ItemImage" android:text="TextView01"android:id="@+id/ItemText" android:bufferType="normal"android:singleLine="true" android:background="#000000"android:layout_width="fill_parent" android:gravity="center"android:layout_margin="1dip" android:layout_gravity="center"android:layout_height="wrap_content"></TextView></LinearLayout>

?

main.xml的代码如下：

<?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" android:id="@+id/MainLinearLayout"><Button android:layout_height="wrap_content"android:layout_width="fill_parent" android:id="@+id/btnCreateDB"android:text="创建数据库"></Button><Button android:layout_height="wrap_content"android:layout_width="fill_parent" android:text="插入一串实验数据" android:id="@+id/btnInsertRec"></Button><Button android:layout_height="wrap_content" android:id="@+id/btnClose"android:text="关闭数据库" android:layout_width="fill_parent"></Button></LinearLayout>

?

演示程序testSQLite.java的源码：

package com.testSQLite;import android.app.Activity;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.util.Log;import android.view.View;import android.widget.Button;import android.widget.LinearLayout;import android.widget.Toast;public class testSQLite extends Activity {GVTable table;Button btnCreateDB, btnInsert, btnClose;SQLiteDatabase db;int id;//添加记录时的id累加标记，必须全局private static final String TABLE_NAME = "stu";private static final String ID = "id";private static final String NAME = "name";private static final String PHONE = "phone";private static final String ADDRESS = "address";private static final String AGE = "age";@Overridepublic void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.main);btnCreateDB = (Button) this.findViewById(R.id.btnCreateDB);btnCreateDB.setOnClickListener(new ClickEvent());btnInsert = (Button) this.findViewById(R.id.btnInsertRec);btnInsert.setOnClickListener(new ClickEvent());btnClose = (Button) this.findViewById(R.id.btnClose);btnClose.setOnClickListener(new ClickEvent());table=new GVTable(this);table.gvSetTableRowCount(8);//设置每个分页的ROW总数LinearLayout ly = (LinearLayout) findViewById(R.id.MainLinearLayout);table.setTableOnClickListener(new GVTable.OnTableClickListener() {@Overridepublic void onTableClickListener(int x,int y,Cursor c) {c.moveToPosition(y);String str=c.getString(x)+" 位置:("+String.valueOf(x)+","+String.valueOf(y)+")";Toast.makeText(testSQLite.this, str, 1000).show();}});table.setOnPageSwitchListener(new GVTable.OnPageSwitchListener() {@Overridepublic void onPageSwitchListener(int pageID,int pageCount) {String str="共有"+String.valueOf(pageCount)+" 当前第"+String.valueOf(pageID)+"页";Toast.makeText(testSQLite.this, str, 1000).show();}});ly.addView(table);}class ClickEvent implements View.OnClickListener {@Overridepublic void onClick(View v) {if (v == btnCreateDB) {CreateDB();} else if (v == btnInsert) {InsertRecord(16);//插入16条记录table.gvUpdatePageBar("select count(*) from " + TABLE_NAME,db);table.gvReadyTable("select * from " + TABLE_NAME,db);}else if (v == btnClose) {table.gvRemoveAll();db.close();}}}/** * 在内存创建数据库和数据表 */void CreateDB() {// 在内存创建数据库db = SQLiteDatabase.create(null);Log.e("DB Path", db.getPath());String amount = String.valueOf(databaseList().length);Log.e("DB amount", amount);// 创建数据表String sql = "CREATE TABLE " + TABLE_NAME + " (" + ID+ " text not null, " + NAME + " text not null," + ADDRESS+ " text not null, " + PHONE + " text not null," + AGE+ " text not null "+");";try {db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);db.execSQL(sql);} catch (SQLException e) {}}/** * 插入N条数据 */void InsertRecord(int n) {int total = id + n;for (; id < total; id++) {String sql = "insert into " + TABLE_NAME + " (" + ID + ", " + NAME+", " + ADDRESS+", " + PHONE+", "+AGE+ ") values('" + String.valueOf(id) + "', 'man','address','123456789','18');";try {db.execSQL(sql);} catch (SQLException e) {}}}}

?

分页表格控件GVTable.java的源码：

view plaincopy to clipboardprint?

1. package?com.testSQLite;??import?java.util.ArrayList;??
2. import?java.util.HashMap;??import?android.content.Context;??
3. import?android.database.Cursor;??import?android.database.sqlite.SQLiteDatabase;??
4. import?android.view.View;??import?android.widget.AdapterView;??
5. import?android.widget.GridView;??import?android.widget.LinearLayout;??
6. import?android.widget.SimpleAdapter;??import?android.widget.AdapterView.OnItemClickListener;??
7. public?class?GVTable?extends?LinearLayout?{??????protected?GridView?gvTable,gvPage;????
8. ????protected?SimpleAdapter?saPageID,saTable;//?适配器 ??????protected?ArrayList<HashMap<String,?String>>?srcPageID,srcTable;//?数据源 ??
9. ??????????protected?int?TableRowCount=10;//分页时，每页的Row总数 ??
10. ????protected?int?TableColCount=0;//每页col的数量 ??????protected?SQLiteDatabase?db;??
11. ????protected?String?rawSQL="";??????protected?Cursor?curTable;//分页时使用的Cursor ??
12. ????protected?OnTableClickListener?clickListener;//整个分页控件被点击时的回调函数 ??????protected?OnPageSwitchListener?switchListener;//分页切换时的回调函数 ??
13. ??????????public?GVTable(Context?context)?{??
14. ????????super(context);??????????this.setOrientation(VERTICAL);//垂直 ??
15. ????????//---------------------------------------- ??????????gvTable=new?GridView(context);??
16. ????????addView(gvTable,?new?LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,??????????????????LayoutParams.WRAP_CONTENT));//宽长式样 ??
17. ??????????????????srcTable?=?new?ArrayList<HashMap<String,?String>>();??
18. ????????saTable?=?new?SimpleAdapter(context,??????????????????srcTable,//?数据来源 ??
19. ????????????????R.layout.items,//XML实现 ??????????????????new?String[]?{?"ItemText"?},//?动态数组与ImageItem对应的子项 ??
20. ????????????????new?int[]?{?R.id.ItemText?});??????????//?添加并且显示 ??
21. ????????gvTable.setAdapter(saTable);??????????gvTable.setOnItemClickListener(new?OnItemClickListener(){??
22. ????????????@Override??????????????public?void?onItemClick(AdapterView<?>?arg0,?View?arg1,?int?arg2,??
23. ????????????????????long?arg3)?{??????????????????int?y=arg2/curTable.getColumnCount()-1;//标题栏的不算 ??
24. ????????????????int?x=arg2?%?curTable.getColumnCount();??????????????????if?(clickListener?!=?null//分页数据被点击 ??
25. ????????????????????????&&?y!=-1)?{//点中的不是标题栏时 ??????????????????????clickListener.onTableClickListener(x,y,curTable);??
26. ????????????????}??????????????}??
27. ????????});????????????
28. ????????//---------------------------------------- ??????????gvPage=new?GridView(context);??
29. ????????gvPage.setColumnWidth(40);//设置每个分页按钮的宽度 ??????????gvPage.setNumColumns(GridView.AUTO_FIT);//分页按钮数量自动设置 ??
30. ????????addView(gvPage,?new?LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,??????????????????LayoutParams.WRAP_CONTENT));//宽长式样 ??
31. ????????srcPageID?=?new?ArrayList<HashMap<String,?String>>();??????????saPageID?=?new?SimpleAdapter(context,??
32. ????????????????srcPageID,//?数据来源 ??????????????????R.layout.items,//XML实现 ??
33. ????????????????new?String[]?{?"ItemText"?},//?动态数组与ImageItem对应的子项 ??????????????????new?int[]?{?R.id.ItemText?});??
34. ????????//?添加并且显示 ??????????gvPage.setAdapter(saPageID);??
35. ????????//?添加消息处理 ??????????gvPage.setOnItemClickListener(new?OnItemClickListener(){??
36. ????????????@Override??????????????public?void?onItemClick(AdapterView<?>?arg0,?View?arg1,?int?arg2,??
37. ????????????????????long?arg3)?{??????????????????LoadTable(arg2);//根据所选分页读取对应的数据 ??
38. ????????????????if(switchListener!=null){//分页切换时 ??????????????????????switchListener.onPageSwitchListener(arg2,srcPageID.size());??
39. ????????????????}??????????????}??
40. ????????});??????}??
41. ????/**??????*?清除所有数据?
42. ?????*/??????public?void?gvRemoveAll()??
43. ????{??????????if(this.curTable!=null)??
44. ????????????curTable.close();??????????srcTable.clear();??
45. ????????saTable.notifyDataSetChanged();????????
46. ????????srcPageID.clear();??????????saPageID.notifyDataSetChanged();??
47. ??????????????}??
48. ????/**??????*?读取指定ID的分页数据,返回当前页的总数据?
49. ?????*?SQL:Select?*?From?TABLE_NAME?Limit?9?Offset?10;??????*?表示从TABLE_NAME表获取数据，跳过10行，取9行?
50. ?????*?@param?pageID?指定的分页ID??????*/??
51. ????protected?void?LoadTable(int?pageID)??????{??
52. ????????if(curTable!=null)//释放上次的数据 ??????????????curTable.close();??
53. ??????????????????String?sql=?rawSQL+"?Limit?"+String.valueOf(TableRowCount)+?"?Offset?"?+String.valueOf(pageID*TableRowCount);??
54. ????????curTable?=?db.rawQuery(sql,?null);????????????
55. ????????gvTable.setNumColumns(curTable.getColumnCount());//表现为表格的关键点！ ??????????TableColCount=curTable.getColumnCount();??
56. ????????srcTable.clear();??????????//?取得字段名称 ??
57. ????????int?colCount?=?curTable.getColumnCount();??????????for?(int?i?=?0;?i?<?colCount;?i++)?{??
58. ????????????HashMap<String,?String>?map?=?new?HashMap<String,?String>();??????????????map.put("ItemText",?curTable.getColumnName(i));??
59. ????????????srcTable.add(map);??????????}??
60. ??????????????????//?列举出所有数据 ??
61. ????????int?recCount=curTable.getCount();??????????for?(int?i?=?0;?i?<?recCount;?i++)?{//定位到一条数据 ??
62. ????????????curTable.moveToPosition(i);??????????????for(int?ii=0;ii<colCount;ii++)//定位到一条数据中的每个字段 ??
63. ????????????{??????????????????HashMap<String,?String>?map?=?new?HashMap<String,?String>();??
64. ????????????????map.put("ItemText",?curTable.getString(ii));??????????????????srcTable.add(map);??
65. ????????????}??????????}??
66. ??????????????????saTable.notifyDataSetChanged();??
67. ????}??????/**?
68. ?????*?设置表格的最多显示的行数??????*?@param?row?表格的行数?
69. ?????*/??????public?void?gvSetTableRowCount(int?row)??
70. ????{??????????TableRowCount=row;??
71. ????}????????
72. ????/**??????*?取得表格的最大行数?????
73. ?????*?@return?行数??????*/??
74. ????public?int?gvGetTableRowCount()??????{??
75. ????????return?TableRowCount;??????}??
76. ??????????/**?
77. ?????*?取得当前分页的Cursor??????*?@return?当前分页的Cursor?
78. ?????*/??????public?Cursor?gvGetCurrentTable()??
79. ????{??????????return?curTable;??
80. ????}????????????
81. ????/**??????*?准备分页显示数据?
82. ?????*?@param?rawSQL?sql语句??????*?@param?db?数据库?
83. ?????*/??????public?void?gvReadyTable(String?rawSQL,SQLiteDatabase?db)??
84. ????{??????????this.rawSQL=rawSQL;??
85. ????????this.db=db;??????}??
86. ??????????/**?
87. ?????*?刷新分页栏，更新按钮数量??????*?@param?sql?SQL语句?
88. ?????*?@param?db?数据库??????*/??
89. ????public?void?gvUpdatePageBar(String?sql,SQLiteDatabase?db)??????{??
90. ????????Cursor?rec?=?db.rawQuery(sql,?null);??????????rec.moveToLast();??
91. ????????long?recSize=rec.getLong(0);//取得总数 ??????????rec.close();??
92. ????????int?pageNum=(int)(recSize/TableRowCount)?+?1;//取得分页数 ????????????
93. ????????srcPageID.clear();??????????for?(int?i?=?0;?i?<?pageNum;?i++)?{??
94. ????????????HashMap<String,?String>?map?=?new?HashMap<String,?String>();??????????????map.put("ItemText",?"No."?+?String.valueOf(i));//?添加图像资源的ID ??
95. ????????????srcPageID.add(map);??????????}??
96. ????????saPageID.notifyDataSetChanged();??????}??
97. ????//--------------------- ??????/**?
98. ?????*?表格被点击时的回调函数??????*/??
99. ????public?void?setTableOnClickListener(OnTableClickListener?click)?{??????????this.clickListener?=?click;??
100. ????}????????
101. ????public?interface?OnTableClickListener?{??????????public?void?onTableClickListener(int?x,int?y,Cursor?c);??
102. ????}??????//--------------------- ??
103. ????/**??????*?分页栏被点击时的回调函数?
104. ?????*/??????public?void?setOnPageSwitchListener(OnPageSwitchListener?pageSwitch)?{??
105. ????????this.switchListener?=?pageSwitch;??????}??
106. ????public?interface?OnPageSwitchListener?{??????????public?void?onPageSwitchListener(int?pageID,int?pageCount);??
107. ????}??}??

查看更多 下一篇