CODEWITHSUNDEEP
androidsqlite
Jenova Projects
Jenova Projects

Reputation: 119

Dynamically creating SQLite table with defined rows

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;
}

}

Upvotes: 1

Views: 5063

Answers (1)

Nils
Nils

Reputation: 657

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

Upvotes: 1

Related Questions