How To Display Sqlite Database Table?

in my app i am using the database for displaying some user information, i created data base by using the SQLite DataBase Browser, and placed that database in the assets folder, thi

Solution 1:

finally i got answer from this BOOK

Beginning.Android.Application.Development.

using the SQLite i prepared database from that i will get row's and display using the TableLayout with TextView..

this is the database adapter class

publicclassDBAdapter3x3  {

publicstaticfinalStringKEY_ROWID="_id";
publicstaticfinalStringKEY_NAME="name";
publicstaticfinalStringKEY_MOVES="moves";
publicstaticfinalStringKEY_TIME="time";
privatestaticfinalStringTAG="DBAdapter";
privatestaticfinalStringDATABASE_NAME="SliderDB3x3.db";
privatestaticfinalStringDATABASE_TABLE="topscore3x3"; 
privatestaticfinalintDATABASE_VERSION=1;
privatestaticfinalStringDATABASE_CREATE="create table topscore3x3 " +
                                        "(_id integer primary key autoincrement, "
                                        + "name text not null, moves integer not null," 
                                        + "time text not null);";

privatefinal Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;

publicDBAdapter3x3(Context ctx)    {
    this.context = ctx;
    DBHelper = newDatabaseHelper(context);
}

privatestaticclassDatabaseHelperextendsSQLiteOpenHelper    {

    DatabaseHelper(Context context)     {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @OverridepublicvoidonCreate(SQLiteDatabase db)   {
        try  {
            db.execSQL(DATABASE_CREATE);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)   {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }
}

//---opens the database---public DBAdapter3x3 open()throws SQLException      {

    db = DBHelper.getWritableDatabase();
    returnthis;
}

//---closes the database---publicvoidclose()   {
    DBHelper.close();
}

//---insert a contact into the database---publiclonginsertContact(String name, int moves,String time)   {

    ContentValuesinitialValues=newContentValues();
    initialValues.put(KEY_NAME, name);
    initialValues.put(KEY_MOVES, moves);
    initialValues.put(KEY_TIME, time);
    return db.insert(DATABASE_TABLE, null, initialValues);
}

//---deletes a particular contact---publicbooleandeleteContact(long rowId)    {

    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

//---retrieves all the contacts---public Cursor getAllContacts()  {

    return db.query(DATABASE_TABLE, newString[] {KEY_ROWID, KEY_NAME,
            KEY_MOVES, KEY_TIME}, null, null, null, null, null);
}

//---retrieves a particular contact---public Cursor getContact(long rowId)throws SQLException    {

    CursormCursor= db.query(true, DATABASE_TABLE, newString[] {KEY_ROWID, KEY_NAME, 
                     KEY_MOVES, KEY_TIME}, KEY_ROWID + "=" + rowId, null,null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

//---updates a contact---publicbooleanupdateContact(long rowId, String name, int moves, String time)   {

    ContentValuesargs=newContentValues();
    args.put(KEY_NAME, name);
    args.put(KEY_MOVES, moves);
    args.put(KEY_TIME, time);       
    return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
//public Cursor fetchAllNotes() {public Cursor SortAllRows() {
    return db.query(DATABASE_TABLE, newString[] { KEY_ROWID, KEY_NAME, 
             KEY_MOVES,KEY_TIME}, null, null, null, null, KEY_MOVES + " ASC");
}
}

i used database in this activity

publicclassTopScore3x3extendsActivity  {

private DBAdapter3x3 db;

@OverridepublicvoidonCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.db3x3);      

    db = newDBAdapter3x3(this);        
    getall();    
    delete();

privatevoiddelete()  {

    db.open();       
    Cursorc= db.SortAllRows();
    int i=1;
    if (c.moveToFirst())   {        
        do {            
            if(i>10) { db.deleteContact(i); } 
            i++;
        } while (c.moveToNext());
    }
    c.close();
    db.close();
}   

privatevoidgetall()  {
    //---get all contacts---
    db.open();
    //db.fetchAllNotes();Cursorc= db.SortAllRows();
    int i=1;
    if (c.moveToFirst())   {        
        do {
            DisplayContact(c,i++);
        } while (c.moveToNext());
    }
    c.close();
    db.close();
} 

publicvoidDisplayContact(Cursor c,int row )   {            

     Stringname11= c.getString(1) + c.getString(2) + c.getString(3);           
     tv1.setText(name11 );

  }
 } 

Solution 2:

use following function to retrieve data from database

/**
 * This function used to select the records from DB.
 * @paramtableName
 * @paramtableColumns
 * @paramwhereClase
 * @paramwhereArgs
 * @paramgroupBy
 * @paramhaving
 * @paramorderBy
 * @return A Cursor object, which is positioned before the first entry.
 */publicCursorselectRecordsFromDB(String tableName, String[] tableColumns,String whereClase, String whereArgs[], String groupBy,String having, String orderBy) 
{
    return myDataBase.query(tableName, tableColumns, whereClase, whereArgs,groupBy, having, orderBy);
}

/**
 * select records from db and return in list
 * @paramtableName
 * @paramtableColumns
 * @paramwhereClase
 * @paramwhereArgs
 * @paramgroupBy
 * @paramhaving
 * @paramorderBy
 * @return ArrayList<ArrayList<String>>
 */publicArrayList<ArrayList<String>> selectRecordsFromDBList(String tableName, String[] tableColumns,String whereClase, String whereArgs[], String groupBy,String having, String orderBy)
{       

    ArrayList<ArrayList<String>> retList = newArrayList<ArrayList<String>>();
      ArrayList<String> list = newArrayList<String>();
      Cursor cursor = myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
                groupBy, having, orderBy);        
      if (cursor.moveToFirst()) 
      {
         do 
            {
             list = newArrayList<String>();
             for(int i=0; i<cursor.getColumnCount(); i++)
             {                   
               list.add( cursor.getString(i) );
             }   
             retList.add(list);
         } while (cursor.moveToNext());
      }
      if (cursor != null && !cursor.isClosed()) {
         cursor.close();
      }
      return retList;

}   

Solution 3:

Check this: http://www.higherpass.com/Android/Tutorials/Accessing-Data-With-Android-Cursors/2/

Use Cursor c = db.rawQuery(sql, null) to get results, iterating is explained there.