Android提高之SQLite分頁表格

2011-11-26 12:40:46

來源：網(wǎng)絡(luò)

點(diǎn)擊：3050

A⁺ A^-

關(guān)鍵詞： Android,SQLite分頁表格

本文實(shí)現(xiàn)并封裝一個SQL分頁表格控件，不僅支持分頁還是以表格的形式展示數(shù)據(jù)。先來看看本文程序運(yùn)行的動畫：

這個SQL分頁表格控件主要分為“表格區(qū)”和“分頁欄”這兩部分，這兩部分都是基于GridView實(shí)現(xiàn)的。網(wǎng)上介紹Android上實(shí)現(xiàn)表格的DEMO一般都用ListView。ListView與GridView對比，ListView最大的優(yōu)勢是格單元的大小可以自定義，可以某單元長某單元短，但是不能自適應(yīng)數(shù)據(jù)表的結(jié)構(gòu)；而GridView最大的優(yōu)勢就是自適應(yīng)數(shù)據(jù)表的結(jié)構(gòu)，但是格單元統(tǒng)一大小。。。對于數(shù)據(jù)表結(jié)構(gòu)多變的情況，建議使用GridView實(shí)現(xiàn)表格。

本文實(shí)現(xiàn)的SQL分頁表格控件有以下特點(diǎn)：

1.自適應(yīng)數(shù)據(jù)表結(jié)構(gòu)，但是格單元統(tǒng)一大??；

2.支持分頁；

3.“表格區(qū)”有按鍵事件回調(diào)處理，“分頁欄”有分頁切換事件回調(diào)處理。

本文程序代碼較多，可以到這里下載整個工程的源碼：http://www.rayfile.com/files/72e78b68-f2e5-11df-8469-0015c55db73d/

items.xml的代碼如下，它是“表格區(qū)”和“分頁欄”的格單元實(shí)現(xiàn)：

view plaincopy to clipboardprint?
<?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>
<?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的代碼如下：

view plaincopy to clipboardprint?
<?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="創(chuàng)建數(shù)據(jù)庫"></Button>
    <Button android:layout_height="wrap_content"
        android:layout_width="fill_parent" android:text="插入一串實(shí)驗數(shù)據(jù)" android:id="@+id/btnInsertRec"></Button>
    <Button android:layout_height="wrap_content" android:id="@+id/btnClose"
        android:text="關(guān)閉數(shù)據(jù)庫" android:layout_width="fill_parent"></Button>
</LinearLayout>
<?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">

演示程序testSQLite.java的源碼：

view plaincopy to clipboardprint?
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累加標(biāo)記，必須全局

    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";

    @Override
    public 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);//設(shè)置每個分頁的ROW總數(shù)
        LinearLayout ly = (LinearLayout) findViewById(R.id.MainLinearLayout);

        table.setTableOnClickListener(new GVTable.OnTableClickListener() {
            @Override
            public 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() {

            @Override
            public void onPageSwitchListener(int pageID,int pageCount) {
                String str="共有"+String.valueOf(pageCount)+
                " 當(dāng)前第"+String.valueOf(pageID)+"頁";
                Toast.makeText(testSQLite.this, str, 1000).show();
            }
        });

        ly.addView(table);
    }

    class ClickEvent implements View.OnClickListener {

        @Override
        public 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();

            }
        }
    }

    /**
     * 在內(nèi)存創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表
     */
    void CreateDB() {
        // 在內(nèi)存創(chuàng)建數(shù)據(jù)庫
        db = SQLiteDatabase.create(null);
        Log.e("DB Path", db.getPath());
        String amount = String.valueOf(databaseList().length);
        Log.e("DB amount", amount);
        // 創(chuàng)建數(shù)據(jù)表
        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條數(shù)據(jù)
     */
    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) {
            }
        }
    }



}
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累加標(biāo)記，必須全局

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";

@Override
public 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);//設(shè)置每個分頁的ROW總數(shù)
  LinearLayout ly = (LinearLayout) findViewById(R.id.MainLinearLayout);

  table.setTableOnClickListener(new GVTable.OnTableClickListener() {
   @Override
   public 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();
   }

class ClickEvent implements View.OnClickListener {

  @Override
  public 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();

   }
  }
}

/**
* 在內(nèi)存創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表
*/
void CreateDB() {
  // 在內(nèi)存創(chuàng)建數(shù)據(jù)庫
  db = SQLiteDatabase.create(null);
  Log.e("DB Path", db.getPath());
  String amount = String.valueOf(databaseList().length);
  Log.e("DB amount", amount);
  // 創(chuàng)建數(shù)據(jù)表
  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條數(shù)據(jù)
*/
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?
package com.testSQLite;

import java.util.ArrayList;
import java.util.HashMap;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.View;
import android.widget.AdapterView;
import android.widget.GridView;
import android.widget.LinearLayout;
import android.widget.SimpleAdapter;
import android.widget.AdapterView.OnItemClickListener;

public class GVTable extends LinearLayout {
    protected GridView gvTable,gvPage;
    protected SimpleAdapter saPageID,saTable;// 適配器
    protected ArrayList<HashMap<String, String>> srcPageID,srcTable;// 數(shù)據(jù)源

    protected int TableRowCount=10;//分頁時，每頁的Row總數(shù)
    protected int TableColCount=0;//每頁col的數(shù)量

    protected SQLiteDatabase db;
    protected String rawSQL="";
    protected Cursor curTable;//分頁時使用的Cursor
    protected OnTableClickListener clickListener;//整個分頁控件被點(diǎn)擊時的回調(diào)函數(shù)
    protected OnPageSwitchListener switchListener;//分頁切換時的回調(diào)函數(shù)

    public GVTable(Context context) {
        super(context);
        this.setOrientation(VERTICAL);//垂直
        //----------------------------------------
        gvTable=new GridView(context);
        addView(gvTable, new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,
                LayoutParams.WRAP_CONTENT));//寬長式樣

        srcTable = new ArrayList<HashMap<String, String>>();
        saTable = new SimpleAdapter(context,
                srcTable,// 數(shù)據(jù)來源
                R.layout.items,//XML實(shí)現(xiàn)
                new String[] { "ItemText" },// 動態(tài)數(shù)組與ImageItem對應(yīng)的子項
                new int[] { R.id.ItemText });
        // 添加并且顯示
        gvTable.setAdapter(saTable);
        gvTable.setOnItemClickListener(new OnItemClickListener(){
            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
                    long arg3) {
                int y=arg2/curTable.getColumnCount()-1;//標(biāo)題欄的不算
                int x=arg2 % curTable.getColumnCount();
                if (clickListener != null//分頁數(shù)據(jù)被點(diǎn)擊
                        && y!=-1) {//點(diǎn)中的不是標(biāo)題欄時
                    clickListener.onTableClickListener(x,y,curTable);
                }
            }
        });

        //----------------------------------------
        gvPage=new GridView(context);
        gvPage.setColumnWidth(40);//設(shè)置每個分頁按鈕的寬度
        gvPage.setNumColumns(GridView.AUTO_FIT);//分頁按鈕數(shù)量自動設(shè)置
        addView(gvPage, new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,
                LayoutParams.WRAP_CONTENT));//寬長式樣
        srcPageID = new ArrayList<HashMap<String, String>>();
        saPageID = new SimpleAdapter(context,
                srcPageID,// 數(shù)據(jù)來源
                R.layout.items,//XML實(shí)現(xiàn)
                new String[] { "ItemText" },// 動態(tài)數(shù)組與ImageItem對應(yīng)的子項
                new int[] { R.id.ItemText });

        // 添加并且顯示
        gvPage.setAdapter(saPageID);
        // 添加消息處理
        gvPage.setOnItemClickListener(new OnItemClickListener(){
            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
                    long arg3) {
                LoadTable(arg2);//根據(jù)所選分頁讀取對應(yīng)的數(shù)據(jù)
                if(switchListener!=null){//分頁切換時
                    switchListener.onPageSwitchListener(arg2,srcPageID.size());
                }
            }
        });
    }

    /**
     * 清除所有數(shù)據(jù)
     */
    public void gvRemoveAll()
    {
        if(this.curTable!=null)
            curTable.close();
        srcTable.clear();
        saTable.notifyDataSetChanged();

        srcPageID.clear();
        saPageID.notifyDataSetChanged();

    }
    /**
     * 讀取指定ID的分頁數(shù)據(jù),返回當(dāng)前頁的總數(shù)據(jù)
     * SQL:Select * From TABLE_NAME Limit 9 Offset 10;
     * 表示從TABLE_NAME表獲取數(shù)據(jù)，跳過10行，取9行
     * @param pageID 指定的分頁ID
     */
    protected void LoadTable(int pageID)
    {
        if(curTable!=null)//釋放上次的數(shù)據(jù)
            curTable.close();

        String sql= rawSQL+" Limit "+String.valueOf(TableRowCount)+ " Offset " +String.valueOf(pageID*TableRowCount);
        curTable = db.rawQuery(sql, null);

        gvTable.setNumColumns(curTable.getColumnCount());//表現(xiàn)為表格的關(guān)鍵點(diǎn)！
        TableColCount=curTable.getColumnCount();
        srcTable.clear();
        // 取得字段名稱
        int colCount = curTable.getColumnCount();
        for (int i = 0; i < colCount; i++) {
            HashMap<String, String> map = new HashMap<String, String>();
            map.put("ItemText", curTable.getColumnName(i));
            srcTable.add(map);
        }

        // 列舉出所有數(shù)據(jù)
        int recCount=curTable.getCount();
        for (int i = 0; i < recCount; i++) {//定位到一條數(shù)據(jù)
            curTable.moveToPosition(i);
            for(int ii=0;ii<colCount;ii++)//定位到一條數(shù)據(jù)中的每個字段
            {
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("ItemText", curTable.getString(ii));
                srcTable.add(map);
            }
        }

        saTable.notifyDataSetChanged();
    }

    /**
     * 設(shè)置表格的最多顯示的行數(shù)
     * @param row 表格的行數(shù)
     */
    public void gvSetTableRowCount(int row)
    {
        TableRowCount=row;
    }

    /**
     * 取得表格的最大行數(shù)
     * @return 行數(shù)
     */
    public int gvGetTableRowCount()
    {
        return TableRowCount;
    }

    /**
     * 取得當(dāng)前分頁的Cursor
     * @return 當(dāng)前分頁的Cursor
     */
    public Cursor gvGetCurrentTable()
    {
        return curTable;
    }

    /**
     * 準(zhǔn)備分頁顯示數(shù)據(jù)
     * @param rawSQL sql語句
     * @param db 數(shù)據(jù)庫
     */
    public void gvReadyTable(String rawSQL,SQLiteDatabase db)
    {
        this.rawSQL=rawSQL;
        this.db=db;
    }

    /**
     * 刷新分頁欄，更新按鈕數(shù)量
     * @param sql SQL語句
     * @param db 數(shù)據(jù)庫
     */
    public void gvUpdatePageBar(String sql,SQLiteDatabase db)
    {
        Cursor rec = db.rawQuery(sql, null);
        rec.moveToLast();
        long recSize=rec.getLong(0);//取得總數(shù)
        rec.close();
        int pageNum=(int)(recSize/TableRowCount) + 1;//取得分頁數(shù)

        srcPageID.clear();
        for (int i = 0; i < pageNum; i++) {
            HashMap<String, String> map = new HashMap<String, String>();
            map.put("ItemText", "No." + String.valueOf(i));// 添加圖像資源的ID

            srcPageID.add(map);
        }
        saPageID.notifyDataSetChanged();
    }

    //---------------------------------------------------------
    /**
     * 表格被點(diǎn)擊時的回調(diào)函數(shù)
     */
    public void setTableOnClickListener(OnTableClickListener click) {
        this.clickListener = click;
    }

    public interface OnTableClickListener {
        public void onTableClickListener(int x,int y,Cursor c);
    }
    //---------------------------------------------------------
    /**
     * 分頁欄被點(diǎn)擊時的回調(diào)函數(shù)
     */
    public void setOnPageSwitchListener(OnPageSwitchListener pageSwitch) {
        this.switchListener = pageSwitch;
    }
    public interface OnPageSwitchListener {
        public void onPageSwitchListener(int pageID,int pageCount);
    }
}

(審核編輯: 智匯小新)

聲明：除特別說明之外，新聞內(nèi)容及圖片均來自網(wǎng)絡(luò)及各大主流媒體。版權(quán)歸原作者所有。如認(rèn)為內(nèi)容侵權(quán)，請聯(lián)系我們刪除。

分享

日韩中文字幕色资源,337p日本欧洲中国大胆精品,亚洲专区视频一区二区,91大神视频在线观看,亚洲av日韩精品久久国产多毛女,日韩一区二区三在线视频,av网站免费在线不卡,黄色国产午夜精品久久久,北条av在线一二三

Android提高之SQLite分頁表格

點(diǎn)擊排行

專題