I am trying to make an app named 'moviesinfo', in which I have a login page, with sign up and forgot password option. When I try to log in, the app crashes. I also don't know if the data entered on the sign-up page is being stored in the database or not.
This is my mainactivity.java
package com.example.moviesinfo;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity {
SQLiteDatabase db;
SQLiteOpenHelper openHelper;
private EditText user;
private EditText password;
private Button btn;
private Button btn2;
private Button forgot;
Cursor cursor;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
forgot=findViewById(R.id.forgot);
btn2=(Button) findViewById(R.id.signup);
user= (EditText) findViewById(R.id.username);
password = (EditText) findViewById(R.id.password);
btn = (Button) findViewById(R.id.login);
openHelper= new DatabaseHelper(this);
db=openHelper.getReadableDatabase();
btn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String username1= user.getText().toString();
String password1= password.getText().toString();
cursor = db.rawQuery("SELECT * FROM " +DatabaseHelper.TABLENAME + " WHERE " + DatabaseHelper.COL2 + "=? AND " + DatabaseHelper.COL3+"=?", new String[]{username1,password1});
if (cursor!=null){
if (cursor.getCount()>0){
cursor.moveToNext();
Intent intent = new Intent(MainActivity.this,listmenu.class);
startActivity(intent);
//Toast.makeText(getApplicationContext(),"Login Successful", Toast.LENGTH_SHORT).show();//
}else{
Toast.makeText(getApplicationContext(),"Error" , Toast.LENGTH_SHORT).show();
}
}
}
});
btn2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
sign_up();
}
});
forgot.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
forgot();
}
});
}
private void sign_up()
{
Intent intent= new Intent(MainActivity.this, signup.class);
startActivity(intent);
}
private void forgot()
{
Intent intent= new Intent(MainActivity.this, forgot.class);
startActivity(intent);
}
}
This is the signup.java class
package com.example.moviesinfo;
import android.content.ContentValues;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import java.lang.String;
import java.util.ArrayList;
public class signup extends AppCompatActivity {
SQLiteOpenHelper openHelper;
DatabaseHelper db;
SQLiteDatabase db1;
public String uname = "";
public String pwd = "";
public ArrayList<String> cpwd = new ArrayList<String>();
EditText e1, e2, e3;
Button b1,b2;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_signup);
openHelper= new DatabaseHelper(this);
e1 = (EditText) findViewById(R.id.username);
e2 = (EditText) findViewById(R.id.password);
e3 = (EditText) findViewById(R.id.cpwd);
b1 = (Button) findViewById(R.id.save);
b2 = (Button) findViewById(R.id.login2);
b1.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db1=openHelper.getWritableDatabase();
String username =e1.getText().toString();
String password =e2.getText().toString();
String confirm_password =e3.getText().toString();
insert_data(username,password,confirm_password);
}
});
b2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent intent = new Intent(signup.this,MainActivity.class);
startActivity(intent);
}
});
}
public void insert_data(String username, String password, String confirm_password)
{
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COL2, username);
contentValues.put(DatabaseHelper.COL3, password);
contentValues.put(DatabaseHelper.COL4, confirm_password);
long id=db1.insert(DatabaseHelper.TABLENAME, null, contentValues);
}
}
This is the DatabseHelper.java class
package com.example.moviesinfo;
import com.example.moviesinfo.signup;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME="userdetails.db";
public static final String TABLENAME="user details";
public static final String COL1="id";
public static final String COL2="username";
public static final String COL3="password";
public static final String COL4="confirmpassword";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null,1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + "TABLENAME(ID INTEGER PRIMARY KEY AUTOINCREMENT,USERNAME TEXT,PASSWORD TEXT,CONFIRMPASSWORD TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " +TABLENAME);
onCreate(db);
}
}
I expect that when I click the login button it should jump to the next activity with checking the login details from the database.
The first issue you will encounter is that the table name user details has a space. user will be extracted as the identifier but then the SQL parser is not expecting details, it is not a keyword or clause and thus a syntax error as per the following which would be found in the log :-
5-19 14:09:14.335 26779-26779/s.e.so56180693anotheractivity E/AndroidRuntime: FATAL EXCEPTION: main
Process: s.e.so56180693anotheractivity, PID: 26779
java.lang.RuntimeException: Unable to start activity ComponentInfo{s.e.so56180693anotheractivity/s.e.so56180693anotheractivity.MainActivity}: android.database.sqlite.SQLiteException: near "details": syntax error (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE IF NOT EXISTS user details(_id INTEGER PRIMARY KEY,username TEXT,password TEXT, confirmpassword TEXT)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6669)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
Caused by: android.database.sqlite.SQLiteException: near "details": syntax error (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE IF NOT EXISTS user details(_id INTEGER PRIMARY KEY,username TEXT,password TEXT, confirmpassword TEXT)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
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:1769)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1698)
at s.e.so56180693anotheractivity.DatabaseHelper.onCreate(DatabaseHelper.java:33)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:393)
at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:322)
at s.e.so56180693anotheractivity.MainActivity.onCreate(MainActivity.java:35)
at android.app.Activity.performCreate(Activity.java:7136)
An identifier can have normally unacceptable characters, such as a space, start with a number, if it is enclosed in specific characters as per :-
If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:
- 'keyword' A keyword in single quotes is a string literal.
- "keyword" A keyword in double-quotes is an identifier.
- [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
- keyword A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.
e.g. you could have :-
public static final String TABLENAME="user details"; //<<<<<<<<<< cannot have spaces in a name unless enclosed
//!!!!NOTE!!!! ONLY 1 of the below would be allowed
public static final String TABLENAME="`user details`"; //<<<<<<<<<< e.g (with space)
public static final String TABLENAME= "[user details]";
public static final String TABLENAME = "\"user details\"";
public static final String TABLENAME = "'user details'";
public static final String TABLENAME="user_details"; //<<<<<<<<<< changed to this for testing.
public static final String TABLENAME="user details";
was changed to :-
public static final String TABLENAME="user_details";
The App was uninstalled and rerun.
The next issue is that the SQL to create the table will not create a table named according to the value that the constant TABLENAME (user_details after applying the Fix 1) has. The table is named TABLENAME as the word TABLENAME is in quotes.
It results in the exception :-
2019-05-19 13:56:15.118 26443-26443/s.e.so56180693anotheractivity E/SQLiteLog: (1) no such table: user_details
The following is how it would be suggested that the CREATE TABLE sql is setup:-
//Uses the identifiers (names) as per the variables
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLENAME + "(" +
COL1 + " INTEGER PRIMARY KEY," + // NO NEED FOR INEFFICIENT AUTOINCREMENT
COL2 + " TEXT UNIQUE," + //ADDED UNIQUE so that user names are not duplicated
COL3 + " TEXT, " +
COL4 + " TEXT" +
")"
);
I also don't know if the data entered on the sign-up page is being stored in the database or not.
To enable you to know or not and as you should now be aware of looking in the log then amending the insert_data method as follows will enable you to see if the data is being stored.
public long insert_data(String username, String password, String confirm_password) // Signature changed to return long (id)
{
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COL2, username);
contentValues.put(DatabaseHelper.COL3, password);
contentValues.put(DatabaseHelper.COL4, confirm_password);
long id=db1.insert(DatabaseHelper.TABLENAME, null, contentValues);
//TODO REMOVE LOGGING BEFORE PUBLISHING THE APP
if (id > 0) {
Log.d("INSERTUSERDETAILSROW","The row was successfully inserted into the " + DatabaseHelper.TABLENAME + " table.");
} else {
Log.d("INSERTUSERDETAILSROW","Row not inserted!!!!!!!!!!");
}
return id; // Can be useful to have access to the id that was generated. Note method signature change
}
Example output :-
in the log :-
D/INSERTUSERDETAILSROW: The row was successfully inserted into the user_details table.
If the password and confirm_password values do not match, the row is still added i.e. the confirm_password values serves no purpose.
A check is added to only add data if the password and confirm_password values match. If they do not then a Toast is issued.
b1.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db1=openHelper.getWritableDatabase();
String username =e1.getText().toString();
String password =e2.getText().toString();
String confirm_password =e3.getText().toString();
if (password.equals(confirm_password)) {
insert_data(username, password, confirm_password);
} else {
Toast.makeText(v.getContext(),"Password and Confirm Password do not match. Data not added.",Toast.LENGTH_LONG).show();
}
}
});
Although it won't appear to be a problem your should not start the activity when you want to return from an activity as that will destroy the initial activity and start another. The correct way is to finish the activity and it will then return to the actual activity from which it is was started from.
b2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
//Intent intent = new Intent(signup.this,MainActivity.class);
//startActivity(intent);
finish(); //<<<<<<<<<< Proper way to return
}
});
That's it. The App should now be relatively functional as far as logging in and signing up is concerned.