fermyth

Database SQLite Android

Jul 12th, 2011
292
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // property of randall mitchell
  2. // http://www.anotherandroidblog.com/2010/08/04/android-database-tutorial/4/
  3.  
  4. package com.anotherandroidblog.tools.database;
  5.  
  6. import java.util.ArrayList;
  7.  
  8. import android.content.ContentValues;
  9. import android.content.Context;
  10. import android.database.Cursor;
  11. import android.database.SQLException;
  12. import android.database.sqlite.SQLiteDatabase;
  13. import android.database.sqlite.SQLiteOpenHelper;
  14. import android.util.Log;
  15.  
  16. public class AABDatabaseManager
  17. {
  18.     // the Activity or Application that is creating an object from this class.
  19.     Context context;
  20.  
  21.     // a reference to the database used by this application/object
  22.     private SQLiteDatabase db;
  23.  
  24.     // These constants are specific to the database.  They should be
  25.     // changed to suit your needs.
  26.     private final String DB_NAME = "database_name";
  27.     private final int DB_VERSION = 1;
  28.  
  29.     // These constants are specific to the database table.  They should be
  30.     // changed to suit your needs.
  31.     private final String TABLE_NAME = "database_table";
  32.     private final String TABLE_ROW_ID = "id";
  33.     private final String TABLE_ROW_ONE = "table_row_one";
  34.     private final String TABLE_ROW_TWO = "table_row_two";
  35.  
  36.     public AABDatabaseManager(Context context)
  37.     {
  38.         this.context = context;
  39.  
  40.         // create or open the database
  41.         CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
  42.         this.db = helper.getWritableDatabase();
  43.     }
  44.  
  45.  
  46.  
  47.  
  48.     /**********************************************************************
  49.      * ADDING A ROW TO THE DATABASE TABLE
  50.      *
  51.      * This is an example of how to add a row to a database table
  52.      * using this class.  You should edit this method to suit your
  53.      * needs.
  54.      *
  55.      * the key is automatically assigned by the database
  56.      * @param rowStringOne the value for the row's first column
  57.      * @param rowStringTwo the value for the row's second column
  58.      */
  59.     public void addRow(String rowStringOne, String rowStringTwo)
  60.     {
  61.         // this is a key value pair holder used by android's SQLite functions
  62.         ContentValues values = new ContentValues();
  63.         values.put(TABLE_ROW_ONE, rowStringOne);
  64.         values.put(TABLE_ROW_TWO, rowStringTwo);
  65.  
  66.         // ask the database object to insert the new data
  67.         try{db.insert(TABLE_NAME, null, values);}
  68.         catch(Exception e)
  69.         {
  70.             Log.e("DB ERROR", e.toString());
  71.             e.printStackTrace();
  72.         }
  73.     }
  74.  
  75.  
  76.  
  77.     /**********************************************************************
  78.      * DELETING A ROW FROM THE DATABASE TABLE
  79.      *
  80.      * This is an example of how to delete a row from a database table
  81.      * using this class. In most cases, this method probably does
  82.      * not need to be rewritten.
  83.      *
  84.      * @param rowID the SQLite database identifier for the row to delete.
  85.      */
  86.     public void deleteRow(long rowID)
  87.     {
  88.         // ask the database manager to delete the row of given id
  89.         try {db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);}
  90.         catch (Exception e)
  91.         {
  92.             Log.e("DB ERROR", e.toString());
  93.             e.printStackTrace();
  94.         }
  95.     }
  96.  
  97.     /**********************************************************************
  98.      * UPDATING A ROW IN THE DATABASE TABLE
  99.      *
  100.      * This is an example of how to update a row in the database table
  101.      * using this class.  You should edit this method to suit your needs.
  102.      *
  103.      * @param rowID the SQLite database identifier for the row to update.
  104.      * @param rowStringOne the new value for the row's first column
  105.      * @param rowStringTwo the new value for the row's second column
  106.      */
  107.     public void updateRow(long rowID, String rowStringOne, String rowStringTwo)
  108.     {
  109.         // this is a key value pair holder used by android's SQLite functions
  110.         ContentValues values = new ContentValues();
  111.         values.put(TABLE_ROW_ONE, rowStringOne);
  112.         values.put(TABLE_ROW_TWO, rowStringTwo);
  113.  
  114.         // ask the database object to update the database row of given rowID
  115.         try {db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);}
  116.         catch (Exception e)
  117.         {
  118.             Log.e("DB Error", e.toString());
  119.             e.printStackTrace();
  120.         }
  121.     }
  122.  
  123.     /**********************************************************************
  124.      * RETRIEVING A ROW FROM THE DATABASE TABLE
  125.      *
  126.      * This is an example of how to retrieve a row from a database table
  127.      * using this class.  You should edit this method to suit your needs.
  128.      *
  129.      * @param rowID the id of the row to retrieve
  130.      * @return an array containing the data from the row
  131.      */
  132.     public ArrayList<Object> getRowAsArray(long rowID)
  133.     {
  134.         // create an array list to store data from the database row.
  135.         // I would recommend creating a JavaBean compliant object
  136.         // to store this data instead.  That way you can ensure
  137.         // data types are correct.
  138.         ArrayList<Object> rowArray = new ArrayList<Object>();
  139.         Cursor cursor;
  140.  
  141.         try
  142.         {
  143.             // this is a database call that creates a "cursor" object.
  144.             // the cursor object store the information collected from the
  145.             // database and is used to iterate through the data.
  146.             cursor = db.query
  147.             (
  148.                     TABLE_NAME,
  149.                     new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
  150.                     TABLE_ROW_ID + "=" + rowID,
  151.                     null, null, null, null, null
  152.             );
  153.  
  154.             // move the pointer to position zero in the cursor.
  155.             cursor.moveToFirst();
  156.  
  157.             // if there is data available after the cursor's pointer, add
  158.             // it to the ArrayList that will be returned by the method.
  159.             if (!cursor.isAfterLast())
  160.             {
  161.                 do
  162.                 {
  163.                     rowArray.add(cursor.getLong(0));
  164.                     rowArray.add(cursor.getString(1));
  165.                     rowArray.add(cursor.getString(2));
  166.                 }
  167.                 while (cursor.moveToNext());
  168.             }
  169.  
  170.             // let java know that you are through with the cursor.
  171.             cursor.close();
  172.         }
  173.         catch (SQLException e)
  174.         {
  175.             Log.e("DB ERROR", e.toString());
  176.             e.printStackTrace();
  177.         }
  178.  
  179.         // return the ArrayList containing the given row from the database.
  180.         return rowArray;
  181.     }
  182.  
  183.  
  184.  
  185.  
  186.     /**********************************************************************
  187.      * RETRIEVING ALL ROWS FROM THE DATABASE TABLE
  188.      *
  189.      * This is an example of how to retrieve all data from a database
  190.      * table using this class.  You should edit this method to suit your
  191.      * needs.
  192.      *
  193.      * the key is automatically assigned by the database
  194.      */
  195.  
  196.     public ArrayList<ArrayList<Object>> getAllRowsAsArrays()
  197.     {
  198.         // create an ArrayList that will hold all of the data collected from
  199.         // the database.
  200.         ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
  201.  
  202.         // this is a database call that creates a "cursor" object.
  203.         // the cursor object store the information collected from the
  204.         // database and is used to iterate through the data.
  205.         Cursor cursor;
  206.  
  207.         try
  208.         {
  209.             // ask the database object to create the cursor.
  210.             cursor = db.query(
  211.                     TABLE_NAME,
  212.                     new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
  213.                     null, null, null, null, null
  214.             );
  215.  
  216.             // move the cursor's pointer to position zero.
  217.             cursor.moveToFirst();
  218.  
  219.             // if there is data after the current cursor position, add it
  220.             // to the ArrayList.
  221.             if (!cursor.isAfterLast())
  222.             {
  223.                 do
  224.                 {
  225.                     ArrayList<Object> dataList = new ArrayList<Object>();
  226.  
  227.                     dataList.add(cursor.getLong(0));
  228.                     dataList.add(cursor.getString(1));
  229.                     dataList.add(cursor.getString(2));
  230.  
  231.                     dataArrays.add(dataList);
  232.                 }
  233.                 // move the cursor's pointer up one position.
  234.                 while (cursor.moveToNext());
  235.             }
  236.         }
  237.         catch (SQLException e)
  238.         {
  239.             Log.e("DB Error", e.toString());
  240.             e.printStackTrace();
  241.         }
  242.  
  243.         // return the ArrayList that holds the data collected from
  244.         // the database.
  245.         return dataArrays;
  246.     }
  247.  
  248.  
  249.  
  250.  
  251.     /**********************************************************************
  252.      * THIS IS THE BEGINNING OF THE INTERNAL SQLiteOpenHelper SUBCLASS.
  253.      *
  254.      * I MADE THIS CLASS INTERNAL SO I CAN COPY A SINGLE FILE TO NEW APPS
  255.      * AND MODIFYING IT - ACHIEVING DATABASE FUNCTIONALITY.  ALSO, THIS WAY
  256.      * I DO NOT HAVE TO SHARE CONSTANTS BETWEEN TWO FILES AND CAN
  257.      * INSTEAD MAKE THEM PRIVATE AND/OR NON-STATIC.  HOWEVER, I THINK THE
  258.      * INDUSTRY STANDARD IS TO KEEP THIS CLASS IN A SEPARATE FILE.
  259.      *********************************************************************/
  260.  
  261.     /**
  262.      * This class is designed to check if there is a database that currently
  263.      * exists for the given program.  If the database does not exist, it creates
  264.      * one.  After the class ensures that the database exists, this class
  265.      * will open the database for use.  Most of this functionality will be
  266.      * handled by the SQLiteOpenHelper parent class.  The purpose of extending
  267.      * this class is to tell the class how to create (or update) the database.
  268.      *
  269.      * @author Randall Mitchell
  270.      *
  271.      */
  272.     private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
  273.     {
  274.         public CustomSQLiteOpenHelper(Context context)
  275.         {
  276.             super(context, DB_NAME, null, DB_VERSION);
  277.         }
  278.  
  279.         @Override
  280.         public void onCreate(SQLiteDatabase db)
  281.         {
  282.             // This string is used to create the database.  It should
  283.             // be changed to suit your needs.
  284.             String newTableQueryString = "create table " +
  285.                                         TABLE_NAME +
  286.                                         " (" +
  287.                                         TABLE_ROW_ID + " integer primary key autoincrement not null," +
  288.                                         TABLE_ROW_ONE + " text," +
  289.                                         TABLE_ROW_TWO + " text" +
  290.                                         ");";
  291.             // execute the query string to the database.
  292.             db.execSQL(newTableQueryString);
  293.         }
  294.  
  295.  
  296.         @Override
  297.         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  298.         {
  299.             // NOTHING TO DO HERE. THIS IS THE ORIGINAL DATABASE VERSION.
  300.             // OTHERWISE, YOU WOULD SPECIFIY HOW TO UPGRADE THE DATABASE.
  301.         }
  302.     }
  303. }
RAW Paste Data