升级SQLite数据库模式

Question

现在我有这样的数据库代码

public class AABDatabaseManager
{
    // the Activity or Application that is creating an object from this class.
    Context context;

    // a reference to the database used by this application/object
    private SQLiteDatabase db;

    // These constants are specific to the database.  They should be 
    // changed to suit your needs.
    private final String DB_NAME = "database_name";
    private final int DB_VERSION = 1;

    // These constants are specific to the database table.  They should be
    // changed to suit your needs.
    private final String TABLE_NAME = "database_table";
    private final String TABLE_ROW_ID = "id";
    private final String TABLE_ROW_ONE = "table_row_one";
    private final String TABLE_ROW_TWO = "table_row_two";

    public AABDatabaseManager(Context context)
    {
        this.context = context;

        // create or open the database
        CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
        this.db = helper.getWritableDatabase();
    }


    public void addRow(String rowStringOne, String rowStringTwo)
    {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, rowStringOne);
        values.put(TABLE_ROW_TWO, rowStringTwo);

        // ask the database object to insert the new data 
        try{db.insert(TABLE_NAME, null, values);}
        catch(Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }


    public void deleteRow(long rowID)
    {
        // ask the database manager to delete the row of given id
        try {db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);}
        catch (Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }


    public void updateRow(long rowID, String rowStringOne, String rowStringTwo)
    {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, rowStringOne);
        values.put(TABLE_ROW_TWO, rowStringTwo);

        // ask the database object to update the database row of given rowID
        try {db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);}
        catch (Exception e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
    }


    public ArrayList<Object> getRowAsArray(long rowID)
    {
        // create an array list to store data from the database row.
        // I would recommend creating a JavaBean compliant object 
        // to store this data instead.  That way you can ensure
        // data types are correct.
        ArrayList<Object> rowArray = new ArrayList<Object>();
        Cursor cursor;

        try
        {
            // this is a database call that creates a "cursor" object.
            // the cursor object store the information collected from the
            // database and is used to iterate through the data.
            cursor = db.query
            (
                    TABLE_NAME,
                    new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
                    TABLE_ROW_ID + "=" + rowID,
                    null, null, null, null, null
            );

            // move the pointer to position zero in the cursor.
            cursor.moveToFirst();

            // if there is data available after the cursor's pointer, add
            // it to the ArrayList that will be returned by the method.
            if (!cursor.isAfterLast())
            {
                do
                {
                    rowArray.add(cursor.getLong(0));
                    rowArray.add(cursor.getString(1));
                    rowArray.add(cursor.getString(2));
                }
                while (cursor.moveToNext());
            }

            // let java know that you are through with the cursor.
            cursor.close();
        }
        catch (SQLException e) 
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }

        // return the ArrayList containing the given row from the database.
        return rowArray;
    }



    public ArrayList<ArrayList<Object>> getAllRowsAsArrays()
    {
        // create an ArrayList that will hold all of the data collected from
        // the database.
        ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();

        // this is a database call that creates a "cursor" object.
        // the cursor object store the information collected from the
        // database and is used to iterate through the data.
        Cursor cursor;

        try
        {
            // ask the database object to create the cursor.
            cursor = db.query(
                    TABLE_NAME,
                    new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
                    null, null, null, null, null
            );

            // move the cursor's pointer to position zero.
            cursor.moveToFirst();

            // if there is data after the current cursor position, add it
            // to the ArrayList.
            if (!cursor.isAfterLast())
            {
                do
                {
                    ArrayList<Object> dataList = new ArrayList<Object>();

                    dataList.add(cursor.getLong(0));
                    dataList.add(cursor.getString(1));
                    dataList.add(cursor.getString(2));

                    dataArrays.add(dataList);
                }
                // move the cursor's pointer up one position.
                while (cursor.moveToNext());
            }
        }
        catch (SQLException e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }

        // return the ArrayList that holds the data collected from
        // the database.
        return dataArrays;
    }

    private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
    {
        public CustomSQLiteOpenHelper(Context context)
        {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            // This string is used to create the database.  It should
            // be changed to suit your needs.
            String newTableQueryString = "create table " +
                                        TABLE_NAME +
                                        " (" +
                                        TABLE_ROW_ID + " integer primary key autoincrement not null," +
                                        TABLE_ROW_ONE + " text," +
                                        TABLE_ROW_TWO + " text" +
                                        ");";
            // execute the query string to the database.
            db.execSQL(newTableQueryString);
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            // NOTHING TO DO HERE. THIS IS THE ORIGINAL DATABASE VERSION.
            // OTHERWISE, YOU WOULD SPECIFIY HOW TO UPGRADE THE DATABASE.
        }
    }
}

现在我需要将其他3列添加到我的表中，从而在googled中更新我的数据库模式，我发现我需要用新的版本号调用sqllitehelper构造函数，所以我按下面的方式修改了代码，但仍然没有调用onupgrade()

  public class AABDatabaseManager 
{
    // the Activity or Application that is creating an object from this class.
    Context context;

    // a reference to the database used by this application/object
    private SQLiteDatabase db;

    // These constants are specific to the database.  They should be 
    // changed to suit your needs.
    static final String DB_NAME = "database_name";
    static final int DB_VERSION = 1;

    // These constants are specific to the database table.  They should be
    // changed to suit your needs.
    private final String TABLE_NAME = "database_table";
    private final String TABLE_ROW_ID = "id";
    private final String TABLE_ROW_ONE = "table_row_one";
    private final String TABLE_ROW_TWO = "table_row_two";
    private final String TABLE_ROW_THREE = "table_row_three";
    private final String TABLE_ROW_FOUR = "table_row_four";
    private final String TABLE_ROW_FIVE= "table_row_five";
    public AABDatabaseManager(Context context)
    {
        //super(context, DB_NAME, null,2); 
        this.context = context;

        // create or open the database
        CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
        this.db = helper.getWritableDatabase();
    }

    public void addRow(String rowStringOne, String rowStringTwo, String rowStringThree, String rowStringFour, String rowStringFive)
    {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, rowStringOne);
        values.put(TABLE_ROW_TWO, rowStringTwo);
        values.put(TABLE_ROW_THREE, rowStringThree);
        values.put(TABLE_ROW_FOUR, rowStringFour);
        values.put(TABLE_ROW_FIVE, rowStringFive);
        // ask the database object to insert the new data 
        try{db.insert(TABLE_NAME, null, values);}
        catch(Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }


    public void deleteRow(long rowID)
    {
        // ask the database manager to delete the row of given id
        try {db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);}
        catch (Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }

    public void updateRow(long rowID, String rowStringOne, String rowStringTwo,String rowStringThree, String rowStringFour,String rowStringFive)
    {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, rowStringOne);
        values.put(TABLE_ROW_TWO, rowStringTwo);
        values.put(TABLE_ROW_THREE, rowStringThree);
        values.put(TABLE_ROW_FOUR, rowStringFour);
        values.put(TABLE_ROW_FIVE, rowStringFive);


        // ask the database object to update the database row of given rowID
        try {db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);}
        catch (Exception e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
    }

    public ArrayList<Object> getRowAsArray(long rowID)
    {
        // create an array list to store data from the database row.
        // I would recommend creating a JavaBean compliant object 
        // to store this data instead.  That way you can ensure
        // data types are correct.
        ArrayList<Object> rowArray = new ArrayList<Object>();
        Cursor cursor;

        try
        {
            // this is a database call that creates a "cursor" object.
            // the cursor object store the information collected from the
            // database and is used to iterate through the data.
            cursor = db.query
            (
                    TABLE_NAME,
                    new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO,  TABLE_ROW_THREE,  TABLE_ROW_FOUR, TABLE_ROW_FIVE },
                    TABLE_ROW_ID + "=" + rowID,
                    null, null, null, null, null
            );

            // move the pointer to position zero in the cursor.
            cursor.moveToFirst();

            // if there is data available after the cursor's pointer, add
            // it to the ArrayList that will be returned by the method.
            if (!cursor.isAfterLast())
            {
                do
                {
                    rowArray.add(cursor.getLong(0));
                    rowArray.add(cursor.getString(1));
                    rowArray.add(cursor.getString(2));
                    rowArray.add(cursor.getString(3));
                    rowArray.add(cursor.getString(4));
                    rowArray.add(cursor.getString(5));
                }
                while (cursor.moveToNext());
            }

            // let java know that you are through with the cursor.
            cursor.close();
        }
        catch (SQLException e) 
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }

        // return the ArrayList containing the given row from the database.
        return rowArray;
    }


    public ArrayList<ArrayList<Object>> getAllRowsAsArrays()
    {
        // create an ArrayList that will hold all of the data collected from
        // the database.
        ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();

        // this is a database call that creates a "cursor" object.
        // the cursor object store the information collected from the
        // database and is used to iterate through the data.
        Cursor cursor;

        try
        {
            // ask the database object to create the cursor.
            cursor = db.query(
                    TABLE_NAME,
                    new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO,  TABLE_ROW_THREE,  TABLE_ROW_FOUR, TABLE_ROW_FIVE},
                    null, null, null, null, null
            );

            // move the cursor's pointer to position zero.
            cursor.moveToFirst();

            // if there is data after the current cursor position, add it
            // to the ArrayList.
            if (!cursor.isAfterLast())
            {
                do
                {
                    ArrayList<Object> dataList = new ArrayList<Object>();

                    dataList.add(cursor.getLong(0));
                    dataList.add(cursor.getString(1));
                    dataList.add(cursor.getString(2));
                    dataList.add(cursor.getString(3));
                    dataList.add(cursor.getString(4));
                    dataList.add(cursor.getString(5));
                    dataArrays.add(dataList);
                }
                // move the cursor's pointer up one position.
                while (cursor.moveToNext());
            }
        }
        catch (SQLException e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }

        // return the ArrayList that holds the data collected from
        // the database.
        return dataArrays;
    }

    private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
    {
        public CustomSQLiteOpenHelper(Context context)
        {
            super(context, DB_NAME,null, 2);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            // This string is used to create the database.  It should
            // be changed to suit your needs.
            String newTableQueryString = "create table " +
                                        TABLE_NAME +
                                        " (" +
                                        TABLE_ROW_ID + " integer primary key autoincrement not null," +
                                        TABLE_ROW_ONE + " text," +
                                        TABLE_ROW_TWO + " text," +
                                        TABLE_ROW_THREE + " text," +
                                        TABLE_ROW_FOUR + " text," +
                                        TABLE_ROW_FIVE+ " text" +
                                        ");";
            // execute the query string to the database.
            db.execSQL(newTableQueryString);
        }


        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            // NOTHING TO DO HERE. THIS IS THE ORIGINAL DATABASE VERSION.
            // OTHERWISE, YOU WOULD SPECIFIY HOW TO UPGRADE THE DATABASE.
            db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
             onCreate(db);
        }
    }

}

请原谅我的长问题，我认为这会帮助我回答。那么，告诉我什么是问题，为什么我不能改变我的数据库结构。

现在，当我运行代码时，会出现类似于未知列table_row_three的错误

正在从这样的另一个服务中调用这个

 offlinedb= new AABDatabaseManager(this);

    offlinedb.addRow(longitude, latitude,imei,"check",date);

Answer 1

编辑:忽略了您的实例调用，在写入数据库之前，您确实缺少了getWritableDatabase()。在编写数据库时，在使用TABLE_ROW_ID工作时，请将TABLE_ROW_ID不变的值更改为"_id“。

String newTableQueryString = "create table if not exists " +
                                    TABLE_NAME + " (" +
                                    TABLE_ROW_ID + " integer primary key autoincrement not null, " +
                                    TABLE_ROW_ONE + " text, " +
                                    TABLE_ROW_TWO + " text, " +
                                    TABLE_ROW_THREE + " text, " +
                                    TABLE_ROW_FOUR + " text, " +
                                    TABLE_ROW_FIVE + " text);";

我就是这样做的，因为SQLite是个大麻烦，我们至少知道你在onCreate()

Answer 2

或者，如果您有足够的时间使用新的框架，请在这里查看：

http://adaframework.com/

我在一个会议上听说过这件事，看上去很酷。

它以透明的方式管理升级。

我不得不说，我还没有对它进行测试，但是在会议上用这个框架在android中管理一个DB的人看到了非常深刻的印象。

Answer 3

当您发现您的代码中没有错误，并且一切正常工作，但您的数据库仍然没有升级，增加您的版本号，这可能会帮助您。这就是我所做的。希望它能对其他人有所帮助