Android Improved SQLite paging table implementation _android

Following the previous article about SQLite paging read on Android, the function is only to display data in a text box. This article is a bit more in-depth, implementing and encapsulating a SQL paging table control that supports not only paging, but also tabular presentation of data.

Let's take a look at the animation that this program runs as shown in the following illustration:

This SQL paging table control is mainly divided into "table area" and "Page Bar", both of which are based on the GridView implementation. Online Introduction to the implementation of the form on Android demo is generally used ListView. ListView compared with the GridView, the biggest advantage of ListView is that the size of the lattice unit can be customized, the cell length can be short, but it is difficult to realize the structure of the adaptive data table, but the best advantage of the GridView is the Adaptive data table structure, but the uniform size of the lattice unit. It is recommended that the GridView Implementation table be used for the changeable data table structure.

The SQL Paging table control implemented in this article has the following characteristics:

1. Adaptive data table structure, but the uniform size of lattice units;

2. Support Paging;

3. "Table area" has key event callback processing, "page bar" has paging handover event callback processing.

This article program code more, you can download the entire project here Source: Http://xiazai.jb51.net/201408/yuanma/testSQLite (Jb51.bet). rar

The code for Items.xml is as follows: It is the grid unit implementation of the table area and the page-bar.

<?xml version= "1.0" encoding= "Utf-8"?> <linearlayout "android:id="
 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>

The code for Main.xml is as follows:

<?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= "CREATE DATABASE" ></Button>
 <button android:layout_height= "wrap_content"
 android:layout_width= "Fill_parent" Insert a string of experimental data "android:id=" @+id/btninsertrec ></Button>
 <button android:layout_height= "Wrap_content" Android:id= "@+id/btnclose"
 android:text= "close database" android:layout_width= "Fill_parent" ></Button>
</LinearLayout>

Demo program Testsqlite.java source code is as follows:

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; // The id accumulation mark when adding records must be global
 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); // Set the total number of ROWs per page
 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) + "Position: (" + 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 = "total" + String.valueOf (pageCount) +
  "Current page" + String.valueOf (pageID) + "Page";
  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); // Insert 16 records
  table.gvUpdatePageBar ("select count (*) from" + TABLE_NAME, db);
  table.gvReadyTable ("select * from" + TABLE_NAME, db);
  } else if (v == btnClose) {
  table.gvRemoveAll ();
  db.close ();
  
  }
 }
 }
 
 / **
 * Create database and data table in memory
 * /
 void CreateDB () {
 // Create database in memory
 db = SQLiteDatabase.create (null);
 Log.e ("DB Path", db.getPath ());
 String amount = String.valueOf (databaseList (). Length);
 Log.e ("DB amount", amount);
 // create data table
 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) {}
 }
 / **
 * Insert N pieces of data
 * /
 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) {
  }
 }
 }
}

The source code for the Paging Table control Gvtable.java is as follows:

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; // adapter
 protected ArrayList <HashMap <String, String >> srcPageID, srcTable; // data source
 
 protected int TableRowCount = 10; // When pagination, the total number of Rows per page
 protected int TableColCount = 0; // Number of col per page
 protected SQLiteDatabase db;
 protected String rawSQL = "";
 protected Cursor curTable; // Cursor used when pagination
 protected OnTableClickListener clickListener; // Callback function when the entire paging control is clicked
 protected OnPageSwitchListener switchListener; // Callback function for paging switch
 
 public GVTable (Context context) {
 super (context);
 this.setOrientation (VERTICAL); // vertical
 // ----------------------------------------
 gvTable = new GridView (context);
 addView (gvTable, new LinearLayout.LayoutParams (LayoutParams.FILL_PARENT,
        LayoutParams.WRAP_CONTENT)); // Width and length style
 
 srcTable = new ArrayList <HashMap <String, String >> ();
 saTable = new SimpleAdapter (context,
  srcTable, // data source
  R.layout.items, // XML implementation
  new String [] {"ItemText"}, // The children of the dynamic array corresponding to ImageItem
  new int [] {R.id.ItemText});
 // add and display
 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; // The title bar is not counted
  int x = arg2% curTable.getColumnCount ();
  if (clickListener! = null // paginated data is clicked
   && y! =-1) {// When the dot is not the title bar
   clickListener.onTableClickListener (x, y, curTable);
  }
  }
 });
 // ----------------------------------------
 gvPage = new GridView (context);
 gvPage.setColumnWidth (40); // Set the width of each page button
 gvPage.setNumColumns (GridView.AUTO_FIT); // The number of paging buttons is automatically set
 addView (gvPage, new LinearLayout.LayoutParams (LayoutParams.FILL_PARENT,
        LayoutParams.WRAP_CONTENT)); // Width and length style
 srcPageID = new ArrayList <HashMap <String, String >> ();
 saPageID = new SimpleAdapter (context,
  srcPageID, // data source
  R.layout.items, // XML implementation
  new String [] {"ItemText"}, // The children of the dynamic array corresponding to ImageItem
  new int [] {R.id.ItemText});
 // add and display
 gvPage.setAdapter (saPageID);
 // Add message processing
 gvPage.setOnItemClickListener (new OnItemClickListener () {
  @Override
  public void onItemClick (AdapterView <?> arg0, View arg1, int arg2,
   long arg3) {
  LoadTable (arg2); // Read the corresponding data according to the selected page
    if (switchListener! = null) {// When pagination switching
    switchListener.onPageSwitchListener (arg2, srcPageID.size ());
    }
  }
   });
 }
 / **
 * Clear all data
 * /
 public void gvRemoveAll ()
 {
 if (this.curTable! = null)
  curTable.close ();
 srcTable.clear ();
 saTable.notifyDataSetChanged ();
 
 srcPageID.clear ();
 saPageID.notifyDataSetChanged ();
 }
 / **
 * Read the paging data of the specified ID and return the total data of the current page
 * SQL: Select * From TABLE_NAME Limit 9 Offset 10;
 * Means to get data from the TABLE_NAME table, skip 10 rows, fetch 9 rows
 * @param pageID specified page ID
 * /
 protected void LoadTable (int pageID)
 {
 if (curTable! = null) // Release the last data
  curTable.close ();
 
   String sql = rawSQL + "Limit" + String.valueOf (TableRowCount) + "Offset" + String.valueOf (pageID * TableRowCount);
   curTable = db.rawQuery (sql, null);
   
   gvTable.setNumColumns (curTable.getColumnCount ()); // The key point of the table!
   TableColCount = curTable.getColumnCount ();
   srcTable.clear ();
   // get field name
   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);
 }
 // List all data
 int recCount = curTable.getCount ();
 for (int i = 0; i <recCount; i ++) {// locate to a piece of data
  curTable.moveToPosition (i);
  for (int ii = 0; ii <colCount; ii ++) // To locate each field in a piece of data
  {
  HashMap <String, String> map = new HashMap <String, String> ();
  map.put ("ItemText", curTable.getString (ii));
  srcTable.add (map);
  }
 }
 saTable.notifyDataSetChanged ();
 }
 / **
 * Set the maximum number of rows displayed in the table
 * @param row number of rows in the table
 * /
 public void gvSetTableRowCount (int row)
 {
 TableRowCount = row;
 }
 
 / **
 * Get the maximum number of rows in the table
 * @return lines
 * /
 public int gvGetTableRowCount ()
 {
 return TableRowCount;
 }
 / **
 * Get Cursor for current page
 * @return Cursor of the current page
 * /
 public Cursor gvGetCurrentTable ()
 {
 return curTable;
 }
 / **
 * Prepare to display data by pagination
 * @param rawSQL sql statement
 * @param db database
 * /
 public void gvReadyTable (String rawSQL, SQLiteDatabase db)
 {
 this.rawSQL = rawSQL;
 this.db = db;
 }
 / **
 * Refresh the paging bar and update the number of buttons
 * @param sql SQL statement
 * @param db database
 * /
 public void gvUpdatePageBar (String sql, SQLiteDatabase db)
 {
 Cursor rec = db.rawQuery (sql, null);
 rec.moveToLast ();
 long recSize = rec.getLong (0); // Get the total
 rec.close ();
 int pageNum = (int) (recSize / TableRowCount) + 1; // Get the number of pages
 
 srcPageID.clear ();
 for (int i = 0; i <pageNum; i ++) {
  HashMap <String, String> map = new HashMap <String, String> ();
  map.put ("ItemText", "No." + String.valueOf (i)); // Add the ID of the image resource
  srcPageID.add (map);
}
  saPageID.notifyDataSetChanged ();
  }
  // ------------------------------------------------ ---------
  / **
  * Callback function when the table is clicked
  * /
  public void setTableOnClickListener (OnTableClickListener click) {
  this.clickListener = click;
  }
 
  public interface OnTableClickListener {
  public void onTableClickListener (int x, int y, Cursor c);
  }
  // ------------------------------------------------ ---------
  / **
  * Callback function when pagination bar is clicked
  * /
  public void setOnPageSwitchListener (OnPageSwitchListener pageSwitch) {
  this.switchListener = pageSwitch;
  }
  public interface OnPageSwitchListener {
  public void onPageSwitchListener (int pageID, int pageCount);
  }
}

I hope the examples described in this article will be useful for the development of Android projects.