java - sqlite for android，如何更新表

Question

我在堆栈溢出上找到了许多处理此问题的链接，但没有一个解决方案对我有用，所以这里是:

我正在尝试添加一个列来保存日期(并且将来可能需要添加一个时间列)，但由于表已经创建，所以遇到了一些麻烦。这是数据库代码:

public class MyDatabase {

public static final String KEY_ROWID = "_id";
public static final String KEY_DATE = "Date";
public static final String KEY_SYS = "Systolic";
public static final String KEY_DIA = "Diastolic";

private static final String DATABASE_NAME = "Blood Pressures";//Used to reference database
private static final String DATABASE_TABLE = "bloodPressureTable";//Tables can be stored in database
// this will be used to store ID, date, systolic and diastolic
private static final int DATABASE_VERSION = 9;

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

public String getData() {
    String[] columns = new String[]{KEY_ROWID, KEY_SYS, KEY_DIA};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
    String result = "";

    int iRow = c.getColumnIndex(KEY_ROWID);
    int iSys = c.getColumnIndex(KEY_SYS);
    int iDia = c.getColumnIndex(KEY_DIA);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {   //if cursor is after position of our last entry, then stop
        result = result + c.getString(iRow) +       //get ROW_ID column, get name of first row and hotness, create new string
                " " + c.getString(iSys) + " " +
                c.getString(iDia) + "\n";
    }

    return result;
}

private static class DbHelper extends SQLiteOpenHelper  {

    public DbHelper(Context context)    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        //TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {   //This is only called when we first create a database
        // when then we will just cycle through onUpgrade, passes a database in
        db.execSQL(
                "CREATE TABLE " + DATABASE_TABLE + " (" + //create a table called table name
                        //adds columns inside parenthesise
                        KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + //integer increment automatically, referenced by keyID
                        KEY_DATE + "TEXT NOT NULL, " +  //SQL uses the word text rather than string
                        KEY_SYS + " INTEGER, " +
                        KEY_DIA + " INTEGER);"
        );
    }

    @Override //called if oncreate has already been called
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


        //db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE); //If table name exists it is going
        // to drop it and then all we have to do is call our onCreate method
        //Removes table added by create table statement
        //onCreate(db);

        if (newVersion > oldVersion)    {
            db.execSQL("ALTER TABLE " + DATABASE_TABLE +
            " ADD COLUMN " + KEY_DATE + " TEXT NOT NULL, ");
        }

    }
}

//Constructor below
public MyDatabase(Context c)    {
    ourContext = c;
}
//open() allows us to open and write to database

public MyDatabase open()    {
    ourHelper = new DbHelper(ourContext); // this is a new instance of that object passing in the context
    ourDatabase = ourHelper.getWritableDatabase();//here we set up our database,
    // getting the writeable database. This will open up our database through our helper
    return this;
}

public void close()    {
    ourHelper.close(); //close our SQLiteOpenHelper

}

public long createEntry(int systolic, int diastolic) {
    ContentValues cv = new ContentValues();
    Calendar c = Calendar.getInstance();
    String s = Integer.toString(c.get(Calendar.DAY_OF_MONTH)) + "/" + Integer.toString(c.get(Calendar.MONTH)) +
            "/" + Integer.toString(c.get(Calendar.YEAR));

    cv.put(KEY_DATE, s);
    cv.put(KEY_SYS, systolic);
    cv.put(KEY_DIA, diastolic);//Put the string in KEY_NAME column?
    return ourDatabase.insert(DATABASE_TABLE, null, cv); //we want this method to return this line
}
}

有人能给我一个解决方案吗？正如你所看到的，我尝试了两种不同的方法(一种在 onUpgrade 中被注释掉)。正如你所看到的，我已经使用了版本 10!

这是日志猫:

03-02 16:03:48.416    1811-1811/com.dissertation.michael.biolog E/SQLiteLog﹕ (1) Cannot add a NOT NULL column with default value NULL
03-02 16:03:48.416    1811-1811/com.dissertation.michael.biolog D/AndroidRuntime﹕ Shutting down VM
03-02 16:03:48.416    1811-1811/com.dissertation.michael.biolog W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0x41696bd8)
03-02 16:03:48.416    1811-1811/com.dissertation.michael.biolog E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.dissertation.michael.biolog, PID: 1811
java.lang.RuntimeException: Failure delivering result ResultInfo{who=null, request=1111, result=-1, data=Intent { (has extras) }} to activity {com.dissertation.michael.biolog/com.dissertation.michael.biolog.MainActivity}: android.database.sqlite.SQLiteException: Cannot add a NOT NULL column with default value NULL (code 1): , while compiling: ALTER TABLE bloodPressureTable ADD COLUMN DateTEXT NOT NULL
        at android.app.ActivityThread.deliverResults(ActivityThread.java:3391)
        at android.app.ActivityThread.handleSendResult(ActivityThread.java:3434)
        at android.app.ActivityThread.access$1300(ActivityThread.java:138)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1284)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:149)
        at android.app.ActivityThread.main(ActivityThread.java:5045)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:515)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:602)
        at dalvik.system.NativeStart.main(Native Method)
 Caused by: android.database.sqlite.SQLiteException: Cannot add a NOT NULL column with default value NULL (code 1): , while compiling: ALTER TABLE bloodPressureTable ADD COLUMN DateTEXT NOT NULL
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1672)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
        at com.dissertation.michael.biolog.MyDatabase$DbHelper.onUpgrade(MyDatabase.java:75)
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
        at com.dissertation.michael.biolog.MyDatabase.open(MyDatabase.java:90)
        at com.dissertation.michael.biolog.MainActivity.onActivityResult(MainActivity.java:170)
        at android.app.Activity.dispatchActivityResult(Activity.java:5430)
        at android.app.ActivityThread.deliverResults(ActivityThread.java:3387)
    at android.app.ActivityThread.handleSendResult(ActivityThread.java:3434)
    at android.app.ActivityThread.access$1300(ActivityThread.java:138)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1284)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:149)
at android.app.ActivityThread.main(ActivityThread.java:5045)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:602)
at dalvik.system.NativeStart.main(Native Method)
03-02 16:03:48.424    1811-1811/com.dissertation.michael.biolog I/Process: Sending signal. PID: 1811 SIG: 9

或者(如果简单的话最好)完全删除我的表以便再次创建版本 1 的方法将是理想的。 (此时尚未将任何有用数据输入数据库)...干杯!

Answer 1

您的应用程序因 java.lang.NumberFormatException: Invalid int: "300-400" 而崩溃，这意味着您传递了无效的 INTEGER 和 300-400 格式 > 不是有效的整数。

附注如果您想在数据库列中输入 300-400，请使用 TEXT 或 VARCHAR(15) 作为列的数据类型