java - 如果不存在则插入到数据库

Question

如果电子邮件不存在，我尝试将行插入数据库:

public boolean AddUserIfNotExist(String email, String password, String username) {

    SQLiteDatabase db = m_helper.getWritableDatabase();


    //String Query = "INSERT OR IGNORE INTO USERS(email, password, username) 
    //values('" + email + "','" + password + "','" + username + "');";

    String Query = "INSERT INTO USERS(email, password, username) " +
            "SELECT '" + email + "','" + password + "','" + username + "' " 
            + "WHERE NOT EXISTS(SELECT 1 FROM USERS WHERE email = '" + email + "');";

    Cursor cursor = db.rawQuery(Query, null);

    if(cursor.getCount() <= 0) {
        return false;
    }
    return true;
}

为什么不起作用？ cursor.getCount 始终返回 false。

Answer 1

INSERT 语句不会返回任何行，因此您不能使用 rawQuery；对于 INSERT，您必须使用 execSQL。

如果要判断记录是否存在，单独进行即可(同时使用参数避免字符串格式问题):

if (DatabaseUtils.queryNumEntries(db, "Users",
                                  "email = ?", new String[] { email })) > 0) {
    // probably UPDATE?
    return true;
} else {
    db.execSQL("INSERT INTO Users(email, password, username) VALUES(?,?,?)",
               new Object[] { email, password, username });
    return false;
}