How to create multiple tables in Android studio database

Question

Currently I'm following tutorial for android database creation on youtube at channel ProgrammingKnowledge, however, it works only for one table, while I need to have 3 tables in the database, and can't get my way around it.

This is the code I currently have.

public class DatabaseHelper extends SQLiteOpenHelper {

    //database name declaration
    public static final String DATABASE_NAME = "museum.db";
    public static final String TABLE_NAME = "exponent_table";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "TITLE";
    public static final String COL_3 = "STORY";
    public static final String COL_4 = "AUTHOR";
    public static final String COL_5 = "DATE";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        SQLiteDatabase db= this.getWritableDatabase(); //will create database and table, just for checking, will be replaced
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //creating tables ???
          db.execSQL("create table "+TABLE_NAME+" (ID INTEGER PRIMARY KEY AUTOINCREMENT, TITLE TEXT, STORY TEXT, AUTHOR TEXT, DATE STRING)");
          
    }

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

I have seen something similar on the link: Multi-table database SQLite android

But still do not understand.

score 11 · Answer 1 · answered Jan 21 '17 at 18:52

this is very good tutorial..read it

you can create multiple tables like this

@Override
public void onCreate(SQLiteDatabase db) {   
  db.execSQL("create table "+TABLE_NAME+" (ID INTEGER PRIMARY KEY AUTOINCREMENT, TITLE TEXT, STORY TEXT, AUTHOR TEXT, DATE STRING)");
  db.execSQL("create table "+TABLE_NAME1+" (ID INTEGER PRIMARY KEY AUTOINCREMENT, Table1Field TEXT)");
}

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

score 1 · Answer 2 · answered Jan 21 '17 at 18:48

You just have to invoke more db.execSQL(sqlQuery); methods.

For example:

db.execSQL("create table "+TABLE_NAME+" (ID INTEGER PRIMARY KEY AUTOINCREMENT, TITLE TEXT, STORY TEXT, AUTHOR TEXT, DATE STRING)");
db.execSQL("create table "+TABLE_NAME2+" (ID INTEGER PRIMARY KEY AUTOINCREMENT, TITLE TEXT, STORY TEXT, AUTHOR TEXT, DATE STRING)");
db.execSQL("create table "+TABLE_NAME3+" (ID INTEGER PRIMARY KEY AUTOINCREMENT, TITLE TEXT, STORY TEXT, AUTHOR TEXT, DATE STRING)");

If you don't know SQL, I recommend learning it before here.

score 0 · Answer 3 · answered Jun 11 '20 at 19:19

in Kotlin I make the database loop in a constant array, so I just add the name of the tables in my array, like this

my object cons

    package app.packagename

    object cons {
      val tableNames = arrayOf(
        0*/"tableZero",
        1*/"tableOne",
        2*/"tableTwo"
      )
    }

my SQLiteDatabase

package app.packagename

    import app.packagename.cons
    import android.content.Context
    import android.database.sqlite.SQLiteDatabase
    import android.database.sqlite.SQLiteOpenHelper

        class DBHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
            override fun onCreate(db: SQLiteDatabase) {
                for(i in cons.tableNames.indices){
                    val createProductsTable = ("CREATE TABLE " + cons.tableNames[i] + "("
                            + Business.idKey + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
                            + Business.KEY_a + " TEXT, "
                            + Business.KEY_b + " TEXT, "
                            + Business.KEY_c + " TEXT, "
                            + Business.KEY_d + " TEXT, "
                            + Business.KEY_e + " INTEGER )")
                    db.execSQL(createProductsTable)
                }
            }
            override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
                // Drop older table if existed, all data will be gone!!!
                for(i in cons.tableNames.indices){

                    db.execSQL("DROP TABLE IF EXISTS ${cons.tableNames[i]}")

                    // Create tables again
                    onCreate(db)
                }
            }
            companion object {
                //version number to upgrade database version
                //each time if you Add, Edit table, you need to change the
                //version number.
                private val DATABASE_VERSION = 1

                // Database Name
                private val DATABASE_NAME = "tables.db"
            }
        }

How to create multiple tables in Android studio database

3 Answers3

Linked