android sqlite 数据库升级

浏览数：23 / 时间：2015年06月11日

场景：

　　数据库版本v1 有一个persion表

　　数据库版本v2 新增表student

　　数据库版本v3 persion表新增一个字段

 1 package com.example.natedb;
 2 
 3 import android.content.Context;
 4 import android.database.SQLException;
 5 import android.database.sqlite.SQLiteDatabase;
 6 import android.database.sqlite.SQLiteDatabase.CursorFactory;
 7 import android.database.sqlite.SQLiteOpenHelper;
 8 
 9 public class SqlDb extends SQLiteOpenHelper {
10 
11     private final static int version = 3;
12     public SqlDb(Context context){
13         this(context,"lihao.db",null,version);
14     }
15     public SqlDb(Context context, String name, CursorFactory factory,
16             int version) {
17         super(context, name, factory, version);
18         
19     }
20 
21     @Override
22     public void onCreate(SQLiteDatabase db) {
23         // TODO Auto-generated method stub
24     //    db.execSQL("create table person(_id integer primary key autoincrement,name text,age text)");
25         //最新
26         db.execSQL("create table person(_id integer primary key autoincrement,name text,age text,score txt)");
27         db.execSQL("create table student(_id integer primary key autoincrement,name text,age text)");
28     }
29 
30     @Override
31     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
32         // TODO Auto-generated method stub
33         int currentVersion = oldVersion;
34         db.beginTransaction();
35         try {
36             if(currentVersion == 1) {
37                 db.execSQL("create table student(_id integer primary key autoincrement,name text,age text)");
38                 currentVersion = 2;
39             }
40 
41             if(currentVersion == 2) {
42                 String tempTable = "person_temp";
43                 //修改原表为临时表
44                 db.execSQL("alter table person rename to "+tempTable);
45                 
46                 //创建新表
47                 db.execSQL("create table person(_id integer primary key autoincrement,name text,age text,score txt)");
48                 
49                 //复制数据
50                 db.execSQL("insert into person(name,age) select name,age from "+tempTable);
51                 
52                 //删除临时表
53                 db.execSQL("drop table "+tempTable);
54             }
55             db.setTransactionSuccessful();
56         } catch (SQLException e) {
57             // TODO Auto-generated catch block
58             e.printStackTrace();
59         } finally{
60             db.endTransaction();
61         }
62         
63         
64     }
65 
66     
67 }