时间戳

首页 > 解决方案 > 创建多个表并将数据插入其中

android - 创建多个表并将数据插入其中

问题描述

对于我的应用程序，我需要在我的数据库中创建 2 个不同的表，我的问题是始终只创建一个表。

这是 DatabaseHelper 类的核心：-

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "Stundenplan.db";
    public static final String TABLE_NAME = "Fach_table";
    public static final String FACH_ID = "ID";
    public static final String FACH_NAME = "FACHNAME";
    public static final String FACH_KUERZEL = "FACHKUERZEL";
    public static final String FACH_RAUM = "FACHRAUM";
    public static final String FACH_LEHRER = "FACHLEHRER";
    public static final String FACH_FARBE = "FACHFARBE";
    public static final String TABLE_LEHRER = "Lehrer_table";
    public static final String LEHRERID = "ID_L";
    public static final String LEHRERNAME = "LEHRERNAME";
    public static final String LEHRERKUERZEL = "LEHRERKUERZEL";
    public static final String LEHRERRAUM = "LEHRERRAUM";
    public static final String LEHRERMAIL = "LEHRERMAIL";

    private static final String create_Table2 = "create table " + TABLE_LEHRER + "("+ LEHRERID +"INTEGER PRIMARY KEY," + LEHRERNAME +"TEXT," + LEHRERKUERZEL + "TEXT,"+ LEHRERRAUM + "TEXT," + LEHRERMAIL + "TEXT)";
    private static final String create_Table =  "create table " + TABLE_NAME + "("+ FACH_ID + "INTEGER PRIMARY KEY," + FACH_NAME +"TEXT," + FACH_KUERZEL + " TEXT,"+ FACH_RAUM + "TEXT," + FACH_LEHRER + " TEXT)";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);

        Log.d("MeineAPP", "DB angelegt");
    }

    @Override
    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);

    }

    @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_NAME);
        db.execSQL( "DROP TABLE IF EXISTS " + TABLE_LEHRER);

        onCreate(db);
        Log.d("MeineAPP", "in upgrade");

    }
...........   
}

请帮忙！

标签： androiddatabasesqliteandroid-sqlite

解决方案

您的问题“始终只创建一个表”是由于非常普遍的误解，即DatabaseHelper.javaonCreate中的方法（通常称为数据库助手）在每次运行应用程序时都会运行。实际上，该方法仅在数据库的生命周期内自动调用一次。onCreate

如果数据库中保存的数据可能丢失，那么解决方法很简单，删除数据库。

  • 这可以通过任何一个来完成
    • 从设置/应用程序中删除应用程序的数据或
    • 从设置/应用程序卸载应用程序然后重新运行应用程序时，onCreate将调用该方法。

在您的情况下，onUpgrade删除表（如果存在），然后调用该onCreate方法。作为删除应用程序数据/卸载应用程序的替代方法，将增加数据库版本，这将导致onUpgrade方法运行，从而重新创建表。

请注意，这仍会导致任何现有数据丢失。

例如：-

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

测试你的代码

实际的表创建语句工作并使用logDatabaseInfo 从此干净运行产生：-

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

但是，查看您的代码，您确实对两种方法 (zeigeFaecher和zeigeLehrer) 存在问题，因为您省略了 and 之间的空格*，from即您有*fromwhich should be * from。

  • 注意测试仅限于这个问题，并且测试绝不是说没有其他方面会或不会导致问题。

附加 - 保留数据（简单解决方案）

如果您有需要保留的数据，那么您将必须设计一种保留数据的方法。

它可以像添加检查以查看预期的表是否存在以及如果不创建它们一样简单，例如您可以在 Databasehelper.java 中添加/添加以下内容：-

例如：-

// 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();
}

然后，您可以在获取 DatabaseHelper 的实例后在初始活动中调用它，例如：-

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

上面的测试是通过注释掉第二个表的创建，删除应用程序的数据（以及数据库）然后使用以下内容运行应用程序onCreate（故意不创建第二个表）

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

}

结果输出是：-

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

即两个表都已创建

推荐阅读