android sqlite 資料庫升級

標籤：

情境：

　　資料庫版本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     @Override22     public void onCreate(SQLiteDatabase db) {23         // TODO Auto-generated method stub24     //    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     @Override31     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {32         // TODO Auto-generated method stub33         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 block58             e.printStackTrace();59         } finally{60             db.endTransaction();61         }62         63         64     }65 66     67 }

 

android sqlite 資料庫升級