How to display a SQLite DataBase table?

Question

in my application I use a database to display some user data, I created a database using SQLite DataBase Browser and placed this database in the resource folder, this is the encoding for this, I want to display this table.

public class DataBaseHelper extends SQLiteOpenHelper{ private Context mycontext; private String DB_PATH = "/data/data/com.slate.game/databases/"; private static String DB_NAME = "slider.db";//the extension may be .sqlite or .db public SQLiteDatabase myDataBase; public DataBaseHelper(Context context) throws IOException { super(context,DB_NAME,null,1); this.mycontext=context; boolean dbexist = checkdatabase(); if(dbexist) { System.out.println("Database exists"); opendatabase(); } else { System.out.println("Database doesn't exist"); createdatabase(); } } public void createdatabase() throws IOException{ boolean dbexist = checkdatabase(); if(dbexist) { System.out.println(" Database exists."); } else{ this.getReadableDatabase(); try{ copydatabase(); } catch(IOException e) { throw new Error("Error copying database"); } } } private boolean checkdatabase() { boolean checkdb = false; try { String myPath = DB_PATH + DB_NAME; File dbfile = new File(myPath); checkdb = dbfile.exists(); } catch(SQLiteException e) { System.out.println("Database doesn't exist"); } return checkdb; } private void copydatabase() throws IOException { //Open your local db as the input stream InputStream myinput = mycontext.getAssets().open(DB_NAME); // Path to the just created empty db String outfilename = DB_PATH + DB_NAME; System.out.println("outfilename"+outfilename); //Open the empty db as the output stream OutputStream myoutput = new FileOutputStream("/data/data/com.slate.game/databases/slider.db"); // transfer byte to inputfile to outputfile byte[] buffer = new byte[1024]; int length; while ((length = myinput.read(buffer))>0) { myoutput.write(buffer,0,length); } //Close the streams myoutput.flush(); myoutput.close(); myinput.close(); } public void opendatabase() throws SQLException { //Open the database String mypath = DB_PATH + DB_NAME; myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE); } public synchronized void close() { if(myDataBase != null){ myDataBase.close(); } super.close(); } @Override public void onCreate(SQLiteDatabase arg0) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } 

please help me how to display this table (database) ..

Answer 1

Finally, I received a response from this book.

 Beginning.Android.Application.Development. 

using SQLite I prepared the database, I get the row and display using TableLayout with TextView ..

this is the database adapter class

 public class DBAdapter3x3 { public static final String KEY_ROWID = "_id"; public static final String KEY_NAME = "name"; public static final String KEY_MOVES = "moves"; public static final String KEY_TIME = "time"; private static final String TAG = "DBAdapter"; private static final String DATABASE_NAME = "SliderDB3x3.db"; private static final String DATABASE_TABLE = "topscore3x3"; private static final int DATABASE_VERSION = 1; private static final String DATABASE_CREATE = "create table topscore3x3 " + "(_id integer primary key autoincrement, " + "name text not null, moves integer not null," + "time text not null);"; private final Context context; private DatabaseHelper DBHelper; private SQLiteDatabase db; public DBAdapter3x3(Context ctx) { this.context = ctx; DBHelper = new DatabaseHelper(context); } private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { try { db.execSQL(DATABASE_CREATE); } catch (SQLException e) { e.printStackTrace(); } } @Override public void onUpgrade(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(); return this; } //---closes the database--- public void close() { DBHelper.close(); } //---insert a contact into the database--- public long insertContact(String name, int moves,String time) { ContentValues initialValues = new ContentValues(); 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--- public boolean deleteContact(long rowId) { return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0; } //---retrieves all the contacts--- public Cursor getAllContacts() { return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, KEY_MOVES, KEY_TIME}, null, null, null, null, null); } //---retrieves a particular contact--- public Cursor getContact(long rowId) throws SQLException { Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] {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--- public boolean updateContact(long rowId, String name, int moves, String time) { ContentValues args = new ContentValues(); 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, new String[] { KEY_ROWID, KEY_NAME, KEY_MOVES,KEY_TIME}, null, null, null, null, KEY_MOVES + " ASC"); } } 

I used the database in this activity

 public class TopScore3x3 extends Activity { private DBAdapter3x3 db; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.db3x3); db = new DBAdapter3x3(this); getall(); delete(); private void delete() { db.open(); Cursor c = db.SortAllRows(); int i=1; if (c.moveToFirst()) { do { if(i>10) { db.deleteContact(i); } i++; } while (c.moveToNext()); } c.close(); db.close(); } private void getall() { //---get all contacts--- db.open(); //db.fetchAllNotes(); Cursor c = db.SortAllRows(); int i=1; if (c.moveToFirst()) { do { DisplayContact(c,i++); } while (c.moveToNext()); } c.close(); db.close(); } public void DisplayContact(Cursor c,int row ) { String name11 = c.getString(1) + c.getString(2) + c.getString(3); tv1.setText(name11 ); } } 

Answer 2

use the following function to retrieve data from a database

 /** * This function used to select the records from DB. * @param tableName * @param tableColumns * @param whereClase * @param whereArgs * @param groupBy * @param having * @param orderBy * @return A Cursor object, which is positioned before the first entry. */ public Cursor selectRecordsFromDB(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 * @param tableName * @param tableColumns * @param whereClase * @param whereArgs * @param groupBy * @param having * @param orderBy * @return ArrayList<ArrayList<String>> */ public ArrayList<ArrayList<String>> selectRecordsFromDBList(String tableName, String[] tableColumns,String whereClase, String whereArgs[], String groupBy,String having, String orderBy) { ArrayList<ArrayList<String>> retList = new ArrayList<ArrayList<String>>(); ArrayList<String> list = new ArrayList<String>(); Cursor cursor = myDataBase.query(tableName, tableColumns, whereClase, whereArgs, groupBy, having, orderBy); if (cursor.moveToFirst()) { do { list = new ArrayList<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; } 

Answer 3

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

Use Cursor c = db.rawQuery(sql, null) to get the results, iteration is explained here.