android - 如何在 Android 中升级 SQLite 数据库？

Question

第一次我创建了一个表 DEPTS。之后我想创建另外两个表 FEEDS 和 ARTICLES。

但是我看到创建新表的命令从来没有执行过，为什么？

这是我的代码:

package com.android.database;

// ... imports ...

public class DatabaseHelper extends SQLiteOpenHelper{

static final String dbName = "appDB";
static int dbVersion = 3;
private static final String DEBPT_TABLE = "Dept";
private static final String COL_DEPT_ID = "DeptID";
private static final String COL_DEPT_NAME = "DeptName";
private static final String COL_DEPT_LAT = "DeptLat";
private static final String COL_DEPT_LNG = "DeptLng";

private static final String FEEDS_TABLE = "feeds";
private static final String COL_FEED_ID = "feed_id";
private static final String COL_FEED_TITLE = "title";
private static final String COL_FEED_URL = "url";

private static final String ARTICLES_TABLE = "articles";
private static final String COL_ARTICLE_ID = "article_id";
private static final String COL_ARTICLE_FEED_ID = "feed_id";
private static final String COL_ATRICLE_TITLE = "title";
private static final String COL_ATRICLE_URL = "url";

    public DatabaseHelper(Context context) {
        super(context, dbName, null, dbVersion);
        // TODO Auto-generated constructor stub
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        /*onCreate(SQLiteDatabase db): invoked when the database is created, 
        this is where we can create tables and columns to them, create views or triggers. */

        db.execSQL("CREATE TABLE "+DEBPT_TABLE+
                "("+COL_DEPT_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
                COL_DEPT_NAME+" TEXT NOT NULL, "+
                COL_DEPT_LAT+" REAL NOT NULL, "+
                COL_DEPT_LNG+" REAL NOT NULL);");

        db.execSQL("CREATE TABLE "+ FEEDS_TABLE+
                " ("+COL_FEED_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
                COL_FEED_TITLE +" TEXT NOT NULL,"+
                COL_FEED_URL+" TEXT_NOT_NULL);");

        db.execSQL("CREATE TABLE "+ ARTICLES_TABLE+
                " ("+COL_ARTICLE_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
                COL_ARTICLE_FEED_ID+ " INTEGER NOT NULL, "+
                COL_ATRICLE_TITLE+" TEXT NOT NULL, "+
                COL_ATRICLE_URL+" TEXT NOT NULL);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        /*onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): invoked when we make a modification to 
        the database such as altering, dropping , creating new tables. */

        db.execSQL("DROP TABLE IF EXISTS "+DEBPT_TABLE);
        db.execSQL("DROP TABLE IF EXISTS "+FEEDS_TABLE);
        db.execSQL("DROP TABLE IF EXISTS "+ARTICLES_TABLE);
        onCreate(db);
    }

    public boolean insertDept(Department dept){
        SQLiteDatabase db = this.getReadableDatabase();// open database for read/write
        ContentValues cv = new ContentValues();
        cv.put(COL_DEPT_NAME, dept.getName());
        cv.put(COL_DEPT_LAT, dept.getLat());
        cv.put(COL_DEPT_LNG, dept.getLng());

        long result = db.insert(DEBPT_TABLE, COL_DEPT_ID, cv);
        // result : the row ID of the newly inserted row, or -1 if an error occurred 
        db.close();
        return (result>0);
    }

    public boolean updateDept(Department dept){
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COL_DEPT_NAME, dept.getName());
        cv.put(COL_DEPT_LAT, dept.getLat());
        cv.put(COL_DEPT_LNG, dept.getLng());

        int result = db.update(DEBPT_TABLE, cv,COL_DEPT_ID+"=?", new String []{String.valueOf(dept.getID())});
        //String[] args: The arguments of the WHERE clause
        db.close();
        return (result>0);
    }

    public boolean deleteDept(Department dept){
        SQLiteDatabase db = this.getWritableDatabase();
        int re = db.delete(DEBPT_TABLE, COL_DEPT_ID+"=?", new String[]{String.valueOf(dept.getID())});
        db.close();
        return (re>0);
    }

    public ArrayList<Department> getAllDepts(){
        ArrayList<Department> depts = new ArrayList<Department>();
        Department dept = null;
        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cur = db.rawQuery("SELECT "+COL_DEPT_ID+ " as _id,"+
                COL_DEPT_NAME+", "+COL_DEPT_LAT+", "+COL_DEPT_LNG+" from "+DEBPT_TABLE,
                new String[]{});
        cur.moveToFirst();
        while(cur.moveToNext()){
            dept = new Department();
            dept.setID(cur.getInt(cur.getColumnIndex("_id")));
            dept.setName(cur.getString(cur.getColumnIndex(COL_DEPT_NAME)));
            dept.setLat(cur.getDouble(cur.getColumnIndex(COL_DEPT_LAT)));
            dept.setLat(cur.getDouble(cur.getColumnIndex(COL_DEPT_LNG)));

            depts.add(dept);
        }
        db.close();
        return depts;
    }

    public int GetDeptID(String Dept)
  {
   SQLiteDatabase db=this.getReadableDatabase();
   Cursor c=db.query(DEBPT_TABLE, new String[]{COL_DEPT_ID+" as _id",COL_DEPT_NAME},
    COL_DEPT_NAME+"=?", new String[]{Dept}, null, null, null);
   //Cursor c=db.rawQuery("SELECT "+COL_DEPT_ID+" as _id FROM "+DEBPT_TABLE+" 
   //WHERE "+COL_DEPT_NAME+"=?", new String []{Dept});
   c.moveToFirst();
   return c.getInt(c.getColumnIndex("_id"));  
  }

    public boolean insertFeed(String title, URL url) {
        SQLiteDatabase db=this.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_FEED_TITLE, title);
        values.put(COL_FEED_URL, url.toString());
        return (db.insert(FEEDS_TABLE, null, values) > 0);
    }

    public boolean deleteFeed(Feed feed) {
        SQLiteDatabase db=this.getReadableDatabase();
        return (db.delete(FEEDS_TABLE, COL_FEED_ID+ "=" + feed.feedId, null) > 0);
}

    public ArrayList<Feed> getFeeds() {
        SQLiteDatabase db=this.getReadableDatabase();
        ArrayList<Feed> feeds = new ArrayList<Feed>();
        try {
                Cursor c = db.query(FEEDS_TABLE, new String[] { COL_FEED_ID, COL_FEED_TITLE,
                        COL_FEED_URL }, null, null, null, null, null);

                int numRows = c.getCount();
                c.moveToFirst();
                for (int i = 0; i < numRows; ++i) {
                        Feed feed = new Feed();
                        feed.feedId = c.getLong(0);
                        feed.title = c.getString(1);
                        feed.url = new URL(c.getString(2));
                        feeds.add(feed);
                        c.moveToNext();
                }
        } catch (SQLException e) {
                Log.e("FEEDS DB", e.toString());
        } catch (MalformedURLException e) {
                Log.e("FEEDS DB", e.toString());
        }
        return feeds;
}

    public boolean insertArticle(Long feedId, String title, URL url) {
        SQLiteDatabase db=this.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_ARTICLE_FEED_ID, feedId);
        values.put(COL_ATRICLE_TITLE, title);
        values.put(COL_ATRICLE_URL, url.toString());
        return (db.insert(ARTICLES_TABLE, null, values) > 0);
        }

    public boolean deleteAricles(Long feedId) {
        SQLiteDatabase db=this.getReadableDatabase();
        return (db.delete(ARTICLES_TABLE, COL_ARTICLE_FEED_ID+"=" + feedId.toString(), null) > 0);
}

    public List<Article> getArticles(Long feedId) {
        SQLiteDatabase db=this.getReadableDatabase();
        ArrayList<Article> articles = new ArrayList<Article>();
        try {
                Cursor c = db.query(ARTICLES_TABLE, new String[] { "article_id",
                                "feed_id", "title", "url" },
                                "feed_id=" + feedId.toString(), null, null, null, null);

                int numRows = c.getCount();
                c.moveToFirst();
                for (int i = 0; i < numRows; ++i) {
                        Article article = new Article();
                        article.articleId = c.getLong(0);
                        article.feedId = c.getLong(1);
                        article.title = c.getString(2);
                        article.url = new URL(c.getString(3));
                        articles.add(article);
                        c.moveToNext();
                }
        } catch (SQLException e) {
                Log.e("ARTICLES DB", e.toString());
        } catch (MalformedURLException e) {
                Log.e("ARTICLES DB", e.toString());
        }
        return articles;
}

}

Answer 1

这里不要传递版本号

public DatabaseHelper(Context context) {

        super(context, dbName, null, dbVersion);

        // TODO Auto-generated constructor stub

    }

应该是这样

  public DatabaseHelper(Context context, String dbName, null, int dbVersion) 
{

        super(context, dbName, null, dbVersion);

        // TODO Auto-generated constructor stub

    }

不要忘记更新你的版本号