Dynamically creating SQLite table with defined rows

Question

I am looking to set up my app to dynamically create table based on user input. But I want to have a defined structure for the tables and the user will essentially name the table.

foe example, I want the user to define the name of the tables they want, but all of the tables will have the same amount and name of rows that they will fill out through another settings option.

here is my db, I currently have two tables in there which I need to change to have the menucategory table will be the table that gets created by the user, and the menuitem table will be the columns of that category table

public class DatabaseHandler extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
protected static final String DATABASE_NAME = "Store";
private static final String sqlcat ="menucategory";

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

@Override
public void onCreate(SQLiteDatabase db) {

    String sql = "CREATE TABLE menuitem " +
            "( id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "name TEXT, " +
            "description TEXT, "+
            "price int, "+
            "cost int, "+
            "cat TEXT)";

    db.execSQL(sql);

    String sqlcat = "CREATE TABLE menucategory " +
            "( id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "name TEXT)";

    db.execSQL(sqlcat);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    String sql = "DROP TABLE IF EXISTS menuitem";
    db.execSQL(sql);

    onCreate(db);

    String sqlcat = "DROP TABLE IF EXISTS menucategory";

    onCreate(db);
}

/**
 * Getting all names
 * returns list of mobile name
 * */
public List<String> getAllNames(){
    List<String> names = new ArrayList<String>();

    // Select All Query
    String selectQuery = "SELECT  * FROM " + sqlcat;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            names.add(cursor.getString(1));
        } while (cursor.moveToNext());
    }

    // closing connection
    cursor.close();
    db.close();

    // returning names
    return names;
}

}

You should show us what you have tried...Also explain why it didn't work or ask how can you make it better. Questions without code usually get voted down and not answered — royB
– royB, Commented Mar 17, 2016 at 12:44
All the tables are created dynamically in android through code(Unless you copy DB)... You just read the table name from user and pass it as an argument. — Viswanath Lekshmanan
– Viswanath Lekshmanan, Commented Mar 17, 2016 at 12:44
Agree with @royB. and Jenova you have to just create table dynamically Right??? — Nils
– Nils, Commented Mar 17, 2016 at 12:45
My problem is i'm not sure where to start @royB which is why there is no code, I originally wanted to do something else which wasn't working so this is a new approach. no code = no idea where to start. thanks Viswanath Lekshmanan for the insight — Jenova Projects
– Jenova Projects, Commented Mar 17, 2016 at 12:51
Show us the code for creating the DB...it will be a good start...We will help you make it dynamic... — royB
– royB, Commented Mar 17, 2016 at 12:57

Nils · Accepted Answer · 2016-03-19 04:58:59Z

1

I don't know what you have do, but to create table dynamically I use this function.

public void createUserTable(String tableName) {
    final SQLiteDatabase db = getWritableDatabase();
    String CREATE_TABLE_NEW_USER = "CREATE TABLE " + tableName + " (" + COLUMN_ID + " INTEGER PRIMARY KEY,"+ COLUMN_NAME + " TEXT)";
    db.execSQL(CREATE_TABLE_NEW_USER);
    db.close();
}

Well after your comments this is a answer ....

String TABLE_MENU_ITEM_LIST = "MenuItemList";  // change as per your requirement 
String CREATE_TABLE_NEW_MENU_CATEGORY = "CREATE TABLE " + TABLE_MENU_ITEM_LIST + " ( ID INTEGER PRIMARY KEY,MenuItemName TEXT )";

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(TABLE_MENU_ITEM_LIST);
}

public void addNewMenuInList(String menuCategoryName) {  // First enter you menu name in MenuItem List
    final SQLiteDatabase db = getWritableDatabase();
    final ContentValues contentValues = new ContentValues();
    contentValues.put(MENU_CATEGORY_NAME, menuCategoryName);
    int i = (int) db.insert(TABLE_MENU_ITEM_LIST, null, contentValues);
    db.close();
    //Now create new table for this category
    if (i > 0) {
        createNewMenuCategoryTable(menuCategoryName);
    }
}

public void createNewMenuCategoryTable(String menuItemName) {
    final SQLiteDatabase db = getWritableDatabase();
    // change your column datatype as per your requirement for price and coast
    String CREATE_TABLE_NEW_MENU_CATEGORY = "CREATE TABLE " + menuItemName + " ( ID INTEGER PRIMARY KEY,Name TEXT,Description TEXT,ImagePath TEXT,Price TEXT,Coast TEXT )";

    db.execSQL(CREATE_TABLE_NEW_MENU_CATEGORY);
    db.close();
}
enter code here

edited Mar 19, 2016 at 4:58

answered Mar 17, 2016 at 13:01

Nils

6576 silver badges16 bronze badges

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

10 Comments

Jenova Projects Over a year ago

thanks for the help, is this placed in the dbhelper class

Jenova Projects Over a year ago

sorry to be a pain, i'm new at android and really new at sqlite. could you show me an example of the add column you'd put in your activity? just wondering on how to add the columns. thanks

Nils Over a year ago

Don't worry.! I always happy to be helping. which table you are creating ?? and how many column you want to add?? which column you want to add??

Jenova Projects Over a year ago

So I need to create menucategory table which the user will give a name to. And I need 5 columns. Basically all.of the menuitem table will become the columns for menucategory

Nils Over a year ago

if you have same columns for all table then yes you can do it.

|

Collectives™ on Stack Overflow

Dynamically creating SQLite table with defined rows

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related