Android Sqlite - unable to call database handler outside of onCreate

Question

So here's the code:

Main Activity:

    package com.dummies.myapplication;

import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

import java.sql.SQLClientInfoException;
import java.util.List;
import com.dummies.myapplication.User;

public class MainActivity extends AppCompatActivity {

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

        DatabaseHandlerV2 db = new DatabaseHandlerV2(this);

        // db.deleteTable();

//       /db.makeTable(DatabaseHandlerV2);

        //Log.d("Insert: ", "Inserting ..");
        db.addUser(new User(1, "name", "email","password","van","username"));


        // Reading all contacts
        Log.d("Reading: ", "Reading all contacts..");
        List<User> users = db.getAllUsers();

        for (User us : users) {
            String log = "Id: "+us.getID()+" ,Name: " + us.getName() + " ,email: " + us.getEmail() + " ,password: " + us.getPassword() + ",username:" + us.getUsername() + ",van:" + us.getVan();
            // Writing Contacts to log
            Log.d("Name: ", log);
        }

        Button login = (Button) findViewById(R.id.loginSubmit);
        final EditText clientUsername = (EditText)findViewById(R.id.username);
        final EditText clientPassword = (EditText)findViewById(R.id.password);

        login.setOnClickListener(new View.OnClickListener(){
            public void onClick(View v) {
                //Log.d("User deets username", clientUsername.getText().toString());
                //Log.d("User deets password", clientPassword.getText().toString());
                String username  = clientUsername.getText().toString();
                String password = clientPassword.getText().toString();
                LoginHelper LoginHelper = new LoginHelper();
                LoginHelper.checkLogin(username, password);

            }
        });

        //db.deleteTable();


    }
    public void loginSuccess(String id, String username, String email, String password, String vanId, String name)
    {
        //Log.d("logged in user", id + "," + username + "," + email + "," + password + "," + vanId);
        // db.addUser(new User(id, username, email,password,vanId));
        int intId = Integer.parseInt(id);
        //int intVanId = Integer.parseInt(vanId);
        DatabaseHandlerV2 db = new DatabaseHandlerV2(this);
        db.deleteTableData();
       // db.addUser(new User(intId, name, email,password,vanId,username));
        db.addUser(new User(1, "name", "email","password","van","username"));
        Log.d("Jon Error", "Logged in user is" + intId);

        // db.deleteTable();
    }


}

Database Helper V2:

package com.dummies.myapplication;

/**
 * Created by Jon on 05/02/2017.
 */

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabaseLockedException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandlerV2 extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

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

    // Contacts table name
    private static final String TABLE_USERS= "users";

    // Contacts Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_EMAIL = "email";
    private static final String KEY_PASSWORD = "password";
    private static final String KEY_VAN = "van_id";
    private static final String KEY_USERNAME = "username";


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

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_EMAIL + " TEXT" + "," + KEY_PASSWORD + " TEXT," + KEY_VAN + " INTEGER," + KEY_USERNAME + " TEXT" + ")";

        db.execSQL(CREATE_USERS_TABLE);
        Log.d("Jon error", "creating table");
    }





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

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact
    void addUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        //onCreate(db);

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, user.getName()); // Contact Name
        values.put(KEY_EMAIL, user.getEmail()); // Contact Email
        values.put(KEY_PASSWORD, user.getPassword());
        values.put(KEY_VAN, user.getVan());
        values.put(KEY_USERNAME, user.getUsername());

        // Inserting Row
        db.insert(TABLE_USERS, null, values);
        db.close(); // Closing database connection

    }

    // Getting single contact
    Contact getUser(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_USERS, new String[] { KEY_ID,
                        KEY_NAME, KEY_EMAIL, KEY_PASSWORD, KEY_VAN, KEY_USERNAME }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2));
        // return contact
        return contact;
    }

    // Getting All Contacts
    public List<User> getAllUsers() {
        Log.d("Jon Error", "debug");
        List<User> userList = new ArrayList<User>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_USERS;

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Log.d("Jon Error:", "test");
                User user = new User();
                user.setID(Integer.parseInt(cursor.getString(0)));
                //Log.d(cursor.getString(0));
                user.setName(cursor.getString(1));
                user.setEmail(cursor.getString(2));
                user.setPassword(cursor.getString(3));
                user.setVan(cursor.getString(4));
                user.setUsername(cursor.getString(5));
                // Adding contact to list
                userList.add(user);
            } while (cursor.moveToNext());
        }

        // return contact list
        return userList;
    }

    // Updating single contact
    public int updateUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, user.getName());
        values.put(KEY_EMAIL, user.getEmail());
        values.put(KEY_PASSWORD, user.getPassword());
        values.put(KEY_VAN, user.getVan());
        values.put(KEY_VAN, user.getUsername());

        // updating row
        return db.update(TABLE_USERS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(user.getID()) });
    }

    // Deleting single contact
    public void deleteUser(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_USERS, KEY_ID + " = ?",
                new String[] { String.valueOf(contact.getID()) });
        db.close();
    }

    public void deleteTable()
    {
        SQLiteDatabase db = this.getReadableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        Log.d("Jon error", "deleteing");
    }

    public void deleteTableData()
    {
        SQLiteDatabase db = this.getReadableDatabase();
        db.execSQL("DELETE FROM " + TABLE_USERS);
    }


    // Getting contacts Count
    public int getUsersCount() {
        String countQuery = "SELECT  * FROM " + TABLE_USERS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

}

Now I'm sure the issue is because I'm calling db.addUser(new User(1, "name", "email","password","van","username")); from the loginSuccess method.

How can I get this simple insert to work? I'm sure the issue is soemthing to do with contexts. I've tried this with no luck Static way to get 'Context' on Android?

Thanks!

Answer 1

Try using a singleton design pattern to access the data. Check this post out to see how.

As far as I can see loginSuccess() is called after receiving data from a web server.

It is probable that your app is trying to write data to the database simultaneously.

Edit: When using multiple instances of DatabaseHandlerV2 consider closing the connection.

Example:

Helper:

public class SampleHelper extends SQLiteOpenHelper {

    private static SampleHelper instance;
    private static Context mContext;

    public static synchronized SampleHelper getHelper(Context context) {
        if (instance == null)
             instance = new SampleHelper(context);
        return instance;
    }

    private SampleHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;
    }
}

Writing:

SampleHelper helper = SampleHelper.getHelper(mContext);
SQLiteDatabase writableDb = helper.getWritableDatabase();
String statement = "INSERT OR REPLACE INTO table_name(colA,colB) VALUES (valA,valB)";
writableDb.execSQL(statement);

Reading:

Cursor cursor = null;
try {    
    cursor = SampleHelper.getHelper(mContext).getReadableDatabase().rawQuery("SELECT colA FROM table_name");
    if (cursor.moveToFirst()) {
        do {
            /**read the data here**/
        } while (cursor.moveToNext());
    }
}
finally {
    if(null != cursor)
        cursor.close();
}

As you can notice the connection to the database is not closed. When using a singleton pattern we use only one instance of SampleHelper so we can keep the connection opened.

I did not check your updated log untill now. It says:

A SQLiteConnection object for database '/data/data/com.dummies.myapplication/databases/userManager' was leaked! Please fix your application to end transactions in progress properly and to close the database when it is no longer needed.

That means your did not close the connection(db.close();), thus the null pointer exception when executing this.getReadableDatabase(); on deleteTableData() method.

I still recommend using the sample code provided to avoid database is locked exceptions.

For openOrCreateDatabase exception check this post. Notice that the database name ends with .db. Also check if your context is null or not.

Answer 2

You have to open a writable database (not readable) every time you want to change its structure. So, put this line: SQLiteDatabase db = this.getWritableDatabase(); instead of this: SQLiteDatabase db = this.getReadableDatabase(); within every method in your DatabaseHandlerV2 class that require inserting, deleting or updating data into the db.

In this specific case, to solve this error you have to change the deleteTableData() method in this way:

public void deleteTableData()
{
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_USERS);
}

Answer 3

DatabaseHandlerV2 maindb=null;

@Override

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    maindb= new DatabaseHandlerV2(this);

}

public void loginSuccess(String id, String username, String email, String password, String vanId, String name)

{ maindb.deleteTableData();

}