database wont update on edit text change android

Question

Okay i am new to android development

Also i went through some already asked questions but i could not solve my issue.

So i am learning android development and for practise i was creating a note keeping app, IT'S NOT YET COMPLETE, i was using SQLite to store the information like Title, and the Note itself

i have three activities in the project namely, HomeActivity, MainActivity and EditorActivity whose code i will be attaching below.

So what i think is the database is not getting updated and storing the new title.

How the app is expected to work : Only first time when you run the app, mainactivity will show you instructions on how to use the app and also set up the database, after that, whenever you open the app you will be directly taken to homeactivity which will display the database contents in a list view. whenever a item in this list view is clicked, the noteID is sent to the editoractivity which has two EditText one for title and one for note itself (currently only working on title) whenever the title is changed, the database should be updated, but that is not happening. Can Someone help me with this ?

CODES :

MainActivity.java

    package com.dharamshi.noteitdownv2;

import android.content.DialogInterface;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.sqlite.SQLiteDatabase;
import android.support.constraint.ConstraintLayout;
import android.support.v4.content.ContextCompat;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.LinearLayout;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    int mInstructionsID = 1;

    ConstraintLayout mConstraintLayout;
    Button mForwardButton;
    Button mBackButton;
    Button mLetsGo;
    LinearLayout mButtonLayout;

    SharedPreferences mSharedPreferences;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mSharedPreferences = this.getSharedPreferences(getPackageName(), MODE_PRIVATE );

        boolean firstTimeSetup = mSharedPreferences.getBoolean("FirstTimeSetup", false);

        if(firstTimeSetup == false) {


            initDatabase();

            mConstraintLayout = findViewById(R.id.mainLayout);
            mForwardButton = findViewById(R.id.forwardButton);
            mBackButton = findViewById(R.id.backButton);
            mButtonLayout = findViewById(R.id.buttonLayout);
            mLetsGo = findViewById(R.id.letsGo);

            mLetsGo.setVisibility(View.INVISIBLE);
            mButtonLayout.setVisibility(View.VISIBLE);

            updateInstructions();
            Toast.makeText(this, "Click on the right side of the screen to continue", Toast.LENGTH_LONG).show();

            mForwardButton.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    if (mInstructionsID <= 4) {
                        mInstructionsID++;
                        updateInstructions();
                    }

                }
            });

            mBackButton.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    if (mInstructionsID > 1) {
                        mInstructionsID--;
                        updateInstructions();
                    }
                }
            });

            mLetsGo.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {


                    mSharedPreferences.edit().putBoolean("FirstTimeSetup", true).apply();


                    Intent intent = new Intent(getApplicationContext(), HomeActivity.class);
                    startActivity(intent);
                    finish();

                }
            });

        }else{
            Intent intent = new Intent(getApplicationContext(), HomeActivity.class);
            startActivity(intent);
            finish();
        }

    }



    public void updateInstructions(){

        switch (mInstructionsID){

            case 1: mConstraintLayout.setBackgroundResource(R.drawable.noteitdownbgone);
                    break;

            case 2: mConstraintLayout.setBackgroundResource(R.drawable.noteitdownbgtwo);
                    break;

            case 3: mConstraintLayout.setBackgroundResource(R.drawable.noteitdownbgthree);
                    break;

            case 4: mConstraintLayout.setBackgroundResource(R.drawable.noteitdownbgfour);
                    break;


        }

        if(mInstructionsID > 4)
            mInstructionsID = 4;

        if(mInstructionsID == 4){

            mButtonLayout.setVisibility(View.INVISIBLE);
            mLetsGo.setVisibility(View.VISIBLE);

        }


    }

    public void initDatabase(){

        try{
            SQLiteDatabase myDatabase = openOrCreateDatabase("Notes", MODE_PRIVATE, null);

            myDatabase.execSQL("CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, title TEXT, message TEXT)");

            myDatabase.execSQL("INSERT INTO notes (title, message) VALUES ('Introduction', 'This is a introduction')");

            Toast.makeText(this, "Database Created!", Toast.LENGTH_SHORT).show();

        }catch (Exception e)
        {
            new AlertDialog.Builder(this)
                    .setTitle("Error")
                    .setMessage(e.getMessage())
                    .setNegativeButton("OK", new DialogInterface.OnClickListener() {
                        @Override
                        public void onClick(DialogInterface dialogInterface, int i) {
                            finish();
                        }
                    }).show();
        }


    }
}

HomeActivity.java

package com.dharamshi.noteitdownv2;

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.constraint.ConstraintLayout;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import java.util.ArrayList;

public class HomeActivity extends AppCompatActivity {

    ConstraintLayout mainLayout;

    public static ArrayList<String> titleList = new ArrayList<>();
    public static ArrayList<String> notesList = new ArrayList<>();
    public static ArrayList<Integer> idList = new ArrayList<>();

    public static ArrayAdapter sArrayAdapter;

    ListView notesListView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_home);

        titleList.clear();
        notesList.clear();
        idList.clear();

        mainLayout = findViewById(R.id.constraintLayout);
        notesListView = findViewById(R.id.notesList);

        SQLiteDatabase sqLiteDatabase = openOrCreateDatabase("Notes" , MODE_PRIVATE, null);

        Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM notes", null);

        int titleIndex = c.getColumnIndex("title");
        int idIndex = c.getColumnIndex("id");
        int messageIndex = c.getColumnIndex("message");


        c.moveToFirst();

        if (c != null) {
            do {
                //Log.i("id", Integer.toString(c.getInt(idIndex)));
                idList.add(c.getInt(idIndex));
                //Log.i("Title", c.getString(titleIndex));
                titleList.add(c.getString(titleIndex));
                //Log.i("Message", c.getString(messageIndex));
                notesList.add(c.getString(messageIndex));

            }while(c.moveToNext());
        }

        sArrayAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, titleList);

        notesListView.setAdapter(sArrayAdapter);

        notesListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {

                Intent intent = new Intent(getApplicationContext(), EditorActivity.class);
                intent.putExtra("noteID", i);

                startActivity(intent);

            }
        });

    }

}

EditorActivity.java

package com.dharamshi.noteitdownv2;

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.widget.EditText;
import android.widget.Toast;

public class EditorActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_editor);

        Intent intent = getIntent();

        final SQLiteDatabase sqLiteDatabase = openOrCreateDatabase("Notes", MODE_PRIVATE, null);

        EditText editTitle = findViewById(R.id.editTitle);
        EditText editNote = findViewById(R.id.editNote);

        final int noteID =intent.getIntExtra("noteID", -1);

        if(noteID != -1)
        {

            editTitle.setText(HomeActivity.titleList.get(noteID));
            editNote.setText(HomeActivity.notesList.get(noteID));

        }

        editTitle.addTextChangedListener(new TextWatcher() {
            @Override
            public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) {

            }

            @Override
            public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {

                sqLiteDatabase.execSQL("UPDATE notes SET title = '" + charSequence.toString() + "' WHERE id = "+ HomeActivity.idList.get(noteID));
                Toast.makeText(EditorActivity.this, "UPDATE notes SET title = '" + charSequence.toString() + "' WHERE id = "+ HomeActivity.idList.get(noteID), Toast.LENGTH_SHORT).show();
                HomeActivity.titleList.set(noteID, charSequence.toString());
                HomeActivity.sArrayAdapter.notifyDataSetChanged();

            }

            @Override
            public void afterTextChanged(Editable editable) {

            }
        });

    }


}

Also i have uploaded the whole package here :

https://drive.google.com/open?id=1Bc18Bbu0dodXFAQj_MeUrxsHEzCX09zp

EDIT : i added a refresh button in the HomeActivity to verify if the database is being updated. Result turns out, no. The database is not getting updated.

public void refreshList(View view){

        idList.clear();
        notesList.clear();
        titleList.clear();

        SQLiteDatabase sqLiteDatabase = openOrCreateDatabase("Notes" , MODE_PRIVATE, null);

        Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM notes", null);

        int titleIndex = c.getColumnIndex("title");
        int idIndex = c.getColumnIndex("id");
        int messageIndex = c.getColumnIndex("message");


        c.moveToFirst();

        if (c != null) {
            do {
                //Log.i("id", Integer.toString(c.getInt(idIndex)));
                idList.add(c.getInt(idIndex));
                //Log.i("Title", c.getString(titleIndex));
                titleList.add(c.getString(titleIndex));
                //Log.i("Message", c.getString(messageIndex));
                notesList.add(c.getString(messageIndex));

            }while(c.moveToNext());
        }

        sArrayAdapter.notifyDataSetChanged();

    }

Answer 1

Try changing this line:

myDatabase.execSQL("CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, title TEXT, message TEXT)");

To

myDatabase.execSQL("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT, message TEXT)");

Answer 2

Your issues are with the use of the id column. First with how it has been defined and then second how you are trying to pass the id from the HomeActivity to the EditorActivity.

Issue 1

Defining the column as id INT PRIMARY KEY will not have what is very likely the desired result of having a unique id assigned (1, then likely 2 then likely 3 etc).

You have to be quite specific and use id INTEGER PRIMARY KEY, this will then define the column as an alias of the rowid and if no value is specified, then it will be a unique id.

To fix this issue change :-

myDatabase.execSQL("CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, title TEXT, message TEXT)");

to :-

myDatabase.execSQL("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT, message TEXT)");

• NOTE for this change to take affect you will need to delete the App's data or uninstall the App and then rerun the App.

Issue 2

The second issue is that in the HomeActivity's onItemClick, you are passing the position of the item in the list and then retrieving this value as if it's the id. Without issue 1 resolved the id of all rows will be null, the position will never match null so you will never update a row.

However, even with the id corrected position will not match the id of the clicked row/item. That is the first id assigned will be 1, the first row is position 0 and then so on.

The good news is that you have the three ArrayLists so that position will equate to the nth element of idList so the fix is relatively simple. You just have to change :-

intent.putExtra("noteID", i);

to :-

intent.putExtra("noteID", idList.get(i));

Other issues.

Issue 3

using :-

    your_cursor.moveToFirst();
    if (your_cursor != null) {
        .....
    }

May result in issues as your_cursor will not be null. A cursor may be empty in which case the getCount() method would return 0 or any of the moveTo??? methods will return false.

I would suggest replacing :-

    Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM notes", null);


    int titleIndex = c.getColumnIndex("title");
    int idIndex = c.getColumnIndex("id");
    int messageIndex = c.getColumnIndex("message");


    c.moveToFirst(); // <<<< NO

    if (c != null) { // <<<< Cursor will not be null does nothing to check
        do {
            //Log.i("id", Integer.toString(c.getInt(idIndex)));
            idList.add(c.getInt(idIndex));
            //Log.i("Title", c.getString(titleIndex));
            titleList.add(c.getString(titleIndex));
            //Log.i("Message", c.getString(messageIndex));
            notesList.add(c.getString(messageIndex));

        }while(c.moveToNext());
    }

with the more compact :-

    Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM notes", null);
    while(c.moveToNext()) {
        idList.add(c.getInt(c.getColumnIndex("id")));
        titleList.add(c.getString(c.getColumnIndex("title")));
        notesList.add(c.getString(c.getColumnIndex("message")));
    }

Issue 4

You are updating the database every time a a change is made (i.e. every time a character is typed or removed). You may find this rather intensive (especially with a Toast).

Note at this stage the changes will not be reflected immediately, but you should see that the data has been updated if you restart the App.

More to come .....

Here's the complete code that implements a solution to the list not changing after an item has been edited. :-

HomeActivity.java

This passes the id to the EditorActivity and uses the onResume method to rebuild the list.

public class HomeActivity extends AppCompatActivity {

    ConstraintLayout mainLayout;

    public static ArrayList<String> titleList = new ArrayList<>();
    public static ArrayList<String> notesList = new ArrayList<>();
    public static ArrayList<Integer> idList = new ArrayList<>();
    public static ArrayAdapter sArrayAdapter;

    SQLiteDatabase sqLiteDatabase; //<<<< ADDED
    ListView notesListView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_home);

        titleList.clear();
        notesList.clear();
        idList.clear();

        mainLayout = findViewById(R.id.constraintLayout);
        notesListView = findViewById(R.id.notesList);
        sqLiteDatabase = openOrCreateDatabase("Notes" , MODE_PRIVATE, null); // <<<< CHANGED

        Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM notes", null);
        /*
        int titleIndex = c.getColumnIndex("title");
        int idIndex = c.getColumnIndex("id");
        int messageIndex = c.getColumnIndex("message");
        c.moveToFirst(); // <<<< NO
        if (c != null) { // <<<< Cursor will not be null does nothing to check
            do {
                //Log.i("id", Integer.toString(c.getInt(idIndex)));
                idList.add(c.getInt(idIndex));
                //Log.i("Title", c.getString(titleIndex));
                titleList.add(c.getString(titleIndex));
                //Log.i("Message", c.getString(messageIndex));
                notesList.add(c.getString(messageIndex));
            }while(c.moveToNext());
        }
        */

        while(c.moveToNext()) {
            idList.add(c.getInt(c.getColumnIndex("id")));
            titleList.add(c.getString(c.getColumnIndex("title")));
            notesList.add(c.getString(c.getColumnIndex("message")));
        }
        sArrayAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, titleList);

        notesListView.setAdapter(sArrayAdapter);

        notesListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {

                Intent intent = new Intent(getApplicationContext(), EditorActivity.class);
                //intent.putExtra("noteID", i); // <<<< i is the position not the ID of the row
                intent.putExtra("noteID", idList.get(i)); //<<<< ADDED
                startActivity(intent);
            }
        });
    }

    //<<<<<<<<<< Added to rebuild the Arrays and to notify the adapter of the changed data
    protected void onResume() {
        super.onResume();
        Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM notes", null);
        idList.clear();
        notesList.clear();
        titleList.clear();
        while(c.moveToNext()) {
            idList.add(c.getInt(c.getColumnIndex("id")));
            titleList.add(c.getString(c.getColumnIndex("title")));
            notesList.add(c.getString(c.getColumnIndex("message")));
        }
        sArrayAdapter.notifyDataSetChanged();
    }
}

EditorActivity.java

This has quite extensive changes including a method setEditTexts to get the values according to the passed id. Also the scope of a number of variables have been changed to the class level.

public class EditorActivity extends AppCompatActivity {

    SQLiteDatabase sqLiteDatabase; //<<<< ADDED
    EditText editTitle, editNote; //<<<< ADDED
    int noteID; //<<<< ADDED

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_editor);

        Intent intent = getIntent();
        sqLiteDatabase = openOrCreateDatabase("Notes", MODE_PRIVATE, null); //<<<< CHANGED
        editTitle = findViewById(R.id.editTitle); //<<<< CHANGED
        editNote = findViewById(R.id.editNote); //<<<< CHANGED
        noteID =intent.getIntExtra("noteID", -1); //<<<<CHANGED

        if(noteID != -1)
        {
            setEditTexts(noteID); //<<<< ADDED
            //editTitle.setText(HomeActivity.titleList.get(noteID)); //<<<< DELETED
            //editNote.setText(HomeActivity.notesList.get(noteID)); //<<<< DELETED
        }

        editTitle.addTextChangedListener(new TextWatcher() {
            @Override
            public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) {

            }

            @Override
            public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {

                String sql = "UPDATE notes SET title = '" + charSequence.toString() + "' WHERE id="+ String.valueOf(noteID); //<<<< CHANGED
                sqLiteDatabase.execSQL(sql); //<<<< CHANGED
                Toast.makeText(EditorActivity.this, sql, Toast.LENGTH_SHORT).show(); //<<<< CHANGED
                //HomeActivity.titleList.set(noteID, charSequence.toString()); //<<<<DELETED done in onResume of HomeActivity
                //HomeActivity.sArrayAdapter.notifyDataSetChanged(); //<<<<DELETED done in onResume of HomeActivity
            }

            @Override
            public void afterTextChanged(Editable editable) {

            }
        });
    }

    //<<<<<<<<<< ADDED
    private void setEditTexts(int id) {
        String whereclasue = "id=?";
        String[] whereargs = new String[]{String.valueOf(id)};
        Cursor c = sqLiteDatabase.query(
                "notes",
                null,
                whereclasue,
                whereargs,
                null,null,null,null
        );
        if (c.moveToFirst()) {
            editTitle.setText(c.getString(c.getColumnIndex("title")));
            editNote.setText(c.getString(c.getColumnIndex("message")));
        }
    }
}