How to get all tables from SQLite database Android Studio

Question

I've been searching on how to get all tables from a database, I'm not familiar with SQLite for Android Studio. I have a DBHelper class that I use to make my sql commands. What I've gathered so far is that I have to make a query to sqlite_master such as "SELECT name FROM sqlite_master WHERE type='table'".

Here is my DBHelper class:

public class DBHelper extends SQLiteOpenHelper
{
public String tableName;
private static final String COLUMN_ID = "id";
private static final String COLUMN_NAME = "name";
private static final String COLUMN_DESC = "desc";
private static final String COLUMN_DECKNAME = "deckname";
private static final String COLUMN_IMAGENAME = "imagename";

public DBHelper(Context context, String tablename) {
    super(context, "UserDecks.db", null, 1);
    tableName = tablename;
}

public DBHelper(Context context){
    super(context, "UserDecks.db", null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + tableName + "(" + COLUMN_NAME + " TEXT, " + COLUMN_DESC + " TEXT, " + COLUMN_DECKNAME + " TEXT, " + COLUMN_IMAGENAME + " TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("DROP TABLE IF EXISTS " + tableName);
}

@Override
public void onOpen(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + "(" + COLUMN_NAME + " TEXT, " + COLUMN_DESC + " TEXT, " + COLUMN_DECKNAME + " TEXT, " + COLUMN_IMAGENAME + " TEXT)");
}

public Cursor GetDeckList(){
    SQLiteDatabase sdb = this.getReadableDatabase();
    Cursor cursor = sdb.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
    return cursor;
}

I only pasted where it reaches up to the GetDeckList method.

This is where I reference DBHelper:

    db = new DBHelper(this);
    Cursor cData = db.GetDeckList();
    int numOfRows = cData.getCount();
    if(numOfRows == 0){
        Toast.makeText(this, "Deck list is empty!", Toast.LENGTH_SHORT).show();
    }else{
        while(cData.moveToNext()){
            DL_deckList = new DeckList(cData.getString(0));
            LIST_currentDecks.add(DL_deckList);
        }
        mAdapter = new RecycleViewAdapter(true, LIST_currentDecks, this);
        layoutManager = new LinearLayoutManager(this);
        recyclerView.setLayoutManager(layoutManager);

        recyclerView.setAdapter(mAdapter);
        recyclerView.setHasFixedSize(false);
    }

I think I am missing some piece of code, because I'm doing what I've read on stackoverflow on how to query from sqlite_master, but my app keeps crashing because I keep trying to create a "null" table. It keeps calling "onCreate" or "onOpen" when I tinker with the code, even though I'm not trying to make a table. So it keeps crashing because I'm not giving it a new table. So I'm assuming I'm missing some code, but I don't know what it is.

Sabrina N · Accepted Answer · 2021-07-02 03:24:35Z

2

try like this

db = new DBHelper(this);
Cursor cData = db.GetDeckList();
if(cData != null) {
    int numOfRows = cData.getCount();
    if(numOfRows == 0){
        Toast.makeText(this, "Deck list is empty!", Toast.LENGTH_SHORT).show();
    }else{
    
        List<String> tableNames = new ArrayList<String>();
        cData.moveToFirst();
        while (!cData.isAfterLast()) {
            tableNames.add(cData.getString(cData.getColumnIndex("name")));
            cData.moveToNext();
        }
        mAdapter = new RecycleViewAdapter(true, tableNames, this);
        
        layoutManager = new LinearLayoutManager(this);
        recyclerView.setLayoutManager(layoutManager);

        recyclerView.setAdapter(mAdapter);
        recyclerView.setHasFixedSize(false);
    }
}

answered Jul 2, 2021 at 3:24

Sabrina N

1501 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wilson Castiblanco · Accepted Answer · 2021-07-02 03:13:51Z

You can remove the code from onCreate, onUpgrade, onOpen and that will give you the results you are looking for.

The result from GetDeckList would be only one record: android_metadata, a table by default.

If you need to create tables in the future, then add the code back to onCreate, in that case, you could have some constants in your DBHandler for your new tables.

Finally, I recommend you have a Singleton to have only one instance of DBHandler to avoid memory leaks or memory allocations. Another piece of advice would be If you can use the abstraction library Room from the Android Jetpack, much better, this library will save you from important issues from using SQLite directly. If you want to check Room out, please check Room Setup

Dharmender Manral · Accepted Answer · 2021-07-02 08:28:55Z

1

Try with the following query for get all database tables

 //use below query for reading all table list
   Cursor cursor = sdb.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT IN ('android_metadata', 'sqlite_sequence', 'room_master_table') ",null);

edited Jul 2, 2021 at 8:28

answered Jul 2, 2021 at 3:46

Dharmender Manral

1,5201 gold badge8 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to get all tables from SQLite database Android Studio

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related