Android教程網
  1. 首頁
  2. Android 技術
  3. Android 手機
  4. Android 系統教程
  5. Android 游戲
 Android教程網 >> Android技術 >> Android開發 >> 關於android開發 >> Android提高21篇之九:SQLite分頁表格

Android提高21篇之九:SQLite分頁表格

編輯:關於android開發

       上次講的Android上的SQLite分頁讀取,只用文本框顯示數據而已,這次就講得更加深入些,實現並封裝一個SQL分頁表格控件,不僅支持分頁還是以表格的形式展示數據。先來看看本文程序運行的動畫:

       這個SQL分頁表格控件主要分為“表格區”和“分頁欄”這兩部分,這兩部分都是基於GridView實現的。網上介紹Android上實現表格的DEMO一般都用ListView。ListView與GridView對比,ListView最大的優勢是格單元的大小可以自定義,可以某單元長某單元短,但是難於實現自適應數據表的結構;而GridView最大的優勢就是自適應數據表的結構,但是格單元統一大小。。。對於數據表結構多變的情況,建議使用GridView實現表格。

       本文實現的SQL分頁表格控件有以下特點:

       1.自適應數據表結構,但是格單元統一大小;

       2.支持分頁;

       3.“表格區”有按鍵事件回調處理,“分頁欄”有分頁切換事件回調處理。

       items.xml的代碼如下,它是“表格區”和“分頁欄”的格單元實現:

XML/HTML代碼
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <LinearLayout android:id="@+id/LinearLayout01"  
  3.         xmlns:android="http://schemas.android.com/apk/res/android"  
  4.         android:layout_width="fill_parent" android:background="#555555"  
  5.         android:layout_height="wrap_content">  
  6.         <TextView android:layout_below="@+id/ItemImage" android:text="TextView01"  
  7.                 android:id="@+id/ItemText" android:bufferType="normal"  
  8.                 android:singleLine="true" android:background="#000000"  
  9.                 android:layout_width="fill_parent" android:gravity="center"  
  10.                 android:layout_margin="1dip" android:layout_gravity="center"  
  11.                 android:layout_height="wrap_content">  
  12.         </TextView>  
  13. </LinearLayout>  

        main.xml的代碼如下:

XML/HTML代碼
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.         android:orientation="vertical" android:layout_width="fill_parent"  
  4.         android:layout_height="fill_parent" android:id="@+id/MainLinearLayout">  
  5.         <Button android:layout_height="wrap_content"  
  6.                 android:layout_width="fill_parent" android:id="@+id/btnCreateDB"  
  7.                 android:text="創建數據庫"></Button>  
  8.         <Button android:layout_height="wrap_content"  
  9.                 android:layout_width="fill_parent" android:text="插入一串實驗數據" android:id="@+id/btnInsertRec"></Button>  
  10.         <Button android:layout_height="wrap_content" android:id="@+id/btnClose"  
  11.                 android:text="關閉數據庫" android:layout_width="fill_parent"></Button>  
  12. </LinearLayout>  

       演示程序testSQLite.java的源碼:

Java代碼
  1. package com.testSQLite;  
  2. import android.app.Activity;  
  3. import android.database.Cursor;  
  4. import android.database.SQLException;  
  5. import android.database.sqlite.SQLiteDatabase;  
  6. import android.os.Bundle;  
  7. import android.util.Log;  
  8. import android.view.View;  
  9. import android.widget.Button;  
  10. import android.widget.LinearLayout;  
  11. import android.widget.Toast;  
  12. public class testSQLite extends Activity {  
  13.         GVTable table;  
  14.         Button btnCreateDB, btnInsert, btnClose;  
  15.         SQLiteDatabase db;  
  16.         int id;//添加記錄時的id累加標記,必須全局  
  17.         private static final String TABLE_NAME = "stu";  
  18.         private static final String ID = "id";  
  19.         private static final String NAME = "name";  
  20.         private static final String PHONE = "phone";  
  21.         private static final String ADDRESS = "address";  
  22.         private static final String AGE = "age";  
  23.           
  24.         @Override  
  25.         public void onCreate(Bundle savedInstanceState) {  
  26.                 super.onCreate(savedInstanceState);  
  27.                 setContentView(R.layout.main);  
  28.                 btnCreateDB = (Button) this.findViewById(R.id.btnCreateDB);  
  29.                 btnCreateDB.setOnClickListener(new ClickEvent());  
  30.                 btnInsert = (Button) this.findViewById(R.id.btnInsertRec);  
  31.                 btnInsert.setOnClickListener(new ClickEvent());  
  32.                 btnClose = (Button) this.findViewById(R.id.btnClose);  
  33.                 btnClose.setOnClickListener(new ClickEvent());  
  34.                 table=new GVTable(this);  
  35.                 table.gvSetTableRowCount(8);//設置每個分頁的ROW總數  
  36.                 LinearLayout ly = (LinearLayout) findViewById(R.id.MainLinearLayout);  
  37.                 table.setTableOnClickListener(new GVTable.OnTableClickListener() {  
  38.                         @Override  
  39.                         public void onTableClickListener(int x,int y,Cursor c) {  
  40.                                 c.moveToPosition(y);  
  41.                                 String str=c.getString(x)+" 位置:("+String.valueOf(x)+","+String.valueOf(y)+")";  
  42.                                 Toast.makeText(testSQLite.this, str, 1000).show();  
  43.                         }  
  44.                 });  
  45.                 table.setOnPageSwitchListener(new GVTable.OnPageSwitchListener() {  
  46.                           
  47.                         @Override  
  48.                         public void onPageSwitchListener(int pageID,int pageCount) {  
  49.                                 String str="共有"+String.valueOf(pageCount)+  
  50.                                 " 當前第"+String.valueOf(pageID)+"頁";  
  51.                                 Toast.makeText(testSQLite.this, str, 1000).show();  
  52.                         }  
  53.                 });  
  54.                   
  55.                 ly.addView(table);  
  56.         }  
  57.         class ClickEvent implements View.OnClickListener {  
  58.                 @Override  
  59.                 public void onClick(View v) {  
  60.                         if (v == btnCreateDB) {  
  61.                                 CreateDB();  
  62.                         } else if (v == btnInsert) {  
  63.                                 InsertRecord(16);//插入16條記錄  
  64.                                 table.gvUpdatePageBar("select count(*) from " + TABLE_NAME,db);  
  65.                                 table.gvReadyTable("select * from " + TABLE_NAME,db);  
  66.                         }else if (v == btnClose) {  
  67.                                 table.gvRemoveAll();  
  68.                                 db.close();  
  69.                                   
  70.                         }  
  71.                 }  
  72.         }  
  73.           
  74.         /** 
  75.          * 在內存創建數據庫和數據表 
  76.          */  
  77.         void CreateDB() {  
  78.                 // 在內存創建數據庫  
  79.                 db = SQLiteDatabase.create(null);  
  80.                 Log.e("DB Path", db.getPath());  
  81.                 String amount = String.valueOf(databaseList().length);  
  82.                 Log.e("DB amount", amount);  
  83.                 // 創建數據表  
  84.                 String sql = "CREATE TABLE " + TABLE_NAME + " (" +   
  85.                         ID        + " text not null, " + NAME + " text not null," +  
  86.                         ADDRESS        + " text not null, " + PHONE + " text not null," +  
  87.                         AGE        + " text not null "+");";  
  88.                 try {  
  89.                         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);  
  90.                         db.execSQL(sql);  
  91.                 } catch (SQLException e) {}  
  92.         }  
  93.         /** 
  94.          * 插入N條數據 
  95.          */  
  96.         void InsertRecord(int n) {  
  97.                 int total = id + n;  
  98.                 for (; id < total; id++) {  
  99.                         String sql = "insert into " + TABLE_NAME + " (" +   
  100.                         ID + ", " + NAME+", " + ADDRESS+", " + PHONE+", "+AGE  
  101.                                         + ") values('" + String.valueOf(id) + "', 'man','address','123456789','18');";  
  102.                         try {  
  103.                                 db.execSQL(sql);  
  104.                         } catch (SQLException e) {  
  105.                         }  
  106.                 }  
  107.         }  
  108.           
  109.           
  110. }  

       分頁表格控件GVTable.java的源碼:

Java代碼
  1. package com.testSQLite;  
  2. import java.util.ArrayList;  
  3. import java.util.HashMap;  
  4. import android.content.Context;  
  5. import android.database.Cursor;  
  6. import android.database.sqlite.SQLiteDatabase;  
  7. import android.view.View;  
  8. import android.widget.AdapterView;  
  9. import android.widget.GridView;  
  10. import android.widget.LinearLayout;  
  11. import android.widget.SimpleAdapter;  
  12. import android.widget.AdapterView.OnItemClickListener;  
  13. public class GVTable extends LinearLayout {  
  14.         protected GridView gvTable,gvPage;          
  15.         protected SimpleAdapter saPageID,saTable;// 適配器  
  16.         protected ArrayList<HashMap<String, String>> srcPageID,srcTable;// 數據源  
  17.           
  18.         protected int TableRowCount=10;//分頁時,每頁的Row總數  
  19.         protected int TableColCount=0;//每頁col的數量  
  20.         protected SQLiteDatabase db;  
  21.         protected String rawSQL="";  
  22.         protected Cursor curTable;//分頁時使用的Cursor  
  23.         protected OnTableClickListener clickListener;//整個分頁控件被點擊時的回調函數  
  24.         protected OnPageSwitchListener switchListener;//分頁切換時的回調函數  
  25.           
  26.         public GVTable(Context context) {  
  27.                 super(context);  
  28.                 this.setOrientation(VERTICAL);//垂直  
  29.                 //----------------------------------------  
  30.                 gvTable=new GridView(context);  
  31.                 addView(gvTable, new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,  
  32.                 LayoutParams.WRAP_CONTENT));//寬長式樣  
  33.                   
  34.                 srcTable = new ArrayList<HashMap<String, String>>();  
  35.                 saTable = new SimpleAdapter(context,  
  36.                                 srcTable,// 數據來源  
  37.                                 R.layout.items,//XML實現  
  38.                                 new String[] { "ItemText" },// 動態數組與ImageItem對應的子項  
  39.                                 new int[] { R.id.ItemText });  
  40.                 // 添加並且顯示  
  41.                 gvTable.setAdapter(saTable);  
  42.                 gvTable.setOnItemClickListener(new OnItemClickListener(){  
  43.                         @Override  
  44.                         public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,  
  45.                                         long arg3) {  
  46.                                 int y=arg2/curTable.getColumnCount()-1;//標題欄的不算  
  47.                                 int x=arg2 % curTable.getColumnCount();  
  48.                                 if (clickListener != null//分頁數據被點擊  
  49.                                                 && y!=-1) {//點中的不是標題欄時  
  50.                                         clickListener.onTableClickListener(x,y,curTable);  
  51.                                 }  
  52.                         }  
  53.                 });  
  54.                   
  55.                 //----------------------------------------  
  56.                 gvPage=new GridView(context);  
  57.                 gvPage.setColumnWidth(40);//設置每個分頁按鈕的寬度  
  58.                 gvPage.setNumColumns(GridView.AUTO_FIT);//分頁按鈕數量自動設置  
  59.                 addView(gvPage, new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,  
  60.                 LayoutParams.WRAP_CONTENT));//寬長式樣  
  61.                 srcPageID = new ArrayList<HashMap<String, String>>();  
  62.                 saPageID = new SimpleAdapter(context,  
  63.                                 srcPageID,// 數據來源  
  64.                                 R.layout.items,//XML實現  
  65.                                 new String[] { "ItemText" },// 動態數組與ImageItem對應的子項  
  66.                                 new int[] { R.id.ItemText });  
  67.                 // 添加並且顯示  
  68.                 gvPage.setAdapter(saPageID);  
  69.                 // 添加消息處理  
  70.                 gvPage.setOnItemClickListener(new OnItemClickListener(){  
  71.                         @Override  
  72.                         public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,  
  73.                                         long arg3) {  
  74.                                 LoadTable(arg2);//根據所選分頁讀取對應的數據  
  75.                             if(switchListener!=null){//分頁切換時  
  76.                                     switchListener.onPageSwitchListener(arg2,srcPageID.size());  
  77.                             }  
  78.                         }  
  79.             });  
  80.         }  
  81.         /** 
  82.          * 清除所有數據 
  83.          */  
  84.         public void gvRemoveAll()  
  85.         {  
  86.                 if(this.curTable!=null)  
  87.                         curTable.close();  
  88.                 srcTable.clear();  
  89.                 saTable.notifyDataSetChanged();  
  90.           
  91.                 srcPageID.clear();  
  92.                 saPageID.notifyDataSetChanged();  
  93.                   
  94.         }  
  95.         /** 
  96.          * 讀取指定ID的分頁數據,返回當前頁的總數據 
  97.          * SQL:Select * From TABLE_NAME Limit 9 Offset 10; 
  98.          * 表示從TABLE_NAME表獲取數據,跳過10行,取9行 
  99.          * @param pageID 指定的分頁ID 
  100.          */  
  101.         protected void LoadTable(int pageID)  
  102.         {  
  103.                 if(curTable!=null)//釋放上次的數據  
  104.                         curTable.close();  
  105.                   
  106.             String sql= rawSQL+" Limit "+String.valueOf(TableRowCount)+ " Offset " +String.valueOf(pageID*TableRowCount);  
  107.             curTable = db.rawQuery(sql, null);  
  108.               
  109.             gvTable.setNumColumns(curTable.getColumnCount());//表現為表格的關鍵點!  
  110.             TableColCount=curTable.getColumnCount();  
  111.             srcTable.clear();  
  112.             // 取得字段名稱  
  113.             int colCount = curTable.getColumnCount();  
  114.                 for (int i = 0; i < colCount; i++) {  
  115.                         HashMap<String, String> map = new HashMap<String, String>();  
  116.                         map.put("ItemText", curTable.getColumnName(i));  
  117.                         srcTable.add(map);  
  118.                 }  
  119.                   
  120.                 // 列舉出所有數據  
  121.                 int recCount=curTable.getCount();  
  122.                 for (int i = 0; i < recCount; i++) {//定位到一條數據  
  123.                         curTable.moveToPosition(i);  
  124.                         for(int ii=0;ii<colCount;ii++)//定位到一條數據中的每個字段  
  125.                         {  
  126.                                 HashMap<String, String> map = new HashMap<String, String>();  
  127.                                 map.put("ItemText", curTable.getString(ii));  
  128.                                 srcTable.add(map);  
  129.                         }  
  130.                 }  
  131.                   
  132.                 saTable.notifyDataSetChanged();  
  133.         }  
  134.         /** 
  135.          * 設置表格的最多顯示的行數 
  136.          * @param row 表格的行數 
  137.          */  
  138.         public void gvSetTableRowCount(int row)  
  139.         {  
  140.                 TableRowCount=row;  
  141.         }  
  142.           
  143.         /** 
  144.          * 取得表格的最大行數         
  145.          * @return 行數 
  146.          */  
  147.         public int gvGetTableRowCount()  
  148.         {  
  149.                 return TableRowCount;  
  150.         }  
  151.           
  152.         /** 
  153.          * 取得當前分頁的Cursor 
  154.          * @return 當前分頁的Cursor 
  155.          */  
  156.         public Cursor gvGetCurrentTable()  
  157.         {  
  158.                 return curTable;  
  159.         }  
  160.                   
  161.         /** 
  162.          * 准備分頁顯示數據 
  163.          * @param rawSQL sql語句 
  164.          * @param db 數據庫 
  165.          */  
  166.         public void gvReadyTable(String rawSQL,SQLiteDatabase db)  
  167.         {  
  168.                 this.rawSQL=rawSQL;  
  169.                 this.db=db;  
  170.         }  
  171.           
  172.         /** 
  173.          * 刷新分頁欄,更新按鈕數量 
  174.          * @param sql SQL語句 
  175.          * @param db 數據庫 
  176.          */  
  177.         public void gvUpdatePageBar(String sql,SQLiteDatabase db)  
  178.         {  
  179.                 Cursor rec = db.rawQuery(sql, null);  
  180.                 rec.moveToLast();  
  181.                 long recSize=rec.getLong(0);//取得總數  
  182.                 rec.close();  
  183.                 int pageNum=(int)(recSize/TableRowCount) + 1;//取得分頁數  
  184.                   
  185.                 srcPageID.clear();  
  186.                 for (int i = 0; i < pageNum; i++) {  
  187.                         HashMap<String, String> map = new HashMap<String, String>();  
  188.                         map.put("ItemText", "No." + String.valueOf(i));// 添加圖像資源的ID  
  189.                         srcPageID.add(map);  
  190.                 }  
  191.                 saPageID.notifyDataSetChanged();  
  192.         }  
  193.         //---------------------------------------------------------  
  194.         /** 
  195.          * 表格被點擊時的回調函數 
  196.          */  
  197.         public void setTableOnClickListener(OnTableClickListener click) {  
  198.                 this.clickListener = click;  
  199.         }  
  200.           
  201.         public interface OnTableClickListener {  
  202.                 public void onTableClickListener(int x,int y,Cursor c);  
  203.         }  
  204.         //---------------------------------------------------------  
  205.         /** 
  206.          * 分頁欄被點擊時的回調函數 
  207.          */  
  208.         public void setOnPageSwitchListener(OnPageSwitchListener pageSwitch) {  
  209.                 this.switchListener = pageSwitch;  
  210.         }  
  211.         public interface OnPageSwitchListener {  
  212.                 public void onPageSwitchListener(int pageID,int pageCount);  
  213.         }  
  214. }  
  1. 上一頁:
  2. 下一頁:
熱門文章
閱讀排行版
Copyright © Android教程網 All Rights Reserved