Android SQLiteOpenHelper(二)

上一篇我们已经了解了SQLiteOpenHelper 和构造函数。

现在我们就来掌握一下：onCreate( ) onUpgrade( ) onDowngrade( )

public void onCreate( SQLiteDatabase db)

　　api：Called when the database is created for the first time. 首次创建数据库时调用,即没有数据库前身,可用于数据初始化。

　　参数 db --->指这个数据库

public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        // 编写【从0开始到最新状态】建表语句
        Log.i("hi", "没有数据库，创建数据库，创建v3.0成功");
        String sql_message = "create table t_message (id int primary key,tou1  varchar(50),userName varchar(50),lastMessage varchar(50),datetime  varchar(50),isdel bit default 0)";
        String sql_init_1 = "insert into t_message values (1,'abc','abc1','abcd1','hi1',0)";
        String sql_init_2 = "insert into t_message values (2,'abc','abc2','abcd2','hi1',0)";
        String sql_init_3 = "insert into t_message values (3,'abc','abc2','abcd2','hi1',0)";
        db.execSQL(sql_message);
        db.execSQL(sql_init_1);
        db.execSQL(sql_init_2);
        db.execSQL(sql_init_3);

    }

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

　　api:

　　Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

The SQLite ALTER TABLE documentation can be found here. If you add new columns you can use ALTER TABLE to insert them into a live table. If you rename or remove columns you can use ALTER TABLE to rename the old table, then create the new table and then populate the new table with the contents of the old table.

　　意思是：

　　数据库需要升级时自动回调。实现应该使用这种方法删除表,添加表或做其他事情需要升级到新模式版本。

　　SQLite ALTER TABLE文档可以在这里找到。如果您添加新列可以使用ALTER TABLE将它们插入到一个生活表。如果您重命名或删除列可以使用ALTER TABLE重命名旧表,然后创建新表和填充旧表的内容。

public void onCreate(SQLiteDatabase db) {
        Log.i("version", "开始版本2");
        db.execSQL("create table info(_id integer primary key,name varchar(20),age integer)");
        String sql1="insert into info values(1,'张三','18')";
        String sql2="insert into info values(2,'李四','19')";
        String sql3="insert into info values(3,'王五','21')";
        db.execSQL(sql1);
        db.execSQL(sql2);
        db.execSQL(sql3);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
             //升级增加一个列
        if (oldVersion == 2){
            String sql_upgrade_1 = "alter table t_message add column isdel bit default 0";
            db.execSQL(sql_upgrade_1);
            Log.i("db", "从2到3，升级成功！");
        }

public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)

　　和onUpgrade一样，一个负责升级，一个负责降级。

    /* 模拟从3.0 降低会2.0 */
    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //正常来讲大于2.0的，应该有t_message 这张表，且2.0有的字段，3.0都有
        try {
            //第一、先把t_message 未来的表，改名
            String rename_sql = "alter table t_message rename to t_message_bak";
            db.execSQL(rename_sql);
            Log.i("down", "1.改名成功");
            //第二、建立2.0的表结构
            String sql_message = "create table t_message (id int primary key,tou1  varchar(50),userName varchar(50),lastMessage varchar(50),datetime  varchar(50))";
            db.execSQL(sql_message);
            Log.i("down", "2.建立2.0表结构成功");
            //第三、把备份的数据，copy到 新建的2.0的表
            String sql_copy = "insert into t_message select id,tou1,userName,lastMessage,datetime from t_message_bak";
            db.execSQL(sql_copy);
            Log.i("down", "3.copy到用户数据到 2.0的表");
            //第四、把备份表drop掉
            String drop_sql = "drop table if exists t_message_bak";
            db.execSQL(drop_sql);
            Log.i("down", "4.把备份表drop掉");
            
        } catch (Exception e) {
            //失败
            Log.i("hi", "降级失败，重新建立");
            String sql_drop_old_table = "drop table if exists t_message";
            String sql_message = "create table t_message (id int primary key,tou1  varchar(50),userName varchar(50),lastMessage varchar(50),datetime  varchar(50))";
            String sql_init_1 = "insert into t_message values (1,'abc','abc1','abcd1','hi1')";
            String sql_init_2 = "insert into t_message values (2,'abc','abc2','abcd2','hi1')";
            String sql_init_3 = "insert into t_message values (3,'abc','abc2','abcd2','hi1')";
            db.execSQL(sql_drop_old_table);
            db.execSQL(sql_message);
            db.execSQL(sql_init_1);
            db.execSQL(sql_init_2);
            db.execSQL(sql_init_3);
        }
    }