时间戳

首页 > 解决方案 > 我如何使用 sql lite 验证登录

android - 我如何使用 sql lite 验证登录

问题描述

我在插入数据后验证数据时遇到问题。而且我不知道插入数据是代码是否有效。

我尝试使用光标，但我不知道错误在哪里。

这是我的 DatabaseHelper.java

private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "user_db";


public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {

    // create notes table
    db.execSQL(User.CREATE_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + User.TABLE_NAME);

    // Create tables again
    onCreate(db);
}

public long insertUser(String email,String password,String dob, String sex) {
    // get writable database as we want to write data
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    // `id` and `timestamp` will be inserted automatically.
    // no need to add them
    values.put(User.COLUMN_Email, email);
    values.put(User.COLUMN_Password, password);
    values.put(User.COLUMN_DOB, dob);
    values.put(User.COLUMN_Sex, sex);

    // insert row
    long id = db.insert(User.TABLE_NAME, null, values);

    // close db connection
    db.close();

    // return newly inserted row id
    return id;
}

public User getUser(String email, String password) {
    // get readable database as we are not inserting anything
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(User.TABLE_NAME,
            new String[]{User.COLUMN_ID, User.COLUMN_Email,User.COLUMN_Password, User.COLUMN_TIMESTAMP},
            User.COLUMN_Email + "=?" + " AND " + User.COLUMN_Password + "=?",
            new String[]{email, password}, null, null, null, null);

    User note = null;
    if (cursor != null) {
        if (cursor.moveToFirst()) {
            note = new User(
                    cursor.getInt(cursor.getColumnIndex(User.COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndex(User.COLUMN_Email)),
                    cursor.getString(cursor.getColumnIndex(User.COLUMN_Password)),
                    cursor.getString(cursor.getColumnIndex(User.COLUMN_DOB)),
                    cursor.getString(cursor.getColumnIndex(User.COLUMN_Sex)),
                    cursor.getString(cursor.getColumnIndex(User.COLUMN_TIMESTAMP)));
        }
    }
    // close the db connection
    cursor.close();

    return note;
}

public List<User> getAllUsers() {
    List<User> notes = new ArrayList<>();

    // Select All Query
    String selectQuery = "SELECT  * FROM " + User.TABLE_NAME + " ORDER BY " +
            User.COLUMN_TIMESTAMP + " DESC";

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            User note = new User();
            note.setId(cursor.getInt(cursor.getColumnIndex(User.COLUMN_ID)));
            note.setEmail(cursor.getString(cursor.getColumnIndex(User.COLUMN_Email)));
            note.setPassword(cursor.getString(cursor.getColumnIndex(User.COLUMN_Password)));
            note.setDOB(cursor.getString(cursor.getColumnIndex(User.COLUMN_DOB)));
            note.setSex(cursor.getString(cursor.getColumnIndex(User.COLUMN_Sex)));
            note.setTimestamp(cursor.getString(cursor.getColumnIndex(User.COLUMN_TIMESTAMP)));

            notes.add(note);
        } while (cursor.moveToNext());
    }

    // close db connection
    db.close();

    // return notes list
    return notes;
}

这是检查用户的代码

private void cekUser(String username,String password) {
        User n = db.getUser(username,password);

        if (n != null) {
            Login();
        }
        else{
            Toast.makeText(Login.this,"Register First",Toast.LENGTH_SHORT).show();
        }
    }

这是我的数据库模型

public static final String TABLE_NAME = "user";

    public static final String COLUMN_ID = "id";
    public static final String COLUMN_Email = "email";
    public static final String COLUMN_Password = "password";
    public static final String COLUMN_DOB = "dob";
    public static final String COLUMN_Sex = "sex";
    public static final String COLUMN_TIMESTAMP = "timestamp";

    private int id;
    private String email;
    private String password;
    private String dob;
    private String sex;
    private String timestamp;


    // Create table SQL query
    public static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + COLUMN_Email + " TEXT,"
                    + COLUMN_Password + " TEXT,"
                    + COLUMN_DOB + " TEXT,"
                    + COLUMN_Sex + " TEXT,"
                    + COLUMN_TIMESTAMP + " DATETIME DEFAULT CURRENT_TIMESTAMP"
                    + ")";

    public User() {
    }

    public User(int id, String email,String password, String dob, String sex ,String timestamp) {
        this.id = id;
        this.email = email;
        this.password = password;
        this.dob = dob;
        this.sex = sex;
        this.timestamp = timestamp;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDOB() {
        return dob;
    }

    public void setDOB(String dob) {
        this.dob = dob;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(String timestamp) {
        this.timestamp = timestamp;
    }

我希望我可以转移到另一个活动，但我得到错误是在一个空对象引用上

标签： androidsqliteandroid-sqlite

解决方案

首先，您必须在参数AND内的两个条件之间放置WHERE：

Cursor cursor = db.query(User.TABLE_NAME,
        new String[]{User.COLUMN_ID, User.COLUMN_Email,User.COLUMN_Password, User.COLUMN_TIMESTAMP},
        User.COLUMN_Email + "=?" + " AND " + User.COLUMN_Password + "=?",
        new String[]{email, password}, null, null, null, null);

而且emailandpassword是字符串，所以不需要String.valueOf(). 然后，如果在表中找不到用户，则
不应实例化该对象，以便在这种情况下返回：Usernull

User note = null;
if (cursor != null) {
    if (cursor.moveToFirst()) {
        note = new User(
            cursor.getInt(cursor.getColumnIndex(User.COLUMN_ID)),
            cursor.getString(cursor.getColumnIndex(User.COLUMN_Email)),
            cursor.getString(cursor.getColumnIndex(User.COLUMN_Password)),
            cursor.getString(cursor.getColumnIndex(User.COLUMN_DOB)),
            cursor.getString(cursor.getColumnIndex(User.COLUMN_Sex)),
            cursor.getString(cursor.getColumnIndex(User.COLUMN_TIMESTAMP)));
    }
}

推荐阅读