[Solved] Creating multiple Tables and inserting Data into them [closed]

by


Your issue “there is always only one table created”, will be due to the very common misconception that the onCreate method, in DatabaseHelper.java (generally termed the Database Helper) runs every time the App is run. Actually the onCreate method is only automatically invoked once for the lifetime of the database.

If the data held in the database can be lost then the fix is simply, delete the database.

  • this can be done by either
    • deleting the App’s data from settings/Apps or
    • uninstalling the App from settings/Apps
      When the App is then rerun the onCreate method will then be invoked.

In your situation the onUpgrade drops the tables, if they exist, and then invokes the onCreate method. As such an alternative to deleting the App’s data/uninstalling the App, would be to increase the Database Version, which will then cause the onUpgrade method to be run, and thus recreating the tables.

Note this would still result in any existing data being lost.

e.g. :-

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 2); //<<<< CHANGED 1 to 2
    Log.d("MeineAPP", "DB angelegt");
}

Testing of your code

The actual table create statements work and using logDatabaseInfo from this a clean run produces :-

05-26 20:52:24.618 1398-1398/? D/MeineAPP: DB angelegt
05-26 20:52:24.622 1398-1398/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/jannikokan.de.stundenplan/databases/Stundenplan.db
    Database Version = 1
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
05-26 20:52:24.626 1398-1398/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = Fach_table Created Using = CREATE TABLE Fach_table(IDINTEGER PRIMARY KEY,FACHNAMETEXT,FACHKUERZEL TEXT,FACHRAUMTEXT,FACHLEHRER TEXT)
    Table = Fach_table ColumnName = IDINTEGER ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = Fach_table ColumnName = FACHNAMETEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Fach_table ColumnName = FACHKUERZEL ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Fach_table ColumnName = FACHRAUMTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Fach_table ColumnName = FACHLEHRER ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = Lehrer_table Created Using = CREATE TABLE Lehrer_table(ID_LINTEGER PRIMARY KEY,LEHRERNAMETEXT,LEHRERKUERZELTEXT,LEHRERRAUMTEXT,LEHRERMAILTEXT)
    Table = Lehrer_table ColumnName = ID_LINTEGER ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = Lehrer_table ColumnName = LEHRERNAMETEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Lehrer_table ColumnName = LEHRERKUERZELTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Lehrer_table ColumnName = LEHRERRAUMTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Lehrer_table ColumnName = LEHRERMAILTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0

However, looking at your code you do have an issue with two methods (zeigeFaecher and zeigeLehrer) in that you have omitted a space between * and from i.e. you have *from which should be * from.

  • Note testing was limited to this issue specifically and that the testing is by no means saying that there are not other aspects that will or will not result in issues.

Additional – Retaining Data (simple solution)

If you have data that needs to be kept then you will have to devise a method of keeping the data.

It could be as simple as adding a check to see if the expected tables exist and if not to create them e.g. you could have/add the following in Databasehelper.java :-

e.g. :-

// This is the publicly accessible driver that has the core lists
// i.e. the list of tables that should exist and the
// corresponding table create statements which are passed to
// the createTablesThatDoNotExist method
public void addAnyNewTables() {
    String[] required_tables = new String[]{
            TABLE_NAME,
            TABLE_LEHRER
    };

    String[] table_create_statements = new String[] {
      create_Table,
      create_Table2
    };
    createTablesThatDoNotExist(required_tables,table_create_statements);
}


// This method checks the validity lengths and count of the 2 arrays
// loping through them if valid pass the table and the create statement to the
// doCheckAndCreateOfTable method.
private void createTablesThatDoNotExist(String[] required_tables, String[] table_create_statements) {

    // If no tables or table create statements then finish
    if (required_tables.length < 1 || table_create_statements.length < 1) {
        return;
    }
    // elements in arrays must match
    if (required_tables.length != table_create_statements.length) {
        return;
    }
    SQLiteDatabase db = this.getWritableDatabase();
    String whereclause = "name";
    for (int i=0; i < required_tables.length;i++) {
        if (required_tables[i].length() > 0 && table_create_statements[i].length() > 0) {
            doCheckAndCreateOfTable(
                    required_tables[i].toString(),
                    table_create_statements[i].toString()
            );
        }
    }
}

// This does the real work by interrogatin sqlite_master to see if the table
// exists. If not then it runs the query to create the table using the
// create_statement passed.
private void doCheckAndCreateOfTable(String table,String create_statement) {
    SQLiteDatabase db = this.getWritableDatabase();
    String whereclause = "name=? AND type=?";
    String[] whereargs = new String[]{table,"table"};
    String table_to_query = "sqlite_master";
    Cursor csr = db.query(table_to_query,null,whereclause,whereargs,null,null,null);
    if (csr.getCount() < 1) {
        db.execSQL(create_statement);
    }
    csr.close();
}

You could then invoke this in the initial activity after getting an instance of the DatabaseHelper e.g. :-

    mDBHlpr = new DatabaseHelper(this);
    mDBHlpr.addAnyNewTables();
    SQLiteDatabase db = mDBHlpr.getWritableDatabase();
    CommonSQLiteUtilities.logDatabaseInfo(db);

The above was tested by commenting out the creation of the second table, deleting the App’s data (and therefore the database) and then running the App using the following in the onCreate (to purposefully NOT create the 2nd table)

public void onCreate(SQLiteDatabase db) {

    Log.d("MeineAPP", "Tabelle angelegt");
    //  db.execSQL("create table " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, FACHNAME TEXT, FACHKUERZEL TEXT, FACHRAUM TEXT, FACHLEHRER TEXT)");
    db.execSQL(create_Table);
    //db.execSQL(create_Table2); //<<<< COMMENTED OUT FOR TEST

}

The resultant output was :-

05-26 21:16:07.672 1742-1742/? D/MeineAPP: DB angelegt
05-26 21:16:07.672 1742-1744/? D/dalvikvm: GC_CONCURRENT freed 236K, 10% free 6158K/6791K, paused 11ms+0ms, total 15ms
05-26 21:16:07.708 1742-1742/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/jannikokan.de.stundenplan/databases/Stundenplan.db
    Database Version = 1
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
    Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = Fach_table Created Using = CREATE TABLE Fach_table(IDINTEGER PRIMARY KEY,FACHNAMETEXT,FACHKUERZEL TEXT,FACHRAUMTEXT,FACHLEHRER TEXT)
    Table = Fach_table ColumnName = IDINTEGER ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = Fach_table ColumnName = FACHNAMETEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Fach_table ColumnName = FACHKUERZEL ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Fach_table ColumnName = FACHRAUMTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Fach_table ColumnName = FACHLEHRER ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = Lehrer_table Created Using = CREATE TABLE Lehrer_table(ID_LINTEGER PRIMARY KEY,LEHRERNAMETEXT,LEHRERKUERZELTEXT,LEHRERRAUMTEXT,LEHRERMAILTEXT)
    Table = Lehrer_table ColumnName = ID_LINTEGER ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = Lehrer_table ColumnName = LEHRERNAMETEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Lehrer_table ColumnName = LEHRERKUERZELTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Lehrer_table ColumnName = LEHRERRAUMTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Lehrer_table ColumnName = LEHRERMAILTEXT ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0

i.e. both tables have been created

5

solved Creating multiple Tables and inserting Data into them [closed]