Lado
Reputation: 276
Android Studio SQLite Cursor
I started to learn SQLite in Android Studio. And I decided to make a simple registration. But now I have some problems. When I run program and enter some datas(like name, email and password). It crashed. Android monitor shows me this part of code(where is cursor). I don't know why on this part of code my app crashed? Please help me. Thanks.
Here it is this code:
public boolean isEmailExists(String email) {
SQLiteDatabase db = this.getReadableDatabase();
String[] columns = {KEY_ID};
String selection = KEY_EMAIL + " =?";
String[] selectionArgs = {email};
Cursor cursor = db.query(TABLE_NAME, columns, selection, selectionArgs, null, null, null);
if (cursor != null && cursor.moveToFirst()&& cursor.getCount()>0) {
return true;
}
return false;
}
And this part when try to login. Both code are similar a little:
public User Authonticate(User user) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, new String[] {KEY_ID, KEY_USER_NAME, KEY_EMAIL, KEY_PASSWORD},
KEY_EMAIL + "=?", new String[]{user.email}, null, null, null);
if(cursor != null && cursor.moveToFirst() && cursor.getCount()>0) {
User user1 = new User(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3));
if(user.password.equalsIgnoreCase(user1.password)) {
return user1;
}
}
return null;
}
And this is full code:
package com.example.lado.banksystem;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by lado on 21/3/18.
*/
public class DBHandler extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "banksystem.db";
public static final int DATABASE_VERSION = 1;
public static final String TABLE_NAME = "banksystem";
public static final String KEY_ID = "id";
public static final String KEY_USER_NAME = "username";
public static final String KEY_EMAIL = "email";
public static final String KEY_PASSWORD = "password";
public DBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String query = "CREATE TABLE " + TABLE_NAME + "( " + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ KEY_USER_NAME + " TEXT, " + KEY_EMAIL + " TEXT, " + KEY_PASSWORD + " TEXT " + ")";
db.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
public void addUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_USER_NAME, user.userName);
values.put(KEY_EMAIL, user.email);
values.put(KEY_PASSWORD, user.password);
db.insert(TABLE_NAME, null, values);
db.close();
}
public User Authonticate(User user) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, new String[] {KEY_ID, KEY_USER_NAME, KEY_EMAIL, KEY_PASSWORD},
KEY_EMAIL + "=?", new String[]{user.email}, null, null, null);
if(cursor != null && cursor.moveToFirst() && cursor.getCount()>0) {
User user1 = new User(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3));
if(user.password.equalsIgnoreCase(user1.password)) {
return user1;
}
}
return null;
}
public boolean isEmailExists(String email) {
SQLiteDatabase db = this.getReadableDatabase();
String[] columns = {KEY_ID};
String selection = KEY_EMAIL + " =?";
String[] selectionArgs = {email};
Cursor cursor = db.query(TABLE_NAME, columns, selection, selectionArgs, null, null, null);
if (cursor != null && cursor.moveToFirst()&& cursor.getCount()>0) {
return true;
}
return false;
}
}
This is error
3-21 21:23:28.492 19346-19346/com.example.lado.banksystem D/EMAILCHECK: Email passed was >>>[email protected]<<<
03-21 21:23:28.492 19346-19346/com.example.lado.banksystem E/SQLiteLog: (1) no such column: id
03-21 21:23:28.493 19346-19346/com.example.lado.banksystem D/AndroidRuntime: Shutting down VM
03-21 21:23:28.493 19346-19346/com.example.lado.banksystem E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.lado.banksystem, PID: 19346
android.database.sqlite.SQLiteException: no such column: id (code 1): , while compiling: SELECT id FROM banksystem WHERE email =?
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.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1165)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1036)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1204)
at com.example.lado.banksystem.DBHandler.isEmailExists(DBHandler.java:71)
at com.example.lado.banksystem.RegisterActivity$1.onClick(RegisterActivity.java:50)
at android.view.View.performClick(View.java:5637)
at android.view.View$PerformClick.run(View.java:22429)
at android.os.Handler.handleCallback(Handler.java:751)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:6119)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)
Upvotes: 2
Views: 1624
Answers (1)
MikeT
Reputation: 56938
Your issue is a common mis-conception about the onCreate method. That is instead of being invoked every time the Database Helper is instantiated, it is only invoked once when the database is created.
The easy fix when developing and when the underlying data does not need to be preserved is to delete the database (delete the App's data or uninstall the App), thus as the database will then be recreated the changed DB structure will be applied.
If the onUpgrade method will drop the table and then invoke the onCreate method increasing the database version number can also fix the issue.
Upvotes: 1