java - 在 sql java for android 中向表中添加一列

Question

我试图将列添加到我创建的 SQL 数据库中。此数据库用于学习 purpes，因此请忽略列名 ..我在尝试这样做时遇到了很多问题，但经过大量阅读后，我看到了一些东西:1. 我需要更改数据库版本，以便启动 onUpgrade 方法2. 我需要在我的 onUpgrade 方法中添加一个“ALTER TABLE”命令。

这样做之后，应用程序不再抛出异常，我认为一切正常。唯一的问题是，如果我尝试使用新列更新表，它不会更新任何内容(即使出于某种原因它不会抛出异常)。

我主要关心的是我是否应该将新列 commend 添加到 onCreat 或不我创建的 onUpdate 方法是添加 KEY_KIDS 列:

public void onUpgrade(SQLiteDatabase db, int oldVersion,
            int newVersion) throws SQLException{
        Log.w(DBHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        db.execSQL("ALTER TABLE " + DATABASE_TABLE[0] +  " ADD COLUMN "
                +  KEY_KIDS + " text not null default kids; ");
        Log.w(DBHelper.class.getName(),
                "Columns added!");
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[0]);
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[1]);
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[2]);
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[3]);
        onCreate(db);
    }

这是我想要添加列的表，新列是 KEY_KIDS:

 public static final String Table1= "CREATE TABLE " + DATABASE_TABLE[0] + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " + 
                KEY_AGE + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL);" ; //

应该是这样，还是没有 KEY_KIDS 声明 (KEY_KIDS + "TEXT NOT..)

请帮忙。几天来我一直在尝试解决我的问题，但无法弄清楚。这是 sql 的完整代码:

     package com.Dvir.newlearning1;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class HotOrNot {

    public static final String KEY_ROWID = "_id";
    public static final String KEY_NAME = "persons_name";
    public static final String KEY_AGE = "persons_age";
    public static final String KEY_HOTNESS = "persons_hotness";
    public static final String KEY_Hairy = "is_hairy";
    public static final String KEY_KIDS = "has_kids";

    private static final String DATABASE_NAME = "NewDB2";
    private static final String[] DATABASE_TABLE = {"peopleTable", 
            "peopleTable2", "peopleTable3","peopleTable4" };
    private static final int DATABASE_VERSION = 2;

    private DBHelper ourHelper;
    private final Context ourContext;
    private SQLiteDatabase ourDatabase;

    private static class DBHelper extends SQLiteOpenHelper{
        public static final String Table1= "CREATE TABLE " + DATABASE_TABLE[0] + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " + 
                KEY_AGE + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL " + 
                KEY_KIDS + " TEXT NOT NULL);" ;
        public static final String Table2 = "CREATE TABLE " + DATABASE_TABLE[1] + " (" +
                    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    KEY_NAME + " TEXT NOT NULL, " + 
                    KEY_AGE + " TEXT NOT NULL, " +
                    KEY_HOTNESS + " TEXT NOT NULL);";
        public static final String Table3= "CREATE TABLE " + DATABASE_TABLE[2] + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " + 
                KEY_AGE + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL);" ;
        public static final String Table4= "CREATE TABLE " + DATABASE_TABLE[3] + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " + 
                KEY_AGE + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL);" ;
        public DBHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase db) throws SQLException{
            // TODO Auto-generated method stub
            db.execSQL(Table1);
            db.execSQL(Table2);
            db.execSQL(Table3);
            db.execSQL(Table4);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion,
                int newVersion) throws SQLException{
            Log.w(DBHelper.class.getName(),
                    "Upgrading database from version " + oldVersion + " to "
                            + newVersion + ", which will destroy all old data");
            /*db.execSQL("ALTER TABLE " + DATABASE_TABLE[0] +  " ADD COLUMN "
                    +  KEY_KIDS + " text not null default kids; ");*/
            Log.w(DBHelper.class.getName(),
                    "Columns added!");
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[0]);
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[1]);
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[2]);
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE[3]);
            onCreate(db);
        }

    }

    public HotOrNot(Context c) throws SQLException{
        ourContext = c;
    }
    public HotOrNot open(){
        ourHelper = new DBHelper(ourContext);
        ourDatabase = ourHelper.getWritableDatabase();
        return this;
    }
    public void close(){
        ourHelper.close();

    }
    public long creatEntry(String name, String age, String hotness, int table, String kids)
        throws SQLException {
        // TODO Auto-generated method stub
        ContentValues cv = new ContentValues();
        cv.put(KEY_NAME,name);
        cv.put(KEY_AGE, age);
        cv.put(KEY_HOTNESS, hotness);
        cv.put(KEY_KIDS, kids);
        return ourDatabase.insert(DATABASE_TABLE[table], null, cv);
    }
    public long creatEntry(String name, String age, String hotness,String hasKids, int table) {
        // TODO Auto-generated method stub
        ContentValues cv = new ContentValues();
        cv.put(KEY_NAME,name);
        cv.put(KEY_AGE, age);
        cv.put(KEY_HOTNESS, hotness);
        cv.put(KEY_KIDS, hotness);
        return ourDatabase.insert(DATABASE_TABLE[table], null, cv);
    }
    public String getData(int table) {
        // TODO Auto-generated method stub
        String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS, KEY_AGE};
        Cursor c;
        c = ourDatabase.query(DATABASE_TABLE[table], columns, null, null, null, null, null);
        String result = "";
        int iRow = c.getColumnIndex(KEY_ROWID);
        int iName = c.getColumnIndex(KEY_NAME);
        int iAge = c.getColumnIndex(KEY_AGE);
        int iHotness = c.getColumnIndex(KEY_HOTNESS);

        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
            result = result + c.getString(iRow) + " " + c.getString(iName) + " " +
                    c.getString(iAge) + " " + c.getString(iHotness) + "\n";
        }

        return result;
    }
    public String getName(long l) throws SQLException{
        // TODO Auto-generated method stub
        String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS, KEY_AGE};
        Cursor c = ourDatabase.query(DATABASE_TABLE[0], columns, KEY_ROWID + "=" + l, null, null,null, null);
        if (c !=null){
            c.moveToFirst();
            String name = c.getString(1);
            return name;
        }
        return null;
    }
    public String getHotness(long l) throws SQLException{
        // TODO Auto-generated method stub
        String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS, KEY_AGE};
        Cursor c = ourDatabase.query(DATABASE_TABLE[0], columns, KEY_ROWID + "=" + l, null, null,null, null);
        if (c !=null){
            c.moveToFirst();
            int iHot = c.getColumnIndex(KEY_HOTNESS);
            String name = c.getString(iHot);
            return name;
        }
        return null;
    }
    public String getAge(long l)throws SQLException {
        // TODO Auto-generated method stub
        String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS, KEY_AGE};
        Cursor c = ourDatabase.query(DATABASE_TABLE[0], columns, KEY_ROWID + "=" + l, null, null,null, null);
        if (c !=null){
            c.moveToFirst();
            int iAge = c.getColumnIndex(KEY_AGE);
            String name = c.getString(iAge);
            return name;
        }
        return null;
    }
    public void updateEntry(int table, long lRow, String mHotness, String mAge,
            String mName) throws SQLException {
        // TODO Auto-generated method stub
        ContentValues cvUpdate = new ContentValues();
        cvUpdate.put(KEY_NAME, mName);
        cvUpdate.put(KEY_AGE, mAge);
        cvUpdate.put(KEY_HOTNESS, mHotness);
        ourDatabase.update(DATABASE_TABLE[table], cvUpdate, KEY_ROWID + "=" + lRow, null);

    }
    public void deleteEntry(long lRow1) throws SQLException{
        // TODO Auto-generated method stub
        ourDatabase.delete(DATABASE_TABLE[0], KEY_ROWID + "=" + lRow1, null);

    }
}

另一件事 - 我一直在尝试使用 for 循环创建多表数据库。但是，我不知道为什么，它不允许我这样做。它尝试做类似的事情: 字符串[]表； 对于 (int i = 0; i<26, i++) { table[i] = "创建表"+ "DATABASE_TABLE[i]"+... }eclips 将“表”末尾的分号标记为红色，但出现以下错误: token “;”上的语法错误，{ 应在此 token 之后有人知道为什么吗？

Answer 1

看这部分:

public static final String Table1= "CREATE TABLE " + DATABASE_TABLE[0] + " (" +
            KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            KEY_NAME + " TEXT NOT NULL, " + 
            KEY_AGE + " TEXT NOT NULL, " +
            KEY_HOTNESS + " TEXT NOT NULL " + 
            KEY_KIDS + " TEXT NOT NULL);" ;

您忘记了 KEY_KIDS 部分之前的逗号 (,)。