安卓SQLite数据库主键查询

Question

我已经在我的android应用程序中成功创建了一个数据库。它有一个名为注册号的主键，用户在向我的应用程序注册时输入。一切正常，但如果两个用户输入相同的注册号，则不会创建帐户，并且会抛出异常：

android.database.sqlite.SQLiteConstraintException: column registration_number 
is not unique (code 19)

这很好。我希望应用程序的行为是这样的。但是我如何通知用户这个注册号不可用呢？我的代码：

public class DatabaseOperations extends SQLiteOpenHelper {

public static final int database_version=1;
public static final String DATABASE_NAME = "Aliah.db";
public static final String TABLE_NAME = "student_info";
public static final String REGISTRATION_NUMBER = "registration_number";
public static final String USER_NAME = "user_name";
public static final String USER_PASS = "user_pass";
public static final String EMAIL = "email";


public DatabaseOperations(Context context) {
    super(context, DATABASE_NAME, null, database_version);
    Log.d("Databse operations", "Database created");
}

@Override
public void onCreate(SQLiteDatabase sdb) {

    try{
    sdb.execSQL("create table "+ TABLE_NAME +" (registration_number VARCHAR PRIMARY KEY, user_name VARCHAR, user_pass VARCHAR,  email VARCHAR);");
    Log.d("Databse operations", "one row inserted");}catch(SQLException e){};
    }
}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
    onCreate(db);

}

public void putInformation(DatabaseOperations dop,String registration_number,String name,String pass,String email){

    SQLiteDatabase SQ = dop.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(REGISTRATION_NUMBER,registration_number);
    cv.put(USER_NAME, name);
    cv.put(USER_PASS,pass);
    cv.put(EMAIL,email);
    long k = SQ.insert(TABLE_NAME,null,cv);
    Log.d("Databse operations", "One row inserted");

}

public Cursor getInformation(DatabaseOperations dop){

    SQLiteDatabase SQ = dop.getReadableDatabase();
    String[] columns = {REGISTRATION_NUMBER, USER_NAME, USER_PASS, EMAIL};
    Cursor CR = SQ.query(TABLE_NAME,columns,null,null,null,null,null);
    return CR;

}

}

Answer 1

使用insertOrThrow而不是insert。这样，您就可以捕获它可能抛出的SQLException并执行您想要的操作(在本例中，通知用户)。

参考：https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insertOrThrow(java.lang.String,%20java.lang.String,%20android.content.ContentValues)

Answer 2

您正在使用列名registration_number作为PRIMARY KEY，并且registration_number的值应该为unique。

最好添加另一个列id，它是INTEGER，并将此id用作PRIMARY KEY。

使用下面的方法检查重复的 registration number**:**

public boolean isExistRegistrationNumber(String regNumber) {

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor c = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);

    if(c.moveToFirst())
    {
        do 
        {
            String registrationNumber = c.getString(c.getColumnIndex(REGISTRATION_NUMBER)));

            if(registrationNumber.equals(regNumber))
                return true;

        }while(c.moveToNext());
    }

    return false;
}

按如下方式更新 DatabaseOperations 类：

public class DatabaseOperations extends SQLiteOpenHelper {

public static final int database_version=1;
public static final String DATABASE_NAME = "Aliah.db";
public static final String TABLE_NAME = "student_info";
public static final String ID = "id";
public static final String REGISTRATION_NUMBER = "registration_number";
public static final String USER_NAME = "user_name";
public static final String USER_PASS = "user_pass";
public static final String EMAIL = "email";


public DatabaseOperations(Context context) {
    super(context, DATABASE_NAME, null, database_version);
    Log.d("Databse operations", "Database created");
}

@Override
public void onCreate(SQLiteDatabase sdb) {

    try {
        sdb.execSQL("create table "+ TABLE_NAME +" (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, registration_number VARCHAR, user_name VARCHAR, user_pass VARCHAR,  email VARCHAR);");
        Log.d("Databse operations", "one row inserted");}catch(SQLException e){};
    }
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
    onCreate(db);

}

public void putInformation(DatabaseOperations dop,String registration_number,String name,String pass,String email){

    SQLiteDatabase SQ = dop.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(REGISTRATION_NUMBER,registration_number);
    cv.put(USER_NAME, name);
    cv.put(USER_PASS,pass);
    cv.put(EMAIL,email);
    long k = SQ.insert(TABLE_NAME, null, cv);
    Log.d("Databse operations", "One row inserted");

}

public Cursor getInformation(DatabaseOperations dop){

    SQLiteDatabase SQ = dop.getReadableDatabase();
    String[] columns = {REGISTRATION_NUMBER, USER_NAME, USER_PASS, EMAIL};
    Cursor CR = SQ.query(TABLE_NAME,columns,null,null,null,null,null);
    return CR;

}

public boolean isExistRegistrationNumber(String regNumber) {

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor c = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);

    if(c.moveToFirst())
    {
        do 
        {
            String registrationNumber = c.getString(c.getColumnIndex(REGISTRATION_NUMBER)));

            if(registrationNumber.equals(regNumber))
                return true;

        }while(c.moveToNext());
    }

    return false;
}

}

最后，对应用程序执行uninstall和reinstall操作，然后再次尝试将数据inserting到数据库中。

希望这能起作用~